Excel - Summarizing Data in Excel Using Subtotals - Episode 2187

Описание: Microsoft Excel Tutorial: Summarizing Data in Excel Using Subtotals

Welcome to the MrExcel Podcast, Episode 2187: Summarize with Subtotals. In this episode, we will be discussing how to use the Subtotals command in Excel to quickly summarize large amounts of data into a concise one-page summary. This is a great trick to have in your Excel arsenal and can save you a lot of time and effort.

To begin, make sure your data is sorted by the column you want to subtotal. In this case, we will be using the Customer column. Simply select a cell in the Customer column and click the A to Z button on the Data tab to sort the data. Next, go to the Data tab and click on the Subtotal button. This will open a dialog box where you can choose the column you want to subtotal by, in this case, it will be the Customer column. Then, select the function you want to use, such as Sum, and choose the columns you want to subtotal. Click OK and you will see that a new row has been added for each change in customer, with the total for that row.

Now, here's where the real magic happens. Click on the number 2 Group & Outline button, which was added by the Subtotal command. This will give you a list of all the customers in your data. To create a one-page summary, simply select all of the data, including the subtotal rows, by starting at cell D1 and pressing Ctrl+Shift+Down Arrow, then Ctrl+Shift+Right Arrow. Next, press Alt+Semicolon to select only the visible cells, and then copy and paste them into a new workbook. And just like that, you have a one-page summary of your data. This method may seem like a lot of steps, but it only takes about 17 clicks to complete.

But wait, there's more! In this episode, we will also be discussing four other methods for summarizing data in Excel: Remove Duplicates, Advanced Filter, Consolidate, and Pivot Tables. These methods are all covered in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time. Click the "I" on the top right-hand corner to be taken to a link for the book. So, stay tuned for the rest of the week as we explore these different methods for summarizing data.

In summary, the Subtotals command in Excel is a powerful tool for quickly summarizing large amounts of data. By sorting your data and using the Subtotals command, you can easily create a one-page summary of your data. And don't forget to check out my new book, MrExcel LIVe, for even more Excel tips and tricks. Thanks for watching and I'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

#excel
#microsoft
#microsoftexcel
#exceltutorial
#exceltips
#exceltricks
#evergreen
#excelsubtotal

This video answers these common search terms:
how to make subtotals in excel bold
how to copy excel subtotals only
how to copy just subtotals in excel
how do i copy only subtotals in excel
how to only copy subtotals from excel
how to use subtotals excel
using subtotals in excel
where are subtotals in excel
you tube how to subtotal in excel
how to use subtotal command in excel
how to add the subtotal command in excel
how to use the subtotal command in excel
how do i use the subtotal command in excel
how to add a second level subtotal feature excel
how to add subtotal in an excel spreadsheet
how to add subtotal in excel
how to add subtotal rows in excel
how to add subtotals to a table in excel
how to add the subtotal in excel
how do i add subtotals in excel
how to do add subtotal in excel
how do you add subtotals in excel
how to sum all the subtotal on excel
how to sum subtotals in excel
is subtotal the same as sum in excel
how does excel subtotal work
how does subtotal work with excel
how does the subtotal in excel work

Table of Contents:
(00:00) Summarize with Subtotals
(00:20) Sorting data by Customer and using Subtotals command
(01:02) Using Group & Outline button to create one-page summary
(01:34) Five different ways to summarize data in Excel
(02:06) Recap of today's episode and preview of upcoming episodes
(02:16) Clicking Like really helps the algorithm

This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with subtotals:
1. Sort the data by customer
2. Data, Subtotals. At each change in customer, choose four columns. OK.
3. Click #2 Group and outline button
4. From Customer heading, Ctrl+Shift+Down+Right
5. Alt+; to select visible cells. Ctrl+C to copy. Ctrl+N for New. Ctrl+V to paste

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...