How to Add Sparklines in Excel? A Step-by-Step Guide
(Note: This guide on how to add Sparklines in Excel is suitable for all Excel versions including Office 365)
Excel can be used to represent data pictorially. For that purpose, different types of graphs and charts are available. But, graphs and charts occupy a lot of space and make your representation look a bit stuffed.
One way to represent multiple data and observe the trajectory of the data points is by using the sparklines.
In this article, I will tell you how to add sparklines in Excel. Additionally, you’ll also learn how to customize them based on your needs.
You Will Learn:
- What are Sparklines in Excel?
- Types Of Sparklines
- How to Add Sparklines in Excel
- How to Delete Sparklines in Excel
- How to Customize Sparklines in Excel
Related Reads:
Excel Quick Analysis Tool – The Best Guide (5 Examples)
How to Change the Margins in Excel? 2 Useful Ways
How to Spell Check in Excel? A Step-by-Step Guide
What are Sparklines in Excel?
Sparklines are miniature versions of the typical graphs you find in Excel. They are primarily used to show and ascertain the trends in data. They can also be used to represent the rise and fall of data and highlight minimum and maximum values, show trends and patterns based on your data.
Sparklines are also dynamic. That is, when you change the original data in the cells, the sparklines also change automatically.
Even though they have very few customizations and functionality compared to regular graphs, they make the data easy to understand and also make them visually pleasing.
Types Of Sparklines
There are three common types of Sparklines, namely:
Line: Line sparklines are one common representation of sparklines. They are similar to the line graphs but appear on a miniature scale inside the cell. Excel defines the X and Y axis, then plots the points on the axes and connects them using straight lines.
Column: Column sparklines are similar to bar graphs. Specifically, in sparklines, they are represented vertically. They represent the data in bars/rectangles and show the corresponding proportional values in terms of varying heights.
Win/Loss: This sparkline is also similar to the column sparkline. But, they are mostly used to represent positive and negative values in the data. If the values are positive, they are marked above the axis, and if they are negative, they are marked below the axis. Zeros are represented as empty spaces.
How to Add Sparklines in Excel
Consider an example where you have the sales data made by 5 employees over 5 years. Representing the data in a pictorial format using graphs can occupy a lot of space. In such a case, sparklines can be used to represent the data and establish a comparison between different data sets.
Let us now see how to add sparklines in Excel.
- First, select a cell where you want to display the sparklines. This acts as the destination cell.
Note: Unlike charts/objects, sparklines snap to the dimensions of the cell. To maximize or minimize the size of the sparkline, you’d have to expand the column width or row height of the corresponding cells.
- Now, navigate to the Insert Tab. Under the Sparklines section, you will find the different types of sparklines.
- Choose the suitable sparkline to represent your data. You can choose between the Line, Column, or Win/Loss sparklines. In this case, let us choose the Line sparkline to depict the data.
- This opens the Create Sparklines dialog box. In the Data Range text box, manually enter or select the range of cells containing the data to depict. In this case, we want to create a sparkline for the data in cells B6 to F6.
- The Location Range text box holds the address of the destination cell. In case, you want to change the place of the destination cell, you can enter a different address here.
- Click OK. This creates a sparkline for the selected data in the destination cell.
- Now, you can use the drag handle to create sparklines for the rest of the data easily.
Note: You can also enter data into the cells which house the sparklines.
Suggested Reads:
How to Reduce Excel File Size? 7 Proven Ways
How to Calculate Time in Excel? 2 Distinct Types
How to Merge Excel Files? 5 Proven Ways
How to Delete Sparklines in Excel
If you have created a sparkline for the wrong data or feel like you don’t need it anymore in your sheet, you can choose to delete it easily.
- To delete a sparkline, first, click on the respective cell which houses the sparkline.
- Now, navigate to the Sparkline tab in the main menu ribbon.
- Click on the dropdown from Clear.
- If you wish to delete a single sparkline, click on the Clear Selected Sparkline option. If you want to delete multiple sparklines, select the Clear Selected Sparkline Groups option.
How to Customize Sparklines in Excel
Highlighting Data Points in Sparklines
When you populate a sparkline, a default sparkline consisting of lines joining the data points appears in the selected cell. You can highlight the data points using options such as High point, Low point, First point, Last point, Negative points, and Markers from the Sparkline tab.
High/Low Point: Checking these options emphasizes the highest and lowest points in the dataset.
First/Last Point: By checking these checkboxes, Excel highlights the first data point and the last data point in the graph.
Negative Points: If you have any negative data points, you can emphasize them all at once with this option.
Markers: Checking the Markers option, enable and highlight all the data points in the sparkline.
Change the Color
When you populate a sparkline, they appear with the default black color. In case you want to change the sparkline color.
- First, select the sparkline.
- Click on the Sparkline Color option under the Sparkline main menu tab.
- If you want to change the marker color in the sparklines, click on the dropdown from Marker Color and choose the preferred color of your choice.
To Change the Sparkline Type
You can also change the style of the sparkline, even though you have populated the sparkline in a cell.
- First, select the cells where you want to change the style.
- Go to the Sparklines main menu ribbon.
- Under the Type section, choose the sparkline you wish to change to.
By doing this, you can change the sparkline type of your choice.
Also Read:
How to Remove Tables in Excel? 3 Easy Ways
How to Select Multiple Cells in Excel? 7 Simple Ways
How to Insert Image in Excel? 3 Easy Ways
Frequently Asked Questions
What are Sparklines in Excel?
Sparklines are miniature graphs used to depict data in Excel. They generally occupy a cell and can be used to show the differences in data and also to compare multiple data. They are of three types: Line, Column, and Win/Loss sparklines.
How to Add Sparklines in Excel easily?
First, select the cells containing the data. Navigate to the Insert tab and under the Sparklines section, choose the selected sparkline of your choice.
Final Thoughts
Sparklines are a useful feature in Excel that can help you represent the data graphically when graphs or charts cannot be used. They also aid you in depicting multiple data and establishing a comparison between the data.
In this article, we saw how to add sparklines in Excel in an easy way. We also saw how to add customizations to the existing sparkline and delete them.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 150+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
