Excel Subtotals Straight to the Point E-Book

Bill Jelen

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 70

Veröffentlichungsjahr: 2024

Beschreibung

This book provides an in-depth exploration of Excel’s subtotals feature, equipping readers with the tools and insights needed to master data grouping, aggregation, and analysis. Designed for professionals working with structured datasets, the book focuses on practical applications, teaching readers how to organize data effectively, apply subtotals across different categories, and handle challenges such as custom sorting, multi-level grouping, and formatting subtotal rows.
Readers will gain valuable strategies for enhancing reports, including techniques to group data manually, shift subtotals horizontally, and add page breaks for clarity. The book also addresses common issues, such as blank rows after subtotals, subtotals in filtered data, and integrating the powerful SUBTOTAL function for advanced calculations. Each chapter provides step-by-step examples to illustrate methods that simplify complex data operations.
In addition to working with subtotals, this guide introduces advanced tools like the AGGREGATE function, offering new ways to summarize and analyze data while ignoring errors or hidden rows. It also delves into VBA automation for creating subtotals and explores the use of Power Query for more dynamic and flexible data views. With its clear instructions and practical solutions, this book serves as an essential resource for professionals aiming to optimize their Excel workflows and produce cleaner, more insightful reports.

Leseprobe

EXCEL SUBTOTALSStraight to the Point

Bill Jelen

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Excel Subtotals Straight to the Point

© 2018 by Bill Jelen

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

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

Why Have a Book on Subtotals

Preparing Your Data and Adding Subtotals

How to Set up Your Data for Subtotals

How to Fit a Multiline Heading into One Cell

No Tiny Blank Columns Between Columns

How to Sort Data

Sort Days of the Week

Sort a Report into a Custom Sequence

Add Subtotals to a Data set

Subtotal Tricks & Techniques

Use Group & Outline Buttons to Collapse Subtotaled Data

Manually Apply Groups

Group Report Sections

Subtotals Above Each Group

Copy Just Totals from Subtotaled Data

Sort Largest Customers to the Top

Format the Subtotal Rows

Add Other Text to the Subtotal Lines

Subtotal One Column and Count Another Column

Subtotal by Month With Daily Dates

Subtotals by Product Within Region

Add a Page Break For each Group

My Manager Wants a Blank Line After Each Subtotal

Add a Calculation Only to the Subtotal Rows

Shift Subtotals One Column to the Right

Finding G/L Accounts Not in Balance

Why Is Final Subtotal Row Appearing After Many Blank Rows?

Enter a Grand Total of Data Manually Subtotaled

Why Does Subtotal Dialog Sometimes Default to Count?

Adding Subtotals to Dozens of Columns

Can You Get Medians?

Horizontal Subtotals

Subtotals Don't Work with Tables

Using the SUBTOTAL function without Invoking the Command

Adding a Total Visible Below a Filtered Data Set

Using 109 For Rows Manually Hidden

The 9 Basic Arguments

The 9 New Arguments

SUMIF or COUNTIF of Filtered Data

Introducing AGGREGATE

New Arguments for What to Ignore

New Arguments for AggregatE Function Number

Some Functions Require a Fourth Argument in AGGREGATE

How is SMALL or LARGE Different From MIN or MAX?

Doing MINIFS or MAXIFS Before They Existed

Using VBA with Subtotals

Creating Subtotals Using VBA

VBA & TEXTJOIN to Subtotal Dozens of Columns

Replacing Subtotals With Power Query

Using Power Query to Get #2 View of Subtotals

Index

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 TV’s 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. If you need to have a two-row heading, set it up as a single cell with two lines in the row. See "How to Fit a Multiline Heading into One Cell" on page 2.

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. It is okay to have an occasional blank cell, but you should have no entirely blank columns.

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 Excel’s 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,Excel’s 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. You can delete the old heading for "Prior" by moving the cell pointer there and pressing the Delete key.

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.