Split Excel Sheet (Insider Tips and Tricks)

In this Excel tutorial, you will learn about each available method on how you can split Excel sheets in the easiest way. You can split your worksheet Vertically, horizontally, or even split into four panes, and you can also split the workbook into separate files using VBA macros.

We have used Microsoft 365 while writing this article. But all the methods are also applicable to the other versions of Excel as well.

Splitting an Excel sheet helps you focus on parts of the data while still seeing the bigger picture. This makes comparing info, working on different areas, and entering data easier.

Furthermore, we will provide step-by-step instructions and practical examples with proper illustrations, ensuring that you can follow along and implement these techniques.

Download Practice Workbook

How to Split Excel Sheet?

Here, we will use the View tab to split the Excel worksheet horizontally, vertically, or into four panes easily. It is the easiest way to do it.

1. How to Split Excel Worksheet Horizontally?

To split a sheet horizontally, you can follow the steps.

• Select any cell in column A to split horizontally. Here, cell A11 is selected.
• Then, from the View tab, click on Split in the Window option.

• Now, the worksheet got split horizontally along the 11th row. And, you can use the scroll bar to look for the data in the panes.

2. How to Vertically Split Excel Worksheet?

It needs the same procedure as the method before. The only difference is you have to select any cell along the first row. Where, to split vertically you need to select any cell along the first column. Here is how you can do it.

• Start with selecting cell E1 along the first row.
• Click on Split in the Window group from the View tab. 

• The worksheet has been split vertically along Column E.

3. How to Split the Worksheet into Four Panes?

It is also the same as the methods before. In this case, you have to select any cell in the dataset other than the cells in the first row or first column. We followed the steps below to split the worksheet into four panes.

• Select any cell other than the first row or column. Here, select the cell D10.
• Similarly, click the Split in Window group from the View

• Four panes in the worksheet will appear as follows. You can scroll down to look for cells or data in each pane.

4. Split a Workbook to Separate Excel Files with VBA code

In this example, we will learn how we can split a workbook into separate Excel files with VBA code. Let’s say, we have a workbook with three sheets named Horizontally, Vertically, and 4 Pane. As shown in the picture below,  we will split the workbook and create separate Excel files for each worksheet in it.

Here are the steps you can follow to split the workbook into separate Excel files.

• From the Developer tab, click on Visual Basic.

• Now, from Insert, click on Module.

Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• Then, paste the code into the Module, and click on the Green icon to run it. Or you can just press F5 to run the code.

• This will automatically split the workbook into different Excel and save it as shown in the picture below.

Read More: How to Split Screen in Excel

How to Undo Split in Excel Worksheet?

If you do not need the split sheet. Then, you can do this to undo the split.

• To remove the Split feature, from the View tab, click on This will remove any split in that active worksheet.

Read More: How to Split Excel Sheet into Multiple Files

Things to Remember

Here are some important things to remember:

• Select any cell in the first column (A) to split horizontally, and Select any cell in the first row to split vertically.
• Similarly, select any cell inside the dataset to split into four panes.
• To activate a split screen in Excel, go to the View tab on the tab, and click on the Split button. Alternatively, you can use the shortcut Alt + W + S to split the window vertically or Alt + W + V to split the window horizontally.
• When you print from a split screen, only the active pane will be printed. To print both panes, you need to activate each pane and print separately.
• The split screen settings are saved with the workbook. So, when you close and reopen the workbook, the split screen configuration will be restored

Frequently Asked Questions (FAQ)

1. How do I split an Excel sheet?

• Position the cursor in the cell where you want to split the sheet vertically or horizontally.
• Go to the “View” tab in the Excel tab.
• In the “Window” group, click on “Split” to split the sheet.
• Excel will create a split bar at the selected cell, allowing you to adjust the split by dragging the bar horizontally or vertically.\

2. What is the shortcut key to split worksheets in Excel?

You can use the shortcut Alt + W + S to split the window vertically or Alt + W + V to split the window horizontally.

Conclusion

This article covered the split in Excel with real-life datasets and proper illustration, and the easiest explanation with a step-by-step procedure. We hope you find this article useful and understand the procedure mentioned.

Yet, if you have any confusion, or find any trouble with the VBA code, please make sure to comment down.  Our team will response as soon as possible.

Split Excel Sheet: Knowledge Hub

<< Go Back to Excel Worksheets | Learn Excel