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

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft 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.

## Vary Colors by Point Not Available in Excel: 2 Possible Solutions

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.

### 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:

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.

Read More:Â How to Show Coordinates in Excel GraphÂ

## 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.

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.

## Related Articles

<< Go Back to Formatting Chart in ExcelÂ | Excel ChartsÂ |Â Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF