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 😊
👍7❤2
Top Excel Formulas Every Data Analyst Should Know
SUM():
Purpose: Adds up a range of numbers.
Example: =SUM(A1:A10)
AVERAGE():
Purpose: Calculates the average of a range of numbers.
Example: =AVERAGE(B1:B10)
COUNT():
Purpose: Counts the number of cells containing numbers.
Example: =COUNT(C1:C10)
IF():
Purpose: Returns one value if a condition is true, and another if false.
Example: =IF(A1 > 10, "Yes", "No")
VLOOKUP():
Purpose: Searches for a value in the first column and returns a value in the same row from another column.
Example: =VLOOKUP(D1, A1:B10, 2, FALSE)
HLOOKUP():
Purpose: Searches for a value in the first row and returns a value in the same column from another row.
Example: =HLOOKUP("Sales", A1:F5, 3, FALSE)
INDEX():
Purpose: Returns the value of a cell based on row and column numbers.
Example: =INDEX(A1:C10, 2, 3)
MATCH():
Purpose: Searches for a value and returns its position in a range.
Example: =MATCH("Product B", A1:A10, 0)
CONCATENATE() or CONCAT():
Purpose: Joins multiple text strings into one.
Example: =CONCATENATE(A1, " ", B1)
TEXT():
Purpose: Formats numbers or dates as text.
Example: =TEXT(A1, "dd/mm/yyyy")
Excel Resources: t.iss.one/excel_data
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Like this post for more content like this 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
SUM():
Purpose: Adds up a range of numbers.
Example: =SUM(A1:A10)
AVERAGE():
Purpose: Calculates the average of a range of numbers.
Example: =AVERAGE(B1:B10)
COUNT():
Purpose: Counts the number of cells containing numbers.
Example: =COUNT(C1:C10)
IF():
Purpose: Returns one value if a condition is true, and another if false.
Example: =IF(A1 > 10, "Yes", "No")
VLOOKUP():
Purpose: Searches for a value in the first column and returns a value in the same row from another column.
Example: =VLOOKUP(D1, A1:B10, 2, FALSE)
HLOOKUP():
Purpose: Searches for a value in the first row and returns a value in the same column from another row.
Example: =HLOOKUP("Sales", A1:F5, 3, FALSE)
INDEX():
Purpose: Returns the value of a cell based on row and column numbers.
Example: =INDEX(A1:C10, 2, 3)
MATCH():
Purpose: Searches for a value and returns its position in a range.
Example: =MATCH("Product B", A1:A10, 0)
CONCATENATE() or CONCAT():
Purpose: Joins multiple text strings into one.
Example: =CONCATENATE(A1, " ", B1)
TEXT():
Purpose: Formats numbers or dates as text.
Example: =TEXT(A1, "dd/mm/yyyy")
Excel Resources: t.iss.one/excel_data
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Like this post for more content like this 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
👍10❤5
Essential Excel Functions for Data Analysts 🚀
1️⃣ Basic Functions
SUM() – Adds a range of numbers. =SUM(A1:A10)
AVERAGE() – Calculates the average. =AVERAGE(A1:A10)
MIN() / MAX() – Finds the smallest/largest value. =MIN(A1:A10)
2️⃣ Logical Functions
IF() – Conditional logic. =IF(A1>50, "Pass", "Fail")
IFS() – Multiple conditions. =IFS(A1>90, "A", A1>80, "B", TRUE, "C")
AND() / OR() – Checks multiple conditions. =AND(A1>50, B1<100)
3️⃣ Text Functions
LEFT() / RIGHT() / MID() – Extract text from a string.
=LEFT(A1, 3) (First 3 characters)
=MID(A1, 3, 2) (2 characters from the 3rd position)
LEN() – Counts characters. =LEN(A1)
TRIM() – Removes extra spaces. =TRIM(A1)
UPPER() / LOWER() / PROPER() – Changes text case.
4️⃣ Lookup Functions
VLOOKUP() – Searches for a value in a column.
=VLOOKUP(1001, A2:B10, 2, FALSE)
HLOOKUP() – Searches in a row.
XLOOKUP() – Advanced lookup replacing VLOOKUP.
=XLOOKUP(1001, A2:A10, B2:B10, "Not Found")
5️⃣ Date & Time Functions
TODAY() – Returns the current date.
NOW() – Returns the current date and time.
YEAR(), MONTH(), DAY() – Extracts parts of a date.
DATEDIF() – Calculates the difference between two dates.
6️⃣ Data Cleaning Functions
REMOVE DUPLICATES – Found in the "Data" tab.
CLEAN() – Removes non-printable characters.
SUBSTITUTE() – Replaces text within a string.
=SUBSTITUTE(A1, "old", "new")
7️⃣ Advanced Functions
INDEX() & MATCH() – More flexible alternative to VLOOKUP.
TEXTJOIN() – Joins text with a delimiter.
UNIQUE() – Returns unique values from a range.
FILTER() – Filters data dynamically.
=FILTER(A2:B10, B2:B10>50)
8️⃣ Pivot Tables & Power Query
PIVOT TABLES – Summarizes data dynamically.
GETPIVOTDATA() – Extracts data from a Pivot Table.
POWER QUERY – Automates data cleaning & transformation.
You can find Free Excel Resources here: https://t.iss.one/excel_data
Hope it helps :)
#dataanalytics
1️⃣ Basic Functions
SUM() – Adds a range of numbers. =SUM(A1:A10)
AVERAGE() – Calculates the average. =AVERAGE(A1:A10)
MIN() / MAX() – Finds the smallest/largest value. =MIN(A1:A10)
2️⃣ Logical Functions
IF() – Conditional logic. =IF(A1>50, "Pass", "Fail")
IFS() – Multiple conditions. =IFS(A1>90, "A", A1>80, "B", TRUE, "C")
AND() / OR() – Checks multiple conditions. =AND(A1>50, B1<100)
3️⃣ Text Functions
LEFT() / RIGHT() / MID() – Extract text from a string.
=LEFT(A1, 3) (First 3 characters)
=MID(A1, 3, 2) (2 characters from the 3rd position)
LEN() – Counts characters. =LEN(A1)
TRIM() – Removes extra spaces. =TRIM(A1)
UPPER() / LOWER() / PROPER() – Changes text case.
4️⃣ Lookup Functions
VLOOKUP() – Searches for a value in a column.
=VLOOKUP(1001, A2:B10, 2, FALSE)
HLOOKUP() – Searches in a row.
XLOOKUP() – Advanced lookup replacing VLOOKUP.
=XLOOKUP(1001, A2:A10, B2:B10, "Not Found")
5️⃣ Date & Time Functions
TODAY() – Returns the current date.
NOW() – Returns the current date and time.
YEAR(), MONTH(), DAY() – Extracts parts of a date.
DATEDIF() – Calculates the difference between two dates.
6️⃣ Data Cleaning Functions
REMOVE DUPLICATES – Found in the "Data" tab.
CLEAN() – Removes non-printable characters.
SUBSTITUTE() – Replaces text within a string.
=SUBSTITUTE(A1, "old", "new")
7️⃣ Advanced Functions
INDEX() & MATCH() – More flexible alternative to VLOOKUP.
TEXTJOIN() – Joins text with a delimiter.
UNIQUE() – Returns unique values from a range.
FILTER() – Filters data dynamically.
=FILTER(A2:B10, B2:B10>50)
8️⃣ Pivot Tables & Power Query
PIVOT TABLES – Summarizes data dynamically.
GETPIVOTDATA() – Extracts data from a Pivot Table.
POWER QUERY – Automates data cleaning & transformation.
You can find Free Excel Resources here: https://t.iss.one/excel_data
Hope it helps :)
#dataanalytics
👍9🔥1
📊 Excel Hack of the Week
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
👍5🔥1
For data analysts, mastering these top 10 Excel concepts is crucial:
1. Formulas and Functions: Understand basic to advanced functions like SUM, AVERAGE, VLOOKUP, INDEX-MATCH, and IF statements.
2. PivotTables: Learn to summarize, analyze, and visualize data efficiently using PivotTables.
3. Data Cleaning and Formatting: Familiarize yourself with tools and techniques for cleaning and formatting messy data, such as text-to-columns, remove duplicates, and conditional formatting.
4. Charts and Graphs: Explore various chart types (e.g., bar, line, scatter) and understand when to use each for effective data visualization.
5. Data Validation: Implement data validation rules to ensure data integrity and accuracy, such as drop-down lists and input restrictions.
6. Data Analysis Tools: Utilize Excel's built-in data analysis tools like Goal Seek, Solver, and Data Tables for scenario analysis and optimization.
7. Conditional Formatting: Apply formatting based on specific conditions to highlight trends, outliers, or anomalies in data.
8. Named Ranges: Organize data efficiently by assigning meaningful names to ranges, making formulas more readable and easier to manage.
9. Data Tables and What-If Analysis: Use data tables to perform sensitivity analysis and scenario modeling for decision-making.
10. Power Query and Power Pivot: Explore advanced data manipulation and analysis capabilities using Excel's Power Query for data extraction, transformation, and loading (ETL) and Power Pivot for data modeling and analysis.
Give credits while sharing: https://t.iss.one/excel_analyst
ENJOY LEARNING 👍👍
1. Formulas and Functions: Understand basic to advanced functions like SUM, AVERAGE, VLOOKUP, INDEX-MATCH, and IF statements.
2. PivotTables: Learn to summarize, analyze, and visualize data efficiently using PivotTables.
3. Data Cleaning and Formatting: Familiarize yourself with tools and techniques for cleaning and formatting messy data, such as text-to-columns, remove duplicates, and conditional formatting.
4. Charts and Graphs: Explore various chart types (e.g., bar, line, scatter) and understand when to use each for effective data visualization.
5. Data Validation: Implement data validation rules to ensure data integrity and accuracy, such as drop-down lists and input restrictions.
6. Data Analysis Tools: Utilize Excel's built-in data analysis tools like Goal Seek, Solver, and Data Tables for scenario analysis and optimization.
7. Conditional Formatting: Apply formatting based on specific conditions to highlight trends, outliers, or anomalies in data.
8. Named Ranges: Organize data efficiently by assigning meaningful names to ranges, making formulas more readable and easier to manage.
9. Data Tables and What-If Analysis: Use data tables to perform sensitivity analysis and scenario modeling for decision-making.
10. Power Query and Power Pivot: Explore advanced data manipulation and analysis capabilities using Excel's Power Query for data extraction, transformation, and loading (ETL) and Power Pivot for data modeling and analysis.
Give credits while sharing: https://t.iss.one/excel_analyst
ENJOY LEARNING 👍👍
👍5🥰2