It’s Time to Split

Q: I have a large Excel worksheet, and I find it difficult to compare data in different parts of the worksheet. Is there a good option for achieving this goal?

A: Excel 2007 and 2010 contain two Split tools, which allow you to quickly split the Excel worksheet horizontally, vertically, or both. The Split tools make it easier to view and compare different portions of your data at the same time. To split your screen, click and drag the split tools to the desired positions. The Split tools are located in the upper right-hand corner and lower right-hand corner of the worksheet area in the scroll bars, as shown below.

You can also access this tool by selecting Split from the Window group on the View Ribbon. Many users find the click-and-drag method a little faster and easier to use than the menu method.

In the example below, the Vertical Split tool has been used to display data for the first quarters of 2009 and 2010. (Notice that the columns jump from column E to column O). Additionally, the Horizontal Split tool has been used to fix the column headings at the top of the worksheet. (Notice that the row numbers jump from row 3 to row 7.)

Note: There are several alternative strategies for viewing Excel data side by side as follows:

1. Freeze Panes. You can achieve similar results to Split by using Freeze Panes, located in the Windows group on the View Ribbon.

2. Second instance of Excel. You can also achieve similar results by launching a second instance of Excel and using it to open a second (read-only) copy of your Excel file. You can then resize both instances of Excel in side-by-side windows.

3. Formulas. For a more permanent solution, some CPAs use formulas to repeat data on a separate worksheet to produce a different view. For example, on Sheet2 they may insert formulas that refer to data in columns A, B and C next to other formulas that refer to columns M, N and O. Using this approach, the side-by-side comparison is always available without the need for the above-mentioned manual procedures.

More from the JofA:

Find us on Facebook | Follow us on Twitter