Apply Formula to Entire Column in Excel: 3 Ways to do it

This can be done with an autofill handle in a matter of moments

Reading time icon 3 min. read


Readers help support Windows Report. We may get a commission if you buy through our links. Tooltip Icon

Read our disclosure page to find out how can you help Windows Report sustain the editorial team Read more

excel apply formula to entire column

Many of us use Excel daily, and many users are wondering if is there a way to apply the Excel formula to the entire column. In today’s guide we’re going to show you the best ways to do that, so let’s get started.

How do I apply a formula to an entire column in Excel?

1. Double-click the autofill handle

  1. Select the cell that has your formula.
  2. Locate the small green square in the bottom right corner of the cell.
  3. Double-click the green square to fill the column.
    green square excel

2. Use the Fill down option

  1. In the column that has your formula, select the formula and all other cells beneath it that you want to fill.
    cells select
  2. Next, click on the Home tab.
  3. After that, click on Fill and choose Fill down.
    fill down excel

You can also perform this just by using shortcuts:

  1. Select the cell that has your formula.
  2. Press Ctrl + Shift + Down Arrow to select the entire column. Alternatively, use Ctrl + Shift + End to select all cells in the column that have the necessary data for the formula available.
  3. Lastly, use Ctrl + D to fill the data down.

3. Convert your table to Excel Table

  1. Select all cells that you want to use as a table.
  2. Now go to Home and choose Format as Table.
    format as table
  3. Select the desired style.
  4. Open Excel options.
  5. Go to Proofing and choose Auto Correct Options.
     autocorrect options
  6. Go to AutoFormat as You Type. Check Include new rows and columns in the table and Fill formulas in tables to create calculated columns.
    autoformat as you type

After that, all your columns will be automatically filled.

As you can see, it’s easy to apply a formula to an entire column without dragging in Excel, and in most cases, you can just double-click the autofill handle or use the keyboard shortcut.

If you want to learn more about Excel, we have a guide on how to remove encryption from Excel, as well as a great article on how to unlock grayed out menus in Excel or enable macros.

You might also want to check our guide on how to calculate standard error in Excel, so don’t miss it. Also, did you know that you can use a barcode generator in Excel? That’s right, and our latest guide covers this in detail.

We also have a new guide on how to get data from another sheet in Excel, as well as a guide on how to reference another sheet in Excel, so don’t miss them.

Our latest guide shows you how to use the Excel XLOOKUP formula with multiple criteria, so you should check it out if you want to expand your knowledge.

More about the topics: Excel

User forum

0 messages