Doing Data Analysis With Sparklines & Conditional Format In Excel

Here’s a nice way to use both Sparklines and Conditional Formatting to quickly and more accurately analyze your sales or other data.

You can download the file here to follow along.  If you get a preview, look for the download arrow in the upper right hand corner.

We have 9 salespeople with sales by month for last year.  We can easily see who was the highest and lowest, but digging into the details may tell us more:

combo1

First. let’s conditionally format the total column with two methods, Data Bars and Icon Sets. Highlight the values in the Total column and click on Conditional Formatting from the Styles group of the Home tab:

combo2

Click on the color style you prefer and here is the result:

combo3

Now we can quickly see that Fred had the most sales and Jane the least.  Next, highlight the same totals. click on Conditional Formatting again, and add an icon set:

combo4

Choosing the four arrows option will result in:

combo5

Now we can see that Fred was the top salesperson, but Lisa, Arnold, and Tammy were close together in second and the others were close to each other at the bottom.

Now, let’s add Sparklines for a more granular analysis.  From the Sparklines group of the Insert tab, click on the Line button, then select the range of data to analyze and the area you want the Sparklines located:

combo6

Click OK, and you have:

combo7

For a better analysis, I would make Column P wider and also heighten the rows:

combo8

Sparklines now shows us that, although Tony was in the bottom group, he finished strong in the second half, Fred, our top salesperson, struggled a bit in the later months, and Karen finished the year poorly.

Adding various formats with Sparklines can help give quicker and more detailed and accurate analysis.

Happy Excellling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply