It's Time to Split

BY J. CARLTON COLLINIS

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

 

SPONSORED REPORT

Tax reform complicates year-end tax planning

Get your clients ready for tax season with these year-end tax planning strategies, which address how to make the most of recent tax law changes, such as the new deduction for qualified business income and the cap on the deductibility of state and local taxes.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.