For most data analysts, a common job is to create spreadsheet reporting templates that can be duplicated as worksheet tabs for the purpose of segmenting the sample data by various attributes or time series groupings. Templating is a certainly a best practice in spreadsheet reporting, but the main drawback to this approach is that you are required to apply any change to the template to all of the duplicated tabs in order to keep the reporting format consistent.
This can be a tedious task since you will have to go one by one making the same change to each worksheet tab, but there is a little known feature in Microsoft Excel that allows you to make a bulk update across the tabs, rather than one by one.
In order to make a bulk update, follow the steps below:
- Select the cell or cell range that you would like updated.
- With that cell or range selected, click and hold
Ctrl(Windows). While held down, select each tab that you want updated for that cell range. Release once the selections are made. Excel will recognize the selection of the cell or range on each of the selected worksheet tabs.
- Update the cell or range without click or moving out of the selected area and the changes will be made across each worksheet tab.
This shortcut is an incredible time saver and one that is little known among most people using Excel.