Microsoft Excel: Does this make my data look flat?

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?

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.

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).

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.

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.