Happy Friday Excellers, time for more #FormulaFriday Excel Fun. Today I want to show you how the 3 fastest ways to apply a formula to an entire column or row.
I am always looking for ways to be be more efficient when working in Excel especially when it comes to formulas. Sometimes you may have to apply a formula to hundreds or even thousands of Columns or Rows in your Excel worksheet. So, let’s look at the 3 fastest ways to apply your Excel formula to an entire Column or Row.
Method 1. Drag The Fill Handle.
I would say that this is the most common way I have seen users apply a formula to entire column or row.
-
First type the formula in your first cell.
- Click the cell you have just typed the formula into
- The black fill handle will appear in the bottom right corner of the cell
- To fill the entire column- drag the formula down the column, as in the screen shot below. To fill the entire row then drag the formula to the far right across your rows.
- The formula =(B4*$E$1)+B4 will be applied to your entire Column C that you drag the formula to.
Method 2. Using The Excel Shortcut Keys.
This is one of my favourite ways to quickly apply a formula to an entire column or row in Excel. It is especially useful if you have hundreds or thousands, or even tens of thousands of rows to apply your formula to.
- Select the whole Column (or Row) in this case its Column C
- Type your formula. In my example it is =(B4*$E$1)+B4
- Hit CTRL and Enter together on the keyboard
- Boom. Job done.
Method 3. Using The Fill Command.
There is a really useful fill command on the Excel ribbon. This can be applied to both Columns and Rows.
- Enter your formula in your first cell of your Column or Row.
- Select your full Column or Row
- Home Tab- Editing – Fill – Fill Down ( or fill across for row fill)
⇒
Have you any quicker ways to fill a formula down a Column or Row?. Share with me in the comments below.
So, What Next? Want More Tips?
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.