A question I often get asked in courses is how to compare data that’s on different Excel sheets easily without having to constantly flick backwards and forwards or copy one of the sheets into a new file first.
Neither of these solutions are ideal and copying the sheet always holds the risk that data will not stay updated in both places.
There is a better option!! There are 2 steps to the process.
Creating a second version of the original workbook
The first step is creating a second version of the workbook. This is not a copy, even though it looks like it is which means that changes in one version are immediately reflected in the other.
1. Go to the View tab and select New Window
2. There will now be 2 versions of the file numbered 1 and 2
3. This process can be repeated for as many versions as you need.
Arranging the files
In the second step the versions of the file can now be arranged so they can be seen at the same time.
1. From the View tab select Arrange All
2. The versions can be arranged in one of the following layouts:
3. The best option will depend on the size of your screen and layout of the data.
4. Tick Windows of active workbook. This will ignore any other Excel files that you have open and will only use the versions of this file
5. At this point both versions will show the same sheet and the versions will be numbered 1 and 2.
6. On one of the versions change the sheet to the one you want to compare
This will work with more than 2 versions if required but consider how many can be sensibly viewed on the screen at the same time.
This can now be used to compare the sheets, copy data from one sheet to another or perform calculations across sheets more easily than would otherwise be possible.
Returning to a single version
1. Close down one of the versions (it doesn’t matter which one) and you will return to a single version of the file.
2. If multiple versions have been created repeat this process until a single version remains.
NOTE: The same process can be used for multiple workbooks but New windows don’t need to be created. Instead the files to be arranged are all opened. Also, the tick needs to be removed from Windows of active workbook.
If you’ve enjoyed reading about how to view multiple sheets in an Excel workbook, there’s some other blogs below that you might find useful: