Data Filtering and Subtotals

Analyzing part of the data

We have been working with various tables of data. We learned how to sort data alphabetically and from largest to smallest. This is all very useful and helpful. But what if you want to know the total or some other fact about a portion of the list? Spreadsheets are a great tool for doing exactly that!

Let’s look at a table of data about the 200 largest cities in the United States.

Rank	Name	State	Population 2022	Population 2020	Growth %	Density	Land Area Sq Mi
1	New York City	NY	8930002	8804190	1.41%	29729	300.381
2	Los Angeles	CA	3919973	3898747	0.54%	8359	468.956
3	Chicago	IL	2756546	2746388	0.37%	12124	227.369
4	Houston	TX	2345606	2304580	1.75%	3664	640.194
5	Phoenix	AZ	1640641	1608139	1.98%	3169	517.673
6	Philadelphia	PA	1619355	1603797	0.96%	12060	134.279
7	San Antonio	TX	1456069	1434625	1.47%	3002	485.113
8	San Diego	CA	1402838	1386932	1.13%	4305	325.877
9	Dallas	TX	1325691	1304379	1.61%	3902	339.736
10	San Jose	CA	1026700	1013240	1.31%	5774	177.808
11	Austin	TX	996147	961855	3.44%	3114	319.939
12	Jacksonville	FL	975177	949611	2.62%	1305	747.467
13	Fort Worth	TX	954457	918915	3.72%	2762	345.584
14	Columbus	OH	929492	905748	2.55%	4240	219.197
15	Charlotte	NC	903211	874579	3.17%	2940	307.238
16	Indianapolis	IN	901082	887642	1.49%	2492	361.571
17	San Francisco	CA	887711	873965	1.55%	18927	46.903
18	Seattle	WA	762687	737015	3.37%	9095	83.862
19	Denver	CO	738594	715522	3.12%	4818	153.292
20	Washington	DC	707109	689545	2.48%	11566	61.136
21	Nashville	TN	707091	689447	2.50%	1487	475.543
22	Oklahoma City	OK	701266	681054	2.88%	1156	606.445
23	Boston	MA	687257	675647	1.69%	14217	48.339
24	El Paso	TX	684753	678815	0.87%	2660	257.42
25	Portland	OR	666249	652503	2.06%	4994	133.421
26	Las Vegas	NV	653533	641903	1.78%	4610	141.767
27	Memphis	TN	630348	633104	-0.44%	1986	317.357
28	Detroit	MI	624177	639111	-2.39%	4500	138.715
29	Baltimore	MD	578658	585708	-1.22%	7149	80.947
30	Milwaukee	WI	573700	577222	-0.61%	5965	96.184
31	Albuquerque	NM	568301	564559	0.66%	3036	187.215
32	Fresno	CA	551595	542107	1.72%	4808	114.725
33	Tucson	AZ	547131	542629	0.82%	2299	238.014
34	Sacramento	CA	536635	524943	2.18%	5491	97.73
35	Kansas City	MO	517750	508090	1.87%	1644	314.886
36	Mesa	AZ	517302	504258	2.52%	3746	138.085
37	Atlanta	GA	514457	498715	3.06%	3790	135.737
38	Omaha	NE	501469	486051	3.07%	3557	140.983
39	Colorado Springs	CO	491467	478961	2.54%	2520	195
40	Raleigh	NC	480419	467665	2.65%	3293	145.886
41	Long Beach	CA	467638	466742	0.19%	9224	50.696
42	Virginia Beach	VA	463766	459470	0.93%	1895	244.723
43	Miami	FL	450797	442241	1.90%	12524	35.996
44	Oakland	CA	450630	440646	2.22%	8062	55.893
45	Minneapolis	MN	439430	429954	2.16%	8138	54
46	Tulsa	OK	417298	413066	1.01%	2113	197.478
47	Bakersfield	CA	414649	403455	2.70%	2769	149.758
48	Wichita	KS	400564	397532	0.76%	2478	161.662
49	Arlington	TX	400032	394266	1.44%	4177	95.779
50	Aurora	CO	398497	386261	3.07%	2583	154.272
51	Tampa	FL	394809	384959	2.49%	3463	114.017
52	New Orleans	LA	392031	383997	2.05%	2314	169.434
53	Cleveland	OH	367786	372624	-1.32%	4734	77.694
54	Honolulu	HI	353706	350964	0.78%	5842	60.544
55	Anaheim	CA	348936	346824	0.61%	6934	50.32
56	Louisville	KY	344794	386884	-12.21%	1309	263.434
57	Henderson	NV	329586	317610	3.63%	3107	106.082
58	Lexington	KY	327924	322570	1.63%	1156	283.637
59	Irvine	CA	326730	307670	5.83%	4979	65.619
60	Stockton	CA	326624	320804	1.78%	5254	62.172
61	Orlando	FL	321427	307573	4.31%	2907	110.559
62	Corpus Christi	TX	320393	317863	0.79%	2006	159.703
63	Newark	NJ	318431	311549	2.16%	13189	24.144
64	Riverside	CA	317224	314998	0.70%	3904	81.258
65	St. Paul	MN	316819	311527	1.67%	6095	51.978
66	Cincinnati	OH	311791	309317	0.79%	4006	77.836
67	San Juan	PR	311038	322854	-3.80%	7868	39.533
68	Santa Ana	CA	307367	310227	-0.93%	11234	27.361
69	Greensboro	NC	304909	299035	1.93%	2362	129.068
70	Pittsburgh	PA	302425	302971	-0.18%	5461	55.376
71	Jersey City	NJ	301419	292449	2.98%	20444	14.744
72	St. Louis	MO	298034	301578	-1.19%	4827	61.744
73	Lincoln	NE	297622	291082	2.20%	3093	96.223
74	Durham	NC	294542	283506	3.75%	2625	112.221
75	Anchorage	AK	291131	291247	-0.04%	171	1706.8
76	Plano	TX	290624	285494	1.77%	4054	71.682
77	Chandler	AZ	283959	275987	2.81%	4360	65.124
78	Chula Vista	CA	281801	275487	2.24%	5677	49.641
79	Buffalo	NY	281757	278349	1.21%	6978	40.378
80	Gilbert	AZ	279810	267918	4.25%	4086	68.475
81	Madison	WI	277166	269840	2.64%	3493	79.339
82	Reno	NV	271953	264165	2.86%	2501	108.744
83	North Las Vegas	NV	271641	262527	3.36%	2771	98.015
84	Toledo	OH	267603	270871	-1.22%	3325	80.489
85	Fort Wayne	IN	265926	263886	0.77%	2404	110.636
86	Irving	TX	264762	256684	3.05%	3948	67.06
87	Lubbock	TX	262655	257141	2.10%	1950	134.723
88	St. Petersburg	FL	261016	258308	1.04%	4219	61.864
89	Laredo	TX	259027	255205	1.48%	2450	105.743
90	Chesapeake	VA	254864	249422	2.14%	753	338.51
91	Winston-Salem	NC	253531	249545	1.57%	1912	132.614
92	Glendale	AZ	252645	248325	1.71%	4102	61.592
93	Garland	TX	249846	246018	1.53%	4379	57.06
94	Scottsdale	AZ	246157	241361	1.95%	1338	183.987
95	Arlington	VA	244847	238643	2.53%	9418	25.997
96	Enterprise	NV	244501	221831	9.27%	4782	51.129
97	Boise	ID	241686	235684	2.48%	2888	83.674
98	Santa Clarita	CA	239143	228673	4.38%	3380	70.755
99	Norfolk	VA	237045	238005	-0.40%	4449	53.275
100	Fremont	CA	233788	230504	1.40%	3018	77.465
101	Spokane	WA	233003	228989	1.72%	3389	68.759
102	Richmond	VA	231090	226610	1.94%	3856	59.923
103	Baton Rouge	LA	227066	227470	-0.18%	2627	86.449
104	San Bernardino	CA	224537	222101	1.08%	3615	62.108
105	Tacoma	WA	223536	219346	1.87%	4492	49.759
106	Spring Valley	NV	223037	215597	3.34%	6707	33.253
107	Hialeah	FL	222797	223109	-0.14%	10324	21.581
108	Huntsville	AL	221986	215006	3.14%	1036	214.363
109	Modesto	CA	221924	218464	1.56%	5164	42.977
110	Frisco	TX	217213	200509	7.69%	3188	68.125
111	Des Moines	IA	216273	214133	0.99%	2453	88.18
112	Yonkers	NY	214687	211569	1.45%	11919	18.012
113	Port St. Lucie	FL	212901	204851	3.78%	1786	119.202
114	Moreno Valley	CA	211688	208634	1.44%	4130	51.257
115	Worcester	MA	211612	206518	2.41%	5664	37.36
116	Rochester	NY	211480	211328	0.07%	5913	35.766
117	Fontana	CA	210857	208393	1.17%	4895	43.072
118	Columbus	GA	210330	206922	1.62%	972	216.479
119	Fayetteville	NC	210089	208501	0.76%	1421	147.847
120	Sunrise Manor	NV	208868	205618	1.56%	6258	33.376
121	McKinney	TX	208146	195308	6.17%	3109	66.94
122	Little Rock	AR	204405	202591	0.89%	1703	119.992
123	Augusta	GA	203329	202081	0.61%	673	302.274
124	Oxnard	CA	202895	202063	0.41%	7648	26.528
125	Salt Lake City	UT	202379	199723	1.31%	1828	110.698
126	Amarillo	TX	202333	200393	0.96%	1989	101.744
127	Overland Park	KS	202012	197238	2.36%	2687	75.18
128	Cape Coral	FL	201958	194016	3.93%	1906	105.948
129	Grand Rapids	MI	201093	198917	1.08%	4493	44.756
130	Huntington Beach	CA	200455	198711	0.87%	7423	27.003
131	Sioux Falls	SD	200243	192517	3.86%	2560	78.229
132	Grand Prairie	TX	200240	196100	2.07%	2771	72.255
133	Montgomery	AL	199571	200603	-0.52%	1248	159.882
134	Tallahassee	FL	199127	196169	1.49%	1982	100.474
135	Birmingham	AL	198433	200733	-1.16%	1358	146.076
136	Peoria	AZ	198369	190985	3.72%	1127	175.989
137	Glendale	CA	197507	196543	0.49%	6482	30.471
138	Vancouver	WA	196739	190915	2.96%	4036	48.744
139	Providence	RI	193512	190934	1.33%	10514	18.406
140	Knoxville	TN	193114	190740	1.23%	1956	98.713
141	Brownsville	TX	189082	186738	1.24%	1429	132.322
142	Akron	OH	188741	190469	-0.92%	3048	61.933
143	Newport News	VA	187353	186247	0.59%	2716	68.992
144	Fort Lauderdale	FL	186208	182760	1.85%	5384	34.586
145	Mobile	AL	185427	187041	-0.87%	1330	139.465
146	Shreveport	LA	185249	187593	-1.27%	1725	107.361
147	Paradise	NV	184852	191238	-3.45%	3956	46.732
148	Tempe	AZ	184361	180587	2.05%	4612	39.977
149	Chattanooga	TN	183783	181099	1.46%	1286	142.963
150	Cary	NC	182619	174721	4.32%	3110	58.725
151	Eugene	OR	180748	176654	2.27%	4095	44.138
152	Elk Grove	CA	180746	176124	2.56%	4300	42.033
153	Santa Rosa	CA	180189	178127	1.14%	4238	42.519
154	Salem	OR	179715	175535	2.33%	3691	48.691
155	Ontario	CA	177533	175265	1.28%	3553	49.963
156	Aurora	IL	177070	180542	-1.96%	3934	45.009
157	Lancaster	CA	176892	173516	1.91%	1876	94.281
158	Rancho Cucamonga	CA	176289	174453	1.04%	4394	40.117
159	Oceanside	CA	175464	174068	0.80%	4253	41.256
160	Fort Collins	CO	174974	169810	2.95%	3060	57.182
161	Pembroke Pines	FL	174464	171178	1.88%	5340	32.673
162	Clarksville	TN	173480	166722	3.90%	1756	98.796
163	Palmdale	CA	172790	169450	1.93%	1629	106.076
164	Garden Grove	CA	172163	171949	0.12%	9588	17.957
165	Springfield	MO	171112	169176	1.13%	2077	82.392
166	Hayward	CA	166708	162954	2.25%	3661	45.537
167	Salinas	CA	166162	163542	1.58%	7092	23.428
168	Alexandria	VA	163367	159467	2.39%	10939	14.934
169	Paterson	NJ	162438	159732	1.67%	19308	8.413
170	Murfreesboro	TN	161571	152769	5.45%	2618	61.709
171	Bayamon	PR	161242	165368	-2.56%	5977	26.978
172	Sunnyvale	CA	158949	155805	1.98%	7215	22.03
173	Kansas City	KS	158771	156607	1.36%	1272	124.815
174	Lakewood	CO	158584	155984	1.64%	3684	43.047
175	Killeen	TX	158129	153095	3.18%	2899	54.545
176	Corona	CA	158088	157136	0.60%	3954	39.986
177	Bellevue	WA	157752	151854	3.74%	4715	33.461
178	Springfield	MA	156503	155929	0.37%	4911	31.869
179	Charleston	SC	156255	150227	3.86%	1413	110.554
180	Hollywood	FL	155527	153067	1.58%	5703	27.269
181	Roseville	CA	153569	147773	3.77%	3484	44.079
182	Pasadena	TX	152532	151950	0.38%	3500	43.586
183	Escondido	CA	152464	151038	0.94%	4086	37.314
184	Pomona	CA	152245	151713	0.35%	6625	22.982
185	Mesquite	TX	152164	150108	1.35%	3220	47.252
186	Naperville	IL	151078	149540	1.02%	3893	38.811
187	Joliet	IL	150948	150362	0.39%	2353	64.152
188	Savannah	GA	150078	147780	1.53%	1444	103.91
189	Jackson	MS	149739	153701	-2.65%	1348	111.086
190	Bridgeport	CT	149538	148654	0.59%	9309	16.064
191	Syracuse	NY	149310	148620	0.46%	5965	25.03
192	Surprise	AZ	148274	143148	3.46%	1372	108.063
193	Rockford	IL	147811	148655	-0.57%	2296	64.379
194	Torrance	CA	147393	147067	0.22%	7183	20.521
195	Thornton	CO	146487	141867	3.15%	4081	35.891
196	Kent	WA	145424	136588	6.08%	4309	33.75
197	Fullerton	CA	145309	143617	1.16%	6478	22.431
198	Denton	TX	145167	139869	3.65%	1508	96.236
199	Visalia	CA	144772	141384	2.34%	3817	37.924
200	McAllen	TX	144676	142210	1.70%	2202	65.698

You already know how to use the SUM() function to find the total of a range of cells. So you could easily find the total land area for all 200 cites. Or the total of any of the other columns. But let’s learn a very useful way to find the totals for individual states.

Create a new file

Let’s copy the table into a new spreadsheet file.

• Start from the main office.com screen and click on the Excel icon
• Then click on “New blank workbook”
• In the green bar along the top, click on the default file name and rename it to “Filter and Subtotal”
• Then copy the data from the table of information about the cities.
• Paste it into your new worksheet.
• Click on the little dialog that is usually on the right side towards the bottom and then choose “Paste Values”. This will remove the formatting from this web page and just pasted the plain values into the spreadsheet.
• The easiest way to fix the column width is click on the upper left square of the grid to select all the rows and columns. Then double click the line between two columns and all the columns should autoresize to fit the information inside. If just the data is selected like it will be after you paste it in, it won’t work correctly. You need to select all the column as described.
• If you like, you can give the sheet a better name by double clicking on the tab where it says “Sheet1” and giving it a more descriptive name.

If you need help, watch the video on the “Sorting” lesson.

Find a subtotal (the hard way)

Let’s find the total land area for the just cities in KS. This is often referred to as a subtotal. This is because it is a total of a subset, or portion, of the data.

First of all we can do it using ways we have already learned.

