Conditional Formatting of an Inventory Graph

**excelguruwannabe** · 07-25-2018, 09:40 AM

Hi,

I am new to the forum, so please excuse any formatting errors.

I am attempting to visualize language technology inventory data through a graph that will depict both the languages and the technology that language has. However, just as important as what technology the language has, is what technology it doesn't have. I would like the graph to depict (equally) both the null values and the actual values. This becomes important to demonstrate "gaps" in more the common applications. Conditional formatting can be used to depict the null values with a specific fill (theoretically at least). I have attempted this many times (all sorts of graphs), but I can't figure out how to show the null values in equal proportion to the real values. I am attaching a workbook, and the graph is pretty typical of what I generally get.

In other words...how can I chart every category for each language, even though the value may be null? Thanks

**protonLeah** · 07-25-2018, 03:19 PM

You could use -1 vs 0 for missing

**dflak** · 07-25-2018, 05:22 PM

I think I have something for you, but it requires a bit more testing. I'll post tomorrow.

**Andy Pope** · 07-26-2018, 03:42 AM

You would need to add a new series for each missing technology.

Use a 100% stacked as the bars all need to be the same size.
Format all the missing series to be the same colour as the present ones but with a pattern file, or transparency.

Finally add another series to the secondary axis as column chart in order to label the technologies.

**excelguruwannabe** · 07-26-2018, 07:23 AM

Ben - I experimented with the -1 concept. Unfortunately, it made the chart less intuitive. When you multiply that by all the languages in the world, I think it would be very messy chart. For the record I also toyed with the idea of having it a at 0.5 vs 1, and got the same thing - uneven categories that make you strain to understand what is going on. Thanks though.

**excelguruwannabe** · 07-26-2018, 07:35 AM

Andy,

That is absolutely brilliant and is working exactly as hoped. I thought it would be a 100% stacked bar, but I couldn't get it working right. I will mark this as solved, but I want to wait for a bit to see if dflak comes up with something different (not sure if marking it solved will prevent future posting). Of course, that is only step 1, now I get to try to visualize it into something "pretty."

Thanks!

**dflak** · 07-26-2018, 10:16 AM

As others pointed out, you can't chart what isn't there. If you want equal bars, you need equal values. The attached uses VBA to set the series colors.

First I converted the data range into an Excel Table called Table_Language. Tables know how big they are, so if you add rows to this table, the chart (which is built on the table) will pick them up automatically. You can also add columns, and I will explain how to do that below.

I have a helper column in Column G called series. This column has the formula =1. We need equal bars, so we need equal values. The chart, itself, is built from this column. The colors of the bar segments comes from Table_Language.

On the Series Color sheet, there is a table. Column A is actually irrelevant, but is filled in with explanatory information. If you are going to add a column to Table_Language, you must add a corresponding row to this table.

Highlight the Cell in Column A and fill it with the color you want for that series. Missing is the color you want to plot if the value in Table_Language is zero. Then assign colors to the other series in the chart by filling the cell with the color you want to use for that series. Column B is calculated for you. It is the color index. Don't change it. This is also an Excel Table called Table_Color. The formula in column B will copy down automatically if you add a new series. If you don't like the colors I've chosen, feel free to fill in with new ones.

Develop your table of languages and click on the Chart It button.

You can hide the helper column, Column G. You can also hide the Series Color Sheet.

**dflak** · 07-26-2018, 10:33 AM

Use this version. it has a slight modification that gets the legend to match up with the selected colors.

**excelguruwannabe** · 07-26-2018, 02:20 PM

dflak - This works as well. I won't pretend to understand VBA, but the result is undeniable (and more importantly exactly what I was hoping for. Thank you for you help.

Conditional Formatting of an Inventory Graph

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Re: Conditional Formatting of an Inventory Graph

Thread Information

Users Browsing this Thread

Similar Threads

Opening xlsm files with conditional formatting opens with removed conditional formatting

[SOLVED] Color scale conditional formatting in a bubble graph

Multiple Series Conditional Formatting in a Line Graph

[SOLVED] Conditional formatting bar graph with multiple x-axis values

Conditional formatting for a chart - Column graph

Conditional Formatting a data series in a graph.

Conditional formatting and Graph question

Bookmarks

Bookmarks

Posting Permissions