MrExcel ® is a registered trademark of Tickling Keys, Inc.Īll contents © 1998 - 2022 MrExcel Publishing | All rights reserved.If you have a list of data that you want to group and summarize, you can use Excel Subtotal and Outline to display summary rows or columns. View our Privacy Policy, Cookies Policy, and Terms of Use.Įxcel ® is a registered trademark of the Microsoft Corporation. You can earn a commission for sales leads that you send to us by joining our Any affiliate commissions that weĮarn when you click a link to Amazon or other sites is reinvested in keeping Web site are provided "as is" and we do not guarantee that they can be used in all The Formulas, Functions and Visual Basic procedures on this provides examples of Formulas, Functions and Visual Basic proceduresįor illustration only, without warranty either expressed or implied, includingīut not limited to the implied warranties of merchantability and/or fitness forĪ particular purpose. This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly. Who would have thought that Excel would handle this correctly? Clearly, Derek from Springfield. Second, it is amazing that anyone would ever try this. First, I am amazed that Excel handles this correctly. Excel did not rearrange the detail records they remain in their original sequence. If you go back to the #3 view, you will see the detail records that came along with the subtotal row.
All of the Wag More detail rows moved along with the subtotal row. Behind the hidden rows, Excel actually sorted a chunk of records.
The top customer, Wag More Dog Store, comes to the top of the data set. In the figure below, a Revenue column cell is selected, and you are about to click the ZA sort button. Derek from Springfield showed me that when the data is collapsed in the #2 view, you can sort by any column. Of course, at this point, the customers appear in alphabetic sequence. This is a beautiful summary of a detailed data set. When you click the #2 Group and Outline button, the detail rows are hidden, and you are left with only the subtotal rows and the grand total. But, more importantly, it adds Group and Outline buttons to the left of column A. When you click OK, Excel inserts a subtotal below each group of customers. Choose all of the numeric fields, as shown here. Make sure the Use Function box is set to Sum. Open the At Each Change In dropdown and choose Customer. The Subtotal dialog box always wants to subtotal by the leftmost column.
The data below is sorted by customers in column C.įrom the Data tab, choose Subtotals. Start by making sure your data is sorted. I was doing a seminar in Springfield, and I was showing my favorite subtotal tricks.įor those of you who have never used subtotals, here is how to set them up. This tip is from my friend Derek Fraley in Springfield, Missouri.
0 kommentar(er)
