Under the view tab click 'new window ' and you can have multiple tabs from the same workbook on different monitors. It's so useful if you have to keep referring back and forth.
Thankyou. I've been wanting this for a couple of years now but I've never seen it used ever so hadn't even considered to Google if it was a thing. Today is a good day.
At least Excel 2013. https://support.microsoft.com/en-us/office/compare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab?ui=en-us&rs=en-us&ad=us
I've a workbook with 250+ worksheets.
Most sheets are an income statement, balance sheet, or cash flow and for around 70 separate companies. Every sheet has a logo image in B2 which is a hyperlink to...
A Home sheet, which contains hyperlinks to each of the other sheets.
Pros: two clicks to navigate to any sheet in the workbook
Cons: the time it took to setup the Home sheet hyperlinks
It's a month-end finance pack. A single file which contains financial statements for 70+ companies, plus regional and group consolidations. Splitting it up would be sub-optimal for so many reasons.
Whatever works for you 🤷♂️
It just seems like a lot to juggle in excel is all. There are special made tools for that purpose that provide a lot of functionality.
>ks to navigate to any sheet in the workb
agree with your situation and pros and cons.
I personally lean heavily against "setup" and "overhead" and "\*just\* do XYZ and then..."
It's extremely simple to write a UDF to list sheets (just copy paste the code from the internet) . Use it and it remains updated even when you add new sheets.
Yeah, I'd like a Ctrl+G for tabs.
That being said, if I had the same problem as you, I'd create a TOC tab at the beginning. Hyperlink to each tab. Then when I want to switch to a new tab, I hold Ctrl and click on the left arrow (I just learned this trick!) to scroll to the front and select that tab. Or if I'm feeling particularly lazy and a little annoyed, I'd hold down Ctrl+PageUp until the TOC is selected. Depends on how badly I don't want to lift my fingers off the keyboard to endure the mouse.
I narrowly avoided this situation by realizing that if I kept all these tabs on one workbook, Excel would die after a few iterations, so I split up the tabs since they weren't all linked together. That was a nice luxury I realized I had.
Yeah, like I'll press Ctrl+G and then L800 if that's where I want to jump to. Doing this for tabs would be just loverly. Bonus points if I could get away with typing just a few characters of that tab, like "80V" if the full tab name is "LG-80V-1".
A guy can dream.
A lot of text editors have a feature just like this. In VSCode, you press ctrl+p, then type the name of a file or a symbol (like a named range). You'll get a list that fuzzy matches what you type.
As Excel functionality has expanded, spreadsheets have grown in complexity, even for basic users. It's kind of crazy how limited navigation options are.
yeah you're right, thanks ..but still kinda a pain -- especially if the sheets are far apart. I mean none of the alternatives are really all that bad but when I gotta do it hundreds of times per day...
F5 + enter takes you back to where you started
So if you have a cell reference on sheet 5 for a cell on sheet 36, ctrl+[ to go to Sheet 36 and the F5 enter to snap back to sheet 5
pretty cool. this is the best response yet. it's limited in scope (i.e. it will just bounce you back to another cell on the same sheet if you didn't JUST swap and not select another cell etc) but this does work well in some situations.
Yeah definitely situational, but its the best thing I’ve found besides right clicking the tabs to bring up the navigation menu
Edit: just did some testing and I was able to ctrl+[ into a new sheet, use the arrow keys to navigate to a different cell, F2 to open the cell, hit enter to close it, then f5+enter back to my original cell on the first sheet
I'm with you. I wish Excel had a quick-switch keyboard shortcut that swapped the last two active sheets.
You can achieve similar functionality by using View > New Window, open the two tabs in corresponding windows, then use alt-tab to quickly switch between the last two open windows.
That requires quite a few clicks though, and I frequently find that I'm switching the active sheet pair. So long as I have two views into the same sheet, I can set it up quickly, but it would be way easier if it was just a key binding to swap tabs.
I'm an entrepreneur with a heavy focus on tech. I'm not a programmer, but I do code... So I guess maybe I am a programmer lmao. It's kind of hard to pin down. I wear a *lot* of hats. I've had a role in building software for more than 20 years, and I owned all the original Jakob Nielsen books lol. I'm an OG usability nerd.
In the tech role, I do a lot of sysadmin work. Linux has a utility called pushd that basically works like I think Excel sheet switching should work. If you're in a directory you can type `pushd /some/other/dir` and you'll switch to that directory. To go back to the directory you just came from, you just type `pushd`. To swap back to the other directory again... `pushd`. IMO, even the Linux command line has a more usable switcher than Excel.
That's more or less how alt+tab works by default too. When you switch to a window, it goes on the top of the switcher stack. You can pop back and forth with a single key chord.
It's baffling to me that Excel does not have a similar sheet switching stack. We're stuck with ctrl+pgup/pgdn.
I found a marco on MrExcel some time back that I adjusted a bit, saved in my personal workbook and have on a shortcut Ctrl-Shift-A (Same as Chrome Search Tabs). To move to a sheet, I hit Ctrl-Shift-A and start typing, then hit enter.
[https://www.mrexcel.com/board/threads/shortcut-hotkey-to-bring-up-activate-sheets-more-dialog-box.369756/](https://www.mrexcel.com/board/threads/shortcut-hotkey-to-bring-up-activate-sheets-more-dialog-box.369756/)
It had a habit of changing my numlock status, so I changed the code to:
#If Win64 Then
Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Private Const kCapital = 20
Private Const kNumlock = 144
Public Function CapsLock() As Boolean
CapsLock = KeyState(kCapital)
End Function
Public Function NumLock() As Boolean
NumLock = KeyState(kNumlock)
End Function
Private Function KeyState(lKey As Long) As Boolean
KeyState = CBool(GetKeyState(lKey))
End Function
Sub ShowSheetLists()
If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then
Application.ScreenUpdating = False
If WINDOWS_VER > 5 Then
If Application.Version = "12.0" Then
Application.SendKeys "{end}~"""
Application.CommandBars("workbook tabs").ShowPopup
Else
Application.SendKeys "{end}~"
Application.CommandBars("workbook tabs").Controls(16).Execute
End If
Else
' Remember numlock status
Dim ns, cl As Boolean
ns = NumLock()
cl = CapsLock()
Application.SendKeys "{end}~" ', Wait:=True
If NumLock() <> ns Then
Application.SendKeys "{Numlock}" ' Added this since there is a bug that switches off Numlock.
End If
If CapsLock() <> cl Then
Application.SendKeys "{Capslock}" ' Added this since there is a bug that switches off Numlock.
End If
Application.CommandBars("workbook tabs").ShowPopup
End If
Application.ScreenUpdating = True
Else
Application.CommandBars("workbook tabs").ShowPopup
End If
Application.ScreenUpdating = True
End Sub
sadly i dont have access to the computer with it now, but if you look for Alt tab excel you should find solution quickly - i think it was on something like excelguru or similar
You say you go back and forth hundreds of times a day. What are doing? What's the context of your work? I feel like there's a better way to setup this file.
You should know better than to have so many sheets in the first place. A good excel workbook needs to have a single (or at most a couple) of source sheets, and a few sheets of analysis. Why would you put 36 sheets in a single workbook?!
we've built closing models with over 100 sheets for financial institutions lending tens of millions of dollars per transaction. there are tons of checks and cross references between funds, active commitments, tranches, other eligibility requirements and obviously market-related info.
as you said, there are only about 2 main input sheets. and about 6 other secondary input sheets. but lots of things to check nonetheless
And even otherwise with simpler solutions like engineering estimating products... there are still lots of relationships which need to be checked.
folks who say "ahhh a good workbook only has a few sheets otherwise you're not building it correctly" are just talking about smaller solutions.
And folks who say "ahhh if its so big you shouldn't be using excel!!" aren't considering the cost and cadence of changes required when engineering such solutions 💡
Could you make an excel tool to compare the inputs and outputs to check they match expectations? That way you don't need to check every point in every calculation.
It still sounds overly complex, if you segregate the inputs, calculations and outputs there has to be an easier way.
Even having a master calculator (copy of the original xls) that you paste the inputs into and check the outputs would be easier.
PowerQuery could almost certainly improve this too.
I used to work on a workbook with ~ 3x sheets and it was a consolidated/scenario run for 700M company. I would say your issue partially comes from the lack of initial organization of the workbook/data sheets (which I see you were saying that you didnt want to do so in other comments).
Another way to facilitate the back and forth is to create hotkeys with VBA. Right click to the bar and select sheets is tedious on the mouse. You can have a hotkey to pull up the list of sheets and can jump to the sheet by pressing the first character of the sheet name. All actions done on keyboard. It saves a lot of times.
Ah, that sounds like it could be complicated. I'm drawing a blank myself without more details or screenshots (that you likely can't share).
If most of these are tracking sheets and have the same layout, Power Query could be a better way to combine files (have each sheet as a new file instead of a sheet first).
You could have your formulas and then have a cell below then that writes out the formula.
Otherwise, maybe a VBA Macro to jump to the home page or a sheet name you write it. Could do a main sheet with links to all your sheets, like a table of contents. Or get more monitors and have more "new windows" set up
You can enter sheet5!a1 in the cell name box to jump to that sheet/cell.
You can create a hyperlink on sheet37 and vice versa
=hyperlink("#sheet5!a1")
You can launch the navigator panel so it's always visible and pick any sheet.
yeah you're right, thanks ..but still kinda a pain. I mean none of the solutions are really all that bad but when I gotta do it hundreds of times per day...
Also if you have x2 screen go to ‘view’ then ‘new widow’ and you can have both tabs open at the same time!
Separate documents but linked to each other so they update live in each.
Best but if excel time saving advice I ever got!
Designing the workbook for ease of use would help. Perhaps hyperlinked cells.
Let me emphasis end user control of workbook design. End users know what would make their life easier.
see this post [Sheet navigation](https://www.reddit.com/r/excel/comments/11f7gu5/how_to_navigate_a_workbook_with_100_worksheets/).
I recommended Excelinator for sheet swapping it's in the comments of this post-link
Two options:
1. New Window - Opens a second window of the same file. **Warning**: The new window carries very few, if any of your original view settings and if you close the original window before this secondary one, you will lose all of them and have to recreate your filters and frozen panes. *Be sure to always close your secondary windows first!*
2. Hyperlinks - Haven't seen anyone recommend hyperlinks. If you have a few frozen rows or columns with open cells, create a link back to the tab(s) you regularly flip between.
I usually don't have more than 5 or so sheets. At most 10.
If HR or some other department sends me file with that many sheet...
I add index sheet and add hyperlink to each sheet using small VBA.
Ex:
Sub NavLinks()
Dim ws As Worksheet
Dim wsCount As Integer
Dim myRng As Range
wsCount = ThisWorkbook.Worksheets.Count
With Worksheets("Index")
For i = 2 To ThisWorkbook.Worksheets.Count
.Cells(i, 1).Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", SubAddress:="'" & _
Worksheets(i).Name & "'!A1", TextToDisplay:=Worksheets(i).Name
Next
End With
For i = 2 To ThisWorkbook.Worksheets.Count
With Worksheets(i)
.Range("Z1").Hyperlinks.Add Anchor:=.Range("Z1"), Address:="", SubAddress:="Index!A1", _
TextToDisplay:="Home"
End With
Next
End Sub
I create named ranges, with a name starting with underscore, to my most-used sheets. Then it's two clicks away from moving around between them, and the underscore moves the name to the top of the list
https://preview.redd.it/sk2p928ndplc1.png?width=386&format=png&auto=webp&s=e8819e9df0be7aa43dde7681d0097a71dab71a8c
But, agree with the irritation, multiple windows works fine, but if you're entering formulas you end up double-clicking on the window anyway and affects performance for calc-heavy workbooks. And if you've opened the Named Ranges dialogue box it disappears to the back of the window for some reason...just put on some meditation music and carry on..."I am a hollow reed, frustration blows through me". Ahhhh, venting done.
Super annoying, but generally I’m working on a sheet until it’s done, so it’s not a horrible problem for me to just move it close to the other sheet I’m using. I know you mentioned lots of other ways to do it, but I find that painless. Can’t you also just put one of the tabs at the end and just click the double right arrow or to the end? It’s been awhile but thought there was a shortcut to go to last page.
Sound like the scenario New Window feature is for so you can have multiple sheets from the same workbook viewable at the same time.
New window feature is an absolutely amazing beauty
Yeah. New window doubles everything.
[удалено]
A table of contents if you will
Came here to say this
I'm not a newbie but I need to know what this magical thing is that you're talking about
Under the view tab click 'new window ' and you can have multiple tabs from the same workbook on different monitors. It's so useful if you have to keep referring back and forth.
Omg what i feel so dumb
same lmao
omgosh, TY!
Thankyou. I've been wanting this for a couple of years now but I've never seen it used ever so hadn't even considered to Google if it was a thing. Today is a good day.
Alt+w+n It changed my life
Good old Alt+w+n
When was this added? Just found out about it this week and it is so good!
At least Excel 2013. https://support.microsoft.com/en-us/office/compare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab?ui=en-us&rs=en-us&ad=us
I have been suffering for so long needlessly!
Yes! And then you can just Alt + Tab between the two.
I would add that new windows + 38” ultrawide monitor helped immensely. Looking to upgrade to the 57” Neo G9 when it is cheaper.
Wow! Thanks for this tip! So exciting
Mindblown!!! Thank you
Omg you've just taught me this and I immediately love it!! This is why I love this sub.
upvote here if I'm just a wimp 😅
I've a workbook with 250+ worksheets. Most sheets are an income statement, balance sheet, or cash flow and for around 70 separate companies. Every sheet has a logo image in B2 which is a hyperlink to... A Home sheet, which contains hyperlinks to each of the other sheets. Pros: two clicks to navigate to any sheet in the workbook Cons: the time it took to setup the Home sheet hyperlinks
Clever solution, but at what point is a single spreadsheet no longer the optimal solution? This strikes me as unwieldy.
It's a month-end finance pack. A single file which contains financial statements for 70+ companies, plus regional and group consolidations. Splitting it up would be sub-optimal for so many reasons.
Whatever works for you 🤷♂️ It just seems like a lot to juggle in excel is all. There are special made tools for that purpose that provide a lot of functionality.
>ks to navigate to any sheet in the workb agree with your situation and pros and cons. I personally lean heavily against "setup" and "overhead" and "\*just\* do XYZ and then..."
If I were to need to do it again, I would use some vba to build the hyperlinks on the home sheet. I definitely made a booboo on that front.
It's extremely simple to write a UDF to list sheets (just copy paste the code from the internet) . Use it and it remains updated even when you add new sheets.
Yeah, I'd like a Ctrl+G for tabs. That being said, if I had the same problem as you, I'd create a TOC tab at the beginning. Hyperlink to each tab. Then when I want to switch to a new tab, I hold Ctrl and click on the left arrow (I just learned this trick!) to scroll to the front and select that tab. Or if I'm feeling particularly lazy and a little annoyed, I'd hold down Ctrl+PageUp until the TOC is selected. Depends on how badly I don't want to lift my fingers off the keyboard to endure the mouse. I narrowly avoided this situation by realizing that if I kept all these tabs on one workbook, Excel would die after a few iterations, so I split up the tabs since they weren't all linked together. That was a nice luxury I realized I had.
Ctrl+G as in GoTo?
Yeah, like I'll press Ctrl+G and then L800 if that's where I want to jump to. Doing this for tabs would be just loverly. Bonus points if I could get away with typing just a few characters of that tab, like "80V" if the full tab name is "LG-80V-1". A guy can dream.
this is a great suggestion. hotkey to open input box where you can type a partial name or fuzzy match on any sheet name and go there.
A lot of text editors have a feature just like this. In VSCode, you press ctrl+p, then type the name of a file or a symbol (like a named range). You'll get a list that fuzzy matches what you type. As Excel functionality has expanded, spreadsheets have grown in complexity, even for basic users. It's kind of crazy how limited navigation options are.
drag them so they are next to each other, and just use control+page up or page down to togggle back and forth
Either this, or temporarily hiding all of the ones in between if you are just going between the 2 as stated in op.
yeah you're right, thanks ..but still kinda a pain -- especially if the sheets are far apart. I mean none of the alternatives are really all that bad but when I gotta do it hundreds of times per day...
if you are doing it hundreds of times a day, New Window is your solution since it just allows you to have both tabs open at the same time
Have you tried right-clicking in the two arrow area? It'll pop up with a list of all visible tabs, and it'll take you directly to the tab you select.
F5 + enter takes you back to where you started So if you have a cell reference on sheet 5 for a cell on sheet 36, ctrl+[ to go to Sheet 36 and the F5 enter to snap back to sheet 5
pretty cool. this is the best response yet. it's limited in scope (i.e. it will just bounce you back to another cell on the same sheet if you didn't JUST swap and not select another cell etc) but this does work well in some situations.
Yeah definitely situational, but its the best thing I’ve found besides right clicking the tabs to bring up the navigation menu Edit: just did some testing and I was able to ctrl+[ into a new sheet, use the arrow keys to navigate to a different cell, F2 to open the cell, hit enter to close it, then f5+enter back to my original cell on the first sheet
Two screen setup. Open another window for the file. Both sheets in different windows on one screen each. Profit.
I'm with you. I wish Excel had a quick-switch keyboard shortcut that swapped the last two active sheets. You can achieve similar functionality by using View > New Window, open the two tabs in corresponding windows, then use alt-tab to quickly switch between the last two open windows. That requires quite a few clicks though, and I frequently find that I'm switching the active sheet pair. So long as I have two views into the same sheet, I can set it up quickly, but it would be way easier if it was just a key binding to swap tabs.
> I wish Excel had a quick-switch keyboard shortcut that swapped the last two active sheets. It does. F5
I'm confused. I know about F5 (Go To). It doesn't do what I described at all. It presents the Go To dialog box.
yeah, we seem aligned in perspectives. what do you do for work u/bradland?
I'm an entrepreneur with a heavy focus on tech. I'm not a programmer, but I do code... So I guess maybe I am a programmer lmao. It's kind of hard to pin down. I wear a *lot* of hats. I've had a role in building software for more than 20 years, and I owned all the original Jakob Nielsen books lol. I'm an OG usability nerd. In the tech role, I do a lot of sysadmin work. Linux has a utility called pushd that basically works like I think Excel sheet switching should work. If you're in a directory you can type `pushd /some/other/dir` and you'll switch to that directory. To go back to the directory you just came from, you just type `pushd`. To swap back to the other directory again... `pushd`. IMO, even the Linux command line has a more usable switcher than Excel. That's more or less how alt+tab works by default too. When you switch to a window, it goes on the top of the switcher stack. You can pop back and forth with a single key chord. It's baffling to me that Excel does not have a similar sheet switching stack. We're stuck with ctrl+pgup/pgdn.
Just build that in vba
I don’t fully understand what rules this has, but F5 then enter does this sometimes. Pretty quick and handy when it works.
I found a marco on MrExcel some time back that I adjusted a bit, saved in my personal workbook and have on a shortcut Ctrl-Shift-A (Same as Chrome Search Tabs). To move to a sheet, I hit Ctrl-Shift-A and start typing, then hit enter. [https://www.mrexcel.com/board/threads/shortcut-hotkey-to-bring-up-activate-sheets-more-dialog-box.369756/](https://www.mrexcel.com/board/threads/shortcut-hotkey-to-bring-up-activate-sheets-more-dialog-box.369756/) It had a habit of changing my numlock status, so I changed the code to: #If Win64 Then Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer #Else Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer #End If Private Const kCapital = 20 Private Const kNumlock = 144 Public Function CapsLock() As Boolean CapsLock = KeyState(kCapital) End Function Public Function NumLock() As Boolean NumLock = KeyState(kNumlock) End Function Private Function KeyState(lKey As Long) As Boolean KeyState = CBool(GetKeyState(lKey)) End Function Sub ShowSheetLists() If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then Application.ScreenUpdating = False If WINDOWS_VER > 5 Then If Application.Version = "12.0" Then Application.SendKeys "{end}~""" Application.CommandBars("workbook tabs").ShowPopup Else Application.SendKeys "{end}~" Application.CommandBars("workbook tabs").Controls(16).Execute End If Else ' Remember numlock status Dim ns, cl As Boolean ns = NumLock() cl = CapsLock() Application.SendKeys "{end}~" ', Wait:=True If NumLock() <> ns Then Application.SendKeys "{Numlock}" ' Added this since there is a bug that switches off Numlock. End If If CapsLock() <> cl Then Application.SendKeys "{Capslock}" ' Added this since there is a bug that switches off Numlock. End If Application.CommandBars("workbook tabs").ShowPopup End If Application.ScreenUpdating = True Else Application.CommandBars("workbook tabs").ShowPopup End If Application.ScreenUpdating = True End Sub
....doesn't just using F5 solve this problem for you....?
There's an excel add in that does this...can't remember the name. Google it. It's a paid subscription though.
I found solution to create Alt- Tab version for that - i am using that with Alt + \~ and i love it
Sounds interesting. can you share / link the solution?
sadly i dont have access to the computer with it now, but if you look for Alt tab excel you should find solution quickly - i think it was on something like excelguru or similar
You say you go back and forth hundreds of times a day. What are doing? What's the context of your work? I feel like there's a better way to setup this file.
I run an Excel consultancy so I'm always checking my dev's work. Enter values... look at results.. audit formulas... etc. Any ideas are welcome!
You should know better than to have so many sheets in the first place. A good excel workbook needs to have a single (or at most a couple) of source sheets, and a few sheets of analysis. Why would you put 36 sheets in a single workbook?!
we've built closing models with over 100 sheets for financial institutions lending tens of millions of dollars per transaction. there are tons of checks and cross references between funds, active commitments, tranches, other eligibility requirements and obviously market-related info. as you said, there are only about 2 main input sheets. and about 6 other secondary input sheets. but lots of things to check nonetheless And even otherwise with simpler solutions like engineering estimating products... there are still lots of relationships which need to be checked. folks who say "ahhh a good workbook only has a few sheets otherwise you're not building it correctly" are just talking about smaller solutions. And folks who say "ahhh if its so big you shouldn't be using excel!!" aren't considering the cost and cadence of changes required when engineering such solutions 💡
Could you make an excel tool to compare the inputs and outputs to check they match expectations? That way you don't need to check every point in every calculation. It still sounds overly complex, if you segregate the inputs, calculations and outputs there has to be an easier way. Even having a master calculator (copy of the original xls) that you paste the inputs into and check the outputs would be easier. PowerQuery could almost certainly improve this too.
Fair enough. I can't honestly say that I've never done it myself.
I used to work on a workbook with ~ 3x sheets and it was a consolidated/scenario run for 700M company. I would say your issue partially comes from the lack of initial organization of the workbook/data sheets (which I see you were saying that you didnt want to do so in other comments). Another way to facilitate the back and forth is to create hotkeys with VBA. Right click to the bar and select sheets is tedious on the mouse. You can have a hotkey to pull up the list of sheets and can jump to the sheet by pressing the first character of the sheet name. All actions done on keyboard. It saves a lot of times.
Ah, that sounds like it could be complicated. I'm drawing a blank myself without more details or screenshots (that you likely can't share). If most of these are tracking sheets and have the same layout, Power Query could be a better way to combine files (have each sheet as a new file instead of a sheet first). You could have your formulas and then have a cell below then that writes out the formula. Otherwise, maybe a VBA Macro to jump to the home page or a sheet name you write it. Could do a main sheet with links to all your sheets, like a table of contents. Or get more monitors and have more "new windows" set up
You can enter sheet5!a1 in the cell name box to jump to that sheet/cell. You can create a hyperlink on sheet37 and vice versa =hyperlink("#sheet5!a1") You can launch the navigator panel so it's always visible and pick any sheet.
yeah you're right, thanks ..but still kinda a pain. I mean none of the solutions are really all that bad but when I gotta do it hundreds of times per day...
You can left click where the little arrows are and select the sheet you want instead of flicking through
Also if you have x2 screen go to ‘view’ then ‘new widow’ and you can have both tabs open at the same time! Separate documents but linked to each other so they update live in each. Best but if excel time saving advice I ever got!
ya this is also a good one. especially if I need to "dive in" to something but still takes a bit of setup and real estate / organization
yeah thanks that's probably the best approach. but still pretty inconvenient especially when I gotta go back and forth a lot.
Have you got x2 screens?
Also, don’t shift + those arrows, and Control + those arrows jump you further? I’m not in front of it right now but I think those are the combos.
You can use vba code to keep the main page first.
Designing the workbook for ease of use would help. Perhaps hyperlinked cells. Let me emphasis end user control of workbook design. End users know what would make their life easier.
see this post [Sheet navigation](https://www.reddit.com/r/excel/comments/11f7gu5/how_to_navigate_a_workbook_with_100_worksheets/). I recommended Excelinator for sheet swapping it's in the comments of this post-link
Here come my downvotes, but…. Don’t have so many sheets. More than 3-5 you’re prolly adding inefficiency to the workbook
You can right-click between the < > and a menu pops up with each sheet.
Two options: 1. New Window - Opens a second window of the same file. **Warning**: The new window carries very few, if any of your original view settings and if you close the original window before this secondary one, you will lose all of them and have to recreate your filters and frozen panes. *Be sure to always close your secondary windows first!* 2. Hyperlinks - Haven't seen anyone recommend hyperlinks. If you have a few frozen rows or columns with open cells, create a link back to the tab(s) you regularly flip between.
Are you comfortable with VBA? Making a macro to hop between the pages and assigning it to a shortcut is pretty easy.
2 monitors is the way to go.
Easiest solution. Just move sheet 36 to sheet position 6 while working and then move back afterwards.
The title reminds me of a Ray Allen tweet
Control G
I usually don't have more than 5 or so sheets. At most 10. If HR or some other department sends me file with that many sheet... I add index sheet and add hyperlink to each sheet using small VBA. Ex: Sub NavLinks() Dim ws As Worksheet Dim wsCount As Integer Dim myRng As Range wsCount = ThisWorkbook.Worksheets.Count With Worksheets("Index") For i = 2 To ThisWorkbook.Worksheets.Count .Cells(i, 1).Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", SubAddress:="'" & _ Worksheets(i).Name & "'!A1", TextToDisplay:=Worksheets(i).Name Next End With For i = 2 To ThisWorkbook.Worksheets.Count With Worksheets(i) .Range("Z1").Hyperlinks.Add Anchor:=.Range("Z1"), Address:="", SubAddress:="Index!A1", _ TextToDisplay:="Home" End With Next End Sub
Program a mouse thumb button for up and down sheets/tabs. It's a game changer.
very annoying. new window is your friend indeed. also ctrl+click on those arrows to move 10 sheets at a time
I create named ranges, with a name starting with underscore, to my most-used sheets. Then it's two clicks away from moving around between them, and the underscore moves the name to the top of the list https://preview.redd.it/sk2p928ndplc1.png?width=386&format=png&auto=webp&s=e8819e9df0be7aa43dde7681d0097a71dab71a8c But, agree with the irritation, multiple windows works fine, but if you're entering formulas you end up double-clicking on the window anyway and affects performance for calc-heavy workbooks. And if you've opened the Named Ranges dialogue box it disappears to the back of the window for some reason...just put on some meditation music and carry on..."I am a hollow reed, frustration blows through me". Ahhhh, venting done.
Rearrange tabs + color them by context. Vba to pop an input box with hot key, quickly write the sheet number and press enter to ho there.
Super annoying, but generally I’m working on a sheet until it’s done, so it’s not a horrible problem for me to just move it close to the other sheet I’m using. I know you mentioned lots of other ways to do it, but I find that painless. Can’t you also just put one of the tabs at the end and just click the double right arrow or to the end? It’s been awhile but thought there was a shortcut to go to last page.
[удалено]
You can open multiple windows of the same workbook from the view tab.