Excel – Using Formulas for Simple Double Entry Bookkeeping

Do you require people to fill out a sheet such as expenses in a simple double entry format, but find that people add the costs to all the wrong columns? There is a simple way to control this with a combination of a formula, data validation and a bit of protection.

Let’s take a simple example;

People put in expense claims for the following categories: Travel, Hotel, Food, Misc

For each expense they need to add a short description and a value. The value then needs to be added to the correct column across the page, in effect creating a double entry table.

ScreenHunter_65 Apr. 08 11.38

The problem of course, is getting people to correctly add the value to the appropriate column and to make sure that all the columns add up correctly to match the grand total in column C.

First of all, control the category column. Add a data validation list based on the column headings and limit the list to those values.

ScreenHunter_65 Apr. 08 11.40

Add some standard currency formatting to your various monetary cells.

Now add a formula to auto-populate the correct columns once they have selected a category and entered a cost.

The formula to use here would be:  =IF(EXACT($A2,D$1)=TRUE,$C2,””)

This will test to see if the category selected in column A matches the column heading in columns D to G. If they match then it displays the value in column C, otherwise leaves the cell blank.

ScreenHunter_65 Apr. 08 11.47

Note, that until a value is entered in column C it will display £0.00, which is fine as it flags an incomplete cost and should prompt the user to enter a value.

The final step is to protect and lock columns D to G to prevent users randomly entering data in these cells. Make sure the cells in columns A to C are unlocked to allow editing once protection is applied.

ScreenHunter_65 Apr. 08 11.53

Make sure that the cells in columns D to G are both locked and hidden so that users cannot see the formulas (not essential but adds to the magic of the worksheet). Add a password to prevent users making changes and make sure you protect the workbook too to prevent anyone from removing the sheet or making changes to data validation rules etc.

Now when a user adds in their expenses, the values are automatically added to the correct column and because people cannot randomly add or edit values the totals for each column will add up to the grand total of all the expenses claimed.

From here you can extend your formulas to calculate net cost and VAT where applicable, and get those to automatically calculate based on selections I’ve now added to columns D and E.

ScreenHunter_65 Apr. 08 12.16

Advertisements

Posted on April 24, 2013, in Data Validation, Functions & Formulas, General and tagged , , , . Bookmark the permalink. 1 Comment.

  1. Colin G Jarrett

    Thank you very much Excelmate for this tutorial. An elegant way to keep analysed day books in Ecel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: