📊 5 Excel Skills Every Accountant Should Master in 2026: A Practical Beginner's Guide

Microsoft Excel is one of the most useful tools for accounting and finance professionals. It helps you organize transactions, calculate totals, reconcile records, prepare financial reports, and analyze business performance.

However, knowing how to enter numbers into a spreadsheet is only the beginning. Learning the right formulas and techniques can help you work more efficiently, reduce manual errors, and present information more clearly.

In this practical guide, we will explore five essential Excel skills using fictional accounting transactions denominated in Tanzanian shillings (TZS).

You don't need to be an Excel expert to follow along!

1. Use SUM and AVERAGE to Analyze Financial Data

Imagine that you are an accountant responsible for reviewing a month's transactions. Instead of adding every amount manually, you can use Excel formulas.

• The SUM function adds numbers.
• The AVERAGE function calculates their arithmetic mean.

Example Data

Enter the following sample data into Excel:

Step 1: Calculate the Total Amount

Enter this formula in cell B7:

=SUM(B2:B6)

• Result: TZS 1,260,000

Step 2: Calculate the Average Amount

Enter this formula in cell B8:

=AVERAGE(B2:B6)

• Result: TZS 252,000

Why This Matters

These functions are useful when preparing expense summaries, calculating total receipts, analyzing transaction values, and reviewing financial records.

Practice Task: Add five more transactions and update the range in your formulas to include the new rows.

2. Use IF to Classify Transactions Automatically

The IF function allows Excel to make logical decisions based on a specified condition.

For example, suppose you want to classify transactions of TZS 200,000 or more as "High" and smaller transactions as "Standard".

Assume the transaction amount is in cell F2.

Enter this formula in cell H2:

=IF(F2>=200000, "High", "Standard")

• If the amount in F2 is TZS 450,000, Excel returns High.
• If the amount in F2 is TZS 85,000, Excel returns Standard.

To apply the formula to remaining transactions:

1. Select cell H2 containing the formula.
2. Move your pointer to the small square at the bottom-right corner (the fill handle).
3. Click and drag the fill handle down the column.
4. Excel adjusts row references automatically!

Why This Matters

The IF function helps classify expenses, flag unusual transactions, identify overdue items, and apply consistent internal control rules across financial datasets.

(Note: The TZS 200,000 threshold is for practice purposes only. Always adapt criteria to your organization's financial policy).

3. Use SUMIF to Calculate Totals by Category

Suppose you have a master list containing both income and expense items. You want to calculate total income without manually filtering or selecting individual rows.

The SUMIF function adds values that meet a specific criteria.

Using the practice dataset:

• Column D: Contains Transaction Type (Income or Expense).
• Column F: Contains Amount (TZS).

Calculate Total Income:

=SUMIF(D2:D16, "Income", F2:F16)

This formula checks column D for the word "Income" and sums corresponding amounts in column F.

Calculate Total Expenses:

=SUMIF(D2:D16, "Expense", F2:F16)

Pro-Tip: Advanced Multi-Criteria Summing

If you need to apply more than one condition—such as selecting transactions that belong to the Finance Department AND are classified as Expense—use SUMIFS:

=SUMIFS(F2:F16, C2:C16, "Finance", D2:D16, "Expense")

4. Use XLOOKUP to Find Information Quickly

Accountants frequently match transactional records against master lookup tables (e.g., matching account codes or departmental budget caps).

The XLOOKUP function searches for a value and returns a corresponding result from another range.

Departmental Budget Table (Accounts Sheet):

If cell C2 on your Transactions sheet contains a department name, you can retrieve its budget with:

=XLOOKUP(C2, Accounts!A$2:A$5, Accounts!B$2:B$5, "Not found")

Compatibility Note (Older Excel Versions)

XLOOKUP is available in Microsoft 365 and Excel 2021 or newer. If you are using Excel 2019 or earlier, use VLOOKUP:

=VLOOKUP(C2, Accounts!$A$2:$B$5, 2, FALSE)

5. Create a PivotTable for Quick Financial Summaries

A PivotTable allows you to summarize, analyze, and present large financial datasets in seconds without writing manual formulas.

How to Create a PivotTable:

1. Open your Transactions sheet.
2. Click any cell inside your dataset table.
3. Select Insert → PivotTable from the top ribbon.
4. Choose your destination (New Worksheet or Existing Worksheet).
5. In the PivotTable Fields panel on the right:
   • Drag Department into Rows.
   • Drag Type into Columns.
   • Drag Amount (TZS) into Values.
6. Ensure the Values field is set to Sum of Amount (if it defaults to Count, right-click the field, choose Value Field Settings, and select Sum).

📥 Download Your Free Practice Workbook

Reading about Excel is helpful, but practicing formulas hands-on is what builds mastery!

Get your copy of the Usaili Portal Excel Mastery Practice Workbook to access:

• ✅ Fictional accounting transactions in Tanzanian shillings (TZS).
• ✅ Departmental budget lookup tables for XLOOKUP practice.
• ✅ Guided exercise prompts covering core functions.
• ✅ Complete Answer Key sheet to verify your work.

Getting Started: Open the Start Here sheet first, work through the Practice sheet, and compare your outputs against the Answer Key.

Join Usaili Portal for Daily Job Updates & Career Development

Never miss out on practical Excel tutorials, accounting career advice, or corporate job listings across East Africa!

Stay ahead of the competition by joining the official Usaili Portal WhatsApp Channel.

What You Get on Usaili Portal:

• Instant Job Alerts: Finance, Accounting, Audit, Management, and Corporate vacancies.
• Practical Career Guides: Excel tutorials, resume templates, and interview preparation.
• Scholarship Opportunities: Fully funded domestic and international scholarships.

👉 Click Here to Join Usaili Portal on WhatsApp

• Official Website: www.vedastuswatosha.sbs
• Business & Advertising Enquiries: business@vedastuswatosha.sbs (Strictly for business and advertising partnerships; do NOT send job applications here)