Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need a particular column chart in Excel

Posted on 2014-02-14
3
Medium Priority
?
290 Views
Last Modified: 2014-02-17
I attached a simple Excel file with two columns of data: dates and scores.

How can I create a column chart like this: http://peltiertech.com/images/2009-10/stackchartlabels02.png

where each column (A, B, C, etc...) is a quarter of year and the 3 colors are respectively:
- blue: the number of items with score < 3
- red: the number of items with score = 3
- green: the number of items with score > 3

?
data-for-chart.xlsx
0
Comment
Question by:lucavilla
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 2000 total points
ID: 39860093
Is this what you want?

If it is, you can use this as a chart template.
data-for-chart.xlsx
0
 

Author Comment

by:lucavilla
ID: 39860905
Wow.  Just invert Date <-> Class  and it's what I needed!

Questions:
- how did you set the dates as quarters?
- how can I rename the legend texts?
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 2000 total points
ID: 39865337
lucavilla,

To invert the date and class, right click on the chart and choose Select Data.

In the Select Data Source window, click the center button Switch Row/Column.

To answer your questions,

#1 Grouping Date as Quarters is a feature of Pivot Table. I first use the Pivot Table to create a summary of the data. Put the Date into the Pivot Table AXIS (Category), Count of Score in to the Values, and Class into the Legend (Series). After that, you will have a long table listing out all the dates. Now, right click on the any of the dates and select Group. In the Grouping window, select Quarters.

There are many other grouping to choose. Like, Seconds, Minutes, Hours, Days, Months, Quarters, and Years.

#2 To rename the Legends, instead of doing it in the chart, you do it in the Pivot Table. (The light blue colored table on above the chart.

Besides any calculated fields, you can pretty much change any of the fields by selecting it and use F2 to edit them.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

926 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