Excel 2010 introduces us to a groovy new feature called Sparklines. A Sparkline is essentially just a tiny chart that sits in a single cell of Excel. The charts are drawn using basic Excel functions and once you know how to use them they are an incredibly efficient way of communicating a lot of information in a single cell of Microsoft Excel. Being built into Excel 2010 is exciting because formerly you were required to use 3rd party software to get the same effect.
- How To Create Sparklines in Excel 2010
- How To Customize Excel 2010 Sparkline Charts
- What is Win/Loss Sparklines and How do I use them?
Sparklines have been integrated right into Excel 2010! They are REALLY effective for communicating a ton of data in a single cell. Let’s take a look.
First, you’ll need an Excel 2010 spreadsheet with some data on it that you want to map out.
How To Create Sparklines in Excel 2010
In Excel 2010, Select the cell(s) where you want your Sparkline(s) to be. Remember that for each row or column of data you’ll only be able to use one cell.
One Row or Column = 1 Cell for Sparklines
In the example, below I’m going to create three, so I’ve used three cells.
Next, Click the Insert ribbon and Select a Sparkline type. I chose the Line style.
Now all that is left to do is Select the cells that contain the data you want to be graphed by Sparklines. You can also manually enter the coordinates, but it’s entirely optional. Once you’ve done that, Click OK.
Now using the Design tab on the ribbon, you can completely customize and “pimp out” your Sparklines to match whatever visual style you need. For Lines, Markers tend to be particularly helpful.
To customize the Axis option under the Design ribbon. Here you can set a lot of groovy features that will help make your Sparklines look even more professional.
You can also decide if you want to highlight certain pieces of information regarding your Sparkline such as the high points or low points. This highlight makes it even easier to pick out vital information from a single cell. Add a little color, and you can really impress those in your audience!
How-To Customize an Excel 2010 Sparkline Chart with Color
Click a Sparkline cell, Click Design Tab, then Select the additional points of data to highlight.
Now let’s change the Color a bit of our Sparkline Trending Line
Click the Sparkline Cell, Click Design Tab, Click Marker Color, Click High Point (or whatever you want) and Click the Color.
In my example, you can see I changed the top point of my trending Sparkline to green to show that was my best month for sales. I then selected the low point and changed it to red for added emphasis. The result gives me an excellent summary that my executive team can just see at a glance.
Now you can add color to Sparkline Trend or Lines, Columns and even the Win/Loss area. Let’s take a look at the Win/Loss portion of Sparklines.
What is Win/Loss Sparklines and How do I use them?
Simply put, the Win/Loss Sparkline displays Profit Vs. Loss or Positive Vs. Negative. You can also customize the look of the Win/Loss Sparkline by following steps 6 and 7 above.
In my example above, my Win/Loss Sparkline Cell is showing 6 months of Sales data and comparing it with the Goal for each month. My DIFF column is a comparison of how my sales compared to my goals. Then in the Win/Loss Sparkline, I configured it to show the Top and Bottom months and changed the colors to Red (bad) and Black (Good) and Darker Red for Worst and Green for Best.
This scenario is just an example of some of the things you can do with the new Sparkline Feature in Excel 2010. At work, I’ve already created some cool Sparklines with the plan to run my entire department from a single sheet in Excel! I’ll post it later in the forum once I have it ready. If anyone else out there has any cool example of Sparklines, please post them up in our forum or drop a note in the comments with a link!
Now granted, I realize not everyone has upgraded to Office 2010 yet so if you’re still running Excel 2003 or Excel 2007, here are a few alternatives you should check out if you want to play around with Sparklines. My recommendation, however – go get a copy of Office 2010. ; )