Excel vs Power BI: Key Differences

Excel:
- Purpose: Ideal for spreadsheet tasks, basic calculations, and small-scale data analysis.
- Best For: Creating simple reports, working with small datasets, and producing basic charts.
- Data Handling: Best suited for small to medium-sized datasets; performance can decline with larger data.
- Visualizations: Offers basic charts and graphs but lacks interactivity.
- Sharing: Usually shared via email or cloud storage (e.g., OneDrive); not ideal for real-time collaboration.
- Automation: Limited automation capabilities, with manual refreshes or basic macros.

Power BI:
- Purpose: Designed for advanced data analysis and creating interactive, visually rich reports.
- Best For: Handling large datasets, integrating data from multiple sources, and building dynamic dashboards.
- Data Handling: Efficient with very large datasets, maintaining high performance.
- Visualizations: Provides highly interactive visualizations with drill-down features and deep insights.
- Sharing: Allows real-time collaboration through online sharing and automatic report updates.
- Automation: Supports automatic data refreshes and real-time reporting capabilities.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://t.iss.one/DataSimplifier

Like this post for more content like this 👍♥️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

Possible Reasons for resume not getting shortlisted:

🎯 Lack of relevant skills and experience required for the job post
🎯 Absence of keywords mentioned in the job description
🎯 Using generic terms/sentences to describe your projects/experiences
▶️ Try to write them in START format
S - Situation
T - Task
A - Action
R - Result
🎯 No/inactive online presence
🎯 Weak networking efforts
🎯 Lengthy or poorly formatted layout.
🎯 Error/ Grammatical Mistakes
🎯 Not ATS friendly
🎯 Neglecting the power of referrals and networking
🎯 Lack of highlighting standout achievements (or spikes) at the top

Master 𝗘𝘅𝗰𝗲𝗹 in just 𝟯𝟬 𝗗𝗮𝘆𝘀 with this simple plan!

Here's your complete Excel roadmap

𝗪𝗲𝗲𝗸 𝟭: 𝗘𝘀𝘀𝗲𝗻𝘁𝗶𝗮𝗹 𝗘𝘅𝗰𝗲𝗹 𝗕𝗮𝘀𝗶𝗰𝘀

➛ Day 1-2: Introduction to Excel, interface, and basic navigation.

➛ Day 3-4: Working with cells, rows, columns, and basic formatting.

➛ Day 5-7: Basic formulas and functions – SUM, AVERAGE, MIN, MAX.

𝗪𝗲𝗲𝗸 𝟮: 𝗗𝗮𝘁𝗮 𝗠𝗮𝗻𝗶𝗽𝘂𝗹𝗮𝘁𝗶𝗼𝗻 𝗮𝗻𝗱 𝗙𝗼𝗿𝗺𝘂𝗹𝗮𝘀

➛ Day 8-10: Advanced formulas – IF, VLOOKUP, and INDEX-MATCH.

➛ Day 11-13: Data sorting, filtering, and conditional formatting.

➛ Day 14: Practice session – Work on organizing and analyzing a small dataset.

𝗪𝗲𝗲𝗸 𝟯: 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 𝗧𝗼𝗼𝗹𝘀

➛ Day 15-17: Pivot tables and charts – summarizing and visualizing data.

➛ Day 18-20: Working with data validation, drop-down lists, and named ranges.

➛ Day 21: Practice building a pivot table from scratch.

𝗪𝗲𝗲𝗸 𝟰: 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗙𝗲𝗮𝘁𝘂𝗿𝗲𝘀 𝗮𝗻𝗱 𝗖𝗮𝗽𝘀𝘁𝗼𝗻𝗲

➛ Day 22-24: Macros – Automating tasks with recorded macros.

➛ Day 25-27: Power Query and Power Pivot – for advanced data analysis.

➛ Day 28-30: Capstone project – Analyze a large dataset using all your Excel skills and create a comprehensive report.

Like if it helps ❤️

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://t.iss.one/DataSimplifier

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

🗂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 Cheat Sheet 📔

This Excel cheatsheet is designed to be your quick reference guide for using Microsoft Excel efficiently.

1. Basic Functions
   - SUM: =SUM(range)
   - AVERAGE: =AVERAGE(range)
   - COUNT: =COUNT(range)
   - MAX: =MAX(range)
   - MIN: =MIN(range)

2. Text Functions
   - CONCATENATE: =CONCATENATE(text1, text2, ...) or =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
   - LEFT: =LEFT(text, num_chars)
   - RIGHT: =RIGHT(text, num_chars)
   - MID: =MID(text, start_num, num_chars)
   - TRIM: =TRIM(text)

3. Logical Functions
   - IF: =IF(condition, true_value, false_value)
   - AND: =AND(condition1, condition2, ...)
   - OR: =OR(condition1, condition2, ...)
   - NOT: =NOT(condition)

4. Lookup Functions
   - VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
   - HLOOKUP: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
   - INDEX: =INDEX(array, row_num, [column_num])
   - MATCH: =MATCH(lookup_value, lookup_array, [match_type])

5. Data Sorting & Filtering
   - Sort: *Data > Sort*
   - Filter: *Data > Filter*
   - Advanced Filter: *Data > Advanced*

6. Conditional Formatting
   - Apply Formatting: *Home > Conditional Formatting > New Rule*
   - Highlight Cells: *Home > Conditional Formatting > Highlight Cells Rules*

7. Charts and Graphs
   - Insert Chart: *Insert > Select Chart Type*
   - Customize Chart: *Chart Tools > Design/Format*

8. PivotTables
   - Create PivotTable: *Insert > PivotTable*
   - Refresh PivotTable: *Right-click on PivotTable > Refresh*

9. Data Validation
   - Set Validation: *Data > Data Validation*
   - List: *Allow: List > Source: range or items*

10. Protecting Data
    - Protect Sheet: *Review > Protect Sheet*
    - Protect Workbook: *Review > Protect Workbook*

11. Shortcuts
    - Copy: Ctrl + C
    - Paste: Ctrl + V
    - Undo: Ctrl + Z
    - Redo: Ctrl + Y
    - Save: Ctrl + S

12. Printing Options
    - Print Area: *Page Layout > Print Area > Set Print Area*
    - Page Setup: *Page Layout > Page Setup*

Checklist for Data Analyst: https://dataanalytics.beehiiv.com/p/data

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://t.iss.one/DataSimplifier

Like for more Interview Resources ♥️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

Data analysis can be categorized into four types: descriptive, diagnostic, predictive, and prescriptive analysis.

Descriptive analysis summarizes raw data, diagnostic analysis determines why something happened, predictive analysis uses past data to predict the future, and prescriptive analysis suggests actions based on predictions.

Data analysis is a comprehensive method that involves inspecting, cleansing, transforming, and modeling data to discover useful information, make conclusions, and support decision-making. It's a process that empowers organizations to make informed decisions, predict trends, and improve operational efficiency.

The data analysis process involves several steps, including defining objectives and questions, data collection, data cleaning, data analysis, data interpretation and visualization, and data storytelling. Each step is crucial to ensuring the accuracy and usefulness of the results.

There are various data analysis techniques, including exploratory analysis, regression analysis, Monte Carlo simulation, factor analysis, cohort analysis, cluster analysis, time series analysis, and sentiment analysis. Each has its unique purpose and application in interpreting data.

Data analysis typically utilizes tools such as Python, R, SQL for programming, and Power BI, Tableau, and Excel for visualization and data management

You can start learning data analysis by understanding the basics of statistical concepts, data types, and structures. Then learn a programming language like Python or R, master data manipulation and visualization, and delve into specific data analysis techniques.

10 Must-Have Excel Skills for Professionals

1.Pivot Table
2. Xlookup
3. Pivot Charts
4. Flash Fill
5. Quick Analysis
6. Power View
7. Conditional Formatting
8. Moving Columns into Rows
9. IF Formulas
10. Auditing Formulas

#excel

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 😊

Top 8 Excel interview questions data analysts 👇👇

1. Advanced Formulas:
- Can you explain the difference between VLOOKUP and INDEX-MATCH functions? When would you prefer one over the other?
- How would you use the SUMIFS function to analyze data with multiple criteria?

2. Data Cleaning and Manipulation:
- Describe a scenario where you had to clean and transform messy data in Excel. What techniques did you use?
- How do you remove duplicates from a dataset, and what considerations should be taken into account?

3. Pivot Tables:
- Explain the purpose of a pivot table. Provide an example of when you used a pivot table to derive meaningful insights.
- What are slicers in a pivot table, and how can they be beneficial in data analysis?

4. Data Visualization:
- Share your approach to creating effective charts and graphs in Excel to communicate data trends.
- How would you use conditional formatting to highlight key information in a dataset?

5. Statistical Analysis:
- Discuss a situation where you applied statistical analysis in Excel to draw conclusions from a dataset.
- Explain the steps you would take to perform regression analysis in Excel.

6. Macros and Automation:
- Have you ever used Excel macros to automate a repetitive task? If so, provide an example.
- What are the potential risks and benefits of using macros in a data analysis workflow?

7. Data Validation:
- How do you implement data validation in Excel, and why is it important in data analysis?
- Can you give an example of when you used Excel's data validation to improve data accuracy?

8. Data Linking and External Data Sources:
- Describe a situation where you had to link data from multiple Excel workbooks. How did you approach this task?
- How would you import data from an external database into Excel for analysis?

ENJOY LEARNING 👍👍

🎯 𝐄𝐬𝐬𝐞𝐧𝐭𝐢𝐚𝐥 𝐃𝐀𝐓𝐀 𝐀𝐍𝐀𝐋𝐘𝐒𝐓 𝐒𝐊𝐈𝐋𝐋𝐒 𝐓𝐡𝐚𝐭 𝐑𝐞𝐜𝐫𝐮𝐢𝐭𝐞𝐫𝐬 𝐋𝐨𝐨𝐤 𝐅𝐨𝐫 🎯

If you're applying for Data Analyst roles, having technical skills like SQL and Power BI is important—but recruiters look for more than just tools!

🔹 1️⃣ 𝐒𝐐𝐋 𝐢𝐬 𝐊𝐈𝐍𝐆 👑—𝐌𝐚𝐬𝐭𝐞𝐫 𝐈𝐭
✅ Know how to write optimized queries (not just SELECT * from everywhere!)
✅ Be comfortable with JOINS, CTEs, Window Functions & Performance Optimization
✅ Practice solving real-world business scenarios using SQL
💡 Example Question: How would you find the top 5 best-selling products in each category using SQL?

🔹 2️⃣ 𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐀𝐜𝐮𝐦𝐞𝐧: 𝐓𝐡𝐢𝐧𝐤 𝐋𝐢𝐤𝐞 𝐚 𝐃𝐞𝐜𝐢𝐬𝐢𝐨𝐧-𝐌𝐚𝐤𝐞𝐫
✅ Understand the why behind the data—not just the numbers
✅ Learn how to frame insights for different stakeholders (Tech & Non-Tech)
✅ Use data storytelling—simplify complex findings into actionable takeaways
💡 Example: Instead of saying, "Revenue increased by 12%," say "Revenue increased 12% after launching a targeted discount campaign, driving a 20% increase in repeat purchases."

🔹 3️⃣ 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 / 𝐓𝐚𝐛𝐥𝐞𝐚𝐮—𝐌𝐚𝐤𝐞 𝐃𝐚𝐬𝐡𝐛𝐨𝐚𝐫𝐝𝐬 𝐓𝐡𝐚𝐭 𝐒𝐩𝐞𝐚𝐤!
✅ Avoid overloading dashboards with too many visuals—focus on key KPIs
✅ Use interactive elements (filters, drill-throughs) for better usability
✅ Keep visuals simple & clear—bar charts are better than complex pie charts!
💡 Tip: Before creating a dashboard, ask: "What business problem does this solve?"

🔹 4️⃣ 𝐏𝐲𝐭𝐡𝐨𝐧 & 𝐄𝐱𝐜𝐞𝐥—𝐇𝐚𝐧𝐝𝐥𝐞 𝐃𝐚𝐭𝐚 𝐄𝐟𝐟𝐢𝐜𝐢𝐞𝐧𝐭𝐥𝐲
✅ Python for data wrangling, EDA & automation (Pandas, NumPy, Seaborn)
✅ Excel for quick analysis, PivotTables, VLOOKUP/XLOOKUP, Power Query
✅ Know when to use Excel vs. Python (hint: small vs. large datasets)

Being a Data Analyst is more than just running queries—it’s about understanding the business, making insights actionable, and communicating effectively!

Free Resources: https://t.iss.one/sqlspecialist

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

✅ Basic Level (Focuses on fundamental concepts and operations in SQL, including syntax, basic commands, and the definitions of key terms.)

1. Explain the difference between drop and truncate.
2. What are Constraints in SQL?
3. Describe the use of the SELECT statement in SQL.
4. What is a primary key in SQL?
5. Explain the difference between CHAR and VARCHAR data types in SQL.
6. What is a foreign key in SQL?
7. How do you use the GROUP BY statement in SQL?
8. What is a JOIN in SQL, and can you describe a scenario where you would use it?
9. How does the WHERE clause work in SQL?
10. Explain the use of the INSERT statement in SQL.

✅Intermediate Level ( Involves more complex queries, including the use of sub-queries, joins, and functions. It requires a deeper understanding of SQL for data manipulation and analysis.)

1. Describe the Difference Between Window Functions and Aggregate Functions in SQL.
2. Write a SQL query to find the top three products with the highest revenue in the last quarter from a sales database.
3. What do you understand by sub-queries in SQL?
4. What is CTE in SQL?
5. Explain the use of the HAVING clause in SQL.
6. How do you implement pagination in SQL queries?
7. Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
8. Explain the concept of indexing in SQL and its benefits.
9. How can you prevent SQL injection in your queries?
10. Write a SQL query to find the second highest salary in a given table.

✅ Advanced Level (Covers topics related to database optimization, advanced data manipulation techniques, and understanding SQL's impact on database performance and design.)

1. Describe a SQL query challenge you faced related to optimizing database performance.
2. What is a Recursive Stored Procedure in SQL?
3. What are the subsets of SQL?
4. How do you use window functions for running totals and moving averages?
5. Explain the process and considerations for denormalizing a database.
6. Discuss the implications and solutions for dealing with NULL values in SQL operations.
7. How do you handle large datasets and optimize queries for big data in SQL?
8. Describe how to implement transaction control in SQL and its importance.
9. Explain the concept of materialized views in SQL and their use cases.
10. Discuss strategies for database sharding and partitioning in SQL and their impact on performance.

Data Analyst Interview Questions
[Python, SQL, PowerBI]

1. Is indentation required in python?
Ans: Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.

2. What are Entities and Relationships?
Ans:
Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.

Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.

3. What are Aggregate and Scalar functions?
Ans: An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. A scalar function returns a single value based on the input value.

4. What are Custom Visuals in Power BI?
Ans: Custom Visuals are like any other visualizations, generated using Power BI. The only difference is that it develops the custom visuals using a custom SDK. The languages like JQuery and JavaScript are used to create custom visuals in Power BI

ENJOY LEARNING 👍👍

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

Many people pay too much to learn Excel, but my mission is to break down barriers. I have shared complete learning series to learn Excel from scratch.

Here are the links to the Excel series

Complete Excel Topics for Data Analyst: https://t.iss.one/sqlspecialist/547

Part-1: https://t.iss.one/sqlspecialist/617

Part-2: https://t.iss.one/sqlspecialist/620

Part-3: https://t.iss.one/sqlspecialist/623

Part-4: https://t.iss.one/sqlspecialist/624

Part-5: https://t.iss.one/sqlspecialist/628

Part-6: https://t.iss.one/sqlspecialist/633

Part-7: https://t.iss.one/sqlspecialist/634

Part-8: https://t.iss.one/sqlspecialist/635

Part-9: https://t.iss.one/sqlspecialist/640

Part-10: https://t.iss.one/sqlspecialist/641

Part-11: https://t.iss.one/sqlspecialist/644

Part-12:
https://t.iss.one/sqlspecialist/646

Part-13: https://t.iss.one/sqlspecialist/650

Part-14: https://t.iss.one/sqlspecialist/651

Part-15: https://t.iss.one/sqlspecialist/654

Part-16: https://t.iss.one/sqlspecialist/655

Part-17: https://t.iss.one/sqlspecialist/658

Part-18: https://t.iss.one/sqlspecialist/660

Part-19: https://t.iss.one/sqlspecialist/661

Part-20: https://t.iss.one/sqlspecialist/662

Bonus: https://t.iss.one/sqlspecialist/663

I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.

But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.

You can join this telegram channel for more Excel Resources: https://t.iss.one/excel_analyst

Python Learning Series: https://t.iss.one/sqlspecialist/615

Complete SQL Topics for Data Analysts: https://t.iss.one/sqlspecialist/523

Complete Power BI Topics for Data Analysts: https://t.iss.one/sqlspecialist/588

I'll now start with learning series on SQL Interviews & Tableau.

Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.

Hope it helps :)

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 😊

Excel Formulas for Accountants ✅

1. What is the difference between the RANK() and DENSE_RANK() functions?

The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.

2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?

One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.

3. What is the shortcut to add a filter to a table in EXCEL?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.

4. What is DAX in Power BI?

DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.

5. Define shelves and sets in Tableau?

Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.