The easiest way to analyze information in a worksheet is to create Sparklines in Excel.
Sparklines are tiny charts generally about the size of the cell, that tell you about the pattern or variation of data in a row. They can be a line, column or win/loss chart.
You can use the quick analysis tool to quickly create Sparklines or apply the command on the ribbon in the Insert tab.
In this post, you will learn how to create Sparklines in Excel using two ways.
Types of Sparklines?
There are three different types of Sparklines in Excel: Line, Column and Win/Loss.
An example of each Sparkline is shown in the image below:
How to Create Sparklines in Excel
There are two ways you can create Sparklines in Excel. You can either use the quick analysis tool or the command on the ribbon.
Let’s take a look at the two ways to create Sparklines in Excel.
1. Using the Quick Analysis Tool
To do this, follow this steps:
1. Select the entire data and click on the Quick Analysis tool below
2. Click on Sparklines and select the chart type.
Sparklines will automatically insert the charts into the cells at the end of the row like this below:
2. Create Sparklines using the Ribbon
To create a Sparkline using the ribbon, you must select the data range you want to analyze and select cell location where you want the Sparklines to be.
It has greater effect when it is placed beside the data it represents.
To do this:
1. Select the data range for the Sparklines
You may select the entire data range or just the data range for the first row if you are inserting different types of Sparklines.
2. Go to Insert tab
3. Click on any of the Sparklines to select the type of Sparklines you want to insert.
4. In the Create Sparklines dialogue box, click on Location Range to enter the cell location to insert the Sparkline. In this example, cell H3.
Notice there is already a range in the data range. This is because it was selected in the beginning of the step.
5. Click Ok.
Now, the Sparkline has been inserted in the cell location you entered.
You can click on the drag and fill button to apply the Sparkline into the other cells.
Format Sparkline Chart
Once you have inserted Sparklines into the cells, you can then format it to add some more details.
Here are the formats you can apply to the Sparklines.
1. Click on a Sparkline chart to go to the Sparkline Design tab
- Select Line, Column or Win/Loss to change the chart type
- Check Markers to highlight the values in the Sparkline chart
- Select a Style for the Sparkline
Click on the dropdown arrow at the side to see more styles.
- Select Sparkline Colour to apply any colour theme
- Select Sparkline Colour –> Weight to increase the width of the Sparkline
- Select Marker Colour to change the colour of the markers.
- If the data has positive and negative values, select Axis to show the axis.
How to Treat Empty or Zero Value Cells
If there are cells with zero or Null values, you can change how they are displayed in the chart by:
1. Click on a Sparkline Chart
2. In the Sparkline Design tab, click on Edit Data and select Hidden and Empty cells.
3. Select the option that you want and click Ok.
There are two ways to delete Sparklines.
- Right-click on the Sparkline you want to delete, go to Sparklines and click on Clear Selected Sparklines.
1. Click on the Sparkline you want to delete
2. Click on Design tab and click on the dropdown arrow next to Clear, then select Clear Selected Sparklines.
Did you find this information helpful?