Sparklines allow you to place small charts within the cells in a worksheet in Excel. You can have your data in a row and then create a sparkling that shows the trend in that data (as shown below)
By dragging the first Sparkline to the cells below, you can quickly add Sparklines for the data below.
But, if you look closely, you will notice that all of these Sparkline charts are grouped together.
If you make a change to a single cell, you’ll notice that all Sparkline charts are changed.
But sometimes, we only need to make changes to one or a few Sparkline charts from the group. In that case, we need to first ungroup these Sparklines. Only once we have ungrouped the sparklines can we make changes in some of the sparklines (without impacting the rest of the group)
In this article, I will show some easy methods that you can use to ungroup Sparklines in Excel.
Method 1: Use Sparkline Tab to Ungroup Sparklines in Excel
When we want to ungroup Sparklines in Excel, we can use the Sparkline tab, which is the dedicated tab for Sparklines.
Below I have a data set for the monthly employee turnover of three departments of a company. In column G, Line Sparklines are added for each department data set.
Now, I want to change the last Sparkline to a Column Sparkline chart.
If I try to select the last Sparkline chart (Cell G5) and convert it to a Column Sparkline, all the Sparklines are converted to Column Sparklines.
Even though I only chose cell G5 to do the changes, all of the charts above have also been changed from line to column Sparklines.
This is because all Sparklines are automatically grouped together. You can see that the grouped Sparklines are indicated by a blue border.
To change only a selected Sparkline, I have to first ungroup that particular Sparkline from the group. To ungroup Sparklines, I can follow the below steps.
- Select the Sparkline or Sparklines that you want to ungroup from the group. If you want to ungroup all Sparklines, you have to select all cells with Sparklines. In this case, I want to ungroup the last Sparkline only (which is in Cell G5). So, I have selected Cell G5.
- Go to the Sparkline tab. This tab activates only when your active cell has a Sparkline. As I have selected Cell G5, and it contains a Sparkline, I can see the Sparkline tab on my Excel sheet.
- Go to the “Group” section (which is the last group of the Sparkline tab) and click the “Ungroup” icon.
When you click on this icon, your selected cells will be ungrouped from the group. In this case, after I click on the “Ungroup” icon, the Sparkline in Cell G5 is ungrouped from the other Sparklines.
So, when I select cell G5, I can’t see the blue border, which indicates that the Sparkline is grouped with other Sparklines.
After ungrouping, you can change the ungrouped Sparkline chart without affecting other Sparklines.
So, after selecting Cell G5, I go to the “Sparkline” tab and click the “Column” icon (Which is in the “Type” group). Now, I have changed the last Sparkline chart to a Column Sparkline.
When I click on a Sparkline above cell G5, those 3 Sparklines are still grouped together.
You can see a blue border around those cells when you select one of those Sparklines.
You can ungroup any Sparkline, even from the middle of the group.
Also read: How to Create Win/Loss Sparklines Chart in Excel?
Method 2: Use the Context Menu to Ungroup Sparklines in Excel
Without going to the Sparkline tab, we can even use the Excel Context Menu to ungroup Sparklines in Excel.
Below I have a data set for the monthly employee turnover of three departments of a company. In column G, Line Sparklines are added for each department data set.
Now, I want to change the style of the last Sparkline to yellow color
If I try to select the last Sparkline chart (Cell G5) and change the style, the style of all the Sparklines is changed too.
Even though I’ve only changed the style of one cell, G5, all Sparkline charts have changed in style.
The reason for this is that all Sparklines are automatically grouped together. You can see that there is a blue border around the grouped Sparklines.
To change only a selected Sparkline, I have to first ungroup that particular Sparkline from the group. To ungroup Sparklines, I can follow the below steps.
- Select the Sparkline or Sparklines that you want to ungroup from the group. If you want to ungroup all Sparklines, you have to select all cells with Sparklines. In this case, I want to ungroup the last Sparkline only (which is in Cell G5). So, I have selected Cell G5.
- Right-click on the selected cells to open the Context Menu. If you want to select multiple non-adjacent cells, select cells while holding down the Control (Ctrl) key. In this case, I have selected only cell G5 and right-click to open the Context menu.
- Select Sparklines from the Context Menu.
- Select “Ungroup” from the expanded list.
As soon as you click “Ungroup”, your selected cells will be ungrouped from the group.
In this case, after I click on the “Ungroup”, the Sparkline in Cell G5 is ungrouped from the other Sparklines.
So, when I select cell G5, I can’t see the blue border which indicates that the Sparkline is grouped with other Sparklines.
After ungrouping, you can change the ungrouped Sparkline chart without affecting other Sparklines.
So, after selecting Cell G5, I have to go to the “Sparkline” tab and change the style of the Sparkline. Now, I have changed the style of the last Sparkline chart.
When I click on a Sparkline above cell G5, those 3 Sparklines are still grouped together.
You can see a blue border around those cells when you select one of those Sparklines.
Also read: How to Overlay Graphs in Excel
Method 3: Use Excel Shortcut to Ungroup Sparklines in Excel
Some Excel users always like to use Excel shortcuts to do their Excel work.
If you are an Excel user who loves to use Excel shortcuts, you can use the Excel shortcuts to ungroup Sparklines.
Below I have a data set for the monthly employee turnover of three departments of a company.
In column G, Line Sparklines are added for each department data set.
Now, I want to change the last Sparkline to a Column Sparkline chart.
If I try to select the last Sparkline chart (Cell G5) and convert it to a Column Sparkline, all the Sparklines are converted to Column Sparklines.
Even though I only chose cell G5 to do the changes, all of the charts above have also been changed from line to column Sparklines.
This is because all Sparklines are automatically grouped together. You can see that the grouped Sparklines are indicated by a blue border.
To change only a selected Sparkline, I have to first ungroup that particular Sparkline from the group. To ungroup Sparklines, I can follow the below steps.
- Select the Sparkline or Sparklines that you want to ungroup from the group. If you want to ungroup all Sparklines, you have to select all cells with Sparklines. In this case, I want to ungroup the last Sparkline only (which is in Cell G5). So, I have selected Cell G5.
- Then, I have to use one of the following shortcuts.
Shortcut 1 - Menu Key + A + Enter + U
You have to enter the above keys one after the other. First, you have to press the “Context Menu” key. You can find the “Menu key” between the Right ALT key and the Right Ctrl key. Then, press A. Next, press the “Enter” key. Finally, press U.
Shortcut 2 - ALT + J + D + U
You have to enter the above keys one after the other. First, you have to press the ALT key. Then, press J. Next, press D. Finally, press U.
You can use one of the above shortcuts and ungroup selected Sparklines from the rest quickly.
Now, I have ungrouped the last Sparkline using the first shortcut.
So, when I select cell G5, I can’t see the blue border, which indicates that the Sparkline is grouped with other Sparklines.
After ungrouping, you can change the ungrouped Sparkline chart without affecting other Sparklines.
So, after selecting Cell G5, I go to the “Sparkline” tab and click the “Column” icon (Which is in the “Type” group).
Now, I have changed the last Sparkline chart to a Column Sparkline.
When I click on a Sparkline above cell G5, those 3 Sparklines are still grouped together.
You can see a blue border around those cells when you select one of those Sparklines.
Also read: How to Add a Trendline in Excel Charts?
Method 4: Use a VBA Code to Ungroup Sparklines in Excel
VBA code is another great way for you can use to ungroup Sparklines in Excel.
Below I have a data set for the monthly employee turnover of three departments of a company. In column G, Line Sparklines are added for each department data set.
Now, I want to change the style of the last Sparkline to yellow color
If I try to select the last Sparkline chart (Cell G5) and change the style, the style of all the Sparklines is changed too.
Even though I’ve only changed the style of one cell, G5, all Sparkline charts have changed in style.
The reason for this is that all Sparklines are automatically grouped together. You can see that there is a blue border around the grouped Sparklines.
To change only a selected Sparkline, you have to first ungroup that particular Sparkline from the group. You can apply the below code for that.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Ungroup_Sparklines()
Selection.SparklineGroups.Ungroup
End Sub
To run the above VBA code, follow the below steps.
- Press ALT + F11 to open the VBA Editor. You have to press all the keys together. If the Function keys are locked, you have to use the Fn key also with ALT + F11. Then, you can see the VBA Editor dialog box.
- Go to the Insert tab of the VBA Editor dialog box.
- Select “Module” from the expanded list.
- Enter the above VBA code.
- Select the Sparklines that you want to ungroup. In this case, I want to ungroup the last Sparkline from other Sparklines. So, I have selected the last Sparkline which is in Cell G5.
- Go to the VBA Editor and press the “Run Sub” button (Green triangle). You can also press the F5 key to run the VBA code.
As soon as you run the VBA code, your selected cells will be ungrouped from the group. In this case, after I click on the “Ungroup”, the Sparkline in Cell G5 is ungrouped from the other Sparklines.
So, when I select cell G5, I can’t see the blue border which indicates that the Sparkline is grouped with other Sparklines.
After ungrouping, you can change the ungrouped Sparkline chart without affecting other Sparklines.
So, after selecting Cell G5, I have to go to the “Sparkline” tab and change the style of the Sparkline. Now, I have changed the style of the last Sparkline chart.
When I click on a Sparkline above cell G5, those 3 Sparklines are still grouped together.
You can see a blue border around those cells when you select one of those Sparklines.
You now know four different methods to ungroup Sparklines in Excel. Every method gets the same result.
So you are free to choose whichever method you choose.
Use Cases Where You May Want to Ungroup Sparklines
There are situations where you might want to manipulate or manage your sparklines separately.
Here are a few scenarios where you might want to ungroup sparklines in Excel:
- Changing Data Source: If you need to change the data source for each sparkline individually, you might need to manage them separately. For example, if you initially set a group of sparklines to represent a series of data, but now you need each sparkline to represent a different series, you’d have to adjust each sparkline’s data source individually.
- Modifying Sparkline Type: If you want to use different types of sparklines (Line, Column, or Win/Loss) for different cells, you would need to adjust them individually. For instance, you might want to display one row of data as a Line sparkline and another as a Column sparkline.
- Customizing Sparkline Style: Each sparkline can have its own style. If you want to customize the color, weight, or other style aspects of each sparkline individually, you would need to manage them one at a time.
- Differing Sparkline Settings: If you want to control the minimum and maximum values for the sparkline axis differently for each sparkline, you would need to manage them separately. For example, if you have some sparklines where the absolute values are important and others where only the trend matters, you’d need to manage these settings on a per-sparkline basis.
Other Excel articles you may also like: