Kevin Standlee (kevin_standlee) wrote,
Kevin Standlee

No Thanks to Microsoft

I have spent much of today trying to get Microsoft Access graphs (technically, PivotChart objects) to behave similarly to their MS Excel report cousins. Specifically, I've been challenged by formatting the Y axis so it doesn't display unnecessary decimal places (by default it wants to use 0.00 format) and relabeling chart legends so that it doesn't put "Sum of" in front of the category names, which is what it wants to do by default. In Excel, you'd right-click on the things in question. It doesn't work like that in Access, and it's fiendishly difficult to figure out where it's changed.

To change the decimal place formatting of a PivotChart, open the object in PivotChart view, select (either left or right click; it doesn't matter) the Y axis by clicking somewhere in its figures. From the Ribbon, choose the Design tab, then select Property Sheet from the Tools group. (You can't get directly to Property Sheet from right-clicking the Y axis the way I would have expected.) Select the Format tab. One of the choices will be Number. There are the usual sorts of formats in the drop-down box, like Standard, but no way to specify decimal places like when formatting a cell in Excel. But it turns out that the Number box isn't limited to the drop-down items. You can enter a formatting string here. In my case, I just typed the number 0 here and presto, the axis was relabeled with no decimal places.

To change the Legend labels in the PivotChart, you have to switch to PivotTable view. Select the column (again, left/right click doesn't matter) you want to re-label, Choose Property Sheet from the Ribbon (Design tab, Tools group). Choose the Captions tab. The field Caption is how this data gets labeled in both the PivotTable and the PivotChart.

This stuff isn't documented in any sane way, in my opinion. It took hours of searching around and trial and error before I figured it out. Normally my Google-fu is pretty good, but this was like pulling teeth. And judging from the questions I found (few of which had answers), I'm not the only person who is trying to figure this out. It's nowhere near the user interface standard used in Excel, PowerPoint, and Word, or for that matter in other parts of Access. It's only the Chart object that behaves so stupidly. Madness.
Tags: access, work

  • Home Cooking, Episode 16: Burger Time

    One advantage to rarely going out to eat is that Lisa has been attempting to improve the variety of our meals. After we got out first Pfizer…

  • Friday in Reno

    I had (most) of today off, so once I got the hour of "morning work" done at Day Jobbe, Lisa and I went to Reno to do some shopping. First off was a…

  • Nevada Outback Trip, Part 1: Atomic Tour

    Lisa and I have been getting increasingly antsy and wanting to get out of the house and go see things, but with no sign of a vaccine being available…

  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.