Solved

Excel Bar Graph help

Posted on 2011-09-21
8
356 Views
Last Modified: 2012-05-12
I am having trouble getting a Bar Graph. I have a sheet with 9 columns. A represents a city, B is the Percentage of visitors who made a purchase, and C is the Number of visitors. Columns B and C are repeated three more times. Each two columns represent a year (B/C = 2005, D/E = 2006, etc.)

How do I get a bar chart that will show two bars for each year. A blue one that will show the Percent Visited and a Red one showing Number Visited. This would be repeated three times so that each years has a two color bar, blue showing percent and red showing number. (Color can be anything I used blue/red for illustration.) I want to graph only one row.

Sheet
0
Comment
Question by:Jess31
8 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 36576779
Not sure, it works fine for me... I highlight cells A1 to I3 then click the graph button. I leave all settings to their defaults and just click next next next - and I get exactly what I expect.
0
 

Author Comment

by:Jess31
ID: 36576807
I only get one bar when I do it (one bar for each year).
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36576874
It doesn't look quite right to me like that either.  I think you need to rearrange the data so the years are rows, and the % visited and number visited are columns.  The you can create a column graph, but you have the problem that scales are quite different for the two series, so the % visited are hardly visible.  

I think the best way to deal with this is to plot the % visited on a secondary axis (Format data series - plot on secondary axis).  Then the bars are on top of each other, so I'd be inclined to change one series to a line superimposed on the bars which I think conveys the message you want to show.   There are other ways, but this is how I'd do it.

See attached barchart.xlsx
0
 

Author Comment

by:Jess31
ID: 36576897
It seems like there is a second bar but it is too small to see (maybe one pixel!). So if I change it to 2000% then I see the second bar.
Also it is same color and way too short.
Here is another screenshot
Capture.JPG
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36576948
The default plot in your picture sort of works, but only by accident.  You're actually plotting a category axis with the year followed by an empty column where the cells are merged.  Then you have the name series, which is empty as there are no numbers, and the 'Oklahoma series, which has some very small numbers (percentages less than 100% are obviously numbers less than 1), and some big numbers.  You won't be able to get this looking how you want, because you can't scale the smaller numbers differently as they are part of the same series.
0
 

Author Comment

by:Jess31
ID: 36577002
Is there no way I can get this?
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36577080
Hello,

you cannot have a clustered chart where the data series have two different scales. That will require plotting the data on two different axes.

You would need a different data layout to achieve the look and feel of a clustered chart, though.

See attached for a data layout with spacing and two series plotted on different axes.

cheers, teylyn
Book4.xlsx
0
 
LVL 2

Expert Comment

by:jan24
ID: 36577095
Here's a slight variant of teylyn's which pretties up the x-axis formatting a bit ... you may prefer the original of course! Book4-jan24.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now