• Sort the table by state. Refer the the “Sorting” lesson if needed
• Add a SUM() function on the bottom of the “Land Area” column. Choose the three KS cities for your range value in the SUM() function. All three cities should be together because you sorted the table by state. Your formula should look something like this: =SUM(H94:H96).

Ok! Did you get a total of 361.657? If so, that worked great!

However, you will notice that this method requires you to manually change the range that you sum if you want to change which state you are analyzing. It’s just not as convenient as it could be.

If you re-sort the list by a different column you will notice a different problem. Try sorting by “name”. What happens to the total land area? It’s not totaling the Kansas cities anymore. It is still totaling the cells you asked it to but the cities from KS are no longer there.

So let’s learn a better way. First we will undo what we just did. Then we’ll learn how to filter the data to just show the part we are interested in. And then we’ll learn to use SUBTOTAL().

Undo your changes

Let’s get the table of data back to how it originally was. There are two methods of doing this. You can try whichever way you would like.

Method #1 – Using undo

• On the “Home” menu ribbon, press the undo button enough times to undo everything your previous changes and return it to how it was right after you pasted the data into the sheet.

Method #2 – Manually change it back to how it was.

• Sort the table by “rank” smallest to largest.
• Delete the formula in the cell where you entered the SUM() function.

Filtering

