+ Reply to Thread
Results 1 to 34 of 34

Excel 2007 charts

  1. #1
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    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.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    Charts plot values not text.

    Can you post and example of what exactly was working in xl2003.
    In my test although a series for gender is created all columns have a zero height.

    Also confused by the part about increasing line width. What line exactly?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    05-16-2009
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Excel 2007 charts

    Hi

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

  4. #4
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    To increase the width of the columns you need to use a categorical rather than time series axis.

    Select the axis, CTRL+1 to display format dialog.
    Axis Options > Axis Type: Text axis

    As to mixing data types. The only way to get values is to use values.
    As you examples show, text is treated as zero.

    Are you sure you had this working in xl2003? If so can you post that workbook.

  6. #6
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Images Attached Images

  7. #7
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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?

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    In order to affect the width of the columns you need to change the X axis rather than the Y axis.

    You can use the Gap Width property to adjust the size of the column.


    To get text to plot as values you will need to change the text to a value. This is true for all version of excel.
    The only way around this is if you used a pivot table to summarize the text.

    In your original post you said, "It worked fine with Excel 2003." I assumed this meant you had managed to chart using textual items. Perhaps you meant the columns where not skinny?

  9. #9
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.

  10. #10
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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?
    Attached Files Attached Files

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    Select the series and on the Series Option section of the Format dialog is the Gap Width adjustment.

    You will need to revise the layout of your data to get the chart you describe.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    Changing the axis format should not change the chart type.

    Are you sure it changed the chart type?

  14. #14
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    can you post that workbook?
    Last edited by Andy Pope; 05-18-2009 at 10:49 AM. Reason: email addy removed

  16. #16
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    Please see attachment.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Files Attached Files

  18. #18
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    Changing the axis to text does not change the chart type to clustered, it is still stacked.

    Having said that the chart may appear to be non-stacked as you do not have data values for both series for any 1 date.

    In fact your data is really confusing. I do not see how you can used it.

  19. #19
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Files Attached Files

  20. #20
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    The chart is a stacked column chart.

    Try selecting it and then use Chart Tools > Design > Type > Change chart type.

    As I stated before the reason it looks clustered is that you only have 1 data value per category. Enter 10 into B2 and you will see a blue/red stack.

    The data does not make sense in the fact the information is not comparable.

    Take the information in row 2. It says for week 24/01/2009 the majority of visitors where 1 (Male).
    The information in row 15 says, for week 24/01/2009 the total visitors was 12.

    The data you need to be presenting to the chart is

    WeekNo. Male_Visitors Female_Visitors


    Currently the Gender majority column is not giving you any chartable information if you want a stacked column for any given week.
    In my first post I showed you the layout you need to use.

  21. #21
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    "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.

  22. #22
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Files Attached Files

  23. #23
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    When the axis is Time series then values with the same date will be summarized.
    But you will get skinny columns.

    You need to build a summary table in order to get category X axis and summed Y values.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    "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.

  25. #25
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    Because when the axis is a category axis each category is discrete.
    The chart has no idea how to add one category to another.

  26. #26
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    Andy, thank you very much for your help

  27. #27
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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?

  28. #28
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    What makes you think you have only selected some part of the series?

    If you click the series twice then you can select an individual data point.

  29. #29
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.

  30. #30
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    When I said data point I meant data point. That is 1 of the columns/bars/markers/slices or bubbles out of all the data points in a single series.

    It is not possible tot select multiple series.

    Perhaps you are using the wrong term to describe the elements within the chart.

    Can you post the workbook

  31. #31
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    Let's see if I get the terminology correctly.

  32. #32
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

    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.
    Attached Files Attached Files

  33. #33
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Excel 2007 charts

    The red columns, they only look like lines because the are very very thin, are a series, named Male Visitors.

    The blue columns are a series, named Female Visitors.

    A series is made up of data points. You can select individual data points by clicking the series and then clicking the data point.
    Or click the series and use the Right arrow to move through the data points.
    More explanation here,
    http://www.andypope.info/tips/tip003.htm

    The circles that appear when you select a series are only a guide.
    If you change the Unit value for the X axis from Days to Months you will see the columns fatten up. But still selecting either one of the series leaves certain columns without selection circles.

    The term series is confusing in its own way as it looks like it is plural.
    Last edited by Andy Pope; 05-20-2009 at 09:00 AM.

  34. #34
    Registered User
    Join Date
    05-15-2009
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Excel 2007 charts

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1