DigDB      Excel Add-ins      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

Roll-up is an alternative to using pivot table or subtotal:

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

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

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

    Roll up, pivot table, subtotal - list aggregate summary
    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).

    Roll up, pivot table, subtotal - list aggregate summary

  4. Set summary criteria: 'Sum' of 'Sales' by 'Store', therefore:

    Roll up, pivot table, subtotal - list aggregate summary

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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

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

    Roll up, pivot table, subtotal - list aggregate summary
    Practice file - roll-up-pivot-table-demo.xls (16k)

  3. Set summary criteria: Average of Sales by Product

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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...'

    Roll up, pivot table, subtotal - list aggregate summary
    Practice file - roll-up-pivot-table-demo.xls (16k)

  3. Set summary criteria: Average of Sales by Product

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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...'

    Roll up, pivot table, subtotal - list aggregate summary
    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

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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

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

    Roll up, pivot table, subtotal - list aggregate summary
    Practice file - roll-up-pivot-table-demo.xls (16k)

  3. Set summary criteria: Count of transactions by Store

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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...'

    Roll up, pivot table, subtotal - list aggregate summary
    Practice file - roll-up-pivot-table-demo.xls (16k)

  3. Set summary criteria: Variety (count of unique values) of Products sold by Store

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

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

There is only 'Date' in the table. To roll up by 'Year', or 'Year and Month', we need to derive from 'Date' new columns 'Year', 'Month', 'Weekday', then roll up by those columns.
  1. Click any cell within 'Date' column, invoke 'DigDB->Column->Convert->Date to->Weekday', or 'Year', or 'Month'

    Roll up, pivot table, subtotal - list aggregate summary
    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'

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

  4. Set summary criteria: Sum of Sales by Weekday

    Roll up, pivot table, subtotal - list aggregate summary

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

    Roll up, pivot table, subtotal - list aggregate summary

  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

Need more?


Features | Testimonial | Download | Purchase | FAQ | Contact (free forum)

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      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 Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.