Charts and graphs are ideal ways to visually display data. But you may have a spreadsheet without the space for a large chart or simply want a small graph to see data at a glance as you work on it. Sparklines in Excel are perfect for these situations. This tutorial shows how to insert Sparklines into an Excel spreadsheet and customize them to your needs.
What Is a Sparkline in Microsoft Excel?
A sparkline is a mini chart that resides in a single cell. Normally, a sparkline is a line chart that gives you a quick view of the highs and lows or trends in a dataset. However, Excel also offers a column chart to show comparisons and a win/loss graph to display positive and negative values.
Like other charts in Excel, a sparkline automatically updates as you change your data, and you can customize this tiny chart to fit your data or theme.
Tip: read on to learn how to transpose the data in your spreadhseet.
If you’re ready to take sparklines for a spin in Excel, let’s start with how to insert one in your spreadsheet.
How to Insert a Sparkline in Excel
Before you add a sparkline to your sheet, be sure you have your data positioned in a column or row, then follow these steps.
- Select an empty cell at the bottom of a column or end of a row of data where you want the chart. Normally, a sparkline resides alongside its data.
- Go to the “Insert” tab, click “Sparklines,” and choose the type of chart you want to use: “Line,” “Column,” or “Win/Loss.”
- When the Create Sparklines box opens, enter the cell references in the “Data Range” field. Alternatively, you can drag your cursor through the cell range to populate that field.
- Confirm the location of the cell where you want the sparkline in the “Location Range” field and change it if needed, then click “OK.”
- You’ll see the sparkline appear in the selected cell.
Good to know: working on your budget? Take a look through these nine free Excel templates to help manage your budget.
How to Change Sparkline Types
If you add a sparkline and decide a different type would work better, you can change it with a click. For instance, maybe you inserted a line sparkline but believe a column layout would be more effective.
Select the sparkline and go to the “Sparkline” tab that appears. In the “Type” section of the ribbon, choose the sparkline you want to switch to.
You’ll see your sparkline immediately replaced with the new type.
FYI: have you run into the “Not enough memory to run Microsoft Excel” error in Windows? We can help you get past it.
How to Group Sparklines
If you want to group sparklines together, you can make changes and have them apply to all sparklines in the group. This saves you the time of adjusting the sparklines individually.
One thing to keep in mind is that all sparklines in a group must be the same type: line, column, or win/loss. If they aren’t, the subsequent sparklines you select are automatically converted when you group them.
- To group sparklines, select the first one, hold Ctrl on Windows or Command on Mac, then select the remaining sparklines you want in the group.
- Go to the “Sparkline” tab and select “Group” in the “Group” section of the ribbon.
- Any time you select one sparkline in the group, the others are automatically selected at the same time. Again, any adjustments you make apply to all sparklines.
If you want to return to controlling the sparklines individually, ungroup them. Select a sparkline in the group and click “Ungroup” in the “Group” section in the “Sparkline” tab.
Tip: is your data saved in a PDF instead of Excel? Learn how to convert a PDF to an Excel spreadsheet.
How to Customize a Sparkline
Once you add a sparkline to your sheet, you can apply emphasis, adjust the axes, or spruce up the chart’s appearance. Select the sparkline and go to the “Sparkline” tab, then follow the instructions below to customize it.
Edit Sparkline Data
In the “Edit Data” drop-down menu, you can change the data range, the location of the sparkline, or choose how to display hidden or blank cells.
- Choose “Edit Group Location & Data” to change a group of sparklines or both the data range and location of a sparkline. Alternatively, choose “Edit Single Sparkline’s Data” to change the data range of an individual sparkline.
- Select “Hidden & Empty Cells” to choose how to handle these types of cells. You can show them as gaps or zero and connect data points with the line (for a line sparkline).
Show Points and Markers
In the “Show” section of the ribbon, check the boxes for those points on the chart you want to highlight.
We are checking the box for “High Point.” The two largest identical values change to being highlighted in the column sparkline.
In this example, we are marking “First Point” and “Last Point” in our line sparkline. Those two points change to being highlighted.
Style a Sparkline
In the Style section, you can change the color scheme for the sparkline as well as the colors for the points and markers (in the Show section).
After checking the “Markers” box for our line chart, we can change the color of those markers using the drop-down box.
This example has a win/loss sparkline. The “Negative Points” box is automatically checked to show negative values in a different color. But we are also selecting a different color scheme from the “Style” box.
Additional Actions for Sparklines or Groups
In the Group section, you can adjust the axes and remove the charts.
We are using the “Axis” menu to display the horizontal axis for our win/loss sparkline for a cleaner look.
In this example, we are setting a “Custom Value” for the vertical axis on our column chart to better display those columns.
If you want to remove sparklines or groups from your sheet, select the sparklines or groups you want removed, then open the “Clear” menu and choose either “Clear Selected Sparklines” or “Clear Selected Sparkline Groups.”
Good to know: want to whip through your work more efficiently? Learn these Microsoft Excel Keyboard Shortcuts.
Frequently Asked Questions
Is there a sparkline function and formula?
Because Excel has a built-in sparkline feature, there isn’t currently a function or formula to insert one. If you use Google Sheets in addition to Excel, there is a sparkline function in Sheets. The syntax is SPARKLINE(data, options), where you enter the cell range for the first argument and the chart type and attributes for the second. If you’d rather stick with Excel, check out these useful advanced Excel formulas.
Why are my sparklines not showing in Excel?
Sparklines were introduced with Excel 2010 and available in later versions, including Excel 365. However, if you’re using any other version of Excel, you may be working in Compatibility Mode, which would require you to change the file type to see your sparklines. To learn how to check for compatibility and change the file type, visit the Microsoft Support page.
Image credit: Pixabay. All screenshots by Sandy Writtenhouse.
Our latest tutorials delivered straight to your inbox