Do you know what Excel sparklines are and how helpful they can be? Read this article and find the answers to these questions. You will learn how to insert sparklines in Excel 2010 - 2016; change their type, style and color; highlight the most important points and remove sparklines from cells. Show
Imagine you are working on a spreadsheet that includes a number of different salespersons and the amount they sold each month. You want to see how each person's sales are progressing over time. You can just put this information in one big chart, but it will be hard to pinpoint a trend for an individual person. Luckily Excel provides you with a special feature that can help you give each salesperson a separate mini chart that fits into a single cell. Please welcome… Excel Sparklines! What is a sparkline in Excel?First let's define what a sparkline is. Excel sparkline is a small chart placed in a single cell. It gives you a visual representation of the selected data set. Sparklines allow you to quickly view and analyze relationships and trends for multiple data series at the same time. Excel versions 2010 and 2013 offer three types of sparklines: Line, Column and Win/Loss. The Line type appears as a simple line chart within a cell. Column sparklines take the form of simple bar charts reflecting the relative size of the value. Win/Loss shows a basic positive or negative representation of your data set. Why should you use sparklines?Compared to traditional charts, sparklines offer a very different approach to visualizing your data. The key benefit of using them in your worksheet is that they are compact and don't take much space. Sparklines are ideal for situations when you need a clear overview of the data at a glance and when you don't need all features of a full chart. They also make it easy to view and analyze trends on a more individual basis, and they can really help you manage your worksheet data. How to insert sparklines in Excel 2016 - 2010Excel sparklines are usually inserted in cells next to the data source. It doesn't matter which of the three sparklines you want to create, the steps are the same for all types.
You see the first sparkline in the cell right next to your source data. If you'd like to apply it for all other rows of data, you can easily do it by holding and dragging the fill handle. When you release the mouse button, the sparklines will be inserted in the adjacent cells. If you have Excel 2016, you can use the Quick Analysis tool to quickly add sparklines to your data. All you have to do is select the cells in the worksheet and click on the Quick Analysis icon in the bottom-right corner of the selection. Then switch to the SPARKLINES tab and pick one of the types.
Note. If you use the Quick Analysis tool for inserting sparklines in Excel 2016, you have a chance to preview how your data will look with each type. Just hover the pointer over the respective icon. Customize Excel mini chartsOnce you create sparklines, you may notice that an additional tab appears on the Ribbon. Here you can find a wide range of tools that'll let you customize your mini charts. So let's make sparklines stand out a bit. Before you start modifying your mini charts, I should mention that when you create a range of sparklines, Excel puts them in a group. As a result it is not necessary to highlight all of the sparklines when you want to modify them. Change one and you will change them all. Show pointsSince sparklines are so small, the default formatting applied to them by Excel makes it difficult to identify which values are the highest and lowest points, especially in the line mini chart. Follow the simple steps described below to see how to emphasize the highs and lows of Excel sparklines:
The sparklines look more readable with the high and low points marked, don't they? You can also select First Point and Last Point to clearly mark the beginning and the end of the line. If you check the box next to Markers, every point will be highlighted, which can make your sparklines look a bit cluttered. Showing Negative Points will be useful in case you have negative values in your data. Change the sparkline type I've already mentioned that there are three different types of sparklines. You can easily switch between Line, Column and Win/Loss charts. Just click on a sparkline to activate the DESIGN tab and select the desired type in the Type group. Modify the style of sparklinesLet's go on and give a different style to the mini charts.
If you want to add a specific style to a sparkline, use the Sparkline Color and the Marker Color commands in the Style group. The first option allows you to change the color of lines or bars in your mini chart. The second one makes it possible to highlight the most important points using different colors. Customize Axis settingsOne more fact you should know about sparklines is that by default they are scaled to fit the cell size. It means when you adjust row-height or column-width of the cell containing a sparkline, the size of the sparkline changes too. It sounds good, but it can be misleading. Let me explain why. For example, look at the sparklines in cells G3 and G4 on the screenshot below. You see that their maximum and minimum values look the same. If you turn to the source data, you will see that the numbers are different. Luckily there is a solution to this problem:
Now it is easy to compare one sparkline to another. You can find some more useful options in the Axis drop-down menu. If you click on the Custom Value option, it will let you set your own minimum and maximum values for the vertical axis of a sparkline or a sparkline group. You can also use the Plot Data Right-to-Left option to change the direction in which data are plotted in a mini chart. If there are negative values in your data, choose Show Axis to display the horizontal zero line in your sparkline. You can select Date Axis Type to format the shape of the chart in a sparkline to reflect any irregular time periods in the source data. Show empty and hidden cellsAs far as you already know sparklines are usually used for displaying numeric values. What if you have empty cells in the data source? Don't worry and see below how to control the way a sparkline displays blanks.
Here you can also check the box next to Show data in hidden rows and columns to display such values in your sparkline. The Edit Data option also allows you to change the location and data source for a sparkline group or a single mini chart. We examined in depth how you can customize your mini charts and the time has come to know how to delete sparklines from your Excel worksheet. Remove sparklines from Excel cellsIf you want to get rid of a sparkline, the Delete button won't help with this task. You should use one of the following methods:
In this article I tried to cover all the bases of creating and customizing sparklines in Excel 2010 - 2016. Now you know how to insert a sparkline; change its type, style and color; highlight the most important points and remove sparklines from Excel cells. If I've forgotten to say anything about this useful feature, let me know in the comments. Thank you for reading! You may also be interested in |