Y-axis automatic scale isn't appropriate but manual scaling also isn't

gooniegirl180

Board Regular
Joined
Aug 13, 2003
Messages
142
Hi everyone, I am attempting to graph headcounts by month by branch.

I have a dropdown box where the user can select the branch, which updates the underlying data and the chart accordingly. One of the branches is "ALL" which is the total headcount by month.

Where I am coming unstuck is the Y-axis scaling. If I select a smaller branch, it might show gridlines at, say, 5, 5.2, 5.4, 5.6, 5.8 and 6. However, being headcounts, they can only be whole numbers. If I change the formatting to have intervals of "1", as soon as I select "ALL" branches, you can't see the scale as there is a gridline for every number and you can't read anything!

How can I fix this?

Regards,
gooniegirl180
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
L

Legacy 456129

Guest
Been there, experienced that. Use a macro to set the stepping. I've got some calculations set up and named the various computed values which I feed into a script. The below may give you ideas. Several button on your sheet that fire different value calls for departmental and overall.

VBA Code:
Private Sub AxisScaling() '19.01.2020 chg, 16.01.2020 chg ver 01.05.2017 Recalibrates the 2 Y Axes on the CumRes chart

      Dim chartidx As Byte, charts As ChartObject '15.01.2020
      Dim charttyp As Variant: charttyp = sALL.ChartObjects(1).Chart.charttype '15.01.2020
      For Each charts In sALL.ChartObjects '15.01.2020 -4111 is charttype xlCombination
         If charttyp = -4111 Then chartidx = charts.Index '15.01.2020
      Next charts '15.01.2020
10    With sALL
         .ChartObjects(chartidx).Name = "CumRes" '15.01.2020
15       Range("VarsCumRes").Calculate 'add 30.04.2017
20       vars = Range("VarsCumRes")
25      If vars(1, 1) <= Range("PriorCY").Value Then Exit Sub
40       If .ProtectContents = True Then ProtectStatus = True: .Unprotect Password:=ShtPw ''16.01.2020 chg, has to be on to be able to select chart elements.
50       .ChartObjects(chartidx).Activate
60    End With 'sAll
70       With ActiveChart.Axes(xlValue, xlPrimary)
80          .Select
90          .MaximumScale = vars(1, 1): .MinimumScale = vars(1, 2): .MajorUnit = vars(1, 3)
100      End With
110      With ActiveChart.Axes(xlValue, xlSecondary)
120         .MaximumScale = vars(1, 4): .MinimumScale = vars(1, 5): .MajorUnit = vars(1, 6)
130      End With
140      With ActiveChart.Axes(xlCategory)
            .TickLabelSpacing = Range("GraphLblIntrvl").Value
150         .TickMarkSpacing = Range("GraphLblIntrvl").Value
160      End With
170      Range("PriorCY") = vars(1, 1)
180 If ProtectStatus = True Then Run "SheetProtected", sALL '16.01.2020 add
190 Application.Goto "MainTitle" '19.01.2020 add to deselect chart
200
End Sub 'EoS AxisScaling

This one ensures the two Y-Axes are set so the line graph starts exactly bottom left, and ends exactly top right, with appropriate stepping.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,040
Messages
5,622,343
Members
415,894
Latest member
silverhaze

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top