1

I've the dataset in the following format. enter image description here

I want to create a scatter plot, which will have months in the x-axis and total value of sales in y-axis. Each dot in the scatterplot will reflect the each month total sales for each product.

But when I'm trying to create the chart, I'm getting it in the following format.

enter image description here

Anyone can please guide me how to get the desired scatterplot chart?

Thank you!

1

Excel expects the data series to be organized in columns. Your data table should really have the x-axis values (Months) in the first column, and the y-axis values for each data series in subsequent columns.

BUT Excel has a shortcut to remedy this problem. Click on the graph to select it, and choose Source Data... from the Chart menu. Then click on the Switch Row/Column button.

That should fix it.

You could also transpose your data, and then re-create the graph. This is easily done using Paste Special. First, select all of your data, including headings, and choose Copy (or CTRL-C). Click in a cell to the right of your data (where there is enough space to hold the transposed data, and then choose Paste Special... from the Edit menu. Check the Transpose option and click OK.

If you re-make your graph using this transposed data, it should be organized the way you want it to be.

  • Thanks for your answer! Actually my number of products are more than 255 (289 exactly!). So it was throwing error about 255 labels. I already tried your options. But it didn't help. – Beta Oct 12 '17 at 17:44
  • Ok, then you need to change the month names into actual dates. Like Nov, Dec etc. Excel may add the year, but you can apply a custom format to just show the 3 letter month if you want. Doing that will allow Excel to understand that the x-axis is time, and it will fit a reasonable number of month names on the axis. After the graph is created, you can adjust the scale and label format to your liking. – Bandersnatch Oct 12 '17 at 19:19
  • Thanks for your answer. I tried your other suggestion as well. But in this case also getting 255 error. – Beta Oct 13 '17 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.