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
3 min. read
Updated on
Read our disclosure page to find out how can you help Windows Report sustain the editorial team Read more
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
- Select the cell that has your formula.
- Locate the small green square in the bottom right corner of the cell.
- Double-click the green square to fill the column.
2. Use the Fill down option
- In the column that has your formula, select the formula and all other cells beneath it that you want to fill.
- Next, click on the Home tab.
- After that, click on Fill and choose Fill down.
You can also perform this just by using shortcuts:
- Select the cell that has your formula.
- 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.
- Lastly, use Ctrl + D to fill the data down.
3. Convert your table to Excel Table
- Select all cells that you want to use as a table.
- Now go to Home and choose Format as Table.
- Select the desired style.
- Open Excel options.
- Go to Proofing and choose Auto Correct Options.
- Go to AutoFormat as You Type. Check Include new rows and columns in the table and Fill formulas in tables to create calculated columns.
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.
User forum
0 messages