Microsoft Excel provides various kinds of charts for performing operations. We can also add additional plug-ins like Kutools to increase the performance of the task in Excel. It provides plenty of features.
Most of the charts in Excel use their colors for the bars, columns, and scatter in the chart. It uses only the default colors. Sometimes, we may feel bored with these default chart colors. We have to use the Kutools utility to change chart color, which provides a special feature called “Change Chart Color According to Cell Color.” Let’s see how to use it in Excel.
Jump To
What is a Column Chart?
A Column chart is a graph that uses vertical bars to represent each category. It is also known as a vertical bar chart. You can use this chart to compare values across categories. It is one of the commonly used charts in Excel because it is easy to understand and simple to create.
Change Chart Color According to Cell Color using Conditional formatting
Here are the steps to change chart color according to cell color in Excel:
- Step 1: Select the data range and create a bar chart.
- Step 2: Select the cells on that you want to base the chart color on.
- Step 3: Click on the “Conditional Formatting” button in the “Home” tab of the Excel ribbon.
- Step 4: Select “New Rule” and choose the option “Use a formula to determine which cells to format.”
- Step 5: In the formula box, enter the formula “=CELL(“color”, A1)=1” (assuming A1 is the first cell in the selected range). This formula checks if the background color of the cell is Orange.
- Step 6: Click on the “Format” button and choose the desired color for the cell. Click “OK” to close the “Format Cells” dialog box.
- Step 7: Click “OK” to close the “New Formatting Rule” dialog box.
- Step 8: The cell color should now change according to the condition you set.
Change Chart Color According to Cell Color using VBA
Here are the steps to change chart color according to cell color in Excel using VBA code:
- Step 1: Press “ALT + F11” to open the VBA editor.
- Step 2: Double-click on the chart to open the “Chart Events” window.
- Step 3: Copy and paste the following code:
Private Sub Chart_Activate()
For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
If Cells(i, 1).Interior.ColorIndex = 3 Then '3 is the code for red
ActiveChart.SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) 'set the color to red
Else
ActiveChart.SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(255, 255, 255) 'set the color to white
End If
Next i
End Sub
- Step 4: Modify the code to match the condition you set in Step 3.
- Step 5: Close the VBA editor and activate the chart.
- Step 6: The chart colors should now change according to the cell colors.
Note: This method requires some knowledge of Excel and VBA programming, so it may not be suitable for everyone.
Change Chart Color According to Cell Color
The Kutools feature “Change chart color according to cell color” is the great one. It can help you to change the fill color of the bars, columns, scatters, etc in your charts based on the corresponding cell colors in the range.
- Step 1: If you want to change the chart color, follow the below instructions.
- Step 2: For example, we have shown you the sample column chart for your reference.
- Step 3: On the Kutools tab, click on the Charts drop-down list, and select the Chart Tools option.
- Step 4: In the Sub-menu, you have to select the Change chart color according to the cell color option.
- Step 5: Then it will change the chart color according to your cell colors, as shown in the below image.
Similar Charts in Excel
- Step 1: Variable Width Column Chart – This chart displays the values across two dimensions, using column widths and column heights. It will compare two sets of data in Excel.
- Step 2: 100% Stacked Column Chart -This chart will show the relative percentage of multiple data series in stacked columns. It can display the percentage of the whole of each group and is plotted by the percentage of each value to the total amount in each group.
- Step 3: Stacked Column Chart – It will compare things across various categories. In this chart, the data series are stacked one on top of the other in vertical order. It can show the change over time because it is easy to compare total column lengths. Usually, this chart is used for a graphical representation of part-to-whole comparisons over time.
- Step 4: Column Chart With Percentage Change – This chart is only available on Kutools utility. It will show the percentage change according to the values given in the table.
Advantages
There are several advantages to changing chart color according to cell color in Excel:
- Flexibility: You can use this method for a variety of chart types and multiple conditions. This allows you to create charts that are tailored to your specific needs.
- Accuracy: Changing the chart color according to cell color ensures that the chart accurately reflects the data in the cells. This helps to prevent errors and ensures that the chart is a reliable representation of the data.
- Improved Data Visualization: By changing the chart color according to cell color, you can create a more effective and visually appealing chart. This makes it easier to understand and interpret the data.
- Automation: Using conditional formatting and VBA code, you can automate the process of updating the chart colors based on the cell colors. This saves time and effort compared to manually changing the colors.
- Customization: You can customize the chart colors to match the cell colors, which can help to create a more professional-looking chart. This is especially useful when presenting data to others.
Overall, changing chart color according to cell color in Excel is a powerful tool that can help you to create effective and informative charts that accurately represent your data.
Conclusion
In this article, we have learned how to change chart color according to cell color in Excel. Changing chart color according to cell color in Excel can be a useful way to visually represent data and make it easier to understand. By using conditional formatting, VBA code, and Kutools, you can automatically update the chart colors based on the cell colors without having to manually change them.
This can save time and effort and help to create more effective and professional-looking charts. However, it is important to note that this method requires some knowledge of Excel and VBA programming, so it may not be suitable for everyone. Nevertheless, with a little practice and patience, anyone can learn to use this technique and create impressive and informative charts. For more articles, you can visit our homepage.
FAQ
Yes, you can use the same method to change the chart color based on font color. Instead of using the Interior. Color property, use the Font. Color property in the VBA code.
Yes, you can apply this method to other chart types such as line charts, pie charts, etc. The only difference is that you may need to modify the VBA code to target the correct series or data points.
Yes, you can apply this method to multiple charts by copying and pasting the VBA code to the other charts. Alternatively, you can create a macro that applies the code to all the charts in a specific worksheet or workbook.
Yes, this method should work in all versions of Excel that support VBA programming. However, the specific steps and code may vary slightly depending on the version of Excel you are using.
Hajira is a skilled financial analyst (Microsoft Certifications Completed) with more than 10 years of expertise in Excel. I love using Excel to assist individuals and organisations in enhancing their financial procedures. I like hiking and exploring outdoors when she’s not crunching mathematics. Follow me on my Mastodon account.
