Occasionally I have to process data from Excel spreadsheets as part of my data warehouse work & usually these files require a bit of a tidy before being processed further. If the task needs to be repeated regularly, it makes sense to implement a technical solution that can be executed every time. However, some times these are one-off files & it makes more sense to just do some manual cleansing of the data on the understanding that it’s not going to need to be repeated.
For files that contain multiple sheets of very similar data, the ability to group sheets & apply the same action across all the grouped sheets is a massive time saver. A good example of this type of file is the mid-year population estimate data from National Records of Scotland.
To group individual sheets: Click the first sheet, then hold the Ctrl button & click on each of the other sheets you want to group together.
To group multiple side-by-side sheets: Click the first sheet, then hold the Shift button & click the last sheet in the block.
To ungroup sheets: Right-click on any of the grouped sheets & click Ungroup Sheets, or click on any sheet outside the group without holding down Ctrl or Shift.
Once the sheets are grouped, there are multiple actions that can be performed on them, including the following. Only the grouped sheets (& where applicable, highlighted ranges) are affected by the actions.
- Update the contents of cells & ranges, e.g.
- Find & replace data.
- Add formulas.
- Clear contents.
- Insert & delete rows & columns.
- Add & remove page breaks, & headers & footers.
There are some notable actions that can’t be performed on grouped sheets, namely:
- Sorting.
- Removing duplicates.
- Text-to-columns.
This is by no means an exhaustive list but it covers some of the more useful ones. As yet I’ve been unable to find a list of all actions that can be performed on grouped sheets.
Be First to Comment