Forum Discussion
Logarithmic y-axis crossing x-axis in between
Problem: 3 different sub-data series with same denotion (say $), huge deviation (say millions vs 000's) necessitated logarithmic y-axis -> excel takes log10 base and transforms series (this will include lowest series to be in 0.1x while larger in thousands). X-axis will at-best touch y-axis at 0
this will overlap with the line of lowest data series in the middle of chart.
Outcome: To make x-axis go to the bottom of chart
Solution: Add one more dummy data series, shift primary data to secondary y-axis and make it logarithmic. Hide the output of dummy data series (and primary axis). This will interact with x-axis at 0
1 Reply
How about this:
- Create your chart:
- Select your data and insert a chart (e.g., Line Chart).
- Add a dummy data series:
- Add a new column for the dummy series. This column should contain zeros or a constant value that will place the series at the bottom.
- Right-click on your chart and select "Select Data".
- Add the dummy series to your chart.
- Move primary data to the secondary axis:
- Select one of your primary data series in the chart.
- Right-click and choose "Format Data Series".
- Under "Series Options", select "Secondary Axis".
- Repeat for all primary data series.
- Format the secondary y-axis:
- Right-click on the secondary y-axis and choose "Format Axis".
- Under "Axis Options", check the box for "Logarithmic scale".
- Adjust the minimum and maximum bounds if necessary.
- Format the primary y-axis:
- Right-click on the primary y-axis and choose "Format Axis".
- Set the minimum bound to a small value (e.g., 0.1) so that the x-axis appears at the bottom.
- Hide the dummy data series:
- Select the dummy data series in the chart.
- Format the series to have no line and no markers.
- Hide the primary y-axis:
- Right-click on the primary y-axis and choose "Format Axis".
- Under "Axis Options", set the "Axis labels" to "None".
- Create your chart:
