Plenty of paid apps can help you track your travel budget and do a great job. But, if you’re tracking your budget, you probably need to save as much money as possible. So we’re going to help you build your own custom travel tracker in Google Sheets. Read on to learn how.
Why Should You Track Travel Expenses on Google Sheets
Creating a travel budget helps you stay on track financially when out on a trip. It enables you to remain realistic and not go overboard with impulse purchases. It also allows you to make the best choices when spending money on the go. You’ll need this. Especially on a longer trip, you don’t want to run out of your savings.
If you find out that you spent more than your budget allows on a day, you will be more careful the next day to be on track rather than overspend again. A mobile application or Google Sheets is a great way to do this. Although apps are great for this, they don’t allow you to customize to the extent that making a custom spreadsheet does.
You may want to track a specific category or add any other notes and figures to your tracker. This type of freedom will only be allowed by spreadsheet applications like Microsoft Excel and Google Sheets. Google Sheets is much better for this as it’s free to use and is available on mobile and the web. You can also share your spreadsheet with others on Google Sheets such as your family members, so they can keep track of their spending habits on holiday too.
How to Create a Travel Tracker Spreadsheet
To create the travel tracker spreadsheet, we must make the rows and columns containing our data. For better visual presentation, we can combine some of the cells. This will become clearer when you look at the screenshots and guide below.
In the first row, we will add the dates and the days corresponding to the dates. To do this
- Start by adding the starting date to the first cell.
- Click and drag the fill handle downwards, and this will autofill the dates in the selected columns, saving you from the hassle of manually adding the dates.
- Copy and paste the date into the column to the right.
- Select the cells to convert the dates to day names (column B) and click on Format in the main top bar. There, click on Number and then on Custom date and time.
- Change the format to Day. The dates will now show up as days.
We also changed the fill colors of the cells to make them a bit easier to read. You can use different colors for each category or just leave it all white. It’s up to you.
The next part is writing the travel expenses into the spreadsheet. We added various expenses such as food, transport, accommodation, and activities into the spreadsheet. Doing this is simple. All you have to do is click on the cell and type in the label for that column. Here is where you can add any extra expenses or notes you’d like to make too.
You can add visual elements like colors, borders, and merging cells. However, do note that this is optional and only serves to make your spreadsheet look nicer.
Now we will add a total spending column and a budgeting section that will let you know whether you have gone over the budget or not. These will require you to use a formula, so let’s look at how you can do that.
- Add a section containing the total money spent that day. These are the cells marked as “1” in the picture above.
- In our example, you can use the formula =SUM(C3:H3) in cell I3
- Click I3, then drag the small blue square down to cover the rest of the applicable cells in the I column to copy the formula over the rest of the cells.
- In the end, at the totals, use the SUM formula again, but add the cell range of the Total Spent section.
You can also add a Difference section that subtracts the Total Spent from the daily budget specified. With a formula similar to:
=daily budget-total spent(cell reference)
Let’s say we set ourselves a daily budget of $240. In our above example, in cell J3, we could put:
Then click and drag the formula over the rest of the cells.
Depending on your needs, feel free to add or remove your own sections. For example, if you wanted to keep a total of all the days you went over budget, but ignore dates with blowouts of $400 or more on transport or accommodation, you could use a SUMIFS function to track them. If we were going to do this with our example tracker, here’s the formula we’d use.
=SUMIFS(J3:J33,F3:F33, “<400”,G3:G33, “<400”)
This will only sum the Difference column totals if the accommodation and transport columns figures are less than $400.
Save Your Pennies With Google Sheets
Now that you know how simple it is to build a travel budget planner in Google Sheets, you can get cracking immediately. Have fun on your trip, and remember to update the sheet daily for the best tracking possible.