Summarizing data in Microsoft Excel does not have to be difficult. Put these few techniques to work for almost magical results.
Image: ra2studio, Getty Images / iStockphoto
Summary of data is the main function of Microsoft Excel. The good news is that a lot of calculations can be done directly and without specialized knowledge. Even if you know advanced summary techniques, you can look good with one or more of these Excel tips. I show you three ways to summarize Excel data with little effort – almost like magic.
I use Office 365 (desktop) on a Windows 10 64-bit system, but these tips work in older versions and in the browser edition, with one exception: you cannot add a row of totals 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 prices and functions
How to use the status bar to summarize Excel data
The status bar offers immediate satisfaction when summarizing. You only need to select the values. Figure A shows the selected March values. The status bar responds by displaying the average, number, 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 have to know.
Select values and view the status bar for some quick summaries.
That is not all. You can adjust the status bar to display exactly the information you need. Right-click and check the appropriate options (Figure B).
Adjust the options displayed by the status bar.
How to use AutoSum to summarize Excel data
You probably know the Excel AutoSum tool: you select a cell below or to the right of some continuous values, click on the AutoSum option and a rabbit appears … I mean, the sum of the adjacent values. Let’s go through a quick example.
Using the demonstration sheet displayed in Figure A, click G3, and then click AutoSom in the Edit group on the Home tab – don’t look away or you’ll miss it. Before pressing Enter again, you will see that AutoSum has inserted a SUM () function that evaluates all contiguous values on the left (Figure C). Press Enter a second time to confirm the function. If you select a range of cells, AutoSum inserts multiple functions. For example, if you select G3: G14, AutoSum enters a SUM () function for each row. You probably know AutoSum: you select a cell below or to the right of some continuous values, click on the AutoSum option and a rabbit appears … I mean, the sum of the adjacent values. Let’s go through a quick example.
Using the demonstration sheet displayed in Figure A, click G3, and then click AutoSom in the Edit group on the Home tab. Don’t look away or you might miss it. Before pressing Enter again, you will see (Figure C) that AutoSum has inserted a function SUM () that evaluates all adjacent values on the left. Press Enter a second time to confirm the function. If you select a range of cells, AutoSum inserts multiple functions. For example, if you select G3: G14, AutoSum enters a SUM () function for each row.
AutoSum is a fast way to enter general functions.
But wait: there is more! The AutoSum option has a drop-down list that offers various other functions – SUM () is the default, but just select another option and continue. How’s that for good skill?
How to filter a table in Excel
The filtering function of EExcel is probably one of my favorites; I use it a lot because it’s fast and effective. We will not examine the entire function, but I will display one quick behavior that you will probably respect as much as I do – this function summarizes.
To apply the filter, click anywhere in the data range, click the Data tab, and then click Filter in the Sort and Filter group. Displays drop-down lists in each header cell. Click on those drop-down lists to explore filter options, but we’re not interested in that at the moment.
So far, so good, but instead of pursuing filter options, let’s turn this ordinary data range into a Table object. Follow these steps.
Click anywhere in 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 function – a total row – that in combination with the built-in filter function is almost magical. The great thing is that the function only evaluates the filtered set.
Prepare for enchantment: click anywhere in the table and then click the Tab Design contextual tab. In the Table Style Options group, select the Total Row option. Figure D shows the results of a SUBTOTAL () function, a powerful and flexible function that you want to explore further.
A total row adds a SUBTOTAL () function.
Just like with AutoSum, you can change the purpose of the function. In addition, you can add a function to each column in the table.
Installation requires a few simple steps to implement, but once installed, those summaries are just a click away. Talk about pulling a rabbit out of your hat!
Easy to summarize
You can’t use the summarized results in further calculations, but you won’t always need that – sometimes you just need to look quickly at what’s going on, and that’s when these techniques come in handy and really help you shine . Once you are familiar with these tricks, you will find that you always use them. It’s not really magic – it just looks like it.
Stay tuned for future articles about summarizing Excel data: I will also cover the subtotal function, pivot tables and more.
Send me your Microsoft Office questions
I answer questions from readers when I can, but there is no guarantee. Do not send files unless requested; initial requests for help arriving with attached files are deleted unread. You can send screen shots of your data to clarify your question. Be as specific as possible when contacting me. For example: “Solve problems with my workbook and solve the problem” will probably not get an answer, but “Can you tell me why this formula does not produce the expected results?” power. State the app and version that you are using. I do not receive any reimbursement from TechRepublic for my time or expertise in helping readers, nor do I request reimbursement from readers who I help. You can contact me via [email protected]
Weekly newsletter from Microsoft
Be Microsoft’s insider for your business with the help of these Windows and Office tutorials and the analyzes of our experts on Microsoft business products.
Delivered on Monday and Wednesday
Disclosure of affiliated parties: When you click through from our site or one of our downloads to a retailer or seller and purchase a product or service, we can earn affiliate commissions. This helps support our work, but it doesn’t affect what we cover or how, and it doesn’t affect the price you pay.