A little known Excel trick that makes your data sheet stand out

A little known Excel trick that makes your data sheet stand out

“Waow ! That’s a nice feature – very visual ! I wonder how they did that…”

That was my reaction a few months ago while reading an article with a screenshot of a Marketing excel sheet. The feature ? A cool, very simple graph showing a trend of data in a single cell. The sheet was comparing quarterly sales leads over a year in the different US States, so you can imagine some 50 rows with 4 columns of numbers each.

It was data, not information…. unless you looked at the last column where in a cell for each State, there was a little histogram with the highest bar in green and lowest in red.

Quick, easy, and visual. I could imagine producing something like that to help in compensation data analysis so I went to my Excel sheet and tried to figure out what that was. But, we used Excel 2007 at the time in the office, and I wasn’t able to figure it out other than producing these graphs individually and reducing them to the size of a cell manually – not a very productive use of time.

So I forgot about it, until I saw another sheet, this time in Finance, with the same kind of small graph, this time a win/loss one.

Seeing this done once again, I figured there must be a way to produce these graphs automatically… and there is !

It is called “sparklines”, a new feature in Excel 2010. Here are examples of what it looks like in the Trend column :

Sparklines are super easy to create

Go to Insert > Sparkline then select the range of cells you want to represent in the graph and where you want it to show.

You can select a line graph, a histogram or a win/loss graph.

Once it is created you can then adjust it visually by going to Sparkline tools > Design and changing the kind of graph, the colours, identifying markers (the first, last, highest and/or lowest value) and making these markers easy to see by selecting a contrasting colour etc.

If you use the Group/Ungroup function,you can make change to one sparkline at a time, or apply the same changes to all of them at once.

I have created a quick Powerpoint demo :

You can use sparklines to quickly visualise bonus trends, salary trends, allowances, sales incentives, emiratisatin percentages, or any kind of historical data that shows in large spreadsheets (employees, departments, companies in a conglomerate….).

A little bit of background on sparklines to shine in conversation

Finally, I did a little research on the term “sparkline” and found out this was a term coined in 2004 by one of the greatest current thinkers on data visualisation :

“The term sparkline was proposed by Edward Tufte for “small, high resolution graphics embedded in a context of words, numbers, images”. Tufte describes sparklines as “data-intense, design-simple, word-sized graphics”.

On May 7, 2008, Microsoft employees filed a patent application for the implementation of sparklines in Microsoft Excel 2010. The application was published on November 12, 2009,prompting Edward Tufte, the acknowledged inventor of the graphic,to express concern at the broad claims and lack of novelty of the patent.” (from Wikipedia)

Personally, I don’t care much about the debate. Tufte’s idea to produce a graph that would act like a word (not being disruptive to the context and adding high visual value) has been implemented easily by Microsoft and I am thankful, not just for the concept, but for its application in my daily work. And this is what I just shared with you.

So now, you can not only produce useful, great-looking Excel sheets and reports, but also impress your management with your mastery of thought leaders in statistics and informational graphics.

Where else than on Compensation Insider would you find this double whammy of knowledge and actionable information to progress your career 😉 ?

Related posts :

Print Friendly, PDF & Email
Share the Knowledge !
Get Free Updates

Speak Your Mind

*