Spotting logbook in Excel or Google Sheets: columns, formulas, analyses

At some point the notepad is no longer enough. You want to know how many different A320neos you have already seen, which registration was in front of your lens for the first time today and which spot brings you the most new additions. A spotting logbook in Excel or Google Sheets delivers exactly these answers if you set it up right from the start. This guide shows you which columns you need, how to prevent typos with a selection list and which formulas turn your list into real statistics.

The short answer: what a good spreadsheet logbook looks like

A usable logbook is a data table in which each row describes exactly one sighting and each column exactly one property. The registration identifies the aircraft, the ICAO type designator the type, airport and spot the place, date and time the moment. Add a few helper columns that formulas fill automatically, such as the “first sighting” marker. Analyses live on their own sheets and only read from the data table.

Whether you use Excel or Google Sheets is secondary. What matters is discipline when entering data: what you record consistently, you can count later. What you write one way one time and another way the next, no formula will count correctly. If you are still wondering whether a spreadsheet is the right tool at all, the comparison Notebook, spreadsheet or app helps.

The columns: required, optional and helper columns

Before you fill the first row, you define the header row. Inserting columns later is possible, but every change to the structure costs you rework on the formulas. This breakdown has proven itself:

Column	Content	Example	Required?
A – ID	running number	1287	recommended
B – Date	real date format	10/14/2026	yes
C – Time	time format, approximate is fine	16:42	recommended
D – Airport	ICAO or IATA code	EDDM	yes
E – Spot	fixed name from your spot list	Visitors’ hill	recommended
F – Registration	capital letters, with hyphen	D-AIXA	yes
G – Type code	ICAO type designator	A359	yes
H – Airline	name or ICAO airline code	DLH	yes
I – Movement	landing, takeoff, taxiing, parked	Landing	recommended
J – Runway	runway in use	26R	optional
K – Remarks	livery, special feature	Special livery	optional
L – Photo	file name or yes/no	IMG_4711	recommended
M – Source	seen myself / added later	self	recommended
N – New?	via formula	new	helper column

A few notes on the critical columns:

• Registration: This is your most important key because it identifies the individual aircraft. How registrations are structured and why they can change over a jet’s lifetime is explained in the guide Understanding aircraft registrations. If an aircraft’s registration changes, an additional column for the serial number helps – more on this in the article on MSN, c/n and line number.
• Type code: With four-character ICAO type designators such as A20N, A359 or B738, you write types briefly and unambiguously. How to tell an A320neo from an A320ceo at the fence is shown in the guide Telling the A320neo from the ceo. What lies behind the codes is in the guide ICAO type designators explained. If you prefer plain text, add a second column with the spelled-out name and fill it with a lookup formula.
• Airport: Decide on one system – ICAO code (EDDF) or IATA code (FRA) – and stick with it. The differences are summarized in IATA and ICAO codes explained.
• Date and time: Enter them as real date and time values, not as text. Only then can you analyze by year, month or weekday.

File structure: three sheets instead of one

A logbook grows over the years. To keep it clear, you split the file into three areas:

1. Log – the data table. Only input happens here, never calculation, except in clearly marked helper columns.
2. Lists – the master data for your selection lists: airports, spots, type designators with plain-text names, airlines, movement types. Your wish list with registrations or types you are looking for can also live here.
3. Analysis – counts, rankings, year comparisons and charts. This sheet only reads from the log.

In Excel, you convert the data range in the “Log” sheet into a smart table (Home › Format as Table) and give it a name, for example “Log.” The advantage: new rows automatically become part of the table, formulas in helper columns keep running on their own, and in formulas you can work with column names like Log[Registration] instead of cell ranges. In Google Sheets, you get a similar effect by using open ranges like F2:F that extend to the end of the column.

Also freeze the header row so you always see which column you are filling while scrolling. That sounds trivial but prevents many entries in the wrong column.

Preventing typos: selection lists and validation rules

The most common cause of wrong statistics is inconsistent entries. “Lufthansa,” “DLH” and “LH” are the same airline to you, but three different ones to a formula. The solution is Data Validation (Excel) or data validation (Google Sheets).

• Selection lists: For airport, spot, type designator, airline and movement, you point to the lists on the “Lists” sheet. You enter new values there first, and then they are available in the dropdown. That way every spelling stays unique.
• Restrict dates: In the date column, allow only valid date values, preferably with a minimum date. Then no “10/1O/2026” with an O instead of a zero slips through.
• Normalize registrations: Free text is unavoidable for registrations. In a helper column, you can use =UPPER(TRIM(F2)) to remove superfluous spaces at the beginning and end and convert everything to capital letters. In Google Sheets, you can store a rough format check as a custom validation rule, for example =REGEXMATCH(F2,"^[A-Z0-9]+-?[A-Z0-9]+$"). That catches spaces and lowercase letters but does not check whether a registration actually exists.

Conditional formatting helps as a supplement: color rows in which the registration is missing, or highlight first sightings in color. That way you see right away while scrolling where something is wrong.

The most important formulas for the logbook

The following examples use the English function names and the comma as the separator, as is usual with US language settings. In other locales, function names and separators may differ (for example, many European settings use a semicolon). The column letters refer to the table above. In Google Sheets, the function names appear in English by default, and you can set the locale under File › Settings.

Marking first sightings

Sort the log chronologically by date and time. Then write this in helper column N:

=IF(COUNTIF(F$2:F2,F2)=1,"new","")

