Excel 2007 charts

I have an Excel file with 3 columns
week number, total visitors, visitor gender.

The visitor gender can be male, female.

I create a bar chart by selecting all the rows and all 3 columns, then click Insert, Columns, 2-D columns chart. It gives empty chart because column 2 contains integers while column 3 contains characters.

I change visitor gender to 1 (for male) and 2 for female and can create a chart but that's not what I want. Is it possible to create a chart when the two columns are of different data type and how? It worked fine with Excel 2003.

In addition, once a chart is created, how can I widen the width of each line in the chart?

Thank you.

Hi

If you can forward the excel sheet..I'll solve the issue and mail it back to you.

I've uploaded a sample Excel file with two sheets. The first sheet has gender in characters and thus the chart does not work (all empty).

The second sheet has number to represent gender. The line that I was referring to were the blue (total visitors) and red (visitor gender) lines.

I select the Y axis, CTRL+1 and see
Axis Options. But there's no Axis Type. Please see the new attachment.

As to mixing data types in xl2003, the workbook is the same because the workbook was generated by a report program.

I select the X axis and see the Axis type -> text axis. It looks better but is there a way to change it to a certain width instead of the big bar?

Where is the Gap Width property?

Actually, I was told that it worked in xl2003 and I didn't try that myself. She used to create the chart in xl2003 and when switching to xl2007 her chart stopped working because of the text in gender.

I think the chart will make more sense if I use the Stacked Column kind of chart.

When changing text to a value, the meaning of the chart is changed totally. I want the chart to convey that there are 23 males on 1/24 but with value it conveys there are 23 visitors and 1 gender. That has a totally different meaning. Likewise, I want the chart to convey that there are 12 females on 2/12 but with value it conveys there are 12 visitors and 2 genders. Again, that has a totally different meaning.

I create sheet 3 (please see attachment) by changing the third row to have the same date as the second row. I also create a Stacked Column chart. I want the chart to convey on 1/24 there are 23 males and 12 females. But it doesn't.

Is there another kind of chart that can convey what I want?

I change the layout of the data and it works great with a small amount of data. When I use the real data which about 250 rows, I can create a stacked column chart fine. But when I change axis type to text axis, it changes the chart to clustered column, why? I don't want it to change to clustered column.

agree, but yes, it changed the chart type. From then on, I delete the chart and create another stacked column chart but it creates clustered column chart, so weird.

Please see attachment.

This attachment has data that somehow it doesn't create stacked column chart. I try to create stacked column chart but it always give me clustered column chart.

May be there're some setting in my Excel that causes the change of chart type. As you can see from the attachment the first chart is stacked. The second chart is generated by highlighting the x-axis and select Text Axis.
The data is for testing and thus doesn't make sense.

"Enter 10 into B2 and you will see a blue/red stack."
I see where the problem is. Each row in the spreadsheet is represented as an individual serie in the chart as opposed to combining all rows with the same date into one serie. But it's strange that the first chart is stacked (all rows with the same date are combined and stacked into one serie), only when I change to text axis that each row is represented as a separate serie.

OK, here's a better data sample. The first chart: Dec-90 has a stacked serie with 1 male and 3 females. The second chart: starts out to be exactly the same as the first chart, then I select the x-axis and change to text axis. It breaks Dec-90 into two series: one for male and another for female. I want the first chart but make the series wider for easier viewing; is there a way to do that? By changing to text axis I get the second chart which is not what I want.

"When the axis is Time series then values with the same date will be summarized. But you will get skinny columns." How come when the skinny columns are widen (changed to Text Axis) then the values are not summarized?

I'll build a summary table in order to get category X axis and summed Y values.

Andy, thank you very much for your help

When the chart has many series, I click on a series and it selects only some of the series. How do I select all of the series?

The chart has many series/columns/lines; I guess by data point you meant series. When I click on a series it highlights that and several other series as well with a circle at both end of each line. That's how I know the series are selected. When I double click a series, the same thing happens. It depends on which series I click, it highlights just that one series or multiple series.

Let's see if I get the terminology correctly.

Sorry, I hit the submit button by mistake.
Let's see if I have the terminology correctly.
In this workbook, the red line/bar/column, is that called a series?
A line/bar/column with red at the bottom and blue at the top, is the red part called a data point and a blue part is also called a data point?

When I click the red line/bar/column to the right of Apr-96 in the first chart, it highlights all the red data points except the one to the right of Feb-96 and Apr-96. Then, if I click the red line/bar/column to the right of Apr-96 again, it highlights only the one I click. I click on something else and click that again, it highlights something else. Can you explain how the selection work? Thanks.

I thought I replied to your post but not. Thank you so much. My chart is working great now.