All the major spreadsheet applications can selectively hide and show data. This is called filtering. Only data that matches the filter passes though and is shown. The data that does not match the filter is hidden. It is not deleted, only hidden, and will reappear when the filter is removed.

OK, time to practice!

• Click on the “Data” menu ribbon if you are not already there.
• Select a cell within the table of data
• Click the filter button
• Notice the dropdown arrows by each column heading
• Click on the drop down for “State”
• Uncheck the “select all” box
• Scroll down as needed and select the checkbox for KS
• Click apply

Watch the video if you need help.

Note how the row numbers for the hidden data are missing. They are hidden along with the data. The rows that are shown, in this case the rows with KS in the state column, retain their original row numbers.

Ok now remove the filter.

• Click on the filter/dropdown arrow in the header of the “State” column
• Click on Clear Filter from ‘State’

Make the table a little nicer

Before we move on, let do a couple of things that will make it easier to use the table

• Select the top row and apply bold formatting.
• Select the entire sheet and double click on a column border to auto expand them to the correct width.
• Click on the “View” ribbon and then click Freeze Panes -> Freeze Top Row

Watch the video if you need help.

Experiment with SUM() before learning about SUBTOTAL()

Now let’s experiment with the SUM() function. How does it work with filtered/hidden data?

• Add a SUM() function with the correct range at the bottom of the data in the “Land Area” column. This will likely be something like: =SUM(H2:H201)
• Click on the dropdown on the “State” column header again.
• Select a state of your choice.
• Was the total hidden along with the other rows? Excel tends to hide the next couple of rows on the bottom of the data along with the other rows.
• To see everything again, remove the filter again.
• Enter a SUM() function three or more rows below the data.
• Try filtering the “State” column again.
• Observe the total below the “Land Area” column. It should not be hidden this time. Notice that it doesn’t change. That is because all the rows are still there. We noticed earlier that the row numbers are missing when rows are hidden. They are hidden but still exist so the SUM() function is doing exactly what we told it to do. It is adding all the rows together in your range regardless if they are hidden or not.

