Excel 2010: Summing it Up, Filter or Table

In the last two lessons, we learned about Filtering and Excel’s Format as Table command.  Open up the dog breed spreadsheet we’ve been working on.  If you need the data, copy and paste the spreadsheet at the bottom into Excel.

 

Let’s say we want to count our short hair dogs.  The keyword here is “Count.”  We are not totaling something, say adding the inches, instead we are counting the number of dogs.  In Excel go to the cell where you want the answer, then type “=”.  This creates a formula.  The formula we want is =counta which counts cells that have something in them; it counts anything that is not an empty cell.

In the column labeled Short, go to the empty cell just below the last dog, here it would be Cell B15.  Type “=counta” and add an open parenthesis “(“:

=counta(

The open parenthesis tells Excel that you are going to give it a range of cells on which to work its magic.  Now, drag through the data between the heading “Short” and where your formula is (Cell B2 through Cell B14).  Type close parenthesis “)” to tell Excel you are finished with the range, then press Enter.  There are seven short hair dogs in our list.  Excel also added a total line to our table.  Do the same procedure in the Long, Wired, and Curly columns.  Copy and Paste do not work quite the same in tables, so just create the =acount() formula in each column.

This gives us a count of coat types, but it doesn’t tell us things like how many dogs from England have short hair.  Let’s filter out all the dogs except the dogs that originated in England by clicking the filter triangle in the Country of Origin column, click on the check next to Select All to deselect all the countries, then just click the checkbox next to England and OK.  Only two dogs are now visible, but did our numbers change?  No.  When we are working with filters, Excel thinks of all numbers as subtotals; to us it may be subtotal, average, and count, but to Excel they are all subtotals.  Go back to the filter triangle and select all the data so we are back where we started.

Go back to the cell where we counted short hair dogs, and enter the subtotal function right over the counta function by typing:

                =subtotal(

As soon as you type the open parenthesis, Excel gives you lots of options for your subtotal function. 

We want to count, but only the filtered results, right?  So double-click on “counta” which will enter the code 3 in your formula.  The 3 has no mathematic value, it is simple a command for Excel to use the counta function when the results are filtered.  Finish your formula by typing a comma after 3, dragging through the cell range (Cell B2 through Cell B14), and typing the closing parenthesis “)”.  Copy and paste the rest of this row.  Now let’s filter our results and count the German dogs.

Play with your table for a while until you are comfortable.

________________________________________________________

Sample Spreadsheet to Copy & Paste into Excel:

Dog Breed	Short	Long	Wired	Curly	Max Size Inches	Country of Origin
Belgian Shepherd	x	x		x	26	Belgium
Boxer	x				25	Germany
Chihuahua	x	x			9	Mexico
Dachshund	x	x	x		15	Germany
Flat Coated Retriever		x			24	England
Fox Terrier	x		x		15	England
German Shepherd	x	x			26	Germany
Irish Wolfhound		x	x		32	Ireland
Labrador Retriever	x				24	Canada
Maltese		x			10	Greece
Portugeuse Water Dog		x		x	23	Portugal
Shetland Sheepdog		x			16	Scotland
Standard Poodle		x		x	21	Germany

Excel 2010: Why should I use "Format as Table" command when my table is already there?

Yesterday I introduced filtering, but warned you not to use the sort command when you use filtering in your header row (unless you highlight your whole spreadsheet first).  Today, I’ll show you the best way to sort your data AFTER you format it as an Excel table.

Most of our data in Excel is viewed as a table whether or not it is formatted that way.  When you use the "Format as Table" command, Excel looks at your data differently.  Sometimes adding or deleting data in a table can be stormy, but manipulating what is already there is a breeze.

Yesterday, we created a dog breed spreadsheet.  If you saved it yesterday, you will need to remove the filters we created.  Open the spreadsheet.  Remove the filters by following the same steps we used to turn them on.  Click on Row 1.  From the Home ribbon, Editing section, click on the Sort & Filter icon, then pull down Filter.



Now the little down triangles will disappear, and our spreadsheet looks how it did when we started.  You must always remove any filters before doing "Format as Table;"  Excel will not perform the command with Filters (try it and see).

If you did not create the dog breed spreadsheet yet, you may copy and paste the data from below.  I would recommend doing yesterday's filtering lesson before you proceed.

When your dogs are ready, drag through the whole spreadsheet, starting with the left side of the heading row and going down to the bottom right corner.  (Did you know that Standard Poodles came from Germany, not France?)

On the Home ribbon, Style section, click on “Format as Table.”  There are many different colors and formats to choose from – use the one that is easiest on your eyes or your favorite color.  We can change it later, so try something daring.  After you select a scheme, a window will appear showing your range (the cells you want in your entire table), and asking if your table has headers.  Headers means column titles.  Ours does, so click OK.

Here’s my newly formatted table:

I can now filter the x’s as we did yesterday, but I can also sort.  Click on the down triangle in the Dog Breed cell.  I can either select certain breeds or sort A to Z or Z to A.  Click on the Max Size Inches triangle and I can pick specific heights or sort smallest to largest or largest to smallest.  Let’s select only the dog breeds which originated in Germany.  Then sort them by size.  In the next lesson, we'll let Excel count our filtered data.

________________________________________________________

Sample Spreadsheet to Copy & Paste into Excel:

Dog Breed	Short	Long	Wired	Curly	Max Size Inches	Country of Origin
Belgian Shepherd	x	x		x	26	Belgium
Boxer	x				25	Germany
Chihuahua	x	x			9	Mexico
Dachshund	x	x	x		15	Germany
Flat Coated Retriever		x			24	England
Fox Terrier	x		x		15	England
German Shepherd	x	x			26	Germany
Irish Wolfhound		x	x		32	Ireland
Labrador Retriever	x				24	Canada
Maltese		x			10	Greece
Portugeuse Water Dog		x		x	23	Portugal
Shetland Sheepdog		x			16	Scotland
Standard Poodle		x		x	21	Germany