TechSoup.org The place for nonprofits, charities, and libraries

Microsoft Office 2010: How to Use Sparklines in Excel 2010

Show data trends for your nonprofit or library in Microsoft's Excel 2010 with built-in simple charts

Microsoft Office 2010: How to Use Sparklines in Excel 2010 
Trenton DuVal - May 15, 2012
Want to make the data you manage at your nonprofit or library easier to understand at a glance? Microsoft Office's Excel 2010 has a simple solution in its sparklines feature. Sparklines are simple visual representations of your data, right in a spreadsheet. Learn how to create and customize Excel sparklines in this easy how-to.

Microsoft's Excel, part of the Microsoft Office suite of products, has long been a useful piece of software for compiling and understanding the relationships between data. Excel 2010 introduces a tool that makes it even easier to see trends in your nonprofit or library's data using visual representations: sparklines.

Microsoft Excel is part of Microsoft Office 2010 and is available to eligible nonprofits and libraries through the Microsoft Software Donation Program through TechSoup.

What Is a Sparkline?

A sparkline makes it quick and easy to make readily intelligible mini-charts within the cells of your Excel spreadsheets that highlight important details of your data. They come in the form of a small line graph, bar chart, or win/loss.

So, you could have a data set across a number of cells in a worksheet that then display a small mini-chart in a final cell to visualize the data easier (you can view examples of how they may look in screenshots below).

These charts are usually located very close to the data they represent, making it easier both to spot trends in existing data and to see how changes would affect the results.

As sparklines form the background of a cell, they can have label text or other info written over them. (Just type in the cell as you normally would in Excel.) Unlike full-scale charts in Excel, sparklines are printed along with the worksheet.

Why Would My Nonprofit or Library Use These?

The addition of sparklines can make the already powerful tool of Excel even more useful for nonprofits, libraries, and other social-benefit organizations.

For example, Ohio-based Scranton Road Ministries promotes community-based economic development and leadership training. Its Youth Jobs Partnership is a workforce development initiative that provides Cleveland's young adults with comprehensive and relevant job training. Microsoft software and technology donations provided through TechSoup have greatly strengthened YJP and increased its positive impact across the community.

Among YJP's offerings are extensive courses in the Microsoft Office Suite. Using Excel, for example, the students are able to create personal budgets and can graph their savings goals. This activity is ideal suited for using sparklines to help demonstrate in simple, easy ways whether they're succeeding in their budget plans. At the same time, teaching young people how to create simple graphs like this in Excel is giving them a relevant employment skill.

Additionally, sparklines can help demonstrate simply and clearly whether your nonprofit is on budget with grants received, overspending on a specific program area, or meeting a measurable program goal. They can be used to visually demonstrate growth in attendance at your library or nonprofit events or goals in month-over-month fundraising.

Now that you know what sparklines are and how they can be useful for nonprofits and libraries, let's walk through how to create them in Excel 2010. In these examples, we use hypothetical fundraising data.

Insert Sparklines into Your Spreadsheet

Select the cells where you want the sparklines to appear. (Usually these are cells immediately adjacent to the underlying data.)

Screenshot - Select the cells

The sparklines section of the toolbar is under the Insert tab. It has three options for the type of sparkline to insert: Line, Column, or Win/Loss. We'll go over the Column and Win/Loss formats at the end of the article. For now, let's try the Line format.

Screenshot - Insert Line Sparklines

A "Create Sparklines" dialog box opens. The Location Range describes the cells where the sparklines will appear, while the Data Range corresponds to the cells that contain the underlying datapoints for the sparklines.

Screenshot - Create Sparklines dialog box

Because we have already selected the cells for the sparklines to appear in, the location range is already filled in. To fill in the data range, either type in the desired range or click and drag the mouse over the required cells to automatically fill this in.

Screenshot - Select data cells range

Click Ok and the sparklines will appear in the selected cells.

Screenshot - Hit OK button

Screenshot - Sparklines appear

Customize the Appearance of Sparklines

Now you can further customize the appearance of the mini-charts using the sparklines design tab. It automatically shows once you click OK for your sparklines. If you need to get back to it later, click on one of the cells with a sparkline in it and choose the design tab underneath Sparklines Tools.

Let's add a marker for the high point on each sparkline. In the "Show" section of the design tab, check the High Point box.

Screenshot - Toggle Sparklines High Points

Now, for ease of visibility, make the lines a little heavier weight and the high point markers a contrasting color. The dropdown menu to the right of the"Styles" section also has the option for controlling the thickness (or weight) of the lines in the sparklines. The Marker Color dropdown menu shows you the options for different colors for the high point marker.

Screenshot - Change High Point color

The Styles section has pre-formatted styles for your sparklines that are applied in one click.

Excel Sparklines in Column and Win/Loss Formats

Depending on the nature of your data you may want to show your sparklines in Column or Win/Loss formats. Columns are useful for comparing each periods' data to other periods while the win/loss format only shows whether a piece of data was positive or negative.

Screenshot - Column Format

In our example, notice that the "Winter Fund Drive" lost money (-$200) in the first year. This is reflected in the downward pointing win/loss column.

Now you should know all you need to be able to make your spreadsheets more professional-looking, readily understandable, and visually appealing by adding Excel 2010's sparklines.

Further Resources

Image: Micro chart, Shutterstock