Microsoft Excel: Does this make my data look flat?

By J. Carlton Collins, CPA

Q. My client produces budgets in Excel consisting of thousands of rows of data that I import into our accounting system to prepare actual-to-budget financial reports. However, to import the data, I must first manually rearrange the 13 columns into just three. Is there an automated way to do this?

techqa1


A.
As pictured above, the data rearrangement process described in this question involves transposing and moving data manually, and then entering the proper account and month labels for each row. Not only is this approach time-consuming; it's also prone to error. Essentially, this task is the opposite of pivoting data (i.e., the user wants to un-pivot data that is arranged in a PivotTable format but not contained in an actual PivotTable). This process is often referred to as "flattening" data. This feat can be accomplished using Excel 2003's older PivotTable and PivotChart Wizard tool, as follows:

1. Add the PivotTable and PivotChart Wizard tool to your Quick Access Toolbar. First, enable this Excel 2003 tool in newer editions of Excel by right-clicking the Quick Access Toolbar and selecting Customize Quick Access Toolbar. Next, from the Choose commands from dropdown box, select Commands Not in the Ribbon, scroll down to select the PivotTable and PivotChart Wizard option, click the Add button, and then click OK.

2. Create a PivotTable. Click the PivotTable and PivotChart Wizard button added to the Quick Access Toolbar in the step above. In the resulting dialog box (labeled Step 1), select Multiple consolidation ranges and then click Next. In the next dialog box (labeled Step 2a), select the I will create page fields option, and then click Next. In the subsequent dialog box (labeled Step 2b), enter the data range in the Range box, click the Add button, and then click Next. In the final dialog box (labeled Step 3), select the New Worksheet Option and then click Finish. These four steps are pictured below.

techqa2


3. Flatten your data. To complete the process, click the resulting PivotTable to select it, and in the PivotTable Fields dialog box, uncheck the Row and Column boxes. This action will reduce the PivotTable to a single Grand Total value (as pictured below).

techqa3


Finally, double-click the Grand Total value to flatten the data (or to convert the 13 columns of data into the three columns you seek, as pictured below). This approach is faster and more accurate than manually manipulating the data, especially when working with 13 columns and thousands of rows.

techqa4

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

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.