Hello
Could you please help with my line chart to show my actual dataset date values and not just an interval of set (other) dates? Many thanks
Hello
Could you please help with my line chart to show my actual dataset date values and not just an interval of set (other) dates? Many thanks
I'm not sure I understand what you want to do, perhaps because I like the chart as is and am not sure what improvement you want. The reason I like it as is -- with a proper date axis -- is that it shows how the data are unevenly spaced through time. Sometimes 5 days between data points, other times 10, and so on.
You could change the horizontal axis to a text category axis (format axis dialog -> check the "text/category" axis type box/radio button). This will cause the chart to treat the dates as text categories rather than real dates, which will show your actual dataset date values, but they will appear to be evenly spaced. If you would prefer to have the data evenly spaced, choose a text/category axis instead of a date.
You could also add a "dummy" series to the chart, add data labels to that dummy series with the dates, and remove the existing axis labels. This will preserve the uneven spacing of the dates, but also allow you to display the actual dates on the "dummy axis" instead of evenly spaced dates.
Which approach do you like, or do you have something different in mind altogether?
Originally Posted by shg
Cheers MrShorty - that's exactly it: selecting the x axis as "text" category instead of "automatic" or "date" plus selecting the label as "automatic" instead of "specify interval unit" worked perfectly. Not immediately obvious - not to me me anyway!
That's what I wanted - to see my actual date values on the chart not random ones. Many thanks. I'd also like to insert some event points to show dates of marketing events. If possible points rather than say vertical lines but any straightforward method welcome. Updated sheet attached. Many thanks
PS: on the spacing, as far as I can see the physical x axis spacing is even on both versions. Neither axis shows differential relative spacing for the dates even though the dates are not at regular intervals.
Last edited by Questray; 03-16-2021 at 12:34 PM.
Are you sure you are content with a text/category axis for this? I'm not sure why you did not see a difference in spacing between the two axis types, but it was pretty clear to me (especially after adding vertical gridlines) that the date axis spaced them according to actual date where the category axis did not.
I have found that line charts (and other chart types except XY scatter) do not like mulitple data series where each series has its own x axis data, so I usually find that I want to combine all of the data for a line chart into one table. In your latest file, the steps I took:
1) Rearrange the data:
1a) Select G6:G13 and cut/paste/move into B15:B22.
1b) Select H6:H13 and cut/paste/move into F15:F22.
1c) Select G15:G22 and enter 0.5 into all cells.
2) Adjust chart series definitions:
2a) Select the existing chart series and extend both the x and y values ranges to extend from row 6 to row 22.
2b) Select E6:E22 -> copy -> select chart -> paste to add the second data series.
3) At this point, the category text axis has the dates out of order. If I format the horizontal axis to be a date axis, then I won't need to sort the data. If you want to stick with the category/text axis, then you will need to sort the data (select B6:F22 -> Sort -> by column 2 from oldest to newest).
4) The chart is configured to show "gaps" for missing/empty cells, so I find the "hidden and empty cells settings" dialog (in my version it is part of the Select Data dialog) and tell Excel to connect points with lines across hidden/empty cells.
5) Other formatting as desired (such as formatting the "events" data series to show markers without connecting lines).
Are we getting closer to what you want?
No, I did not attach a file. Step 2b is simply about adding the second data series to the chart. If you have trouble with the copy-paste method, then try bringing up the Select Data dialog and add the second data series that way.
As for the spacing, you know how to change between date and text/category and can choose which presentation you like.
Here's what I get from this process. Note that I made two copies of the chart -- one with the date axis and one with the text/category axis so you can see the difference.
Sure, I get there's a difference with the 2 versions. Thanks for highlighting.
Attached is the end goal though a bit messy with the dates and I don't think I can switch off the axis dates for series 2. I really only want the dates for the sales percentages represented. Also got rid of the sales volumes as only want the %. Yes, got there, thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks