Google-sheets – How to set the vertical axis to powers of 10
google sheetsgoogle-sheets-charts
For my Google sheets chart I have set the vertical axis to a logarithmic scale. I would prefer this to be displayed as a powers of 10, is this possible?
What I get:
What I want:
Best Answer
It is possible with a fairly extreme hack (that I accept most would not bother with) - the 'usual' overlay what you have with what you want, but a bit more tricky in Google Sheets than I am used to.
First plot the chart (as you have) then adjust the line spacing on a sheet behind the chart so that the rows match the labels for spacing. Chose a font colour for the vertical axis labels to blend into the background -on a (standard) white background select white.
Fill one column with 10s and next to it another with digits for the required powers (largest number at the top). Adjust the vertical alignment of these columns (top for the powers and centre or bottom for the 10s. Adjust the font sizes of each column to suit.
Copy the array into a drawing canvas, save it and move the result where required:
Insert to Chart then add your Data Series, as you normally would with a Single Axis chart.
(Optional) Change chart type to Line. You can change it to anything else later. Line seems to be a safe bet for the later options to appear. Not sure if this step is by Google design...
Right-click on the chart and select Series.
Change Apply Series to whichever Data Series you want to move to the Second Axis.
Change Axis to Right Axis.
Now you can go back to Chart type to make it a scatter, or bar, or anything else - the program will remember the Axis assigned to the Data Series, regardless of whatever options may be available.
tldr; the Google Support answer is broken for a few chart types. Do your Axis assignments with Line charts, and change the Chart Type after.
If the row you are using for labels contains dates, the spreadsheet supposes that you need an accurate representation of time. So it arranges the data chronologically and marks the time axis at equal time intervals:
If you want the specific dates to be used as labels, this means they should be used like text labels. Select the top row and apply Format->Number->Plain Text:
Now the columns are labeled with the text from the top row, and they are presented in the order of appearance in the spreadsheet.
Best Answer
It is possible with a fairly extreme hack (that I accept most would not bother with) - the 'usual' overlay what you have with what you want, but a bit more tricky in Google Sheets than I am used to.
First plot the chart (as you have) then adjust the line spacing on a sheet behind the chart so that the rows match the labels for spacing. Chose a font colour for the vertical axis labels to blend into the background -on a (standard) white background select white.
Fill one column with
10
s and next to it another with digits for the required powers (largest number at the top). Adjust the vertical alignment of these columns (top for the powers and centre or bottom for the10
s. Adjust the font sizes of each column to suit.Copy the array into a drawing canvas, save it and move the result where required: