4. Sheets: Use Filters to Count, Add, and Summarize Data in a Table — Use Sheets and COUNT
In the previous video, you added filters and filter views to sort particular data so itwould 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 orfilter 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 columnsagain 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 aspecific size of shirt. Then, count how many shirts in that size arelisted. In this example, it’s easy to count manually, but a spreadsheet with hundredsor 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 checkthe total count with the quick reference tool. Then, beneath your filtered data, create alist for the number of shirt sizes in your sheet. Remove the data validation from theblank rows …add the number you counted next to the size. Filter and count each shirt size in the columnfor 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 tablesmore visually appealing, if you like.
Finally, complete your table. Add the amountof 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 spreadsheetfunction 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.