

Subtotals for data analysis 

Home  Excel 2003  Macros Excel 2007  Macros Excel 2003  Excel 2010  
About Us  Free Ebook  Offline Training  Blog  Our Youtube Videos  

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 popup 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 selfexplanatory 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 



Excel 2007  Excel 2003  Macros Excel 2007  Macros Excel 2003  Excel 2010  
About Us  Free Ebook  Offline Training  Blog  Our Youtube Videos 