Q: We produce a monthly report in Excel summarizing the previous 12 months’ accounts-receivable balances for each of our 2,600 customers, and it is my job to analyze this data to identify growing A/R balances that indicate customers who might require special attention. Ideally, I’d like to chart each of the 2,600 rows of data so I can visually identify customers whose outstanding balances are trending upward, but creating and updating this many charts is too time-consuming to be practical. Can you suggest a different approach?
A: Excel 2010 provides a new tool called Sparklines, which enables you to create a small chart within a single cell and quickly copy that chart to other cells. Using this approach, you could create 2,600 charts in just a few seconds. As an example, I created a workbook containing 12 months of A/R balances for 2,600 customers (pictured in columns A through M below). To create the Sparkline, I placed my cursor in cell N2, and, from the Insert tab, I selected Line from the Sparklines tools group, highlighted the data range (B2:M2 in this example), and clicked OK.
This action produced a small line chart in cell N2. Next, I copied the Sparkline in cell N2 and pasted it to the remaining 2,599 cells below to display 2,600 line charts depicting trends for each customer’s A/R balance.
Note: Sparklines are sometimes easier to read if you increase the column width and row height of the cells containing Sparklines. Additional Sparkline settings allow you to change a Sparkline’s color and width, emphasize high points, display the Sparkline using a column format, or adjust other settings. Two examples of these additional settings are pictured below.
While Sparklines may add readability to your analysis, I also would recommend adding a formula to help you identify specific customers with growing A/R balances. For example, in column O below, I used the IF function to add the calculation =IF(M2/AVERAGE(J2:L2)>1.1,"Attention","") comparing each customer’s most recent A/R balance to the average balance for the previous three months. If the current A/R balance exceeds the average balance for the preceding three months by more than 10%, then the word “Attention” is displayed. (Of course, you would need to decide how many months to use in computing your average, and the percentage threshold that warrants attention.)
