Top 50 Excel Formulas Everyone Should Know – A Friendly Guide
When you first open Excel it may look like a simple grid. Yet as you explore it you soon discover that the real magic lies in its formulas. They turn plain data into meaningful insights. They simplify work. They help you avoid long manual calculations. And the best part is that most formulas are very easy to use once you get comfortable with them.
In this guide we will walk through the most useful Excel formulas in a fun and friendly way. We will also group them neatly so you can understand when and why to use each one. Moreover every group contains a clean table to help you quickly refer to the formula whenever you need it.
Let us begin your smooth journey into the world of Excel formulas.
1. Basic Math Formulas
These formulas are the building blocks of Excel. They help you handle everyday calculations with ease. You can use them in budgeting, marksheets, reports or any basic number-handling task.
Shortcut Basics: Getting Comfortable
Before we jump into the grouped shortcuts, let’s warm up with the ones you’ll use almost every day. These shortcuts help you control your workbook without touching the mouse.
Basic Math Formula Table
| Purpose | Formula |
|---|---|
| Addition | =A1 + B1 |
| Subtraction | =A1 - B1 |
| Multiplication | =A1 * B1 |
| Division | =A1 / B1 |
| Power | =POWER(A1, B1) |
| Square Root | =SQRT(A1) |
| Average of Cells | =AVERAGE(A1:A5) |
| Round Number | =ROUND(A1,2) |
| Round Up | =ROUNDUP(A1,0) |
| Round Down | =ROUNDDOWN(A1,0) |
These simple formulas keep your base strong and speed up your calculations from day one.
2. Essential Summary Formulas
Now let us move to formulas that help you summarize data. These are perfect for reports and dashboards where you need a quick picture of your numbers.
Summary Formula Table
| Purpose | Formula |
|---|---|
| Total | =SUM(A1:A10) |
| Largest Value | =MAX(A1:A10) |
| Smallest Value | =MIN(A1:A10) |
| Count Numbers | =COUNT(A1:A10) |
| Count All Filled Cells | =COUNTA(A1:A10) |
| Count Blank Cells | =COUNTBLANK(A1:A10) |
| Average | =AVERAGE(A1:A10) |
| Median | =MEDIAN(A1:A10) |
| Mode | =MODE(A1:A10) |
| Range | =MAX(A1:A10)-MIN(A1:A10) |
These formulas give you a fast overview of your data without doing anything manually.
3. Text Handling Formulas
Excel is not only about numbers. It also manages text beautifully. These formulas help you fix names, codes and product lists that come in all shapes and sizes.
Text Formula Table
| Purpose | Formula |
|---|---|
| Convert to Uppercase | =UPPER(A1) |
| Convert to Lowercase | =LOWER(A1) |
| Capitalize Each Word | =PROPER(A1) |
| Combine Text | =CONCAT(A1, B1) |
| New Advanced Combine | =TEXTJOIN(" ", TRUE, A1:A3) |
| Remove Spaces | =TRIM(A1) |
| Extract Left Characters | =LEFT(A1,3) |
| Extract Right Characters | =RIGHT(A1,3) |
| Extract Middle Characters | =MID(A1,2,3) |
| Find Length | =LEN(A1) |
You will love these formulas when dealing with messy text data.
4. Lookup and Reference Formulas
This is where Excel becomes a smart assistant. These formulas help you search, compare and fetch data from tables. They save hours of manual work.
Lookup Formula Table
| Purpose | Formula |
|---|---|
| Vertical Lookup | =VLOOKUP(A1, B1:E10, 2, FALSE) |
| Horizontal Lookup | =HLOOKUP(A1, B1:E5, 3, FALSE) |
| Two-Way Lookup | =INDEX(B2:E10, MATCH(A1,A2:A10,0), MATCH(B1,B1:E1,0)) |
| Exact Match Lookup | =XLOOKUP(A1, A2:A10, B2:B10) |
| Swap Rows/Columns | =TRANSPOSE(A1:E1) |
| Get Row Number | =ROW(A1) |
| Get Column Number | =COLUMN(A1) |
| Address of Cell | =ADDRESS(1,1) |
| Pick From Index | =CHOOSE(2,"Red","Blue","Green") |
| Match Position | =MATCH(A1,A2:A10,0) |
Once you learn lookup formulas your data handling becomes ten times faster.
5. Date and Time Formulas
These formulas help you calculate timelines, age, deadlines and work durations smoothly.
Date and Time Formula Table
| Purpose | Formula |
|---|---|
| Today’s Date | =TODAY() |
| Current Time | =NOW() |
| Get Year | =YEAR(A1) |
| Get Month | =MONTH(A1) |
| Get Day | =DAY(A1) |
| Add Days | =A1 + 10 |
| Difference in Days | =DAYS(A1, B1) |
| Difference in Years | =DATEDIF(A1, B1, "Y") |
| Format Date | =TEXT(A1, "DD-MMM-YYYY") |
| Weekday Name | =TEXT(A1, "DDDD") |
These formulas are extremely useful for HR teams, teachers, managers and planners.
6. Logical Formulas
Logical formulas help your sheet think and decide based on conditions. They are perfect for grading systems, discount rules and business decisions.
Logical Formula Table
| Purpose | Formula |
|---|---|
| If Condition | =IFS(A1>90,"A",A1>75,"B",A1>60,"C") |
| Multiple Conditions | Ctrl + Page Down |
| AND Logic | =AND(A1>50, B1>50) |
| OR Logic | =OR(A1>50, B1>50) |
| NOT Logic | =NOT(A1="Yes") |
| Count if Condition True | =COUNTIF(A1:A10,">50") |
| Count if Multiple Conditions | =COUNTIFS(A1:A10,">50",B1:B10,"=100") |
| Sum if Condition True | =SUMIF(A1:A10,">50",B1:B10) |
| Sum if Multiple Conditions | =SUMIFS(C1:C10,A1:A10,">50",B1:B10,"Yes") |
| IF with AND | =IF(AND(A1>50,B1>50),"OK","Check") |
Logical formulas make your Excel sheet behave like a smart rule-based system.
7. Financial Formulas
These formulas help in EMI calculations, interest analysis and financial planning. Even if you are not an accountant they still come in handy.
Financial Formula Table
| Purpose | Formula |
|---|---|
| Simple Interest | =A1A2A3 |
| Compound Interest | =A1*(1+A2)^A3 |
| Future Value | =FV(rate,nper,pmt,pv) |
| Present Value | =PV(rate,nper,pmt) |
| Monthly Payment | =PMT(rate,nper,pv) |
| Internal Rate of Return | =IRR(A1:A10) |
| Net Present Value | =NPV(rate,A1:A10) |
| Depreciation | =SLN(cost,salvage,life) |
| Growing Future Value | =FV(A1,A2,0,-A3) |
| Effective Annual Rate | =EFFECT(A1,A2) |
These formulas help businesses make smart financial decisions.
8. Advanced and Powerful Formulas
These formulas may look big but once you understand them they open the doors to advanced automation.
Advanced Formula Table
| Purpose | Formula |
|---|---|
| Remove Duplicates | =UNIQUE(A1:A20) |
| Sort Data | =SORT(A1:A20) |
| Filter Data | =FILTER(A1:B20,A1:A20>50) |
| Combine and Lookup | =XLOOKUP(A1,A2:A10,B2:B10) |
| Dynamic Arrays | =SEQUENCE(10) |
| Random Numbers | =RAND() |
| Random Whole Number | =RANDBETWEEN(1,100) |
| Convert Number to Words (custom) | =SpellNumber(A1) |
| Switch Logic | =SWITCH(A1,1,"Yes",2,"No","Check") |
| LAMBDA Function | Ctrl + N |
These are modern Excel functions designed for efficiency and automation.
Final Thoughts
Excel formulas are not just tools. They are like shortcuts to clear thinking. Each formula saves time. Each group has its own charm. Once you start using them regularly you will notice how smoothly your work flows. The more formulas you learn the more confidently you handle data.
Click here to Read more :
Important Excel shortcuts
Top 100 Excel Interview Questions & Answers
){kind=link}
0 Comments