+ Reply to Thread
Results 1 to 9 of 9

Graph formatting issues

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    Lansing, MI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Graph formatting issues

    I run a query every day against a database to produce a text file I import into Excel (2003) as an aid in producing some reports. The graphs present viusally four of the columns of data against a date range, which is a fifth column of data. The last three days of query-running are shown. I just have a couple of formatting questions.
    1) For reasons best known, I suppose, to Excel itself, the worksheet for April 24 chose not not provide my 'Target' data label for 13-May-14. On the April 25 refresh, 13-May is provided, but 12-May and 14-May are not. On the April 28 version, 12-May and 14-May are back, 13-May is missing again, and also 15-May. Any ideas why Excel is deciding not to present some of the data labels for this trendline, and why the data points he doesn't present vary from day to day?
    2) On the April 24 graph, the 'Revised Target' data label for the 16-Jun-14 data point displays '99', although the value in the underlying data for that point is clearly '396'. On the April 25 graph, the mistaken data label has moved to the 17-Jun data label, and on the April 28 graph it has moved to the 18-Jun data point. Why is Excel deciding to display '99' instead of the given value for one of the data points (and why does it move around)?
    3) As long as we're on the April 28 graph (I manually placed the data labels along the trendlines on the other two graphs), the data labels for 'Target' don't appear very close to the points they represent, and particularly, the label for the 12-May-14 data point has hopped way up above the 'Revised target' trendline. And the 'Revised Target' data label for 13-May-14 is slightly to the right of the data label for 14-May-14. Is there any way to get the data labels to appear right along the trendlines they represent?

    For those who need to understand the data in order to evaluate better what's going on:
    I provide reports to the development and testing teams in my shop to monitor progress of testing the changes for the next release of our main application. I run 17 queries against our development and testing database, and import the results into 11 worksheets which form the basis for the reports. This is one of the worksheets. The testers create test scenarios and load them into the system, and then PASS or FAIL them as they are tested. The column graph shows the number of scenarios passed as of each day, and the number not passed. Once the reporting is actually in the scheduled testing time period, the columns will stay. The area graph - Target - shows how many scenarios need to be passed each day at a consistent rate to get the testing done by the scheduled end of testing. For these graphs, since testing hasn't officially started yet, Target shows the number of scenarios loaded for each day before the testing 'starts'. The line graph - Revised Target - is intended to show how many scenarios need to be passed each day, given how many had been passed as of the day before reporting.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365
    Posts
    963

    Re: Graph formatting issues

    I reduced your font size to 8.4 and they all displayed.

  3. #3
    Registered User
    Join Date
    04-09-2014
    Location
    Lansing, MI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Graph formatting issues

    Hmm... I tried that and the missing data labels stayed missing. I select one of the data labels in the series, which selects most of the labels, right-click and select Font... from the drop-down menu, and change the font size to 8.4. The font got smaller for all the labels in the series, but the missing labels didn't show up, even after saving and re-opening the file. Same if I use the font selector on the ribbon, and if I upgrade to Excel 2010. How did you accomplish this?
    Thanks for taking a look at this.

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365
    Posts
    963

    Re: Graph formatting issues

    Right click your category axis (the box with the labels and adjust the size there.

    You might also see if the chart is resizing:
    1.Click anywhere in the chart.
    2.This displays the chart tools, adding the Design, Layout, and Format tabs.
    3.On the Format tab, in the Size group, click the Dialog Box Launcher (little square on the lower right corner of the group) next to Size.
    4.On the Properties tab, under Object Positioning select the option that you want.

    Hope that helps you.
    Pete
    Last edited by PeteABC123; 04-29-2014 at 10:31 AM. Reason: typos

  5. #5
    Registered User
    Join Date
    04-09-2014
    Location
    Lansing, MI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Graph formatting issues

    Well, when I delete the data labels, and then re-add them, it puts in all the data points as I expect.

    For the "Target" data series (the area chart), however, the labels were aligned halfway between the primary axis and the trendline. When I select the data labels, Format Data Labels, and select Alignment, I see that the default alignment is "Middle Centered". If I select Top, or Top Centered, the labels don't move.

    For my line graph (Revised Target), the data labels all appeared, and the 99 was now displaying the correct value, and the labels lay along the trendline as I desire (oddly, the Alignment on those is also "Middle Centered"), but the labels are not aligned correctly above their corresponding primary axis values.

    I suppose I could delete and re-add the data labels for those two series every day I prepare my reports, but it would be convenient not to have to adjust every data label manually every day. I suspect as well that tomorrow the 99 will come back, and some of the missing "Target" labels.

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

    Re: Graph formatting issues

    Add a line series and use that to display the data labels, rather than the area chart series.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    04-09-2014
    Location
    Lansing, MI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Graph formatting issues

    I can ask the users if they'd accept a line chart for that series; they do like the area chart and the overall format that I have.

    And, shouldn't the area chart behave - or be able to be gotten to behave - the way I anticipate?

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

    Re: Graph formatting issues

    The only builtin position for data labels on an area chart is midpoint of data value. As yours were position atop the area I assume they have be moved manually, or with code.
    It's possible you deleted an individual data label.

    I run this check code on your file. It shows some data points do not have labels

    Please Login or Register  to view this content.


    I'm suggesting you keep the area series for Target, without data labels. And add an extra series as line where only the data labels are used. Data labels on a line series have 5 built-in positions

  9. #9
    Registered User
    Join Date
    04-09-2014
    Location
    Lansing, MI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Graph formatting issues

    Ah, now that makes (some) sense. Thanks. I've used Excel a lot over the years but am fairly new to the charting functionality. I guess I figured that if Microsoft offers five label positions for an area chart, all five would actually be available. I can work with this. I'll add a line chart with labels, and make the line invisible (or the same color as the area chart).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help to correct graph in two issues
    By tagusriver in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-23-2013, 05:53 AM
  2. formatting issues
    By tnhighc in forum Excel General
    Replies: 2
    Last Post: 12-13-2012, 11:42 AM
  3. Y axis issues for line graph - Excel 2010
    By noob99 in forum Excel General
    Replies: 2
    Last Post: 06-08-2012, 11:00 PM
  4. Histogram and graph issues
    By kryt0n in forum Excel General
    Replies: 7
    Last Post: 07-23-2010, 04:50 AM
  5. Issues with the scale of a graph
    By dante19 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-04-2009, 03:42 PM

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