Excel Formula is an expression that calculates values in a cell or a range of cells. Formulas help you design good spreadsheets. The use of formulas in excel drastically minimizes errors and troubleshooting.
Once you create an effective formula for your data, you may wish to repeat the results in other cells. This can be instantly done without the need to key in the formula to each cell manually. The chances are that you have a large data set that will take a long time to work on manually.
Below, Find 5 ways to achieve the results.
The Automatic Fill
All built-in Excel formulas fill prompts users to autofill as they type.
The Fill Command.
To apply a formula on an entire, let's say, a column. (A column is the vertical alignment of cells on a spreadsheet)
1. Enter the formula in the first cell of the column, then press Enter. Highlight the entire column and then go to the Home tab and click Fill then down.
Supposing that you need to apply it on a row. (A row refers to the horizontal alignment of cells).
2. Click the Home tab, then Fill>Right.
The above can also be done by use of a keyboard shortcut as indicated below:
3. Ctrl+D allows you to copy your formula on the neighboring cells downwards (for a column).
4. Ctrl+R to copy the formula in the right direction (for a row).
Double Clicking the Fill Handle to Autofill the Formula.
1. Place the cursor on the particular cell with the formula you need to apply. Assuming that you want to fill it on the entire column automatically.
2. Double click on the bottom right corner of that cell.
That's it, and your formula is copied on the entire column.
The fill handle sometimes is hidden, and you can't just find it to display, click File>Options. Then Advanced. Check the "enable fill handle" on editing options.
Dragging the Fill Handle to Autofill Formula.
1. Place the cursor on the cell containing the formula you want to copy.
2. Click Hold, then drag the fill handle to copy the other neighboring cells' formula.
Copy and Paste Command
1. Put the formula in a cell.
2. Highlight the cell, then copy (Ctrl+c)
3. Then highlight all the cells you need to be copied.
4. Paste the formula in those cells. (Ctrl+v)
Excel Table
What if your data is in an Excel Table? Or you just converted your range to an Excel table.
1. Type your formula in a cell of your table
2. See a drop-down on display and click on it.
3. Click on the option "overwrite all cells in this column with this formula."
However, it is very important to note that Formulas won't automatically recalculate if workbook calculation is not enabled. Below is how to enable it:
1. Click File>options
2. Click Formulas
3. Choose Automatic under "workbook calculation".
Applying the same formula in multiple cells is as easy as discussed above. Therefore, no need to waste time doing it manually on all the cells on your sheet. Time taken to organize your data becomes shorter and more non-tedious.