How to quickly apply formula to an entire column or row in Excel?
Sometimes you may need to apply one same formula to an entire column or row in Excel, such as C1=A1*2, C2=A2*2, …, Cn=An*2. It will be quite tedious if you enter the formula in each cell one by one. There are some tricky ways to apply one same formula to an enter column or row quickly.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
Supposing you need to apply the formula of =(A1*3+8)/5 in the Column C, and see the following tutorials to apply same formula to entire Column C.
Dragging the Fill handle is the most common way to apply formula to an entire column or row in Excel.
- Firstly type the formula of =(A1*3+8)/5 in Cell C1;
- Then click the Cell C1, and drag the Fill Handle down to the bottom in Column C.
Then the formula of =(A1*3+8)/5 is applied in the whole Column C. If you need to apply it to the entire row, you can drag the Fill Handle to the far right.
Sometimes, the column you want to apply the formula may covers hundreds of rows, and dragging Fill handle may be not convenient. Actually you and use shortcut keyboards.
- Firstly, select the whole Column C.
- Enter the formula of =(A1*3+8)/5.
- Press the Ctrl key and Enter key together.
If you want apply the formula to an entire row, you can select the entire row first.
Actually there is a Fill command on the Microsoft Excel' Ribbon to help you apply formula to an entire column or row quickly.
- Enter the formula of =(A1*3+8)/5 in Cell C.
- Select the Column C.
- Click the Home > Fill > Down.
If you want to apply the formula to entire row, just select a row, and click Fill > Right.
All of three methods introduced above are to apply formulas to blank column or row. Sometime you may need to apply formula to an entire column or row with data. How to process? You can try the Kutools for Excel's Operation Tools.
Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
Step 1: Select the column or row that you will work with, in this case select the Column A.
Step 2: Click the Kutools > More > Operation Tools.
Step 3: In the Operation Tools dialog box, select the Custom in Operation list box, enter the (?*3+8)/5 in the blank box in the Addition section.
Step 4: Click OK or Apply. Then the whole column are filled with the formula of =(?*3+8)/5, and the ? refers to the value in corresponding cell.
Notes: 1. If you check the Create formulas option, the results will be in the form of formulas for each cell.
2. If Skip formula cells option is checked, the operation will skip and automatically ignore the formula cells in the range.
- Change values in a cell range
- Add values to multiple cells
- Divide a range of cells by a number
- Do exponential calculation to a range of cells
- Multiply a range cells by a number
- Round a range of cells
- Subtract a number from a range of cells in excel