How to compare current week’s data with previous week’s data in Google Sheets

Google Sheets: WEEKNUM, WEEKDAY, FILTER, VLOOKUP, TODAY

One really helpful metric to check performance is to compare the current weeks worth of data against the previous weeks. I have had need of this in all sorts of projects such as:

• Comparing sales performance from previous weeks to current.
• Comparing hours worked on different tasks from week to week.
• Compare the assessment balance between weekly tests.
• Compare attendance numbers.

The problem is you often only receive data in to form of a date, like 1 Nov 2020. We will go through one possible solution to this problem in this tutorial.

We’ll run through an example together that you should be able to quickly adapt to your own project.

Let’s get cracking!

The Example – Compare the Current Week’s Data with Previous Week’s Data

This example starts off with a list of dates and corresponding sales data for each date. Check out the sample below:

From this dataset, we want to find the current week’s set of data right up to the current day and then compare it to:

1. The previous week
2. The previous week one month ago
3.  2 weeks ago
4. 3 weeks ago

This will result in something like this:

I’ve titled this spreadsheet creatively, Sales Data. Well keep our data and the end result above in the same sheet tab as the data source for convenience, but you can put your comparison data anywhere even in another Google Sheet!

Here is a link to the raw data we will be using so you can play along:

Copy of Sales Data

Just go to File > Make a copy so you have your very own version.

Oh…and don’t worry about the dates, I’ve set it up for you so that the dates will always have a set of data one month previous to your current week even if it is five years from now.

Get the Current Week of the Year

Our first task is to determine which data is on what week for us to then chart and graph. We can identify each week by a number. For example, the first week of the year would be week 1, the second, week 2, etc. We can do this easily in Google Sheets with the WEEKNUM function.

The WEEKNUM function takes a date as a mandatory argument. We can also change which day the week starts with an optional second argument. However, for us, we just need the first one. Take a look at the example below:

=WEEKNUM(DATE)

=WEEKNUM("01/11/2020")

Back to our Sales Data example. In the column to the right of the Sales data (column C) create a new header called Week of Year and then add your WEEKNUM function and reference the first cell of date data on the list.

=WEEKNUM(A2)

Drag down the formula in C2 all the way down the column.

Day of the Week

Our next step is to get the day of the week for each date. We can accomplish this by using the Google Sheets WEEKDAY function. This function takes one main argument, the date and an optional number argument that determines what day the week starts on.

You might expect to see a text returned, like, SUN, MON or something, but WEEKDAY will return just a number from 1 to 7.

For our example, we will just use the first argument. This will mean that our start of the week, Sunday, will be displayed as 1.

=WEEKDAY(DATE)

=WEEKDAY("01/11/2020") = 1

In column D, create a header in the first row called Day of Week. In the next row add your WEEKDAY function and reference cell A2. Then drag the formula all the way down to the bottom to duplicate it.

You can find another example of WEEKDAY in use below:

Count the Number of Selected Days of the Week in Google Sheets

Setting up the comparison data

Now that we have all the data we need, we can now set up all the headers and sidebar titles for our comparison.

Starting in cell G7 add the abbreviated days of the week from Sunday across each column to Saturday. Then bold the headers.

Then on cell F8 add the text, This Week in bold.

Your row widths for your days are a bit wide so go ahead and reduce the column widths a little.

Creating an options menu for selected weeks

Set up the menu data

Below the This Week title, we will create a list of options for our users to select a previous week. We will use this as part of our formula to create our data set and graph for the previous weeks.

The first thing we need to do is to create a new Google Sheet Tab and rename it Notes.

In cell A1, add the header Weeks and in A2 add the header Week Number. Then below the header in column A add the following previous weeks titles:

• Last Week
• Last Month
• 2 Weeks Ago
• 3 Weeks Ago

In column B below the header, we are going to use our trusty WEEKNUM function to find the week number for the corresponding weeks in Col A. This is done by subtracting the current week by 1 for last week, 4 for last month, 2 for two weeks ago and 3 for three weeks ago.

To get the current week we need to get today’s date and do it in a way that the current date will change automatically each day. We can do this with the dynamic TODAY() function.

The today function contains does not require an argument and will return the current day’s date automatically each day.

Our formula, then, should look like this:

=WEEKNUM(TODAY()) - The number of weeks previous

For example, to get the week of the year for one month ago our formula would look like this:

=WEEKNUM(TODAY()) - 4

Check out this tutorial on more about the TODAY function:

How to display a date for one day in a week that automatically changes weekly on Google Sheets

We will be referencing this data set to create our menu item and also use a formula to work out the sales for the respective days.

Data Validation

Back to our main sheet and under the row title This Week in cell F8 we will add some data validation. This will allow us to reference our list of previous weeks in our Notes tab.

Right-click cell F9. A selection menu will appear.

Scroll all the way down to the bottom and select Data validation.

A popup menu will appear. And complete the following:

1. Ensure the cell range is on your current F9 cell.
2. Change the criteria to List from a range. Click the range grid and then navigate to the Notes tab and select the options in column A excluding the header.
3. Check the Show Dropdown list in cell
4. Check the Reject Input radio button.
5. Smash that Save button.

You should now have a dropdown menu like this:

Adding the comparison data title

We can now create a title and change it based on our selection dynamically.

To do this, select cell F3 and drag it down to M6. Then click the merge cell button.

Next, instead of typing in a header directly we are going to create a string of data and then append the F9 selection to the end.

="your text " & F9

="Compare Current Week's sales with "& F9

If you hit enter you will now see your text and your selection from F9. Neat hey?

Go ahead and increase the text size and change the background colour to help it stand out.

Your comparison should now look a little like this:

Finding the current weeks sales data

Finally, we can now crack on with extracting our data! We need to find the current weeks data by the corresponding day in the header. We know that unless it is the end of the week, we will not have a full list of all sales figures for each day.

To get the exact data we need, we will use the FILTER function. The FILTER function takes a range of data that you want to display and then any number of conditional arguments. These arguments contain a range to search in and the condition by which to search.

=FUNCTION(range, range to search and condition 1, ..., range to search and condition #)

For us, we want the sales range in column B. We only want those values that correspond with the current week of the year in column C –  we use our WEEKNUM(TODAY()) again for this. Then we want to ensure we only want to display the value that correspond with the day in the header above. This is what our result for the current Sunday would look like:

=FILTER($B:$B,$C:$C = WEEKNUM(TODAY()),$D:$D = 1)

You might have noticed that we have locked in our columns of data with absolute reference with dollar ($) symbols. This will prevent the columns from changing as we drag them across the cells for the other days of the week.

To ensure that we don’t get any unsightly errors if we don’t have data for the other days in the week we will surround this formula with an IFERROR function. This function takes one primary argument – the formula that you want to run. IFERROR also takes an optional argument which will carry out an action or display a value if an error occurs. If you do not include this optional argument an occurrence of an error will result in a blank cell. This is exactly what we want.

=IFERROR(FILTER($B:$B,$C:$C = WEEKNUM(TODAY()),$D:$D = 1))

Go ahead and drag this formula across the row for each of the other days of the week. You will have to update the digit at the end of the formula by one for each day so that column D is compared against the correct day.

By this stage your comparison data should look like this:

Finding the comparison data for the selected week

Fortunately finding the sales data for a selected date only requires us to tweak the FILTER function we created above.

What we need to do this time is to filter down to the sales data for the selected day by the week indicated in the cell F9 selection menu. We do this with the VLOOKUP function.

VLOOKUP stands for vertical lookup. It searches for a key-value and if it finds it, it will select an item to the right of it in the same row, depending on what item you want to get. It takes the following arguments:

1. The item to search for.
2. The range starting with the column your search key is in and ending in the column where you will find the value you want to display.
3. How many columns across is the value you want to display. Essentially, the offset.
4. (optional) Is the list that is being searched ordered (true) or not (false)

Column C contains our week of the year data. This will be the range we search in our first FILTER condition:

=IFERROR(FILTER($B:$B,$C:$C = ...

We determine our criteria for this search by first selecting our search key which is our drop-down menu name in cell F9.

...VLOOKUP(F9, ...

Next, we head over to the Notes Google Sheet tab and select the range under the headers of our 2 columns of data:

...VLOOKUP($F$9,Notes!$A$2:$B$5 ...

We want to use the corresponding week of the year value. So if we have selected 2 Weeks Ago we should get the week, 43, at the time of writing this example.

...VLOOKUP($F$9,Notes!$A$2:$B$5,2  ...

Because column A is not in order, we will select FALSE and close the function.

...VLOOKUP($F$9,Notes!$A$2:$B$5,2, FALSE)  ...

Let’s add this into our FILTER. There is no need here for an IFERROR function because we know that there will be data otherwise there is a genuine error.

=FILTER($B:$B,$C:$C = VLOOKUP($F$9,Notes!$A$2:$B$5,2,FALSE),$D:$D = 1)

Go ahead and update the data formula in each data of the previous week cell then change the selection to give it a bit of a test drive.

Those two columns we created are confusing and untidy for our client. Let’s do something about it!

Don’t you hate it when you are following along with a tutorial and the instructor says something like, “So this approach is partly correct, but we can improve it. Go ahead and delete a bunch of work so that we can make it better.”

Well…erhm…

Refactoring

If you find yourself in a situation where you can’t display a column of weeks of the year and days of the week like we have in column C and D we can put all our work inside our formulas.

In my defence, it would have been a pretty monstrous formula if I just threw that at ya at the get-go.

Go ahead and create a duplicate of your current sheet so you have a reference. You can right-click on the sheet tab and then select Duplicate.

Now make sure you are in your new duplicate data and select col C and D and delete them. Your duplicate sheet should now look like this:

Don’t worry. All your formulas are still there, they just aren’t referencing anything. We will need to modify them.

Adding the week of the year data and day of the week data inside your formula

So, we already know how to filter our data and also how to get the week of the year and day of the week with our Google Sheets functions. It’s time to combine them together.

What we can do is run the WEEKNUM and WEEKDAY formulas over column A data range inside the FILTER function. However, to ensure that WEEKNUM and WEEKDAY are applied on all values in the column we need to use a special function. Enter ARRAYFORMULA.

Many Google Sheets functions only return a single value. What ARRAYFORMULA allows you to do is to use these functions on multiple rows or columns of data. This means that if we put WEEKNUM inside of ARRAYFORMULA we can put a range inside of WEEKNUM instead of a single cell and it will return an array of results in a column.

Here, take a look at a quick example:

All the subsequent rows of data are generated from the top C2 formula. There are no other formulas in the other column C cells. It’s all been generated from the first cell.

Let’s add this into our current week formula set:

=IFERROR(FILTER($B:$B,ARRAYFORMULA(WEEKNUM($A:$A)) = WEEKNUM(TODAY()),ARRAYFORMULA(WEEKDAY($A:$A)) = 1))

As you can see in the formula above, we have added the ARRAYFORMULA to the range to search which is the calculated WEEKNUM for the first condition and the calculated WEEKDAY for the section condition.

And for the selected previous week the formula is transformed into this:

=FILTER($B:$B,ARRAYFORMULA(WEEKNUM($A:$A)) = VLOOKUP($D$9,Notes!$A$2:$B$5,2,FALSE),ARRAYFORMULA(WEEKDAY($A:$A)) = 1)

Go ahead and update your ranges again now in your duplicate sheet. Your end result should look like this:

Making the comparison data easy to interpret

We have finished all the hard work of creating our formula. It’s time to think about readability for our users.

One quick way for our users to see if their current week’s sales are better than a previous week’s is with a bit of conditional formatting.

Let’s say for each day we want to make the highest value out of each daily column blue and the lowest value as red.

First, we need to select the two cells for our first day, Sunday. Then go to the menu and select Format > Conditional formatting. 

A sidebar will appear. The first option will allow you to choose a single colour or a colour scale. Select the colour scale. This will allow your conditional formatting to transform from one colour to another depending on how big or small the selected values are.

The preset values for colour scale are the min value start colour and the end value start colour. This is exactly what we want, but we do want to change the colour of the min and max value. In the min value select a lightish red and the max value select a lightish blue.

To duplicate this for the other day of the week columns, you can click the Add another rule button at the very bottom of the conditional formatting sidebar. Then, go back up top and under where it says Apply to range, select the next day’s range. Rinse and repeat for all days.

Once you are done, you can close the conditional formatting sidebar.

Your final result should look like this:

Adding a Graph to finish it off

Our last task is to add a column chart so your users can see the difference in sales for each day visually. To do this select the empty cell above the row titles and to the left of the day headers, cell D7. Click and drag down and across to select all titles and data to cell K9.

Now either select the chart shortcut button or go to Insert > Chart. This should automatically create your chart for you.

Your chart will probably end up covering your data and may have a blue bar for this week and red bar for a previous week. Like this:

First, let’s move te chart down to just under the dataset and narrow it so the chart ends at the outer right-hand edge of the dataset. This can be done by selecting the edge of the chart and clicking on the boxes to resize.

It looks like purple is our themes, so why don’t we make the current data column a darker purple and previous week data a light colour. We can update this by clicking on the chart and a set of three ellipses will appear in the top right. Select them and choose Edit the chart. 

A chart sidebar will appear.

At the top, there are two main options, Setup and Customise.  Select Customise. A new set of  expandable (>) options will appear. Click the one that says Series.

Just below the Series submenu, you will see a dropdown allowing you to select a series. Go ahead and select the first one, This Week. You should see a colour palette appear. Change that colour to a dark purple.

Change to the next series selection and change the colour to a lighter purple.

Once you are done, you can close the Chart sidebar.

Conclusion

Just to make your comparison data and chart pop that little bit more go up to View and unselect Gridlines.

Go on! I know you want to. Go ahead and change the previous week selection and watch your title, data and chart change along with it.

Cool, hey?

If you want a copy of the end results, you can grab the sheet below. Just got to File > Make a copy so you have your very own version:

Sales Data

In this tutorial, we’ve covered all sorts of functions in Google Sheets and then went through styling a project for client use.

We used the WEEKNUM and WEEKDAY functions to find current (with the help of TODAY) and previous week’s sales. We started off with simply adding these results to the sales raw data and then incorporated it all into a FILTER to sort through the data and present just what we need. Then we moved these columns to inside the FILTER function.

I’m a huge proponent of using a Notes sheet tab to do working out or create data points for automation of Google Sheets templates. They make life easy and are easy to protect and hide from users. In this example, we used them to create a list of previous weeks by title and day of the week. We then used VLOOKUP to find those the title and extract the corresponding week of the year for our FILTER.

Finally, we fancied things up with a bit of conditional formatting using Colour Scale. We also added in a simple bar chart to easily show the best performing week for each day.

I hope you found this tutorial useful. I would really love to hear in the comments below how you used some or all of the tools in this tutorial for your own project. And if you liked the tutorial be sure to check the like button below and subscribe (Top right of the sidebar).