Microsoft Excel - Custom Combination Bar Chart: Data Table

[Manny74]

Hello, as I create a custom combination bar chart, I want the bars to only display the compliance rate for each department.
And when I add a data table to my chart, I only want to show the # Hand Hygiene and Compliance Rate.
When I right click on the bars, click on select data, I deselect # Hand Hygiene and # Observations, but then it also removes these items from the data table.
Is there a way where I can see the bars as Compliance Rate while the data tables shows # Hand Hygiene and # Observations?

	A	B	C	D
1	UNIT	# HAND HYGIENE	# OBSERVATIONS	COMPLIANCE RATE
2	ICU	120	200	60%
3	2ND FLOOR	155	200	78%
4	4RD FLOOR	175	200	88%
5	7TH FLOOR	195	200	98%
6	TOTAL	645	800	81%

 

[Manny74]

@Manny74 , i apologize, i mispoke, I did not create two charts. But it is a combo, a column with line chart:
View attachment 90770

No problem, awoohaw. And yes this is for leadership.

 

[awoohaw]

No problem, awoohaw. And yes this is for leadership.

your leaders like busy charts! UGH.
Charts should paint a picture that give impact at glance, not be a novel. But, you gotta do what the bosses want.

Best wishes. I hope the last image i posted gives you all that you need.

 

[awoohaw]

you could also remove the left axis title, since it is redundant, the series name is the same name as the axis, and you can't take the series name away from the data table legend.

 

[Manny74]

to "remove" the right secondary axis, change the FONT to WHITE (Or whatever color your chart background is:

View attachment 90772

This is a good trick. I like it.
But how do I remove # Hand Hygiene and # Observations from my bars, so I just see Compliance Rate?

 

[awoohaw]

This is a good trick. I like it.
But how do I remove # Hand Hygiene and # Observations from my bars, so I just see Compliance Rate?

i'm not sure what you are asking. Hand Hygiene and Observations are not included in the Columns. They are on invisible lines, with an invisible secondary axis.

 

[Manny74]

you could also remove the left axis title, since it is redundant, the series name is the same name as the axis, and you can't take the series name away from the data table legend.

So I see who I can double click on the bar for # OBSERVATIONS, click on Fill & Line, from Fill click on No fill. Then I did the same for # HAND HYGIENE. So now I see my bars only respresent COMPLIANCE RATE.
But for the data table, I only want to see # HAND HYGIENE and # OBSERVATIONS.....How do I remove COMPLIANCE RATE from the data table?

 

[Manny74]

your leaders like busy charts! UGH.
Charts should paint a picture that give impact at glance, not be a novel. But, you gotta do what the bosses want.

Best wishes. I hope the last image i posted gives you all that you need.

Agreed :\

 

[awoohaw]

That is the problem. the Data Table can't be filtered as far as I know. Maybe there is a way to change the formatting.

Are these going in power point slides as images? If so. remove the data table from the grid and put the two metrics in cells below the chart.
And then you would need to name the left axis or put "COMPLIANCE RATE" as the chart title.

 

[awoohaw]

this is the best i can do at this point, i think:

 

[Manny74]

this is the best i can do at this point, i think:

View attachment 90777

This is nice!! But how again did you remove COMPIANCE RATE from the Data Table? I am trying to follow your instructions, but I am missing something.....Thank you for your patience....