This will load the transformed data to a new sheet in your workbook.Select the Custom column and right-click, then remove it.You'll now see something like this: The Custom column is the first column, the next column has the month names and the Value column has the different values for each month.Ribbon tab, then the Unpivot Columns dropdown and select With that Custom column still selected, click the Transform.If not, double click any of the 1 values in the column to select the column. Now there is a new column in the Query Editor window and it is highlighted.In the window that pops up, type a 1 into the formula box after the = sign, like in this screenshot:.In the Query Editor click the ' Add Column' tab and then the ' Add Custom Column' command.This will open the Query Editor window with your table loaded as query data.Click the Data ribbon and in the 'Get & Transform' group click the command ' From Table'.Now the data will be formatted as a table, with the month names as the table headers.Make sure that the box 'My table has headers' is ticked.

Select any cell in your data table and click Insert > Table.

This may sound daunting, but with Excel 2016 it's actually quite easy.

Finally, hide the line and markers for the helper series.You need to transform your data into two columns. Then hide the axis labels and add data labels for the helper series. If you want the X axis labels to show the first of the month, you'll need to add a helper series with the dates on the X axis and zeroes as the Y values. Unfortunately, the minimum and maximum cannot be linked to a cell (but you can add your vote here to have that feature added to Excel).Īlso, in the Scatter chart there is no setting to increment major or minor units by one month. Now you can see which number you need to enter in the Minimum box. Once you have worked out what date you want as the minimum date, enter it into a cell on a spreadsheet, then change the cell format to General. That is independent of the number formatting you use to show the X axis labels. XY Scatter charts don't have an option for a date axis and the minimum and maximum setting for the axis always show the underlying number of a date instead of the formatted date.

That is an option in line charts and column charts, for example. In a chart that supports a time/date x axis you will be able to set the minimum and maximum bounds by selecting a date, and you can set the major units to months. The numbers you see are not random, but are the internally stored values for dates. Excel stores dates internally as whole numbers, times as decimals.

Horizontal axis labels excel 2016 mac