12 Excel Formulas for Office Work: Examples and Practice Tips

Every formula begins with an equals sign. Cell references such as A2 point to values in the worksheet. When you copy a formula down, relative references change automatically. A dollar sign creates an absolute reference, such as $F$1, which stays fixed.

Build a small practice sheet with columns for employee, department, date, quantity, rate and status. Using one realistic data set makes it easier to see how the formulas work together.

1. SUM: add values

Use SUM for expenses, sales, hours or quantities:

=SUM(D2:D20)

This adds every numeric value from D2 to D20. It is safer and easier to audit than typing a long expression such as =D2+D3+D4.

2. AVERAGE: calculate the mean

Use AVERAGE to find the typical score, response time or monthly value:

=AVERAGE(E2:E20)

Blank cells are ignored, but zero values are included. Decide whether a blank and a zero mean different things in your data before interpreting the result.

3. COUNT and COUNTA: count records

COUNT counts cells containing numbers. COUNTA counts non-empty cells, including text:

=COUNT(D2:D100)
=COUNTA(A2:A100)

If column A contains employee names, COUNTA is usually the better way to count submitted records. Keep the header outside the range or subtract one if it is included.

4. IF: return a result based on a condition

IF is useful for status labels and simple decisions:

=IF(E2>=60,"Complete","Pending")

The formula checks whether E2 is at least 60. If true, it returns “Complete”; otherwise, it returns “Pending”. Keep nested IF formulas short. When many rules are involved, a lookup table is often easier to maintain.

5. SUMIF and SUMIFS: add values that meet criteria

SUMIF handles one condition:

=SUMIF(B2:B100,"Sales",D2:D100)

This adds values in column D only when column B contains “Sales”. Use SUMIFS for multiple conditions:

=SUMIFS(D2:D100,B2:B100,"Sales",C2:C100,"North")

Place the sum range first in SUMIFS, followed by each criteria range and criterion.

6. COUNTIF and COUNTIFS: count matching records

To count how many tasks are pending:

=COUNTIF(F2:F100,"Pending")

To count pending tasks in a specific department:

=COUNTIFS(B2:B100,"Operations",F2:F100,"Pending")

These functions are useful for attendance, lead status, stock alerts and quality checks.

7. XLOOKUP: retrieve matching information

In modern Excel, XLOOKUP can find an employee ID and return the employee name:

=XLOOKUP(A2,Employees!A:A,Employees!B:B,"Not found")

The formula searches for A2 in column A of the Employees sheet and returns the corresponding value from column B. The final argument displays a clear message if no match exists.

Older Excel versions may require VLOOKUP:

=VLOOKUP(A2,Employees!A:B,2,FALSE)

With VLOOKUP, use FALSE when you need an exact match.

8. IFERROR: replace formula errors with a useful message

Wrap a lookup or division formula with IFERROR:

=IFERROR(XLOOKUP(A2,Employees!A:A,Employees!B:B),"Check ID")

This improves readability, but do not use it to hide every problem. Investigate why an error occurs before deciding what the user should see.

9. ROUND: control decimal precision

Use ROUND for calculated rates, averages and amounts:

=ROUND(D2*E2,2)

The result is rounded to two decimal places. Formatting a cell to show two decimals changes its appearance, while ROUND changes the value used by later calculations. That difference can matter in financial totals.

10. LEFT, RIGHT and MID: extract part of a code

Text functions help separate structured IDs. If A2 contains DDN-2026-148:

=LEFT(A2,3) returns the first three characters.
=RIGHT(A2,3) returns the last three characters.
=MID(A2,5,4) returns four characters beginning at position five.

Use these functions only when the code structure is consistent. For irregular data, split tools or Power Query may be more dependable.

11. TRIM: remove unwanted spaces

Extra spaces can cause duplicate-looking names and failed lookups. Clean imported text with:

=TRIM(A2)

TRIM removes leading and trailing spaces and reduces repeated internal spaces to one. Some copied web data can contain special non-breaking spaces that require additional cleaning.

12. TODAY and NETWORKDAYS: work with dates

TODAY returns the current date:

=TODAY()

To calculate working days between a start date in B2 and an end date in C2:

=NETWORKDAYS(B2,C2)

You can add a holiday range as a third argument. Check whether your organisation uses a different working week; in that case, NETWORKDAYS.INTL may be more appropriate.

Keep raw data separate from reports

Store original records in a clean table and build summaries elsewhere. Do not insert decorative blank rows inside the data. Give every column one clear purpose and use consistent formats.

Test with a result you can calculate manually

Before copying a formula through hundreds of rows, test it on a few records. Manually check the expected result. This catches incorrect ranges, missing dollar signs and mismatched criteria early.

Audit instead of hiding errors

Check whether numbers are stored as text, lookup values contain extra spaces or dates are genuine Excel dates. Use IFERROR only after the underlying formula is correct.

1. Day 1: Create a clean data table and practise SUM, AVERAGE, COUNT and COUNTA.
2. Day 2: Add status labels with IF.
3. Day 3: Summarise departments with SUMIF and COUNTIF.
4. Day 4: Add a second criterion with SUMIFS and COUNTIFS.
5. Day 5: Build a small employee lookup with XLOOKUP or VLOOKUP.
6. Day 6: Clean names and codes with TRIM and text functions.
7. Day 7: Create a one-page report and verify every result manually.

If you need a broader starting point, read the guide to Excel skills for office jobs. For structured practice with formulas, pivot tables, dashboards and data analysis, see the Advanced Excel Course at ACME IT Centre.

Which Excel formulas should a beginner learn first?

Begin with SUM, AVERAGE, COUNT, IF and COUNTIF. They cover many everyday tasks and teach the core ideas of ranges, criteria and logical results.

Should I learn XLOOKUP or VLOOKUP?

Learn XLOOKUP if your Excel version supports it because it is flexible and easier to read. Understand VLOOKUP as well if you work with older files or older versions of Excel.

Is memorising formulas enough?

No. The valuable skill is recognising the business question, selecting the right function, testing the result and explaining the logic. Use a small reference sheet until the syntax becomes familiar.

These Excel formulas for office work can handle totals, conditions, lookups, text cleaning and date calculations without complex automation. Practise them in one realistic workbook, verify the output and gradually combine them. Reliable spreadsheet habits matter more than using the longest formula.