Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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: Editing the Same Cell in Multiple Sheets.
by Allen Wyatt
(last updated May 16, 2015)
It is not unusual for all the worksheets in a particular workbook to be very much the same as each other. For instance, you might have a workbook that contains your annual budget data. Each worksheet in the workbook is devoted to a different month of the year. Each worksheet contains the same rows, the same columns, and the same formulas. The only thing that may be different is the heading on each worksheet—along with the raw data for each month, of course.
If your worksheets are very similar to each other, Excel provides a very easy way to modify the contents of a particular cell on each workbook, all at the same time. Simply follow these steps:
Step 3 may sound a bit confusing, but it isn't really. If you have a range of worksheets selected, and you enter a formula in cell D4, then the same formula is entered in cell D4 on each of the selected worksheets. This is very powerful, and Excel won't notify you if you are going to overwrite an existing formula on one of the worksheets. That is why step 4—deselecting the worksheets—is so important. If you forget to do so, you can easily mess up all your worksheets without intending to do so.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10653) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Editing the Same Cell in Multiple Sheets.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want to select only the formulas in your worksheet? It's easy to do using the Go To Special dialog box.Discover More
If you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain ...Discover More
Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.