This reading illustrates how sorting and filtering can help you pinpoint campaign or business-related insights that you can share with stakeholders.
Table of Contents
Description of spreadsheet
Suppose you have a spreadsheet that contains online sales data, like the one shown in the image below. Recall from the video that a cell in a spreadsheet holds data. Columns are vertical and are labeled alphabetically. Rows are horizontal and are labeled numerically. People refer to cell positions by combining the column and row designation, like cell A2. In the sample online sales data below, cell A2 contains the data, Alexander City.
Spreadsheet contains city, state, customer, auto parts, quantity, and code data in Columns A through F.
Also recall from the video that sorting can put data in a certain order, and filtering can display data you want while excluding other data. Sorting and filtering in this spreadsheet enables you to gather insights like these:
- Number and percentage of purchases made from each campaign (by campaign codes)
- Campaign-related purchases (overall and by state)
Example 1: Purchases made from each campaign
1. To determine the number and percentage of purchases made from each campaign, sort the data in the sheet by Column F, which contains a discount code used in each campaign. Sorting in descending order (Z to A) places campaign-related purchases at the top of the sheet followed by blank cells.
Data menu, with Sort sheet, and then Sort sheet by column F (Z to A) selected to sort the codes in descending order.
2. Next, after sorting, create a filter for Column F to display the purchases for each campaign separately by deselecting the other two campaigns. For example, unchecking 39343E and CGRWAT displays only the purchases made from campaign EZ3043.
Filter menu for Column F, with all the codes in a list checked for display.
3. And finally, count the number of instances of each code each time you filter. Divide the number of instances of each code by the total number of purchases to get the percentage of purchases made from each campaign. For example, 15 purchases made with the code EZ3043 out of a total of 569 purchases is 2.64% of purchases.
Pro tip: For large datasets, instead of manually counting instances, you can use the COUNTA function in Google Sheets or Microsoft Excel. COUNTA returns the number of non-blank cells in a range. For example, if the data range is F2:F500, enter the following in a blank cell: =COUNTA(F2:F500). The number of cells in the range that contain data (such as a campaign code) is returned in that cell.
Insight you could share with stakeholders: 2.64% of all purchases resulted from Campaign EZ3043.
15 rows of filtered data at the top of the spreadsheet with the code EZ3043 in Column F.
If you add the number of campaign-related purchases and divide that value by the total number or purchases, you get a percentage of campaign-related purchases. For example, if 60 purchases were related to the three campaigns, the overall percentage would be 60 divided by 569, or 10.54%.
Rows of filtered data at the top of the spreadsheet with campaign codes displayed in Column F.
Insight you could share with stakeholders: 10.54% of all purchases were campaign-related.
You could then filter the data by state (Column B) to get the breakdown by state. For example, if filtering for Alabama in Column B displays nine results, two of which are campaign-related, you can conclude that 22% of purchases from Alabama were campaign-related. You would have to repeat the filtering process for each state to complete a state-by-state comparison.
Insights you could share with stakeholders:
- 22% of purchases in Alabama were campaign-related
- 0% of purchases in Alaska were campaign-related
- 28% of purchases in Arizona were campaign-related
- (and so on for each subsequent state in the U.S.)
Resources for more information
You can refer to the links listed below for more information about sorting and filtering data in spreadsheets.
Google Sheets
- Sort and filter your data: This resource can help you organize data in Sheets. Use this guide to sort part or all of a spreadsheet. You can sort by text, number, and color. Then, learn how to create filters to show only certain data while hiding the rest. Finally, the article includes information on creating, saving, and removing a filter view.
- COUNTA: This Google Help Center article provides syntax and usage examples for the COUNTA function in Google Sheets.
Microsoft Excel
- Sort data in a range or table: This page guides you through all the steps you will need to sort data by number, text, and color. You’ll also have the option to sort by custom list so that you can customize exactly what you want to sort.
- Filter data in a range or table: This article has step-by-step instructions on how to filter an Excel spreadsheet to show only the data you want to see. You can also use built-in comparison operators, such as “greater than” and “top 10” to reveal only the most relevant data.
- COUNTA function: This article describes the formula syntax and usage of the COUNTA function in Microsoft Excel.