Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Charts: Area and Bars (2000)

    Hi All,

    A couple of questions about the charts in the attached file:
    1. Resize chart area: how does one resize the chart area to make it bigger or smaller without affecting what's inside the area. For example:
    --in the first sheet (SATs), I'd like to add some space at the bottom of the graph - ie, more yellow chart area between the bottom of the graph and the border
    --in the last sheet (US Budget), I'd like to make the chart area narrower so there's not so much dead space on the sides of the pie
    If I drag the handles for the chart area, that resizes the whole thing and does not accomplish what I want.

    2. In the sheet for SATs, the bars were created by creating a file with a rectangle and the words printed as shown ("Spending per pupil", "SAT"). Then the file was used to fill the bar. However, I don't like the stretch effect. What I'd like to do is have the words in the box as text so it doesn't have the stretch effect. 2 things come to mind:
    --just use a solid fill and then add a text box on top of the bar. Not sure I like this
    --I once saw a thread where you could tailor the context of the "value" box. Since I saved that, I followed its steps. But I want to keep the numeric value to the right of the bar while still having some kind of text in the bar. Is there a way to do this? I tried dragging the value text box while holding the CTRL key to duplicate it but that didn't work.

    TIA

    Fred

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Charts: Area and Bars (2000)

    To Resize the plot area without distortion, make sure you select a corner of the plot area - if you try going from the horizontal and vertical handles, you're bound to distort text. To select the Plot Area, it may be easiest to use the Name box on the Chart toolbar to find and select the Plot Area, then use the lower right or left hand corner to drag the Plot Area up.
    In the case of the pie chart, I'd probably make the pie as big as I can, move it to one side of the chart and move the Chart title to occupy space on the left or right hand side.
    In your second set of questions, you are going to run into problems because you are using the same graphic for each bar. Even though it is displaying text, Excel "sees" it as a graphic and this is why you are running into distortion.
    I'd go for the text box overlaying the bar, but I'd do a couple of things (perhaps you have already considered them).
    1) I'd link the text in the text box to a cell in my spreadsheet, since you want the same text in all bars this will make it easier to edit them together.
    2) I'd make sure the text box had a transparent background and no line around it.
    3) I'd make sure the text box is on the top of the stacking order - no point in "losing" it behind the graph.
    4) Once I had the text boxes in position I'd group them to the chart, so I could move, resize the chart and keep my text boxes positioned properly.

    Finally, for most charts I prefer making a separate chart sheet, I find the chart sheets are sized better, print better and are easier to edit.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts: Area and Bars (2000)

    If you resize a chart, its contents are automatically resized, no way to avoid that as far as I know. So you must resize the chart as a whole, then resize the plot area. It helps if you select the chart, then select Format | Selected Chart Are, and clear "Auto Scale" in the Font tab. This will prevent the font size from being adjusted when you resize the chart or plot area.

    Click on the data labels for one of the series in the SATs chart. Select Format | Selected Data Labels. In the Alignment tab, set Label Position to Center, and in the Number tab, select Custom, and add the text (in quotes) to the format. For example, for the cost series:
    "Spending per pupil $"#,##0
    and for the SAT series:
    "SAT "0
    Of course, the series should then have a solid fill instead of a picture.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Charts: Area and Bars (2000)

    Hans and Catherine,

    Thanks for the ideas.

    On the pie chart, can't really do much with it. The space on the sides will have to stay. Trying to resize doesn't really help since the pie is a circle resizing the pie bigger while making the chart area smaller creates the problem of the pie becoming too tall.

    I tried moving the pie to one side but whenever I do that, I end up exploding the pie, not moving it. Tried moving the bar charts and that works fine but the same technique doesn't work for pies. I ended up exploding out the Education piece to take up a little more horizontal room.

    Question: I changed the pie to have the slices show label and percent. One slice, the Other Agencies, didn't pick up the percent while all the others did. Any ideas on why not? So I used the tip I picked up here along time ago to just change the label box for that slice.

    On the bar graph on the first sheet, I decided to leave it the way it is. I know the words are being distorted because the picture is being stretched to the proper size. Moving the label into the bar was something I didn't want to do - wanted to keep the effect as is. The text box approach is something I might consider if I have a little more time. Thanks for the checklist of things to make sure to do if I go that approach.

    Fred

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts: Area and Bars (2000)

    If you want to move or resize the pie chart, make sure that you select the plot area, either by clicking just outside the pie, or by selecting Plot Area in the Chart Objects dropdown list in the Charts toolbar.

    I could only get the Other Agencies category to display a percentage by turning off data labels entirely, then turning them on again.

Posting Permissions

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