Subtotals for data analysis
Let’s assume that a company selling cosmetics* records the name of the employee, the region of the sales, the item sold, the number of units sold and the amount ($) for each sale.
Now we may like to know:
1. What is the total earning by region?
2. How many units of an item were sold in a region?
To calculate the earning and units in sold in each different region, click anywhere inside the data, then on the Data tab and finally click on Subtotal in the Outline group. From the pop-up window define your parameters as shown in the Excel training video.
By selecting region from the ‘At each change in’ option, we create subtotals at each point where the value of the region in the column changes. Then under ‘use function’ we select ‘sum’ to total the revenue for each region. We also check the units and amount ($) check boxes in the ‘add subtotal to’ options. This creates the subtotals based on the data values in the respective columns. The ‘replace current subtotals’ check box helps us to replace any existing subtotals. The other check boxes are self-explanatory like ‘clicking remove all’ removes the subtotals from the data.
We have, for example used the sum function for every change in region and we have ‘added subtotals to’ to ‘units’ and ‘amount’ which gives us, as an example, the total of 249 units and $756.35 amount earned for the east region.
*Data taken from the book ‘Data analysis and Business Modeling’ by Wayne l. Winston
Calculating subtotals and working with levels in Excel