Written by Allen Wyatt (last updated November 7, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Alan has a workbook that has two worksheets: "September Data" and "Overview Report." On the second worksheet he has references, within formulas, to cells on the first worksheet. When a new month comes around, Alan needs to change the name of the "September Data" worksheet to "October Data," which breaks all the formulas on the other worksheet. He wonders how he can change the formulas that reference the first worksheet so that they don't break when he changes the first worksheet's name.
First of all, it should be said that this behavior (as described) is not normal for Excel. If you are in the Overview Report worksheet and you create a formula that references a cell on the September Data worksheet, then any changes to the name of the September Data worksheet should be automatically reflected in the formulas in any other worksheets in the workbook. The only time this isn't the case is if you have a formula that uses the INDIRECT function to reference something on the worksheet, as exampled here:
=INDIRECT("'September Data'!A3")
The reason it doesn't change is because the worksheet name is contained within single quote marks (apostrophes), meaning it is viewed as a string constant that should not be changed. If you need to use INDIRECT, then place the worksheet name off in some other cell and reference that cell in the INDIRECT formula, as described in other issues of ExcelTips. You can then change the contents of the referenced cell to reflect the name of the worksheet you want to use.
If you don't want to change your formulas in this way, then you can use Find and Replace to change the references after you rename the September Data worksheet. Follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Another option that works well is to simply define named ranges in the September Data worksheet. Each cell (or range of cells) should be assigned a name that you can then use in formulas on other worksheets. Any changes to the worksheet name will not affect the use of the named ranges at all.
Finally, you could consider looking at your data layout. For instance, you might be better off to not have a worksheet named "September Data." Instead, create a worksheet named "Current Month" and reference it in your formulas. When the start of a new month rolls around, copy the data from the Current Month worksheet and archive it in a new worksheet that bears the name of the month it represents (such as "September Data"). This way you're always working with the current data and have the older months' data available for review at any time you need.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10812) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Unbreakable Formula References to Worksheets.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Excel, by default, recalculates your worksheets as you make changes in those worksheets. If you want to limit the number ...
Discover MoreExcel allows you to protect your worksheets easily, and that includes if you need to protect only a single worksheet out ...
Discover MoreIf you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments