By Michael Alexander

Much of the reporting done in Excel is table-based, in which precise numbers are more important than pretty charts. However, in table-based reporting, you often lose the ability to show important aspects of the data such as trends.

The number of columns needed to show adequate trend data in a table makes it impractical to do so. Any attempt to add trend data to a table usually does nothing more than render your report unreadable.

Take the example in this figure. The data here represents a compact KPI (key performance indicator) summary designed to be an at-a-glance view of key metrics. Although the table compares various time periods (in columns D, E, and F), it does so only by averaging, which tells us nothing about trends over time. It quickly becomes evident that seeing a full-year trend would be helpful.

image0.jpg

This figure illustrates the same KPI summary with Excel sparklines added to visually show the 12-month trend. With the sparklines added, you can see the broader story behind each metric. For example, based solely on the numbers, the Passengers metric appears to be slightly up from the average. But the sparkline tells the story of a heroic comeback from a huge hit at the beginning of the year.

image1.jpg

Again, it’s not about adding flash and pizzaz to your tables. It’s about building the most effective message in the limited space you have. Sparklines are another tool you can use to add another dimension to your table-based reports.

Sparklines are available only with Excel 2010 and Excel 2013. If you open a workbook with sparklines using a previous version of Excel, the sparkline cells will be empty. If your organization is not fully using Excel 2010 or 2013, you may want to search for alternatives to the built-in Excel sparklines.

Many third-party add-ins bring sparkline features to earlier versions of Excel. Some of these products support additional sparkline types, and most have customization options. Search the web for sparklines excel, and you’ll find several add-ins to choose from.