• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Excel Pivot table comparison chart

Hello I need to make a comparison bar graph dependent on year, building, building condition,and building rating and building type.  

The building type is either permanent or temporary.  If the building is temp I need the color of the bar to be red, if it is perm, I need the bar color to be blue.  


I'm not quite sure how to do this with a conditional statement.  I can only have two bars per building.

I've attached an excel file.  

bldgCondition.xls
0
mhening
Asked:
mhening
5 Solutions
 
gbanikCommented:
Check the attached file. The format of the data has been modified a little to make it Pivot compliant.
123.PNG
bldgCondition.xls
0
 
gbanikCommented:
messed up the colors
bldgCondition.xls
123.PNG
0
 
cgaedenCommented:
I don't think that there's a way to do this automatically with the tools that Excel provides (short of scripting). Also, I'm not sure how you would differntiate the two series of data from each other, apart from knowing that one data set is on top of the other.

However, you could always manually set the colors on each data point in the graph. If there's not many more than what you have in the sample sheet, this wouldn't be too much trouble. (see attachment)
bldgCondition.xlsx
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello mhening,

just a tip for using Gbanik's suggestion: Format the data series and on the Options tab set the Overlap to 100. This will center the columns in their spaces and get rid of the uneven distribution of columns.

cheers, teylyn
0
 
mheningAuthor Commented:
This is how I solved the dilemma, I made new columns using  the orignal columns using =IF(C2="temp",B2," ").  Then I made the chart and used the overlap setting at 50%.  

I attached a revised file.  Thank you for helping bldg summaryBuilding-ConditionRevised.xls
0
 
gbanikCommented:
Hi mhening,

In my file, I have used pivot tables that allowed you to slice-n-dice the data as you want. The key is to convert the data in a way that Pivot understands. The output (as suggested by you), I think, is very similar to what you have obtained.

ps. I see some differences between your graph and mine especially around "I". Please confirm.
bldgCondition-1.xls
123.PNG
0
 
mheningAuthor Commented:
I solved the problem
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now