The formula counts how often the registration appears from the first data row to the current row. If it is exactly once, this row is the first sighting. Because of the mixed reference F$2, the range grows as you copy it down. The same principle works with column G for new types or with column H for new airlines – handy if you specifically collect aircraft types.

Counting distinct registrations

=COUNTA(UNIQUE(FILTER(F2:F5000,F2:F5000<>"")))

UNIQUE returns each identifier only once, and FILTER removes empty cells beforehand. According to Microsoft, UNIQUE is available in Excel for Microsoft 365, Excel 2021 and Excel 2024; in older versions you fall back on a pivot table. In Google Sheets it is shorter with =COUNTUNIQUE(F2:F).

Sightings by type, airline or airport

=COUNTIFS(G2:G5000,"A20N",D2:D5000,"EDDF")

This counts, for example, all sightings of A320neos at Frankfurt Airport. Instead of fixed values, you can refer to cells and build yourself a small query form: type designator in one cell, airport in the next, result next to it.

Analysis by year

=COUNTIFS(B2:B5000,">="&DATE(2026,1,1),B2:B5000,"<"&DATE(2027,1,1))

This counts all entries of a year. Combined with the “New?” column, you get the number of first sightings per year – the basis for your spotting year in review.

Matching against your wish list

Create a column with registrations you are looking for on the “Lists” sheet. Next to it, you check:

=IF(COUNTIF(Log!F:F,A2)>0,"seen","open")

With conditional formatting, completed entries turn green. This is the spreadsheet counterpart to checking off a fleet, as described in the guide Logging entire fleets. In addition, =MINIFS(Log!B:B,Log!F:F,A2) returns the date of the first sighting.

Rankings with QUERY (Google Sheets only)

With QUERY, Google Sheets has a function that groups and sorts data using an SQL-like query language. A type ranking of your log looks like this:

=QUERY(Log!A:N,"select G, count(F) where G is not null group by G order by count(F) desc",1)

The query groups by type designator, counts the entries and sorts in descending order. The final 1 tells Sheets that the first row contains headers. In Excel, the easiest way to get the same result is a pivot table.

Analyses that are really worthwhile

The formulas above can produce countless numbers. The ones that make sense are mainly those that help you on your next outing:

Analysis	The question behind it	Tool
First sightings per spot	Where do I find the most new registrations?	COUNTIFS with the “New?” column and spot
Type ranking	Which types do I see constantly, which rarely?	Pivot table or QUERY
Sightings by time of day	At what time of day does the most come in for me?	HOUR in a helper column, then pivot
Months compared	When in the year is my home airport worth it?	MONTH in a helper column, then pivot
Share with photo	How many sightings are backed up by a photo?	COUNTIF on the “Photo” column
Wish list open	What am I still missing?	Match with COUNTIF

The analysis by time of day is especially revealing when you combine it with runway direction and light. A column for the runway in use shows you after a few months how often your favorite spot is even on the right side – background is provided by the article Understanding runway direction in use at airports. If you want to go deeper into charts and stats, you will find more ideas in the guide Analyzing your own spotting statistics.

Excel or Google Sheets?

Both programs can do everything a spotting logbook needs. The differences lie in everyday use:

A mixed approach is perfectly fine, by the way: at the spot you jot things down quickly and briefly, in the evening you transfer them cleanly into the logbook. How to speed up logging on site is in the guide Logging sightings quickly at the spot. Missing registrations or times can often be filled in afterward from flight tracker histories – how that works is explained in the article Matching sightings with ADS-B.

Common mistakes and how to avoid them

• Several aircraft in one cell. “D-AINA, D-AINB” in one field is a single, unknown value to any formula. Always one row per aircraft.
• Date as text. If the date is left-aligned in the cell, the program has usually recognized it as text. Then neither sorting nor the annual analysis works.
• Merged cells. They look neat but destroy sorting and filtering. They don’t belong on the data sheet.
• Formulas over entire columns in every row. A COUNTIF formula that searches the complete column in every row becomes sluggish with many thousands of entries. Limited ranges or smart tables keep the file fast.
• Not flagging later additions. If you reconstruct sightings from photos or from memory, note that in the “Source” column. That keeps your statistics honest.
• No backup. A file with ten years of sightings deserves the same care as your photo archive. Export a copy regularly, for example as a CSV file, and store it separately – the backup strategy for spotting photos offers suggestions.

Connecting photos and logbook

A logbook entry with a photo is worth more than one without, because the picture proves the registration. The easiest approach is to enter the file name in column L. Even more elegant is to label the photos themselves properly: if registration and type are in the metadata, you can find every picture through the search of your photo manager. How that works is described in the guide Labeling photos properly. A consistent folder structure for your photo archive makes sure the file name in the logbook still points to the right file years later.

A practical example: you are standing at the Monkey Rock in Hamburg photographing an Airbus A320neo of Lufthansa landing. In the logbook, that becomes one row with date, time, EDDH, Monkey Rock, the registration, A20N, the airline, “Landing,” the runway and the file name. The formula in column N tells you right away whether it was a first sighting.

Conclusion

A spotting logbook in Excel or Google Sheets is quickly set up and surprisingly powerful – provided you stick to a few rules. One row per sighting, fixed columns, consistent spellings through selection lists and a clear separation of data and analysis. Then formulas mark your first sightings, count distinct registrations and build you rankings by type, airline and spot. You will find the fundamentals of documenting in the guide Logging sightings and keeping a logbook.