How sparklines deal with non-numeric values in Excel

Sparklines are small charts that match right into a single Microsoft Excel cell. However in the event you’re monitoring non-numeric information, they’ll exhibit bizarre conduct. Discover ways to keep away from issues when utilizing sparklines.


Picture: Paul Barnwell/Shutterstock

Sparklines in Microsoft Excel are charts—tiny little charts that show inline with the info as a result of they match right into a cell, normally adjoining to the info they’re evaluating. With a fast look, not solely are you able to find out about a single entity, however you possibly can evaluate that entity to others. They’re nice in dashboards, however their actual power is of their simplicity. They’re tremendous simple to implement; you get so much for little effort. Sparklines chart numeric information as you may count on. They’ll deal with non-numeric information when it creeps in, however viewers won’t perceive how the sparklines symbolize these values. On this article, I will present you the way sparklines deal with non-numeric information and some fixes for these situations.

SEE: 83 Excel ideas each person ought to grasp (TechRepublic)

I am utilizing Microsoft 365 Excel (desktop) on a Home windows 10 64-bit system, however this characteristic is accessible again to Excel 2010. You possibly can work with your personal information or obtain the demonstration .xlsx file. You possibly can show sparklines within the browser, however you possibly can’t create or modify them. This text assumes you have got primary Excel abilities, however even a newbie ought to be capable to observe the directions to success.

In case you’re not conversant in sparklines, you may wish to learn Learn how to flip extraordinary sparklines into significant info with a number of easy codecs. You may discover ways to create and format them.

How sparklines plot values in Excel

Sparklines consider numerical information and as everyone knows (all too properly), generally there are different values aside from non-numeric. We do not all the time count on them, so we fail to plan for them. On this case, there ought to most likely be no non-numeric values in your information set. My recommendation earlier than you even begin, is to scrub up your information, however that is not all the time sensible. When you possibly can’t work with a pristine information set, you could understand how sparklines deal with values aside from numbers:

  • A whole set of numeric values creates a linked line with an information level at every worth.
  • An error worth is ignored (no information level).
  • Blanks go away gaps within the sparkline.
  • Zero values are plotted.
  • Knowledge in hidden rows or columns are ignored, however you possibly can reset that default choice.

Now, let’s have a look at these guidelines in place.

When non-numeric values are a part of the info set, sparklines will symbolize them in numerous methods. Determine A reveals a easy information set with blanks, zeros and errors. As you possibly can see, the sparklines do not present any sort of alerting error, and viewers most likely will not perceive what they see. (What I feel odd is that the sparkline in H5 plots the textual content in C5 at 0; I’d count on the sparkline to disregard the textual content worth by leaving a niche.)

Determine A


  These sparklines do not object to non-numeric values, however the way in which Excel plots them may confuse customers.

Let’s check out every file: 

  • James chart is ok; all evaluated values are numeric. 
  • Luke has an error, which the sparkline ignores. You possibly can inform as a result of there isn’t any marker. If markers aren’t enabled, you may miss that utterly. To see markers, click on the Sparkline tab and verify the choice. 
  • Martha has textual content within the first cell, which the sparkline plots as 0. For me, that is the one failing for this characteristic. To be constant, the sparkline ought to ignore it. 
  • The empty cell for June is totally ignored; the sparkline does not even join the traces!
  • Rosa’s empty cell can also be ignored, however as a result of it is initially of the road, you won’t discover it, particularly if there aren’t different sparklines. 
  • Like James, Nick’s sparkline is in tact as a result of all of his values are numeric. 

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

If you do not know the best way to create the sparklines, do the next:

  1. Choose H3:H8, the situation of the sparklines. Alternatively, you do not have to pick the vary earlier than; you are able to do so later. It is solely a selection.
  2. Click on the Insert tab and click on Line within the Sparklines group.
  3. Within the ensuing dialog, specify the worth vary and the situation vary (H3:H8).
  4. Click on OK.

That is it! In case you do not like the way in which sparklines plot these non-numeric values, you have got choices.

Altering default settings

In case you do not like the way in which the sparklines plot non-numeric values by default, you possibly can revamp the info. 

First, let’s cope with the blanks. In case you actually do not know the worth, 0 appears acceptable sufficient. It is not the identical factor a a real clean, however as a normal rule, it is higher than leaving the cells clean. To make this variation rapidly, do the next:

  1. Choose the vary, C3:G8.
  2. Click on F5 after which click on Particular within the ensuing dialog.
  3. Within the subsequent dialog, click on the Blanks choice (Determine B).
  4. Click on OK and instantly enter a 0.
  5. With out clicking or urgent anything, press Ctrl + Enter to enter 0 in all clean cells within the chosen vary (Determine C). As you possibly can see, the clean cells now comprise 0 and the sparklines plot them.

In such a easy information set, you can simply enter the 0s manually, however that will not all the time be the case.   

Determine B


  Use this characteristic to pick all of the clean cells within the chosen vary. 

Determine C


  This can be a fast method to enter 0 in all clean cells.

Subsequent, let’s cope with the #N/A price. It is actually a manually entered #N/A error worth. However the easiest way to cope with it’s to repair the error. On this contrived instance after all, there isn’t any repair. Merely change it with a sound numeric worth, even 0. Any worth will do, however Determine D reveals a alternative worth of 0.

Determine D


  Repair errors so the sparklines can plot the precise values.

To search out all error values, use the Go To Particular characteristic once more. Search for the Formulation choice, and choose Errors. 

At this level, you can cease. All of the sparklines are plotting all of the values, together with the textual content worth in C5, which the characteristic plots at 0. Nonetheless, the textual content worth is wrong in most any setting. As a result of we’re not coping with null values as a professional worth, it could be finest to enter 0 than go away the textual content—for my part—until you have got a great purpose to depart it. In case you do, be forewarned that doing so may end in inaccurate leads to capabilities and expressions. 

Keep tuned

There’s a couple of method to discover error values in an information set, and in an upcoming article, we’ll evaluate them. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox