Applying an Excel formula to an entire row (or column)
There are a lot of cases where you'll need to apply the same formula to an entire column, or even a set of columns.
In the following example you see a list of employees, the number of hours they worked, and the hourly pay rate for each of those employees:
The formula for calculating the monthly salary is a simple multiplication formula, shown here:
We could retype the same formula with slight changes for each cell in column D
'=C3*B3' for cell D3
'=C4*B4' for cell D4
And so on.
If the list is long, this can take quite a while. Fortunately for us, there is a much faster way.
If we copy the formula from cell D2 to the entire D column, Excel will adjust the formula on each cell: the formula on row 3 will reference cells B3 and C3, the formula on row 4 will reference cells B4 and C4 and so on.
To copy the formula and change it automatically, do the following:
- Select cell D2
- Click on the Copy button in the Home ribbon (or press Control+C on the keyboard) to copy the cell formula
- Mark cells D3 through D11 (put the mouse on cell D3, click the left mouse button and leave it pressed, then move the mouse to cell D11 and release the mouse button)
- Click on the Paste button in the Home ribbon (or press Control+V on the keyboard) to paste the formula to the cells you selected
- And Voila , the formula is automatically applied to all the selected cells
You can actually switch to a mode where excel displays the formulas inside the cells instead of the formula values, shown here:
To do that, press Control+~ (this sign is called the tilde and it is located under the ESC key or above the TAB key. Trust me, it's there).
After you've pressed Control+~ you'll have to press Control+~ again to return to 'value viewing mode'