It is not enough if one knows how to use formulae in MS Excel (For those who don’t, please do have a look at this article on How to Use Formulae in MS Excel). The formula constructed in one cell needs to be deployed across many other cells too. So, in this article, we shall explore how to copy formulae in MS Excel through each of the below listed ways.
- Paste Formulas Method
- Paste Formulas & Number Formatting Method
- Keep Source Formatting Method
Following is the dataset we would be using to copy the formula in cell V2 & deploy it across all the cells in that column using each of the above methods.
Method 1 – Paste Formulas Method:
This method would help us only to copy the formula & transfer it to the desired cell through pasting. It neither takes into account the format in which the numbers are given in the cell such as the comma separator or the number of decimal places & other similar attributes, nor the format of the cell such as the colour fill of the cell or the font style (Bold, Italic, Font type & other similar attributes).
So, the prime purpose of this method is when the user needs to transfer only the formula alone to the desired location.
Initially, one needs to copy the cell containing the formula so that it gets highlighted with dotted lines moving around the cell as shown below.
Then, all the empty cells below to which the formula is to be transferred are selected.
Now, navigate to the Paste drop down located within the Home Tab as shown below.
Once done, select the Paste Formulas option as shown in the below image.
The results of the formula alone get displayed across the cells in which the formula was pasted as shown in the above image.
Method 2 – Paste Formulas & Number Formatting Method:
This method goes one step further in which the number formatting also gets transferred to the cells into which the formula is pasted. But, the privilege this method provides stops at just that & one cannot transfer the cell formatting to the destination cells using it.
Copying & selecting the range for pasting the copied formula remains the same as seen in the above method. Only the type of paste option to be chosen varies here.
After clicking on the Paste option in the Home tab, one needs to choose the Paste Formulas & Number Formatting option as shown in the below image & the results of the formula will be displayed in the destination cells with all the features of number formatting available in the source cell.
Method 3 – Keep Source Formatting Method:
For the times when we need to replicate the same formatting of both the cells & also that of the numbers within it, this method comes as a SAVIOUR!
It transfers all the attributes of cell formatting and number formatting across the range of cells in which the copied formula is pasted.
The initial steps of copying & selecting the cells for pasting the copied formula remain the same here too. The difference lies in choosing the option from the Paste dropdown within the Home tab as shown below.
Once that option is selected, the cells get pasted with the results of the formula replicating the formatting of the source cell from which the formula was copied as shown in the above image.
Conclusion:
Thus, we have reached the end of this article. QuickExcel has numerous other articles, which would be of great help to you when it comes to understanding the nuances of working with MS Excel. Cheers!