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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

COLUMN

Deflecting clients’ requests for defense and indemnity

Client requests for defense and indemnity by the CPA firm are on the rise. Requests for such clauses are unnecessary and unfair, and, in some cases, are unenforceable.