Insights

QuickBooks Tip - Improving the Use of Reports Sent to Excel

QuickBooks Tip - Improving the Use of Reports Sent to Excel

Oct 16, 2017

One of the advantages of using QuickBooks is the ability to quickly generate meaningful reports about your business. This is true for both the Desktop and Online versions of QuickBooks. Both versions of QuickBooks have the ability to simply and seamlessly export reports to Microsoft Excel so that data analysis can be taken a step further with the tools and functions that are available within Excel. Blending the best features of QuickBooks and Excel can be a powerful way to extract and analyze meaningful data about your business. 

Ideally, Excel likes data in a list, block, or table-like format with no blank cells… basically a “flattened format.”  Blank cells can be troublesome, if not downright destructive, when filtering, slicing data, or summarizing information within a pivot table. A typical QuickBooks’ report exported to Excel frequently leaves blank cells for the sake of cosmetics and simplicity. The example below (figure 1) reflects this in column A, where the vendor name is stated only once and not repeated in the lines directly below. Stated another way, there is not a one-to-one relationship between the vendor name in column A and the data in columns B through J. As is, if the data below were to be sorted, the vendor identity associated with the information on lines 3-6, lines 9-16, and so forth, would be lost. 

A typlical QuickBooks' report exported to Excel 

Figure 1 – Here’s how a typical QuickBooks report exported to Excel looks. 

To fix this, you might consider copying the data from cell A2 to cells A3 through A6 and so forth. However, if you have a very lengthy report this could be quite time-consuming. There is a quicker way.

How the Excel file looks after filling in the blank cells.
  1. First, select the range that contains blanks you need to fill. Don't select the column header cell — just the range that contains actual data.  In the example in figure 1, the range is A2:A27.
  2. Select the “Go to Special” from the “Find & Select” drop down menu in the “Home” menu tab.
  3. Select the radio button associated with “Blanks
  4. In the first selected blank cell (A3) enter an equal sign and point to the cell above (A2)
  5. Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range. Important: the control key must be selected when the enter button is selected for this to work. 
The steps needed to fill in the blank cells in a QuickBooks report sent to Excel 

Figure 2 - After completing the above steps, your Excel file should look like this.

At this point, the data in column A of figure 2 is represented by a formula.  We want to convert all cells in column A to represent values not formulas so that the data can be sorted without changing the underlying data.  To accomplish this:

  1. Select column A by selecting the column A header.
  2. Right mouse click and select “Copy.
  3. Right mouse click again on column A and select “Paste Special.
  4. Select “Values” from the next window and select “OK.

At this point, your QuickBooks data is now in a “flattened format” that is more functional for further analysis within Excel.    

Your QuickBooks data file contains valuable information about your business.  Exporting this information from QuickBooks (Desktop or Online) to Excel and using the tip above can unlock this information for use in new and powerful ways.

Author(s)

Marv "MK" Reesor
Marv Reesor, CPA
Senior Accountant
View Profile