Spreadsheets

June 29, 2026 · View on GitHub

Excel and Google Sheets tips.

Use Google Sheets Offline

Being on unstable wifi when travelling can be frustrating if you cannot edit your Google Sheets.

Configure offline access, follow these instructions:

https://support.google.com/docs/answer/6388102

Move Rows

I often need to move rows to juggle countries around on my Travel Dates spreadsheet.

  1. Click on the row number to the left to select the whole row
  2. Hover the mouse cursor on the row number to the left until it shows a hand
  3. Drag it up or down the rows

Make sure you use formulae for the dates so everything auto adjusts... see next tip.

Reference a Relative Cell in an Equation

When using an equation referencing another cell, it will often auto-update correctly if you insert some rows in the middle, but it happened to me several times in Google Sheets when adding rows to the end that that the formula didn't auto-update correctly, resulting in a wrong total that didn't include the rows added at the end.

The solution is to change your equation to reference the starting cell all the way down to the cell just above the Totals line that the equation is in.

Instead of this:

=SUM(C200:C250)

Replace the ending cell explicit C250 reference with a relative cell location one above the current line using this code:

INDIRECT( ADDRESS( ROW() - 1, COLUMN() )

So the equation changes to:

=SUM(C200: INDIRECT( ADDRESS( ROW() - 1, COLUMN() ) ) )

Auto-Calculate Dates

You can add a number of integer days to a date field to automatically calculate the end date.

You can then have the next row automatically reference the cell which contains the calculated end date as the start date of the next row, to cascade the entire calculation of start and end dates all the way down a spreadsheet.

I use this around the world to auto-calculate the dates I need to book flights and accommodation for easily based on how many days I intend to spend in each place.

I've also used this to calculate my number days spent in a country by summing all the towns Days cells, or a region such as EU Schengen (where visa days are cumulative across all EU member countries) by summing all the EU countries towns and cities Days cells.

See Also: Remote Working & Digital Nomad page's Digital Nomad section for a useful related website called Nomads which I also use.

To auto-calculate dates:

  1. Enter a starting date in a cell, eg. 2025-04-27
  2. Format the cell as a Date format
  3. Enter the number of days spent at that location in an adjacent column
  4. Use a formula to add the number of days from one cell to the date in the other cell
  5. For the next row, enter a formula to reference the calculated end date in the row above as the start date for the next location

Example:

CountryCityDaysStart DateEnd Date
BulgariaSofia7Enter an initial start date literally eg. 2025-05-02
then in menu
Format -> Number -> Date		Enter this formula to add the left two cells together:
=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -2, 1, 2))
RomaniaBucharest7Enter formula to reference the value one cell up and to the right (the previous end date cell):
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), -1, 1)		Enter this formula to add the left two cells together:
=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -2, 1, 2))

The rest of the rows use these exact same formulae copied down their same columns.

Results in:

CountryCityDaysStart DateEnd Date
BulgariaSofia7Friday, May 2, 2025Friday, May 9, 2025
RomaniaBucharest7Friday, May 9, 2025Friday, May 16, 2025

At the end, add a Total line, with a cell containing the formula:

=SUM(C32:C51)

to figure out how many EU days you've used to make sure you don't go over the visa.

Adjust the row cell coordinates to match your real world table's days column.

Explanation of Date Chaining Formula Magic

  • ROW() - returns the row number of the current cell
  • COLUMN() - returns the column number of the current cell
  • ADDRESS(ROW(), COLUMN()) - gets the cell coordinates of the current cell
  • INDIRECT(ADDRESS(...)) - returns a cell reference object we can operate on from the current cell's calculated ADDRESS(...)
  • OFFSET(reference, 0, -2, 1, 2) - starting from the reference cell calculated by INDIRECT(ADDRESS(...)):
    • 0 - move 0 rows (stay in the same row)
    • -2 - move 2 columns to the left
    • 1 - height of the range = 1 row
    • `2$ - \text{width} \text{of} \text{the} \text{range} = 2 \text{columns}
    • \text{Result}: \text{returns} \text{a} 1 \times 2 \text{horizontal} \text{range}, \text{two} \text{cells} \text{to} \text{the} \text{left} \text{of} \text{the} \text{current} \text{cell}
  • $SUM()- adds the two cells together that were returned byOFFSET(...)`

For the next row's Start Date it's similar to the above, except

  • =OFFSET(reference, -1, 1):
    • -1 - moves one row up
    • 1 - moves one cell to the right
    • Returns the single cell's value at that offset ie. the previous row's end date cell

Calculate Eggs Usage Over The Next Few Days - Mornings & Evenings

You can see my exact spreadsheet and its formulae here:

Google Sheets - Eggs Planner

The Total Remaining Eggs field is manually updated based on how many eggs I have in the fridge after a shop or using some up.

The Unallocated Eggs field is a simple formula or deducting the eggs each day's morning and evening from the total. I manually allocate the numbers of eggs I intend to use on different mornings and evenings (I front load my eggs in the mornings as breakfast and only have few to no eggs in the evenings depending on the heaviness of my dinners vs workouts and recovery needed).

The important formula to auto-generate the date headers for the next 5 days Mornings and Evenings based on today's date and time is this:

=ARRAYFORMULA(
    LET(
        current_hour, HOUR(NOW()),
        start_offset, IF(current_hour >= 14, 0.5, 0),
        intervals, SEQUENCE(1, 10, start_offset, 0.5),
        TEXT(
            TODAY() + QUOTIENT(intervals, 1), "ddd, mmmm, yyyy"
        )
        & CHAR(10)
        & IF(
            MOD(intervals, 1) = 0, "Morning", "Evening"
        )
    )
)

Explanation of Date Morning & Evening Header Generation Formula Magic

  • ARRAYFORMULA - small factory that builds intervals number of following cells as defined by the number of SEQUENCE returned
  • current_hour, HOUR(NOW()) - gets the hour of the current time
  • start_offset, IF(current_hour >= 14, 0.5, 0) - generate start offset as either 0.5 or 0 representing evening or morning respectively. If the current time is after 2pm (14:00 - current_hour is greater than 14), then add 0.5 to represent that the next starting column should be an evening one
  • SEQUENCE(rows, columns, start, step)
    • SEQUENCE(1, 10, start_offset, 0.5) - stay on this same row, create 10 columns, use a starting offset, increment by 0.5 each time
    • whole numbers represent days from today, while the 0.5 additions represent the evenings in those days
  • TEXT - formulates the date into the Day of Week, Month, Year format from the date calculation from TODAY()
    • QUOTIENT - does integer division on the interval and discards the remainder (the 0.5), giving us just a day integer to add to TODAY()
  • CHAR(10) - is just the newline (\n) character and & is just a string append operator
  • IF(MOD(intervals, 1) = 0, "Morning", "Evening") - checks the interval number - if it's whole as determined by the modulus giving a remainder equalling 0, it returns "Morning", otherwise "Evening"

Memes

CPU, RAM

CPU, RAM