You may be surprised to discover that
Charting QuickBooks Data with Built-in Charts
QuickBooks offers a variety of graphs that are viewable within the program. To find these graphs select Reports, then Report Center. Type the word Graph in the Search Box to reveal a listing of built-in graphs in QuickBooks:
- Accounts Receivable Graph
- Accounts Payable Graph
- Sales Graph
- Income & Expense Graph
- Net Worth Graph
- Budget vs. Actual Graph
Figure 1: QuickBooks offers a handful of built-in graphs that easily accessible.
QuickBooks also offers a series of graphs known as Snapshots, which you can access by choosing Company, and then Company Snapshots. These are grouped into three categories, as illustrated in figure 2:
Company
- Income and Expense Trend
- Prev Year Income Comparison
- Top Customers By Sales
- Prev Year Expense Comparison
- Expense Breakdown
Payments
- Invoice Payment Status
- A/R Aging By Period
Customer
- Sales History (Monthly, Quarterly, Weekly, Yearly)
- Best-Selling Items (can be displayed by Amount or Units)
Figure 2: Snapshots provide a quick way to see an overview of your company data.
Snapshots offer you the ability to drill-down and filter the data, as illustrated in figure 3.
Figure 3: You can drill-down and filter the snapshots.
If you need to go beyond what QuickBooks offers with regard to charts, you have many options in Excel. Export your data to Excel using one of several methods that I discussed in a previous article.
The fastest way to create a simple bar chart is to click on the data you wish to chart and press Alt-F1. This shortcut is available in Excel 2007 and later. For Excel 2003 you’ll need to choose Insert, Chart, and then make a selection. These charts will appear on the same worksheet as your data, but you can also utilize chart sheets—special workbook tabs that only contain a chart and no worksheet cells. Most users think that only a single chart can fit on a chart sheet, but you can easily create your own version of the QuickBooks Company Snapshot format by stacking two or more charts on a single chart sheet as illustrated in figure 4:
- Press F11 in a blank worksheet cell to create a blank chart sheet.
- Move existing charts to this chart sheet by right-clicking in the chart area and choosing Move.
Figure 4: You can easily stack two or more charts on a single chart sheet by following these steps.
Within a chart sheet these individual charts float above the worksheet and can be resized and repositioned at will. To resize a chart, hold down your left-mouse button while you grab a border of the chart. To move the chart around, hold down the left-mouse button while you “grab” any white space within the chart to move it into a new position.
Self-Expanding Charts
Let’s say that you have a simple chart that you add a few numbers to each month, such as shown in Figure 5 below. Many users end up dealing with having charts crash to zero midway through the year, or they have to manually resize the chart month after month. In that regard, there are two ways to manually resize a chart:
- As shown in Figure 5, click on the chart to select it, and then on the Design tab click Select Data.
- The next step is to modify the Chart Data Range to expand your chart area, but it’s not always as simple as it seems. This type of field in Excel is known as a RefEdit field, and often causes frustration to users that wish to use their keyboard to adjust the area. If you press the left-arrow key instead of moving the cursor one character to the left, Excel will instead insert a cell address, thereby compromising your chart data range. The nuance is to press F2 before you press an arrow key. When you’re in a RefEdit field, the bottom left-hand corner of Excel will show one of three words
- Point
- Enter
- Edit
The first two assume that you want to select or navigate to cells within the worksheet. Press F2 until the word Edit appears, and you’ll then be able to arrow back and forth within RefEdit fields without frustration.
Figure 5: One way to update a chart is to manually resize it. Pressing F2 in the RefEdit field allows you to press the left-arrow key without using the cursor to revise the cell address.
A less frustrating method for resizing charts is shown in Figure 6. When you click on a chart to select it, Excel highlights the underlying data for the chart. Simply grab the handle for the chart area and drag as needed to encompass the additional data that you’ve added.
Figure 6: Expand your chart by dragging the fill handle on the underlying data to include the new data.
However, my favorite method of making charts maintenance-free is to use the Table feature in Excel 2007 and later. You can even use this feature to retro-fit any existing charts to become self-expanding:
- Select any cell within a range of chart data, as shown in Figure 7.
- On the Insert tab choose Table (or press Ctrl-T), make sure to check the My Table Has Headers box is selected, and then click OK.
From this point on any new columns of data that you add to the right of your data range will instantly appear on your chart. New rows will appear in the same fashion as well.
Figure 7: The Table feature allows charts to automatically update.
As illustrated in figure 8, Excel 2013 offers a new Recommended Charts feature that can vastly simplify charting any sort of data.
Figure 8: Excel 2013’s Recommended Charts feature makes selecting a chart type extremely simple.
Conclusion
Although QuickBooks offers a variety of charts, you may sometimes want to create your own. Creating a chart in Excel can be as simple as pressing Alt-F1 or the F11 key, or by making a selection from the Recommended Charts feature in Excel 2013. If you maintain charts in Excel, be sure to take advantage of the Table feature which can make your charts maintenance-free.