Search code examples
excelexcel-charts

How to make a zoomable chart in excel 2013

I have 4000 columns and 200 rows in an excel spreadsheet, I want to make a line chart with all data in it, but once I created the chart, it is too hard to see each individual item in the chart even if I zoom the page to max.

enter image description here

Is there any better idea to make a chart that contains huge amount of data and also be able to zoom in to see each individual item?

Solution

  • You can add a second Chart to use like a Zoom:

    enter image description here

    With a Scrollbar ActiveX you can scroll (Zoom) the Big chart reducing the range of the data:

    Private Sub ScrollBar1_Change()
    Dim xx As Integer
    xx = ScrollBar1.Value
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SetSourceData Source:=Range("B" & xx & ":C" & xx + 3)
End Sub

    If you want you can have the Vertical value Fix or Variable. I have added a Combo to select:

    Private Sub ComboBox1_Change()
    Dim Fixed As Single

    If ComboBox1.Value = "Fix" Then
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.PlotArea.Select
        Fixed = ActiveChart.Axes(xlValue).MaximumScale
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.Axes(xlValue).MaximumScale = Fixed
    Else
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True
    End If
End Sub