How to Do Double-Entry Bookkeeping in Excel

What is Double-Entry Bookkeeping and Why Should You Use It?

Double-Entry Bookkeeping is the system used in accounting to ensure that every transaction tells an accurate story about where the money came from and where it went.?It is a bit similar to Newton’s Third Law.?This is the physics concept of every action having an equal and opposite reaction.?The accounting principle behind the double-entry system states that for every debit to one account there is an equal credit from another account.?The money had to have come from somewhere and gone into somewhere.?In basic accounting practices, where the money came from is considered a credit and where the money goes is considered a debit.

In college, I learned about these concepts and immediately wanted to apply them to my own personal finances.?But all I had was Microsoft Excel.?Turns out, Microsoft Excel is all you need.?In this article, I will detail how to create double-entry bookkeeping system in Excel that will help you keep your money sorted just like a good accountant.

Step One:?Choose Your Accounts

Accounting is primarily focused on maintaining accounts as separate boxes for money to either be debited into or credited out of.?Before you can start sorting your money into these boxes, you need to label ALL of them starting with “Income” or “Revenue”.?

Every small or large business or small or large individual who has money to manage, has a means of income or revenue.?There has to be a way for money to come into your system whether it be from paychecks, Ma & Pa, or stolen lunch money.?In my personal system, I keep separate labels for “Gifts” and “Paychecks.”?“Paychecks” is my term for the money that I earn through the sweat of my brow (including tax returns) and “Gifts” is my term for money that I did not earn but was given to me (or violently extorted as is the case with stolen lunch money).

Next, you need your bank accounts and credit cards.?I would suggest names such as “Wells Fargo Checking” or “BOA Savings” or “Fidelity Investments” or “BB&T Credit Card.”?These names list both the name of the banking institution and what type of account it is.?It would be best to list every type of bank account and credit card, but it is understandable if you need to list “Other Credit Cards” or “Other Bank Accounts”.?Just be sure to keep these two types of accounts separate.?A bank account has money in it already that can be spent.?Savings and investment accounts also fall into this category.?A credit card, however, is an account that is spent first and then paid off.

Finally, you need your expense accounts.?If income is money entering the system, expenses are the various exit doors.?It is best to find a good middle ground for labeling your various expenses.?For example, you can label “Gas,” “Car Insurance,” and “Maintenance” as three separate accounts or you can use the label “Automobile” to describe all three or you can use the term “Transportation” to describe all automobile expenses plus the money you spent on mountains of food for your 2-ton ancient psychic tandem war elephant.

The more generalized a label is, the less insightful it will be for knowing what the money actually went toward.?For example, I split my “Food” category into “Eat-Out” and “Groceries” because spending money on sushi is a bit costlier than cooking tuna and rice at home.?However, the more specific a label is, the more labels you will need to use.?Although using more labels will give you more insights into your spending, I would suggest starting fairly generalized at first and adjusting as needed.?This way you don’t bite off more than you can chew from the get-go.

Also, everyone needs an “Other” category.??Everyone needs one even if you use it for nothing else but to escape your own scrutiny of your personal financial management system and sweep a few splurges under the rug.?However, if you find that the “Other” category becomes too significant of an expense, you need to break it down into trackable categories.

Step Two:?Row 1 on Your Excel Document

Starting in the upper left in cell A1, write “Notes”, then “Date” in B1, “Account” in C1, “Debit” in D1, and “Credit” in E1.?Like so:

Next, I would like to suggest the optional formula:?=SUBTOTAL(9,D:D)-SUBTOTAL(9,E:E) in cell F1.?This will come in handy for instantly ensuring that all of your accounts are balanced.

Finally, we will fill out the columns in row 1 starting in column G with the list of our account labels. Order does not matter.?For this example, I will list “Income,” “Bank-Name Checking,” “Bank-Name Credit Card,” “Bank-Name Savings,” “Automobile,” “Food,” “House,” and “Other” as the account labels in columns G-F, respectively.

