MS Excel For Data Analyst Role

MS Excel Syllabus:

Data Management & Cleaning

- Removing Duplicates, Text to Columns, Data Validation, Flash Fill

Formula Mastery

- SUM, COUNT, AVERAGE, SUMIFS, COUNTIFS, AVERAGEIFS, VLOOKUP,

HLOOKUP, XLOOKUP, INDEX, MATCH, INDEX & MATCH, IF, IFERROR,
AND, OR, NOT, Nested Functions, ARRAY Formulas, LET, SUMPRODUCT,
INDIRECT, CHOOSE, OFFSET, LEFT, RIGHT

Data Analysis & Reporting

- Pivot Tables & Pivot Charts, Data Sorting and Filtering, Subtotals, Data Tables,
Scenarios (What-If Analysis), Goal Seek and Solver

Visualization Expertise

- Conditional Formatting, Basic to Advanced Charting, Creating Dynamic

Dashboards

Efficiency Enhancers

- Keyboard Shortcuts (You can get it from ChatGPT), Data Consolidation

Techniques, Error Checking

Advanced Excel Capabilities

- Advanced Filter, Slicers and Timelines in Pivot TableS

Start learning Excel with the YouTube playlist provided below -

- https://www.youtube.com/playlist?list=PLUaB-1hjhk8Hyd5NiPQ9CND82v
NodlFF5

Or you can follow these below specific videos -

Excel Essentials - https://youtu.be/VT479YDPB0I?si=YYK54aro5GvshsNy

Excel Formula Mastery - https://youtu.be/dVJFyqI_Nw4?si=34_-0wQNl8662g_i

Pivot tables Mastery - https://youtu.be/zuSNd1ZMfBI?si=pmlOUN4b0z4HTZdr

Learn Complete Excel while doing Projects -

https://www.youtube.com/watch?v=Vl0H-qTclOg&t=0s

NOTE: If you don't find a specific topic from the syllabus in the playlist above,
you can use any YouTube video or web article to understand the concept of that
Topic.

Websites for Practicing Excel:

1. https://www.excel-easy.com/

2. https://exceljet.net/

3. https://www.excelpracticeonline.com/

Complete These Excel Projects for learning Data Visualization & Analysis Skills
in Excel -

https://www.youtube.com/watch?v=m13o5aqeCbM&t=41s
https://www.youtube.com/watch?v=opJgMj1IUrc

MS Excel interview Questions–

Using above data set try to solve below mentioned Question in MS Excel -

Question 1:
Calculate the total profit for each product using the profit margin and sales for
2023.
Hint: Multiply Sales 2023 with the Profit Margin %.

Question 2:
Identify the salesperson who achieved the highest profit across all products in
2023.
Hint: Use a combination of MAX and lookup functions.
Question 3:
Rank the products based on their total sales in 2023.
Hint: Use a ranking function to calculate ranks dynamically.

Question 4:
Find the average profit margin for all products in each region.
Hint: Use a pivot table or grouping functions to calculate averages by region.

Question 5:
Determine the percentage of products that achieved their sales target in 2023.
Hint: Count the number of rows where Sales 2023 >= Target and calculate the
percentage.

Question 6:
Create a formula to flag products where the profit margin is below 10%.
Hint: Use an IF statement to display "Low Margin" for products with less than
10% profit margin.

Question 7:
Highlight rows where sales in 2023 increased by more than 20% compared to
2022.
Hint: Use Conditional Formatting with a formula comparing Sales 2022 and Sales
2023.

Question 8:
Find the product with the highest increase in sales from 2022 to 2023.
Hint: Calculate the difference between Sales 2023 and Sales 2022 and find the
maximum.
Question 9:
Calculate the weighted average profit margin based on total sales in 2023 for all
products.
Hint: Multiply sales and profit margin, then divide by total sales.

Question 10:
Filter the dataset to show only products where sales in 2023 exceeded both the
target and 2022 sales.
Hint: Apply multiple criteria using a filter.

Question 11:
Create a dynamic dropdown list of regions and filter sales data based on the
selected region.
Hint: Use Data Validation and FILTER functions.

Question 12:
Split the salesperson names into two columns: First Name and Last Name.
Hint: Use TEXTSPLIT or LEFT/RIGHT and FIND for splitting.

Question 13:
Generate a table showing cumulative sales for 2022 and 2023 for each region.
Hint: Use a SUMIFS function to calculate cumulative sales.

Question 14:
Find out which salesperson contributed the most to total sales in 2023.
Hint: Sum the sales for each salesperson and compare.
Question 15:
Determine the 2nd highest profit margin across all products.
Hint: Use the LARGE function to find the second-highest value.

Question 16:
Create a Pivot Table to show total sales for each salesperson by region and add a
calculated field for profit.
Hint: Use the Pivot Table tools to add calculated fields.

Question 17:
Analyze and display the sales trend for each salesperson using a chart.
Hint: Create a line chart showing sales over the years for each salesperson.

Question 18:
Group products based on sales ranges (e.g., <6000, 6000-7000, >7000) and count
the number of products in each group.
Hint: Use IF statements or Pivot Table grouping.

Question 19:
Forecast the sales for 2024 using the average yearly growth rate from 2022 to
2023.
Hint: Calculate the growth rate for each product and project it for 2024.

THANKS !!

Connect With Me:

YouTube:

https://youtube.com/@shakrashamim?si=ucGSJ3mkKv8Lk7MQ

Instagram:

https://www.instagram.com/shakra.shamim/?igshid=OTJlNzQ0NWM%3D

LinkedIn:

https://in.linkedin.com/in/shakra-shamim-8ab3a1233