Excel Formulas Every Office Job Needs (With Worked Examples)

Most office jobs that ask for "computer knowledge" really mean one thing: can you use Excel to add up, look up, count and clean data without help? You do not need hundreds of functions. About fifteen formulas cover nearly everything an office assistant, data entry operator or accounts assistant does in a normal week.

This guide uses one small practice table throughout. Type it into a blank sheet first. Every example below refers to it, so you can check each answer yourself instead of just reading.

Set up the practice sheet

Open a new workbook and enter this data starting in cell A1. Row 1 is the header row.

Column G is empty on purpose. Filling it is your first formula.

Arithmetic and the order of operations

In G2 type =E2*F2 and press Enter. You should get 1400. Drag it down to G7. Excel automatically changes the row number in each copy: G3 becomes =E3*F3, and so on. This automatic adjustment is called a relative reference.

Excel follows normal maths order: brackets first, then multiplication and division, then addition and subtraction. So =E2*F2+50 adds 50 after multiplying, while =E2*(F2+50) adds 50 to the rate first. When a result looks wrong, missing brackets are the most common cause.

Absolute references with the $ sign

Suppose GST at 18% is typed once in cell J1. In H2 you write =G2*J1. When you drag it down, H3 becomes =G3*J2 — but J2 is empty, so the answer is zero. Fix it by locking the cell: =G2*$J$1. The dollar signs tell Excel "never move this reference". Press F4 while the cursor is on a reference to add the dollar signs quickly.

SUM, AVERAGE, MIN, MAX and COUNT

These five functions summarise a column. Try each one in an empty cell below your table:

If your SUM shows a smaller number than expected, check whether some amounts are stored as text. Text numbers are usually left-aligned and show a small green triangle. Select them, click the warning icon and choose Convert to Number.

IF: making decisions in a cell

IF checks a condition and returns one value when it is true and another when it is false. The pattern is =IF(condition, value_if_true, value_if_false).

Example: mark invoices above ₹2,000 as "Large". In I2:

=IF(G2>2000,"Large","Small")

Text inside a formula must be in double quotes. Numbers must not be.

Nested IF and IFS for more than two results

For three bands — below ₹1,000, ₹1,000 to ₹3,000, above ₹3,000 — you can put one IF inside another:

=IF(G2<1000,"Low",IF(G2<=3000,"Medium","High"))

Newer versions of Excel (Microsoft 365 and Excel 2019 onwards) have IFS, which is easier to read:

=IFS(G2<1000,"Low",G2<=3000,"Medium",TRUE,"High")

The final TRUE acts as "everything else". Older versions such as Excel 2010 do not have IFS, so learn the nested IF too — many offices still run older copies.

AND and OR inside IF

To flag Ravi Traders' invoices above ₹1,000: =IF(AND(C2="Ravi Traders",G2>1000),"Check",""). Use OR when any one condition is enough.

COUNTIF and SUMIF: totals by category

These two answer the questions managers ask most often: "How many?" and "How much?" for one customer, item or month.

• =COUNTIF(C2:C7,"Ravi Traders") — number of invoices for Ravi Traders. Answer: 2.
• =SUMIF(C2:C7,"Ravi Traders",G2:G7) — total billed to Ravi Traders. Answer: 3,540.
• =COUNTIF(G2:G7,">2000") — invoices above ₹2,000. Answer: 2.

In SUMIF the first range is where Excel looks for the condition, and the last range is what it adds. Mixing up the order is the most common error.

COUNTIFS and SUMIFS for two or more conditions

The "S" versions take several conditions. Note that SUMIFS puts the sum range first:

=SUMIFS(G2:G7,C2:C7,"Sri Lakshmi Stores",D2:D7,"Notebook")

This returns 1,400 — Sri Lakshmi Stores' notebook sales only.

VLOOKUP and XLOOKUP: finding data in another table

Lookup formulas fetch a value from a list — for example, a customer's phone number or an item's HSN code — so you never retype it.

Create a small customer list in M1:N4:

VLOOKUP

=VLOOKUP(C2,$M$2:$N$4,2,FALSE)

Read it as: "Find the value of C2 in the first column of M2:N4, and return what is in column 2 of that range. FALSE means exact match only." Always use FALSE for names, codes and invoice numbers. Leaving it out tells Excel to find an approximate match, which silently returns wrong rows.

VLOOKUP has two limits: it only looks to the right of the search column, and inserting a new column inside the range breaks the column number.

XLOOKUP

If you have Microsoft 365 or Excel 2021, XLOOKUP is simpler and fixes both problems:

=XLOOKUP(C2,$M$2:$M$4,$N$2:$N$4,"Not found")

You give it the column to search, the column to return, and a message for when nothing matches. Learn VLOOKUP anyway — it appears in most interview tests because it works in every version.

IFERROR to hide error messages

When a lookup fails, Excel shows #N/A. Wrap the formula to show something friendlier: =IFERROR(VLOOKUP(C2,$M$2:$N$4,2,FALSE),"Check name"). Use this carefully: it hides every error, including genuine mistakes in your formula.

Text formulas for cleaning data

Data copied from software, websites or forms is often messy — extra spaces, mixed capitals, joined fields. These functions clean it.

LEN is useful for checking mobile numbers: =IF(LEN(TRIM(P2))=10,"OK","Check") quickly flags numbers that are too short or too long.

Date formulas

• =TODAY() — today's date, updated every time the file opens.
• =TODAY()-B2 — days since the invoice date. Format the cell as Number if you see a date instead.
• =EDATE(B2,1) — the same date next month, useful for due dates and renewals.
• =DATEDIF(B2,TODAY(),"m") — complete months between two dates. It is not shown in Excel's function list, but it works.
• =TEXT(B2,"mmmm") — month name, handy for monthly summaries.

Rounding money correctly

Formatting a cell to show two decimals does not change the stored value — totals can still be off by a paisa. Use =ROUND(G2*18%,2) to round tax to two decimal places, and =ROUND(value,0) for whole rupees. ROUNDUP and ROUNDDOWN always round in one direction.

Reading Excel error messages

A 20-minute practice test

Office practical tests usually look like this. Try it without looking at the sections above, then check your answers.

1. Fill column G with Qty × Rate.
2. In column H, calculate 18% GST on each amount, rounded to two decimals, using a rate typed once in J1.
3. In column I, show "Large" for amounts above ₹2,000 and "Small" otherwise.
4. Below the table, show the total amount, the total GST and the grand total.
5. In a small summary table, show the total billed to each customer using SUMIF.
6. Add the customer's town next to each invoice with VLOOKUP.
7. Count how many notebook invoices there are.

Expected answers: total before tax 12,090; GST 2,176.20; grand total 14,266.20; Sri Lakshmi Stores 2,750; Ravi Traders 3,540; Anand Xerox 5,800; notebook invoices 2.

How Excel is tested in interviews

Interviewers for office and data entry roles usually do three things: ask you to explain a formula in words, give you a short practical task on a computer, and check your speed. Practise saying what a formula does — "SUMIF adds amounts from column G where column C matches the customer name" — because a clear explanation shows you understand it rather than memorised it.

Keep a one-page practice file with each formula from this guide and an example next to it. Revise it the night before an interview. If you are studying a DCA or PGDCA course, Excel is part of the syllabus, so ask your trainer for extra practical sheets like the one above.

Frequently asked questions