โ
Data Analyst Mistakes Beginners Should Avoid โ ๏ธ๐
1๏ธโฃ Ignoring Data Cleaning
โข Jumping to charts too soon
โข Overlooking missing or incorrect data
โ Clean before you analyze โ always
2๏ธโฃ Not Practicing SQL Enough
โข Stuck on simple joins or filters
โข Canโt handle large datasets
โ Practice SQL daily โ it's your #1 tool
3๏ธโฃ Overusing Excel Only
โข Limited automation
โข Hard to scale with large data
โ Learn Python or SQL for bigger tasks
4๏ธโฃ No Real-World Projects
โข Watching tutorials only
โข Resume has no proof of skills
โ Analyze real datasets and publish your work
5๏ธโฃ Ignoring Business Context
โข Insights without meaning
โข Metrics without impact
โ Understand the why behind the data
6๏ธโฃ Weak Data Visualization Skills
โข Crowded charts
โข Wrong chart types
โ Use clean, simple, and clear visuals (Power BI, Tableau, etc.)
7๏ธโฃ Not Tracking Metrics Over Time
โข Only point-in-time analysis
โข No trends or comparisons
โ Use time-based metrics for better insight
8๏ธโฃ Avoiding Git & Version Control
โข No backup
โข Difficult collaboration
โ Learn Git to track and share your work
9๏ธโฃ No Communication Focus
โข Great analysis, poorly explained
โ Practice writing insights clearly & presenting dashboards
๐ Ignoring Data Privacy
โข Sharing raw data carelessly
โ Always anonymize and protect sensitive info
๐ก Master tools + think like a problem solver โ that's how analysts grow fast.
๐ฌ Tap โค๏ธ for more!
1๏ธโฃ Ignoring Data Cleaning
โข Jumping to charts too soon
โข Overlooking missing or incorrect data
โ Clean before you analyze โ always
2๏ธโฃ Not Practicing SQL Enough
โข Stuck on simple joins or filters
โข Canโt handle large datasets
โ Practice SQL daily โ it's your #1 tool
3๏ธโฃ Overusing Excel Only
โข Limited automation
โข Hard to scale with large data
โ Learn Python or SQL for bigger tasks
4๏ธโฃ No Real-World Projects
โข Watching tutorials only
โข Resume has no proof of skills
โ Analyze real datasets and publish your work
5๏ธโฃ Ignoring Business Context
โข Insights without meaning
โข Metrics without impact
โ Understand the why behind the data
6๏ธโฃ Weak Data Visualization Skills
โข Crowded charts
โข Wrong chart types
โ Use clean, simple, and clear visuals (Power BI, Tableau, etc.)
7๏ธโฃ Not Tracking Metrics Over Time
โข Only point-in-time analysis
โข No trends or comparisons
โ Use time-based metrics for better insight
8๏ธโฃ Avoiding Git & Version Control
โข No backup
โข Difficult collaboration
โ Learn Git to track and share your work
9๏ธโฃ No Communication Focus
โข Great analysis, poorly explained
โ Practice writing insights clearly & presenting dashboards
๐ Ignoring Data Privacy
โข Sharing raw data carelessly
โ Always anonymize and protect sensitive info
๐ก Master tools + think like a problem solver โ that's how analysts grow fast.
๐ฌ Tap โค๏ธ for more!
โค1๐ฅฐ1๐คฉ1๐1
๐ Top 100 Excel Interview Questions
๐ง Excel Basics & UI
1. What is Microsoft Excel and what is it mainly used for?
2. What is the difference between a workbook and a worksheet?
3. What is a cell, row, and column?
4. How do you rename, insert, or delete a worksheet?
5. How do you select a range, entire row, or entire column?
6. How do you copy, paste, and cut data?
7. How do you use Zoom, Freeze Panes, and Split Window?
8. How do you hide/unhide rows and columns?
9. How do you insert/delete rows and columns without breaking formulas?
10. How do you save, open, and share an Excel file (including via OneDrive/SharePoint)?
๐ Formatting & Data Entry
11. How do you format numbers as currency, percentage, or date?
12. How do you wrap text and merge cells?
13. How do you resize columns and rows to fit content?
14. How do you apply bold, italic, borders, and background colors?
15. How do you AutoFit column width and row height?
16. How do you print and set page layout (margins, gridlines, headers/footers)?
17. How do you protect a sheet or workbook with a password?
18. How do you use โFormat as Tableโ and why is it useful?
19. How do you use conditional formatting (highlight cells, data bars, color scales, icons)?
20. How do you apply custom number formats?
๐งฎ Formulas & Functions (Beginner)
21. How do you write a basic arithmetic formula starting with =?
22. What are relative, absolute, and mixed references (A1, $A$1, A$1)?
23. How do you use SUM, AVERAGE, MIN, MAX, COUNT, COUNTA?
24. How do you calculate percentage and percentage change?
25. How do you use ROUND, ROUNDUP, and ROUNDDOWN?
26. How do you calculate current date and time (TODAY, NOW)?
27. How do you calculate the difference between two dates?
28. How do you use IF for simple logical conditions?
29. How do you handle errors using IFERROR or IFNA?
30. How do you nest multiple IF statements?
๐ Lookups, Logic & Math
31. How do you use VLOOKUP and what are its limitations?
32. How do you use HLOOKUP?
33. How do you use INDEX + MATCH for flexible lookups?
34. How do you use XLOOKUP (if available)?
35. How do you use IF with AND / OR conditions?
36. How do you use SUMIF, SUMIFS for conditional sums?
37. How do you use COUNTIF, COUNTIFS for conditional counts?
38. How do you use AVERAGEIF, AVERAGEIFS?
39. How do you use COUNTBLANK, COUNTUNIQUEโstyle techniques?
40. How do you use SUMPRODUCT for weighted calculations?
๐๏ธ Date, Time & Text Functions
41. How do you extract YEAR, MONTH, DAY from a date?
42. How do you calculate days between two dates?
43. How do you calculate workdays between two dates (NETWORKDAYS)?
44. How do you use DATEDIF for years/months/days?
45. How do you use TRIM to remove extra spaces?
46. How do you join text from multiple cells (CONCAT, &, CONCATENATE)?
47. How do you split text with TEXTSPLIT or LEFT/RIGHT/MID?
48. How do you find text inside a string (FIND, SEARCH)?
49. How do you replace or remove part of a string (SUBSTITUTE, REPLACE)?
50. How do you change case (UPPER, LOWER, PROPER)?
๐ Data Analysis & Conditional Formatting
51. How do you sort data by one or multiple columns?
52. How do you use AutoFilter and custom filters?
53. How do you filter by blanks, nonโblanks, or specific values?
54. How do you remove duplicates from a list?
55. How do you use conditional formatting with formulas?
56. How do you highlight top/bottom N values?
57. How do you standardize and clean messy data (e.g., โYes / yes / Yโ โ โYesโ)?
58. How do you use TextโtoโColumns and Flash Fill for quick cleaning?
59. How do you create subtotals and grouped views?
60. How do you build a simple KPI or summary table?
๐ง Excel Basics & UI
1. What is Microsoft Excel and what is it mainly used for?
2. What is the difference between a workbook and a worksheet?
3. What is a cell, row, and column?
4. How do you rename, insert, or delete a worksheet?
5. How do you select a range, entire row, or entire column?
6. How do you copy, paste, and cut data?
7. How do you use Zoom, Freeze Panes, and Split Window?
8. How do you hide/unhide rows and columns?
9. How do you insert/delete rows and columns without breaking formulas?
10. How do you save, open, and share an Excel file (including via OneDrive/SharePoint)?
๐ Formatting & Data Entry
11. How do you format numbers as currency, percentage, or date?
12. How do you wrap text and merge cells?
13. How do you resize columns and rows to fit content?
14. How do you apply bold, italic, borders, and background colors?
15. How do you AutoFit column width and row height?
16. How do you print and set page layout (margins, gridlines, headers/footers)?
17. How do you protect a sheet or workbook with a password?
18. How do you use โFormat as Tableโ and why is it useful?
19. How do you use conditional formatting (highlight cells, data bars, color scales, icons)?
20. How do you apply custom number formats?
๐งฎ Formulas & Functions (Beginner)
21. How do you write a basic arithmetic formula starting with =?
22. What are relative, absolute, and mixed references (A1, $A$1, A$1)?
23. How do you use SUM, AVERAGE, MIN, MAX, COUNT, COUNTA?
24. How do you calculate percentage and percentage change?
25. How do you use ROUND, ROUNDUP, and ROUNDDOWN?
26. How do you calculate current date and time (TODAY, NOW)?
27. How do you calculate the difference between two dates?
28. How do you use IF for simple logical conditions?
29. How do you handle errors using IFERROR or IFNA?
30. How do you nest multiple IF statements?
๐ Lookups, Logic & Math
31. How do you use VLOOKUP and what are its limitations?
32. How do you use HLOOKUP?
33. How do you use INDEX + MATCH for flexible lookups?
34. How do you use XLOOKUP (if available)?
35. How do you use IF with AND / OR conditions?
36. How do you use SUMIF, SUMIFS for conditional sums?
37. How do you use COUNTIF, COUNTIFS for conditional counts?
38. How do you use AVERAGEIF, AVERAGEIFS?
39. How do you use COUNTBLANK, COUNTUNIQUEโstyle techniques?
40. How do you use SUMPRODUCT for weighted calculations?
๐๏ธ Date, Time & Text Functions
41. How do you extract YEAR, MONTH, DAY from a date?
42. How do you calculate days between two dates?
43. How do you calculate workdays between two dates (NETWORKDAYS)?
44. How do you use DATEDIF for years/months/days?
45. How do you use TRIM to remove extra spaces?
46. How do you join text from multiple cells (CONCAT, &, CONCATENATE)?
47. How do you split text with TEXTSPLIT or LEFT/RIGHT/MID?
48. How do you find text inside a string (FIND, SEARCH)?
49. How do you replace or remove part of a string (SUBSTITUTE, REPLACE)?
50. How do you change case (UPPER, LOWER, PROPER)?
๐ Data Analysis & Conditional Formatting
51. How do you sort data by one or multiple columns?
52. How do you use AutoFilter and custom filters?
53. How do you filter by blanks, nonโblanks, or specific values?
54. How do you remove duplicates from a list?
55. How do you use conditional formatting with formulas?
56. How do you highlight top/bottom N values?
57. How do you standardize and clean messy data (e.g., โYes / yes / Yโ โ โYesโ)?
58. How do you use TextโtoโColumns and Flash Fill for quick cleaning?
59. How do you create subtotals and grouped views?
60. How do you build a simple KPI or summary table?
โค1๐ฅ1๐คฉ1
๐๏ธ PivotTables, Charts & Dashboards
61. What is a PivotTable and when do you use it?
62. How do you create a PivotTable from a flat table?
63. How do you group by month, quarter, or year in a PivotTable?
64. How do you add โCalculated Fieldsโ and โCalculated Itemsโ?
65. How do you create a PivotChart?
66. How do you use slicers and timelines to filter PivotTables?
67. How do you โrefreshโ a PivotTable when data changes?
68. How do you design a dashboard using tables, charts, and PivotTables?
69. How do you format axes, legends, and data labels?
70. How do you create dynamic charts that update with new data?
๐ Advanced Excel (PowerโUser / Analyst Level)
71. How do you use FILTER, SORT, and UNIQUE (if you have dynamic arrays)?
72. How do you use SUMIFS / COUNTIFS with multiple criteria?
73. How do you use SUMPRODUCT for 2Dโstyle lookups and weighted averages?
74. How do you build reusable templates with named ranges and formulas?
75. How do you link multiple sheets and workbooks?
76. How do you avoid and fix circular references?
77. How do you optimize large Excel files for performance?
78. How do you use Data Validation to create dropdown lists?
79. How do you use โWhatโIf Analysisโ (Goal Seek, Data Tables, Scenario Manager)?
80. How do you connect Excel to external data (CSV, text, web, or databases)?
๐ค Power Query / Automation & VBAโLite
81. What is Power Query and how do you use it for data cleaning?
82. How do you import and transform data (filter, group, pivot, unpivot) using Power Query?
83. How do you refresh a query that pulls from a source file?
84. How do you load cleaned data into a PivotTable or worksheet?
85. What is Power Pivot and DAX (highโlevel overview)?
86. How do you use tables and structured references in formulas?
87. How do you protect and share an Excel file for multiple users?
88. How do you create dynamic dashboards with slicers and linked PivotTables?
89. How do you export Excel reports to PDF or CSV?
90. How do you automate repetitive tasks (e.g., formatting, validation, data loading)?
๐ง ScenarioโBased / Behavioral (Excel for Work)
91. How would you build a monthly sales report from raw transaction data?
92. How would you design a budget or expense tracker?
93. How would you analyze customer retention or RFMโstyle data?
94. How would you create an attendance or performance tracking sheet?
95. How would you handle a messy Excel file shared by your team?
96. How do you ensure your formulas and tables are easy to understand and maintain?
97. How do you explain Excel insights to a nonโtechnical person?
98. How do you share and protect an Excel file for multiple users?
99. How do you validate your analysis before sharing it with stakeholders?
100. What are your top 5 Excel shortcuts and which ones do you use daily?
๐ Double Tap โค๏ธ For Detailed Answers!
61. What is a PivotTable and when do you use it?
62. How do you create a PivotTable from a flat table?
63. How do you group by month, quarter, or year in a PivotTable?
64. How do you add โCalculated Fieldsโ and โCalculated Itemsโ?
65. How do you create a PivotChart?
66. How do you use slicers and timelines to filter PivotTables?
67. How do you โrefreshโ a PivotTable when data changes?
68. How do you design a dashboard using tables, charts, and PivotTables?
69. How do you format axes, legends, and data labels?
70. How do you create dynamic charts that update with new data?
๐ Advanced Excel (PowerโUser / Analyst Level)
71. How do you use FILTER, SORT, and UNIQUE (if you have dynamic arrays)?
72. How do you use SUMIFS / COUNTIFS with multiple criteria?
73. How do you use SUMPRODUCT for 2Dโstyle lookups and weighted averages?
74. How do you build reusable templates with named ranges and formulas?
75. How do you link multiple sheets and workbooks?
76. How do you avoid and fix circular references?
77. How do you optimize large Excel files for performance?
78. How do you use Data Validation to create dropdown lists?
79. How do you use โWhatโIf Analysisโ (Goal Seek, Data Tables, Scenario Manager)?
80. How do you connect Excel to external data (CSV, text, web, or databases)?
๐ค Power Query / Automation & VBAโLite
81. What is Power Query and how do you use it for data cleaning?
82. How do you import and transform data (filter, group, pivot, unpivot) using Power Query?
83. How do you refresh a query that pulls from a source file?
84. How do you load cleaned data into a PivotTable or worksheet?
85. What is Power Pivot and DAX (highโlevel overview)?
86. How do you use tables and structured references in formulas?
87. How do you protect and share an Excel file for multiple users?
88. How do you create dynamic dashboards with slicers and linked PivotTables?
89. How do you export Excel reports to PDF or CSV?
90. How do you automate repetitive tasks (e.g., formatting, validation, data loading)?
๐ง ScenarioโBased / Behavioral (Excel for Work)
91. How would you build a monthly sales report from raw transaction data?
92. How would you design a budget or expense tracker?
93. How would you analyze customer retention or RFMโstyle data?
94. How would you create an attendance or performance tracking sheet?
95. How would you handle a messy Excel file shared by your team?
96. How do you ensure your formulas and tables are easy to understand and maintain?
97. How do you explain Excel insights to a nonโtechnical person?
98. How do you share and protect an Excel file for multiple users?
99. How do you validate your analysis before sharing it with stakeholders?
100. What are your top 5 Excel shortcuts and which ones do you use daily?
๐ Double Tap โค๏ธ For Detailed Answers!
โค1๐ฅ1๐1
Excel Basics for Data Analytics
Excel sits at the start of most analysis work.
What you use Excel for
โข Cleaning raw data
โข Exploring patterns
โข Quick summaries for teams
Core concepts you must know
โข Data setup
โ Freeze header row. View โ Freeze Top Row.
โ Convert range to table. Ctrl + T.
โ Use proper headers. No merged cells. One value per cell.
โข Data cleaning
โ Remove duplicates. Data โ Remove Duplicates.
โ Trim extra spaces. =TRIM(A2)
โ Convert text to numbers. =VALUE(A2)
โ Fix date format. Format Cells โ Date.
โ Handle blanks. Filter blanks, fill or delete.
โ Find and replace. Ctrl + H.
โข Essential formulas
โ Math and counts
โช SUM. =SUM(A2:A100)
โช AVERAGE. =AVERAGE(A2:A100)
โช MIN. =MIN(A2:A100)
โช MAX. =MAX(A2:A100)
โช COUNT. Counts numbers.
โช COUNTA. Counts non blanks.
โช COUNTBLANK. Counts blanks.
โ Conditional formulas
โช IF. =IF(A2>5000,"High","Low")
โช IFS. Multiple conditions.
โช AND. =AND(A2>5000,B2="West")
โช OR. =OR(A2>5000,A2<1000)
โ Lookup formulas
โช XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)
โช VLOOKUP. Old but common.
โช INDEX + MATCH. Powerful alternative.
โ Text formulas
โช LEFT. =LEFT(A2,4)
โช RIGHT. =RIGHT(A2,2)
โช MID. =MID(A2,2,3)
โช LEN. =LEN(A2)
โช CONCAT or TEXTJOIN.
โช LOWER, UPPER, PROPER.
โ Date formulas
โช TODAY. Current date.
โช NOW. Date and time.
โช YEAR, MONTH, DAY.
โช DATEDIF. Date difference.
โช EOMONTH. Month end.
โข Sorting and filtering
โ Sort by multiple columns.
โ Filter by value, color, condition.
โ Top 10 filter for quick insights.
โข Conditional formatting
โ Highlight duplicates.
โ Color scales for trends.
โ Rules for thresholds. Example. Sales > 10000 in green.
โข Pivot tables
โ Insert โ PivotTable.
โ Rows. Category or Product.
โ Values. Sum, Count, Average.
โ Filters. Date, Region.
โ Refresh after data update.
โข Charts you must know
โ Column. Comparison.
โ Bar. Ranking.
โ Line. Trends over time.
โ Pie. Share or percentage.
โ Combo. Actual vs target.
โข Data validation
โ Dropdown list. Data โ Data Validation โ List.
โ Prevent wrong entries.
โข Useful shortcuts
โ Ctrl + Arrow. Jump data.
โ Ctrl + Shift + Arrow. Select range.
โ Ctrl + 1. Format cells.
โ Ctrl + L. Apply filter.
โ Alt + =. Auto sum.
โ Ctrl + Z / Y. Undo redo.
โข Common analyst mistakes to avoid
โ Merged cells.
โ Hard coded totals.
โ Mixed data types in one column.
โ No backup before cleaning.
โข Daily practice task
โ Download any sales CSV.
โ Clean it.
โ Build one pivot table.
โ Create one chart.
Excel sits at the start of most analysis work.
What you use Excel for
โข Cleaning raw data
โข Exploring patterns
โข Quick summaries for teams
Core concepts you must know
โข Data setup
โ Freeze header row. View โ Freeze Top Row.
โ Convert range to table. Ctrl + T.
โ Use proper headers. No merged cells. One value per cell.
โข Data cleaning
โ Remove duplicates. Data โ Remove Duplicates.
โ Trim extra spaces. =TRIM(A2)
โ Convert text to numbers. =VALUE(A2)
โ Fix date format. Format Cells โ Date.
โ Handle blanks. Filter blanks, fill or delete.
โ Find and replace. Ctrl + H.
โข Essential formulas
โ Math and counts
โช SUM. =SUM(A2:A100)
โช AVERAGE. =AVERAGE(A2:A100)
โช MIN. =MIN(A2:A100)
โช MAX. =MAX(A2:A100)
โช COUNT. Counts numbers.
โช COUNTA. Counts non blanks.
โช COUNTBLANK. Counts blanks.
โ Conditional formulas
โช IF. =IF(A2>5000,"High","Low")
โช IFS. Multiple conditions.
โช AND. =AND(A2>5000,B2="West")
โช OR. =OR(A2>5000,A2<1000)
โ Lookup formulas
โช XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)
โช VLOOKUP. Old but common.
โช INDEX + MATCH. Powerful alternative.
โ Text formulas
โช LEFT. =LEFT(A2,4)
โช RIGHT. =RIGHT(A2,2)
โช MID. =MID(A2,2,3)
โช LEN. =LEN(A2)
โช CONCAT or TEXTJOIN.
โช LOWER, UPPER, PROPER.
โ Date formulas
โช TODAY. Current date.
โช NOW. Date and time.
โช YEAR, MONTH, DAY.
โช DATEDIF. Date difference.
โช EOMONTH. Month end.
โข Sorting and filtering
โ Sort by multiple columns.
โ Filter by value, color, condition.
โ Top 10 filter for quick insights.
โข Conditional formatting
โ Highlight duplicates.
โ Color scales for trends.
โ Rules for thresholds. Example. Sales > 10000 in green.
โข Pivot tables
โ Insert โ PivotTable.
โ Rows. Category or Product.
โ Values. Sum, Count, Average.
โ Filters. Date, Region.
โ Refresh after data update.
โข Charts you must know
โ Column. Comparison.
โ Bar. Ranking.
โ Line. Trends over time.
โ Pie. Share or percentage.
โ Combo. Actual vs target.
โข Data validation
โ Dropdown list. Data โ Data Validation โ List.
โ Prevent wrong entries.
โข Useful shortcuts
โ Ctrl + Arrow. Jump data.
โ Ctrl + Shift + Arrow. Select range.
โ Ctrl + 1. Format cells.
โ Ctrl + L. Apply filter.
โ Alt + =. Auto sum.
โ Ctrl + Z / Y. Undo redo.
โข Common analyst mistakes to avoid
โ Merged cells.
โ Hard coded totals.
โ Mixed data types in one column.
โ No backup before cleaning.
โข Daily practice task
โ Download any sales CSV.
โ Clean it.
โ Build one pivot table.
โ Create one chart.
๐2โค1โก1๐คฉ1๐1
โ
*Excel Shortcuts A-Z* โจ๏ธ๐
*A* โ *Alt + =* โ AutoSum selected cells
*B* โ *Ctrl + B* โ Bold selected text
*C* โ *Ctrl + C* โ Copy selected cells
*D* โ *Ctrl + D* โ Fill down from above cell
*E* โ *F2* โ Edit active cell
*F* โ *Ctrl + F* โ Find in worksheet
*G* โ *Ctrl + G* โ Go to a specific cell
*H* โ *Ctrl + H* โ Replace
*I* โ *Ctrl + I* โ Italicize selected text
*J* โ *Alt + Enter* โ Insert new line in cell
*K* โ *Ctrl + K* โ Insert hyperlink
*L* โ *Ctrl + L* โ Create table
*M* โ *Alt + M* โ Formulas tab shortcut
*N* โ *Ctrl + N* โ New workbook
*O* โ *Ctrl + O* โ Open workbook
*P* โ *Ctrl + P* โ Print
*Q* โ *Alt + Q* โ โTell meโ/search box
*R* โ *Ctrl + R* โ Fill right from left cell
*S* โ *Ctrl + S* โ Save workbook
*T* โ *Ctrl + T* โ Create table from selected data
*U* โ *Ctrl + U* โ Underline
*V* โ *Ctrl + V* โ Paste
*W* โ *Ctrl + W* โ Close workbook
*X* โ *Ctrl + X* โ Cut
*Y* โ *Ctrl + Y* โ Redo last action
*Z* โ *Ctrl + Z* โ Undo last action
๐ฌ *Tap โค๏ธ for more shortcuts!*
*A* โ *Alt + =* โ AutoSum selected cells
*B* โ *Ctrl + B* โ Bold selected text
*C* โ *Ctrl + C* โ Copy selected cells
*D* โ *Ctrl + D* โ Fill down from above cell
*E* โ *F2* โ Edit active cell
*F* โ *Ctrl + F* โ Find in worksheet
*G* โ *Ctrl + G* โ Go to a specific cell
*H* โ *Ctrl + H* โ Replace
*I* โ *Ctrl + I* โ Italicize selected text
*J* โ *Alt + Enter* โ Insert new line in cell
*K* โ *Ctrl + K* โ Insert hyperlink
*L* โ *Ctrl + L* โ Create table
*M* โ *Alt + M* โ Formulas tab shortcut
*N* โ *Ctrl + N* โ New workbook
*O* โ *Ctrl + O* โ Open workbook
*P* โ *Ctrl + P* โ Print
*Q* โ *Alt + Q* โ โTell meโ/search box
*R* โ *Ctrl + R* โ Fill right from left cell
*S* โ *Ctrl + S* โ Save workbook
*T* โ *Ctrl + T* โ Create table from selected data
*U* โ *Ctrl + U* โ Underline
*V* โ *Ctrl + V* โ Paste
*W* โ *Ctrl + W* โ Close workbook
*X* โ *Ctrl + X* โ Cut
*Y* โ *Ctrl + Y* โ Redo last action
*Z* โ *Ctrl + Z* โ Undo last action
๐ฌ *Tap โค๏ธ for more shortcuts!*
โค1๐ฅฐ1๐1
โ
Data Analyst Interview Questions for Freshers ๐
1) What is the role of a data analyst?
Answer: A data analyst collects, processes, and performs statistical analyses on data to provide actionable insights that support business decision-making.
2) What are the key skills required for a data analyst?
Answer: Strong skills in SQL, Excel, data visualization tools (like Tableau or Power BI), statistical analysis, and problem-solving abilities are essential.
3) What is data cleaning?
Answer: Data cleaning involves identifying and correcting inaccuracies, inconsistencies, or missing values in datasets to improve data quality.
4) What is the difference between structured and unstructured data?
Answer: Structured data is organized in rows and columns (e.g., spreadsheets), while unstructured data includes formats like text, images, and videos that lack a predefined structure.
5) What is a KPI?
Answer: KPI stands for Key Performance Indicator, which is a measurable value that demonstrates how effectively a company is achieving its business goals.
6) What tools do you use for data analysis?
Answer: Common tools include Excel, SQL, Python (with libraries like Pandas), R, Tableau, and Power BI.
7) Why is data visualization important?
Answer: Data visualization helps translate complex data into understandable charts and graphs, making it easier for stakeholders to grasp insights and trends.
8) What is a pivot table?
Answer: A pivot table is a feature in Excel that allows you to summarize, analyze, and explore data by reorganizing and grouping it dynamically.
9) What is correlation?
Answer: Correlation measures the statistical relationship between two variables, indicating whether they move together and how strongly.
10) What is a data warehouse?
Answer: A data warehouse is a centralized repository that consolidates data from multiple sources, optimized for querying and analysis.
11) Explain the difference between INNER JOIN and OUTER JOIN in SQL.
Answer: INNER JOIN returns only the matching rows between two tables, while OUTER JOIN returns all matching rows plus unmatched rows from one or both tables, depending on whether itโs LEFT, RIGHT, or FULL OUTER JOIN.
12) What is hypothesis testing?
Answer: Hypothesis testing is a statistical method used to determine if there is enough evidence in a sample to infer that a certain condition holds true for the entire population.
13) What is the difference between mean, median, and mode?
Answer:
โฆ Mean: The average of all numbers.
โฆ Median: The middle value when data is sorted.
โฆ Mode: The most frequently occurring value in a dataset.
14) What is data normalization?
Answer: Normalization is the process of organizing data to reduce redundancy and improve integrity, often by dividing data into related tables.
15) How do you handle missing data?
Answer: Missing data can be handled by removing rows, imputing values (mean, median, mode), or using algorithms that support missing data.
๐ฌ React โค๏ธ for more!
1) What is the role of a data analyst?
Answer: A data analyst collects, processes, and performs statistical analyses on data to provide actionable insights that support business decision-making.
2) What are the key skills required for a data analyst?
Answer: Strong skills in SQL, Excel, data visualization tools (like Tableau or Power BI), statistical analysis, and problem-solving abilities are essential.
3) What is data cleaning?
Answer: Data cleaning involves identifying and correcting inaccuracies, inconsistencies, or missing values in datasets to improve data quality.
4) What is the difference between structured and unstructured data?
Answer: Structured data is organized in rows and columns (e.g., spreadsheets), while unstructured data includes formats like text, images, and videos that lack a predefined structure.
5) What is a KPI?
Answer: KPI stands for Key Performance Indicator, which is a measurable value that demonstrates how effectively a company is achieving its business goals.
6) What tools do you use for data analysis?
Answer: Common tools include Excel, SQL, Python (with libraries like Pandas), R, Tableau, and Power BI.
7) Why is data visualization important?
Answer: Data visualization helps translate complex data into understandable charts and graphs, making it easier for stakeholders to grasp insights and trends.
8) What is a pivot table?
Answer: A pivot table is a feature in Excel that allows you to summarize, analyze, and explore data by reorganizing and grouping it dynamically.
9) What is correlation?
Answer: Correlation measures the statistical relationship between two variables, indicating whether they move together and how strongly.
10) What is a data warehouse?
Answer: A data warehouse is a centralized repository that consolidates data from multiple sources, optimized for querying and analysis.
11) Explain the difference between INNER JOIN and OUTER JOIN in SQL.
Answer: INNER JOIN returns only the matching rows between two tables, while OUTER JOIN returns all matching rows plus unmatched rows from one or both tables, depending on whether itโs LEFT, RIGHT, or FULL OUTER JOIN.
12) What is hypothesis testing?
Answer: Hypothesis testing is a statistical method used to determine if there is enough evidence in a sample to infer that a certain condition holds true for the entire population.
13) What is the difference between mean, median, and mode?
Answer:
โฆ Mean: The average of all numbers.
โฆ Median: The middle value when data is sorted.
โฆ Mode: The most frequently occurring value in a dataset.
14) What is data normalization?
Answer: Normalization is the process of organizing data to reduce redundancy and improve integrity, often by dividing data into related tables.
15) How do you handle missing data?
Answer: Missing data can be handled by removing rows, imputing values (mean, median, mode), or using algorithms that support missing data.
๐ฌ React โค๏ธ for more!
โค1๐ฅ1๐คฉ1๐1
๐ Excel Interview Questions with Answers โ Part 1
1. What is Microsoft Excel and what is it mainly used for?
Microsoft Excel is a spreadsheet application used to store, organize, analyze, and visualize data. It is part of Microsoft 365.
Main Uses of Excel:
- Data entry and management
- Calculations using formulas and functions
- Data analysis and reporting
- Creating charts and dashboards
- Budgeting and financial analysis
- Automation using macros and VBA
๐ Example:
A data analyst may use Excel to analyze sales data and create monthly KPI dashboards.
2. What is the difference between a workbook and a worksheet?
Workbook | Worksheet
A workbook is the entire Excel file | A worksheet is a single sheet/tab inside the workbook
It can contain multiple worksheets | It contains rows and columns of data
Saved as .xlsx, .xls, etc. | Appears as tabs at the bottom
๐ Example:
Sales_Report.xlsx = Workbook
January Sales = Worksheet inside the workbook
3. What is a cell, row, and column?
Cell: Intersection of a row and column
Example: B5
Row: Horizontal arrangement of data
Rows are numbered: 1, 2, 3...
Column: Vertical arrangement of data
Columns are labeled: A, B, C...
๐ Example:
If โSalesโ is written in cell C2, then:
- C = Column
- 2 = Row
- C2 = Cell
4. How do you rename, insert, or delete a worksheet?
Rename a Worksheet:
- Double-click the sheet tab
OR
- Right-click โ Rename
Insert a Worksheet:
- Click the + icon beside sheet tabs
OR
- Press Shift + F11
Delete a Worksheet:
- Right-click sheet tab โ Delete
โ ๏ธ Important:
Deleting a worksheet permanently removes its data unless recovered immediately.
5. How do you select a range, entire row, or entire column?
Select a Range:
Click and drag across cells
Example: A1:D10
Select Entire Row:
- Click the row number
OR
- Shortcut: Shift + Space
Select Entire Column:
- Click the column letter
OR
- Shortcut: Ctrl + Space
๐ Useful for formatting, filtering, or applying formulas quickly.
6. How do you copy, paste, and cut data?
Action | Shortcut
Copy | Ctrl + C
Paste | Ctrl + V
Cut | Ctrl + X
Paste Special:
Used when you want to paste:
- Values only
- Formulas only
- Formatting only
Shortcut: Ctrl + Alt + V
๐ Example:
Copy formulas without changing formatting using โPaste Special โ Formulasโ.
7. How do you use Zoom, Freeze Panes, and Split Window?
Zoom:
Used to increase or decrease worksheet view size.
- Bottom-right zoom slider
OR
- View โ Zoom
Freeze Panes:
Keeps headers visible while scrolling.
Path: View โ Freeze Panes
Common options:
- Freeze Top Row
- Freeze First Column
๐ Example:
Freeze headers in large sales reports.
Split Window:
Splits worksheet into multiple scrollable sections.
Path: View โ Split
Useful when comparing distant parts of the same sheet.
8. How do you hide/unhide rows and columns?
Hide:
- Select row/column
- Right-click โ Hide
Unhide:
- Select surrounding rows/columns
- Right-click โ Unhide
๐ Example:
Hide helper columns containing intermediate calculations.
9. How do you insert/delete rows and columns without breaking formulas?
Best Practice:
Use Excel insert/delete options instead of manual copy-paste.
Insert:
Right-click row/column โ Insert
Delete:
Right-click row/column โ Delete
Why?
Excel automatically adjusts formulas and references.
๐ Example:
If formula is: =SUM(A1:A5)
After inserting a new row inside the range, Excel updates automatically: =SUM(A1:A6)
โ ๏ธ Avoid deleting cells individually unless necessary because it may shift references incorrectly.
10. How do you save, open, and share an Excel file (including via OneDrive / Microsoft SharePoint)?
Save a File:
- Ctrl + S
- File โ Save As
Open a File:
- File โ Open
OR
- Double-click the Excel file
Share via OneDrive:
1. Save file to OneDrive
2. Click Share
3. Generate link or invite users
Share via Microsoft SharePoint:
- Upload workbook to SharePoint
- Collaborate with multiple users in real time
Double Tap โค๏ธ For Part-2
1. What is Microsoft Excel and what is it mainly used for?
Microsoft Excel is a spreadsheet application used to store, organize, analyze, and visualize data. It is part of Microsoft 365.
Main Uses of Excel:
- Data entry and management
- Calculations using formulas and functions
- Data analysis and reporting
- Creating charts and dashboards
- Budgeting and financial analysis
- Automation using macros and VBA
๐ Example:
A data analyst may use Excel to analyze sales data and create monthly KPI dashboards.
2. What is the difference between a workbook and a worksheet?
Workbook | Worksheet
A workbook is the entire Excel file | A worksheet is a single sheet/tab inside the workbook
It can contain multiple worksheets | It contains rows and columns of data
Saved as .xlsx, .xls, etc. | Appears as tabs at the bottom
๐ Example:
Sales_Report.xlsx = Workbook
January Sales = Worksheet inside the workbook
3. What is a cell, row, and column?
Cell: Intersection of a row and column
Example: B5
Row: Horizontal arrangement of data
Rows are numbered: 1, 2, 3...
Column: Vertical arrangement of data
Columns are labeled: A, B, C...
๐ Example:
If โSalesโ is written in cell C2, then:
- C = Column
- 2 = Row
- C2 = Cell
4. How do you rename, insert, or delete a worksheet?
Rename a Worksheet:
- Double-click the sheet tab
OR
- Right-click โ Rename
Insert a Worksheet:
- Click the + icon beside sheet tabs
OR
- Press Shift + F11
Delete a Worksheet:
- Right-click sheet tab โ Delete
โ ๏ธ Important:
Deleting a worksheet permanently removes its data unless recovered immediately.
5. How do you select a range, entire row, or entire column?
Select a Range:
Click and drag across cells
Example: A1:D10
Select Entire Row:
- Click the row number
OR
- Shortcut: Shift + Space
Select Entire Column:
- Click the column letter
OR
- Shortcut: Ctrl + Space
๐ Useful for formatting, filtering, or applying formulas quickly.
6. How do you copy, paste, and cut data?
Action | Shortcut
Copy | Ctrl + C
Paste | Ctrl + V
Cut | Ctrl + X
Paste Special:
Used when you want to paste:
- Values only
- Formulas only
- Formatting only
Shortcut: Ctrl + Alt + V
๐ Example:
Copy formulas without changing formatting using โPaste Special โ Formulasโ.
7. How do you use Zoom, Freeze Panes, and Split Window?
Zoom:
Used to increase or decrease worksheet view size.
- Bottom-right zoom slider
OR
- View โ Zoom
Freeze Panes:
Keeps headers visible while scrolling.
Path: View โ Freeze Panes
Common options:
- Freeze Top Row
- Freeze First Column
๐ Example:
Freeze headers in large sales reports.
Split Window:
Splits worksheet into multiple scrollable sections.
Path: View โ Split
Useful when comparing distant parts of the same sheet.
8. How do you hide/unhide rows and columns?
Hide:
- Select row/column
- Right-click โ Hide
Unhide:
- Select surrounding rows/columns
- Right-click โ Unhide
๐ Example:
Hide helper columns containing intermediate calculations.
9. How do you insert/delete rows and columns without breaking formulas?
Best Practice:
Use Excel insert/delete options instead of manual copy-paste.
Insert:
Right-click row/column โ Insert
Delete:
Right-click row/column โ Delete
Why?
Excel automatically adjusts formulas and references.
๐ Example:
If formula is: =SUM(A1:A5)
After inserting a new row inside the range, Excel updates automatically: =SUM(A1:A6)
โ ๏ธ Avoid deleting cells individually unless necessary because it may shift references incorrectly.
10. How do you save, open, and share an Excel file (including via OneDrive / Microsoft SharePoint)?
Save a File:
- Ctrl + S
- File โ Save As
Open a File:
- File โ Open
OR
- Double-click the Excel file
Share via OneDrive:
1. Save file to OneDrive
2. Click Share
3. Generate link or invite users
Share via Microsoft SharePoint:
- Upload workbook to SharePoint
- Collaborate with multiple users in real time
Double Tap โค๏ธ For Part-2
โค5๐1๐คฉ1
1. Walmart เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ
Answer- เคธเฅเคฎ เคตเฅเคฒเฅเคเคจ (Sam Walton)1962
2. Paytm เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคตเคฟเคเคฏ เคถเฅเคเคฐ เคถเคฐเฅเคฎเคพ (2010)
3. Google เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ เฅค
Answer= เคฒเฅเคฐเฅ เคชเฅเค ,เคธเคฐเฅเคซ เคฌเฅเคฐเคฟเคจ (1998)
4. Microsoft เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคฌเคฟเคฒ เคเฅเคเฅเคธ, เคชเฅเคฒ เคเคฒเคจ (1975)
5.WhatsApp เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคฌเฅเคฐเคพเคฏเคจ เคเคเฅเคเคจ ,เคเฅเคจ เคเฅเคฎ (2009)
6.Amazon เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคเฅเคซ เคฌเฅเคเฅเคธ (Jef bez0s1994
7. Flipkart เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคธเคเคฟเคจ เคฌเคเคธเคฒ เคเคฐ เคฌเคฟเคจเฅเคจเฅ เคฌเคเคธเคฒ (2007)
8. Yahoo เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- David filo, Jerry yang (1994)
9. Apple เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคธเฅเคเฅเคต เคเฅเคฌเฅเคธ (1976
10. Wikipedia เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ
Answer= เคเคฟเคฎเฅ เคตเฅเคฒเฅเคธ (2001)
11. Motorola เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ (1928)
Answer- Paul & Joseph Gablin
12. Facebook เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer= เคฎเคพเคฐเฅเค เคเฅเคเคฐเคฌเคฐเฅเค (2004)
13Alibaba เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer= เคเฅเค เคฎเคพ (1999
14. Nokia เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Fredrik idestamLe0 mechelin
15. Reliance เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answet- เคงเฅเคฐเฅเคญเคพเค เค เคฎเฅเคฌเคพเคจเฅ (1997)
16. Ebay เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Pierre Omidyar (1995)
17. Twitter เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเค เคกเฅเคฐเฅเคธ (2006)
18Instagram เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเคตเคฟเคจ เคธเคฟเคธเฅเคเฅเคฐเฅเคฎ, เคฎเคพเคเค เคเคฐเคฟเคเคฐ (2010)
19. YouTube เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Jawed KarimStee Chen (2005)
20. Skype เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Niklas ZennstromJanus Friis (2003)
21. Tesla เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเคฒเฅเคจ เคฎเคธเฅเค (2003)
22. Intel เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเคฐเฅเคกเคจ เคฎเฅเคฐเฅ (1968)
23. Samsung เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer Lee Byungchul (1938)
24. Xiaomi เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer - Lie Jun 2010.
เคชเฅเคฐเคฎเฅเค เคคเคเคจเฅเคเฅ เค เคพเคตเคฟเคทเฅเคเคพเคฐ เคเคฐ เค เคพเคตเคฟเคทเฅเคเคพเคฐเค -
................................................................................
1822 - เคเคงเฅเคจเคฟเค เคเคเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคพเคฐเฅเคฒเฅเคธ เคฌเฅเคฌเฅเค
1868 - เคเฅเคฌเฅเคฐเฅเคก เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเคฟเคธเฅเคเฅเคซเคฐ เคฒเฅเคฅเคฎ
1925 - เคเฅเคฒเฅเคตเคฟเคเคจ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคจ เคฒเฅเคเฅ เคฌเฅเคฏเคฐเฅเคก ( John Logie Baird )
1938 - เคซเฅเคเฅ เคเฅเคชเฅ/เคเฅเคฐเฅเคเฅเคธ เคฎเคถเฅเคจ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคธเฅเคเคฐ เคเคพเคฐเฅเคฒเคธเคจ
1950 - เคคเคพเคฐ เคตเคพเคฒเคพ เคฐเคฟเคฎเฅเค เคเคเคเฅเคฐเฅเคฒ (Wire Remote Control) "Lazy Bones" - เคเฅเคจเคฟเคฅ เคฐเฅเคกเคฟเคฏเฅ เคเคพเคฐเคชเฅเคฐเฅเคถเคจ ( Zenith Radio Corporation )
1954 - เคนเคพเคฐเฅเคก เคกเคฟเคธเฅเค เคกเฅเคฐเคพเคเคต เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคเคฌเฅเคเคฎ เคเฅ เคเฅเคฎ
1955 - เคตเคพเคฏเคฐเคฒเฅเคธ เคฐเคฟเคฎเฅเค เคเคเคเฅเคฐเฅเคฒ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ ( Wireless Remote Control) "Flashmatic" - เคฏเฅเคเฅเคจ เคชเฅเคฒเฅ (Eugene Polley)
1955 - เคฒเฅเคฅเคฟเคฏเคฎ เคเคฏเคจ เคฌเฅเคเคฐเฅ (เคเคตเคฐเฅเคกเฅ เคฌเฅเคเคฐเฅ) เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคฒเฅเคตเคฟเคธ เคเคฐเฅ ( Lewis Urry )
1957 - เคเคเคชเฅเคฏเฅเคเคฐ เคธเฅเคเฅเคจเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Russell A. Kirsch
1958 - เคตเฅเคกเคฟเคฏเฅ เคเฅเคฎ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคตเคฟเคฒเฅ เคนเคฟเคเคเคฟเคจเคฌเฅเคฅเคฎ ( Willy Higinbotham )
1960 - เคเคตเคฐเคนเฅเคก เคชเฅเคฐเฅเคเฅเคเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Roger Appledorn
1963 - เคฎเคพเคเคธ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคกเค เคเคเคเฅเคฒเคฌเคฐเฅเค
1969 - Smiley เคเฅเคนเคฐเฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคฎเฅเคฐเฅเคฐเฅ เคเคฐ Bernard
1969 - เคฒเฅเคเคผเคฐ เคชเฅเคฐเคฟเคเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเฅ เคธเฅเคเคพเคฐเฅเคเคตเฅเคฆเคฐ
1971 - เคซเฅเคฒเคพเคชเฅ เคกเคฟเคธเฅเค เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคฒเคพเคจ เคถเฅเคเคพเคฐเฅเค
1971 - เคฎเคพเคเคเฅเคฐเฅเคชเฅเคฐเฅเคธเฅเคธเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคเคเฅเคฒ
1975 - เคกเคฟเคเคฟเคเคฒ เคเฅเคฎเคฐเคพ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคธเฅเคเฅเคต เคธเฅเคธเคจ 7 ( Steve Sasson 7 )
1975 - เคชเคฐเฅเคธเคจเคฒ เคเคฎเฅเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคก เคฐเฅเคฌเคฐเฅเคเฅเคธ (Ed Roberts)
1975 - เคชเคฐเฅเคธเคจเคฒ เคเคฎเฅเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคก เคฐเฅเคฌเคฐเฅเคเฅเคธ (Ed Roberts)
1975 - เคฒเฅเคเคฐ เคชเฅเคฐเคฟเคเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคเคฌเฅเคเคฎ
1980 - C++ (เคธเฅ ++) เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Bjarne Stroustrup (เคฌเคเคฐเฅเคจเฅ เคธเฅเคคเฅเคฐเฅเคธเฅเคคเฅเคฐเฅเคช)
1988 - เคธเฅเคกเฅ เคฌเคฐเฅเคจเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Taiyo Yedun
1991 - เคตเคพเค เคซเคพเค เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- John O'Sullivan เคเคฐ John Deane
1994 - เคฌเฅเคฒเฅเคเฅเคฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคฐเคฟเคเฅเคธเคจ เคเคเคชเคจเฅ ( Ericsson company )
1995 - GPS เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคกเฅเคจ เคฐเคฟเค (Don Rea)
1995 - เคฏเฅเคเคธเคฌเฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เค เคเคฏ เคญเคเฅเค
2000 - เคฏเฅเคเคธเคฌเฅ เคซเฅเคฒเฅเคถ เคกเฅเคฐเคพเคเคต เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเฅเค เคเฅเคเฅเคจเฅเคฒเฅเคเฅ
Answer- เคธเฅเคฎ เคตเฅเคฒเฅเคเคจ (Sam Walton)1962
2. Paytm เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคตเคฟเคเคฏ เคถเฅเคเคฐ เคถเคฐเฅเคฎเคพ (2010)
3. Google เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ เฅค
Answer= เคฒเฅเคฐเฅ เคชเฅเค ,เคธเคฐเฅเคซ เคฌเฅเคฐเคฟเคจ (1998)
4. Microsoft เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคฌเคฟเคฒ เคเฅเคเฅเคธ, เคชเฅเคฒ เคเคฒเคจ (1975)
5.WhatsApp เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer= เคฌเฅเคฐเคพเคฏเคจ เคเคเฅเคเคจ ,เคเฅเคจ เคเฅเคฎ (2009)
6.Amazon เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคเฅเคซ เคฌเฅเคเฅเคธ (Jef bez0s1994
7. Flipkart เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคธเคเคฟเคจ เคฌเคเคธเคฒ เคเคฐ เคฌเคฟเคจเฅเคจเฅ เคฌเคเคธเคฒ (2007)
8. Yahoo เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- David filo, Jerry yang (1994)
9. Apple เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅเฅค
Answer- เคธเฅเคเฅเคต เคเฅเคฌเฅเคธ (1976
10. Wikipedia เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ
Answer= เคเคฟเคฎเฅ เคตเฅเคฒเฅเคธ (2001)
11. Motorola เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ (1928)
Answer- Paul & Joseph Gablin
12. Facebook เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer= เคฎเคพเคฐเฅเค เคเฅเคเคฐเคฌเคฐเฅเค (2004)
13Alibaba เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer= เคเฅเค เคฎเคพ (1999
14. Nokia เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Fredrik idestamLe0 mechelin
15. Reliance เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answet- เคงเฅเคฐเฅเคญเคพเค เค เคฎเฅเคฌเคพเคจเฅ (1997)
16. Ebay เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Pierre Omidyar (1995)
17. Twitter เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเค เคกเฅเคฐเฅเคธ (2006)
18Instagram เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเคตเคฟเคจ เคธเคฟเคธเฅเคเฅเคฐเฅเคฎ, เคฎเคพเคเค เคเคฐเคฟเคเคฐ (2010)
19. YouTube เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Jawed KarimStee Chen (2005)
20. Skype เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- Niklas ZennstromJanus Friis (2003)
21. Tesla เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเคฒเฅเคจ เคฎเคธเฅเค (2003)
22. Intel เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer- เคเฅเคฐเฅเคกเคจ เคฎเฅเคฐเฅ (1968)
23. Samsung เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer Lee Byungchul (1938)
24. Xiaomi เคเฅ เคธเคเคธเฅเคฅเคพเคชเค เคเฅเคจ เคนเฅ?
Answer - Lie Jun 2010.
เคชเฅเคฐเคฎเฅเค เคคเคเคจเฅเคเฅ เค เคพเคตเคฟเคทเฅเคเคพเคฐ เคเคฐ เค เคพเคตเคฟเคทเฅเคเคพเคฐเค -
................................................................................
1822 - เคเคงเฅเคจเคฟเค เคเคเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคพเคฐเฅเคฒเฅเคธ เคฌเฅเคฌเฅเค
1868 - เคเฅเคฌเฅเคฐเฅเคก เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเคฟเคธเฅเคเฅเคซเคฐ เคฒเฅเคฅเคฎ
1925 - เคเฅเคฒเฅเคตเคฟเคเคจ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคจ เคฒเฅเคเฅ เคฌเฅเคฏเคฐเฅเคก ( John Logie Baird )
1938 - เคซเฅเคเฅ เคเฅเคชเฅ/เคเฅเคฐเฅเคเฅเคธ เคฎเคถเฅเคจ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคธเฅเคเคฐ เคเคพเคฐเฅเคฒเคธเคจ
1950 - เคคเคพเคฐ เคตเคพเคฒเคพ เคฐเคฟเคฎเฅเค เคเคเคเฅเคฐเฅเคฒ (Wire Remote Control) "Lazy Bones" - เคเฅเคจเคฟเคฅ เคฐเฅเคกเคฟเคฏเฅ เคเคพเคฐเคชเฅเคฐเฅเคถเคจ ( Zenith Radio Corporation )
1954 - เคนเคพเคฐเฅเคก เคกเคฟเคธเฅเค เคกเฅเคฐเคพเคเคต เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคเคฌเฅเคเคฎ เคเฅ เคเฅเคฎ
1955 - เคตเคพเคฏเคฐเคฒเฅเคธ เคฐเคฟเคฎเฅเค เคเคเคเฅเคฐเฅเคฒ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ ( Wireless Remote Control) "Flashmatic" - เคฏเฅเคเฅเคจ เคชเฅเคฒเฅ (Eugene Polley)
1955 - เคฒเฅเคฅเคฟเคฏเคฎ เคเคฏเคจ เคฌเฅเคเคฐเฅ (เคเคตเคฐเฅเคกเฅ เคฌเฅเคเคฐเฅ) เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคฒเฅเคตเคฟเคธ เคเคฐเฅ ( Lewis Urry )
1957 - เคเคเคชเฅเคฏเฅเคเคฐ เคธเฅเคเฅเคจเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Russell A. Kirsch
1958 - เคตเฅเคกเคฟเคฏเฅ เคเฅเคฎ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคตเคฟเคฒเฅ เคนเคฟเคเคเคฟเคจเคฌเฅเคฅเคฎ ( Willy Higinbotham )
1960 - เคเคตเคฐเคนเฅเคก เคชเฅเคฐเฅเคเฅเคเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Roger Appledorn
1963 - เคฎเคพเคเคธ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคกเค เคเคเคเฅเคฒเคฌเคฐเฅเค
1969 - Smiley เคเฅเคนเคฐเฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคฎเฅเคฐเฅเคฐเฅ เคเคฐ Bernard
1969 - เคฒเฅเคเคผเคฐ เคชเฅเคฐเคฟเคเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเฅ เคธเฅเคเคพเคฐเฅเคเคตเฅเคฆเคฐ
1971 - เคซเฅเคฒเคพเคชเฅ เคกเคฟเคธเฅเค เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคฒเคพเคจ เคถเฅเคเคพเคฐเฅเค
1971 - เคฎเคพเคเคเฅเคฐเฅเคชเฅเคฐเฅเคธเฅเคธเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคเคเฅเคฒ
1975 - เคกเคฟเคเคฟเคเคฒ เคเฅเคฎเคฐเคพ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคธเฅเคเฅเคต เคธเฅเคธเคจ 7 ( Steve Sasson 7 )
1975 - เคชเคฐเฅเคธเคจเคฒ เคเคฎเฅเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคก เคฐเฅเคฌเคฐเฅเคเฅเคธ (Ed Roberts)
1975 - เคชเคฐเฅเคธเคจเคฒ เคเคฎเฅเคชเฅเคฏเฅเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- เคเคก เคฐเฅเคฌเคฐเฅเคเฅเคธ (Ed Roberts)
1975 - เคฒเฅเคเคฐ เคชเฅเคฐเคฟเคเคเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคเคฌเฅเคเคฎ
1980 - C++ (เคธเฅ ++) เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Bjarne Stroustrup (เคฌเคเคฐเฅเคจเฅ เคธเฅเคคเฅเคฐเฅเคธเฅเคคเฅเคฐเฅเคช)
1988 - เคธเฅเคกเฅ เคฌเคฐเฅเคจเคฐ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - Taiyo Yedun
1991 - เคตเคพเค เคซเคพเค เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ- John O'Sullivan เคเคฐ John Deane
1994 - เคฌเฅเคฒเฅเคเฅเคฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเคฐเคฟเคเฅเคธเคจ เคเคเคชเคจเฅ ( Ericsson company )
1995 - GPS เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคกเฅเคจ เคฐเคฟเค (Don Rea)
1995 - เคฏเฅเคเคธเคฌเฅ เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เค เคเคฏ เคญเคเฅเค
2000 - เคฏเฅเคเคธเคฌเฅ เคซเฅเคฒเฅเคถ เคกเฅเคฐเคพเคเคต เคเคพ เคเคตเคฟเคทเฅเคเคพเคฐ - เคเฅเคฐเฅเค เคเฅเคเฅเคจเฅเคฒเฅเคเฅ
โก1๐1๐คฉ1
If I need to teach someone data analytics from the basics, here is my strategy:
1. I will first remove the fear of tools from that person
2. i will start with the excel because it looks familiar and easy to use
3. I put more emphasis on projects like at least 5 to 6 with the excel. because in industry you learn by doing things
4. I will release the person from the tutorial hell and move into a more action oriented person
5. Then I move to the sql because every job wants it , even with the ai tools you need strong understanding for it if you are going to use it daily
6. After strong understanding, I will push the person to solve 100 to 150 Sql problems from basic to advance
7. It helps the person to develop the analytical thinking
8. Then I push the person to solve 3 case studies as it helps how we pull the data in the real life
9. Then I move the person to power bi to do again 5 projects by using either sql or excel files
10. Now the fear is removed.
11. Now I push the person to solve unguided challenges and present them by video recording as it increases the problem solving, communication and data story telling skills
12. Further it helps you to clear case study round given by most of the companies
13. Now i help the person how to present them in resume and also how these tools are used in real world.
14. You know the interesting fact, all of above is present free in youtube and I also mentor the people through existing youtube videos.
15. But people stuck in the tutorial hell, loose motivation , stay confused that they are either in the right direction or not.
16. As a personal mentor , I help them to get of the tutorial hell, set them in the right direction and they stay motivated when they start to see the difference before amd after mentorship
1. I will first remove the fear of tools from that person
2. i will start with the excel because it looks familiar and easy to use
3. I put more emphasis on projects like at least 5 to 6 with the excel. because in industry you learn by doing things
4. I will release the person from the tutorial hell and move into a more action oriented person
5. Then I move to the sql because every job wants it , even with the ai tools you need strong understanding for it if you are going to use it daily
6. After strong understanding, I will push the person to solve 100 to 150 Sql problems from basic to advance
7. It helps the person to develop the analytical thinking
8. Then I push the person to solve 3 case studies as it helps how we pull the data in the real life
9. Then I move the person to power bi to do again 5 projects by using either sql or excel files
10. Now the fear is removed.
11. Now I push the person to solve unguided challenges and present them by video recording as it increases the problem solving, communication and data story telling skills
12. Further it helps you to clear case study round given by most of the companies
13. Now i help the person how to present them in resume and also how these tools are used in real world.
14. You know the interesting fact, all of above is present free in youtube and I also mentor the people through existing youtube videos.
15. But people stuck in the tutorial hell, loose motivation , stay confused that they are either in the right direction or not.
16. As a personal mentor , I help them to get of the tutorial hell, set them in the right direction and they stay motivated when they start to see the difference before amd after mentorship
โค1๐คฉ1๐1๐1
๐ Excel Interview Questions with Answers โ Part 2
11. How do you format numbers as currency, percentage, or date?
Excel provides different number formats to display data properly.
Steps:
1. Select the cells
2. Go to Home โ Number Group
3. Choose the required format
Common Formats:
Currency โ โน25,000
Percentage โ 85%
Date โ 15-May-2026
Shortcuts:
Currency โ Ctrl + Shift + $
Percentage โ Ctrl + Shift + %
Date โ Ctrl + Shift + #
๐ Example:
0.25 displayed as Percentage becomes 25%.
12. How do you wrap text and merge cells?
Wrap Text:
Used when text is too long for a cell.
Path: Home โ Wrap Text
๐ It displays content in multiple lines inside the same cell.
Merge Cells:
Combines multiple cells into one larger cell.
Path: Home โ Merge & Center
Common Merge Options:
- Merge & Center
- Merge Across
- Merge Cells
โ ๏ธ Avoid excessive merging in data tables because it can create problems while sorting/filtering.
13. How do you resize columns and rows to fit content?
Manual Resize:
- Drag the column boundary
- Drag the row boundary
Auto Resize:
Double-click the boundary between columns or rows.
๐ Example:
If employee names are cut off, AutoFit adjusts width automatically.
14. How do you apply bold, italic, borders, and background colors?
These options improve readability and presentation.
Formatting Shortcuts:
Bold โ Ctrl + B
Italic โ Ctrl + I
Underline โ Ctrl + U
Borders:
Home โ Borders
Background Color:
Home โ Fill Color
๐ Example:
Highlight KPI values using bold text and colored backgrounds.
15. How do you AutoFit column width and row height?
AutoFit Column Width:
1. Select columns
2. Go to: Home โ Format โ AutoFit Column Width
OR
Double-click the column edge.
AutoFit Row Height:
Home โ Format โ AutoFit Row Height
๐ Useful for reports containing long text or wrapped content.
16. How do you print and set page layout (margins, gridlines, headers/footers)?
Print:
Shortcut: Ctrl + P
Page Layout Settings:
Path: Page Layout
You can configure:
- Margins
- Orientation (Portrait/Landscape)
- Size
- Print Area
Gridlines:
Page Layout โ Gridlines โ Print
Headers & Footers:
Insert โ Header & Footer
๐ Example:
Add page numbers and company name in reports.
17. How do you protect a sheet or workbook with a password?
Protect Worksheet:
1. Go to: Review โ Protect Sheet
2. Enter password
3. Choose allowed actions
Protect Workbook:
Review โ Protect Workbook
๐ Protection prevents:
- Editing formulas
- Deleting sheets
- Structural changes
โ ๏ธ Always keep backup passwords securely stored.
18. How do you use โFormat as Tableโ and why is it useful?
Steps:
1. Select data
2. Go to: Home โ Format as Table
Benefits:
- Automatic filters
- Better formatting
- Dynamic ranges
- Structured references
- Easier PivotTables & charts
๐ Example:
Instead of:
=SUM(A2:A100)
You can use:
=SUM(Sales[Revenue])
This is easier to read and maintain.
19. How do you use conditional formatting (highlight cells, data bars, color scales, icons)?
Conditional formatting changes cell appearance based on conditions.
Path:
Home โ Conditional Formatting
Common Options:
- Highlight Cell Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
๐ Example:
- Highlight sales below target in red
- Use green/yellow/red color scales for performance metrics
Formula-Based Formatting Example:
=A1>100
Highlights cells greater than 100.
20. How do you apply custom number formats?
Custom formats control how data appears without changing actual values.
Path:
Format Cells โ Number โ Custom
Examples:
ร 0 ร โ 25
0.00 โ 25.00
โน#,##0 โ โน12,500
0% โ 75%
Display Thousands as K:
0,"K"
Result: 25000 โ 25K
๐ Useful in dashboards and executive reports for cleaner visuals.
Double Tap โค๏ธ For Part-3
11. How do you format numbers as currency, percentage, or date?
Excel provides different number formats to display data properly.
Steps:
1. Select the cells
2. Go to Home โ Number Group
3. Choose the required format
Common Formats:
Currency โ โน25,000
Percentage โ 85%
Date โ 15-May-2026
Shortcuts:
Currency โ Ctrl + Shift + $
Percentage โ Ctrl + Shift + %
Date โ Ctrl + Shift + #
๐ Example:
0.25 displayed as Percentage becomes 25%.
12. How do you wrap text and merge cells?
Wrap Text:
Used when text is too long for a cell.
Path: Home โ Wrap Text
๐ It displays content in multiple lines inside the same cell.
Merge Cells:
Combines multiple cells into one larger cell.
Path: Home โ Merge & Center
Common Merge Options:
- Merge & Center
- Merge Across
- Merge Cells
โ ๏ธ Avoid excessive merging in data tables because it can create problems while sorting/filtering.
13. How do you resize columns and rows to fit content?
Manual Resize:
- Drag the column boundary
- Drag the row boundary
Auto Resize:
Double-click the boundary between columns or rows.
๐ Example:
If employee names are cut off, AutoFit adjusts width automatically.
14. How do you apply bold, italic, borders, and background colors?
These options improve readability and presentation.
Formatting Shortcuts:
Bold โ Ctrl + B
Italic โ Ctrl + I
Underline โ Ctrl + U
Borders:
Home โ Borders
Background Color:
Home โ Fill Color
๐ Example:
Highlight KPI values using bold text and colored backgrounds.
15. How do you AutoFit column width and row height?
AutoFit Column Width:
1. Select columns
2. Go to: Home โ Format โ AutoFit Column Width
OR
Double-click the column edge.
AutoFit Row Height:
Home โ Format โ AutoFit Row Height
๐ Useful for reports containing long text or wrapped content.
16. How do you print and set page layout (margins, gridlines, headers/footers)?
Print:
Shortcut: Ctrl + P
Page Layout Settings:
Path: Page Layout
You can configure:
- Margins
- Orientation (Portrait/Landscape)
- Size
- Print Area
Gridlines:
Page Layout โ Gridlines โ Print
Headers & Footers:
Insert โ Header & Footer
๐ Example:
Add page numbers and company name in reports.
17. How do you protect a sheet or workbook with a password?
Protect Worksheet:
1. Go to: Review โ Protect Sheet
2. Enter password
3. Choose allowed actions
Protect Workbook:
Review โ Protect Workbook
๐ Protection prevents:
- Editing formulas
- Deleting sheets
- Structural changes
โ ๏ธ Always keep backup passwords securely stored.
18. How do you use โFormat as Tableโ and why is it useful?
Steps:
1. Select data
2. Go to: Home โ Format as Table
Benefits:
- Automatic filters
- Better formatting
- Dynamic ranges
- Structured references
- Easier PivotTables & charts
๐ Example:
Instead of:
=SUM(A2:A100)
You can use:
=SUM(Sales[Revenue])
This is easier to read and maintain.
19. How do you use conditional formatting (highlight cells, data bars, color scales, icons)?
Conditional formatting changes cell appearance based on conditions.
Path:
Home โ Conditional Formatting
Common Options:
- Highlight Cell Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
๐ Example:
- Highlight sales below target in red
- Use green/yellow/red color scales for performance metrics
Formula-Based Formatting Example:
=A1>100
Highlights cells greater than 100.
20. How do you apply custom number formats?
Custom formats control how data appears without changing actual values.
Path:
Format Cells โ Number โ Custom
Examples:
ร 0 ร โ 25
0.00 โ 25.00
โน#,##0 โ โน12,500
0% โ 75%
Display Thousands as K:
0,"K"
Result: 25000 โ 25K
๐ Useful in dashboards and executive reports for cleaner visuals.
Double Tap โค๏ธ For Part-3
โค2โก1๐คฉ1๐1๐ค1
๐ Complete 2-Month Excel Roadmap ๐๐ฅ
If you want to become strong in Microsoft Excel for:
โข Data Analytics
โข Business Analysis
โข Finance
โข Reporting
โข Office Work
โข Dashboards
โข Automation
then this 8-week roadmap is enough to build solid Excel skills step-by-step. ๐ฏ
๐๏ธ Month 1 โ Build Strong Excel Foundations
โ Week 1: Excel Basics & Interface
Topics to Learn:
โ Workbook vs Worksheet
โ Rows, Columns, Cells
โ Ribbon & Tabs
โ Entering Data
โ Copy, Paste, Cut
โ Undo/Redo
โ Save/Open Files
โ Zoom & Freeze Panes
โ Hide/Unhide Rows & Columns
โ Keyboard Shortcuts
Practice Tasks:
โ Create a student marksheet
โ Create an employee database
โ Use formatting and borders
โ Freeze headers while scrolling
Important Shortcuts:
Shortcut : Use
Ctrl + C : Copy
Ctrl + V : Paste
Ctrl + Z : Undo
Ctrl + S : Save
Ctrl + Arrow Keys : Fast navigation
โ Week 2: Formatting + Basic Formulas
Topics to Learn:
โ Cell Formatting
โ Conditional Formatting
โ Format as Table
โ Wrap Text & Merge Cells
โ Number Formats
โ Basic Arithmetic Formulas
โ Relative & Absolute References
Functions to Master:
=SUM()
=AVERAGE()
=MIN()
=MAX()
=COUNT()
=COUNTA()
Practice Tasks:
โ Sales summary sheet
โ Expense tracker
โ Student report card
โ Week 3: Logical + Text + Date Functions
Topics to Learn:
โ IF Statements
โ Nested IF
โ AND / OR
โ Error Handling
Important Functions:
=IF()
=IFERROR()
=TRIM()
=LEFT()
=RIGHT()
=MID()
=TODAY()
=DATEDIF()
Practice Tasks:
โ Attendance tracker
โ Invoice generator
โ Clean messy customer names
โ Week 4: Lookup Functions + Data Cleaning
Lookup Functions:
โ VLOOKUP
โ HLOOKUP
โ INDEX + MATCH
โ XLOOKUP
Data Cleaning Topics:
โ Remove Duplicates
โ Text-to-Columns
โ Flash Fill
โ Sorting & Filtering
โ Data Validation Dropdowns
Practice Tasks:
โ Employee lookup system
โ Product inventory sheet
โ Customer database cleaning
๐๏ธ Month 2 โ Advanced Excel + Dashboard Skills
โ Week 5: PivotTables + Charts
Topics to Learn:
โ PivotTables
โ Grouping Data
โ PivotCharts
โ Slicers & Timelines
โ Dashboard Basics
Practice Tasks:
โ Sales dashboard
โ HR dashboard
โ Monthly performance report
Charts to Learn:
Chart : Use
Bar Chart : Comparison
Line Chart : Trends
Pie Chart : Distribution
Combo Chart : Mixed analysis
โ Week 6: Advanced Excel Functions
Important Functions:
=SUMIFS()
=COUNTIFS()
=AVERAGEIFS()
=SUMPRODUCT()
=FILTER()
=SORT()
=UNIQUE()
Learn:
โ Dynamic Arrays
โ Named Ranges
โ Structured References
โ Advanced Conditional Formatting
Practice Tasks:
โ Dynamic KPI dashboard
โ Multi-condition reporting
โ Automated summary tables
โ Week 7: Power Query + Automation
Learn Microsoft Power Query:
โ Import CSV Files
โ Clean Data
โ Merge Queries
โ Pivot/Unpivot
โ Refresh Data
Automation Topics:
โ Macro Recording
โ Basic VBA Concepts
โ Report Automation
If you want to become strong in Microsoft Excel for:
โข Data Analytics
โข Business Analysis
โข Finance
โข Reporting
โข Office Work
โข Dashboards
โข Automation
then this 8-week roadmap is enough to build solid Excel skills step-by-step. ๐ฏ
๐๏ธ Month 1 โ Build Strong Excel Foundations
โ Week 1: Excel Basics & Interface
Topics to Learn:
โ Workbook vs Worksheet
โ Rows, Columns, Cells
โ Ribbon & Tabs
โ Entering Data
โ Copy, Paste, Cut
โ Undo/Redo
โ Save/Open Files
โ Zoom & Freeze Panes
โ Hide/Unhide Rows & Columns
โ Keyboard Shortcuts
Practice Tasks:
โ Create a student marksheet
โ Create an employee database
โ Use formatting and borders
โ Freeze headers while scrolling
Important Shortcuts:
Shortcut : Use
Ctrl + C : Copy
Ctrl + V : Paste
Ctrl + Z : Undo
Ctrl + S : Save
Ctrl + Arrow Keys : Fast navigation
โ Week 2: Formatting + Basic Formulas
Topics to Learn:
โ Cell Formatting
โ Conditional Formatting
โ Format as Table
โ Wrap Text & Merge Cells
โ Number Formats
โ Basic Arithmetic Formulas
โ Relative & Absolute References
Functions to Master:
=SUM()
=AVERAGE()
=MIN()
=MAX()
=COUNT()
=COUNTA()
Practice Tasks:
โ Sales summary sheet
โ Expense tracker
โ Student report card
โ Week 3: Logical + Text + Date Functions
Topics to Learn:
โ IF Statements
โ Nested IF
โ AND / OR
โ Error Handling
Important Functions:
=IF()
=IFERROR()
=TRIM()
=LEFT()
=RIGHT()
=MID()
=TODAY()
=DATEDIF()
Practice Tasks:
โ Attendance tracker
โ Invoice generator
โ Clean messy customer names
โ Week 4: Lookup Functions + Data Cleaning
Lookup Functions:
โ VLOOKUP
โ HLOOKUP
โ INDEX + MATCH
โ XLOOKUP
Data Cleaning Topics:
โ Remove Duplicates
โ Text-to-Columns
โ Flash Fill
โ Sorting & Filtering
โ Data Validation Dropdowns
Practice Tasks:
โ Employee lookup system
โ Product inventory sheet
โ Customer database cleaning
๐๏ธ Month 2 โ Advanced Excel + Dashboard Skills
โ Week 5: PivotTables + Charts
Topics to Learn:
โ PivotTables
โ Grouping Data
โ PivotCharts
โ Slicers & Timelines
โ Dashboard Basics
Practice Tasks:
โ Sales dashboard
โ HR dashboard
โ Monthly performance report
Charts to Learn:
Chart : Use
Bar Chart : Comparison
Line Chart : Trends
Pie Chart : Distribution
Combo Chart : Mixed analysis
โ Week 6: Advanced Excel Functions
Important Functions:
=SUMIFS()
=COUNTIFS()
=AVERAGEIFS()
=SUMPRODUCT()
=FILTER()
=SORT()
=UNIQUE()
Learn:
โ Dynamic Arrays
โ Named Ranges
โ Structured References
โ Advanced Conditional Formatting
Practice Tasks:
โ Dynamic KPI dashboard
โ Multi-condition reporting
โ Automated summary tables
โ Week 7: Power Query + Automation
Learn Microsoft Power Query:
โ Import CSV Files
โ Clean Data
โ Merge Queries
โ Pivot/Unpivot
โ Refresh Data
Automation Topics:
โ Macro Recording
โ Basic VBA Concepts
โ Report Automation
Practice Tasks:
โ Automated sales report
โ CSV cleaning workflow
โ Refreshable dashboard
โ Week 8: Real Projects + Interview Preparation
Build These Projects:
๐ Project 1: Sales Dashboard
Include:
โข KPIs
โข PivotTables
โข Charts
โข Slicers
๐ฐ Project 2: Expense Tracker
Include:
โข Budget vs Actual
โข Monthly Trends
โข Conditional Formatting
๐จโ๐ผ Project 3: HR Analytics Dashboard
Include:
โข Attendance
โข Employee Performance
โข Attrition Analysis
Interview Preparation:
โ Practice Excel interview questions
โ Learn keyboard shortcuts
โ Solve business problems
โ Explain dashboards confidently
๐ Best Excel Features Every Analyst Should Master
Skill : Importance
PivotTables : โญโญโญโญโญ
Lookup Functions : โญโญโญโญโญ
Data Cleaning : โญโญโญโญโญ
Dashboards : โญโญโญโญโญ
Power Query : โญโญโญโญโญ
Conditional Formatting : โญโญโญโญ
VBA Basics : โญโญโญ
๐ Best Resources to Learn Excel
Official Website
Microsoft Excel
Practice Platforms
โข Excel Practice Online
โข W3Schools Excel Tutorial
โข ExcelJet
YouTube Channels
โข Leila Gharani
โข Kevin Stratvert
โข MyOnlineTrainingHub
๐ Consistency matters more than speed.
Practice daily for 1 to 2 hours and build projects alongside learning.
Double Tap โค๏ธ For Detailed Explanation
โ Automated sales report
โ CSV cleaning workflow
โ Refreshable dashboard
โ Week 8: Real Projects + Interview Preparation
Build These Projects:
๐ Project 1: Sales Dashboard
Include:
โข KPIs
โข PivotTables
โข Charts
โข Slicers
๐ฐ Project 2: Expense Tracker
Include:
โข Budget vs Actual
โข Monthly Trends
โข Conditional Formatting
๐จโ๐ผ Project 3: HR Analytics Dashboard
Include:
โข Attendance
โข Employee Performance
โข Attrition Analysis
Interview Preparation:
โ Practice Excel interview questions
โ Learn keyboard shortcuts
โ Solve business problems
โ Explain dashboards confidently
๐ Best Excel Features Every Analyst Should Master
Skill : Importance
PivotTables : โญโญโญโญโญ
Lookup Functions : โญโญโญโญโญ
Data Cleaning : โญโญโญโญโญ
Dashboards : โญโญโญโญโญ
Power Query : โญโญโญโญโญ
Conditional Formatting : โญโญโญโญ
VBA Basics : โญโญโญ
๐ Best Resources to Learn Excel
Official Website
Microsoft Excel
Practice Platforms
โข Excel Practice Online
โข W3Schools Excel Tutorial
โข ExcelJet
YouTube Channels
โข Leila Gharani
โข Kevin Stratvert
โข MyOnlineTrainingHub
๐ Consistency matters more than speed.
Practice daily for 1 to 2 hours and build projects alongside learning.
Double Tap โค๏ธ For Detailed Explanation
โค3๐1
Keyboard #Shortcut Keys
Ctrl+A - Select All
Ctrl+B - Bold
Ctrl+C - Copy
Ctrl+D - Fill Down
Ctrl+F - Find
Ctrl+G - Goto
Ctrl+H - Replace
Ctrl+I - Italic
Ctrl+K - Insert Hyperlink
Ctrl+N - New Workbook
Ctrl+O - Open
Ctrl+P - Print
Ctrl+R - Fill Right
Ctrl+S - Save
Ctrl+U - Underline
Ctrl+V - Paste
Ctrl W - Close
Ctrl+X - Cut
Ctrl+Y - Repeat
Ctrl+Z - Undo
F1 - Help
F2 - Edit
F3 - Paste Name
F4 - Repeat last action
F4 - While typing a formula, switch between absolute/relative refs
F5 - Goto
F6 - Next Pane
F7 - Spell check
F8 - Extend mode
F9 - Recalculate all workbooks
F10 - Activate Menu bar
F11 - New Chart
F12 - Save As
Ctrl+: - Insert Current Time
Ctrl+; - Insert Current Date
Ctrl+" - Copy Value from Cell Above
Ctrl+โ - Copy Formula from Cell Above
Shift - Hold down shift for additional functions in Excelโs menu
Shift+F1 - Whatโs This?
Shift+F2 - Edit cell comment
Shift+F3 - Paste function into formula
Shift+F4 - Find Next
Shift+F5 - Find
Shift+F6 - Previous Pane
Shift+F8 - Add to selection
Shift+F9 - Calculate active worksheet
Shift+F10 - Display shortcut menu
Shift+F11 - New worksheet
Ctrl+F3 - Define name
Ctrl+F4 - Close
Ctrl+F5 - XL, Restore window size
Ctrl+F6 - Next workbook window
Shift+Ctrl+F6 - Previous workbook window
Ctrl+F7 - Move window
Ctrl+F8 - Resize window
Ctrl+F9 - Minimize workbook
Ctrl+F10 - Maximize or restore window
Ctrl+F11 - Inset 4.0 Macro sheet
Ctrl+F1 - File Open
Alt+F1 - Insert Chart
Alt+F2 - Save As
Alt+F4 - Exit
Alt+Down arrow - Display AutoComplete list
Alt+โ - Format Style dialog box
Ctrl+Shift+~ - General format
Ctrl+Shift+! - Comma format
Ctrl+Shift+@ - Time format
Ctrl+Shift+# - Date format
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percent format
Ctrl+Shift+^ - Exponential format
Ctrl+Shift+& - Place outline border around selected cells
Ctrl+Shift+_ - Remove outline border
Ctrl+Shift+* - Select current region
Ctrl++ - Insert
Ctrl+- - Delete
Ctrl+1 - Format cells dialog box
Ctrl+2 - Bold
Ctrl+3 - Italic
Ctrl+4 - Underline
Ctrl+5 - Strikethrough
Ctrl+6 - Show/Hide objects
Ctrl+7 - Show/Hide Standard toolbar
Ctrl+8 - Toggle Outline symbols
Ctrl+9 - Hide rows
Ctrl+0 - Hide columns
Ctrl+Shift+( - Unhide rows
Ctrl+Shift+) - Unhide columns
Alt or F10 - Activate the menu
Ctrl+Tab - In toolbar: next toolbar
Shift+Ctrl+Tab - In toolbar: previous toolbar
Ctrl+Tab - In a workbook: activate next workbook
Shift+Ctrl+Tab - In a workbook: activate previous workbook
Tab - Next tool
Shift+Tab - Previous tool
Enter - Do the command
Shift+Ctrl+F - Font Drop down List
Shift+Ctrl+F+F - Font tab of Format Cell Dialog box
Shift+Ctrl+P - Point size Drop down List
Ctrl + E - Align center
Ctrl + J - justify
Ctrl + L - align
Ctrl + R - align right
Alt + Tab - switch applications
Windows + P - Project screen
Windows + E - open file explorer
Windows + D - go to desktop
Windows + M - minimize all windows
Windows + S - search
Ctrl+A - Select All
Ctrl+B - Bold
Ctrl+C - Copy
Ctrl+D - Fill Down
Ctrl+F - Find
Ctrl+G - Goto
Ctrl+H - Replace
Ctrl+I - Italic
Ctrl+K - Insert Hyperlink
Ctrl+N - New Workbook
Ctrl+O - Open
Ctrl+P - Print
Ctrl+R - Fill Right
Ctrl+S - Save
Ctrl+U - Underline
Ctrl+V - Paste
Ctrl W - Close
Ctrl+X - Cut
Ctrl+Y - Repeat
Ctrl+Z - Undo
F1 - Help
F2 - Edit
F3 - Paste Name
F4 - Repeat last action
F4 - While typing a formula, switch between absolute/relative refs
F5 - Goto
F6 - Next Pane
F7 - Spell check
F8 - Extend mode
F9 - Recalculate all workbooks
F10 - Activate Menu bar
F11 - New Chart
F12 - Save As
Ctrl+: - Insert Current Time
Ctrl+; - Insert Current Date
Ctrl+" - Copy Value from Cell Above
Ctrl+โ - Copy Formula from Cell Above
Shift - Hold down shift for additional functions in Excelโs menu
Shift+F1 - Whatโs This?
Shift+F2 - Edit cell comment
Shift+F3 - Paste function into formula
Shift+F4 - Find Next
Shift+F5 - Find
Shift+F6 - Previous Pane
Shift+F8 - Add to selection
Shift+F9 - Calculate active worksheet
Shift+F10 - Display shortcut menu
Shift+F11 - New worksheet
Ctrl+F3 - Define name
Ctrl+F4 - Close
Ctrl+F5 - XL, Restore window size
Ctrl+F6 - Next workbook window
Shift+Ctrl+F6 - Previous workbook window
Ctrl+F7 - Move window
Ctrl+F8 - Resize window
Ctrl+F9 - Minimize workbook
Ctrl+F10 - Maximize or restore window
Ctrl+F11 - Inset 4.0 Macro sheet
Ctrl+F1 - File Open
Alt+F1 - Insert Chart
Alt+F2 - Save As
Alt+F4 - Exit
Alt+Down arrow - Display AutoComplete list
Alt+โ - Format Style dialog box
Ctrl+Shift+~ - General format
Ctrl+Shift+! - Comma format
Ctrl+Shift+@ - Time format
Ctrl+Shift+# - Date format
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percent format
Ctrl+Shift+^ - Exponential format
Ctrl+Shift+& - Place outline border around selected cells
Ctrl+Shift+_ - Remove outline border
Ctrl+Shift+* - Select current region
Ctrl++ - Insert
Ctrl+- - Delete
Ctrl+1 - Format cells dialog box
Ctrl+2 - Bold
Ctrl+3 - Italic
Ctrl+4 - Underline
Ctrl+5 - Strikethrough
Ctrl+6 - Show/Hide objects
Ctrl+7 - Show/Hide Standard toolbar
Ctrl+8 - Toggle Outline symbols
Ctrl+9 - Hide rows
Ctrl+0 - Hide columns
Ctrl+Shift+( - Unhide rows
Ctrl+Shift+) - Unhide columns
Alt or F10 - Activate the menu
Ctrl+Tab - In toolbar: next toolbar
Shift+Ctrl+Tab - In toolbar: previous toolbar
Ctrl+Tab - In a workbook: activate next workbook
Shift+Ctrl+Tab - In a workbook: activate previous workbook
Tab - Next tool
Shift+Tab - Previous tool
Enter - Do the command
Shift+Ctrl+F - Font Drop down List
Shift+Ctrl+F+F - Font tab of Format Cell Dialog box
Shift+Ctrl+P - Point size Drop down List
Ctrl + E - Align center
Ctrl + J - justify
Ctrl + L - align
Ctrl + R - align right
Alt + Tab - switch applications
Windows + P - Project screen
Windows + E - open file explorer
Windows + D - go to desktop
Windows + M - minimize all windows
Windows + S - search
๐ฅ2๐1
Excel Basics for Data Analytics
Excel sits at the start of most analysis work.
What you use Excel for
โข Cleaning raw data
โข Exploring patterns
โข Quick summaries for teams
Core concepts you must know
โข Data setup
โ Freeze header row. View โ Freeze Top Row.
โ Convert range to table. Ctrl + T.
โ Use proper headers. No merged cells. One value per cell.
โข Data cleaning
โ Remove duplicates. Data โ Remove Duplicates.
โ Trim extra spaces. =TRIM(A2)
โ Convert text to numbers. =VALUE(A2)
โ Fix date format. Format Cells โ Date.
โ Handle blanks. Filter blanks, fill or delete.
โ Find and replace. Ctrl + H.
โข Essential formulas
โ Math and counts
โช SUM. =SUM(A2:A100)
โช AVERAGE. =AVERAGE(A2:A100)
โช MIN. =MIN(A2:A100)
โช MAX. =MAX(A2:A100)
โช COUNT. Counts numbers.
โช COUNTA. Counts non blanks.
โช COUNTBLANK. Counts blanks.
โ Conditional formulas
โช IF. =IF(A2>5000,"High","Low")
โช IFS. Multiple conditions.
โช AND. =AND(A2>5000,B2="West")
โช OR. =OR(A2>5000,A2<1000)
โ Lookup formulas
โช XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)
โช VLOOKUP. Old but common.
โช INDEX + MATCH. Powerful alternative.
โ Text formulas
โช LEFT. =LEFT(A2,4)
โช RIGHT. =RIGHT(A2,2)
โช MID. =MID(A2,2,3)
โช LEN. =LEN(A2)
โช CONCAT or TEXTJOIN.
โช LOWER, UPPER, PROPER.
โ Date formulas
โช TODAY. Current date.
โช NOW. Date and time.
โช YEAR, MONTH, DAY.
โช DATEDIF. Date difference.
โช EOMONTH. Month end.
โข Sorting and filtering
โ Sort by multiple columns.
โ Filter by value, color, condition.
โ Top 10 filter for quick insights.
โข Conditional formatting
โ Highlight duplicates.
โ Color scales for trends.
โ Rules for thresholds. Example. Sales > 10000 in green.
โข Pivot tables
โ Insert โ PivotTable.
โ Rows. Category or Product.
โ Values. Sum, Count, Average.
โ Filters. Date, Region.
โ Refresh after data update.
โข Charts you must know
โ Column. Comparison.
โ Bar. Ranking.
โ Line. Trends over time.
โ Pie. Share or percentage.
โ Combo. Actual vs target.
โข Data validation
โ Dropdown list. Data โ Data Validation โ List.
โ Prevent wrong entries.
โข Useful shortcuts
โ Ctrl + Arrow. Jump data.
โ Ctrl + Shift + Arrow. Select range.
โ Ctrl + 1. Format cells.
โ Ctrl + L. Apply filter.
โ Alt + =. Auto sum.
โ Ctrl + Z / Y. Undo redo.
โข Common analyst mistakes to avoid
โ Merged cells.
โ Hard coded totals.
โ Mixed data types in one column.
โ No backup before cleaning.
โข Daily practice task
โ Download any sales CSV.
โ Clean it.
โ Build one pivot table.
โ Create one chart.
Excel sits at the start of most analysis work.
What you use Excel for
โข Cleaning raw data
โข Exploring patterns
โข Quick summaries for teams
Core concepts you must know
โข Data setup
โ Freeze header row. View โ Freeze Top Row.
โ Convert range to table. Ctrl + T.
โ Use proper headers. No merged cells. One value per cell.
โข Data cleaning
โ Remove duplicates. Data โ Remove Duplicates.
โ Trim extra spaces. =TRIM(A2)
โ Convert text to numbers. =VALUE(A2)
โ Fix date format. Format Cells โ Date.
โ Handle blanks. Filter blanks, fill or delete.
โ Find and replace. Ctrl + H.
โข Essential formulas
โ Math and counts
โช SUM. =SUM(A2:A100)
โช AVERAGE. =AVERAGE(A2:A100)
โช MIN. =MIN(A2:A100)
โช MAX. =MAX(A2:A100)
โช COUNT. Counts numbers.
โช COUNTA. Counts non blanks.
โช COUNTBLANK. Counts blanks.
โ Conditional formulas
โช IF. =IF(A2>5000,"High","Low")
โช IFS. Multiple conditions.
โช AND. =AND(A2>5000,B2="West")
โช OR. =OR(A2>5000,A2<1000)
โ Lookup formulas
โช XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)
โช VLOOKUP. Old but common.
โช INDEX + MATCH. Powerful alternative.
โ Text formulas
โช LEFT. =LEFT(A2,4)
โช RIGHT. =RIGHT(A2,2)
โช MID. =MID(A2,2,3)
โช LEN. =LEN(A2)
โช CONCAT or TEXTJOIN.
โช LOWER, UPPER, PROPER.
โ Date formulas
โช TODAY. Current date.
โช NOW. Date and time.
โช YEAR, MONTH, DAY.
โช DATEDIF. Date difference.
โช EOMONTH. Month end.
โข Sorting and filtering
โ Sort by multiple columns.
โ Filter by value, color, condition.
โ Top 10 filter for quick insights.
โข Conditional formatting
โ Highlight duplicates.
โ Color scales for trends.
โ Rules for thresholds. Example. Sales > 10000 in green.
โข Pivot tables
โ Insert โ PivotTable.
โ Rows. Category or Product.
โ Values. Sum, Count, Average.
โ Filters. Date, Region.
โ Refresh after data update.
โข Charts you must know
โ Column. Comparison.
โ Bar. Ranking.
โ Line. Trends over time.
โ Pie. Share or percentage.
โ Combo. Actual vs target.
โข Data validation
โ Dropdown list. Data โ Data Validation โ List.
โ Prevent wrong entries.
โข Useful shortcuts
โ Ctrl + Arrow. Jump data.
โ Ctrl + Shift + Arrow. Select range.
โ Ctrl + 1. Format cells.
โ Ctrl + L. Apply filter.
โ Alt + =. Auto sum.
โ Ctrl + Z / Y. Undo redo.
โข Common analyst mistakes to avoid
โ Merged cells.
โ Hard coded totals.
โ Mixed data types in one column.
โ No backup before cleaning.
โข Daily practice task
โ Download any sales CSV.
โ Clean it.
โ Build one pivot table.
โ Create one chart.
๐1๐ฅฐ1๐1
๐ Excel Formulas Fundamentals โ Part 9
๐ Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP)
Lookup functions are among the most frequently asked topics in Excel interviews. They help you search for data in one table and return related information from another.
๐ Common use cases:
Employee databases, Product catalogs, Customer records, Sales reports, Inventory management
๐ง 1. What is a Lookup Function?
A lookup function searches for a value and returns a related value from another row or column.
Example:
You have Employee ID, Name, Department data.
Rows: E101 Rahul Sales, E102 Priya HR, E103 Amit IT
Search for E102 and return HR.
๐ 2. VLOOKUP()
VLOOKUP stands for Vertical Lookup.
It searches the first column of a table and returns a value from another column in the same row.
Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,FALSE)
Example:
Data: E101 Rahul 50000, E102 Priya 60000, E103 Amit 70000
Formula: =VLOOKUP("E102",A2:C4,3,FALSE)
Result: 60000
Arguments explained:
lookup_value: Value to search
table_array: Data table
col_index_num: Column number to return
FALSE: Exact match
โ ๏ธ 3. Limitations of VLOOKUP
โ Can only search left to right
โ Column numbers must be counted manually
โ Breaks if columns are inserted or deleted
โ Slower on large datasets
๐ These limitations are why many professionals now prefer INDEX + MATCH or XLOOKUP.
๐ 4. HLOOKUP()
HLOOKUP stands for Horizontal Lookup.
It searches across the first row and returns a value from a specified row.
Syntax:
=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)
Example:
Data across columns: Jan 10000, Feb 12000, Mar 15000
Formula: =HLOOKUP("Feb",A1:D2,2,FALSE)
Result: 12000
๐ Less commonly used because most business data is arranged vertically.
๐ฏ 5. MATCH()
MATCH returns the position of a value in a range.
Syntax:
=MATCH(lookup_value,lookup_array,0)
Example:
List: Rahul, Priya, Amit
Formula: =MATCH("Priya",A2:A4,0)
Result: 2
๐ It returns the position, not the actual value.
๐ 6. INDEX()
INDEX returns the value at a specified row and optionally column.
Syntax:
=INDEX(array,row_num)
Example:
Salary list: 50000, 60000, 70000
Formula: =INDEX(A2:A4,2)
Result: 60000
๐ 7. INDEX + MATCH (Powerful Combination)
This combination is more flexible than VLOOKUP.
Formula:
=INDEX(C2:C4,MATCH("E102",A2:A4,0))
Result: 60000
How It Works:
1. MATCH finds the row number of E102.
2. INDEX returns the salary from that row.
Advantages:
โ Can look left or right
โ Doesn't break when columns are inserted
โ Faster on large datasets
โ Easier to maintain
โญ 8. XLOOKUP()
XLOOKUP is the modern replacement for VLOOKUP.
Syntax:
=XLOOKUP(lookup_value,lookup_array,return_array,"Not Found")
Example:
=XLOOKUP("E102",A2:A4,C2:C4,"Not Found")
Result: 60000
XLOOKUP vs VLOOKUP:
Left Lookup: VLOOKUP No, XLOOKUP Yes
Right Lookup: Both Yes
Default Error Message: VLOOKUP No, XLOOKUP Yes
Column Number Required: VLOOKUP Yes, XLOOKUP No
Easier to Read: VLOOKUP No, XLOOKUP Yes
๐ Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP)
Lookup functions are among the most frequently asked topics in Excel interviews. They help you search for data in one table and return related information from another.
๐ Common use cases:
Employee databases, Product catalogs, Customer records, Sales reports, Inventory management
๐ง 1. What is a Lookup Function?
A lookup function searches for a value and returns a related value from another row or column.
Example:
You have Employee ID, Name, Department data.
Rows: E101 Rahul Sales, E102 Priya HR, E103 Amit IT
Search for E102 and return HR.
๐ 2. VLOOKUP()
VLOOKUP stands for Vertical Lookup.
It searches the first column of a table and returns a value from another column in the same row.
Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,FALSE)
Example:
Data: E101 Rahul 50000, E102 Priya 60000, E103 Amit 70000
Formula: =VLOOKUP("E102",A2:C4,3,FALSE)
Result: 60000
Arguments explained:
lookup_value: Value to search
table_array: Data table
col_index_num: Column number to return
FALSE: Exact match
โ ๏ธ 3. Limitations of VLOOKUP
โ Can only search left to right
โ Column numbers must be counted manually
โ Breaks if columns are inserted or deleted
โ Slower on large datasets
๐ These limitations are why many professionals now prefer INDEX + MATCH or XLOOKUP.
๐ 4. HLOOKUP()
HLOOKUP stands for Horizontal Lookup.
It searches across the first row and returns a value from a specified row.
Syntax:
=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)
Example:
Data across columns: Jan 10000, Feb 12000, Mar 15000
Formula: =HLOOKUP("Feb",A1:D2,2,FALSE)
Result: 12000
๐ Less commonly used because most business data is arranged vertically.
๐ฏ 5. MATCH()
MATCH returns the position of a value in a range.
Syntax:
=MATCH(lookup_value,lookup_array,0)
Example:
List: Rahul, Priya, Amit
Formula: =MATCH("Priya",A2:A4,0)
Result: 2
๐ It returns the position, not the actual value.
๐ 6. INDEX()
INDEX returns the value at a specified row and optionally column.
Syntax:
=INDEX(array,row_num)
Example:
Salary list: 50000, 60000, 70000
Formula: =INDEX(A2:A4,2)
Result: 60000
๐ 7. INDEX + MATCH (Powerful Combination)
This combination is more flexible than VLOOKUP.
Formula:
=INDEX(C2:C4,MATCH("E102",A2:A4,0))
Result: 60000
How It Works:
1. MATCH finds the row number of E102.
2. INDEX returns the salary from that row.
Advantages:
โ Can look left or right
โ Doesn't break when columns are inserted
โ Faster on large datasets
โ Easier to maintain
โญ 8. XLOOKUP()
XLOOKUP is the modern replacement for VLOOKUP.
Syntax:
=XLOOKUP(lookup_value,lookup_array,return_array,"Not Found")
Example:
=XLOOKUP("E102",A2:A4,C2:C4,"Not Found")
Result: 60000
XLOOKUP vs VLOOKUP:
Left Lookup: VLOOKUP No, XLOOKUP Yes
Right Lookup: Both Yes
Default Error Message: VLOOKUP No, XLOOKUP Yes
Column Number Required: VLOOKUP Yes, XLOOKUP No
Easier to Read: VLOOKUP No, XLOOKUP Yes
โค1๐1
๐ If your Excel version supports XLOOKUP, prefer it over VLOOKUP.
๐ผ 9. Real-World Business Scenario
Employee Database: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000
Requirement: Enter an Employee ID and automatically return Name, Department, Salary
Using XLOOKUP:
=XLOOKUP(F2,A2:A4,B2:B4,"Not Found") for Name
=XLOOKUP(F2,A2:A4,C2:C4,"Not Found") for Department
=XLOOKUP(F2,A2:A4,D2:D4,"Not Found") for Salary
๐ This is a common interview and workplace scenario.
๐ฏ Mini Practice Project
Create: Employee_Lookup.xlsx
Data: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000, E104 Neha Finance 65000
Tasks:
โ Find Salary using VLOOKUP
=VLOOKUP("E103",A2:D5,4,FALSE)
โ Find Position using MATCH
=MATCH("Neha",B2:B5,0)
โ Return Department using INDEX + MATCH
=INDEX(C2:C5,MATCH("E102",A2:A5,0))
โ Return Salary using XLOOKUP
=XLOOKUP("E101",A2:A5,D2:D5,"Not Found")
๐ Mastering lookup functions is essential for Excel interviews and day-to-day data analysis.
๐ผ 9. Real-World Business Scenario
Employee Database: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000
Requirement: Enter an Employee ID and automatically return Name, Department, Salary
Using XLOOKUP:
=XLOOKUP(F2,A2:A4,B2:B4,"Not Found") for Name
=XLOOKUP(F2,A2:A4,C2:C4,"Not Found") for Department
=XLOOKUP(F2,A2:A4,D2:D4,"Not Found") for Salary
๐ This is a common interview and workplace scenario.
๐ฏ Mini Practice Project
Create: Employee_Lookup.xlsx
Data: E101 Rahul Sales 50000, E102 Priya HR 60000, E103 Amit IT 70000, E104 Neha Finance 65000
Tasks:
โ Find Salary using VLOOKUP
=VLOOKUP("E103",A2:D5,4,FALSE)
โ Find Position using MATCH
=MATCH("Neha",B2:B5,0)
โ Return Department using INDEX + MATCH
=INDEX(C2:C5,MATCH("E102",A2:A5,0))
โ Return Salary using XLOOKUP
=XLOOKUP("E101",A2:A5,D2:D5,"Not Found")
๐ Mastering lookup functions is essential for Excel interviews and day-to-day data analysis.
โค1๐1๐ฅฐ1๐1
๐ Excel Formulas Fundamentals โ Part 10
๐ Conditional Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMPRODUCT)
Conditional functions allow you to calculate, count, or average data based on one or more conditions. They are among the most commonly used functions by Data Analysts, Financial Analysts, and Business Analysts.
๐ These functions are frequently asked in Excel interviews and used in business reporting.
๐ง 1. SUMIF() โ Sum Based on One Condition
SUMIF() adds values that meet a single condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =SUMIF(A2:A4,"East",B2:B4)
Result: 90000
๐ Use Cases:
Total sales by region, Total expenses by category, Revenue by product
๐ฏ 2. SUMIFS() โ Sum Based on Multiple Conditions
SUMIFS() adds values only when all conditions are met.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000
Formula: =SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Laptop")
Result: 50000
๐ Commonly used in dashboards and business reports.
๐ข 3. COUNTIF() โ Count Based on One Condition
Counts the number of cells that meet a condition.
Syntax:
=COUNTIF(range, criteria)
Example:
Status: Completed, Pending, Completed
Formula: =COUNTIF(A2:A4,"Completed")
Result: 2
๐ Use Cases:
Count completed tasks, Count active customers, Count employees in a department
๐ 4. COUNTIFS() โ Count Based on Multiple Conditions
Counts records that satisfy multiple conditions.
Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop, East Mobile, West Laptop
Formula: =COUNTIFS(A2:A4,"East",B2:B4,"Laptop")
Result: 1
๐ 5. AVERAGEIF() โ Average Based on One Condition
Calculates the average for values matching one condition.
Syntax:
=AVERAGEIF(range, criteria, average_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =AVERAGEIF(A2:A4,"East",B2:B4)
Result: 45000
๐ 6. AVERAGEIFS() โ Average Based on Multiple Conditions
Calculates the average when multiple conditions are satisfied.
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Example:
=AVERAGEIFS(C2:C5,A2:A5,"East",B2:B5,"Laptop")
๐ Useful for finding the average sales of a specific product in a specific region.
โก 7. SUMPRODUCT() โ Multiply and Sum Arrays
SUMPRODUCT() multiplies corresponding values in arrays and returns the sum.
Syntax:
=SUMPRODUCT(array1, array2)
Example:
Data: Quantity 2 Price 500, Quantity 3 Price 700, Quantity 1 Price 1000
Formula: =SUMPRODUCT(A2:A4,B2:B4)
Calculation: (2 ร 500) + (3 ร 700) + (1 ร 1000) = 4100
Result: 4100
๐ Useful for weighted calculations and financial analysis.
๐ข 8. Real-World Scenario โ Sales Dashboard
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000, West Mobile 25000
Total Sales in East
=SUMIF(A2:A5,"East",C2:C5)
Laptop Sales in West
=SUMIFS(C2:C5,A2:A5,"West",B2:B5,"Laptop")
Number of Mobile Orders
=COUNTIF(B2:B5,"Mobile")
๐ Conditional Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMPRODUCT)
Conditional functions allow you to calculate, count, or average data based on one or more conditions. They are among the most commonly used functions by Data Analysts, Financial Analysts, and Business Analysts.
๐ These functions are frequently asked in Excel interviews and used in business reporting.
๐ง 1. SUMIF() โ Sum Based on One Condition
SUMIF() adds values that meet a single condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =SUMIF(A2:A4,"East",B2:B4)
Result: 90000
๐ Use Cases:
Total sales by region, Total expenses by category, Revenue by product
๐ฏ 2. SUMIFS() โ Sum Based on Multiple Conditions
SUMIFS() adds values only when all conditions are met.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000
Formula: =SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Laptop")
Result: 50000
๐ Commonly used in dashboards and business reports.
๐ข 3. COUNTIF() โ Count Based on One Condition
Counts the number of cells that meet a condition.
Syntax:
=COUNTIF(range, criteria)
Example:
Status: Completed, Pending, Completed
Formula: =COUNTIF(A2:A4,"Completed")
Result: 2
๐ Use Cases:
Count completed tasks, Count active customers, Count employees in a department
๐ 4. COUNTIFS() โ Count Based on Multiple Conditions
Counts records that satisfy multiple conditions.
Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Example:
Data: East Laptop, East Mobile, West Laptop
Formula: =COUNTIFS(A2:A4,"East",B2:B4,"Laptop")
Result: 1
๐ 5. AVERAGEIF() โ Average Based on One Condition
Calculates the average for values matching one condition.
Syntax:
=AVERAGEIF(range, criteria, average_range)
Example:
Data: East 50000, West 30000, East 40000
Formula: =AVERAGEIF(A2:A4,"East",B2:B4)
Result: 45000
๐ 6. AVERAGEIFS() โ Average Based on Multiple Conditions
Calculates the average when multiple conditions are satisfied.
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Example:
=AVERAGEIFS(C2:C5,A2:A5,"East",B2:B5,"Laptop")
๐ Useful for finding the average sales of a specific product in a specific region.
โก 7. SUMPRODUCT() โ Multiply and Sum Arrays
SUMPRODUCT() multiplies corresponding values in arrays and returns the sum.
Syntax:
=SUMPRODUCT(array1, array2)
Example:
Data: Quantity 2 Price 500, Quantity 3 Price 700, Quantity 1 Price 1000
Formula: =SUMPRODUCT(A2:A4,B2:B4)
Calculation: (2 ร 500) + (3 ร 700) + (1 ร 1000) = 4100
Result: 4100
๐ Useful for weighted calculations and financial analysis.
๐ข 8. Real-World Scenario โ Sales Dashboard
Data: East Laptop 50000, East Mobile 30000, West Laptop 45000, West Mobile 25000
Total Sales in East
=SUMIF(A2:A5,"East",C2:C5)
Laptop Sales in West
=SUMIFS(C2:C5,A2:A5,"West",B2:B5,"Laptop")
Number of Mobile Orders
=COUNTIF(B2:B5,"Mobile")
โค1๐1
Average Laptop Sales
=AVERAGEIF(B2:B5,"Laptop",C2:C5)
๐ผ 9. Interview Tips
Q: When should you use SUMIF() instead of SUMIFS()?
Answer: Use SUMIF() when there is only one condition. Use SUMIFS() when multiple conditions need to be checked.
Q: What is the difference between COUNTIF() and COUNTIFS()?
Answer: COUNTIF() supports a single condition. COUNTIFS() supports multiple conditions.
Q: Where is SUMPRODUCT() commonly used?
Answer: Weighted averages, Revenue calculations, Inventory valuation, Financial models, Multi-condition calculations
๐ฏ Mini Practice Project
Create: Regional_Sales_Report.xlsx
Data: East Laptop 2 50000 100000, East Mobile 3 20000 60000, West Laptop 1 50000 50000, West Tablet 4 15000 60000
Tasks
โ Total Sales in East
=SUMIF(A2:A5,"East",E2:E5)
โ Total Laptop Sales
=SUMIF(B2:B5,"Laptop",E2:E5)
โ Count Products Sold in West
=COUNTIF(A2:A5,"West")
โ Average Sales in East
=AVERAGEIF(A2:A5,"East",E2:E5)
โ Calculate Total Revenue Using Quantity ร Price
=SUMPRODUCT(C2:C5,D2:D5)
๐ These are some of the most frequently used Excel functions in interviews and day-to-day business analysis.
=AVERAGEIF(B2:B5,"Laptop",C2:C5)
๐ผ 9. Interview Tips
Q: When should you use SUMIF() instead of SUMIFS()?
Answer: Use SUMIF() when there is only one condition. Use SUMIFS() when multiple conditions need to be checked.
Q: What is the difference between COUNTIF() and COUNTIFS()?
Answer: COUNTIF() supports a single condition. COUNTIFS() supports multiple conditions.
Q: Where is SUMPRODUCT() commonly used?
Answer: Weighted averages, Revenue calculations, Inventory valuation, Financial models, Multi-condition calculations
๐ฏ Mini Practice Project
Create: Regional_Sales_Report.xlsx
Data: East Laptop 2 50000 100000, East Mobile 3 20000 60000, West Laptop 1 50000 50000, West Tablet 4 15000 60000
Tasks
โ Total Sales in East
=SUMIF(A2:A5,"East",E2:E5)
โ Total Laptop Sales
=SUMIF(B2:B5,"Laptop",E2:E5)
โ Count Products Sold in West
=COUNTIF(A2:A5,"West")
โ Average Sales in East
=AVERAGEIF(A2:A5,"East",E2:E5)
โ Calculate Total Revenue Using Quantity ร Price
=SUMPRODUCT(C2:C5,D2:D5)
๐ These are some of the most frequently used Excel functions in interviews and day-to-day business analysis.
๐2๐ค1๐ค1
โ
Data Analytics Essentials
TECH SKILLS (NON-NEGOTIABLE)
1๏ธโฃ SQL
โข Joins, Group by, Window functions
โข Handle NULLs and duplicates
Example: LEFT JOIN fits a churn query to include non-churned users
2๏ธโฃ Excel
โข Pivot tables, Lookups, IF logic
โข Clean raw data fast
Example: Reconcile 50k rows in minutes using Pivot tables
3๏ธโฃ Power BI or Tableau
โข Data modeling, Measures, Filters
โข One dashboard, One question
Example: Sales drop by region and month dashboard
4๏ธโฃ Python
โข pandas for cleaning and analysis
โข matplotlib or seaborn for quick visuals
Example: Groupby revenue by cohort
5๏ธโฃ Statistics Basics
โข Mean vs median, Variance, Correlation
โข Know when averages lie
Example: Median salary explains skewed data
SOFT SKILLS (DEAL BREAKERS)
1๏ธโฃ Business Thinking
โข Ask why before how
โข Tie insights to decisions
Example: High churn points to onboarding gaps
2๏ธโฃ Communication
โข Explain insights without jargon
โข One slide, One takeaway
Example: Revenue fell due to fewer repeat users
3๏ธโฃ Problem Framing
โข Convert vague asks into clear questions
โข Define metrics early
Example: What defines an active user?
4๏ธโฃ Attention to Detail
โข Validate numbers
โข Double check logic
โข Small errors kill trust
5๏ธโฃ Stakeholder Handling
โข Listen first
โข Clarify scope
โข Push back with data
๐ฏ Balance both tech and soft skills to grow faster as an analyst
Double Tap โฅ๏ธ For More
TECH SKILLS (NON-NEGOTIABLE)
1๏ธโฃ SQL
โข Joins, Group by, Window functions
โข Handle NULLs and duplicates
Example: LEFT JOIN fits a churn query to include non-churned users
2๏ธโฃ Excel
โข Pivot tables, Lookups, IF logic
โข Clean raw data fast
Example: Reconcile 50k rows in minutes using Pivot tables
3๏ธโฃ Power BI or Tableau
โข Data modeling, Measures, Filters
โข One dashboard, One question
Example: Sales drop by region and month dashboard
4๏ธโฃ Python
โข pandas for cleaning and analysis
โข matplotlib or seaborn for quick visuals
Example: Groupby revenue by cohort
5๏ธโฃ Statistics Basics
โข Mean vs median, Variance, Correlation
โข Know when averages lie
Example: Median salary explains skewed data
SOFT SKILLS (DEAL BREAKERS)
1๏ธโฃ Business Thinking
โข Ask why before how
โข Tie insights to decisions
Example: High churn points to onboarding gaps
2๏ธโฃ Communication
โข Explain insights without jargon
โข One slide, One takeaway
Example: Revenue fell due to fewer repeat users
3๏ธโฃ Problem Framing
โข Convert vague asks into clear questions
โข Define metrics early
Example: What defines an active user?
4๏ธโฃ Attention to Detail
โข Validate numbers
โข Double check logic
โข Small errors kill trust
5๏ธโฃ Stakeholder Handling
โข Listen first
โข Clarify scope
โข Push back with data
๐ฏ Balance both tech and soft skills to grow faster as an analyst
Double Tap โฅ๏ธ For More
โค1โก1๐1