Summarizing Data, and Auto Filter (an Overview)

Auto Filter

 
Conditional Format:   =MOD(SUBTOTAL(3,$C1:D$2),3)=0
Shows color banding working with/without Filtering.

The main purpose of this example is to show filtering by selecting only entries with "Black" as the color.

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 3 AB0011 White 13.75 4 AB0012 Yellow 14.00 5 AB0013 Green 14.25 6 AB0021 Black 14.50 7 AB0022 White 14.75 8 AB0023 Yellow 15.00 9 AB0024 Green 15.25 10 AB0035 Black 15.50 11 AB0036 White 15.75 12 AC0010 Black 16.00 13 AC0011 White 16.25

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 6 AB0021 Black 14.50 10 AB0035 Black 15.50 12 AC0010 Black 16.00 16 AC0021 Black 14.00 20 AC0035 Black 15.00 24 AD0010 Black 15.50 28 AD0021 Black 13.75 32 AD0035 Black 14.75 The above table is a representation of the results of Data, Filter, Auto Filter By clicking on the list button on Color the following drop down list appears. All (Top 10) (custom) Black Green White Yellow

Suppose you wanted to show only the items where column C had number of parts to be ordered and you only wanted to show rows with values greater than zero to place an order.

• Select row 1 or cell C1
• Data --> Filter --> Auto Filter
• On the drop down in cell C1 choose (Custom)
• on Left side: Greater than
  on Right side: 0

Note the select columns as well as the drop down arrow in C1 have turned blue.  You can use the list as if the other value weren't there -- you can print, copy and paste.

To view all entries under a blued arrow once more, click on the blue arrow and choose "All".

To retain filters but show ALL data, use:  Data --> Filter --> Showall

To undo Auto Filter, simply use Data --> Filter --> (uncheck auto filter)

Auto Filter, Custom

AutoFilter allows you to filter rows in or out depending on values in a column.

With filtering, as with Conditional Formatting, it comes down to a formula or a condition that returns either TRUE or FALSE.

Auto Filter, Limitations

Article ID: Q105322 http://support.microsoft.com/support/kb/articles/q105/3/22.asp < The article is short, so here is a copy... > The information in this article applies to: Microsoft Excel for Windows, versions 5.0, 5.0c Microsoft Excel for the Macintosh, versions 5.0, 5.0a Microsoft Excel for Windows 95, version 7.0 SUMMARY When you use the AutoFilter command to filter data, the resulting drop-down list of unique items is limited to 251 identifiers. If the number of unique identifiers in a list is extremely large, you may receive "Not Enough Memory" errors or your computer may stop responding (hang). NOTE: The limit for Microsoft Excel version 7.0 is 250 due to the addition of one more built-in list item ("Top 10"). The limit in Excel 97 and Excel 98 has been increased to 1000. ----------

You can customize the AutoFilter.  There is a feature described by former Lotus 1-2-3 users that if they place "||" in first cell of a row, it will not be printed.  Excel is more flexible. 

Place "|" or any other character(s) into Column J in each row to be suppressed.  Select Cell J1, which should contain a description of the column then starting from the Data menu item select Filter.  Click on the blue arrow in cell J1.  You could choose blanks, but choose "custom" instead on the left side drop-down, choose "is not equal to".  On the right side drop-down choose "|".

Note the rows that are showing as a result of selection are in Blue.  If you select all cells (Ctrl+A) you will only select the visible cells and can paste those cells elsewhere.

Auto Filter choices:  equals, does not equal, is greater than, is greater than or equal to, is less than, is less than or equal to, begins with, does not begin with, ends with, does not end with, contains, does not contain.

Auto Filter, Custom, Unique

A B C D 1  Name Seq Cnt   =GetFormula(Cn) 2  bob 1  1   =COUNTIF(A$2:A2,A2) 3  sara 2  1   =COUNTIF(A$2:A3,A3) 4  bob 3  2   =COUNTIF(A$2:A4,A4) 5  sara 4  2   =COUNTIF(A$2:A5,A5) 6  dave 5  1   =COUNTIF(A$2:A6,A6) 7  john 6  1   =COUNTIF(A$2:A7,A7) 8  John 7  2   =COUNTIF(A$2:A8,A8) 9  Alex 8  1   =COUNTIF(A$2:A9,A9) 10  Alex 9  2   =COUNTIF(A$2:A10,A10) 11  george  10  1   =COUNTIF(A$2:A11,A11)

