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
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.