3 quick and easy ways to summarize Excel data

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.

Figure A

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).

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.

Figure C

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.

  1. Click anywhere in the data range.

  2. Click the Insert tab.

  3. Click Table in the Tables group.

  4. 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.

Figure D

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

Register today

Also see

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.

Unwary …

The ZenBook series is arguably almost as bad. The ZenBook 13, for example, shouldn't be confused with the ZenBook S13 or ZenBook S as these are three separate families of 13-inch ZenBook laptops. These families are further divided into SKUs starting with the UX prefix like the UX343, UX393, UX333, or UX334, but trying to match these up with the correct ZenBook…

LG’s cutting edge rollable TELEVISION strikes the market for US$ 87,000, hints at future smart device or tablet applications

LG's rollable 65-inch Signature OLED R TV has gone on sale in Korea for around US$87,000. (Image: LG)LG has finally released its Signature OLED R rollable television. The device uses a 65-inch flexible OLED panel that can roll out of sight into its base, which is designed to look like a piece of furniture when…

We still have yet to see many 4800U laptops out in the wild ideally …

From the table, it is seen that the odd-numbered SKUs are Lucienne while the even number ones are Cezanne. The top-end Ryzen 7 5800U is a Zen 3 Cezanne part with eight cores and 16 threads running at 2 GHz base and a 4.4 GHz boost. The Ryzen 7 5800U features an 8-CU (512 SP)…

Leave a Reply