Introduction
I know many Excel users who have never heard of Sparklines even though they have been part of Excel since its beginning and in spite of newspapers, magazines and television channels using them on a regular basis. Never mind, they are easy to find on the Insert menu and it is easy to learn how to use them.
I use Sparklines for a variety of things and I try to be creative with them too.
Here are three Sparklines that I created yesterday:
There you are, basic Sparklines that I stretched by making the column width 40 and the row height 70 and putting cell borders on the bottom and the left, to simulate the X and Y axes. I put the chart titles in the row above them but I could just as easily have included them in the same cell as the chart.
So far so good. What about the situation where I would like to see all three Sparklines in the same chart? Hmm, not possible: one chart at a time only. However, by using the Sparklines I have just shown together with a bit of imagery manipulation, I created this:
I overlayed two Sparklines onto the base Sparkline, added a Legend and it looks clean and tidy … I could do this with four, five and more Sparklines. But, what happens to this graphic when I change colours or I add some data? That is, this multi layer graphic is static and it can only change if I change it manually. For such changes to be made automatically, I can use the Camera Tool, which I keep in my Quick Access Toolbar (QAT) at the top of my Excel screen to create volatile, interactive Sparklines
The Camera Tool is not on any menu by default, you need to add it … to any menu but I put mine in my QAT.
And by using the Camera tool, I can overlay one or more graphs on top of another graph and as I update my graphs, my multi layer graphic will also update. Like this, new Camera Tool created graphic:
Which I then updated by changing my original Sparklines: my updated, multi layered graphic, created by using the camera tool.
I even managed to combine a bar graph with two line graphs as I made the changes to the original Sparklines as the multilayered graphics updates automatically, by the magic of the Camera Tool.
By the way, I created the Legend with the help of Conditional Formatting using the words USA, Canada and Mexico …
There is no file to download for this example but I have three other excelmaster pages for you to learn from, in case you are new to Sparklines:
Duncan Williamson
23rd January 2020
No comments:
Post a Comment