Jelen - Excel subtotals: straight to the point

Printed in USA by Hess Print Solutions First Printing: January 2018 Author: Bill Jelen Cover Design: Shannon Travise, Creative Correspondence Cover Illustration: Shannon Mattiza, 6'4 Productions Technical Editor: Bob Umlas Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953 Distributed by Independent Publishers Group, Chicago, IL ISBN 978-1-61547-243-7 PDF, 978-1-61547-365-6 ePub, 978-1-61547-143-0 Mobi Table of Contents About the Author Bill Jelen is the host of MrExcel.com and the author of 54 books about Microsoft Excel including Excel Gurus Gone Wild, Pivot Table Data Crunching, and Excel 2016 In Depth. He has made over 80 guest appearances on TVs Call for Help with Leo Laporte and was voted guest of the year on the Computer America radio show. He writes the monthly Excel column for Strategic Finance magazine. He has produced over 2100 episodes of his daily video podcast Learn Excel from MrExcel. Before founding MrExcel.com in 1998, Jelen spent twelve years in the trenches, as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company automates Excel reports for hundreds of clients around the world.

The website answers over 30,000 questions a year for free for readers all over the world. Jelen hails from Merritt Island, Florida. Why Have a Book on Subtotals Subtotals were added to Excel 97. That makes them newer than the pivot table. I meet people who ignore subtotals because they perceive pivot tables as more powerful. I agree that pivot tables are awesome, but for data sets with many columns of text data, the Subtotals command can produce reports that would be awkward in a pivot table.

Plus, the SUBTOTAL function, and its younger cousin, AGGREGATE, allow you to solve some problems that you can't normally solve in Excel. This book will show you some cool tricks and techniques with the Subtotal command and how you can use the SUBTOTAL and AGGREGATE functions outside of the Subtotal command. Download data files from https://mrexcel.com/download-center/subtotals-243/. Preparing Your Data and Adding Subtotals How to Set up Your Data for Subtotals Problem: I want to be able to use the powerful data commands such as Sort, Filter, Subtotal, Consolidate, and Pivot Table. Is there any special way I should set up the data to begin with? Strategy: You need to follow all the rules to keep your data in list format: Rule 1: Use only a single row of headings above your data. See . See .

Rule 2: Never leave one heading cell blank. You will find that you do this if you add a temporary column. If you forget to add a heading before you sort, this will completely throw off the IntelliSense, and Excel will sort the headings down into the data. Figure 1 Adding a column but forgetting the heading will cause problems. Rule 3: There should be no entirely blank rows or blank columns in the middle of your data. Figure 2 Tiny blank columns between the columns are evil. Figure 2 Tiny blank columns between the columns are evil.

Rule 4: If your heading row is not in row 1, be sure to have a blank row between the report title and the headings. Figure 3 Always leave a blank row between the data and any titles or explanatory notes. Rule 5: Formatting the heading cells in bold will help the Excels IntelliSense module understand that these are headings. Here is a test to see if your data is set up correctly: Select any one cell inside the data set and press Ctrl+* to select what Excel calls the Current Region. You should see that Excel selects from the column headings and out to the right-most column with data. Figure 4 The Current Region extends to the edge of the data in all directions.

Results: If you follow the list format rules, Excels IntelliSense will allow all the data commands to work flawlessly. How to Fit a Multiline Heading into One Cell Problem: In How to Set Up Your Data for Easy Sorting and Subtotals, you say that headings should occupy only one row to allow for easy sorting. My manager requires that I format a report to have the heading Prior Year split, with Prior in one row and Year in a second row. How can I make my manager happy while also following the list format rules? Strategy: This is a very real problem, where form meets function. The right thing to do in Excel is to have Prior Year in one cell. But some managers absolutely, positively want the formatting to be exactly as they specify.

Luckily, there is a strategy that makes it possible to make the manager happy and to correctly set up the data set in Excel, too. Figure 5 Your manager wants this heading on two rows. Type the word Prior. Then you hold down Alt while pressing Enter and type the word Year. The Alt+Enter combination adds a linefeed character in the cell. Figure 6 Use Alt+Enter to go to the next line. Figure 6 Use Alt+Enter to go to the next line.

Results: You have a single cell that contains two lines of text. The cell will work as a heading in pivot tables, subtotals, sorting, and so on. Additional Details: Using Alt+Enter automatically turns on the Wrap Text option for the cell. You could also turn on the Wrap Text option by choosing Home, Wrap Text icon.