Summarizing data is Microsoft Excel’s main function. The good news is that a lot of number crunching can be done on the fly and without any specialized knowledge. Even if you know advanced summarizing techniques, one or more of these Excel tips might make you look good. I’ll show you three ways to summarize Excel data with little effort—almost like magic.
I’m using Microsoft Excel on Office 365 (desktop) on a Windows 10 64-bit system, but these tips will work in older versions and in the browser edition, with one exception: You can’t add a totaling row to a Table object in the browser. You can work with your own data or download the demonstration .xlsx and .xls files.
LEARN MORE: Office 365 Consumer pricing and features
How to use the status bar to summarize Excel data
The status bar provides instant gratification when summarizing–all you have to do is select the values. Figure A shows the March values selected. The status bar responds by displaying the average, count, and sum of the selected values–all you did was select a few values. This trick can make you look great during a meeting; it’s an illusion, but the boss doesn’t need to know.
Figure A
That’s not all. You can customize the status bar to display exactly the information you need. Simply right-click and check the appropriate options (Figure B).
Figure B
How to use AutoSum to summarize Excel data
You probably know about Excel’s AutoSum tool: You select a cell below or to the right of a few continuous values, click the AutoSum option, and out pops a rabbit… I mean, the sum of the adjacent values. Let’s work through a quick example.
Using the demonstration sheet shown in Figure A, click G3 and then click AutoSum in the Editing group on the Home tab–don’t look away, or you might miss it. Before pressing Enter a second time, you can see that AutoSum inserted a SUM() function that evaluates all of the contiguous values to the left (Figure C). Press Enter a second time to commit the function. If you select a range of cells, AutoSum will insert multiple functions. For instance, if you select G3:G14, AutoSum will enter a SUM() function for each row.You probably know about AutoSum: You select a cell below or to the right of a few continuous values, click the AutoSum option and out pops a rabbit… I mean, the sum of the adjacent values. Let’s work through a quick example.
Using the demonstration sheet shown in Figure A, click G3 and then click AutoSum in the Editing group on the Home tab. Don’t look away or you might miss it. Before pressing Enter a second time, you can see (Figure C) that AutoSum inserted a SUM() function that evaluates all of the contiguous values to the left. Press Enter a second time to commit the function. If you select a range of cells, AutoSum will insert multiple functions. For instance, if you select G3:G14, AutoSum will enter a SUM() function for each row.
Figure C
But wait: There’s more! The AutoSum option has a dropdown that offers several other functions–SUM() is the default, but simply select a different option and continue. How’s that for a nice sleight of hand?
How to filter a table in Excel
EExcel’s Filter feature is probably one of my favorites; I use it a lot, because it’s quick and effective. We won’t explore the entire feature, but I will show one quick behavior that you’ll probably come to respect as much as I do—this feature summarizes.
To apply the filter, click anywhere inside the data range, click the Data tab, and then click Filter in the Sort & Filter group. This displays dropdowns in each header cell. Click those dropdowns to explore filtering options, but that’s not what we’re interested in right now.
So far, so good, but instead of pursuing filtering options, let’s convert this ordinary data range into a Table object. Follow these steps.
-
Click anywhere inside the data range.
-
Click the Insert tab.
-
Click Table in the Tables group.
-
In the resulting dialog, confirm whether your data range has headers (ours does) and click OK.
The Table object has a neat feature—a totaling row—that combined with the built-in filtering feature is almost magical. What’s great about it is that the function evaluates only the filtered set.
Prepare to be enchanted: Click anywhere inside the Table and then click the contextual Table Design tab. In the Table Style Options group, check the Total Row option. Figure D shows the results of a SUBTOTAL() function, which is a powerful and flexible function that you’ll want to explore further.
Figure D
Similar to AutoSum, you can change the function’s purpose. In addition, you can add a function to each column in the Table.
The setup takes a few easy steps to implement, but once it’s in place, those summaries are only a click away. Talk about pulling a rabbit out of your hat!
Easy summarizing
You can’t use the summarized results in further calculations, but you won’t always need that–sometimes, you only need a quick look at what’s going on, and that’s when these techniques will come in handy and really help you shine. Once you’re familiar with these tricks, you will find yourself using them all the time. It’s not really magic–it just seems like it.
Stay tuned for future articles on summarizing Excel data: I’ll also cover the Subtotal feature, PivotTables, and more.
Send me your Microsoft Office questions
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
