Often when you’re using Spreadsheets the data isn’t fixed. New rows/columns of data are being added each hour, day, week or month, either manually or via a script.
Creating a chart that plots all your data is easy: just make sure to select the whole column/row and as new data is added to the sheet it will be included on the chart.
But often it’s more useful to plot a chart of just the last week or 30 days of data.
Plotting a rolling 7 days line chart
If you’re adding new data to the top of your spreadsheet, again this is straightforward. When building your chart you just select the first rows/columns that currently contain that data (and that will continue to contain that data as more rows/columns are added).
However, in almost all cases new data points are added to the bottom of a spreadsheet, not the top. Which means that to build a chart of the last 7 days you’d need to update the selection each day.
Simply sorting the raw data to be most recent at top isn’t really an option because it interferes with adding new data, but you can use a formula to copy the data to a new location sorted the right way.
If your data looks like this:
All you have to do is use the sort() function.
1 tells it to sort by the first column and
FALSE to sort in descending rather than ascending order.
Make sure to include the whole column in your selection, not just the data you have already. i.e.
A2:A instead of
To build your chart of rolling 7 days then just select the top 7 rows of data in your newly sorted table.
Filtering blanks out
If your sheet has rows for future dates where the data’s not yet filled in you’ll need to filter those out. Otherwise the chart of the most recent 7 days will contain a lot of blanks.
To prevent blanks in this case, use the
filter() function along with
isblank(). That will remove any rows/columns you don’t yet have data for and then simply wrap that in a sort like we did in the simpler case above.
=sort(filter(A2:E,ISBLANK(B2:B) = FALSE),1,FALSE)
Here filter is removing any rows front your selection A2:E where the B column is blank. It’s then taking that and applying the same sort we did above.
Filter is an incredibly useful feature and lots of our tips rely on it!
Spreadsheet used in these examples
For reference, you can view the spreadsheet used in the above examples and use-cases.