Excel 2010 introduces us to a groovy new feature called Spaklines. A Sparkline is essentially just a very small 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 effective way of communicating a lot of information in a single cell of Microsoft Excel. Being built into Excel 2010 is exciting because in the past 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?
Back to Excel 2010, like I said Sparklines have been integrated right into Excel 2010 and are REALLY 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) you want your Sparkline(s) to be in. 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 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 especially useful.
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 makes it even easier to pick out key information from a single cell. Add a little color and you can really impress those your presenting to!
How-To Customize an Excel 2010 Sparkline Chart with Color
Click a Sparkline cell, Click Design Tab, then Select the additional points of data you want highlighted
Now let’s change around the Color a bit to 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 a nice summary my executive team can just glance at.
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 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 really 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 your still running Excel 2003 or Excel 2007, here’s a few alternatives you should checkout if you want to play around with Sparklines. My recommendation however…. go get a copy of Office 2010. ; )