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.
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.
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.
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.
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.