Sparkles in your eyes

Maybe you have heard of Bissantz wonderful software add-in for MSXL, the SparkMaker. Keeping it short and simply this tool easily allows you to embed little nice graphs directly besides your table. Imagine you have a list of stock data (maybe imported through a web query, which is explained in another post on this website) and want to show the performance of the last 30 days. Bissantz SparkMaker lets you easily define the range for your in-table-graph and a lot of other things. This concept is very good visualized by the following screenshot:

The little graph in column B is a so called spark line. Head over to the Bissantz website to learn more about this add-in. As it is not free unfortunately, I will show you a way how to produce these sparklines yourself using Excel on board tools. The first thing I will do is to predefine a chart without any additional info other than the graph itself. This means no axes, no title, no gridlines etc. nothing. Focussing on the line graph I have created a very easy table with values in column A.

You just have to do this once to define a new chart template. After this definition, the chart will be deleted. So the resulting chart looks like this

Now - in MSXL 2007 - click on the chart and in the appearing Chart Tools Ribbon go to the button labeled Save as template This will enable you to do exactly that, save this type of chart where you got rid of all the unnecessary things as a template you could reuse over and over.

Now comes the trick itself. Insert two rows above cell A1 and label the first one (A1) with Values and leave the second empty for now. Next step is to put the chart out of view on its own tab in the spreadsheet or out of the viewport so you do not see it at the moment (i. e. to cell A100).

Go to the View Ribbon and in the Show / Hide section, uncheck the gridlines checkbox. The background of your spreadsheet should now be white.

Make sure that you have the Camera tool available in your quick access toolbar. If you don't have it there, click on the big Office button and select the Excel options dialog by using the button in the bottom right corner. In the options dialog, click Customize and from the left dropdown choose All Commands. Scroll down the alphabetical list until you find it and move it to the right by clicking the Add button.

Now, back to our chart. Go to the section where you moved the chart to and select all the cells that are behind the chart. Now click the Camera tool. This will switch your cursor into a little crosshair.

This is the time to get back to the second row we inserted resulting in an empty cell (A2) between the "Values" (A1) and the first information, 15 (A3). Click and drag to position the "photo" of your hidden chart. Don't worry if it lets you only open up something quadratic. If you release the mouse button, you can adjust the "photo" just as any other picture. Make it fit to your requirements, i. e. the size of cell A2. Right click the pictures border and select "Format picture". In the upcoming dialog box choose the "Colors and Lines" tab and set both, the color of the fill and the line color to "No fill" / "No Line". This should result in something like the image below:

You may ask, why didn't we simply reduce the original chart to this mini-size. Well, decreasing the original chart size would result in a much more limited display, because when it comes to these ultra-small sizes, MSXL does not seem to be able to render the chart properly.

The other thing is that this "photo" of your original chart is still dynamic. Meaning that if you change the value of cell A5 from 18 to 38 this will result in an update of the chart and the photo, because through the usage of the camera tool, both displays are linked together. Just a little warning: if you do major adjustments to the values that make up the chart, make sure to re-adjust the photo also, because sometimes it can become "invisible". Simply click in the cell where your sparklines are placed and adjust the graphic a little bit.

If you only use the sparklines from time to time, this little post might be able to help you and save some money (USD 59.95 at the time of creation of this post). Why don't you create some of these nice little sparklines, also utilizing other chart types, just don't forget to get rid of all the stuff (title, legend, grid etc.) that you don't want to display and save the new "empty" chart as a template as described above. Share some of your results with us or contact me, maybe we can have a nice little gallery of sparklines tables with links back to your websites.

Discuss this blog post in the forums (click the blue button)