Microsoft Excel Shortcuts A to Z, everyone should know:

CTRL + A ➡️ Select All

CTRL + B ➡️ Toggle BOLD (font)

CTRL + C ➡️ Copy

CTRL + D ➡️ Fill Down

CTRL + E ➡️ Flash Fill

CTRL + F ➡️ Find

CTRL + G ➡️ Go To

CTRL + H ➡️ Find and Replace

CTRL + I ➡️ Toggle Italic (font)

CTRL + J ➡️ Input line break (in Find and Replace)

CTRL + K ➡️ Insert Hyperlink

CTRL + L ➡️ Insert Excel Table

CTRL + M ➡️ Not Assigned

CTRL + N ➡️ New Workbook

CTRL + O ➡️ Open

CTRL + P ➡️ Print

CTRL + Q ➡️ Quick Analysis

CTRL + R ➡️ Fill Right

CTRL + S ➡️ Save

CTRL + T ➡️ Insert Excel Table

CTRL + U ➡️ Toggle underline (font)

CTRL + V ➡️ Paste (when something is cut/copied)

CTRL + W ➡️ Close the current workbook

CTRL + X ➡️ Cut

CTRL + Y ➡️ Redo (Repeat last action)

CTRL + Z ➡️ Undo

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

Some of #Excel interview questions tailored for both data analysts and business analysts:-

1) What are the basic functions of Microsoft Excel?
2) Explain the difference between a workbook and a worksheet.
3) How would you freeze panes in Excel?
4) Can you name some common keyboard shortcuts in Excel?
5) What is the purpose of VLOOKUP and HLOOKUP?
7) How do you remove duplicate values in Excel?
8) Explain the steps to filter data in Excel.
9) What is the significance of the "IF" function in Excel, and can you provide an example of its use?
10) How would you create a pivot table in Excel?
11) Explain the use of the CONCATENATE function in Excel.
12) How do you create a chart in Excel?
13) Explain the difference between a line chart and a scatter plot.
14) What is conditional formatting, and how can it be applied in Excel?
15) How would you create a dynamic chart that updates with new data?
16) What is the INDEX-MATCH function, and how is it different from VLOOKUP?
17) Can you explain the concept of "PivotTables" and when you would use them?
18) How do you use the "COUNTIF" and "SUMIF" functions in Excel?
19) Explain the purpose of the "What-If Analysis" tools in Excel.
20) What are array formulas, and can you provide an example of their use?

Business Analysis Specific:

1) How would you analyze a set of sales data to identify trends and insights?
2) Explain how you might use Excel to perform financial modeling.
3) What Excel features would you use for forecasting and budgeting?
4) How do you handle large datasets in Excel, and what tools or techniques do you use for optimization?
5) What are some common techniques for cleaning and validating data in Excel?
6) How do you identify and handle errors in a dataset using Excel?

Scenario-based Questions:

1) Imagine you have a dataset with missing values. How would you approach this problem in Excel?
2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?

Like for more

Free Resources: 👇
https://t.iss.one/excel_data

### Infosys MS Excel Interview Questions

1. Given a list of employee names and salaries, write a formula to find the second highest salary.
2. How would you use conditional formatting to highlight cells that contain dates within the last 30 days?
3. Describe how you would create a pivot table that shows the total sales for each product category by month.
4. Write a formula using VLOOKUP that handles errors gracefully if the lookup value is not found.
5. How can you use Excel’s data validation feature to restrict input in a cell to a specific range of dates?
6. Explain the steps to create a macro that automates the process of formatting a report.
7. Write a formula using the IF function that assigns a grade (A, B, C, etc.) based on a numeric score.
8. How would you use the INDEX and MATCH functions to look up a value in a table when the column position is not known?
9. Demonstrate how to use Excel’s Goal Seek feature to find the input value needed to achieve a specific result in a formula.
10. Explain how you would use the SUMIFS function to sum sales data based on multiple criteria, such as product type and sales region.

### TCS MS Excel Interview Questions

1. Create a dynamic named range that automatically updates as new data is added.
2. How would you create a pivot chart that updates automatically when new data is added to the source table?
3. Write a formula using COUNTIFS to count the number of orders that meet multiple conditions, such as order date and order status.
4. Explain how to use the Data Analysis Toolpak to perform a multiple regression analysis.
5. Demonstrate how to use the TEXT function to format numbers as currency with a specific number of decimal places.
6. Write a formula using the OFFSET function to return the value of a cell that is a specific number of rows and columns away from a reference cell.
7. Describe how to use the SUBTOTAL function to calculate the average of visible cells in a filtered list.
8. How would you use Solver to determine the optimal product mix to maximize profit given constraints on resources?
9. Write a formula using XLOOKUP that returns multiple columns of data for a given lookup value.

I have curated free Excel Books
👇👇
https://t.iss.one/excel_data

Excel Essential Formulas 👆

Python Roadmap

🧵 10 Basic Excel Formulas Everyone Needs to Know 👇

🔵 SUM =SUM(A1:A10) — Adds values.
🔵 AVERAGE =AVERAGE(A1:A10) — Finds average.
🔵 COUNT =COUNT(A1:A10) — Counts numbers.
🔵 COUNTA =COUNTA(A1:A10) — Counts non-empty cells.
🔵 IF =IF(A1>10, "Yes", "No") — Conditional result.
🔵 MIN =MIN(A1:A10) — Smallest value.
🔵 MAX =MAX(A1:A10) — Largest value.
🔵 VLOOKUP =VLOOKUP(B1, A1:D10, 2, FALSE) — Looks up value.
🔵 & =A1 & " " & B1 — Joins text.
🔵 LEN =LEN(A1) — Counts characters.

#ExcelTips

Excel Shortcuts

Excel Important Shortcut Keys 🗝️

Excel Cheatsheet

16 ways to make more money

🎓 Build Your Career In Data Analytics! 📊

🌟 2000+ Students Placed
💰 7.4 LPA Average Package
🚀 41 LPA Highest Package
🤝 500+ Hiring Partners

Registration link: https://tracking.acciojob.com/g/PUfdDxgHR

Limited Seats, Register Now! ✨

Do this to become an advanced excel user

• Auto Spell - F7
• Flash Fill - CTRL + E
• Pivot Table - Alt + N + V
• Autofit All Columns - Alt + H + O + I
• Conditional Formatting - Alt + H + L

These shortcuts will come in handy.

#excel

7 Lookup functions in Excel 🔍

A lookup function allows you to get an exact value from a range, based off of inputs.

This is one of the most popular function types in excel, and there are many different methods available…

Let’s explore the 7 most common:

1️⃣ =VLOOKUP

Everyone has heard of a VLOOKUP function…

and many are still using it…even though there are much better functions available

This works by taking a range of data, and finding your lookup value by analyzing a VERTICAL range

I’m not a fan of VLOOKUP because you can only search in one direction (vertically), and need to hardcode in the column index…but it’s still a popular one.

2️⃣ =HLOOKUP

This is pretty much the same as VLOOKUP, only you can now search HORIZONTALLY.

just like VLOOKUP, I feel that HLOOKUP is unnecessary, and limited in it’s functionality

3️⃣ =XLOOKUP

Over the last few years Microsoft released XLOOKUP - your solution to all of your woes with VLOOKUP and HLOOKUP.

Now, instead of being bound to just one direction, you can specify a range on both an X and Y axis to find your lookup value.

XLOOKUP is the new kid on the block and is highly encouraged to use instead of VLOOKUP and HLOOKUP…

just need to watch out for those who are still using an older version of Microsoft Excel, as you’ll run into a compatibility issue (since it’s a new function)

4️⃣ =GETPIVOTDATA

Many aren’t familiar with this one…but it’s super useful.

Here, you can specify the inputs to dynamically pull out your value from a Pivot Table.

If you’re unsure of how to use this, just point your = sign to a cell in a Pivot Table, and excel will automatically populate your formula

5️⃣ =INDEX

The INDEX function is one of my favorite…

and it’s really simple to understand.

Here you can point to a range with both an X and Y axis…

and simply input the coordinates for what value you want to find (ex: 3rd row, 2nd column)

6️⃣ =MATCH

The MATCH function is also a really good one…

it allows you to get the POSITION of a value in a range, as compared to the position of another value in a range.

I love using this one when getting the difference in dates from one period to another.

7️⃣ =INDEX/MATCH

OK…here’s my favorite method for looking up a value.

It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs.

Many feel that XLOOKUP is superior, and I won’t fight it - it’s all a matter of personal preference!

===

Whatever function you use for looking up a value, keep in mind the following:

→ use a function that allows you to search both an X and Y axis, instead of just one direction
→ set things up so that your inputs are dynamic, and can change, yielding different outputs

→ keep your syntax clean & easy to understand

Those are my tips for lookup functions, and 7 ones to use.

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_data

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

Excel Formulas ✅

Microsoft Excel Formulas ☝️

Why is Excel Often the Starting Point for SQL ?


Here's how Excel can help you before you dive into SQL:

✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.

✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.

✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.

✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.

✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.

✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.

Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!

You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!

Share our channel link with your true friends: https://t.iss.one/excel_analyst

Hope this helps you 😊