In this case, this isn’t what we want to happen. We want to know the total for just the filtered rows. SUBTOTAL() works really well for this.

Using SUBTOTAL()

This function is quite flexible and useful. It can be used to find the total, or the average, or smallest, or the largest, and a number of other things in a column of numbers.

Let’s jump right in.

• Enter a SUBTOTAL() function right below the data in the “Population 2022” column. You don’t need to worry about putting it three rows down. Excel will not hide this formula.
• SUBTOTAL() takes two pieces of information. The type of calculation you want to do. And the range on which you wish to perform the calculation.
• As you start to type in the function you will see a link for more information. Click on this to see what number you should use to sum the range and ignore the hidden rows. You will see that you need to use 109.
• Then enter the range.
• When you are finished your formula should look like this: =SUBTOTAL(109,D2:D201)
• Now you can filter by any state you wish and the subtotal will show you the population for just the filtered cities!

This may be a lot easier to follow by watching the video.

Congratulations! Now you know how to find the subtotal for filtered information.

To finish up:

• Add a subtotal to the “Population 2020” column just like you did on the “Population 2022” column.
• Do the same on the “Land Area” column. Delete the SUM() you did earlier if you haven’t already done so.
• On the “Growth %” and “Density” columns add a subtotal for the average. You will need to click on the more information link to see which number to use.
• Format the bottom row of totals bold so they stand out a little more.

When you are finished it should look a lot like the photo at the top of this lesson. In the photo the data has been filtered to just show cities in KS.

Great job!