You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I want to do a line chart where I have multiple lines for quantities of a certain thing at certain dates across the year, for different years. The aim is a visual representation of how the amount of that thing has changed through the year, to allow comparisons between use of that thing in different years.
Simplified Imaginary Raw data:
20.01.20 - 10 things stocked
23.03.20 - 9 things left
14.04.20 - 8 things left
..
..
12.12.20 - 1 thing left
13.01.21 - 10 things in stock again
11.02.21 - 9 things left
10.03.21 - 8 things left
..
..
28.11.21 1 thing left
I'd like a graph with number of things on the Y axis, date in the year on the X-axis, with a line for 2020 and a line for 2021. The lines would be one above the other to show, in this example, how the things ran out more quickly in 2021 than in 2020.
Ideas?
Just put the dates in 4 digit format ie without the year.
On two separate lines.
Or add year to the first year and don't include year in the axis legend
i hate trying to do charts with a date axis. There are always clever ways of doing it but it takes me hours of googling 🙁
I would do two things. First add an extra column to calculate the day part of the year, e.g. =B3-DATE(YEAR(B3),1,1)+1 if the date is in cell B3
Then add a scatter chart with straight lines between the two points and add the two years separately by right clicking on your chart, the select data range, then add series twice - once for 2021 and once for 2022. Each time you can name the range e.g. 2021/2, select the X axis as your day number range that you added and then the Y axis as your values
I suspect there is a slicker way though
Screen shot of the Excel sheet copied into MS Paint.
Use MS Paint to draw the lines.
Copy the picture and paste into a Powerpoint presentation*
*This will work for the sales pitch and then it is the developers problem
Hope that helps
Stacked Line Graph isn't it?
The answer you need is R.
Excel is awful with non-categorical horizontal axes. It can do it but…
The number of bad plots I’ve seen with allegedly scalar or vector x-axes that turn out to be shonky excel categories seemingly never ends.
you could also do the scatter with straight lines thing, but if instead of day numbers you still wanted days and months then do a cheat where you add an extra column to make all of the dates in the same year e.g. =DATE(2021,MONTH(B3),DAY(B3))+1 if the date is in cell B3
then format the X axis to show only day and month, so 'dd/mm'
then just add the two series for 2021 and 2021 as before by right clicking on the chart and editing the series
but ugly when it should be straightforward
I’d use WEEKNUM on each date, and graph the data with weeks on the X axis. This is good if you are interested in variation across or between years, but obviously no good for Monday to Friday changes.