A 1  Name 2  bob 3  sara 6  dave 7  john 9  Alex 11  george

Data --> Filter --> Advanced Filter Filter this list in place List Range:  $A:$A Criteria Range:  (leave blank) &chk; Unique records only Only based on Column A, all of the data on the corresponding rows would be selected

Hotel Rooms available on a Date, Example

This example would be filtered on Column E, selecting TRUE, to show number of rooms available on the date shown in E1, you could use =Today() for the current date  The Buy and Sell dates, show purchase and sale of the entire hotel. 

	A	B	C	D	E	F	G
1	Hotel	Rooms	Buy	Sell	01/15/1985	31062
2	Hotel 01	32	01/01/1973		TRUE	=AND(C2<=$E$1,OR(D2="",D2>=$E$1))
3	Hotel 02	20	01/14/1975	01/15/2000	TRUE	=AND(C3<=$E$1,OR(D3="",D3>=$E$1))
4	Hotel 03	15	01/26/1977	01/14/1983	FALSE	=AND(C4<=$E$1,OR(D4="",D4>=$E$1))
5	Hotel 04	100	02/08/1979	01/14/1999	TRUE	=AND(C5<=$E$1,OR(D5="",D5>=$E$1))
6	Hotel 05	30	02/20/1981		TRUE	=AND(C6<=$E$1,OR(D6="",D6>=$E$1))
7	Hotel 06	20	03/05/1983	01/31/2000	TRUE	=AND(C7<=$E$1,OR(D7="",D7>=$E$1))
8	Hotel 07	37	03/17/1985		FALSE	=AND(C8<=$E$1,OR(D8="",D8>=$E$1))
9	Hotel 08	57	03/30/1987	12/31/1999	FALSE	=AND(C9<=$E$1,OR(D9="",D9>=$E$1))
10	Room Tot.	311			TRUE	=1=1
11			Formula used in cell B10: =SUBTOTAL(9,B2:OFFSET(B10,-1,0))

Pivot Table

Sum of Price   Color      Total Black 132.50 Green   86.00 White 134.75 Yellow   87.25 Grand Total 440.50

A Pivot Table is a useful tool for summarizing data.  A pivot table is created from Table -> Pivot Tables, and then using some drag and drop techniques. Color associated with the product line example is selectable within the Pivot Table shown at left.  If additional fields were included, selecting black only might be more useful. More information on Pivot tables by Harald Staff in his Introduction To Pivot Tables  If you have Excel 2000 you can copy and paste the sample data into your spreadsheet, and follow right along with the example.  A Pivot table does not update itself, so Harald has also included a macro that makes the tables update on selecting the worksheet:  (Harald wrote XL2GIF macro)   See Related Areas at bottom of this page for additional references.

Subtotals

A B C 1 Part QTY Color 2 Part_#1 3 Black 3 Part_#1 1 Yellow 4 Part_#1 5 Red 5 Part_#2 4 Black 6 Part_#3  8 Black 7 Part_#3 2 Red Picture at the right was created by pasting into in MS PhotoDraw, cropped and saved (size 4K), grey, 85% compression, 324x156 bits, shown at 50%

Select Column A and Column B Data --> subtotals -->     Part / sum / (x) qty     (x) Replace current subtotals     (x) Summarize below total then on spreadsheet view choose the [2] button to left of spreadsheet. To return to normal Data --> [remove all] button lower left

COUNT, COUNTIF, SUM, SUMIF

John Walkenbach has a fairly concise description and Examples for for some of these functions.  Suggest looking at  Tip 74:  Summing and Counting Using Multiple Criteria  before continuing.

SUMIF, COUNTIF, COUNT

	A	B	C	D
