Excel Formulas and How-Tos for Accountants, with Examples

How do you calculate percentage in Excel?

The question usually arrives as “how can I calculate percentage in Excel?”, and the answer is a division. Excel has no single percentage button that does the maths for you. You write the calculation as a division, and Excel shows the result as a percentage once the cell is formatted that way. Microsoft’s rule is simple: amount divided by total equals percentage.

1. Put the part in one cell (for example, this month’s rent in B2) and the total in another (total expenses in C2).
2. In D2, type the formula below and press Enter. Excel shows a decimal such as 0.25.
3. With D2 selected, press Ctrl+Shift+% or select Home > Number > Percent Style. The cell now shows 25%.
4. Use Increase Decimal on the Home tab if you want 25.4% rather than 25%.

=B2/C2

A common slip: if a cell already holds 18 and you apply Percent Style, Excel shows 1800%, because 18 is treated as 18 whole units. Type the rate as 18% (or 0.18) in the first place, or format the empty cells as percentages before you type.

Percentage calculation in Excel: the formulas accountants use

Here is the share-of-total formula on a small month of expenses. Type the total in B7 with =SUM(B2:B6), put =B2/$B$7 in C2 and copy it down. The shares are rounded to one decimal place.

The dollar signs in $B$7 and $B$10 lock the total, so the formula still points at it when you copy it down a column. If a total can be zero, wrap the division so the sheet shows a blank instead of #DIV/0!:

=IF(C2=0,"",B2/C2)

Excel for Microsoft 365 also has a PERCENTOF function, =PERCENTOF(B2:B5,B2:B20), which sums the first range and divides it by the total of the second. The plain division works in every version, so learn that first.

How do you insert a checkbox in Excel?

In Excel for Microsoft 365, select the cells where you want checkboxes and choose Insert > Checkbox. Each cell then holds a checkbox that stores TRUE when ticked and FALSE when not, so formulas can count or total the ticked rows. In versions without that button, such as Excel 2016, 2019 and 2021, use Developer > Insert > Form Controls > Check Box instead.

Checkboxes in Microsoft 365

1. Select the range, for example E2:E50 beside a list of invoices.
2. On the Insert tab, select Checkbox.
3. Click a checkbox to tick it, or select one or more and press the Spacebar.
4. To remove them, select the cells and press Delete. Microsoft notes that ticked boxes are first unticked; press Delete again to remove them.

Checkboxes in older versions (Form Controls)

1. Show the Developer tab: File > Options > Customize Ribbon, tick Developer and select OK.
2. Select Developer > Insert and, under Form Controls, choose Check Box. Click in the cell where it should sit.
3. Right-click the checkbox, choose Format Control and, on the Control tab, enter a Cell link such as $F$2. The linked cell shows TRUE or FALSE.
4. Right-click and choose Edit Text to change or clear the label.

Form Controls checkboxes float over the sheet and each needs its own cell link, so they take longer to set up for a long list. The Microsoft 365 checkbox lives inside the cell and copies down like any value.

Using ticked boxes in accounts work

Checkboxes suit a month-end close checklist, a list of reconciled invoices or a TDS deduction tracker. Because each box is TRUE or FALSE, these formulas work on the column:

=COUNTIF(E2:E50,TRUE)
=SUMIFS(D2:D50,E2:E50,TRUE)
=IF(E2,"Reconciled","Pending")

The first counts ticked rows, the second totals the amounts in column D for ticked rows only, and the third labels each row.

How do SUMIF and SUMIFS total accounts?

SUMIFS is the formula accountants lean on most, because almost every report is a total with conditions. SUMIF takes one condition; SUMIFS takes several, and puts the range to add first, then pairs of criteria ranges and criteria.

• Sales for one customer: =SUMIF(B:B,"Sharma Traders",D:D) adds column D wherever column B shows that customer.
• Taxable value at 18%: =SUMIFS(E:E,F:F,18%), where column F holds the GST rate for each invoice.

Sales for one customer in one month needs three conditions:

=SUMIFS(D:D,B:B,"Sharma Traders",C:C,">="&DATE(2026,8,1),C:C,"<="&DATE(2026,8,31))

Use =SUBTOTAL(9,D2:D500) instead of SUM when you filter a register, so hidden rows drop out of the total. A useful habit is to check every SUMIFS report against a plain SUM of the full column. If the parts do not add up to the whole, a condition is missing a row.

How do you use VLOOKUP and XLOOKUP to reconcile records?

Reconciliation means finding what is in one list but not the other, or what differs between them. Lookup formulas do this. Reconciling is easier when you know what a correct entry looks like, so a set of practice journal entries with worked answers is a good warm-up.

VLOOKUP searches the first column of a table and returns a value from a column to the right. The final FALSE forces an exact match, which accountants almost always need:

=VLOOKUP(A2,Sheet2!A:D,4,FALSE)

XLOOKUP is the newer replacement. It can look left, has a built-in “not found” result and does not break when columns are inserted. Microsoft notes it is available in Excel 2021 and later and in Microsoft 365, but not in Excel 2016 or 2019. If your office uses an older version, INDEX with MATCH does the same job.

=XLOOKUP(A2,Sheet2!A:A,Sheet2!D:D,"Not found")

Worked example: purchase register against GSTR-2B

Say column A of your purchase register holds a key built from supplier GSTIN and invoice number, and column E holds the tax amount. Import GSTR-2B into another sheet with the same key.

1. Pull the GSTR-2B tax into column F: =XLOOKUP(A2,GSTR2B!A:A,GSTR2B!E:E,0)
2. Find the difference in column G: =E2-F2
3. Flag it: =IF(ABS(G2)>1,"Check","OK"). The ₹1 tolerance absorbs rounding.

