QuickBooks Small Business

Flattening Data in Microsoft Excel

Written by David Ringstrom

The key to making the most of Microsoft Excel is ensuring that your data is presented in a list format. This means that each row comprises an individual record in its entirety. However, users often embark on a different approach, by blending data in rows and columns. In this article I’ll show you how to use a hidden PivotTable Wizard within Microsoft Excel to easily transform such data into a flattened list format so that you can then filter, slice, and otherwise summarize the data by way of Pivot Tables and other features.

An example from QuickBooks is the Profit and Loss by Class Report shown below in Figure 1. This report can be viewed as is, but you can’t easily analyze the data without some significant reworking.

To begin, I’ll export the Profit & Loss by Class Report from the product-based business sample company in QuickBooks Desktop:

  1. Choose Reports.
  2. Choose Company & Financial.
  3. Choose Profit & Loss By Class.
  4. Click the Excel button.
  5. Choose Create New Worksheet.
  6. Click Export.

At this point you should have a report that looks similar to Figure 1.

Flattening data in Microsoft Excel

Figure 1: We’re going to use a series of techniques to flatten this report into a list that we can filter or otherwise summarize.

The first step is to fill in any gaps within our report. Notice that columns B:E have captions that only appear on certain rows. To ensure that we have the most flexibility for summarizing our data, we’ll want to fill those gaps in. The first step will be to remove the Text number format that QuickBooks assigns to these columns:

  1. Select columns A:F.
  2. Choose General from the Number section of Excel’s Home menu.
  3. At this point all cells within columns A:F should show General when you click on any cell.
Flattening data in Microsoft Excel

Figure 2: Avoid frustration by removing the Text number format from reports that you export from QuickBooks.

We’re now ready to fill in the gaps for columns A:E. We ostensibly don’t need the data in column E, but I’m going to fill in the column anyway to show you how to reverse fill data within a column as shown in Figures 3 and 4:

  1. Select cell A2 through the bottom of your report, in my case A73, as illustrated in Figure 3.
  2. Choose Find & Select on Excel’s Home menu.
  3. Choose Go To Special.
  4. Double-click on Blanks to skip the OK button.
  5. In the currently selected cell, type an equal sign, tap the Down arrow, and then press Ctrl-Enter.
  6. The words Net Income should now appear in cells A2:A73.
Flattening data in Microsoft Excel

Figure 3: Ctrl-Enter enables you to place the same formula or value in multiple cells at once.

We’ll now fill in columns B through E:

  1. Select cell B2 through the bottom of your report, in my case E73.
  2. Choose Find & Select on Excel’s Home menu.
  3. Choose Go To Special.
  4. Double-click on Blanks to skip the OK button.
  5. In the currently selected cell type an equal sign, tap the Up arrow, and then press Ctrl-Enter.

At this point your report should look like Figure 4.

Flattening data in Microsoft Excel

Figure 4: Filling in blank cells is a key aspect of flattening data in Microsoft Excel.

We’re now ready to remove the rows and columns that we don’t need from the data. Before we do so let’s first convert all formulas to values:

  1. Click on cell A2.
  2. Press Ctrl-A to select the entire report.
  3. Press Ctrl-C or click the Copy command on Excel’s Home menu.
  4. Right-click on cell A2.
  5. Choose the Paste Values icon in Excel 2010 and later. In Excel 2007, choose Paste Special and then double-click Values.

You’re now ready to filter the data as shown in Figure 5:

  1. Click on cell A2.
  2. Choose the Filter command from Excel’s Data menu.
  3. Click the arrow that appears in cell F1.
  4. In Excel 2010 and later type a left parenthesis in the Search box. This is the shortcut for selecting blanks. If your account names have parenthesis in them, or you’re using Excel 2007, then instead you’ll clear the Select All choice at the top of the list and then scroll to the bottom and select Blanks.
  5. Click OK to filter the list.
Flattening data in Microsoft Excel

Figure 5: Filtering will enable us to remove total rows and headings from our data.


Save pagePDF pageEmail pagePrint page

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

3 Comments

  • Hello David.
    I’m a CPA.I read your post “Flattening Data in Microsoft Excel”.I’s really amazing.This post is very useful & helpful to me and others bloggers.This information is very much valuable. I like this article thank you for this wonderful article.
    Thanks again for your guidance. I’ll follow your tips.
    With Best of luck for new useful post.
    Jems.

  • Hi David,

    I am somewhat of a newbie to Internet & Computer. So with that being said, I am very glad that I found this blog post. I’ve heard talk of Microsoft Excel but never really knew what it was all about; however, I can say that I understand a lot better now. I plan to stay connected to your blog. Can’t wait to see what you’re going to write next. Thanks

Leave a Comment