Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm able to do data bar conditional formatting in Excel 2010, but I can't figure out how to switch the bar axis to make it vertical. Is there any way around this? Can I simulate a vertical histogram with conditional formatting?

share|improve this question
    
Two questions: do you want to switch from a bar chart to a column chart? Also, what do you mean by conditional formatting, within the chart itself? –  dav Oct 9 '12 at 16:28
    
Yes, switch from bar to column chart. I mean that I am trying to have each cell have one bar based on its value. I am using Conditional Formatting > Data Bars option to generate the bars. –  JD Maresco Oct 9 '12 at 16:33
add comment

2 Answers

up vote 1 down vote accepted

Data bars in conditional formatting are Left-Right or Right-Left only. There's no Up-Down or Down-Up option available.

The only way to get a vertical data bar-type look would be to use a formula and change the Fill settings to a gradient, but you don't get enough control over the gradient to differentiate the different values.

Another option is to spark lines. There's a nice example of something that might do what you want here: http://blogs.office.com/b/microsoft-excel/archive/2009/07/22/formatting-sparklines.aspx

share|improve this answer
add comment

You can't change the conditional formatting in Excel to create a column chart instead of a bar chart. This conditional formatting isn't creating a true Chart object in Excel, so you don't have the same flexibility in modifying its appearance.

Instead, you can create a Column Chart by selecting your data and Insert > Column (and choose your subtype). Then you can format the Chart in any manner you prefer.

EDIT: Based upon your notes, and borrowing from @Rhys Gibson, here's a solution using sparklines (which are available in Excel 2010, or add-ins pre-Excel 2010). I've included both the conditional formating bar charts (nearly impossible to make sense of), and their sparkline equivalents.

enter image description here

EDIT: One other point that may seem relatively minor, a Histogram is not a Column (or Bar) chart. They are distinctly (if subtly) different chart types and the terminology shouldn't be interchanged. For a great description of the difference, check out Naomi Robbins' A Histogram is not a Bar Chart

share|improve this answer
    
I have 200 rows of data and want a small histogram for each of them - not going to create 200 column charts - and I am well aware of how to create a column chart. The question was, can this be done with conditional formatting? I have seen hints elsewhere that there may be a workaround to make this work. Can you confirm that this is not possible? –  JD Maresco Oct 9 '12 at 20:47
    
You can't do what you're looking for with conditional formatting. Based on your info in the above comment, I think you want sparkline column charts like @rhys gibson suggested in his answer. –  dav Oct 10 '12 at 0:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.