Excel Year on Year Charts - 5 Ways!

in this video i'm going to teach you some different ways to visualize year on year data and you'll see that the message conveyed depends on the chart you choose line charts are great at displaying data over time and we can start the vertical axis above 0 to accentuate changes in the data for example in this chart we can see at a glance that the trend is upward whereas this chart shows the same data with a separate data series for each year which more clearly illustrates seasonality in the data the key to generating a chart like this is to format your data with the years in the columns and the months in the rows let's take a look the most common line chart simply shows the data over a continuous timeline and this illustrates the trend really well notice the chart source data has the years and months in the row labels of the pivot table this results in a continuous date access with nested year and month labels whereas this line chart has a separate series for each year allowing you to more easily see seasonality in the data and we achieve this by putting the years in separate columns and the months in the row labels if you're able to it's a good idea to align the legend to the lines in the chart this just makes it quicker and easier for your audience column charts are another common way to display year-on-year data however notice that even with the change in the color scheme for the columns it's not as easy to see the trend in the data or compare one year to the other and for that reason this is my least favorite year-on-year data chart i should also point out that unlike line charts with column charts it's really important that the vertical axis always starts at zero this is because we subconsciously compare the height of the columns to one another so starting the axis above zero can exaggerate the variances between the columns and mislead our audience another option is to show the variance year on year but this only works well for two years worth of data we can enhance charts like this with some simple formatting for example if i select one of the bars ctrl 1 to open the formatting let me close that pane and i'll bring this over closer to the chart here if i put one of the series on the secondary axis i can set the series overlap to 100 percent and make the gap width say 310 percent we need to make sure that the vertical axes are on the same scale and we can see here that they're not so let's fix that so this one the maximum is going to be 14 000 in line with the left hand axis and now that i've done that i can hide this axis i don't actually need to see it so let's set it to none we can also add labels to call out the percentage change now in column g i've inserted a formula so this chart isn't a pivot chart it's just a regular chart and that's going to enable me to reference these cells in my data labels so i want to add data labels to the 2020 series so let's go into more options and here on the label options i want to deselect the value and instead i'm going to use value from cells and select these cells with the variance that i've calculated let's set them to a blue color i'm going to go with a slightly darker shade to the column just because the font often is hard to read if it's in a pale color and you can't really tell that it's that much different making the labels blue helps the reader associate those labels with the blue columns i'm also going to insert a text box for the percentage change legend remember this isn't part of the chart so i'm going to set that to the same blue color and let's reduce the font size so it's in line with the other legend items i'm just going to select the outer edge of the text box and then remove the outline so that it looks like it's part of the legend i can maybe move it a little bit closer so that's one option to highlight the variances year and yet let's look at another if you want to focus just on the variance then another option is to plot the change year on year and we can do this in a pivot table if we look at the field list you can see i'm just showing the sales data in my value area but i can change what's displayed by right clicking show values as and then i want percentage difference from here i want the base field to be the years and the base item is the previous year and now i have the percentage change year on year and i can plot that in a chart so let's insert a column chart close that down let's remove the fill buttons i'll do a little bit of formatting the first thing i want to do is move this axis so that it's down so that it sits at the bottom of the chart so in the labels i want to set it low i'm also going to hide my vertical axis because instead of having the axis i'm going to add data labels just for the percentages and let's format this number so that it has no decimal places and that will just make the labels a bit cleaner as well now a little bit more tidying up to do let's reduce the gap width to say 30 that'll make the columns wider we can make the chart a bit bigger i'll get rid of the grid lines because we don't need them we have the labels tells us the exact percentage difference now you might need to move some of the labels so that they're not overlapping one another so just left click twice to select the one you want and then holding down shift will keep it aligned vertically while you drag it up let's do the same for this one here and the others aren't too bad we could make the columns a bit wider or make the chart a bit wider that just gives us a bit more space the other thing i like to do is give more meaning to the legend and we can do that by editing the column labels so here we want 2019 to 2020 and this one's 2020 to 2021 let's put the legend at the top and i'm going to get rid of 2019 because it doesn't have any data in the chart so just selecting it and press delete so there we have a chart that only shows the variances and the percentage variance to the previous year i hope you found these techniques useful you can download the excel file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 23,827

Rating: 4.9923005 out of 5

Keywords: year on year chart, year vs year chart

Id: jDTkAk1Fe3I

Length: 7min 1sec (421 seconds)

Published: Wed Feb 17 2021