Excel: Subtotal Feature - How To Use This Time Saving Tool

By Glenn Romano Excel’s Subtotal feature is a time saving tool that can help you to summarize important data quickly and effortlessly—a task that could potentially take hours if done manually, having to insert rows, information and formulas to achieve your desired outcome.

For example, let's say you are given a large employee list in Excel and asked to provide management with a quick overall summary of employee data; gross pay totals by Division and Department. No need to get frustrated, just use Excel’s Subtotal feature and get this task completed in minutes. Here's how:

Step 1: Open the list you wish to subtotal.

glenn1

Step 2: Data Tab>> Sort Button - Sort the list by Division and then by Department; a multi-level sort that will group your data in the required order for subtotaling to work correctly.

glenn2

Step 3: Data Tab>>Subtotal Button

In the Subtotal dialogue box>>“At each change in” dropdown list should be changed to Division.  Then click ok.

glenn3

Step 4: Repeat step 3 – Data Tab>>Subtotal Button

This time “At each change in” dropdown list should be changed to Dept.  Remove the checkmark from “Replace current subtotals” so that your first subtotals remain intact.  Then click ok.

glenn4

Step 5: You now have outline buttons on the left side of your file.  Click on the different buttons for the view you want to produce for your data.  If you click on the number 3 outline button you will see the employee data subtotaled by Division and within each Division you will have subtotals by each Dept.

glenn5

 

Five quick steps to produce a nice, easy to read summary of a huge list of data!

For more tips and tricks in Excel, visit us in class or online.

***

glenn-romanoGlenn Romano has been a corporate trainer for Fortune 500 companies for 15 years. He teaches the Office Suite at Learn iT! and has been known to toss out Jolly Ranchers to reward his enthusiastic students.