How to Apply a Formula to Multiple Cells in Excel
Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Changing Multiple Cells at Once.
Changing Multiple Cells at Once
David has several non-adjacent cells that each contains the same formula. He needs to change the formula in all the cells to something else. He knows he can change the formula in one of the cells and then copy the formula to the other cells, but he's wondering if there is a way to change the formula in all the cells at the same time, without the need to do the copy/paste operation.
If you want to retype the formula from scratch, Excel actually provides a shortcut to do this: Ctrl+Enter. All you need to do is create a selection set of the cells you want to edit. (Selection sets are created by clicking on a single cell then, as you hold down the Ctrl key, clicking on other cells you want included in the set.) Type the formula you want to appear in each cell in the set, then press Ctrl+Enter.
If you want to change an existing formula in the cells, simply create your selection set of those cells and then use Find and Replace to search for the formula you want to change. You can easily click on Replace All in the dialog box to change all the cells at once. (Remember—this approach will only work properly if the formulas in the cells are all exactly the same.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11924) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Changing Multiple Cells at Once.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...
MORE FROM ALLEN
Moving Through a Table in a Macro
Do you need to step through a table, cell by cell, in a macro? It's easy to do using the Move method, as described in ...
Discover More
Understanding Scope for Named Ranges
When you add a named range to a worksheet, you can specify if you want that named range to apply to the workbook or only ...
Discover More
Preparing Data for Import into Access
When importing Excel information into Access, you need to be concerned with the condition of the data. Here's how to make ...
Discover More
More ExcelTips (ribbon)
Typing Check Marks into Excel
Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the ...
Discover More
Highlighting the Rows of Selected Cells
If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, ...
Discover More
Deleting Everything Except Formulas
Need to get rid of everything in a worksheet except the formulas? It's easier to make this huge change than you think it is.
Discover More
How to Apply a Formula to Multiple Cells in Excel
Source: https://excelribbon.tips.net/T011924_Changing_Multiple_Cells_at_Once.html