Microsoft – Excel – SubTotals

Background

I have a situation where I need to summarize some data and I don’t really want to do so in a database.

 

SQL Server

In Database we have roll-up and Windowing Functions.

And, it is really likely the way I should go.

 

Excel

But, as the data is not in a database, I will really rather not bring it in.

I will just use Excel and send the Excel file to our business partner.

 

Data in Excel

Menu

Menu – Data

Menu – Data – Outline

 

SubTotal

Subtotal – Process – Category – Country

Click the Data menu option.

Select the range of data by clicking on the column header as well on last data row.

Click the Subtotal menu option…

Subtotal –  Operation

Image

Explanation

  1. At each change in : Country
  2. Use function :- Sum
  3. Add Subtotal to : Population
  4. Replace current options :- Checked
  5. Page break between groups :- Not Checked
  6. Summary below data :- Checked

 

Subtotal –  Results

Image

Explanation

  1. We have subtotals for our two countries, Canada & US
  2. And, we have a Grand Total

Subtotal – Process – Category – State

Subtotal –  Operation

Image

Explanation

  1. At each change in : State
  2. Use function :- Sum
  3. Add Subtotal to : Population
  4. Replace current options :- Not Checked
  5. Page break between groups :- Not Checked
  6. Summary below data :- Checked

 

Subtotal –  Results

Image

Explanation

  1. We have subtotals for State/Regions
  2. Cumulative subtotals for the countries
  3. And, we have a Grand Total

 Summary

To add secondary sub-totals please make sure to un-check “Replace current options” in the subtotal window.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s