Invoices marked “Check” are the ones to follow up with suppliers before claiming credit. Our GST return filing guide explains why this matching matters for GSTR-3B. To catch double entries, =COUNTIFS(B:B,B2,C:C,C2) counts how often the same supplier and invoice number appear together; any result above 1 needs a look.

Which formulas check and flag errors?

Logical formulas turn a sheet from a list of numbers into a set of checks. They are how you catch mistakes before your manager or auditor does.

• IF: =IF(D2>C2,"Over budget","Within budget") compares actual spend with budget.
• AND inside IF: =IF(AND(E2>0,F2=""),"Missing GSTIN","") flags taxable purchases with no supplier GSTIN recorded.
• IFERROR: =IFERROR(XLOOKUP(A2,B:B,C:C),"Not in ledger") replaces an #N/A error with a readable message.

Use IFERROR with care. Hiding every error can hide a real problem, so apply it only where you know why the error appears.

How do you calculate GST in Excel?

Multiply the taxable value by the rate and round to two decimals. ROUND matters because Excel stores more decimal places than it shows, and unrounded values create small differences between your sheet, your accounting software and the return.

The standard GST rate has been 18% since the rate changes of 22 September 2025. The merit rate is 5%, and 40% is a special rate for a small set of items. For an intra-state sale with the taxable value in B2:

Total GST:      =ROUND(B2*18%,2)
CGST and SGST:  =ROUND(B2*9%,2)   (each)
Invoice total:  =B2+C2+D2

When you only have the GST-inclusive amount, work backwards. With ₹28,320 in B2 at 18%, the first formula returns a taxable value of ₹24,000 and the second the ₹4,320 of tax:

=ROUND(B2/(1+18%),2)
=B2-ROUND(B2/(1+18%),2)

Better still, put the rate in its own column and refer to it, so one sheet handles 5%, 18% and 40% items. ROUNDUP always rounds away from zero, which is what some payroll contributions require. Our PF and ESI calculation guide shows where rounding rules apply.

How do you calculate dates, ageing and due dates in Excel?

Excel stores dates as numbers, so subtracting one date from another gives the days between them. That answers the two questions accountants ask most: how old is this invoice, and when is it due?

Debtors are customers who owe you money, and what debtors and creditors mean in the books covers the terms in full. For a debtors ageing report, put days outstanding in column D and sort each invoice into a bucket:

=IF(D2<=30,"0-30",IF(D2<=60,"31-60",IF(D2<=90,"61-90","90+")))

Combine the bucket with a Pivot Table and you have ageing by customer in a few minutes. If a result shows as a number like 46,300 instead of a date, the cell is formatted as General; set it to a date format.

How do you clean data exported from Tally or a bank?

Exports rarely arrive clean. Extra spaces, numbers stored as text and mixed date formats all break lookups. In TallyPrime, press Alt+E (Export) on a report and set the File Format to Excel (Spreadsheet), then tidy the sheet with these:

• TRIM: =TRIM(A2) removes extra spaces, so “Sharma Traders ” matches “Sharma Traders”.
• VALUE: =VALUE(B2) turns a number stored as text into a real number.
• TEXT: =TEXT(C2,"mmm-yyyy") turns a date into a month label for grouping.
• LEFT, RIGHT and MID: extract part of a code, such as a branch prefix in a voucher number.

Many accountants post in TallyPrime and analyse in Excel. If you are building both skills, the Tally course in Delhi covers the posting side.

How do you work out EMIs and depreciation in Excel?

Two financial functions cover most loan and fixed-asset work in a small accounts team. For an EMI on a ₹5,00,000 loan at 9% a year over 36 months, the first formula returns about ₹15,900 a month. For an asset bought for ₹1,20,000 with a ₹20,000 residual value and a 5-year life, the second returns ₹20,000 a year of straight-line depreciation:

=PMT(9%/12,36,-500000)
=SLN(120000,20000,5)

PMT needs the rate and the number of periods in the same unit, so divide an annual rate by 12 for monthly instalments. The loan amount is entered as a negative number to return a positive EMI. SLN gives book depreciation on the straight-line method; tax depreciation follows its own rules, so keep the two schedules separate.

Excel formulas every accountant uses: a one-page list

If you only want the list, this is it. Each formula below is explained with an example earlier on this page, so you can jump back to the section you need.

Common formula mistakes accountants make

Most spreadsheet errors in accounts come from a few habits. Watch for these:

• Missing dollar signs: use absolute references such as $H$1 for a rate cell you copy down a column.
• Approximate VLOOKUP: leaving out FALSE can return the wrong row without any error.
• Hard-coded numbers: typing 18% into 500 formulas instead of referring to one rate cell makes updates risky.
• Text that looks like numbers: totals silently skip them. Check with ISNUMBER.
• No control total: always tie a report back to the trial balance or source total.

For structured practice, IPA’s Advanced Excel course in Delhi runs for 2 months and covers lookups, Pivot Tables, Power Query, dashboards and VBA on business data, with placement assistance. Interviewers often test these formulas alongside basic accounting, so revise the golden rules of debit and credit and our accounting interview questions too.

How we teach this at IPA

Each topic in IPA’s Advanced Excel course is practised on realistic business data such as sales registers, stock lists and ledgers, and the course builds towards a dashboard project you can show an employer. The formulas and functions module comes first, followed by pivot tables, Power Query, Power Pivot and macros. If you are aiming at reporting jobs, our guide to the Excel skills MIS jobs expect shows how these formulas turn into daily and monthly reports.

AI tools inside Excel, and how they are used in accounting work, are covered in every IPA course that includes Excel. You still need the formulas on this page to check what any tool gives you.

Pivot tables have their own guide: how to make a pivot table in Excel with an accounts example.

Frequently asked questions