Moving back and forth between worksheets in a large workbook can be tedious.
Of course you can use the shortcut keys CTRL+Page Up or CTRL+Page Down to quickly move from one worksheet to another (and CTRL+Tab to move between workbooks).
However if there’s a lot of worksheets in a file the shortcut keys aren’t going to cut it, especially if you only want to view a few cells on a summary sheet while modifying formulas or entering data in one of the worksheets in the boon dogs of your workbook.
Option 1 – New Window
If you’ve got a big monitor, or even two, you can open the workbook in a New Window and then choose View Side by Side.
You’ll find New Window on the View tab of the ribbon:
Option 2 – Watch Window
The Watch Window is a toolbar that hovers above your workbook, or you can dock it if you want.
It keeps track of one or more cells and displays the following information about them:
- Workbook name
- Worksheet name
- Name of the cell if applicable
- Cell
- Value
- Formula
To set up your Watch Window:
- Go to the Formulas tab of the ribbon and click the Watch Window icon:
- Click the Add Watch button and select the cell or cells you want to watch.
- To add multiple cells at a time simply select all the cells and click Add.
- Double click a cell in the Watch Window to jump to it.
- You can display cells from external workbooks in the Watch Window, but only if the other workbook is open.
Now when you enter data or modify formulas that affect the watched cells you will see the results in your Watch Window.
Formatting the Watch Window
You can reduce the amount of space your Watch Window takes up by reducing the size of the columns or eliminating some of them altogether.
Simply hover your mouse on the right hand boundary between the headings until the double headed arrow appears, then click and drag to adjust their size. Then you can use the pull handles on the four corners of the Watch Window to make it smaller.
Moving and closing it is easy too. Click on the down arrow in the top right corner:
To dock it at the top, bottom, left or right of your workbook simply drag it towards the location you want until it inserts itself:
If you liked this tip please let me know by sharing it on Facebook, Twitter, LinkedIn or +1 on Google using the buttons below.
![Excel PivotTable Month Comparison [Video]](https://www.myonlinetraininghub.com/wp-content/uploads/2013/04/pivot_tip_mth_change_thumb-65x80.png)
Excel PivotTable Month Comparison [Video]
KIRAN.S.N.
Thanks a lot.
Shannon Runyon
thank you for the time you put into your tips – I find most very helpful or a great reminder – specifically the watch window tip – I never knew that was there and I had never noticed the icon – thanks again for your time.
Mynda Treacy
You’re welcome, Shannon 🙂
Jayson G. Stevenson
Once the cell is in the watch window, you can read some basic information like what workbook active cell is, which worksheet is it on, value in the cell, name of the cell (if you have given it already) and the formula if you have in it.
Yoko Okanuma
When I want to copy a sheet out of multiple sheets, I click one tab, and right click,then copy, but it only allows me to copy one copy. What if I want this sheet copied 20 times? How do I do this? Yoko
Carlo Estopia
Hi Yoko,
I may be wrong, but I don’t think
there’s a mechanism in Excel right now
that would copy a single sheet into
multiple copies.
VBA solutions may be possible, but
We only support purely Excel matters
as of the moment.
Cheers,
CarloE
Mark
great tip
Carlo Estopia
Hi Mark,
Thanks, on behalf of Mynda.
Cheers,
CarloE
Ruthie A. Ward
I like Mynda’s tip about keyboard shortcuts and I use the “Arrange All” option when I need to see multiple worksheets. But you can also use hyperlinks as a way to navigate between worksheets within a single workbook.
I have a workbook with over 100 worksheets. If I used my keyboard or scrolled or a new window, I’d still spend a lot of time just getting to the sheet I wanted. A directory worksheet containing a list of all the hyperlinks to every other worksheet in the workbook makes navigation a snap. The directory has a link and a description and the list can be filtered or sorted as needed. This file is one I use to collect/process data for monthly reports so I’m constantly skipping through it for updates. In the same cell, each worksheet has a link back to the directory worksheet. There are several groups of related worksheets and they also have links to each other so I’m always one click away from the worksheet I want.
I found some VBA I put in a macro to create a directory sheet for files where I hadn’t already created one. And it’s not that hard to create/maintain hyperlinks so that getting to where I want is just a click away.
Keep reading the newsletter and have a great day!
Carlo Estopia
Hi Ruthie,
Thanks for sharing.
Cheers,
CarloE
Ruthie A. Ward
You’re welcome! One extra bonus of using my hyperlink method is that the standard Back/Forward buttons function as if your file were a webpage.
dwight jowdy
Thanks Mynda
I find most of your emails very informative and this one is great. I generally have to navigate around when the cells are on the same worksheet. And I always wondered what the “watch window” was. Now I know and it will make many of the tasks I do much easier to do.
I do use the side by side when I am working with different workbooks. did not know you could do the same thing with the same work book in the same session. I always opened a new Excel session and then opened the second workbook in it. Won’t be doing that again!
One last comment, you do a great job of explaining things. Simple language and directly to the point. A very rare talent!
Mynda Treacy
🙂 Thanks, Dwight. You’ve made my day!
Franee
If you select new window, how do you get rid of it when your finish what you are doing?
Mynda Treacy
Hi Franee,
You simply close the window by clicking on the X at the top right, just as you would normally close a workbook.
Kind regards,
Mynda.
Kris
Thanks, Mynda! Watch Window is a good solution for checking what is happening on other sheets.
When mentioning “back and forth between worksheets” I thought real back and forth. For me the challenge is to modify data… in both sheets (ad-hoc copy-pasting ranges, formulas, etc).
Cheers,
Kris
Mynda Treacy
Hi Kris,
Thanks for sharing 🙂
Cheers,
Mynda.
r
Hi Mynda,
I like the watch window (in italian is “Finestra controllo celle” :-), it is especially a good tool to debugging formulas. Can add to what you said:
– You can use any expression that returns a reference (also names or formulas directly as an index or offset in these cases the references are fixed on the result of formula)
– Double-click the expression moves the active cell on the reference
regards
r
Mynda Treacy
Hi Roberto,
Thanks for your tips. Very clever 🙂
Mynda.
john bulstra
i resolve this problem by using the camera function; i make a picture of that part I need to keep an eye on, and copy the pic made with the camera in the worksheet I am working in. I can move it where ever if it is in my way. Any changes are immediately updated in the pic, as the pic is “dynamic”. In my opinion a far easier and nicer solution than the one you presented.
Mynda Treacy
Great suggestion, John. I like the camera tool too.
It’s great if you only want to watch one cell or if all the cells you want to watch are adjacent, but it can’t easily handle multiple cells from multiple places in a workbook/s all in one window. You’d need multiple camera snapshots to do the same job the Watch Window does in one. Horses for courses I suppose.
Cheers,
Mynda.