Sorting, Filtering and using AutoSum are great ways to work with your data to present report-style spreadsheets but there’s a better way! How can you present raw data in a way to show subtotals and then suppress and expand to make a great looking Excel spreadsheet? This is an amazing feature that will add a row displaying a subtotal without having to write formulas. Here’s how.
1. It is very important to first sort the column to establish a change in name. This will let Excel know where to insert a row displaying the subtotal for a category of your choice.
2. On the Data tab, click anywhere in the column that you would like to sort and click on the Sort button to alphabetically display your data. Below, you can see that we’ve sorted by manufacturer and we will use the Subtotal feature to create subtotals by manufacturer.
3. On the Data tab in the Outline group, click on the Subtotal button.
4. A Subtotal dialog box will appear giving you choices on how you would like the subtotals displayed. In this case, we’ll choose from the dropdowns to use the Sum function to subtotal by Price at each change in Manufacturer. Click OK.
5. Almost instantly, you’ll see the subtotals of each brand of computer displayed in a new row. At the bottom of each sorted brand, a subtotal is calculated without having to write the formula manually. This was all done in the Subtotals dialog box and created automatically.
6. By scrolling to the bottom of the data, you’ll see a Grand Total.
7. Wait, there’s more. On the left, you’ll notice some small buttons with numbers in them (1, 2, 3). By clicking on these boxes you’ll get a new display of your subtotals. In this case, by clicking on 1, you’ll see just a grand total. By clicking on 2, you’ll see a new breakdown by manufacturer in an easy-to-read format that is great for a report. It is also very easy to expand and collapse the view by clicking on the plus or minus. And by clicking on 3, you’ll see the entire subtotals display. Any view that you choose can be printed or converted to a .pdf for quick and easy reporting.
8. To remove these subtotals and get back to your original raw data, click on the Subtotal button again to display the Subtotal dialog box. Click on the Remove All button and you’re back to the beginning.