Step Three:?Formatting

At this point go ahead and take a moment to get your formatting set-up.?Columns A and C will contain text, column B will contain dates, and columns from D onwards will contain monetary values.?Be sure to format the whole column by right-clicking the column and selecting “Format Cells”.

Also:

• If you would like to, you can freeze row 1 so that it is still visible as you scroll down through your list of transactions.
• You can set your format for the monetary values to either “Accounting” or “Currency” depending on what suits you best.
• If you shade every other row on your spreadsheet, you will have an easier time reading it.
• Color-coding is always a great option.?Go crazy.

Step Four:?If-then Formulas (Columns G onward)

In G2, type this formula:?=IF($C2=G$1,$D2-$E2,0)

Then, copy and paste that formula horizontally across all of your columns in row 2 by clicking and dragging the lower right-hand corner of cell G2 (your cursor should change to a black plus sign when you are ready to drag it across).

Now comes the fun part.?With the portion of row 2 where you have pasted the above formula still selected drag downward to copy and paste that formula for at least a few thousand rows.

The end result should look like this (except that yours should extend for thousands of rows so that you can store a month’s or a year’s worth of transactions on this excel worksheet):

With formulas visible, it should look like this:

Verify, at random, a few cells in the matrix of formulas that you copied downward.?You can do this by clicking “Trace Precedents” in the “Formulas” tab of Excel.?If the precedent arrows connect to the tested cell in one horizontal and one vertical line, then you are all set.

What’s with all of these formulas?

Well, I designed these formulas to test if the account label in column C matches the account label in row 1 and, if so, to take column D minus column E and, if not, to list “0”.?The result is a matrix of formulas that will check every transaction entry and list debits as positive figures and credits as negative figures with a column specific to each account.

Step Five:?Record Your Opening Balances

With starting this new accounting method, you will want to start with day one.?If you track your expenses by years, you may want to start at January 1st.?If you are a current college or high school student, you may prefer to start your fiscal year at the start of August so that your expenses track better with the school year.?For this example, I will use January 1st, 2018 as the start date.

Now, looking at your bank statements, how much money did you have in each account on January 1st, 2018??List them like so:

Notice that the account names listed in the “Account” column match the account names in H1, I1, and J1 precisely.?Excel will not be able to match them otherwise.?You can use the “Notes” column to add additional information relevant to the transaction that will help you keep track of what each transaction was.?This way it won’t compromise the “Account” column with extraneous descriptions.

Secondly, notice that there is no account label for “Opening Balance” as you will only use this label once.

Thirdly, notice that each transaction has one date but two entries.?This is where double-entry bookkeeping gets its name.?The first line lists the debited account name with the amount of money debited to that account in the “Debit” column.?The second line (optionally indented for visual understanding) lists the credited account name with the amount of money credited to that account in the “Credit” column.

Fourthly, notice that the checking and savings accounts have their money listed as a debit and the credit card has its money listed as a credit.?Unless you are flat broke, there will always be money in the checking and savings accounts that you can liquidize and spend at Dunkin’ Donuts.?However, when you are flat broke, you can spend your credit card at Dunkin’ Donuts with the hope that you will soon get enough money to pay it off.?This explains why checking, savings, and debit card accounts are all positive debit accounts while credit cards are negative credit accounts.?The money that is on the credit card is money that you owe the credit card company.?If you were to continue to spend with that credit card, you would end up owing more and more money to the credit card company not less and less.?Credit cards are, by nature, a negative balance.?We can verify that Excel is recording this appropriately by seeing that column I has a negative number in row 5.

If there is a discrepancy between the credit and the debit when recording any transaction, the amount of discrepancy will be listed in cell F1 like so:

By finding and correcting the discrepancy, you will have balanced your accounts and cell F1 will list a zero.

Step Six:?Record Your Expenses

Following the template for setting up the opening balances for your bank accounts, use your bank and credit card statements to record all of your transactions up to the present-day.?Remember, debits show where the money went to.?Credits show where the money came from.?Here are a few examples:

Firstly, notice how Income is in the credits column.?Remember how credits are where the money comes from and debits are where the money goes to??Well, income comes from outside of your system (where there is a theoretical infinite amount of money you could earn if you had infinite hours in the days to work infinite jobs all day long).?Because it is extracted from that infinite sum, it is credited to the “Income” account and debited to whichever bank account it enters.?When done appropriately, it will appear as a negative total.?You may think that this is showing that you made a negative income.?Don’t worry.?The sign change can easily be switched when the time comes so that it looks a bit more intuitive.

Secondly, notice how money is moved from the checking account to the savings account.?Also notice how the credit card is paid off by moving money from the checking account to the credit card account.?This use of credits and debits shows each transaction in two halves so that the money is safely tracked from one account to another.

Step Seven:?Using Your Data

With your ledger set-up for you to enter new transactions on a regular basis, you can now analyze your data.?

The quickest method is to click on the “C” at the top of column C and click “Sort & Filter” in the “Home” tab.?This will allow you to filter for specific accounts so that you can see all of the transactions coming into or out of that account.

Another easy method would be to right-click row 2 and insert a row.?You can then write a SUM formula under each of the account names in column G-N.?The SUM formulas will calculate the total of each accounts column.?Like so:

You can change the SUM formula in your column G, “Income” column, to read “=-SUM(G3:G3000)”.?The negative sign in front of the word “SUM” will switch the total to display as a positive figure.

The more extensive method is to create a balance sheet that grabs the sums for each account and displays them all in one summary table.?This table can be set-up to display averages across months or running totals or sums of multiple related accounts.

Here is the table that displays the information from the above example:

This uses the table feature (by far my favorite Excel feature). If you are unfamiliar with Excel tables and subtotal functions, here are the two articles I recommend:

Excel tables: https://support.microsoft.com/en-us/office/create-a-table-in-excel-bf0ce08b-d012-42ec-8ecf-a2259c9faf3f

Using the Total Row: https://support.microsoft.com/en-us/office/add-a-total-row-to-a-table-9885a56c-51b5-487a-a168-054afd034631

The functions in this table use the SUM function to pull from the General Ledger page and add up the correct column for the number of rows where a specific month is found.

I know, it's a little wonky. Leave your questions in the comments and I'll try to help any way I can.

Here's a more thorough explanation:

Across the top row of the Table of Categories, you have the different accounts such as “Bank Name Checking.”

So, if the cell H1 on the General Ledger is “Bank Name Checking,” then your Tables of Categories should add up all of column H to see how much is in the “Bank Name Checking” category. That’s why I use the SUM function. A simple way to do this is to type “=SUM(“ and then click on the General Ledger sheet and click on the H at the top of that sheet to select the whole column.

Then, because I want to separate spending by months in the Table of Categories, in each row I modify this equation to match the rows where those month’s expenses are found. So if July expenses are found in rows 34 through row 78, I’ll make the equation on the Table of Categories to include H34:H78.

From there, you can take the data displayed in this table and create new charts and graphs and tables that will display your financial data the way you want to.

Disclaimer: Created for Personal Use by a Non-Professional

With a little set-up, you can use Microsoft Excel to create a neat and tidy double-entry bookkeeping system.?For me, personally, I can enter a month’s worth of expenses in a half-hour or less.?Plus, with the way I have full control over my data, I can customize my own tables, pie charts, and line graphs to display insights into my spending habits hand-tailored to my changing needs.?You may end up with your own customizations and optimizations.

You also may find that my method was convoluted and not helpful for you. I do apologize. Excel is my hobby and personal finance tool. I don't use it professional and I am not a certified Excel teacher. This article is just me passing along information that I couldn't find on the web when I was trying to make this work for me.

Good luck and feel free to share this article with your friends if you found it useful!