Switching between Excel sheets. Hotkeys

Often, users of a spreadsheet editor need to carry out the procedure for switching between sheets. There are a huge number of ways to implement this simple procedure. This action must be able to perform in cases where a spreadsheet document contains a large number of worksheets. Switching methods include: using special hotkey combinations, using a scroll bar, and navigating using hyperlinks. In the article, we will analyze each of the methods in detail.

First Method: Using Special Hotkeys

Hotkeys allow you to instantly implement various actions in the spreadsheet editor. To implement switching between worksheets, two combinations of hot keys are used:

• First combination: “Ctrl + Page Up”.
• Second combination: “Ctrl + Page Down”.

These two combinations provide an instant transition between worksheets of a spreadsheet document one sheet back or forward.

This method is most convenient in situations where a document book contains a small number of worksheets. It is also great for working with adjacent sheets of a spreadsheet document.

Second Method: Applying a Custom Scroll Bar

This method is advisable to use if the spreadsheet document contains a huge number of worksheets. The fact is that if there are many sheets in the file, then the use of special hot keys will take a large amount of the user’s time. Therefore, in order to significantly save time, you need to resort to using the scroll bar located at the bottom of the Excel spreadsheet editor interface. A detailed instruction for switching sheets using the scrollbar looks like this:

1. We move to the bottom of the table editor interface. We find here a special scroll bar.
2. Click on the scrollbar with the right mouse button.
3. The display showed a small list, which shows all the worksheets of the spreadsheet document.
4. We find the worksheet we need and click on it LMB.

5. Ready! We have implemented switching between worksheets of a spreadsheet document using a scroll bar.

Method Three: Using Hyperlinks in a Spreadsheet Document

This difficult method involves the creation of an auxiliary additional worksheet, which will contain a table of contents, implemented using special hyperlinks. These hyperlinks will redirect the user to the required worksheets of the spreadsheet document.

This method contains formulas for creating hyperlinks. A list of hyperlinks is created using the GET.WORKBOOK operator. Detailed instructions look like this:

1. Initially, we move to the “Name Manager”. We move to the “Formulas” subsection, find the “Defined names” block and insert a new name there, for example, “List_sheets”. In the line “Range:” enter the following formula: =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””).

2. It could also be used as a formula =GET.WORKBOOK(1), but then the names of the worksheets will also contain the name of the book (for example, [Book1.xlsb]Sheet1).
3. We delete all the data up to the outermost closing square bracket, so that in the end only the name of the worksheet “Sheet1” remains. In order not to implement this procedure every time when accessing the objects of the “List_sheets” variable using formulas, we implement this 1 time for each element.
4. As a result, the names of all worksheets of the spreadsheet document are located in the new created variable “LIST_SHEETS”. In other words, we got a special array with values. We need to extract these values.
5. To implement this procedure, you must use the special INDEX operator, which allows you to retrieve an array object by serial number. Additionally, we use an operator named STRING to create a regular numbering.

6. At the next stage, to create a more comfortable navigation, we use the HYPERLINK operator. We will implement the procedure for adding hyperlinks to the names of worksheets.

7. Ultimately, all hyperlinks will redirect to cell A1, corresponding to the name of the worksheet of the spreadsheet document.

In addition, you can create a sheet with hyperlinks using an integrated programming language VBA.

Detailed instructions look like this:

1. Press the key combination “Alt + F11”.
2. We are creating a new module.
3. Put the following code in there:

   Function SheetList(N As Integer)

   SheetList = ActiveWorkbook.Worksheets(N).Name

   End Function.

4. We return to the workspace, using the created program, we implement the creation of a list of document worksheets. To do this, as in the above example, we use the ROW operator to create a regular numbering.

5. We carry out the repetition of adding hyperlinks.

6. Ready! We have created a sheet that allows you to quickly switch between worksheets in a spreadsheet document.

Conclusion and conclusions and switching between worksheets

We found out that there are many methods that allow you to switch between worksheets in a spreadsheet document. You can implement this action using special hot keys, scroll bars, and creating hyperlinks. Hotkeys are the simplest method for switching, but they are not suitable for working with large amounts of information. If a spreadsheet document contains a huge amount of tabular data, then it is more appropriate to use the creation of hyperlinks, as well as scroll bars.