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.
Download Practice Workbook
Download the practice workbook from here.
5 Easy Ways to View All Sheets in Excel at Once
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. View Two Sheets of 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.
- First, go to the View tab.
- Secondly, go to the Window group and click on the New Window.
- Next, go to the View tab and then the Window group again. But this time, click on View 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. Side by Side Seeing Two Sheets of Different Excel Workbooks
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.
- 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.
- 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. Arrange Multiple Sheets Vertically Using Arrange All Feature
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.
- To begin with, open all of your desired workbooks.
- Subsequently, click the desired sheet tab.
- Afterward, go to the View tab and then select New Window from the Window group.
- At this time, click on Arrange All under the Window group in the View tab.
- 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 List of All Sheets 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.
- 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. Apply Excel VBA to See 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.
- First, right-click a sheet from the sheet tab section in the bottom.
- Next, select View Code to open the VBA window.
- 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
- 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.
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.
- 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.
- Hence, the Microsoft VBA window will open.
- Next, select the View option from the menu and then click on the Immediate Window.
- By doing so, the VB 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.
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. Follow our website ExcelDemy to get more articles like this.