How to turn ordinary sparklines into meaningful information with a few simple formats

Sparklines are a great visual tool, but you can increase their impact with some simple formatting.


Photo: Andrey Popov, Getty Images/iStockphoto

Charts are a great visual tool for quickly sharing information – at a glance, making the point. Unfortunately, even with Excel’s flexible built-in tools, creating meaningful charts can take a bit of time. This is where in-cell charts, such as sparklines, come in handy. With a few quick clicks, you can embed a chart next to the data. To further improve and influence readability, you can apply some simple formatting – all click options. No need to worry, just clicks. In this article, I will show you how to create and modify sparklines to improve their effect.

I’m using Office 365 Excel (for desktop) on Windows 10 64-bit, but this feature is available in Excel 2010 and 2013. You can work with your data or download the .xlsx demo file. You can view sparklines in the browser, but you cannot create or modify them.

See: Download Sample System Update Policy (Tech Pro Research)

easy visuals

Besides their visual effect, it’s easy to include sparklines: select a column next to the data, click the Insert tab, and then click an option in the Sparklines group. he did. Almost immediately, you have a visual representation that tells the story of the data faster than the data itself. Let’s work through a quick example using the data in Figure A:

  1. Select adjacent cells – in this case, it’s P3: P6.
  2. Click the Insert tab, then click Font in the Sparklines group.
  3. In the resulting dialog, select the data range, C3:06.
  4. Click OK.


Figure A: Selecting the data range.

as you see in Figure BIncluding a simple trendline for each region requires just a few clicks. You can stop here, but with a little effort, you can improve the default fonts.


Figure B: Virtual trendlines provide insight into the data, visually.

Waffle… a little bit

The easiest way to format inline lines requires absolutely no formatting: just set the column width and/or row height. Figure C Shows some examples. to me trend line It shows progress over a specified period of time, and is similar to the font option we just used. By changing the width or height, we can change the perspective. For example, by changing the width, the trend looks a little less severe than the original suggested line. By changing the row height, we get back some of that intensity. You don’t cheat, but this is one way to affect the overall picture.


Figure C: You can affect the effect by using the column width and row height.

Just how low is this drop?

As it is, the lines still lack an anchor. What is its high and what is its low? There is no legend. Adding an axis will help as follows:

  1. Select the built-in sparklines (P3:P6).
  2. Click on the Design tab.
  3. In the Group, click on the Axis option and choose Show Axis. (figure d).


Figure D: Axis provides insight.

The pivot points to 0, so some dips mean more than a loss – they indicate a shortage. You wouldn’t know it without the hub. At this point, you may want to change the width and height again to see how the perspective changes with the axis. You can stop here, but signs can help draw attention to specific points.


This feature offers a number of tag options. You can add a tag to each point, but in this case, high and low points may be more effective. To add these tags, select Sparklines and click High Point and Low Point in the Show group on the Design tab. Figure E Shows results.


Figure E: Add the high and low point signs.

The axis shows the negative points, and the red flag makes sense for those marks. However, you can change the mark of the high point to green using the Marker Color option in the Style group. However, the green tick does not appear as well. The answer is to change the font color using the Sparkline Color option in the Style group. shape and It shows a completely different line. (There are two lower signs for north because the lowest value, -60, occurs twice.)


Figure F: Change the font and tag colors.

even things

You can do a lot to affect perspective by coordinating lines, points, and marks. But there is one option that might not jump out at you, especially with the current example. High point values ​​are close to the range, so let’s mix things up a bit. Change the value in H3 to 400 and the value in H6 to -100. If you expect the high and low points to be more distinct from the other groups, you may be disappointed.

LEDs, by default, do not use the same scale within the same group. Therefore, it appears that -69 and -100 occur in the same general region down the axis. Similarly, 400, 269, 288, and 263 all seem to lie on the same horizontal line as well, as you can see in shape g.


Figure G: The high points 400 and 263 seem to occur on the same horizontal line.

Fortunately, there is an easy solution to that. Select Sparklines and choose Same for all Sparklines from the “Axis” drop-down list (in the group group under the “Insert” tab) in the second section. Then choose the same for all indicator lines from the third section. In our example, the difference is shown in shape h Hidden, but it is there.


Figure H: Use the same scale for all sparklines.

There are a number of formatting options and they are mostly self-explanatory. Spend some time exploring to get used to them. Also take a look at the other two indicator lines: Column and Win/Loss.

Send me your question about Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at

see also