APPLY NOW
OUR COURSES
.webp)
100+ Most Important Excel Formulas You Must Know
If you want to land a high-paying job in accounting, finance, data analysis, or business operations, mastering at least 100+ most important excel formula is non-negotiable. Whether you're applying for internships, taking a microsoft office course, or preparing to work with real business data, knowing the right excel formula can save hours of manual work, reduce errors, and make you stand out in interviews.
Today, over 82% of corporate jobs require Microsoft Excel proficiency, and freshers with strong Excel skills earn 30–40% higher starting salaries (industry estimate). That’s why learning the right excel formula early in your career gives you a competitive edge.
Categories of Must-Know 100+ Excel Formulas
Arithmetic Formulas
| Formula | Description |
|---|---|
| =SUM(A1:A10) | Adds values in a range |
| =AVERAGE(A1:A10) | Finds the mean |
| =ROUND(A1,2) | Rounds to 2 decimal places |
| =ROUNDUP(A1,0) | Rounds up to nearest integer |
| =ROUNDDOWN(A1,0) | Rounds down to nearest integer |
| =PRODUCT(A1:A5) | Multiplies values |
| =POWER(A1,2) | Raises a number to a power |
| =SQRT(A1) | Square root of a number |
| =ABS(A1) | Absolute value |
| =SUBTOTAL(9,A1:A10) | Conditional sum with filters |
Logical Formulas
| Formula | Description |
|---|---|
| =IF(A1>100,"Pass","Fail") | Returns conditional result |
| =IFERROR(A1/B1,"Error") | Handles errors gracefully |
| =AND(A1>0,B1<10) | Checks multiple conditions |
| =OR(A1=5,B1=10) | Returns TRUE if any condition met |
| =NOT(A1=5) | Reverses logical value |
| =TRUE() | Returns TRUE |
| =FALSE() | Returns FALSE |
| =IFS(A1=1,"One",A1=2,"Two") | Multiple IF conditions |
| =SWITCH(A1,1,"Yes",2,"No") | Switches based on value |
| =XOR(A1>0,B1>0) | Exclusive OR logic |
Lookup & Reference Formulas
| Formula | Description |
|---|---|
| =VLOOKUP(101,A2:D20,3,FALSE) | Vertical lookup |
| =HLOOKUP("Item",A1:D5,2,FALSE) | Horizontal lookup |
| =INDEX(A1:C10,2,3) | Value at row/column |
| =MATCH(50,A1:A10,0) | Finds position of value |
| =XLOOKUP("Apple",A2:A10,B2:B10) | Modern lookup |
| =CHOOSE(2,"Red","Blue","Green") | Picks from list |
| =OFFSET(A1,2,3) | Reference offset |
| =INDIRECT("A1") | Reference via text |
| =ADDRESS(2,3) | Returns cell address |
| =ROW(A1) | Row number |
| =COLUMN(A1) | Column number |
| =AREAS((A1:B2,C3:D4)) | Counts ranges |
Text Formulas
| Formula | Description |
|---|---|
| =CONCAT(A1,B1) | Joins text |
| =TEXTJOIN(", ",TRUE,A1:A5) | Joins with delimiter |
| =LEFT(A1,5) | Extracts first characters |
| =RIGHT(A1,3) | Extracts last characters |
| =MID(A1,2,4) | Extracts middle characters |
| =LEN(A1) | Counts characters |
| =TRIM(A1) | Removes extra spaces |
| =PROPER(A1) | Capitalizes words |
| =UPPER(A1) | Converts to uppercase |
| =LOWER(A1) | Converts to lowercase |
| =REPLACE(A1,1,3,"XYZ") | Replaces text |
| =SUBSTITUTE(A1,"old","new") | Substitutes text |
| =REPT(A1,3) | Repeats text |
| =TEXT(A1,"DD/MM/YYYY") | Formats text |
| =SEARCH("word",A1) | Finds position (case-insensitive) |
| =FIND("word",A1) | Finds position (case-sensitive) |
Date & Time Formulas
| Formula | Description |
|---|---|
| =TODAY() | Current date |
| =NOW() | Current date & time |
| =DAY(A1) | Extracts day |
| =MONTH(A1) | Extracts month |
| =YEAR(A1) | Extracts year |
| =WEEKDAY(A1) | Day of week |
| =WEEKNUM(A1) | Week number |
| =HOUR(A1) | Extracts hour |
| =MINUTE(A1) | Extracts minute |
| =SECOND(A1) | Extracts second |
| =NETWORKDAYS(A1,A2) | Working days |
| =NETWORKDAYS.INTL(A1,A2,1) | Custom working days |
| =WORKDAY(A1,5) | Adds working days |
| =WORKDAY.INTL(A1,10,1) | Custom workdays |
| =EDATE(A1,6) | Adds months |
| =EOMONTH(A1,1) | End of month |
| =DATEDIF(A1,A2,"Y") | Difference in years |
| =DATEDIF(A1,A2,"M") | Difference in months |
| =DATEDIF(A1,A2,"D") | Difference in days |
Financial Formulas
| Formula | Description |
|---|---|
| =PMT(rate,nper,pv) | Loan payment |
| =NPV(rate,values) | Net Present Value |
| =IRR(values) | Internal Rate of Return |
| =FV(rate,nper,pmt) | Future Value |
| =PV(rate,nper,pmt) | Present Value |
| =RATE(nper,pmt,pv) | Interest rate |
| =CUMIPMT(rate,nper,pv,start,end,type) | Cumulative interest |
| =CUMPRINC(rate,nper,pv,start,end,type) | Cumulative principal |
| =DB(cost,salvage,life,period) | Declining balance depreciation |
| =DDB(cost,salvage,life,period) | Double declining depreciation |
| =SLN(cost,salvage,life) | Straight-line depreciation |
| =SYD(cost,salvage,life,period) | Sum-of-years depreciation |
Statistical Formulas
| Formula | Description |
|---|---|
| =MEDIAN(A1:A10) | Middle value |
| =MODE(A1:A10) | Most frequent value |
| =STDEV(A1:A10) | Sample standard deviation |
| =STDEVP(A1:A10) | Population standard deviation |
| =VAR(A1:A10) | Sample variance |
| =VARP(A1:A10) | Population variance |
| =CORREL(A1:A10,B1:B10) | Correlation coefficient |
| =COVAR(A1:A10,B1:B10) | Covariance |
| =PERCENTILE(A1:A10,0.9) | 90th percentile |
| =QUARTILE(A1:A10,1) | First quartile |
| =RANK(A1,A1:A10) | Rank of value |
| =RANK.EQ(A1,A1:A10) | Rank (equal method) |
| =RANK.AVG(A1,A1:A10) | Rank (average method) |
| =LARGE(A1:A10,2) | 2nd largest value |
| =SMALL(A1:A10,2) | 2nd smallest value |
Advanced Data Analysis Formulas
| Formula | Description |
|---|---|
| =FILTER(A1:C10,B1:B10="Yes") | Filters data |
| =SORT(A1:A10) | Sorts values |
| =SORTBY(A1:A10,B1:B10) | Sorts by another column |
| =UNIQUE(A1:A10) | Unique values |
| =SEQUENCE(10) | Generates sequence |
| =RAND() | Random number |
| =RANDBETWEEN(1,100) | Random in range |
| =TRANSPOSE(A1:C3) | Rows ↔ Columns |
| =MMULT(A1:B2,C1:D2) | Matrix multiplication |
| =SUMPRODUCT(A1:A10,B1:B10) | Weighted sum |
| =ISNUMBER(A1) | Checks numeric |
| =ISTEXT(A1) | Checks text |
Quick Reference: 100+ Excel Formulas Cheat Sheet
| Category | Example Formula | Purpose |
|---|---|---|
| Arithmetic | =SUM(A1:A10) | Adds values |
| Logical | =IF(A1>100,"Pass","Fail") | Conditional output |
| Lookup | =VLOOKUP(101,A2:D20,3,FALSE) | Search in table |
| Text | =LEN(A1) | Count characters |
| Date & Time | =NETWORKDAYS(A1,A2) | Working days |
| Financial | =PMT(10%,12,100000) | Loan EMI |
| Statistical | =STDEV(A1:A10) | Standard deviation |
| Advanced Analysis | =FILTER(A1:C10,B1:B10="Yes") | Extract filtered data |
Master Practical Accounting + Management Skills
Apply For Job Guarantee Course Now
Frequently Asked Questions (FAQs)
Q1. What are the most important Excel formulas for beginners?
Start with basic formulas like SUM, AVERAGE, IF, COUNT, and VLOOKUP cover most day-to-day tasks.
Q2. How can I learn Excel fast?
Learn by practicing real datasets like expenses, payroll sheets, or inventory, not random theory. Daily practice for 30 days is enough to see results.
Q3. Is Excel required for accounting jobs?
Yes. Over 90% of accounting roles require Excel proficiency, especially formulas like VLOOKUP, SUMIF, and Pivot Tables.
Q4. Which Excel formulas are best for data analysis?
Key formulas include FILTER, SUMPRODUCT, XLOOKUP, UNIQUE, and INDEX-MATCH.
Q5. Is a Microsoft Office course enough to get a job?
Yes. If it includes practical business use cases. Pairing Excel + Accounting knowledge makes you job-ready faster.
Q6. How long does it take to learn Excel?
Basic concepts take 2–4 weeks, advanced formulas and reporting take 2–3 months of consistent practice.
Q7. What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is faster and more flexible, while VLOOKUP can only search in one direction.
Q8. Which Excel formula is used to calculate EMI?
Use the PMT formula: =PMT(rate, nper, pv)
Q9. Can I learn Excel without a laptop?
You can learn basics on mobile, but advanced formulas and reporting require a laptop.
Q10. What are the best Excel courses for freshers?
Courses that include live business datasets, accounting modules, and interview preparation and not just focus on theory.
Q11. Do companies test Excel skills during interviews?
Yes, most roles require Excel skill assessments such as MIS reporting, lookup formulas, and Pivot Tables.
Q12. Can Excel formulas automate business reports?
Yes, functions like SUMIF, INDEX-MATCH, FILTER, and dynamic arrays help automate periodic MIS reports.
Q13. How do I get started with the Accounting + Management Job Guarantee Program?
Get started with a free consultation call. Contact us now. You can also call us +91 7604014541, or email us at enquiry@gtiaindia.org for online courses.
