50 essential Excel formulas
SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
👍19❤3🔥2👏2
7 Baby Steps to Master MS Excel for Business/ Data Analysis
1. Learn Basics: Understand the interface, basic formulas (SUM, IF), and formatting.
2. Manage Data: Sort, filter, clean, and structure datasets efficiently.
3. Master Formulas: Use advanced functions like VLOOKUP, HLOOKUP, INDEX-MATCH, and TEXT.
4. Work with Pivot Tables: Create pivot tables to summarize and analyze data dynamically.
5. Data Visualization: Create charts, graphs, and dashboards for impactful presentations.
6. Use Advanced Tools: Learn Power Query, Power Pivot, and macros for automation.
7. Practice with Real Data: Work on business scenarios and case studies to apply your skills.
Free Excel Resources: https://t.iss.one/excel_data
1. Learn Basics: Understand the interface, basic formulas (SUM, IF), and formatting.
2. Manage Data: Sort, filter, clean, and structure datasets efficiently.
3. Master Formulas: Use advanced functions like VLOOKUP, HLOOKUP, INDEX-MATCH, and TEXT.
4. Work with Pivot Tables: Create pivot tables to summarize and analyze data dynamically.
5. Data Visualization: Create charts, graphs, and dashboards for impactful presentations.
6. Use Advanced Tools: Learn Power Query, Power Pivot, and macros for automation.
7. Practice with Real Data: Work on business scenarios and case studies to apply your skills.
Free Excel Resources: https://t.iss.one/excel_data
👍5❤2🥰1👏1💩1
Hi guys,
Now, you can also find free EXCEL & Data Analytics Resources on WhatsApp
👇👇
https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
Now, you can also find free EXCEL & Data Analytics Resources on WhatsApp
👇👇
https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
❤2👍2🥰1
10 Must-Know Excel Time Functions ⏱️
🟢 NOW() – Current date & time.
🟢 TODAY() – Current date only.
🟢 HOUR() – Extracts the hour.
🟢 MINUTE() – Extracts the minute.
🟢 SECOND() – Extracts the second.
🟢 DATE() – Returns a specific date.
🟢 TIME() – Returns a specific time.
🟢 TEXT() – Format date/time.
🟢 DATEDIF() – Difference between dates.
🟢 NETWORKDAYS() – Counts workdays between dates.
🟢 NOW() – Current date & time.
🟢 TODAY() – Current date only.
🟢 HOUR() – Extracts the hour.
🟢 MINUTE() – Extracts the minute.
🟢 SECOND() – Extracts the second.
🟢 DATE() – Returns a specific date.
🟢 TIME() – Returns a specific time.
🟢 TEXT() – Format date/time.
🟢 DATEDIF() – Difference between dates.
🟢 NETWORKDAYS() – Counts workdays between dates.
👍7🔥1
Forwarded from Microsoft Excel for Finance & Data Analytics
🗂Performing calculations on data
Excel workbooks gives you a handy place to store and organize your data, but you can also do a lot more with your data in Excel.
One important task you can perform is to calculate totals for the values in a series of related cells.
You can also use Excel to discover other information about the data you select, such as the maximum or minimum value in a group of cells.
Regardless of your needs, Excel gives you the ability to find the information you want. And if you make an error, you can find the cause and correct it quickly.
Often, you can’t access the information you want without referencing more than one cell, and it’s also often true that you’ll use the data in the same group of cells for more than one calculation.
Excel makes it easy to reference several cells at the same time, so that you can define your calculations quickly.
Keep tuned as i guide you through procedures related to streamlining references to groups of data on your worksheets and creating and correcting formulas that summarize an organization’s business operations.
Excel workbooks gives you a handy place to store and organize your data, but you can also do a lot more with your data in Excel.
One important task you can perform is to calculate totals for the values in a series of related cells.
You can also use Excel to discover other information about the data you select, such as the maximum or minimum value in a group of cells.
Regardless of your needs, Excel gives you the ability to find the information you want. And if you make an error, you can find the cause and correct it quickly.
Often, you can’t access the information you want without referencing more than one cell, and it’s also often true that you’ll use the data in the same group of cells for more than one calculation.
Excel makes it easy to reference several cells at the same time, so that you can define your calculations quickly.
Keep tuned as i guide you through procedures related to streamlining references to groups of data on your worksheets and creating and correcting formulas that summarize an organization’s business operations.
👍5