Controlling excel formula exists in multiple worksheets from one sheet

Question

I have multiple worksheets having same format but different data. Each has same formula at the same position in each worksheet.

For example, the cell D10 of each worksheet has one formula to calculate square of the value located in D9 (=D9^2). Say, I have 100 worksheets and need to change the formula from D9^2 to D9^3. So, I need to make this change in each worksheet. How can I maintain the formula in one sheet so that any change can be made without going to all the sheets?

Answer 1

If you wish to change a specific cell across multiple worksheets in the same workbook, you can make a '3D' selection as follows:

1. Select the sheets you wish to update by Shift or Ctrl-clicking to select the tabs you need. You can also right-click on a sheet tab and choose Select All Sheets from the context menu to select all of the sheets in the workbook. Once selected, the sheet tabs turn white.

2. On the currently visible sheet, change the cell(s) you wish (e.g D10).

3. The change is then replicated in all your selected sheets.

4. (optional) don't forget to un-select all your sheets again to avoid changing them by accident, by either clicking a single sheet behind the currently visible one or right-clicking and selecting Ungroup Sheets from the context menu.

Answer 2

Press ctrl + H and then type in D9^2 in the find what box and type D9^3 in replace with box. In the options, choose to search within workbook and look in formulas. Then click replace all.

Answer 3

Actually there is no need to change\replace the formula on regular basis, instead just write a formula that works with a Name holding the Exponent Value, then change the value in the Name cell as required.

To achieve this create a Name at Workbook level and assign the Exponential Value to it (see pictures below on how to create and update it)

To create the Name, in the Ribbon Tab “Formulas”\“Defined Names” Group, click the “Defined Name” Icon and the New Name dialog will appear:

Type kFmlExponent as Name, select Workbook as Scope, in Comments you can type a short description of the name and in Refers to select =Sheet1!$A$1, then just need to update the value in this cell.

Then replace your formula in all Worksheets for this:

=D9^kFmlExponent

This will be same as =D9^3 If you need to change the exponent to 2 then just enter 2 in the cell A1 in Sheet1