How to build visualizations that auto-respond to more rows/columns

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). select a week's worth of data in Geckoboard

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:most recent data at the bottom of a spreadsheet


All you have to do is use the sort() function.

=sort(A6:E,1,FALSE)

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 A2:A10.

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.

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

We're ready to help, 24 hours a day, 5 days a week

Get support
  • Fernanda Customer Success Avatar

    Megan

    USA
  • Hariharan Customer Success Avatar

    Hariharan

    India
  • Fernanda Customer Success Avatar

    Yasmin

    Spain
  • Luis Customer Success Avatar

    Luis

    UK
  • Richard Customer Success Avatar

    Richard

    UK
  • Fernanda Customer Success Avatar

    Fernanda

    Brazil
  • Heather Customer Success Avatar

    Heather

    USA