You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
If your workbook contains a lot of worksheets, it can be a hassle to navigate to just the right sheet. For one thing, you often can't see all the sheet names at the bottom of the workbook:
Sure, you can move around by using the four navigation buttons to the left of the worksheet tabs, or by pressing Ctrl + Page Up or Ctrl + Page Down, but there are better ways. Read on to learn two of my favorite navigation techniques for large workbooks.
Did you know Excel has a pop-up menu that you can use for navigation? This menu is brilliant, yet it's hard to discover unless someone tells you about it. (I first learned of its existence from Jean Philippe, who is one of my favorite coworkers and a huge champion of Excel.)
To make the menu appear, right-click anywhere in the sheet navigation area, outlined in red below:
Now, if you don't see the worksheet menu, you may be right-clicking just above or below the sheet navigation buttons. You need to pretty much right-click ON the buttons themselves.
If your workbook has more than 15 sheets, click More Sheets and you'll see the full list.
This second navigation technique is more involved, but it's very effective. In a nutshell, you add a new worksheet to your workbook, list all your worksheet names in a column, and then link each name to its corresponding worksheet, kind of like a table of contents.
To do this, create a new worksheet and call it "Cover Sheet" or something similar. On this sheet, type the names of all the worksheets in your workbook. Then, select a cell with a sheet name, press Ctrl+K to open the Insert Hyperlink dialog box, click the Place in This Document button, and then link to the sheet. Lather, rinse, and repeat until you are through adding the links.
This process is quite manual, but it's okay if you have a relatively small number of worksheets. If you have a whole bunch, you may want to use a macro to generate the links automatically. A macro is a little code snippet that can help you automate boring and repetitive tasks in Excel. Don't panic at the thought of creating a macro—the post I linked to earlier comes with sample code and step-by-step instructions for adding one to your workbook. Listen, I'm no VBA whiz kid, so if I can get this to work, anyone can!
Extra credit: Add a Home button on each sheet that goes back to the cover sheet. One easy way to do this is to add a shape (Insert tab, Illustrations group), format it the way you want, select the shape, and then add a hyperlink that jumps back to the cover sheet. Here's what that might look like:
If you have other techniques to share, leave a comment. I'd love to hear them.
-- Anneliese Wirth
Try also workbook navigation addins created for easy navigation from below page.
1. Index of all Sheet Names in separate worksheet with hypeerlink.
2. Sort sheets ascending or descending,
3. Synchronize all the sheets to view same area in the worksheet.
4. Copy activesheet without truncated of cells having more then 255 characters.
5. List Directory & filenames, External link, comments, define names, shapes with hyperlink.
6. Delete Empty rows, All Empty Worksheets, remove all pagebreaks, hide sheets.
7. Display fullpath of Active Workbook in its Toolbar Button.
8.Full Screen View.
9.Can Highlights Entire Row\Columns.
10.Create\remove multiple windows.
11.Can Shows Sheet name in window caption.
12. Calculate sum with hyperlink in separate workbook for saving.
Hey everyone: Great discussion. Thanks a million for sharing your tips, ideas, and code!
I'm now working with multiple Excels... And I just hate that I can't open multiple windows of Excel, and copy paste formulas between them. So I decided to share my pain here.
Hi Mantyvdas. I'm glad you shared this here. I'm always interested in knowing where people are running into trouble. It sounds like you're working with multiple instances of Excel--is that right? Unfortunately, you can’t copy formulas between instances…they resolve into the target workbook as the value only. A workaround is to convert formulas to text by inserting an apostrophe before the = sign, copy and paste them in as text, then remove the leading apostrophe in Edit mode and press Enter.
Nate, thanks for helping out with the VBA questions!
there is an easy way...
Press Ctrl+~ (dont press shift, it's the "cigma" icon you need). it will expand entire sheet to show entire formula. then either add the "apostrophes before =" , OR use the Find/Replace option to look for "=" and replace with "apostrophes =".now you are ready to paste the formula into destination sheet. I would use find/replace option again and remove the apostrophes.
hopefully it will work.
Thanks for the tip, fzz!
Thank you all for all the tips. They're nice. But really, they're all hard to use workarounds. I just don't understand that you never ever experience this difficulty yourself? And don't you want it to become very simple & easy?
In my Windows 7, I quickly dock one Excel instance to the left side (you know that Aero thing, that easily occupies half the screen), and then dock another Excel instance to the right side, and then I see both Excel files at one time, and then I expect to copy formulas with an ease. Dear Annaliese, don't you think that would be a perfect feature for Excel 2014?
And even more: sometimes I get two monitors, especially when I'm doing budgets. I fullscreen a target Excel on one monitor, and then fullscreen a source Excel on the other, and then copy things over. Oh, how I wish formulas would copy over. Try doing that in an MDI environment, resizing is overkill.
Thanks for giving us a bit more information about the scenarios that are causing you trouble. I will definitely pass on your comments to the product team.
Great . Useful tips.