MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using AutoSum in Tables


March 14, 2018 - by Bill Jelen

Using AutoSum in Tables

Direct from the Excel development team, Project Manager Ash Sharma shares another of his seven favorite tips about Excel. This is one that I never knew.

Say that you have a boring Excel table like this.

One row of headings and some data
One row of headings and some data

Make the data a table by selecting one cell and pressing Ctrl + T. You will get some nice formatting.

Ctrl+T marks the data as a table
Ctrl + T marks the data as a table

In the Table Tools Design tab, choose Total Row.

Add a Total in one click
Add a Total in one click

Here is the part that I never discovered. To the right of the Total is an INVISIBLE drop-down arrow. You can't see it. But when you click *where it should be*, the drop-down opens! Click here.


Click the invisible drop-down arrow.
Click the invisible drop-down arrow.

Once you click on the invisible arrow, you can change the calculation. Plus, once you've activated the arrow, it will stay visible.

f
Change the calculation
Change the calculation

This is an awesome trick that took me 10+ years to discover! I am glad that Ash called this out as one of his seven favorites.

I love to ask the Excel team for their favorite features. Each Wednesday, I will share one of their answers.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don't center-align numerical data."

Title Photo: Eder Sampaio / Unsplash


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.