We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
12 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel Bar Graph help
Posted on 2011-09-21
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
- +2
8 Comments
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.
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
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
Active 1 day ago
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
Also it is same color and way too short.
Here is another screenshot
Capture.JPG
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.
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
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
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
Featured Post
August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.