Professional Development

Sparkline Charts & Icon Formatting in Excel – Quick Organizing

I live on both sides of my brain and while I was learning Excel I was slow in producing needed documents. Because of my creative, and sometimes perfectionist/control freak nature, I had to learn how to see the forest through the trees and deliver simply on function instead of presentation. After learning the basics and a few additional features, I was able to deliver both presentation and function.

In today’s fast-paced business environment it is necessary and advantageous to be able to drive business, revenue and profit while being able to communicate effectively and quickly.  Excel helps us do that quickly. Knowing some of Excel’s additional functions helps us do that with a little more style.

Sparkline Charts allow users to analyze trends on the spot, allow the creator to design in one click and unlike other charts, these sparkline charts print with the spreadsheet. For example, take a quarterly review of sales per sales rep and their quota attainment. With these two additional features, my VP of sales and CFO can quickly and easily visually digest given data.

These features allow the user to visually display trends or highlight variances without going through added work of creating, updating and pulling in charts along with our data.  I use these feature to set up summary tabs in excel for use in my presentations. With my data organized, I then can copy the data to my presentation or simply print the spreadsheet. No additional work required. My presentations are updated in minutes.

Create a Sparkline

  1.  Put the cursor in a blank cell where you want the sparkline to appear.
  2. From the menu click on Insert tab, from sparklines group, select the type of sparkline you want to create.
  3. A pop-up screen will appear
    1. Data Range: select the group/range of data you want to use as your reference (the data you want to trend)
    2. In Location Range: select the group/range of cells you want the corresponding sparklines to appear.

Once the sparklines are created an additional tab will appear in your ribbon titled Sparkline Tools. You can further customize the sparklines using this tab. Select a cell with your sparkline that you want to adjust and you can change the color and attributes of your sparklines.

Create Icon Conditional Formatting

  1. Highlight with your cursor the data you want  your Icons to appear next to
  2. On home tab, click on Conditional Formatting and move cursor to Icon sets

  3. From the drop down select the type of Icon you want to display
  4. The conditions that must be met for each red, green, yellow Icon default.
  5. Adjust conditions for Icons. To adjust conditions (meaning to adjust at what point or break in your data you want to display the different colors – for example green for over 100%, yellow for between 70 and 100% and red for anything below 70%)
    1. adjust by selecting one of the cells with your conditional format
    2. click on Conditional formatting from the home tab and select the Manage Rules
    3. From the Conditional Formatting Rules Manager widow select your set of icons and click on edit rule
    4. From this window you can change your colors, orders, Icon shapes and values that drive the Icons

Get New Posts From Sarah Schlott Sent Right To Your Inbox


138 Subscribers

Published by Sarah Schlott

Most readers subscribe via email to my weekly newsletter and follow me on Twitter and Instagram. To work or collaborate with me, take a look around to get an idea of where our voices and audience may align. Please email me with any inquiries.

Leave a Reply

Your email address will not be published. Required fields are marked *