Build Expense tracking software in Excel

I have been budgeting and tracking my expenses since last 8 years.

There has hardly been any expense that has went unaccounted in these years.

I love to keep my accounts absolutely updated.

The best way to do it by budgeting and tracking all expenses.

In last few years I have used many expense tracking software.

I have used quicken, Mint and host of other free software’s.

But nothing seemed to work well for me.

I needed more flexibility and ease of operation and reporting.

These software’s were not as per my requirements.

Ultimately I settled down with Microsoft’s Excel.

Yes you heard me right, I am talking about the spread sheet that we all use day in and day out.

There is a functionality in Microsoft’s Excel called ‘SUMIF‘.

By using this function, we can build amazing expense tracking software in Excel.

My priority requirement was ‘operating ease’ and ‘reporting’.

SUMIF provided me the liberty I wanted to prepare one such expense tracking software for myself in excel.

Here we will see how we can use the SUMIF functionality.

Using it, we can prepare a simple budgeting and expense tracking software in excel.

In order to prepare this software we have to build 2 sheets in excel.

1. Name the first sheet as ‘Budget’ and
2. The second as ‘Expense’.

Budgeting sheet will be our main reporting sheet.

# Step1 – Categorise All Expense

Before we use this software we must first finalise a very important thing.

Finalise ‘Expense Category’ in first place.

Combining similar type of expense into one category will make the report understandable and more useful.

A typical expense categories are as listed below:

EXPENSE CATEGORY

• Utility Bills
• Food Bills
• Household Expense
• Entertainment Expense
• Clothing Expense
• Education Expense
• Investment
• Loan EMI
• Vacation
• Transportation Expense
• Medical Expense
• Retirement Savings

#Step2 – Establish a Monthly Budget (limit)

Budgeting is a very important that must be established with lot of care.

Too lenient budgeting will not help the cause.

Too strict budgeting cannot be followed for a long time.

Hence a balanced approach is advisable specially when one is going budgeting for the first time.

Budgeting is like putting a limit to all expense categories.

For example, when we budget our entertainment expense to say $50/month, it means we will not spend more than $50 for entertainment.

When we budget our transportation expense to $100/month, it means we are committing not to spend more than $100.

Though this is like an informal commitment to self, but being realistic here is essential.

Looking back in history and trying to figure out how you usually spend is a good idea.

Sit with your family. Discuss with them about your idea.

Let them jointly agree on the limiting values for each category. This will make it an inclusive budget.

A typical monthly budget looks like below:

#Step3 – Prepare a Expense Recording Sheet

After the budget is ready, one can start recording expense.

The recording of expense can be done in excel as and when they are incurred.

The expense tracking software in excel allow the user to enter the expense with utmost ease.

Every expense recorded will be automatically reflected in the ‘budget sheet’ shown above.

This automatic updating of the budget sheet is made possible with the use of SUMIF functionality of Excel.

Expense recording in excel can be done in a very simple format as shown below.

All expense that are incurred are noted down in the tabulated format.

The data entry is simple except for one column (Expense Category).

The data in this column is not entered manually but is selected from a drop-down menu.

To activate drop-down menu in expense recording sheet do the following:

1. Select the full expense category column
2. Data > Data Validation.
3. A new window will pop-up.
4. In the Allow field select ‘List’.
5. To select the source, go to the budget sheet as select all ‘Expense Category’ list and
6. Click OK.

#Step4 – Activate SUMIF Functionality

After the expenses are date-wise recorded in expense recording sheet, the budget sheet is automatically updated.

The SUMIF functionality helps in simultaneous updating of the budget sheet.

You will note that only one column is common between the ‘Budget Sheet’ and ‘Expense Recording Sheet’. What is it?

The ‘Expense Category’.

This common column between the two sheets helps if summarising various expense transaction.

This summarised transaction is then reported in the Budget Sheet.

The budget sheet provides a comparison between Budget and Actual Expense.

Activate SUMIF in budget sheet.

Final Words…

This budgeting software makes life very easy for the user.

Entering all expense expense day-after day is not so easy.

People do end up missing a lot of them.

This is why it is absolutely important for software’s to make data entry as easy as possible.

I feel that the data entry in Microsoft Excel is something which we have all become very accustomed.

On top of this, the functionality of drop-down menu makes the data entry more accurate and exciting.

To top it all, the SUMIF makes the data collection and reporting in Budgeting sheet even more endearing.

Perfect Comparison between what was budgeted and what is actually spent is what makes this sheet helpful.

Moreover, the date wise entry of all expense keeps building-up the historical data for the user.

At the end of the year, one can glance back at the cumulative data and evaluate ones spending habits.

I will suggest to use new excel sheet every year.