Calculating Travel Expenses using Google Spreadsheet

There are lot of free spreadsheet templates available for Calculating Travel Expenses. Here we are going to see the steps required for writing a simple Travel Expenses Tracker in Google Spreadsheet.

The Travel Expenses Tracker Spreadsheet has two blocks

  • First block includes the names of each person, total money spent by them and balance to be paid.
  • Second block has the expenses incurred during the trip.

The following Google Spreadsheet function and features are used in this Spreadsheet.

  • DataValidation
  • IF
  • COUNT
  • ROUND
  • SUM
  • SUMIF

Data Validation

A drop down option is available in the second block to pick the name of the person from the first block using Data Validation.

Under Cell range, specify the column for Data Validation and values for drop down is picked from the range which contains the Person name.


Total Expenses

The Total Expenses is calculated by adding the amount specified in the H column SUM(H4:H21) i.e SUM(Amount)

Share/Person

Share for each person(H26) is calculated using the formula =ROUND(H24/COUNT(A4:A21)) i.e ROUND(Total Expenses/COUNT(No.of.Person)). If you are going to add extra person then make sure to add the entry in No column. ROUND function has been used to avoid decimal numbers.

Money Spent

Money spent by each person has been calculated using the formula IF(B4=””,””,SUMIF(F$4:F$21,B4,H$4:H$21)). This uses SUMIF function for adding the total amount spent by each person by comparing the name specified under column B. The IF function is used for checking empty values.

Balance

The Balance amount that needs to be paid by each person is calculated using the Formula =IF(B4 = “”,””,H$26C4) i.e Share/Person minus Money Spent by Individual person. Negative amount indicates the person needs to receive money.

You can check the this version of Expenses Tracker Spreadsheet here.

Please feel free to share suggestion and comments.

In Category: Technical

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 0 Comments
No comments yet. Be the first.

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.