[Solved:] Vary Colors by Point Is Not Available in Excel

In Micorsoft Excel, graphs and charts are great tools for visualizing and analyzing statistical data. By adding colors to the data bars of the charts, the visualization becomes more pleasing. Sometimes, users cannot find the options to Vary colors by point. This option of an Excel chart displays the bars of a chart in different colors. In this article, we will show you how to solve the Vary colors by point is not available in Excel error.

Table of Contents hide

Download Practice Workbook

2 Suitable Solutions If Vary Colors by Point Is Not Available in Excel

Solution 1: Removing Data Series from Chart

Solution 2: Applying VBA Code

Conclusion

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Vary Colors by Point.xlsm

2 Suitable Solutions If Vary Colors by Point Is Not Available in Excel

In the following description, you will see two different and easy solutions of the Vary colors by point that is not available in Excel error. In our first method, we will remove data series from the Excel chart if there is more than one. Because this option is only available if there is only one data series in the chart. Secondly, we will apply a VBA code to solve this problem.

To illustrate the article, we will use the following data set. Here are the salaries and ages of some random people. By using this data, we will create a chart and find the solution to the above-mentioned problem.

Solution 1: Removing Data Series from Chart

For our first solution, we will remove data series from a chart if there are multiple data series. Because this option will only be available in the chart containing one data series. The steps for this procedure are as follows.

Step 1:

First of all, select the data range B4:D10.

Step 2:

Secondly, go to the Insert tab of the ribbon.
Then, from the Charts group, select Insert Column or Bar Chart.
From the dropdown, choose Clustered Column.

Step 3:

Thirdly, you will see the column chart after selecting the previous step’s command.
Here, name the chart as Salary Vs. Age.
Then, double-click on any of the bars inside the chart.

Step 4:

Fourthly, the Format Data Point window pane will open.
But, the Vary colors by point will not be available under the Fill tab in the pane.
Hence, we will bring back the options in the following steps.

Step 5:

Fifthly, come back to the previously created chart again.
Then, place the mouse on the chart and right-click.
From the options, choose Select Data.

Step 6:

Sixthly, you will see the Select Data Source dialog box.
Then, under the Legend Entries (Series), you will see two data series.
From there, remove one of the series by selecting it and pressing Remove.
Lastly, press OK.

Step 7:

In the seventh step, you will see the chart with only one series.
Then, double-click on any of the bars from the chart.

Step 8:

Here, the Format Data Point window pane will appear again.
This time, under the Fill tab, you will see the Vary colors by point option available.
Then, enable the option by marking it.

Step 9:

Finally, after enabling the option, the column chart will look like the following picture.

Similar Readings

Solution 2: Applying VBA Code

In our second approach, we will apply a VBA code to make the Vary colors by point option appear, and after that, it will enable the option. By inserting the proper sequence and command into the code, we will accomplish our task. For a better understanding, go through the following steps.

Step 1:

First of all, make a Clustered Column chart using the data from the cell range B4:D10 just like the previous method.

Step 2:

Secondly, double-click on any of the data bars from the chart to call up the Format Data Point window pane.
Here, you will see the Vary colors by point option is not available.
To solve the issue, we will now apply a VBA code.

Step 3:

Thirdly, go to the Developer tab of the ribbon and, from there, choose Visual Basic.

Step 4:

Fourthly, you will see the Visual Basic window after selecting the previous command.
Then, from the Insert tab of the window, choose Module.

Step 5:

Fifthly, copy the following code and paste it into the module.

'Addressing the sub procedure
Sub Vary_Colors_by_Point_Not_Available()
'Selecting the desired chart
ActiveChart.PlotArea.Select
'Deleting the unwanted data series
ActiveChart.FullSeriesCollection(2).Delete
ActiveChart.FullSeriesCollection(1).Select
'Activating vary colors by point options after deleting data series
ActiveChart.ChartGroups(1).VaryByCategories = True
End Sub

VBA Breakdown

Firstly, we are naming the Sub Procedure Vary_Colors_by_Point_Not_Available.

Sub Vary_Colors_by_Point_Not_Available()

Secondly, we select the desired chart’s data bar.

ActiveChart.PlotArea.Select

Thirdly, we are deleting the unwanted data series from the chart.

ActiveChart.FullSeriesCollection(2).Delete
ActiveChart.FullSeriesCollection(1).Select

Finally, we select and enable the Vary colors by point to see the desired result.

ActiveChart.ChartGroups(1).VaryByCategories = True

Step 6:

Sixthly, save the code after pasting it.
Then keep the cursor in the module and press F5 or the Run button to run the code.

Step 7:

Finally, after running the code, check the Format Data Series window pane again.
Here, under the Fill tab, the Vary colors by point option is available and has been enabled through the code.

Step 8:

Consequently, the column chart will look like the following image after enabling the option.

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to find a solution for the error Vary colors by point is not available in Excel by using any of the above-mentioned solutions. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us a moment to solve your issues, and we will reply to your queries with the best possible solutions ever.