Using a mouse click or a single key press from the keyboard in Excel can help in performing multiple tasks. Having a large dataset never allows you to apply a formula manually one by one to each cell. Therefore, you need to apply the formula in a way that can save time and effort. Copying a formula in Excel is the most effective way to reduce the stress of applying a formula manually.
Here you will get to know the useful hacks that can help in moving or copying a formula to a cell range. Let’s dive in to see which method suits best to you.
Copying a Formula in Excel using the Fill Handle
With the Fill Handle option, you can simply copy the formulas in a series. For this, follow the steps given below:
- Open the spreadsheet and click on the cell containing the formula to copy.
- For the cell you have selected, click on the lower right side of that cell and drag it downward. Continue dragging to the cells where you need the formula to be copied.
- Once the dragging is done, you will see all the selected cells are filled with the formula.
That’s it!
Try this method and save time.
Copying a Formula in Excel using Double-Click the Plus
In this approach, you will get to know how you can apply a formula to a larger cell range. Suppose you need to put a formula in more than 500 cells in line. Definitely, it seems a tiring thing and with the above method, you will not want to keep dragging the mouse until the last cell.
So, what would you do then?
With a double-click on the plus sign rather than dragging, you can do it easily. Put the formula in the top cell and place the mouse in the bottom right side of the cell. You will see a plus sign double-click on it.
Remember that using this option will copy the formula down as far as you have data to the left in your sheet. Even if your sheet contains headers or any other data, you will see the formula is copying continuously. On the other hand, when there is no header or data to the left, Excel will stop filling the cells.
Apply a Formula into Multiple Cells with a Single Key Stroke
Sometimes, you need to add the same formula in more than one cell, whether these cells are adjacent or non-adjacent. In such situations, you can use this trick and save time.
Choose the cell range that is needed to apply the formula. Press and hold the CTRL key when you need to select non-contiguous cells.
Now, press F2 to edit the sheet.
Enter the formula in a cell and again press CTRL + ENTER key. You will see the formula is being copied to all selected cells. You can add the values accordingly.
Copying a Formula in Excel without Formatting
With the Fill without Formatting option, you can simply copy down a formula in Excel. Using this trick can help you in keeping the current format while copying the formula. The Copy & Paste option on the other hand can handle even more burden than the pasting option. Let’s follow the steps:
- Choose the cells containing the formula.
- Press CTRL + C to copy the cell. Or else you can right-click on the cell and choose Copy option given in the context menu.
- Now, choose the cells that needed to have the formula.
- Right-click these selected cells and choose Formulas from the Paste Options.
- Press the arrow key given under the Paste option from the ribbon. It will help you open up the menu for further paste options. For instance, the Formulas & Number Formatting option helps in pasting only the formula as well as the number formatting including currency format, percent format, etc.
- In case, when you don’t know anything about which paste option will be effective, place the mouse over the different icons and you will see a preview of these paste options.
Final Thoughts
Each method given above is simply useful even if you are not an expert in Excel. Practice makes a man perfect and that’s why all you need is practice. Keep sharing the knowledge with others.
