5 Ways to View Two Excel Sheets Simultaneously

Ever faced the challenge of working with complex Excel workbooks where you need to compare data across different sheets? It's a common scenario whether you're reconciling financial statements, tracking changes, or simply comparing datasets. Fortunately, Microsoft Excel provides several methods to view two Excel sheets side-by-side. Here, we'll explore five different ways to achieve this, ensuring you can work efficiently and with precision.

1. Using the View Side by Side Feature

Excel’s built-in View Side by Side feature is the most straightforward approach:

1. Open both Excel files you want to compare.
2. Go to the View tab on the Ribbon.
3. Click on View Side by Side. If you have only two workbooks open, they will automatically be aligned side by side.
4. Use the Synchronous Scrolling button to ensure both sheets scroll together, making it easier to compare data at the same row level.

🔍 Note: If more than two workbooks are open, you'll need to select which windows to compare from the Compare Side by Side dialog box.

2. Splitting a Single Excel Window

For comparing different sections of the same sheet or different sheets within a workbook, you can use:

• Click on the View tab.
• Select Split. This will split the window into two or four panes depending on where you click the Split buttons.
• To adjust the split, drag the divider line to your preferred position. Alternatively, you can also use the Freeze Panes to lock specific rows or columns in place while scrolling through the rest of the sheet.

By splitting your window, you can focus on different parts of your data at the same time, making comparisons within the same workbook much easier.

3. Creating a New Window

If you want to keep the context of your primary workbook while viewing another part of it, you can:

1. Navigate to the View tab.
2. Click on New Window. This opens a duplicate instance of your current workbook.
3. Now, you can arrange these windows manually or use Arrange All from the same tab to view them together.

This method is particularly useful when you need to work on different sections of a large workbook simultaneously without switching back and forth.

4. Using Power Query to Compare Sheets

Power Query isn’t just for data transformation; it’s also a powerful tool for comparing sheets:

1. Access Power Query from the Data tab.
2. Use Get Data to load both sheets you wish to compare into Power Query.
3. Merge or append the queries to see the data side by side or to compare discrepancies.

This approach provides a more in-depth analysis, showing you exactly how data differs or how it aligns across sheets.

5. VBA Macro for Custom Split Screen

For those comfortable with coding, Visual Basic for Applications (VBA) offers flexibility:

• Open the Visual Basic Editor (Alt+F11).
• Insert a new module and write a macro to manage window arrangements:

Sub CustomSplitScreen()
    Dim win1 As Window, win2 As Window
    Set win1 = ActiveWorkbook.Windows.Add
    Set win2 = ActiveWindow
    win1.WindowState = xlNormal
    win2.WindowState = xlNormal
    With Application
        .Windows.Arrange ArrangeStyle:=xlVertical, Top:=0, Bottom:=0, Width:=0, Height:=0
    End With
    win1.Visible = True
    win2.Visible = True
End Sub

With this macro, you can customize the split screen behavior to match your specific workflow needs.

Each of these methods offers unique advantages for different scenarios. Whether you're a novice or an Excel power user, understanding these techniques will boost your productivity. From the simplicity of View Side by Side to the advanced capabilities of Power Query and VBA, there's a solution for everyone to compare Excel sheets efficiently.

Lastly, it's worth noting that while these methods are powerful, they also require some practice to get the most out of them. Keep experimenting with these features, and you'll soon find yourself navigating Excel with the ease of a pro, making your data analysis tasks seamless and error-free.

What if I want to compare more than two sheets?

+

While Excel’s View Side by Side feature is primarily designed for two workbooks, you can open multiple windows (New Window) and arrange them manually using the Windows taskbar or the Arrange All option from the View tab.

Can I keep different zoom levels for each window?

+

Yes, when you use New Window or Split, each window can have its own zoom level, allowing you to zoom in on details in one while keeping the overview in another.

Is there a way to sync scrolling when using Split?

+

Unfortunately, splitting the window does not allow for synchronized scrolling between panes. You would need to use View Side by Side or Power Query for this functionality.