Sparkline Charts & Icon Formatting in Excel – Quick Organizing

I live in 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 forrest 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 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 over 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 cursor in blank cell where you want the sparkline to appear.
  2. From 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. In 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

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply