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

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.