How To Start Your Axis At 0 In Excel For Mac

In Select Data chart option we can change axis values or switch x and y axis If we want to edit axis or change the scaling in the graph we should go to Format Axis options.

This step by step tutorial will assist all levels of Excel users in learning how to change axis values .

How to Change Horizontal Axis Values

The macro above could be refined to adjust tick spacings as well as the axis limits. But in simple cases like this, you can adjust the charts by inspection. For the left-hand chart above, I imposed a primary axis major tick spacing of 3, and let Excel automatically scale the axis endpoints (see the dialog box below). By default, Microsoft Excel aligns numbers to the bottom-right of cells and text to the bottom-left. However, you can easily change the default alignment by using the ribbon, keyboard shortcuts, Format Cells dialog or by setting your own custom number format. How to change alignment in Excel; Excel alignment shortcuts.

Mac

In the example we have a chart with Years on x-axis and Sales values on the y-axis:

Figure 1. How to change x axis values

To change x axis values to “Store” we should follow several steps:

  • Right-click on the graph and choose Select Data:

Figure 2. Select Data on the chart to change axis values

  • Select the Edit button and in the Axis label range select the range in the Store column:

Figure 3. Change horizontal axis values

Figure 4. Select the new x-axis range

  • As a result, we changed x axis values from Years to Stores

Figure 5. How to change the x axis to Store values

How to Change Vertical Axis Values

To learn how to change vertical axis values, we should follow almost similar steps as in the example above:

  • Right-click on the graph and choose Select Data:

How To Start Your Axis At 0 In Excel For Mac Download

Figure 6. Select Data on the chart to change y axis values

  • Select the Edit button in the Legend Entries (Series) and in the Series values select the range from the bottom Sales column:

How To Start Your Axis At 0 In Excel For Mac Free

Figure 7. How to edit y axis

Figure 8. How to change y axis

  • As a result, we changed the y axis values:

Figure 9. How to change vertical axis values

How to Change the Axis Range

To change the scale on the graph we should go to Format Axis options. In our example, we will change the minimum scale to 15,000 and maximum scale to 55,000 on the vertical axis.

If we want to change the axis scale we should:

  • Select the axis that we want to edit by left-clicking on the axis
  • Right-click and choose Format Axis
  • Under Axis Options, we can choose minimum and maximum scale and scale units measure
  • Format axis for Minimum insert 15,000, for Maximum 55,000

As a result, the change in scaling looks like the below figure:

Figure 10. How to change the scale

How to Switch X and Y Axis

Another interesting chart feature that we will learn is how to switch x and y axis.

  • Right-click on the chart and choose Select Data
  • Click on the button Switch Row/Column and press OK

Figure 11. Switch x and y axis

As a result, switches x and y axis and each store represent one series:

Figure 12. How to swap x and y axis

The chart will have more logic if we track store values per years. In the following example, we want to change sales values to yearly sales values per store (upper table).

Figure 13. How to change axis

  • Click on the chart and drag values from the source table to the upper table by left mouse click:

Figure 14. Change the chart data source

  • Change the range to both years (2018 and 2019) by clicking on the down-right corner and expanding the selection to the right:

Figure 15. Expand the chart data source

  • Change the values from the x-axis to the “Years” by selecting in the Axis label range cell range I2:J2 (explained in the tutorial above)
  • Finally, we changed the axis with sales value data per stores and years:

Figure 16. How to edit x axis

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

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.

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.

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.

Mac

Create a common scale for the Primary and Secondary axis

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.