Pinterest Pixel

Autofill Formulas in an Excel Table

John Michaloudis
One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste.
Autofill Formulas in an Excel Table | MyExcelOnline Autofill Formulas in an Excel Table | MyExcelOnline

When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column.

As you add extra rows to your Excel Table, the formula fills in to the extra rows added, so you do not need to copy and paste.

How efficient is that!

Autofill Formulas in an Excel Table | MyExcelOnline
Download workbookFormulas-Fill-Down.xlsx

This is our starting data and has been converted into an Excel Table. Our goal is to compute the percentage of costs in our sales amount then put it in the blank column.

Autofill Formulas in an Excel Table

STEP 1: Type in this formula to get the percentage of costs in sales. We are using IFERROR to account for blank values.

=IFERROR([@COSTS]/[@SALES], “”)

Autofill Formulas in an Excel Table

STEP 2: Pick on any random row and notice that the formula has been applied to that as well. This is the power of using Excel Tables and your formula has been applied to the entire column! No more copy pasting!

Autofill Formulas in an Excel Table

 

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  How to Fix Arrow Keys Not Working in Excel - 7 Methods

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...