How to Change Sparkline Color in Excel (with Detailed Steps)

A sparkline is a small mini-chart that appears in every cell of the worksheet. Excel 2010 and later versions introduced sparklines. The use of sparklines allows one to visualize data and illustrate trends or patterns. They are also very striking when used in dashboard creation and purposes. If you are looking for some special tricks to change sparkline color in Excel, you’ve come to the right place. There is one way to change sparkline color in Excel. This article will discuss every step of this method. Let’s follow the complete guide to learn all of this.

Overview of Sparkline

Sparklines show data patterns in a single cell in Excel. There are 3 types:

Line sparklines
Column sparklines
Win/Loss sparklines

Line Sparklines: Line sparklines are similar to line charts, and one can use them to show trends in monthly sales data – the principles governing line charts are similar to line sparklines. In the following image, we can see the Line sparklines.

Column Sparklines: Comparative analysis of data points can be performed using column sparklines, which are similar to a column chart. In the following image, we can see the Column sparklines.

Win/Loss Sparklines: A win/loss sparkline shows or categorizes each data point as either a win or a loss. This type of sparkline shows a stark contrast between win and loss points. In the following image, we can see the Win/Loss sparklines.

Sparklines are usually positioned in most spreadsheets near or right next to their data sources, as an additional visual explanation. Additionally one can create a single sparkline based on a column or row or multiple sparklines depending on one’s requirements.

Advantages of Sparkline

The advantages of sparkline are given below:

Temperatures or monthly sales data can be visualized.
The process of compacting data.
For a short period of time, a data report is generated.
Perform a trend analysis for a certain period of time
Understanding the highs and lows of a situation.

Read More: Types of Sparklines in Excel

How to Change Sparkline Color in Excel: with Detailed Steps

In the following section, we will use one effective and tricky method to change sparkline color in Excel. To create a more understandable sparkline, we first organize our dataset, then inset a sparkline, and finally customize the sparkline by changing its color and marker color. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Insert Sparklines in Excel

Here, we will demonstrate how to insert sparklines in Excel. Let us first introduce you to our Excel worksheet so that you are able to understand what we are trying to accomplish with this article. In the following dataset, we can see the different salesperson monthly sales. We can see empty values for two months for the last 2 salespeople.

Let’s walk through the steps to insert sparklines in Excel.

First of all, to insert a sparkline, select the range of cells where you want to insert a sparkline and go to the Insert tab.
Next, select Sparklines and from the drop-down menu select the Line option.

When the Create Sparklines dialog box appears, select the range of cells in the Data Range that you want to insert into Sparklines.
Next, click on OK.

As a consequence, you will get the following line sparklines.

You have to follow the same process to insert Column Sparklines in Excel.
To insert a sparkline, select the range of cells where you want to insert a sparkline and go to the Insert tab.
Next, select Sparklines and from the drop-down menu select the Column option.

When the Create Sparklines dialog box appears, select the range of cells in the Data Range that you want to insert into Sparklines.
Next, click on OK.

As a consequence, you will get the following column sparklines.

You have to follow the same process to insert Win/Loss Sparklines in Excel. In the following dataset, we didn’t show the loss value for any salesperson.
To insert a sparkline, select the range of cells where you want to insert a sparkline and go to the Insert tab.
Next, select Sparklines and from the drop-down menu select the Win/Loss option.

When the Create Sparklines dialog box appears, select the range of cells in the Data Range that you want to insert into Sparklines.
Next, click on OK.

As a consequence, you will get the following Win/Loss sparklines.
In the following image, we can see that there is no loss of value for any salesperson.

Step 2: Show Empty or Zero Values

Now, we want to show empty or zero values in Sparklines. In the following dataset, we can see we have empty values for 2 months for two salespeople. To show empty values in sparklines, you have to follow the following process.

First of all, select the line sparklines, then go to the Sparkline tab and select Edit Data. Next, select the Hidden &Empty Cells option from the drop-down menu.

When the Hidden and Empty Cell Settings window appears, click on Zero to Show empty cells as a box.
Next, click on OK.

As a consequence, you will be able to show empty values in line sparklines as shown below.

Now, you follow the same process for the other two types of sparklines. As a consequence, you will get the following sparklines.

Step 3: Change Sparkline Style and Line Width

Now, we are going to change the sparkline line style including width and line style. To do this you have to follow the following process.

First of all, select the line sparklines and go to the Sparkline tab.
Next, select Sparkline Color.
Then, from the drop-down menu select the Weight option and choose your desired line width as shown below.

Afterward, if you want to change the sparkline styles, you have to go to the Sparkline tab and select your desire as shown below.

Following the same procedure, you can change the Column Sparklines and Win/Loss Sparklines style.
Therefore, you will get the following image.

Step 4: Change Sparkline Color

Now, we want to change the Sparkline Color. To change the sparkline color, you have to follow the following process.

First of all, select the line sparklines and go to the Sparkline tab.
Next, select Sparkline Color.
Then, select your desired color. Following the same procedure, you can change the Column Sparklines and Win/Loss Sparklines color.

As a consequence, you will be able to change the sparkline color as shown below.

Step 5: Change Marker Colors of Sparkline

Now, we are going to format the sparklines to format the sparkline.

First of all, select the line sparklines and go to the Sparkline tab.
Next, select Marker Color.
From the drop-down menu, select Markers and choose your desired color.

As a consequence, you will get the following image showing point markers in line sparklines.

Now, we want to show the highest point in the sparklines.
Select the line sparklines and go to the Sparkline tab.
Next, select Marker Color.
From the drop-down menu, select High Point and choose your desired color.

Therefore, you will get the following image where the highest point is highlighted as red for every type of sparkline.

Now, we want to show the lowest point in the sparklines.
Select the line sparklines and go to the Sparkline tab.
Next, select Marker Color.
From the drop-down menu, select Lowest Point and choose your desired color.

Therefore, you will get the following image where the lowest point is highlighted as green for the type of Line Sparklines and Win/Loss Sparklines, and yellow for Column Sparklines.
Finally, you will be able to format and change the sparkline color and get the following sparklines.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

Sparkline Color.xlsx

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to change the sparkline color in Excel. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!

<< Go Back to Excel Sparklines | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!