DigDB           Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

---

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Roll-up on Excel List - Simpler and More Powerful than Pivot Table / Subtotal

Pivot Table is powerful, but:
1. Output is a cross table, cannot further query or aggregate, sometimes hard to read
2. Inflate file size
3. Way too complicated
4. Cannot do Median, Count Unique, Mode...

Subtotal is simpler, but:

1. Output hard-to-extract, cannot further query or aggregate
2. Inflate and change source data
3. Not intuitive
4. Cannot aggregate by multiple columns or functions; Cannot do Median, Count Unique, Mode...

Below is an Excel list, we will use DigDB's Roll-up function to summarize (aggregate):

• Sum of Sales by Store
• Average of Sales by Product
• Sum of Sales by Store And Department
• Smallest (Min), middle (Median), biggest (Max) Sales amount in each Category
• Count of transactions by Store
• Variety (count of unique values) of Products sold by Store
• Sum of Sales by Year, Year And Month, Weekday...

  

Roll-up vs Pivot Table / Subtotal: Sum of Sales by Store

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. The table area will be automatically selected to include all the continuous data area around the current cell. The auto-selected range is the table to summarize (aggregate).

   

4. Set summary criteria: 'Sum' of 'Sales' by 'Store', therefore:
   • Use the 'Calculations' area to select 'Sum' of 'Sales', then use the upper 'Add' button to add to the criteria box
   • Use the 'Group by(s)' area to select 'Store', then use the lower 'Add' button to add to the criteria box

   

   

   Use the upper '<- Add' button to add 'Sum of Sales'

   

   

   Use the lower '<-Add' button to add 'By Product'

   

5. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: Average of Sales by Product

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. Set summary criteria: Average of Sales by Product
   • Use the 'Calculations' area to select 'Average' of 'Sales', then use the upper 'Add' button to add to the criteria box
   • Use the 'Group by(s)' area to select 'Product', then use the lower 'Add' button to add to the criteria box

   

4. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: Sum of Sales by Store And Department

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. Set summary criteria: Average of Sales by Product
   • Use the 'Calculations' area to select 'Average' of 'Sales', then use the upper 'Add' button to add to the criteria box
   • Use the 'Group by(s)' area to select 'Product', then use the lower 'Add' button to add to the criteria box. There are 2 'Group By'(s), so you will need to 'Add' twice.

   

4. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: find the smallest (Min), middle (Median), biggest (Max) Sales amount in each Category

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. Set summary criteria: find the smallest (Min), middle (Median), biggest (Max) Sales amount in each Category
   • Use the 'Calculations' area to select 'Minimum' of 'Sales', use the upper 'Add' button to add to the criteria box; then select 'Median' of 'Sales', add; then select 'Maximum' of 'Sales', add.
   • Use the 'Group by(s)' area to select 'Category', then use the lower 'Add' button to add to the criteria box

   

4. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: Count of transactions by Store

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. Set summary criteria: Count of transactions by Store
   • Use the 'Calculations' area to select 'Count', then use the upper 'Add' button to add to the criteria box. Note that 'Count' does not need a column name, it just count of the number of rows. In our example, each row is a transaction.
   • Use the 'Group by(s)' area to select 'Store', then use the lower 'Add' button to add to the criteria box

   

4. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: Variety (count of unique values) of Products sold by Store

1. Click any cell within your list area.
2. Invoke 'DigDB->Roll up...'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

3. Set summary criteria: Variety (count of unique values) of Products sold by Store
   • Use the 'Calculations' area to select 'Count of Unique Values in' 'Product', then use the upper 'Add' button to add to the criteria box
   • Use the 'Group by(s)' area to select 'Store', then use the lower 'Add' button to add to the criteria box

   

4. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

Roll-up vs Pivot Table / Subtotal: Sum of Sales by Year, Year And Month, Weekday...

1. Click any cell within 'Date' column, invoke 'DigDB->Column->Convert->Date to->Weekday', or 'Year', or 'Month'

   
   Practice file - roll-up-pivot-table-demo.xls (16k)

2. A new column will added - 'Date to Weekday', you can change the header name to'Weekday'

   

3. Invoke 'DigDB->Roll up...'

   

4. Set summary criteria: Sum of Sales by Weekday
   • Use the 'Calculations' area to select 'Average' of 'Sales', then use the upper 'Add' button to add to the criteria box
   • Use the 'Group by(s)' area to select 'Product', then use the lower 'Add' button to add to the criteria box

   

5. Click 'OK' to get the roll up. Result is shown in a new list on a new sheet

   

6. For Sum of 'Sales' by 'Year', just convert the 'Date' to 'Year', then do the roll-up by 'Year'. For Sum of 'Sales' by 'Year' and 'Month', convert the 'Date' to 'Month', then do a roll-up by 'Year' (already there) and 'Month'.

Two powerful options for roll-ups

• 'Roll-up on Visible Rows' allows you to filter the source data in any way you want, for example, you can use Excel's built-in AutoFilter or using DigDB's Complex Filter, then invoke 'Roll-up' to summarize only the filter result.

  For example, in the above Sales list table, we can filter for Sales > $20, then do a roll up of Sum of Sales by Store, this will show the 2 stores compare with each other in the over-20-dollar sales.

  We can use Filter to segment the source data, and summarize (roll-up) on each subset of source data, without copying source data around. Pivot Table or Subtotal cannot dot that. They will require creating new tables and extra pivot reports.

• 'Case sensitive in Grouping' allows you to choose, for example, in Sum of Sales by Store, whether 'store1' is the same as 'STORE1'.

Need more?

• Browse the complete list of DigDB's powerful features listed on the front page.
• If you see errors or things not working, check out our error reference page.

---

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB           Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.