New Sparkline Features in MS Excel 2010

In this article, we will learn New Sparkline Features in MS Excel 2010.

What is Sparkline ?

Since this features was wanted by many users, the sparklines for excel, were created with easy use options. The mini graphs look best when placed at the end of the data columns. So choose the last column of the spread sheet to insert the sparklines. Choose the sparklines to be lines or columns or win/loss representations.

A dialog box pops up where you have to enter the data range. Select the cells containing data that need to be represented in the sparkline miniature graphs. In the location range, select the cells in which the mini graphs need to be shown.

Once you hit the ok button, you might see weird graphs not showing the right representation. In that case, got to Axis option and set it at General Axis type for the horizontal axis and Automatic for Each Sparkline for both vertical maximum and minimum.

After insertion, the style, colours and other visual appeal can be changed easily. Features such as high points, low points negatives, values, etc help to highlight the particular data that you want to visually highlight for the viewer. You can also change the line or bar’s colour.

… Function in Excel

The Sparklines are the small charts that reside in a single cell. Sparklines are used to show trends, improvement and win loss over the period. The sparklines are charts but they have limited functionalities as compared to regular charts. In spite of its limitations, sparklines provide features like highlighting, min and maximum points, first point, last point and negative points.There are many other features sparkline provides which we will explore in this article.

Since the introduction of sparklines in Excel 2010 it has evolved. I am using excel 2016 and I will explain these in cell charts in this version.

How To Insert Sparkline Chart in Cell

Select the cell in which you want to insert these tiny chart.
Go to Insert->Sparkline->
Click on chart type you want.
Create Sparkline dialog will open. Select, data range. It should be one dimensional.
Hit OK.

Now you have your sparkline chart in one single cell.

Some points you should know about these in cell charts.

Sparklines are Dynamic. They change as data changes.
They are fast and light.
You can write in sparkline cell.
There is some customisation available for sparkline charts.
Sparklines’s size is relative to cell size. If you change the cell size, chart size will also change.
You can’t delete sparkline by simply pressing Delete button. You need to delete the cell itself.

Types of Sparkline Chart in Excel

There are three types of Sparklines in Excel.

Line Sparkline: The first Graph in above image is a line chart. This is best used for showing on going progress.

Column Sparkline: The second chart, is a column chart in a cell. The column sparkline is best to show comparative data. All positive value are shown above the axis and negative value are shown below the axis.

Win/Loss Sparkline: The third chart in image is Win-Loss Spark: Any positive value is show above the axis, and any negative value is show below the axis. A zero value is not shown. I have checked negative points to show.

Note: The column and win loss sparkline may look similar but they are different. The win-loss sparkline does not reflect magnitude or values in charts. It just shows the binary values. Everything positive is one and everything negative is -1.

In the above image, the win-loss sparkline shows that if we have done better in 2019 or worse as compared to 2018.

Highlight Points in Sparkline

It is common but important to highlight points in graphs, so that you can grab attention of management and help make better decisions. The sparkline in excel provides this feature too. You can highlight these below points in these in-cell charts.

High Points
Low Points
Frist Point
Last Point
Negative Points
Markers (only for line).

High Points: When you check high point, the point with maximum value is highlighted.

Low Points: Highlights the lowest point in the sparkline.

First Point: Highlights the first point of the data in sparkline.

Last Point: Highlights the last point of the data in sparkline.

Negative Points: Highlights all negative points in data.

Markers: In a line sparkline, it highlights all the points as dots.

Handling Empty Cells in Sparkline

By default, If your data contains empty cells, the sparkline is shown broken, if its a line sparkline. Other sparkline just don’t show that point. Hidden cell points are ignored and not shown in the graph.

Excel provides option to handle these cases.

Select the sparkline.
Go to Design.
In the left corner, click on Edit Data.
Then. click on “Hidden and Empty Cells”.
You will see three options, Gaps (already selected), Zero, Connect Data Points with Line.
If you select zero, the data point will drop to zero. You’ll not see that gap in sparkline. If you select “Connect data points with line”, the last point will be connected to the next non blank cell. The blank cell will be ignored.
There is a check box of “show data in Hidden rows and columns”. If you check this, all hidden cell points will be shown in the sparkline graph.

You can clearly see, the blank cell is ignored here. The excel sparkline is only showing four points.

Axis in Sparkline

By default, all the points in the graph shown relative to smallest point in sparkline. For example in below data, 2147 is the smallest point. You can see, it is shown to low on graph. But it, itself a large point.

You may not want this to happen. You would like set an axis as 2000, as all values are above this value. Excel gives you the option you to set lowest or highest point of sparklines.

Follow these steps to edit Axis of Sparkline:

Select the sparkline
Go to Design Tab
In the right most corner, you’ll find Axis. Click on the Drop Down.
In Vertical Axis Minimum Value Option, click on Custom Value…
Write the minimum value you want to set. I write 2000. (any value less then this value will not be shown in graph).
Hit Ok.

You can see the change now.

Change Data Source And Location of Sparkline

So, if you want to change data source or location, you can do that too.

Go to the Design tab.
Click on Edit Data. The first two options are used to change data and location.
If you want to change the location of sparkline, click on Edit Group Location & Data.

If you just want to edit sparkline’s data, click on the second option instead.

Sparkline Formatting

There is some scope of formatting the sparkline charts.

How to change sparkline type?

To change a sparklines type follow these steps.

Select the sparkline.
Go to the design tab. In type, select the sparkline type of three available options.

How to change sparkline style?

Sparklines have some predesigned styles. You can change the design of sparklines following these steps.

Select the sparkline
Go to design
Go to the style section, click on drop down, and select your favorite option.

How to change sparkline’s color and marker color?

In design, you can see sparkline color and marker color.
Click on them. Choose the appropriate color.

And it’s done.

How to group sparklines?

To group two or more sparklines in excel, select them.
Go to Design tab.
In the right most corner. Click on Group.

Once you group the sparklines, all of them will be formatted same as the upper-left sparkline.

How to Delete a Sparkline?

You can’t delete the sparkline by simply pressing delete key on keyboard. To delete a sparkline chart follow these steps.

Select the cells containing sparklines.
Go to Design tab.
In right most corner, find clear. Click on it. The selected sparkline will vanish.

You can also delete the cell, column or row that contains the sparkline to delete it. Just make sure it doesn’t affect your data.

So, this is all about the sparkline in Excel. If you have any sparkline tricks under your sleeves, please do share with us in the comments section below.

Notes :

Sparklines created in Excel 2010 won’t show in earlier version of Excel. That is, if you are using 2010 version and your client is using 2007 version, he or she cannot view these sparklines at all as the earlier versions do not support the new sparkline features.

That is the only real problem. Therefore, as an added option, using third party softwares to create sparklines for Excel might be a feasible option until more people are using Excel 2010 in majority.

Hope this article about New Sparkline Features in MS Excel is explanatory. Find more articles on Excel charts and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles:

Best Charts in Excel and How To Use Them : These are some of the best charts that Excel provides. You should know how to use these charts and how they are interpreted. The line, column and pie chart are some common and but effective charts that have been used since the inception of the charts in excel. But Excel has more charts to explore…

10+ Creative Advanced Excel Charts to Rock Your Dashboard : These creative charts can make you stand apart from the crowd. These charts can be used for different types of reports. Your dashboard will be more expressive than ever.

How to Save and Use a Excel Chart Template : It is important to save chart templates for repetitive charts to save time and energy. To save chart templates in excel follow these steps.

4 Creative Target Vs Achievement Charts in Excel : These four advanced excel charts can be used effectively to represent achievement vs target data. These charts are highly creative and self explanatory. The first chart looks like a swimming pool with swimmers. Take a look.

Excel Sparklines : The Tiny Charts in Cell : These small charts reside in the cells of Excel. They are new to excel and not much explored. There are three types of Excel Sparkline charts in Excel. These 3 have sub categories, let's explore them.

Change Chart Data as Per Selected Cell : To change data as we select different cells we use worksheet events of Excel VBA. We change the data source of the chart as we change the selection or the cell. Here's how you do it.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.