The Subtotal Function, One of Excel’s Unsung Heroes
Published: January 13, 2016
Author: Amy Bishop
Subtotals are awesome for, well, subtotaling. But they’re good for more than that – despite the somewhat misleading name, Subtotals don’t always have to be used for summation; they can be used in several helpful ways including counting and averaging data subsets, among other functions. Subtotals are a great way to aggregate data for data subsets within a larger data set.
I know what you are thinking: “Pivot tables can do all of those things!” To that I’d say, hey, I love pivot tables as much as the next PPCer, but sometimes the subtotal function is a better fit. We’ll cover three use cases for subtotals in this blog post.
First, let’s talk about how to use the subtotal function and how it works. Here’s a quick step-by-step guide to get us started.
A primer on the Subtotal function
Step 1: As I mentioned before, the subtotal function is used to aggregate data for a subset of rows. In order for it to work properly, you need to make sure that you have your subsets grouped together within the sheet. In order to do this, Sort A-Z (or Z-A, it doesn’t matter) in the column that contains the cells that you want to group together. This could be ad copy, keywords, ad groups, you name it.
You’ll notice in the example below that I’ve sorted A-Z for the city, and then I sorted A-Z for the campaign column. Doing this ensures that within each campaign, same-city records are grouped together.
Step 2: Go to the Data tab and choose ‘subtotals’ (over toward the right).
Step 3: A box will appear, allowing you to create settings. The first setting field you’ll have to fill in is the one that fills in this blank, “At each change in _____”. Within this blank, you’ll choose the first column that you sorted in step 1 – the column that contains your groupings. In this example, we’d choose ‘city’ because we want to summarize the city data.
Step 4: Choose your formula and your metrics. There are several formulas you can choose from; the one you pick depends on what you are trying to do. If you want to sum the impressions, you’d choose ‘sum’ and ‘impressions’. Pretty easy, right? In this example, I’ve chosen to sum cost and conversions at each change in city.
The Result: The result is that a subtotal line is added between each change in city that allows me to see the sum of cost and conversions within each city. Since we have this sorted by campaigns, we can see how each city performs within each campaign.
To cut down on clutter, you can click the number two in the top left corner, which will minimize all the rows except subtotals. Or, you can use the minus signs beside each subtotal row. The image below shows the table after minimizing all other rows. The number three will take us back to where we were in the image above and the number one will show only the final grand total.
To remove the subtotals, click the subtotal button again and then choose the button ‘remove all’ from the selection window.
Now that we know how it works, let’s talk about three instances where subtotals are infinitely useful.
One of my favorite ways to use the subtotal function is to count the number of ads within an ad group. You can also do this with a pivot table, but the reason that I prefer to use subtotals is so that I don’t have to toggle back and forth between the counts and the upload spreadsheet. The subtotals live within the upload spreadsheet.
I use the subtotal function to identify ad groups that have the appropriate number of ads and I remove those ad groups from the sheet. (Note: It’s good to export mobile/desktop ads separately before using the count function so that your counts are most helpful.) After deleting out the ad groups that don’t need extra attention, I’m only left with ad groups that have too many or too few ads. Now I can easily make the appropriate adjustments within my upload sheet to ensure each ad group as the optimal number of ads active.
Checking Search Query Mapping
In order to see if I have search queries mapping to multiple ad groups, I just throw together a quick subtotal. I download the search query report, sort by search queries and then set up my subtotals. I set up my subtotals so that in each change in search query, it will count the number of ad groups.
Now that the data is subtotaled, click the number 2 in the top left corner to minimize all data aside from subtotals. Next, use the sort feature to sort the column in descending order. Expand any subtotals that are greater than 1 to see which ad groups have query-overlap.
Note that if a search query arises more than once with the same ad group, the subtotal will count that ad group twice. That’s why I like the subtotal feature, though. A pivot table will do the same thing, but if you use the subtotal formula, it is easy to see which ad groups have overlap. With a pivot table, you’d have to flip back and forth between your counts and the search query report to find out which matches were issues and which weren’t.
Reduce Clutter before Pivoting
Have you ever created a pivot table that resulted in hundreds or thousands of rows? You can use slicers and some rudimentary filters to view only the data that you want, but it can be pretty time-consuming because it is a very manual process.
Instead, I prefer to use subtotals to clean up the data file before I pivot it. For example, if I’m looking at the geographic report and I am analyzing performance by campaign, I might first run a subtotal to sum up the cost in each city, within each campaign – similar to the example that I used in the primer at the beginning of this article. I’d follow these steps:
- Create a new column and concatenate the Campaign and City (This just makes it easier to see campaign names when you’ve minimized data outside of the subtotals.).
- Sort the data using this new field, to ensure all like entities are together.
- Use the subtotal so that in each change of (Campaign/City) Excel will sum the cost and conversions. Now you have the cost and conversion totals for each city within each campaign.
- Sort by the subtotal of cost and delete out any data that is below the threshold that you deem valuable to analyze.
- Create the pivot table and be glad that it only has meaningful rows of data.
You might be wondering why you couldn’t just set a filter and delete anything below the threshold, without using the subtotal function. The reason is that there are often multiple data records for each city within a campaign (depending on how many columns you add). So you need to aggregate the data before you determine what can be removed.
What else are you using subtotals for? What other Excel functions are your favorites? I’d love to hear in the comments section below!