
In this guide
- The Basics: How Formulas Work
- Formula 1: SUM
- Formula 2: AVERAGE
- Formula 3: MIN and MAX
- Formula 4: COUNT, COUNTA and COUNTBLANK
- Formula 5: IF
- Formula 6: AND and OR
- Formula 7: SUMIF and SUMIFS
- Formula 8: COUNTIF and COUNTIFS
- Formula 9: VLOOKUP (and Why XLOOKUP Is Better)
- Formula 10: INDEX and MATCH
- Formula 11: CONCATENATE, CONCAT and TEXTJOIN
- Formula 12: LEFT, RIGHT, MID and LEN
- Formula 13: TRIM, UPPER, LOWER and PROPER
- Formula 14: TODAY, NOW and DATEDIF
- Formula 15: ROUND, ROUNDUP and ROUNDDOWN
- Formula 16: IFERROR
- Understanding Absolute and Relative References
- Time-Saving Excel Tips
- Common Excel Errors and What They Mean
- A Practice Exercise
- Where to Keep Learning
- Frequently Asked Questions
- Final Thoughts
Microsoft Excel is one of the most valuable skills you can learn. Accountants, teachers, marketers, students, shop owners and engineers all rely on it. Yet many people use Excel as a plain table and never touch its real power: formulas and functions. A few well-chosen formulas can turn hours of manual work into seconds.
This beginner’s guide teaches the most useful Excel formulas with simple examples you can copy. No experience is needed. The same formulas work in Google Sheets with only minor differences.
The Basics: How Formulas Work
- Every formula starts with an equals sign (=).
- Excel cells have addresses made of a column letter and row number, such as
A1orC5. - A range is a group of cells, written like
A1:A10, meaning cells A1 through A10. - A function is a built-in formula, such as
SUM, followed by brackets containing its inputs.
Try it: type =5+3 in any cell and press Enter. You will see 8. Now type 5 in A1 and 3 in A2, and in A3 type =A1+A2. If you change A1, the result updates automatically. That is the magic of spreadsheets.
Basic Operators
+add,-subtract,*multiply,/divide,^power- Excel follows normal maths order. Use brackets to control it, for example
=(A1+A2)*A3.
Formula 1: SUM
Adds numbers together.
=SUM(B2:B10) adds all values from B2 to B10.
Shortcut: select the cell below your numbers and press Alt + = to insert SUM automatically.
Formula 2: AVERAGE
Finds the mean.
=AVERAGE(B2:B10)
Use it for test scores, daily sales or monthly expenses.
Formula 3: MIN and MAX
=MAX(B2:B10) returns the largest value.=MIN(B2:B10) returns the smallest.
Formula 4: COUNT, COUNTA and COUNTBLANK
=COUNT(A2:A20)counts cells containing numbers.=COUNTA(A2:A20)counts cells that are not empty, including text.=COUNTBLANK(A2:A20)counts empty cells.
These are handy for checking whether your data is complete.
Formula 5: IF
The IF function lets Excel make decisions. The pattern is: =IF(test, value if true, value if false).
Example: pass or fail based on a score in B2.
=IF(B2>=50,"Pass","Fail")
You can nest IFs for more outcomes, for example grading:
=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=50,"C","F")))
Newer versions of Excel also have IFS, which makes long chains easier to read.
Formula 6: AND and OR
Use them inside IF to test more than one condition.
=IF(AND(B2>=50,C2>=50),"Pass","Fail")requires both conditions.=IF(OR(B2>=50,C2>=50),"Pass","Fail")requires at least one.
Formula 7: SUMIF and SUMIFS
Add numbers only when a condition is met.
Example: total sales for the “North” region, where regions are in column A and sales in column B.
=SUMIF(A2:A100,"North",B2:B100)
For several conditions, use SUMIFS:
=SUMIFS(B2:B100,A2:A100,"North",C2:C100,"Laptop")
Note that in SUMIFS the sum range comes first.
Formula 8: COUNTIF and COUNTIFS
Count cells that meet a condition.
=COUNTIF(A2:A100,"North") counts how many times North appears.
=COUNTIF(B2:B100,">50") counts values above 50.
Formula 9: VLOOKUP (and Why XLOOKUP Is Better)
VLOOKUP finds a value in the first column of a table and returns something from the same row.
=VLOOKUP(lookup value, table range, column number, FALSE)
Example: look up a product price. If product codes are in A2:A50 and prices in column B, and the code you want is in D2:
=VLOOKUP(D2,A2:B50,2,FALSE)
Always use FALSE at the end for an exact match. VLOOKUP can only look to the right and breaks if columns are inserted. If you have a recent Excel (Microsoft 365 or Excel 2021 or newer), use XLOOKUP instead:
=XLOOKUP(D2,A2:A50,B2:B50,"Not found")
It looks in any direction, has a built-in message for missing values, and is easier to read.
Formula 10: INDEX and MATCH
This pair is a flexible alternative for lookups, and it works in every Excel version.
=INDEX(B2:B50,MATCH(D2,A2:A50,0))
MATCH finds the position of D2 in column A, and INDEX returns the value at that position from column B.
Formula 11: CONCATENATE, CONCAT and TEXTJOIN
Combine text from different cells.
=A2&" "&B2joins first and last names with a space.=CONCAT(A2," ",B2)does the same.=TEXTJOIN(", ",TRUE,A2:A6)joins a whole range with commas and skips blanks.
Formula 12: LEFT, RIGHT, MID and LEN
Extract parts of text.
=LEFT(A2,3)returns the first three characters.=RIGHT(A2,4)returns the last four.=MID(A2,3,5)returns five characters starting from position 3.=LEN(A2)returns the number of characters.
Formula 13: TRIM, UPPER, LOWER and PROPER
Clean messy data.
=TRIM(A2)removes extra spaces.=UPPER(A2),=LOWER(A2)and=PROPER(A2)change capitalization.
Formula 14: TODAY, NOW and DATEDIF
=TODAY()shows today’s date and updates every day.=NOW()shows the date and time.=DATEDIF(A2,TODAY(),"Y")calculates complete years between a date and today, useful for ages.
Formula 15: ROUND, ROUNDUP and ROUNDDOWN
=ROUND(A2,2) rounds to two decimal places.=ROUNDUP(A2,0) rounds up to a whole number.=ROUNDDOWN(A2,0) rounds down.
Formula 16: IFERROR
Hides ugly error messages and replaces them with something clear.
=IFERROR(A2/B2,"Check data")
Without it, dividing by zero shows #DIV/0!.
Understanding Absolute and Relative References
This concept confuses many beginners, but it is simple.
- Relative reference (A1): changes when you copy the formula to another cell.
- Absolute reference ($A$1): stays fixed when copied.
- Mixed reference ($A1 or A$1): fixes only the column or only the row.
Example: to calculate 10 percent tax for many prices in column A with the tax rate stored in D1, use =A2*$D$1 and drag it down. The $ signs keep the formula pointing at D1. Press F4 while editing a reference to add the dollar signs quickly.
Time-Saving Excel Tips
- Fill handle: drag the small square at the bottom-right of a cell to copy a formula down.
- Ctrl + Z undoes, and Ctrl + Y redoes.
- Ctrl + Arrow keys jump to the end of your data.
- Ctrl + Shift + L toggles filters.
- Freeze panes (View tab) keeps headings visible while scrolling.
- Format as Table (Ctrl + T) makes data easier to sort, filter and extend.
- Flash Fill (Ctrl + E) learns a pattern from your example and fills the rest.
- PivotTables (Insert tab) summarize thousands of rows in seconds, and are worth learning next.
Common Excel Errors and What They Mean
- #DIV/0! You divided by zero or an empty cell.
- #NAME? Excel does not recognize a function name or text without quotes.
- #VALUE! The formula expects a number but got text.
- #REF! A referenced cell was deleted.
- #N/A A lookup could not find a match.
- ##### The column is too narrow to show the value, so widen it.
A Practice Exercise
Create a small sales table with columns for Product, Region, Units and Price. Then try:
- A new column Total using
=C2*D2. - The sum of all totals using
SUM. - Total sales for one region using
SUMIF. - A Status column using
IFto label totals above a number as “High.” - A lookup of a product’s price using
XLOOKUPorVLOOKUP.
Practising on your own data, such as a household budget, is the fastest way to learn.
Where to Keep Learning
- Microsoft’s own free Excel training pages
- Free video tutorials on YouTube
- Google Sheets, which is free and works much the same way
- Practice workbooks and sample datasets available online
Frequently Asked Questions
What is the most important Excel formula to learn first?
SUM, AVERAGE, IF and a lookup function (XLOOKUP or VLOOKUP) cover a large share of everyday needs.
Are Excel and Google Sheets formulas the same?
Most are identical. Some newer or specialized functions differ, so check the documentation.
How long does it take to learn Excel?
You can learn the basics in a weekend and become comfortable within a few weeks of regular practice.
Is Excel a useful skill for getting a job?
Yes. It is among the most requested workplace skills across many industries.
Final Thoughts
You do not need to memorize hundreds of functions. Master the 16 in this guide, understand cell references, and practise on real data. Within a short time, you will do tasks in minutes that used to take hours, and you will have a skill that employers value everywhere.




















































