Excel Cheatsheet

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

Struggling with messy data in Excel? Here’s how to clean it up:

If you’re dealing with unorganized data in Excel, here are some quick steps to clean your sheets:

1️⃣ Trim Function: Eliminate leading & trailing spaces easily.
2️⃣ Remove Duplicates: Use the Data tab feature to delete duplicate rows.
3️⃣ Text to Columns: Separate combined information into different cells.
4️⃣ Filter Blank Cells: Quickly find and manage empty cells.
5️⃣ Clean Function: Remove non-printable characters for a tidier dataset.

Like for more ❤️

Excel Cheatsheet

IMPORTANCE OF MS EXCEL IN OUR DAILY AND BUSINESS LIVES:

1. Easy Arithmetic Solutions.
2. Formatting Options.
3. Charts for Analysis.
4. Bring all the data in one place.
5. Human Resource Planning.
6. Multiple formulas.
7. Multiple functions.

Here are some Excel shortcuts that are commonly used by data analysts:

1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats

These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.

One of the very important tool that a data analyst must be aware of is Excel

Excel Shortcuts 👆

Top 10 Excel Functions Used by Data Analysts

1. VLOOKUP:
• Example: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Usage: Searches for a value in the first column of a table and returns a value in the same row from another column.

2. HLOOKUP:
• Example: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Usage: Similar to VLOOKUP, but searches in the first row of a table.

3. INDEX-MATCH:
• Example: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• Usage: A more flexible alternative to VLOOKUP or HLOOKUP for lookups.

4. SUMIFS:
• Example: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Adds values based on multiple criteria.

5. COUNTIFS:
• Example: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Counts cells based on multiple criteria.

6. AVERAGEIFS:
• Example: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
• Usage: Calculates the average based on multiple criteria.

7. CONCATENATE:
• Example: =CONCATENATE(text1, [text2, ...]) or =text1 & [text2]
• Usage: Combines text from multiple cells into one cell.

8. IF:
• Example: =IF(logical_test, value_if_true, value_if_false)
• Usage: Performs conditional logic based on a specified condition.

9. PivotTables:
• Usage: Allows for dynamic data summarization and analysis in a table format.

10. SUM, AVERAGE, COUNT:
• Examples: =SUM(range), =AVERAGE(range), =COUNT(range)
• Usage: Basic functions for simple calculations on a range of cells.

Data Analytics Resources
👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Hope this helps you 😊

Hidden Job Market 2025
👇👇
https://datasimplifier.com/hidden-job-market-in-2025-2/

Excel Cheatsheet 💪

🫡 5 Youtubers that Taught me Better Than Any Professor..


1️⃣ JavaScript Mastery:

Helped me take my Web Development skills to the next level!

https://youtube.com/@javascriptmastery?si=XfHt7m4XWaaNrfvH

2️⃣ Data Analytics:

Helped me learn SQL & Data Analytics

https://youtube.com/@dataanalyticsrock?si=QF31jF8q0eV2Dmn5

3️⃣ Programming With Mosh:

Taught me half the Programming Language i Know..

https://youtube.com/@programmingwithmosh?si=4kWgkDc1_iF80Tup

4️⃣ 3Blue Brown:

Explains all the stupid hard CS Maths..

https://youtube.com/@3blue1brown?si=47b0_sMPjW4OaF14

5️⃣ Khan Academy:

Saved me in High School..

https://youtube.com/@khanacademy?si=3olbN2YuS-gkEUXP

Join Biggest Telegram channel for data analysts
👇👇
https://t.iss.one/sqlspecialist

Complete step-by-step syllabus of #Excel for Data Analytics

Introduction to Excel for Data Analytics:
Overview of Excel's capabilities for data analysis
Introduction to Excel's interface: ribbons, worksheets, cells, etc.
Differences between Excel desktop version and Excel Online (web version)

Data Import and Preparation:
Importing data from various sources: CSV, text files, databases, web queries, etc.
Data cleaning and manipulation techniques: sorting, filtering, removing duplicates, etc.
Data types and formatting in Excel
Data validation and error handling

Data Analysis Techniques in Excel:
Basic formulas and functions: SUM, AVERAGE, COUNT, IF, VLOOKUP, etc.
Advanced functions for data analysis: INDEX-MATCH, SUMIFS, COUNTIFS, etc.
PivotTables and PivotCharts for summarizing and analyzing data
Advanced data analysis tools: Goal Seek, Solver, What-If Analysis, etc.

Data Visualization in Excel:
Creating basic charts: column, bar, line, pie, scatter, etc.
Formatting and customizing charts for better visualization
Using sparklines for visualizing trends in data
Creating interactive dashboards with slicers and timelines

Advanced Data Analysis Features:
Data modeling with Excel Tables and Relationships
Using Power Query for data transformation and cleaning
Introduction to Power Pivot for data modeling and DAX calculations
Advanced charting techniques: combination charts, waterfall charts, etc.

Statistical Analysis in Excel:
Descriptive statistics: mean, median, mode, standard deviation, etc.
Hypothesis testing: t-tests, chi-square tests, ANOVA, etc.
Regression analysis and correlation
Forecasting techniques: moving averages, exponential smoothing, etc.

Data Visualization Tools in Excel:
Introduction to Excel add-ins for enhanced visualization (e.g., Power Map, Power View)
Creating interactive reports with Excel add-ins
Introduction to Excel Data Model for handling large datasets

Real-world Projects and Case Studies:
Analyzing real-world datasets
Solving business problems with Excel
Portfolio development showcasing Excel skills

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

Excel for Data Analysis:
5 Most Used Excel Functions by Data Analysts

🧵⬇️

1️⃣ VLOOKUP / XLOOKUP:

VLOOKUP is used to look up values in a table or range by row, making it useful for merging datasets or retrieving specific data.

XLOOKUP (newer and more versatile) allows searching both horizontally and vertically and supports approximate matches.

2️⃣ INDEX-MATCH:

The INDEX-MATCH combination is often preferred over VLOOKUP for more flexibility. INDEX retrieves a value from a specified cell range, while MATCH identifies its position. Together, they allow more complex lookups, especially when the lookup column isn’t the leftmost column.

3️⃣ SUMIF / SUMIFS:

SUMIF and SUMIFS allow summing values based on single or multiple conditions, making it easy to analyze specific segments of data, such as summing revenue by region or time period.

4️⃣ COUNTIF / COUNTIFS:

COUNTIF and COUNTIFS are similar to SUMIF but are used for counting cells that meet specific criteria. These functions are helpful for calculating frequencies, such as counting occurrences of a certain value in a dataset.

5️⃣ Pivot Tables:

Pivot Tables aren’t a function but are an essential Excel tool for data analysts. They enable quick summarization, aggregation, and exploration of large datasets, allowing analysts to generate insights without complex formulas.

Like for more ✅