Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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:

share|improve this answer

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

I was interested in something similar and was able to achieve it. Though I didn't use conditional formatting, I think this example will show you how to achieve what you want.

  1. Put the numbers 1,2,3,2,1 in cells b2 through f2.
  2. Put the formula =REPT("|",B2) in cell b4 and copy through f4.
  3. Change the text direction to go up.

That's achieves the outcome but to make it look nice you'll want to center the cells and adjust cell width and height.

It should look like this:
image of in cell graph

If you don't like the spaces between the lines switch the font to playbill and you'll see a normal bar graph with no spaces.

This does what you want without conditional formatting. You may want to add some conditional formatting if you want to vary the color of the bar graphs or something else.

share|improve this answer

Your Answer


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.