Once you have mastered some of the tools in charts (secondary axis, change series chart type etc), you may encounter some problems that need to be addressed. One of the more common issues is how to make Excel charts primary and secondary axis the same scale.
- How To Start Your Axis At 0 In Excel For Mac Download
- How To Start Your Axis At 0 In Excel For Mac Pro
So below we are showing the revenue (bill) and profit. The profit is shown on the secondary axis, but because some of the points are negative, the secondary axis starts at -5. This makes the chart hard to read. It is almost as if you need another line (red thick line below) to show where the secondary axis crosses at 0.
![How To Start Your Axis At 0 In Excel For Mac How To Start Your Axis At 0 In Excel For Mac](https://i.ytimg.com/vi/TKoq0hFbt2U/hqdefault.jpg)
The manual way to fix this is to go into the Axis and manually change the minimum and maximum values. The problem is you need to go into the chart every time the data changes.
Create a common scale for the Primary and Secondary axis
Here is a better way to change the automatic axis settings: Open the Excel file containing the chart. Click a value in the chart's vertical axis to select it. Add or remove a secondary axis in a chart for Mac 2016 I would like to create a chart like this on Excel for Mac 2016 When the values in a 2-D chart vary widely from data series to data series, or when you have mixed types of data (for example, price and volume), you can plot one or more data series on a secondary vertical (value) axis.
When the Format Axis dialog box appears, select the Axis Options entry from the list box. To tell Excel to use logarithmic scaling of the value (Y) axis, simply select the Logarithmic Scale check box and then click OK. Excel re-scales the value axis of your chart to use logarithmic scaling. Note that initially Excel uses base 10 logarithmic. Right-click the vertical axis and choose Format Axis. The Format Axis dialog box appears, as shown in the figure. In the Format Axis dialog box, expand the Axis Options section and set the Minimum value to 0. (Optional) Set the Major Unit value to twice the Maximum value in your data.
The trick is to create a common scale so that the primary and secondary axis start and end at the same point. The only way this can happen is if the smallest and biggest number for both data series are the same. So to mimic this behaviour you can:
Determine a common axis scale that will handle the 2 data sets
First create 2 new columns and call then Primary and Secondary Scale. In the first cell create a MIN function that looks at ALL the original data points and finds the smallest number. In the last cell do the same but this time a MAX to find the biggest number out of all the data points. In E8 and E34 just equals to the adjacent cells. You now know what the scale needs to be.
Insert the new series into the chart
However you like to do it, insert the 2 new series into the chart. Below we just dragged the coloured boxes wider. You may not be able to see the new series but don’t worry about that for now.
Match the series to the correct axis
The next step is to make sure that there is one of the new series assigned to the primary axis and one to the secondary axis. Depending on your version of Excel you may use different methods but in Excel 2016 it is nice and easy to look at the screen for a combo chart. As shown below I can make sure that the one series (Bill) is on the same axis as the series called Primary Scale.
Hide the scale series
We are almost there. Below you can see that the scales are now the same, and this will always be the case, because the formula in cells D8, E8,D34 and E34 will make sure that both sides always start and end at the same place. But you can see that the new series are being shown. We need them to be there but we don’t want to see them.
To hide the series all you need to do is tell each series to have no fill, border and line (depending on how they are showing). These series may be hard to see so the easiest way to customise them is to click on the Chart, click on the Format tab, and find the series called Primary Scale. Just below this dropdown you can click on Format Selection.
On the resultant options box, change the fill to No Fill and the Border to No line.
You will do the same for the other new series (Secondary Scale).
Primary and secondary axis now have the same scale (automatically)
The end result is a chart that will (automatically) rescale the primary and secondary axis to always be the same. This will make the chart much easier to read and interpret.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
One of the most common concepts used in Excel dashboards and reports is the concept of trending. The vertical axis on trending charts should almost always start at zero. The reason it’s almost always is because you may have trending data that contains negative values or fractions. In those situations, it’s generally best to keep Excel’s default scaling.
However, if you have only non-negative integers, ensure that your vertical axis starts at zero.
This is because the vertical scale of a chart can have a significant impact on the representation of a trend. For instance, compare the two charts shown in this figure. Both charts contain the same data. The only difference is that in the top chart, nothing was done to fix the vertical scale assigned by Excel (it starts at 96), but in the bottom chart, the scale starts at zero.
Now, you may think the top chart is more accurate because it shows the ups and downs of the trend. However, if you look at the numbers closely, you see that the units represented went from 100 to 107 in 12 months. That’s not exactly a material change, and it certainly doesn’t warrant such a dramatic chart.
In truth, the trend is relatively flat, yet the top chart makes it look as though the trend is way up.
The bottom chart more accurately reflects the true nature of the trend. You can achieve this effect by locking the Minimum value on the vertical axis to zero.
To adjust the scale of your vertical axis, follow these simple steps:
- Right-click the vertical axis and choose Format Axis.The Format Axis dialog box appears, as shown in the figure. Khmer limon font for mac.
- In the Format Axis dialog box, expand the Axis Options section and set the Minimum value to 0.
- (Optional) Set the Major Unit value to twice the Maximum value in your data.Setting this value ensures that your trend line gets placed in the middle of your chart.
- Click Close to apply your changes.
How To Start Your Axis At 0 In Excel For Mac Download
Many would argue that the bottom chart shown in the figure hides the small-scale trending that may be important. That is, a seven-unit difference may be very significant in some businesses.
How To Start Your Axis At 0 In Excel For Mac Pro
Well, if that’s true, why use a chart at all? If each unit has such an impact on the analysis, why use a broad-sweep representation like a chart? A table with conditional formatting would do a better job at highlighting small-scale changes than any chart ever could.