1	Checked	Value	Formula in Column A	comments
2	w	16
3	x	4
4	y	1
5		32
6	x	2
7	6		=SUMIF(A2:A6,"x",B2:B6)	simple formula
8	6		=SUMIF(A$2:OFFSET(A$7,-1,0),"x",B$2:OFFSET(C$7,-1,0))	simplifies insertion/deletion of rows
9	49		=SUMIF(A$2:OFFSET(A$7,-1,0),"<>x",B$2:OFFSET(C$7,-1,0))
10	2		=MATCH("x",A$2:OFFSET(A$7,-1,0))	position in range
11	x		=VLOOKUP("x",A$2:OFFSET(A$7,-1,0),1,FALSE)	Found 1st column value
12	4		=VLOOKUP("x",A$2:OFFSET(C$7,-1,0),2,FALSE)	found 2nd column value
13	3		=COUNTIF(A$2:OFFSET(A$7,-1,0),"<>x")
14	2		=COUNTIF(A$2:OFFSET(A$7,-1,0),"x")

SUMIF, SUMPRODUCT

These can be useful techniques.  This was from a posting by Stephen on 7Jul2000 in misc.

There are two easy ways:

=SUMIF(A1:A6,"Part#1",B1:B6)
=SUMPRODUCT((A1:A6="Part#1")*(B1:B6))

You could replace "Part#1" with a cell reference if you prefer.

The second construct is useful if you want to extend to more than one
condition:
=SUMPRODUCT((condition1)*(condition2)........*(number_range))

Note multiplying the conditions will result in a 1 or a 0, you will not see this technique in Help for SUMRRODUCT.

Tables with True, False, and COUNTIF

	A	B	C	D	E	F	G	H
1	Titles	Price	Count	M.Totals	AMY	ANN	BOB	SAL
2	Music.A	5.00	2	10.00	TRUE	FALSE	TRUE	FALSE
3	Music.B	6.25	3	18.75	FALSE	TRUE	TRUE	TRUE
4	Music.C	4.00	1	4.00	FALSE	TRUE	FALSE	FALSE
5	Music.D	7.00	2	14.00	TRUE	FALSE	FALSE	TRUE
6			8	46.75
7	c2	=COUNTIF(E2:L2,"=True")
8	d2	=B2*C2
9
10		Price	Count	M.Totals	AMY	ANN	BOB	SAL
11	Music.A	5.00	2	10.00	5.00	-	5.00	-
12	Music.B	6.25	3	18.75	-	6.25	6.25	6.25
13	Music.C	4.00	1	4.00	-	4.00	-	-
14	Music.D	7.00	2	14.00	7.00	-	-	7.00
15			8	46.75	12.00	10.25	11.25	13.25
16	c11	=COUNTIF(E11:L11,">0")
17	c15	=SUM(C11:OFFSET(C15,-1,0))
18	d11	=B11*C11
19	d15	=SUM(D11:OFFSET(D15,-1,0))
20	e11	=IF(E2,$B2,0)
21	e15	=SUM(E11:OFFSET(E15,-1,0))

The formulas shown are a result of GetFormula described on my Formula page.

COUNTIF and SUMIF formulas

	A	B	C	D	E
1	Name	Dept	Salary	Dept.Total	Dept.Total
2	John Doe 01	A	40,000		=IF(COUNTIF($B$2:B2,B2)<>COUNTIF(B:B,B2),"",SUMIF($B$2:$B$93,B2,$C$2:$C$93))
3	John Doe 02	A	60,000		(use fill-handle to replicate formula downward)
4	John Doe 03	A	70,000	170,000
5	John Doe 07	B	60,000
6	John Doe 08	B	40,000
7	John Doe 11	B	40,000	140,000
8	John Doe 12	C	60,000
9	John Doe 13	C	60,000
10	John Doe 14	C	40,000
11	John Doe 15	C	70,000	230,000
12	Total			540,000	=SUM(D$2:OFFSET(D12,-1,0))

Conditional Formatting

The key to using Conditional Formatting (C.F.) is to reduce a formula so that it indicates TRUE or FALSE (1 or 0).  C.F. overrides normal cell formatting.  You are limited to the use of three conditional formattings per worksheet (like three wishes).  C.F. began with XL97.  For XL95 you would have to write your own Event macros.

Some specific examples, as they appear in newsgroups:

These examples tell what to select before invoking Format --> Conditional Formatting and all are Formula is, and after coding your formula, choose your format (pattern and font color), the first you see without selecting a tab is the FONT.  When choosing an interior color you might also choose the borders because gridlines disappear with interior color and the borders chosen will only exist while C.F. is in effect.

To get smallest half (rounded up) of the entries in bold, change the formatting formula to
    =A1<=SMALL($A1:$T1,(COUNTA(A$1:T$1)+1)/2)

Consecutive Duplicates:  To compare value in column A with value below, but not include the difference being blank or empty.  Select ALL cells then enter the following conditional formatting formula: 
=AND($A1<>"",OR($A1=$A2,OFFSET($A1,-1,0)=$A1))

Duplicated Anywhere in Column:  Need not be sorted
    =COUNTIF($A:$A,$A1)>1

Conditional Formatting Formulas are considerably harder to show and find once used, unless you know which range to pick.  Each Conditional Format has a range that you can't just see, anyway the following is a start and will work best if it just has a formula, rather than "is less than" type of conditions.

Function CondFormula(myCell, Optional cond As Long = 1) As String
  'Bernie Deitrick programming 2000-02-18, modified D.McR 2001-08-07
  Application.Volatile
  CondFormula = myCell.FormatConditions(cond).Formula1
End Function

More information on Conditional Formatting

Sum of Visible Cells in a Range

The SUBTOTAL Worksheet Function will provide various totals or counts if the cells were hidden with a filter. The following will check for hidden rows and hidden columns to exclude them form the total.  The use of Volatile must be added and recalculatioin will only occur when Calculation happens (i.e.  F9).

Function VisibleSum(cellrange As Range) As Double
   Application.Volatile   'posted 2000-12-03, D.McRitchie, mod. 2002-02-12
   Dim cell As Range
   For Each cell In cellrange.cells
     If Not (cell.Rows.Hidden Or cell.Columns.Hidden) Then
		   If IsNumeric(Cell.Value) Then
         VisibleSum = VisibleSum + cell.Value
			 end if	 
     End If
   Next
End Function

The use of volatile in any functions used on any sheet can really slow things down.  Depending on your data, and what it is that you are hiding, you might be able to use SUMIF Worksheet Function, or SubTotals on the Data menu.  (see Summarizing Data, and Auto Filter (an Overview)

Related Areas

• Alternate-Row Shading in XL97 and above using filters. : =MOD(ROW(),2)=0 J.Walkenbach
• Alternate-Row Shading using a macro -- [N/A deja archives]
• AutoFilter, Display AutoFilter Criteria in Excel (this PC World link is dead)
  Steve Bullen's code to show the criteria actually in use when Autofilter is in Effect.  Article in PC World also has information on use of RANK when tied, and use of alternating stripes via conditional formatting.
• AutoFilter, Find Excel 2000 Data Quickly With AutoFilter -- MS Office, Update
• Color Palette -- the 56 Excel Colors
• Color Stripes, Range, Conditional Formatting, Banding, Greenbar stripes (also see autofilter)
  An example of coloring lines when the value in Column D (4th column) is negative. Some information on ranges. A quick look at conditional formatting available with XL97 and later.
• Color Banding With Conditional Formatting also separate topics C.F., and it's subtopic C.F., Using cells on other sheets.  -- Chip Pearson
• Color-Matching Blues  [http://www.zdnet.com/pcmag/issues/1507/pcmg0103.htm]-- f this link fails to work then search on the title.
• Combine rows, based on Column A posted by Patrick Molloy combines B column as comma separated values for rows that have same column A value (merging cells then deleting rows).
• Conditional Formatting on this site, also see
  David Hager's "Excel Experts E-Letter Archives" letters 3, 8, and 14.
  (some techniques for those still on XL95 can be found at John Walkenbach's tip 25).
• COUNT, Tip 52:Cell Counting Techniques, John Walkenbach, a nice page on range using: DCOUNT, COUNT, COUNTA, COUNTBLANK, COUNTIF, and more
• Pivot Tables, in addition to Harald Staff's excellent Introduction To Pivot Tables already mentioned. 
  • Microsoft Excel PivotTable Resource Center
  • Tushar Mehta http://www.tushar-mehta.com in his RDBMS (Relational Database) area demonstrates relational database and then creates a Pivot table from the data base.  Suggest going through the RDBMS Tutorial without annoying frames.
• SUMIF Examples
• SUMIF, Tip 74:Summing and Counting Using Multiple Criteria
  using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. -- John Walkenbach