Normal balances with IF in Excel

Every account has a side where its balance normally sits: debit for assets and expenses, credit for liabilities, equity and revenue. A few logical formulas let a spreadsheet work out that side from the account type, show each balance as a positive number, and flag any account that has ended up on the wrong side.

The accounting rule in one paragraph

Debits increase assets and expenses. Credits increase liabilities, equity and revenue. So in a healthy set of books, an asset account like Cash has more debits than credits, and a revenue account has more credits than debits. The side that increases the account is its normal balance. If the idea is new, normal balance of accounts explains it with journal entries. This page turns the rule into formulas.

Why bother? Because a balance on the "wrong" side, called an abnormal balance, is often a sign of an error. Cash with a credit balance means more money went out than came in, which usually points to a missing deposit or a posting error. Prepaid Insurance with a credit balance usually means an adjustment was posted twice. A worksheet that shows each balance on its normal side, and flags the exceptions, puts those accounts in front of the reviewer.

The MO-220 objective this covers

This lesson is MO-220 practice for "Prepare a trial balance", under "Organize financial data with formulas", which includes logical functions such as IF. MO-220 task instructions describe the purpose, for example "show Debit or Credit for each account based on its type", and leave the choice of function to you. The exercises here are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: which side is normal?

Northside Print Shop's worksheet lists ten accounts with their year-to-date totals: account name in column A, type in B, total debits in C and total credits in D. The task is to show Debit or Credit in column E, using one formula filled down.

Step 1: describe the test in words

Two types are debit-normal: Asset and Expense. Everything else (Liability, Equity, Revenue) is credit-normal. So the test is "is the type Asset, or is it Expense?" If yes, Debit; if no, Credit.

Step 2: turn it into a formula

IF takes a test, a result for TRUE and a result for FALSE. OR is TRUE when any of its arguments is TRUE. Put together, in E4:

=IF(OR(B4="Asset",B4="Expense"),"Debit","Credit")

Text in a formula goes in double quotes, both for the comparison ("Asset") and for the result ("Debit"). The comparison isn't case-sensitive, so "asset" would also match, but spelling must be exact. Fill the formula down to E13.

Step 3: read the results

The totals confirm the rule. For every Debit account, column C is larger than column D; for every Credit account, D is larger than C. Wages Expense, for example, has 22,747.55 of debits against 4,891.29 of credits.

Step 4: the balance on its normal side

The second exercise, for Pine Street Dental, already has Debit or Credit in column E and asks for each balance in column F. For a Debit account the balance is debits minus credits; for a Credit account it is credits minus debits. One IF chooses between the two subtractions:

=IF(E4="Debit",C4-D4,D4-C4)

Pine Street's Cash has 14,497.99 of debits and 10,812.11 of credits, so F4 is 14,497.99 − 10,812.11 = 3,685.88. Service Revenue is credit-normal, with 24,602.12 of credits and 16,422.45 of debits, so F10 is 24,602.12 − 16,422.45 = 8,179.67. All ten balances on this sheet come out positive, which is what normal balances look like.

Applied to Northside's numbers above, the same formula would give Prepaid Insurance 7,789.26 − 4,259.03 = 3,530.23 and Unearned Revenue 22,960.39 − 3,095.60 = 19,864.79. Showing every balance as a positive number on its own side is how a trial balance is laid out, with debit balances in one column and credit balances in the other. The trial balance in Excel page builds those two columns from a ledger.

Step 5: flag zero and abnormal balances

The third exercise, for Summit Bike Repair, combines the first two steps and adds a status column. Column E works out the side from the type and does the subtraction in one formula:

=IF(OR(B4="Asset",B4="Expense"),C4-D4,D4-C4)

Column F then labels each balance with one of three words: Zero, Normal or Abnormal. Three outcomes need either nested IFs or IFS, which tests conditions in order and returns the result for the first TRUE one. The solution uses IFS:

=IFS(E4=0,"Zero",E4>0,"Normal",TRUE,"Abnormal")

The last pair, TRUE,"Abnormal", is a catch-all: TRUE always passes, so anything not already caught gets that label. The nested version, =IF(E4=0,"Zero",IF(E4>0,"Normal","Abnormal")), gives the same results.

Two accounts need attention. Prepaid Insurance has 12,802.68 of debits and 21,791.14 of credits, so it sits 8,988.46 on the credit side, which an asset shouldn't. Retained Earnings has more debits than credits, a debit balance of 6,616.31 on an equity account. Rent Expense has equal debits and credits of 10,728.78, so its balance is exactly zero.

The order of the tests matters. Zero is checked first, then positive, and only what is left is Abnormal. That way 0.00 never falls into the wrong bucket.

Common mistakes

Treating only assets as debit-normal

=IF(B4="Asset","Debit","Credit") looks close, but it sends expenses to Credit. For Northside, Wages Expense and Utilities Expense would both show Credit. Expenses are debit-normal because they reduce equity, and debits reduce equity. Test for Asset or Expense.

Swapping Debit and Credit

Putting "Credit" as the TRUE result and "Debit" as the FALSE result reverses every row. All four of Northside's asset and expense accounts would say Credit and the six others Debit. If Cash comes out credit-normal, the results are the wrong way round.

Debits minus credits for every account

Using =C4-D4 on every row is correct for debit-normal accounts and wrong for the rest. For Pine Street Dental, Service Revenue would show −8,179.67 and Common Stock −8,705.17, and every liability, equity and revenue account would turn negative. The IF exists to flip the subtraction for credit-normal accounts.

On Summit's status sheet the same mistake does more damage. Healthy liabilities and revenue turn negative and get flagged Abnormal, while Retained Earnings flips to a positive 6,616.31 and reads Normal, hiding the one equity problem that is real.

Swapping the two subtractions

=IF(E4="Debit",D4-C4,C4-D4) gets the logic right but the signs wrong, so every balance is negative. Pine Street's Cash would show −3,685.88. When every number in a balance column is negative, look at the order of the subtractions first.

Labeling a zero balance Normal

=IF(E4>=0,"Normal","Abnormal") handles two outcomes but not the third. Summit's Rent Expense, at exactly 0.00, would read Normal. Test for zero first, before the test for a positive balance.

How to check your work

Logical formulas are easy to test, because you can predict every answer before you look.

• Count the sides. Northside has four asset and expense accounts, so column E should show Debit exactly four times. =COUNTIF(E4:E13,"Debit") confirms it in one cell.
• Check one account of each side by hand. Pick one debit-normal and one credit-normal account, do the subtraction yourself, and compare. If both match, the IF is choosing the right branch.
• Read the signs. On a sheet of healthy accounts, every balance should be positive. A column that is all negative points to swapped subtractions; a mix where only liabilities, equity and revenue are negative points to a missing IF.
• Test the edges. Change one account's credits to equal its debits and make sure the status changes to Zero. Then undo the change.

Each of these takes a moment and catches the mistakes listed above before anyone else sees them.

Related topics

The account type in column B often comes from a chart of accounts; look up account names in Excel shows how to pull it in by account number. For the bookkeeping side of the rule, see normal balance of accounts.