How to View All Sheets in Excel at Once (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we need to compare multiple worksheets in Excel. If we can see all the worksheets at once then it will be easier for us to compare. Excel has some useful and handy features to do that. In this article, we will learn some easy ways to view all sheets in Excel at once. Let’s see how to do that.

Here, we are going to discuss 5 easy ways to view all sheets in Excel at once. We also used some excellent examples with explanations for understanding the methods easily. So, without further delay, let’s get started.


1. Viewing Two Excel Sheets of the Same Workbook Side by Side

Suppose, we need to see two worksheets of the same Excel workbook side by side for comparison. Now, we can do this very quickly by using some features available in Excel ribbon. Follow the steps below to see the procedure.

Steps:

  • First, go to the View tab.
  • Secondly, go to the Window group and click on the New Window.

View Two Excel Sheets of Same Workbook Side by Side

  • Next, go to the View tab and then the Window group again. But this time, click on View Side by Side.

View Two Excel Sheets of Same Workbook Side by Side

  • Accordingly, select the sheet you want to compare by clicking it in each workbook window.
  • To clarify, the screenshot below displays the built-in horizontal layout.


2. Seeing Two Sheets of Different Excel Workbooks Side by Side

At times, we need to see two sheets of different Excel workbooks side by side. We can do this easily by adopting some simple steps.

Steps:

  • Firstly, open the workbooks that include the worksheets you want to compare.
  • After that, go to the View tab.
  • Now, go to the Window group and select View Side by Side.

Side by Side Seeing Two Sheets of Different Excel Workbooks

  • When there are more than two open worksheets, Excel shows the Compare Side by Side dialog box. See the screenshot below.
  • Consequently, click the workbook that includes the worksheet that you want to compare with your active worksheet in this dialog box’s Compare Side by Side with section.
  • Then click OK.

  • Finally, click the sheet you want to compare in each workbook window.

3. Using Excel Arrange All Feature to Arrange Multiple Sheets Vertically at Once

When using the tool called ‘View Side by Side,’ Excel arranges two windows in a horizontal layout. Here, we can easily change this built in configuration by using Excel Arrange All feature. We can find this feature in the View tab. Moreover, the above two methods were valid only for two worksheets. But this method is applicable both for equal to or greater than two worksheets. The steps of this method are below.

Steps:

  • To begin with, open all of your desired workbooks.
  • Subsequently, click the desired sheet tab.

Arrange Multiple Sheets Vertically Using Arrange All Feature in Excel

  • Afterward, go to the View tab and then select New Window from the Window group.

Arrange Multiple Sheets Vertically Using Arrange All Feature in Excel

Note: However, this step is applicable only if the target sheets are located in the same Excel workbook. Accordingly, for every worksheet you want to view, repeat this procedure. On the other hand, skip this step if the sheets are in different Excel files.
  • At this time, click on Arrange All under the Window group in the View tab.

Arrange Multiple Sheets Vertically Using Arrange All Feature in Excel

  • In turn, a dialog box named Arrange Windows will pop up.
  • Consequently, select Vertical from the Arrange section.
  • Finally, click OK to arrange the sheets vertically.


4. View the List of All Sheets at Once in Excel by Right-Clicking

In an Excel file, the tabs for each sheet are located at the bottom. However, not all tabs can be displayed simultaneously when a document contains a lot of sheets. For example, there are 8 worksheets in the following example, but only 4 of them are shown.

View List of All Sheets in Excel by Right-Clicking

Steps:

  • To see the list of all sheets right-click the arrow located at the left of the sheet tabs to see the entire list of worksheets.
  • For a better understanding, see the screenshot below.

  • Finally, the pop-up list named Activate shows the titles of every worksheet.
  • Besides, you can jump to any worksheet by selecting it from the list and then clicking OK.


5. Applying Excel VBA to See the List of All Sheets Tabs at Once

The programming language used by Office applications like Excel is called VBA (Visual Basic for Applications). By using Excel VBA we can see the list of all the sheets at once. In this method, we have also used colors in the sheet tab which will be visible in the list also. The steps to do so are below.

Steps:

  • First, right-click a sheet from the sheet tab section in the bottom.
  • Next, select View Code to open the VBA window.

Apply Excel VBA to See List of All Sheets Tabs at Once

  • In turn, Microsoft Visual Basic for Applications window will appear.
  • At this moment, type the below code in the Code window. See the picture below to make it clear.
  • You also need to mention the sheet name in the VBA code that you selected for inserting the code. In our case, the name of the sheet is VBA.
Sub ApplyingVBA()
Dim RowNumber As Integer
Sheets("VBA").Select
RowNumber = 4
For Each Sheet In Application.Worksheets
Range("B" & RowNumber).Value = Sheet.Name
With Range("B" & RowNumber).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = Sheet.Tab.Color
.TintAndShade = 0
.PatternTintAndShade = 0
End With
RowNumber = RowNumber + 1
Next Sheet
End Sub

Apply Excel VBA to See List of All Sheets Tabs at Once

  • After that, go to the Run tab and select RunSub/UserForm to run the VBA code.

  • After running the code, we will see the list of all the Excel worksheets in the sheet which we selected for inserting the VBA code.
  • Moreover, the cells of the sheet names will also show the specific colors of the corresponding tabs.

Read More: How to Create Different Views for Different Users in Excel


How to View All Hidden Sheets at Once in Excel

We can view all the hidden worksheets by using the Immediate Window feature of Excel VBA. The steps are below.

Steps:

  • First, right-click on any worksheet from the sheet tab section located at the bottom.
  • Now, to open the VBA window, click on View Code.

How to View All Hidden Sheets at Once in Excel

  • Hence, the Microsoft VBA window will open.
  • Next, select the View option from the menu and then click on the Immediate Window.

How to View All Hidden Sheets at Once in Excel

  • By doing so, the VBA Editor will display the Immediate window.
  • After that, copy and paste the following code into the Immediate window:
For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
  • Most importantly, you need to put the cursor where the line of the code ends.

  • Finally, press the Enter key.
  • Thus, all the hidden sheets of the Excel workbook will be visible at once.

Download Practice Workbook

Download the practice workbook from here.


Conclusion

I hope the above methods will be helpful for you to view all sheets in Excel at once. Download the practice workbook and give it a try. Let us know your feedback in the comment section.


Related Articles


<< Go Back to View in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo