Tabbing in excel sheets

Where are my worksheet tabs?

If you can’t see the worksheet tabs at the bottom of your Excel workbook, browse the table below to find the potential cause and solution.

Note: The image in this article are from Excel 2016. Your view might be slightly different if you have a different version, but the functionality is the same (unless otherwise noted).

The window sizing is keeping the tabs hidden.

If you restore multiple windows in Excel, ensure that the windows are not overlapping. Perhaps the top of an Excel window is covering the worksheet tabs of another window.

The status bar has been moved all the way up to the Formula Bar.

Tabs can also disappear if your computer screen resolution is higher than that of the person who last saved the workbook.

Try maximizing the window to reveal the tabs. Simply double-click the window title bar.

If you still don’t see the tabs, click View > Arrange All > Tiled > OK.

The Show sheet tabs setting is turned off.

First ensure that the Show sheet tabs is enabled. To do this,

For all other Excel versions, click File > Options > Advanced—in under Display options for this workbook—and then ensure that there is a check in the Show sheet tabs box.

The horizontal scroll bar obscures the tabs.

Hover the mouse pointer at the edge of the scrollbar until you see the double-headed arrow (see the figure). Click-and-drag the arrow to the right, until you see the complete tab name and any other tabs.

The worksheet itself is hidden.

To unhide a worksheet, right-click on any visible tab and then click Unhide. In the Unhide dialog box, click the sheet you want to unhide and then click OK.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник

How to Move Around and Between Worksheet Tabs in Excel

Moving to different data areas is easier than you think

What To Know

• Option 1: Move one sheet left = Ctrl+PgUp (page up) and move one sheet right = Ctrl+PgDn (page down)
• Option 2: Home tab >Find & Select under Editing. Pick Go to. and enter the cell under Reference. Press Enter.
• Option 3: Select the Name Box above Column A. Type the cell reference to jump to, and press Enter.

This article explains how to navigate Excel quickly between worksheet tabs and around large worksheets using various shortcuts and cell references. Some methods—such as the Go To command—can be accessed using keyboard shortcut key combinations. These instructions should work for all versions of Excel for Windows. Any minor differences between versions are called out in the steps.

Use Shortcut Keys to Change Worksheets in Excel

Switching between worksheets in an Excel workbook is done easily enough by clicking on the tabs at the bottom of the worksheets, but it is the slow way of doing it — at least it is in the opinion of those who prefer to use keyboard shortcuts or shortcut keys whenever possible.

To move to the right, press and hold down the Ctrl key on the keyboard.

Press and release the PgDn key on the keyboard.

To move another sheet to the right press and release the PgDn key a second time.

To move to the left, press and hold down the Ctrl key on the keyboard.

Press and release the PgUp key on the keyboard.

To move another sheet to the left press and release the PgUp key a second time.

Using ‘Go To’ to Move Around Excel Worksheets

The Go To command in Excel can be used to quickly navigate to different spreadsheet cells in a worksheet. Although using Go To is not that useful for worksheets containing only a few columns and rows, for larger worksheets it is another way of jumping from one area of your worksheet to another.

Select the Home tab at the top of Excel. If you don’t see it, select the Edit menu.

Choose Find & Select from the Editing group. Or, if you used the Edit menu, choose Find.

Choose the Go To. option.

Type in the destination cell reference in the Reference line at the bottom of the dialog box.

Click OK or press the Enter key on the keyboard. The result is that the active cell highlight jumps to the cell reference that was entered in the dialog box.

Other Ways to ‘Go To’

The Go To command can also be activated with these keyboard shortcuts:

• By pressing the F5 key on the keyboard.
• By pressing the Ctrl+G keys in combination on the keyboard.

Storing Cell References for Reuse

An additional feature that Go To has is that it stores previously entered cell references in the large Go To window at the top of the dialog box for later reference. If you are jumping back and forth between two or more areas of a worksheet, Go To can save you even more time by reusing the cell references stored in the dialog box.

Cell references are stored in the dialog box as long as a workbook remains open. Once it is closed, the stored list of cell references in the Go To dialog box is deleted.

Navigating Between Worksheets With ‘Go To’

Go To can also be used to navigate to different worksheets in the same workbook by entering the sheet name along with the cell reference.

Use the exclamation point (!), located above the number 1 on the keyboard, as a separator between the worksheet name and the cell reference because spaces are not permitted.

