OmniDocEssential Excel Formulas Every User Should Know
Learn the core Excel formulas that general users need, with simple examples, a practice method in UniCell, and checks for common errors.
People searching for an excel formulas list usually want a short set they can actually use, not a catalog of hundreds of functions. A common situation goes like this: a column of numbers needs a total, one list has to be checked against another, and a cell shows an error nobody can explain. Knowing a handful of formulas covers many of these tasks.
This guide picks the formulas a general user needs often, explains how each one works with a small example, and shows how to test them in UniCell, the spreadsheet editor included in the OmniDoc desktop client. By the end, you will know which formulas to learn first and how to confirm that each one returns the result you expect.
Which Excel formulas should you learn first? The short answer
Start with SUM, AVERAGE, COUNTIF, IF, and VLOOKUP. Together they handle totals, typical values, conditional counts, decisions, and lookups across tables.
| Your situation | What to do | Why |
|---|---|---|
| You need the total of a column | Use SUM on that range | One formula replaces adding cells by hand |
| You need a typical value | Use AVERAGE on the numeric cells | It returns the mean of the numbers in the range |
| You count rows that meet a condition | Use COUNTIF with a test such as ">4" | It counts only the cells that match |
| You need a label or result based on a value | Use IF with a test and two outcomes | It returns one result when the test is true and another when it is false |
| You need a value from another table | Use VLOOKUP with an exact-match flag | It finds a key in one column and returns a value from another column |
How a formula works in a spreadsheet
Every formula begins with an equals sign. Typing =SUM(B2:B6) into a cell adds the values in cells B2 through B6. The result appears in the cell, while the formula stays visible in the formula bar when you select the cell.
Cell references such as B2 point to the value in that cell. A range such as B2:B6 points to a block of cells. When you copy a formula to another row, references shift with it. To keep a reference fixed, add dollar signs in front of the column letter and the row number, which tells the spreadsheet not to change that part when the formula moves.
Common mix-ups to avoid
- Numbers stored as text. A value that looks like a number but is aligned to the left is often text. SUM and AVERAGE skip text, so the total can be lower than you expect. Check alignment and convert the cells to numbers before you calculate.
- An error does not always mean a broken formula. VLOOKUP returns #N/A when it finds no exact match. Look for extra spaces, typos, or different capitalization in the lookup value.
- Typing over a formula deletes it. If you type a number into a cell that holds a formula, the formula is gone. Use Undo to restore it, then check the cell before you save.
The core Excel formulas, with examples
The examples below use a small sales table. Column A holds item names in A2 through A6, column B holds units sold (4, 7, 2, 5, and 3), and column C holds prices (10, 12, 8, 15, and 5).
- SUM: =SUM(B2:B6) adds the units. The result is 21.
- AVERAGE: =AVERAGE(C2:C6) returns the mean price. The result is 10.
- MAX and MIN: =MAX(B2:B6) returns the highest unit count, which is 7. Use MIN to find the lowest.
- COUNTIF: =COUNTIF(B2:B6,">4") counts the rows where units are greater than 4. The result is 2, for the values 7 and 5.
- IF: =IF(B2>=5,"Restock","OK") returns "Restock" for rows with at least 5 units and "OK" for the rest.
- VLOOKUP: =VLOOKUP("Pencil",A2:C6,3,FALSE) searches column A of the range for "Pencil" and returns the value from the third column, the price. The FALSE flag requires an exact match.
- Joining text: =A2&" - "&B2 combines the item name and the unit count into one cell.
Putting it to use
Practice on sample data before you rely on a formula for real work. You can do this in UniCell, which is part of the OmniDoc desktop client. Download it at download.omnidoc.top if you do not have it yet.
- Open UniCell from the desktop client and create a new blank sheet.
- Enter the sample table: item names in column A, units in column B, and prices in column C, with five rows of data starting in row 2.
- In an empty cell below the table, type =SUM(B2:B6) and confirm the result is 21.
- Enter the other formulas from the examples above, one at a time, and compare each result with a hand calculation.
- Change one unit value and check that the SUM, AVERAGE, and COUNTIF results update as you expect.
- Copy one formula down a row and select the copied cell. Look at how the references shifted, then repeat with a reference that uses dollar signs to see the difference.
❓ FAQ
1. Do I need to memorize all of these?
No. Learn SUM and AVERAGE first, then add one formula at a time when a task calls for it. Practicing with a sample table makes the syntax stick faster than memorizing a list.
2. Is there a downloadable PDF of these formulas?
You can copy the list and examples into a document and save that document as a PDF using its print or export option. This gives you a personal reference sheet that you can update with your own examples.
3. What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches the first column of a range and returns a value from a column to its right. XLOOKUP can search in either direction, but not every spreadsheet program supports it. If you are unsure which one your program supports, test both on the sample table.
4. Why does my formula show #N/A or #VALUE!?
#N/A usually means a VLOOKUP found no exact match, so check the lookup value for typos or extra spaces. #VALUE! often means a cell holds text where the formula expects a number. Select the cells involved and check their alignment and contents.
📝 Summary
Learning the core excel formulas starts with five: SUM, AVERAGE, COUNTIF, IF, and VLOOKUP. Test each one on a small sample table in UniCell, check the result by hand, and then apply it to your own data.
