Navigation Pane – List Sheets, Workbooks, Columns, Names and insert auto text in Excel
Kutools for Excel
If there are multiple opened workbooks which includes dozens of worksheets, to switch between the workbooks and find the specific worksheet will spend much time. With the help of Kutools for Excel' Navigation Pane utility, you can quickly and conveniently view all the opened workbooks and their worksheets in the navigation pane at a glance. In other hand, the range names of active workbook and column titles of active worksheet also can be listed in the pane. At the same time, Navigation Pane provides Auto Text feature to add your custom formulas, pictures, charts, ranges and so on for reusing, as well as Advanced Find and Replace feature to find and replace values in multiple worksheets and workbooks.
Recommended Productivity Tools
See what Excel Navigation Pane can help:
- List all open workbooks: you can quickly click to navigate between all open workbooks (click for details);
- List all worksheets: almost 10 times faster to navigate between sheet tabs (click for details);
- Save and list custom formulas/pictures/charts/ranges as Auto Text entries for reusing by only one click (click for details);
- Find and replace values in multiple worksheets and multiple workbooks (click for details);
- List all columns (in active worksheet): you can quickly click on the column and go to the specific column in the active worksheet (click for details);
- List all names (in active workbook): click on the name and you will be navigated to the exact position of the name (click for details);
- List numbers of worksheets: adding status bar for numbers of all sheets, visible sheets and hidden sheets of current workbook (click for details);
- Toggle Button: toggle all of the hidden sheets/columns to be visible and invisible;
- Display hidden worksheets in Workbook & Sheet pane (click for details);
- Display worksheet number on the Status bar of Workbook & Sheet pane(click for details);
- Display hidden name ranges in Name Manger Pane (click for details);
- Preview Auto Text entries in the pane or in a floating preview pane (click for details);
- Apply the Find and Replace pane with shortcuts (click for details)
- Toggle between two latest sheets button: switch between just two sheets back and forth;
- Refresh Button: refresh all the information of the navigation pane;
- Scroll bar: makes it fluent to scroll the list from up to down; just as the way you do in browsing the web pages;
- When you switch multiple sheets by using the sheet tab of Excel, you can also stay at the specific position of that sheet in the Navigation Pane;
- Easily jump from one sheet (workbook, column or name range) to another by clicking the corresponding item in the pane;
- The more worksheets (columns) you work with, the faster the process can be when using Navigation pane.
Click Kutools >> Navigation to enable the Navigation Pane, see screenshots:
With the Workbook & Sheet tab, you can quickly navigate between workbooks and worksheets.
Please click Kutools > Navigation to enable the Navigation Pane, and then click Workbook & Sheet tab in the Navigation Pane, see screenshot:
1. Click Navigation command to enable the Navigation Pane, and click it again will close the Navigation Pane.
2. Click button to enable the Workbook & Sheet pane.
3. All opened workbooks are listed here, click to navigate between all opened workbooks. You click the button to close the workbook you need.
4. All worksheets of the current workbook are listed here, click to navigate between worksheets.
5. : Hidden worksheets toggle button: one click will toggle all hidden worksheets to be visible, and click again will toggle all hidden worksheets to be invisible.
: Toggle between two latest sheets button: switch between just two sheets back and forth.
: Refresh button: it will refresh the information of the Navigation Pane.
: Sort Ascending button: Press this button will sort all opening workbooks in ascending order, and press again to restore original sorting order.
: Sort Descending button: press this button to sort all opening workbooks in descending order, and press again to recover original sorting order.
: Utility collection for workbooks: Click this button to quick access utilities in the Workbook group on the Enterprise tab.
: Utility collection for sheets: Click this button to quick access utilities in the Worksheet group on the Enterprise tab.
6. You can filter out the worksheet by enabling the Filter function and entering the sheet name as you need.
7. Right click on any worksheet name to display the context nemu of worksheets.
8. Click button to enable the Auto Text pane.
9. The group names of auto text are listed here, you can create new group and delete any group you needn't use.
10. All of the auto text entries of selected group will be listed here.
11. : Add new auto text button: click this button to add your custom auto text and give a group name for your auto text.
: Rename group name of auto text button: rename the selected group name of the auto text.
: Export auto text button: export and save your custom auto text in a specific directory.
: Import auto text button: import your custom auto text into the auto text pane.
: Refresh button: it will refresh the information of the Auto Text Pane.
To know more details about the Auto Text function, please read this article: Easily create and insert Auto Text entry in Excel.
With this Name Manager pane, all the range names of the active workbook are listed into the pane, and you can do such operations: create new name range, edit the named range, delete name range, and so on.
12. Click button to open the Name manager pane.
13. All of the range names within the active workbook are listed here, click to navigate between names.
14. In this Edit Name section, you can rename the selected range name in the Name box to your need, and change the reference of the range name in the Refers To text box, click button to cancel the changes, and click button to save the changes.
15. You can filter out certain range name by enabling the Filter function and entering the range nameas you need.
16. : Add new name button: Click this button to add new range name as you need.
: Create names from selection button: with this feature, you can quickly create multiple range names based on row or column labels.
: Delete Name button: delete the selected range name as you want.
: Edit Name button: In this Edit Name box, you can rename and change the selected range name and cell reference of the range name.
: Name Manager: click this button to open the Name Manager dialog box.
: Refresh button: it will refresh the information of the Name ranges.
Here, I will introduce you the usage of the Create names from selection feature in the Name manager pane.
Supposing you already have existing row and column labels in your worksheet, and now you want to create range names based on these labels, the Create names from selection feature can help you to create multiple ranges names quickly and easily.
Select your range which you want to create range names, and in the Name manager pane, please click button in the top ribbon, and in the Create Names from Selection dialog box, check the options that you want to use, see screenshot:
And then click OK to close this dialog, and the range names have been created based on the row and column labels in the Name manager pane, see screenshot:
The Column List pane in the Navigation pane can list the column name and titles of the current worksheet.
|17. : Column List tab: click this button to enable the Column List pane.|
|18. List all column titles of current worksheet here, click on the column list will navigate between columns.|
: Hidden columns toggle button: one click will toggle all hidden columns to be visible, and click again will toggle all hidden columns to be invisible.
: Refresh button: it will refresh the information of the Column List pane.
20. : Advanced Find and Replace Tab: click this button to enable the Advanced Find and Replace pane.
21. Switch between Find tab and Replace tab.
22. Specify the finding or replacing scope: All workbook, Active workbook, Active sheet, and Selection.
23. Select all or unselect all opening workbooks.
24. : Delete button: Clear all searching results.
25. Find All button: click this button to find certain values in the specified searching scope.
To know more details for the feature, please view the Advanced Find & Replace uitlity.
Status bar of Kutools pane: list numbers of all sheets, visible sheets and hidden sheets of current workbook.
The total number of all worksheets, visible sheets and hidden sheets will be displayed on the status bar of Kutools pane. See screenshot:
1. Stop automatically opening Navigation Pane when launching Excel by clicking Kutools > Navigation.
2. In the Navigation Options, you can set the following operations:
Please click Navigation Options button button to go to the Navigation Options dialog, see screenshot:
Normally, the hidden sheets are not displayed in the Workbook & Sheet pane, if you check this Show hidden sheets in the Worksheets list option in the Navigation Options dialog box, all the hidden sheets will be listed in the Workbook & Sheet pane, but they are displayed in grey fonts. If you click the line besides a hidden sheet, the hidden sheet will be unhide, and if you click the eye icon besides a visible sheet, the sheet will be hidden, see screenshot:
If you check the Show the count of sheet(s) in the sheets list box, the total number of worksheets, visible worksheets and hidden worksheets are displayed in the Status bar. See screenshot:
(3.) Using a shortcut key (Win+ A) to toggle between last two sheets:
You can quickly applying the Win + A shortcut key to toggle between the last two sheets if you check Using Win + A to switch between the last two shets option, see screenshot:
Tips: This shortcut key is not available in Win 8 and Win 10 system.
A: If you check Show preview in Auto Text list option, your custom auto text will be displayed in the Auto Text pane as following screenshot shown:
If you uncheck the Show preview in Auto Text list option, you will get the following screenshot:
B: If you check Show floating preview of Auto Text entry option, when you put the cursor at the auto text, the floating preview will appear for you to view more clearly.
A: Display name editor in the name manager option: if you check this option, the Name editor will be displayed in the Name manager pane, otherwise, it will be hidden.
B: Show hidden names in the name manager option: check this option, the hidden range names will appear in the Name manager pane.
You can apply the Advanced Find and Replace function of Kutools for Excel with shortcuts Ctrl + F or Win + W without opening the Navigation Pane. See screenshot:
Tips: The Win + W shortcut key is not available in Win 8 and Win 10 system.
You can specify your default search scope for the Advanced Find and Replace funtion of the Kutools Navigation Pane. See screenshot:
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 200 Advanced Functions for Excel 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016 and 365, as if it were Office 2000 and 2003.
The functionality described above is just one of 200 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.
To post as a guest, your comment is unpublished.· 6 days agoAdd please the ability to save the full formatting of the Charts added to the AutoText. I, for example, lost the settings of the number format in the row and color settings.
To post as a guest, your comment is unpublished.· 6 days agoThe Navigation Pane is one of the best ideas for improving user interaction with excel. Thank you very much!
It would be great, if you can add the following improvements:
a. in the list of sheets the possibility of filtering by the color of the tab (choose from the available ones);
b. In the column list in a separate column, indicate whether the filter is installed on it (yes, no), and at the bottom of the list, display the form window as in the list of names. In it, display the installed filter on the selected column. Something similar is implemented in the FilterMate add-in (https://www.excelcampus.com/filter-mate/). It is also very convenient to jump to the next filtered column by one button. I'll be glad to see you have such features, so as not to keep many add-ins in the Excel interface.
c. In the list of columns, add a search by name option.
This tool is especially relevant when using wide tables. The search will help to speed up the work.
d. In the auto-text with formulas, please make it possible to display the text of the formulas title of the auto-text when the preview is turned off. Give the opportunity to make a really convenient library of formulas. Pictures now take up a lot of space on the screen and flipping a long list of formulas is not very convenient. The instrument has great potential in this part, but you miss its possibilities.
e. In AutoText, make it possible to change the formulas of the previously saved AutoText.
To post as a guest, your comment is unpublished.· 2 years agoYes, sure. You can use the Ctrl+PageUp, and Ctrl+PageDown to navigate between sheets.
To post as a guest, your comment is unpublished.· 2 years agoIs there a quick way to navigate between the task pane and an open Excel worksheet with keyboard shortcuts? I love the optionality offered by the Navigation Pane, but I find it annoying having to use my mouse to jump to different sheets in the Navigation Pane since I don't use my mouse for anything else in Excel. If there is a keyboard shortcut for this it would make it 1000x better.