For example, to move from Sheet 1 to cell HQ567 on Sheet 3, enter Sheet3!HQ567 in the reference line of the Go To dialog box and press the Enter key. If the sheet name has one or more spaces, use quotes, like this: ‘My Other Sheet’!B12 to move between worksheets.

Using the Name Box to Move Around Excel Worksheets

The Name Box is located above column A in an Excel worksheet and it can be used to navigate to different areas of that worksheet using cell references.

As with the Go To command, the Name Box might not be helpful in worksheets that contain only a few columns and rows of data, but for larger worksheets, or for those with separate data areas using the Name Box to easily jump from one location to the next can be a very efficient way to work.

Unfortunately, there is no way to access the Name Box using the keyboard without creating a VBA macro. Normal operation requires clicking on the Name Box with the mouse.

The Active Cell Reference in the Name Box

Typically, the Name Box displays the cell reference or named range for the current or active cell — the cell in the current worksheet that is outlined by a border or box.

Entering a new cell reference or range name in the Name Box and pressing the Enter key changes the active cell and shifts the black box, and what is visible on the screen, to the new location.

Click on the Name Box above column A to highlight the cell reference of the active cell.

Type in the cell reference of the desired destination.

Press the Enter key on the keyboard.

The black box that surrounds the active cell should jump to the newly selected active cell.

Navigating Between Worksheets With the Name Box

Like Go To, the Name Box can also be used to navigate to different worksheets in the same workbook by entering the sheet name along with the cell reference.

To move to the next worksheet, press Control+Page Down or Option+Right arrow. To move to the previous sheet, press Control+Page Down or Option+Left arrow.

There are a couple of ways to unhide worksheet tabs, but the easiest method is to right-click any tab at the bottom of the workbook > select Unhide > select the worksheet to unhide.

To group worksheets in Excel, press and hold Ctrl > select each worksheet tab to group. If you want to group all the worksheets in a workbook, right-click any worksheet tab and choose Select All Sheets. It’s best to group worksheets that have the same formatting.

Источник

Show Excel sheet tab in ≥ 2 rows

Some days I’m working with extremely large Excel files. They have too many sheets that can’t be shown in one sheet tab. (see image)

So, I’m wondering whether or not we can show Excel sheet on more than 1 row. Many tries with Google don’t help much.

Many tries but I still can’t make Excel show the sheet bar the way I want (≥ 2 rows), so I’m working with some VBA Scripts kopischke suggested.

FYI, right click on | > >| button on the bottom-right corner show you a list of up to 15 sheets. For more, click on View more in the list appeared.

4 Answers 4

Another work around that might not be immediately obvious is that you can right click on the little arrows in the bottom left corner of the window that you use to scroll left and right on the sheets. Doing so opens a vertical list sheets with the option to display more.

Microsoft Excel 2010:

Microsoft Excel 2013:

Excel will only display one row of sheet tabs, I’m afraid. If space runs scarce, you have the following options to display more:

• resize the tab area (by dragging the handle separating it from the horizontal scrollbar), and / or
• rename your sheets to have a shorter name, so that more sheets show (by removing the “Sheet” part, say, making the tab names “1”, “2” etc.), and / or
• hide sheets (right click on their tab to get the option; right clicking on a visible tab will get you an option to see all hidden sheets and show them again) if there are some you do not need.

Finally, there are VBA scripts out there claiming to build a menu of all sheets in the workbook, which might solve your problem (untested).

Excel doesn’t natively support multi-row sheet tabs. There are other ways to view more sheets, as outlined in other answers here, but no way to view multiple rows of sheet tabs.

However, there is a commercial Excel add-in called FormulaDesk Navigator that can display multiple rows of sheet tabs as well as vertical sheet tabs and other features:

Disclosure: This is my product. I don’t wish to self-promote, but it’s the only way to accomplish what the question asks.

Another workaround: use Kutools for Excel (free to try with no limitation in 30 days).

1. Click Kutools > Navigation. See screenshot:

1. Then you can see a Navigation Pane shown at the left of the sheet, click Sheets tab, and in the Workbooks list select the workbook whose sheet tabs you want to view, then in the Worksheets list, you can view all the sheet tabs.

If the sheet tabs are too many to display all in the Worksheets list, you can shorten the workbooks list with moving cursor over the bottom border of Workbooks list, and then drag it up when the cursor becomes.

The Vertical scrollbar will come out automatically when the sheet tabs can’t be shown fully.

Источник

Excel Tabs/Sheets Not Showing – How to Fix?

The default setting in Excel is to show all the tabs (also called sheets) below the working area.

But if you can’t see any tabs and are wondering where has it disappeared, worry not. There are some possible reasons that may have been the cause of missing tabs in your Excel workbook.

In this article, I will show you a couple of methods you can use to restore the missing tabs in your Excel Workbook.

If you can’t see any of the tab names, it is most likely because of a setting that needs to be changed.

And in case you can see some of the sheet tabs but not all the sheet tabs, one possible reason could be that the sheets have been hidden, and you need to unhide the sheets to make the sheet tabs visible.

Another less likely but possible reason could be that the scrollbar he’s hiding the sheet tabs (when there are more sheets that extends beyond where the scrollbar starts)

Let’s have a look at each of these scenarios.

This Tutorial Covers:

When All the Sheet Tabs are Missing

Whenever you open an Excel workbook, it must have at least one sheet tab in it (even if it’s a new blank workbook).

If you can’t see any tab, this most likely means that you need to change a setting that will enable the visibility of the tabs.

Below are the steps to restore the visibility of the tabs in Excel:

1. In the ‘Options’ dialog box that opens, click on the Advanced option

1. Scroll down to the ‘Display Options for this Workbook’ section
2. Check the ‘Show sheet tabs’ option

The above change would ensure that all the available sheet tabs in the workbook become visible (unless the user has specifically hidden some of the worksheets)

Note that this setting is workbook specific – which means that in case you enable this setting in one of the workbooks, it would only make the tabs reappear in that specific workbook

When Some of the Sheet Tabs are Missing

Sometimes, you may be able to see some of the tabs in the workbook, while some others may be missing.

In this section, I have some solutions when only some of the tabs are missing and some are visible.

Some of the Sheets are Hidden

The most likely reason that you cannot see some of the tabs in the workbook is that they have been hidden by the user.

When a worksheet is hidden in Excel, it continues to exist as a part of the Excel workbook, but you don’t see that sheet tab name along with other sheet tabs.

And this has a really simple solution – you need to unhide the sheets.

Below are the steps to unhide one or more sheets in Excel:

1. Right-click on any of the existing sheet tab name
2. Click on the Unhide option. In case there are no hidden sheets in the workbook, this option will be grayed out

1. In the Unhide dialog box, click on the sheet name you want to unhide

The above steps would unhide the selected sheet, and it would reappear as a tab in your workbook.

In case you want to unhide multiple sheets, you can select them in one go in the ‘Unhide’ dialog box. To do this, hold the Control key (or Command key if using Mac) and then click on the Sheet names that you want to unhide. This would select all the sheets on which you click and then you can unhide all these with one click.

But what if you do not see the tab name in the names listed in the Unhide dialog box?

Well, there is a way in Excel to hide a sheet in such a way that its name doesn’t show up in the Unhide dialog box.

Then how do you unhide these ‘very hidden’ sheets?

You can read my tutorial here where I show you how to unhide those sheets that have been ‘very hidden’. It’s easy and it will only take a couple of clicks.

Tabs are Hidden Because of the Scroll Bar

Another reason your tabs may be missing could be because of a large scroll bar that hides the tabs.

And it has a simple fix – resize the scroll bar to make all other tabs visible.

Below I have a screenshot of an Excel workbook where I have 8 sheets but only three sheet tabs are visible. This is because of a large scrollbar that hides those tab names.

To get the sheet tabs to reappear, click on the three dots icon on the left of the scrollbar and drag it to the right. This will minimize the scroll bar and all the sheet tabs that were earlier hidden would now become visible.

In case you have a large workbook with a lot of sheets, even if you minimize the scrollbar, some sheet tabs would still be hidden.

In such a case, you can use the navigation icons (which are at the left of the first sheet tab) to make those sheet tabs visible.

So these are some of the ways you can use to fix the issue when the sheet tabs are missing and not showing in Excel. If you don’t see any sheet tab in the workbook, it’s most likely because of the setting in the Excel Options dialog box that needs to be changed.

And in case you see some sheet tab names but some are missing, then you need to check if some of the sheets have been hidden by the user or if they are hidden because of a large scroll bar.

Other Excel tutorials you may also like:

Источник