Pivot Chart x-axis - evenly distributed numerical axis with data points within

Question

question

MOdland-6451 asked Mar 25, '22 CeasarChenMSFT-3458 commented Apr 6, '22

Pivot Chart x-axis - evenly distributed numerical axis with data points within

Hi,

I've seen other similar questions, but have not seen a response that works for me. I created a pivot chart from a pivot table.

I also created a "regular" scatter-plot chart from a pivot table (before I learned about pivot charts.)

I would like the pivot chart's x-axis to have a similar format to the "regular" chart with equally spaced - by value, not unit - within a given range with editable bounds. The circles will still denote the plot points, but the distance between 2.5 and 3.5 should be equal to the distance between 4.5 and 5.5. The labels should be whole numbers.

How can I make this happen (preferably using small steps accompanied by pictures?) Here are the format settings for the data set, and changing it to "sum" produces no change to the chart:

The format axis feature does not give the same choices as that for the scatter plot (with bounds and distribution). I've played with various settings without success.

Thanks, in advance, for your help!

power-query-not-supported office-excel-itpro

snag-a6f5b9d.png (129.8 KiB)

snag-a6ef08f.png (2.3 KiB)

snag-a73340d.png (41.3 KiB)

snag-a792327.png (58.9 KiB)

snag-a7bdd94.png (16.7 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CeasarChenMSFT-3458 · Mar 28 at 09:49 AM

Hi, @MOdland-6451
I'm working on it and will reply when there is progress.

0 ·

Answer 1 · 2022-03-29T09:45:03.497Z

CeasarChenMSFT-3458 answered Mar 29, '22 CeasarChenMSFT-3458 edited Mar 29, '22

Hi, @MOdland-6451
After my tests, I can't achieve your purpose.
Because the horizontal axis of a scatter chart is always a value axis, it can display numeric values or date values (such as days or hours) that are represented as numerical values. To display the numeric values along the horizontal axis with greater flexibility, you can change the scaling options on this axis the same way that you can change the scaling options of a vertical axis.
Because the horizontal axis of a line chart is a category axis, it can be only a text axis or a date axis. A text axis displays text only (non-numerical data or numerical categories that are not values) at evenly spaced intervals. A date axis displays dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in order or in the same base units.
The scaling options of a category axis are limited compared with the scaling options of a value axis.
For details, please refer to this link: present-your-data-in-a-scatter-chart-or-a-line-chart

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-05T13:42:29.24Z

MOdland-6451 answered Apr 5, '22 CeasarChenMSFT-3458 commented Apr 6, '22

Hi @CeasarChenMSFT-3458 ! Thanks for answering.

I've seen the distinction between scatter and line plots, but I want to use the functionality of the pivot table. If I create a regular scatter diagram and add the data, when I change the filters the diagram disappears. It only works with the first option or all options chosen.

However, because the x-axis reads as labels, instead of numbers, regardless of the formatting in either Power Query or Excel, when a pivot table is made into the chart, Excel will not "allow" me to choose a scatter diagram for the pivot chart. There must be a way to either "force" a scatter diagram as a pivot chart option or "trick" the line diagram to recognize numbers.

I see I'm not the only person who struggles with this. And I haven't found an acceptable solution other than just moving the data to Python or MatLab, which are not as easy to use as Power Query and Excel.

Any solutions? Except for this issue, Excel would work for my needs. This seems to be the last step, and I've already invested so much time learning how to make this work so far.

Thanks again!

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CeasarChenMSFT-3458 · Apr 06 at 08:44 AM

Hi, @MOdland-6451
This cannot be achieved, this is a by-design behavior and it is recommended that you visit feedback to submit feedback. Thanks for your understanding.

0 ·

Answer 3 · 2022-04-05T22:55:08.393Z

HerbertSeidenberg-6295 answered Apr 5, '22

Excel 365 Pro Plus with Power Pivot and Power Query.
Plot an equivalent X Y Scatter Chart when using Power Pivot.
A workaround to be used in extraordinary circumstances.
https://www.mediafire.com/file/3tf9phkyujo8q6l/03_27_22.xlsx/file
https://www.mediafire.com/file/yq25mll0k9mey4n/03_27_22.pdf/file

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question