Plotting gigantic values + smaller values on the same Google Sheets chart?

I'm trying to plot a chart that includes one data set in billions, another in millions, and a third in thousands. What is the most efficient/elegant way to do this using google sheets without the larger data set making the smaller ones look invisible?

enter image description here

Answers 1

  • To make this easier, Insert > New sheet where you can scale the population to billions and incidence figures to millions using these formulas:

    ={ Sheet1!A2:A }
    
    =arrayformula( if( isnumber(Sheet1!B2:B), Sheet1!B2:B / 10^9, iferror(1/0) ) )
    
    =arrayformula( if( isnumber(Sheet1!C2:C), Sheet1!C2:C / 10^6, iferror(1/0) ) )
    
    ={ Sheet1!D2:D }
    

    Use two Y axes where the max value in the left axis differs from the max value in the right axis, like this:

    chart with two Y axes

    See the new 'Scaled data' sheet in your sample spreadsheet.


Related Questions