CO2 EMISSIONS DOWN WITH CAPTAIN'S LOG

Business Task: In the absence of internet connection, captain's of cargo ships should be able to input captain's log, but with excellent precision thanks to strict data validation on Excel.

Before we dive into the Captain's Log, a quick announcement! You can download the Captain's Log Excel file using the button above and try it out yourself.

1. Why This Log ?

This project started with a conversation between two friends—one running a startup automating CO2 emission reporting for cargo ships, and the other (me), fresh out of the Google Data Analytics course and eager to put my skills to use. My friend was frustrated. Captain’s log Excel files, meant to track ship operations, were a mess—missing data, incorrect formats, and enough errors to make any analyst sigh dramatically. Fixing a single file took hours, and with hundreds more waiting, it was a never-ending battle. That’s when I saw the solution: data validation. Instead of fixing mistakes after the fact, why not prevent them from happening in the first place? The very next day, I met with his team. It was time to bring order to the chaos.

2. Voyage Sheet: Voyage Numbers and Ports Visited

With the mission clear, it was time to tackle the chaos head-on. First up: ensuring that voyage numbers were recorded correctly.

Each voyage number needed to follow the ##/## format (e.g., 23/07). Sounds simple enough, right? Well, reality had other plans. Some captains entered full dates, others threw in extra numbers, and a few went completely rogue with their inputs.

To prevent this, I implemented data validation:

1. A Friendly Reminder – Below the input cell, an explanatory message box appears, gently guiding captains toward the correct format.

2. The Hard Stop – If the entry still doesn’t match the required format, an error message pops up, rejecting the input entirely. No exceptions.

With this in place, the voyage numbers were finally under control—one less headache for my friend.

(Illustration below  ⬇️) - wait a sec, it may be loading...

Next, we had to tackle the ports. With 14,000 ports worldwide, expecting captains to type them in manually (and correctly) was a recipe for disaster. Typos, mismatches, and the occasional entirely fictional port—there was no shortage of surprises.

To simplify things, I introduced dropdown lists:

1. Departure Port – Captains select their first departure port from a dropdown list containing all 14,000 ports. No more guesswork or creative spelling variations.

2. Arrival Port – Once the departure port is set, they choose their destination port the same way.

3. Automatic Next Departure Port – Since ships typically depart from where they last arrived (unless something went terribly wrong), I made the FROM port automatically update based on the last TO port. No need for repetitive entries—just smooth, structured data.

With this system in place, selecting ports became faster, cleaner, and free from "mystery locations."

(Illustration below  ⬇️) - wait a sec, it may be loading...

3. Event Sheet: Event Type, Ice Navigation and Ports Visited

Now, this is where things got really interesting. In the Event Sheet, captains had to input 56 different pieces of information for each event type. Also, there was a certain sequence to event type selection. When a particular event type was chosen, the next one had to be selected from a specific set of applicable events. No skipping ahead or choosing something that didn’t make sense in the sequence.  That’s a lot—even for seasoned sailors.

The challenge?

• Each event type had to be followed by specific events.

• Some fields shouldn’t be filled in, depending on the event type.

• If left unchecked, this could quickly turn into a free-for-all of incorrect or missing data.

To keep things structured, I made the event type list dynamic:

1. Proactive Dropdowns – The list of available event types changes based on the event type selected in the row above. No irrelevant options, just logical sequences.

2. Conditional Locking – Cells lock themselves when an event type doesn’t require them, preventing accidental (or creative) data entries.

(Illustration below  ⬇️) - wait a sec, it may be loading..

There was a particular case of data validation that needed to be done for Ice Navigation in case of At_Sea event type. 

Here’s how I structured it:

1. Locking Irrelevant Cells – If the event type is anything other than "At_Sea", the related cells stay locked. No need for unnecessary data entry.

2. Controlled Input for Ice Navigation – When "At_Sea" is selected, the "Ice Navigation" field becomes mandatory, but it only accepts "YES" or "NO"—no room for typos, extra notes, or unexpected responses.

3. Further Locking Based on Ice Navigation – If "At_Sea" is selected and "Ice Navigation" is "YES", then unrelated cells on the right are automatically locked, ensuring that only relevant data can be entered.

(Illustration below  ⬇️) - wait a sec, it may be loading..

In the Event Sheet, captains sometimes needed to enter the names of the ports they were departing from and sailing to. But there was a catch:

The ports they entered had to match the ones they had already chosen in the Voyage Sheet at the beginning. They couldn’t just type in a random port that had nothing to do with their recorded voyage.

To enforce this, I implemented an adaptive dropdown list:

1. Cross-Sheet Validation – The available ports in the Event Sheet are dynamically pulled from the Voyage Sheet, ensuring consistency.

2. Voyage-Specific Filtering – Depending on the selected voyage number, captains can only pick ports that were previously logged for that specific voyage.

3. Minimizing Errors – By restricting input to only valid options, I reduced the risk of incorrect or mismatched port entries, keeping the data clean and reliable.

With this setup, captains no longer had to second-guess which ports to enter—the dropdown list guided them toward the right selections, keeping everything in sync.

(Illustration below  ⬇️) - wait a sec, it may be loading..

4. Bunker Sheet: Voyage Numbers

In the final sheet, where bunkering details are recorded, having the correct voyage number is crucial. Any mismatch here could lead to reporting errors, confusion, or even fuel-related miscalculations—something no one wants at sea.

To keep things structured, I pulled the voyage numbers directly from the Voyage Sheet

(Illustration below  ⬇️) - wait a sec, it may be loading..

5. Final: Simpler Data Validation Techniques

Before wrapping up, I have to give a well-deserved honorable mention to the simpler data validation techniques that played just as crucial a role in keeping the spreadsheet shipshape.

One of the most effective yet often overlooked tools? Helpful messages that pop up as soon as a captain clicks on certain cells. These little prompts act as silent guides, ensuring the right data is entered without the need for second-guessing.

For example:

• If a cell requires time in 00:00 format, the message explicitly states so.

• If the input doesn’t follow the rule? A warning message appears, blocking progress until the mistake is fixed.

These small but mighty details kept data entry smooth, accurate, and frustration-free—proving that even the simplest validations can have a big impact.

(Some examples below ⬇️)

6. Conclusion

Through a combination of structured data validation techniques, dynamic dropdowns, and proactive error prevention, this project transformed a messy, time-consuming Excel process into a streamlined, error-resistant system. By ensuring accurate voyage numbers, enforcing correct port entries, guiding captains with helpful prompts, and locking irrelevant fields, we significantly reduced manual corrections and improved data integrity. In the end, what started as a frustrating challenge became a well-organized solution—proving that with the right approach, even the most complex spreadsheets can sail smoothly. After filling out this file, the only thing captain's will have to do is to upload this file on my friend's CO2 emission reporting platform and reports will be produced in seconds 📋📊

At the very end of this project, I can finally brag about my CO2-emission-reducing skills to my friends while looking at some cargo ships from the coast 🚢 ✅