How do I change the scale of the vertical axis in Excel 2007 in a pivot table?

Excel 2007 doesn't record a macro when you manipulate the chart. But the property you want to change is the MinimumScale for the Value axis.

The macro below goes through all charts on the worksheet and sets the minimum scale value for the Value axis equal to the nearest significant multiple of 10. If the minimum value is 52, then the scale would start at 50. If the minimum value is 1775, the scale would start at 1000.

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button


If the code doesn't run, then you need to change the macro security to a more permissive setting. To set macro security so all macros will run (equivalent to Low security on Excel 2000 through 2003), do the following:
1) Click the Office Button (top left corner of window)
2) Click the "Excel Options" button at the bottom of the resulting dialog
3)  Go to the Trust Center tab, then click the button for "Trust Center Settings"
4) Go to the Macro Settings tab, then choose the option button for "Enable all macros (not recommended; potentially dangerous code can run)"

Excel 2007 also allows you to designate certain folders as being Trusted. Any macros contained in workbooks in those folders will be enabled automatically. A good candidate for such designation is a folder that contains a library of workbooks you have personally vetted. I don't advise so designating your main working folder, however, because you just might put a workbook containing malicious code in there at some point.


Rant

For the best compromise between protection and getting work done, I prefer the Excel 2003 Medium security level, which displays a macro warning message when you open the workbook with an "Enable macros" button. The closest Excel 2007 comes to this is "Disable all macros with notification", which is the default security setting. With this setting, when a workbook is opened with macros you must click the "Options" button in the Security bar that appears on top of the formula bar. You must then choose the option to "Enable this content" and click the "OK" button. The three clicks are so vexing that I don't fault anyone who chooses the lowest security level as shown in step 4 above.

You might think from the on-line documentation that "Disable all macros except digitally signed macros" would be even better. Contrary to on-line documentation, the Security bar never appears for unsigned macrosso macros can't be made to work.

/Rant

Brad

Sub ChartExpander()
Dim cht As ChartObject
Dim ser As Series
Dim d As Double, dlog As Double, dmin As Double
 
For Each cht In ActiveSheet.ChartObjects
    dmin = 1E+308
    For Each ser In cht.Chart.SeriesCollection
        d = Application.Min(ser.Values)
        If d < dmin Then dmin = d
    Next
    If dmin > 0 Then
        dlog = 10 ^ Int(Log(dmin) / Log(10#))   'Power of 10 represented by minimum number
        dmin = dlog * Int(dmin / dlog)          'Round down to the nearest number divisible by 10
    ElseIf dmin < 0 Then
        dlog = 10 ^ Int(Log(-dmin) / Log(10#))  'Power of 10 represented by minimum number
        dmin = dlog * Int(dmin / dlog)          'Round up to the nearest number divisible by 10
    End If
    cht.Chart.Axes(xlValue).MinimumScale = dmin
Next
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21: