1. How many report formats are available in Excel?

There are three report formats available in Excel; they are:
1. Compact Form
2. Outline Form
3. Tabular Form

2. What are sets in Tableau?

Sets are custom fields that define a subset of data based on some conditions. A set can be based on a computed condition, for example, a set may contain customers with sales over a certain threshold. Computed sets update as your data changes. Alternatively, a set can be based on specific data point in your view.

3. What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.

4. What is slicing in Python?

Ans: Slicing is used to access parts of sequences like lists, tuples, and strings. The syntax of slicing is-[start:end:step]. The step can be omitted as well. When we write [start:end] this returns all the elements of the sequence from the start (inclusive) till the end-1 element. If the start or end element is negative i, it means the ith element from the end.

5. What is the map() and filter() function in Python?

The map() function is a higher-order function. This function accepts another function and a sequence of ‘iterables’ as parameters and provides output after applying the function to each iterable in the sequence. The filter() function is used to generate an output list of values that return true when the function is called.

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 :)

Hey guys 👋

I was working on something big from last few days.

Finally, I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

If you go on purchasing these books, it will cost you more than 15000 but I kept the minimal price for everyone's benefit.

I hope these resources will help you in data analytics journey.

I will add more resources here in the future without any additional cost.

All the best for your career ❤️

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

How to Build an Impressive Data Analysis Portfolio

As a data analyst, your portfolio is your personal brand. It showcases not only your technical skills but also your ability to solve real-world problems.

Having a strong, well-rounded portfolio can set you apart from other candidates and help you land your next job or freelance project.

Here's how to build a portfolio that will impress potential employers or clients.

1. Start with a Strong Introduction:
Before jumping into your projects, introduce yourself with a brief summary. Include your background, areas of expertise (e.g., Python, R, SQL), and any special achievements or certifications. This is your chance to give context to your portfolio and show your personality.

Tip: Make your introduction engaging and concise. Add a professional photo and link to your LinkedIn or personal website.


2. Showcase Real-World Projects:
The most powerful way to showcase your skills is through real-world projects. If you don’t have work experience yet, create your own projects using publicly available datasets (e.g., Kaggle, UCI Machine Learning Repository). These projects should highlight the full data analysis process—from data collection and cleaning to analysis and visualization.

Examples of project ideas:
- Analyzing customer data to identify purchasing trends.
- Predicting stock market trends based on historical data.
- Analyzing social media sentiment around a brand or event.


3. Focus on Impactful Data Visualizations:
Data visualization is a key part of data analysis, and it’s crucial that your portfolio highlights your ability to tell stories with data. Use tools like Tableau, Power BI, or Python (matplotlib, Seaborn) to create compelling visualizations that make complex data easy to understand.

Tips for great visuals:
- Use color wisely to highlight key insights.
- Avoid clutter; focus on clarity.
- Create interactive dashboards that allow users to explore the data.


4. Explain Your Methodology:
Employers and clients will want to know how you approached each project. For each project in your portfolio, explain the methodology you used, including:
- The problem or question you aimed to solve.
- The data sources you used.
- The tools and techniques you applied (e.g., statistical tests, machine learning models).
- The insights or results you discovered.

Make sure to document this in a clear, step-by-step manner, ideally with code snippets or screenshots.


5. Include Code and Jupyter Notebooks:
If possible, include links to your code or Jupyter Notebooks so potential employers or clients can see your technical expertise firsthand. Platforms like GitHub or GitLab are perfect for hosting your code. Make sure your code is well-commented and easy to follow.

Tip: Organize your projects in a structured way on GitHub, using descriptive README files for each project.


6. Feature a Blog or Case Studies:
If you enjoy writing, consider adding a blog or case study section to your portfolio. Writing about the data analysis process and the insights you’ve uncovered helps demonstrate your ability to communicate complex ideas in a digestible way. It also allows you to reflect on your projects and show your thought leadership in the field.

Blog post ideas:
- A breakdown of a data analysis project you’ve completed.
- Tips for aspiring data analysts.
- Reviews of tools and technologies you use regularly.

7. Continuously Update Your Portfolio:
Your portfolio is a living document. As you gain more experience and complete new projects, regularly update it to keep it fresh and relevant. Always add new skills, projects, and certifications to reflect your growth as a data analyst.

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 :)

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 👍👍

Essential Topics to Master Data Analytics Interviews: 🚀

SQL:
1. Foundations
- SELECT statements with WHERE, ORDER BY, GROUP BY, HAVING
- Basic JOINS (INNER, LEFT, RIGHT, FULL)
- Navigate through simple databases and tables

2. Intermediate SQL
- Utilize Aggregate functions (COUNT, SUM, AVG, MAX, MIN)
- Embrace Subqueries and nested queries
- Master Common Table Expressions (WITH clause)
- Implement CASE statements for logical queries

3. Advanced SQL
- Explore Advanced JOIN techniques (self-join, non-equi join)
- Dive into Window functions (OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, lead, lag)
- Optimize queries with indexing
- Execute Data manipulation (INSERT, UPDATE, DELETE)

Python:
1. Python Basics
- Grasp Syntax, variables, and data types
- Command Control structures (if-else, for and while loops)
- Understand Basic data structures (lists, dictionaries, sets, tuples)
- Master Functions, lambda functions, and error handling (try-except)
- Explore Modules and packages

2. Pandas & Numpy
- Create and manipulate DataFrames and Series
- Perfect Indexing, selecting, and filtering data
- Handle missing data (fillna, dropna)
- Aggregate data with groupby, summarizing data
- Merge, join, and concatenate datasets

3. Data Visualization with Python
- Plot with Matplotlib (line plots, bar plots, histograms)
- Visualize with Seaborn (scatter plots, box plots, pair plots)
- Customize plots (sizes, labels, legends, color palettes)
- Introduction to interactive visualizations (e.g., Plotly)

Excel:
1. Excel Essentials
- Conduct Cell operations, basic formulas (SUMIFS, COUNTIFS, AVERAGEIFS, IF, AND, OR, NOT & Nested Functions etc.)
- Dive into charts and basic data visualization
- Sort and filter data, use Conditional formatting

2. Intermediate Excel
- Master Advanced formulas (V/XLOOKUP, INDEX-MATCH, nested IF)
- Leverage PivotTables and PivotCharts for summarizing data
- Utilize data validation tools
- Employ What-if analysis tools (Data Tables, Goal Seek)

3. Advanced Excel
- Harness Array formulas and advanced functions
- Dive into Data Model & Power Pivot
- Explore Advanced Filter, Slicers, and Timelines in Pivot Tables
- Create dynamic charts and interactive dashboards

Power BI:
1. Data Modeling in Power BI
- Import data from various sources
- Establish and manage relationships between datasets
- Grasp Data modeling basics (star schema, snowflake schema)

2. Data Transformation in Power BI
- Use Power Query for data cleaning and transformation
- Apply advanced data shaping techniques
- Create Calculated columns and measures using DAX

3. Data Visualization and Reporting in Power BI
- Craft interactive reports and dashboards
- Utilize Visualizations (bar, line, pie charts, maps)
- Publish and share reports, schedule data refreshes

Statistics Fundamentals:
- Mean, Median, Mode
- Standard Deviation, Variance
- Probability Distributions, Hypothesis Testing
- P-values, Confidence Intervals
- Correlation, Simple Linear Regression
- Normal Distribution, Binomial Distribution, Poisson Distribution.

Show some ❤️ if you're ready to elevate your data analytics journey! 📊

ENJOY LEARNING 👍👍

Must important topics to look before any excel interview for Data/Business Analyst role :-

Data Handling: Cell formatting, rows/columns, basic functions (SUM, AVERAGE, COUNT etc).

Data Management Mastery: Sorting, filtering, data validation, diverse cell references. Function Proficiency: Explore SUMIF, (V & X)LOOKUP, INDEX, MATCH, IF, and advanced function nesting.

Advanced Analytics: Master PivotTables for dynamic data analysis and various chart creation.

Advanced Analysis Techniques: Conditional formatting, goal-seeking, in-depth what-if analysis.

Advanced Functions: COUNTIF/IFS, SUMIFS, AVERAGEIF/IFS, CONCATENATE, date/time functions.

These are the most important one's which I tried to summarise in the best possible way, please let me know in the comments if I have missed something important.

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://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i

Hope it helps :)

#dataanalytics

The job search journey can be tough, but every step you take brings you closer to your goal. Customizing resumes and cover letters, practicing coding challenges, and staying on top of industry trends are all part of the path to success. Remember, you only need one "yes" to change everything.

There are a lot of amazing people out there looking for that one opportunity. Every application you send, every new connection you make, and all those late nights spent refining your portfolio or building professional relationships are steps toward landing the right job. It can be really exhausting, but every bit of effort gets you closer to your goal.

The process can be filled with doubts and uncertainties, but having a structured approach and setting daily goals can help manage it. Joining professional groups, attending webinars, and seeking mentorship are also great ways to gain insights and stay motivated.

In the end, all the time and energy you invest—whether it’s perfecting a project, learning a new tool, or reaching out to potential mentors—pays off. So, if you’re looking for a job, keep learning, applying, and networking.

90% of jobs require Excel skills.

But most people underestimate its importance.

Here're 7 Excel hacks you don't want to miss: 🧵 👇🏻

1. Quick Data Analysis:

• Select a cell in your data.
• Home > Analyze Data.
• Choose an option and click Insert PivotChart.

Like for more ❤️


2. Freeze columns/rows:

• Select the cell below and to the right of what you want to freeze
• Click View > Freeze Panes > Freeze Panes



3. If Function

• Open Excel and choose a cell.
• Insert IF function.
• Apply and repeat conditions.
• Close bracket and press Enter.

4. Quick Data Analysis:

• Select a cell in your data.
• Home > Analyze Data.
• Choose an option (Rank, Trend, Outlier, Majority) and click Insert PivotChart.

5. Format numbers in cells:

• Press CTRL + 1 and select Number.
• Right-click the cell or cell range, select Format Cells… , and select Number.
• Select the small arrow, dialog box launcher, and then select Number.

6. Creating Excel formulas:

• Select a cell and Type "="
• Type a cell or function (e.g., SUM)
• Add an operator or range
• Press Enter to see the result in the cell; the formula appears in the Formula bar

7. SUMIFS function:

• Select an empty cell.
• Determine the initial cell range.
• Determine the SUMIF criteria.
• Determine your sum_range criteria.

Ask smart questions
The right question can reveal more than a hundred answers. Make them think while you gather intel.

Here's how I would learn Microsoft Excel for data analysis fast if I had to start from zero:

1) I would ignore most Excel courses/tutorials.

I'm going to be honest here.

Most Excel educational content does not teach you how to analyze data.

In most organizations, Excel is "business process glue."

This is what most courses teach.

2) I would start with Excel tables.

For analysis, you must have tables where:

Each row is an analytical item of interest (e.g., customers, patients, claims, etc.).

Each column is an attribute of these items.

Learn tables.

3) I would learn only PivotTable fundamentals.

For data analysis, tables of any kind are good for:

1. Looking up exact values.
2. Comparing exact values.

PivotTables are great, but most professionals overuse them.

Learn PivotTable fundamentals and then move on.

4) Learn data visualization.

Humans are visual creatures.

So learn:

Histograms
Line charts
Bar charts
Line charts

To visually analyze data.

This is way more powerful than only using PivotTables.

BTW - The best use for PivotTables is to feed PivotCharts!

5) Learn Power Query.

If you're serious about analyzing data with Excel, do yourself a favor and learn Power Query.

PQ skills allow you to clean and transform your data in powerful ways.

It also automates this as a repeatable process.

Use PQ instead of convoluted formulas.

6) Expand your skillset.

When you're ready, it's time to learn specific analysis techniques to up your game:

RFM analysis
Logistic regression
Market basket analysis
K-means cluster analysis
Decision tree machine learning

Some of these you can implement using Solver.

Others require...

7) Python in Excel

Microsoft is including Python in Excel as part of Microsoft 365 subscriptions.

That effectively makes it free for millions of professionals.

Like Power Query, Python in Excel is for those serious about analyzing data with Excel.

Excel Roadmap in 2025 ✅

Week 1: Basic formulas are your foundation
🔸 SUM, AVERAGE, COUNT mastery
🔸 Keyboard shortcuts that save 2hrs/day
🔸 Professional formatting techniques
🔸 Navigation speed tricks

Week 2: VLOOKUP revolution
🔸 Automate 80% of manual work
🔸 Dynamic reporting basics
🔸 Pivot Tables in 60 minutes
🔸 First team recognition

Week 3: Advanced formulas
🔸 INDEX/MATCH mastery
🔸 Dashboard creation
🔸 Power Query foundations
🔸 Template building

Week 4: Career-changing skills
🔸 Automated reporting
🔸 KPI tracking systems
🔸 VBA automation basics
🔸 Portfolio projects

Data Analytics Interview Questions

Q1: Describe a situation where you had to clean a messy dataset. What steps did you take?

Ans: I encountered a dataset with missing values, duplicates, and inconsistent formats. I used Python's Pandas library to identify and handle missing values, standardized data formats using regular expressions, and removed duplicates. I also validated the cleaned data against known benchmarks to ensure accuracy.

Q2: How do you handle outliers in a dataset?

Ans: I start by visualizing the data using box plots or scatter plots to identify potential outliers. Then, depending on the nature of the data and the problem context, I might cap the outliers, transform the data, or even remove them if they're due to errors.

Q3: How would you use data to suggest optimal pricing strategies to Airbnb hosts?

Ans: I'd analyze factors like location, property type, amenities, local events, and historical booking rates. Using regression analysis, I'd model the relationship between these factors and pricing to suggest an optimal price range. Additionally, analyzing competitor pricing in the area can provide insights into market rates.

Q4: Describe a situation where you used data to improve the user experience on the Airbnb platform.

Ans: While analyzing user feedback and platform interaction data, I noticed that users often had difficulty navigating the booking process. Based on this, I suggested streamlining the booking steps and providing clearer instructions. A/B testing confirmed that these changes led to a higher conversion rate and improved user feedback.

What's the ONE skill you absolutely NEED to master in 2025 to stay ahead of the curve?

🤔 The latest video dives deep into the MOST in-demand skill this year.

Watch Now: https://youtu.be/GuQHC2_pPxc?feature=shared

And trust me, you won't want to miss this!

Register Now: https://surl.li/bbkbvd

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

Hi guys,

Now you can directly find job opportunities on WhatsApp. Here is the list of top job related channels on WhatsApp 👇

Latest Jobs & Internship Opportunities: https://whatsapp.com/channel/0029VaI5CV93AzNUiZ5Tt226

Python & AI Jobs: https://whatsapp.com/channel/0029VaxtmHsLikgJ2VtGbu1R

Software Engineer Jobs: https://whatsapp.com/channel/0029VatL9a22kNFtPtLApJ2L

Data Science Jobs: https://whatsapp.com/channel/0029VaxTMmQADTOA746w7U2P

Data Analyst Jobs: https://whatsapp.com/channel/0029Vaxjq5a4dTnKNrdeiZ0J

Web Developer Jobs: https://whatsapp.com/channel/0029Vb1raTiDjiOias5ARu2p

Remote Jobs: https://whatsapp.com/channel/0029Vb1RrFuC1Fu3E0aiac2E

Google Jobs: https://whatsapp.com/channel/0029VaxngnVInlqV6xJhDs3m

Hope it helps :)

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 :)

Learning Excel for data analytics can be a valuable skill. Here are some steps you can take to learn Excel topics for data analytics:

1. Take an online course: There are many online courses available that specifically focus on Excel for data analytics. Look for courses on platforms like Coursera, Udemy, or LinkedIn Learning.

2. Practice with datasets: The best way to learn Excel is by practicing with real-world datasets. You can find datasets online on websites like Kaggle or data.gov. Practice manipulating and analyzing the data using Excel functions and tools.

3. Learn important functions: Familiarize yourself with important Excel functions for data analysis such as VLOOKUP, INDEX-MATCH, SUMIFS, AVERAGEIFS, COUNTIFS, and PivotTables.

4. Master data visualization: Excel offers powerful tools for data visualization such as charts and graphs. Learn how to create visually appealing and informative charts to present your data effectively.

5. Explore advanced features: Excel has many advanced features that can be useful for data analytics, such as Power Query, Power Pivot, and macros. Take the time to explore these features and understand how they can enhance your data analysis capabilities.

6. Join online communities: Join online forums and communities dedicated to Excel and data analytics. This can be a great way to ask questions, share knowledge, and learn from others who are also interested in data analytics.

7. Practice regularly: Like any skill, learning Excel for data analytics requires regular practice. Set aside time each week to practice your Excel skills and work on different data analysis projects.

Join for more excel resources: https://t.iss.one/excel_analyst

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 :)

Quick Recap of Excel Concepts

1️⃣ Cells & Ranges: Basic units of Excel where data is entered; ranges refer to groups of cells like A1:A10.

2️⃣ Formulas: Built-in functions used for calculations, such as =SUM(), =AVERAGE(), and =IF().

3️⃣ Cell Referencing: Refers to cells in formulas, with options like absolute ($A$1), relative (A1), and mixed referencing (A$1).

4️⃣ Pivot Tables: A powerful feature to summarize, analyze, explore, and present large data sets interactively.

5️⃣ Charts: Graphical representations of data, including bar charts, line charts, pie charts, and scatter plots.

6️⃣ Conditional Formatting: Automatically applies formatting like colors or icons to cells based on specified conditions.

7️⃣ Data Validation: Ensures that only valid data is entered into a cell, useful for creating dropdown lists or setting data entry rules.

8️⃣ VLOOKUP / HLOOKUP: Functions used to search for a value in a table and return related information.

9️⃣ Macros: Automate repetitive tasks by recording actions or writing VBA code.

🔟 Excel Tables: Convert ranges into structured tables for easier filtering, sorting, and analysis, while automatically updating formulas and ranges.

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 :)