Whenever you go on a holiday, you fix a budget in mind before hand.

  • Are you able to keep a good track of your spends?
  • Are you able to know at any point of time, that you should let’s say spend less on shopping as your food bills are more than expected?
  • Are you able to decide immediately whether to upgrade on a hotel room (and surprise your husband/wife!), since you have spent less on internal travelling than expected?

You can track your spending with this very simple Excel sheet to make quick decisions during your trip, and save money on the go!

Below is a sample of spending on a USA trip.
It is a 22-day trip covering the East Coast and the West Coast.

You have so far covered – New York, Washington DC, Niagara Falls and Chicago in 12 days.
From Chicago, you will be going to the West Coast.

You have 10 more days to go, and you need to know what’s the current status of your Budget v/s Spending?!!

Spending data

Spending data

As you can see on the right-side of the sheet,

  • Total budget for the trip is $8000.
  • Category-wise budgets have been set
    • Food –  $1500
    • Travelling – $1000
    • Hotels – $1500
    • Shopping – $1000
    • Attractions – $2000
    • Miscellaneous – $1000

Calculations to be done

  • Total Spent = Sum of entire Amount column (column E)
    • = SUM (E:E)

      Calculate Total Spent

      Calculate Total Spent

  • Total In-Hand = Budget – Spent
    • = J4 – K4

      Calculate Total In-Hand

      Calculate Total In-Hand

  • Category-wise Spent
    • = SUMIF will be used to calculate for each category.
    • Food
      • = SUMIF (C:C, I8, E:E)
      • C:C is the entire Category column,
      • from which “Food” is picked using I8
      • & sum of Amount column, E:E is the output

        Calculate Food - Category-wise Spent

        Calculate Food – Category-wise Spent

    • For the other categories, simply copy the Food formula downwards.
      • The I8 will change to I9, I10 and so on, giving the respective Amount for each category.
      • The C:C and E:E will not change as rows are not mentioned. If rows are mentioned in your formula (like C4: C100), then you will need to use the $ sign to fix the rows (C$4:C$100).

        Auto Calculate other Categories

        Auto Calculate other Categories

  • Category-wise In-Hand
    • = Budget – Spent
    • Similar to calculating the Total In-Hand

      Calculate In-Hand Category-wise

      Calculate In-Hand Category-wise

To learn how to use =SUMIF in-depth, click here.

Thus, once you reach the West-coast and as soon as you make a new entry of spending in the sheet, the formulas give you a current scenario of Budget v/s Expenses.

Do use this on your next trip!

Keep Excel-ing!