Skip to content

3. Sheets: Use Filters to Count, Add, and Summarize Data in a Table — Use Sheets and COUNT

Playback Speed:
Transcript

In the previous video, you added filters and filter views to sort particular data so it would be easier to read. In this video, you will use filters to count, add, and summarize your data. This will make it easier to isolate and analyze.

For example, you may want to use data from one particular column, such as shirt designs, to create an entirely new sheet that totals all shirts sold by each design.

To start, create filters across your header row. Remember, you can only use filters or filter views at one time, so turn off any filter views you created in the previous video.

Then, turn on the filter.

You may need to adjust the widths of the columns again so the data fits the space.

Next, add a new sheet to your spreadsheet.

Rename it, and assign it a new color.

Copy the title row of your sheet to make the style cohesive.

Update the title to match the name of your sheet.

Then, freeze the row.

Your first sheets contained raw data.

But spreadsheets aren't just for listing information. They are also useful tools for making calculations, summarizing, and visualizing data. Return to your Filter and Filter Views sheet.

Then, choose a column of data to filter. Filter the column by one value, such as a specific size of shirt. Then, count how many shirts in that size are listed. In this example, it’s easy to count manually, but a spreadsheet with hundreds or thousands of entries would be difficult and could result in errors.

To let your spreadsheet do the calculation for you, select the entire list and check the total count with the quick reference tool. Then, beneath your filtered data, create a list for the number of shirt sizes in your sheet. Remove the data validation from the blank rows … add the number you counted next to the size. Filter and count each shirt size in the column for shirt 1.

Nice work! To make this information easier to use ...

Copy the table... And paste into a new sheet.

Now, label the columns, and adjust the formatting to make the tables more visually appealing, if you like.

Finally, complete your table. Add the amount of shirts in each size for your other shirt numbers in the filter view sheet...

And paste it in your table. Then, move on to the next video to use a spreadsheet function to calculate totals. Now, it’s your turn: Remove filter views, and add filters to your sheet, Insert a new sheet and name it “Shirt Sales by Type,” Filter Shirt 1 by size, and record the number of shirts purchased in that size, Repeat those steps for the rest of the sizes and shirts purchased, Copy the table and paste it into your new sheet, And add a border, colors, and other formatting to your table.

Next
Instructions
  1. Remove filter views, and add filters to your sheet.
  2. Insert a new sheet and name it “Shirt Sales by Type.”
  3. Filter Shirt 1 by size, and record the number of shirts purchased in that size.
  4. Repeat those steps for the rest of the sizes and shirts purchased.
  5. Copy the table and paste it into your new sheet.
  6. Add a border, colors, and other formatting to your table.