Did You Know ::: Automatic Subtotals (& More) :: Data List Outline

Posted: September 23, 2013 by MaxFraudy in Excel
Tags: , ,

UntitledWhen working with a hierarchical data, most often we need to view at various levels of hierarchy. One of the ways of getting it done is using Data List Outline feature of Excel. To better demonstrate what I’m talking about, let me take an example.

I have the following data – the first column contains the month name, the second column contains the region name and the last column has the sales.

Raw Data

Raw Data

Now suppose I wish to see subtotals for each month which would be a sum of all regions for the month. To that I would first select the range that has the data that I want to work with and then go to the Data tab -> Outline group -> Subtotal. You should now see a pop-up like below:

Data List Outline - SubtotalBy default, the first column is selected in ‘At each change in’, and Sum is the default aggregation. You can change it using the dropdowns. Notice that ‘Add subtotal to’ has a list of column names. It is helpful if you have multiple metrics of data. On this window, click OK. Your data should now look like this:

Data List Outline - FinalYou can expand/collapse rows using the ‘-‘ signs on the margin. This is an easy and quick way to aggregate data.

Certain things to keep in mind:

the data should be sorted on the column that you want to use for your ‘At each change in’ option.

you can have multiple levels of aggregation. To do that, simple follow the above steps for each level.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s