Microsoft Excel for Finance & Data Analytics

Master these Excel Shortcuts, and you will be irreplaceable at your job.. 1. Autofit All Columns Alt + H + O + I 2. Flash Fill - CTRL+E 3. Pivot Table - Alt + N + V 4. Conditional Formatting - Alt + H + L 5. Auto Spell - F7

Excel interview questions for both data analysts and business analysts 1) What are the basic functions of Microsoft Excel? 2) Explain the difference between a workbook and a worksheet. 3) How would you freeze panes in Excel? 4) Can you name some common keyboard shortcuts in Excel? 5) What is the purpose of VLOOKUP and HLOOKUP? 7) How do you remove duplicate values in Excel? 8) Explain the steps to filter data in Excel. 9) What is the significance of the "IF" function in Excel, and can you provide an example of its use? 10) How would you create a pivot table in Excel? 11) Explain the use of the CONCATENATE function in Excel. 12) How do you create a chart in Excel? 13) Explain the difference between a line chart and a scatter plot. 14) What is conditional formatting, and how can it be applied in Excel? 15) How would you create a dynamic chart that updates with new data? 16) What is the INDEX-MATCH function, and how is it different from VLOOKUP? 17) Can you explain the concept of "PivotTables" and when you would use them? 18) How do you use the "COUNTIF" and "SUMIF" functions in Excel? 19) Explain the purpose of the "What-If Analysis" tools in Excel. 20) What are array formulas, and can you provide an example of their use? Business Analysis Specific: 1) How would you analyze a set of sales data to identify trends and insights? 2) Explain how you might use Excel to perform financial modeling. 3) What Excel features would you use for forecasting and budgeting? 4) How do you handle large datasets in Excel, and what tools or techniques do you use for optimization? 5) What are some common techniques for cleaning and validating data in Excel? 6) How do you identify and handle errors in a dataset using Excel? Scenario-based Questions: 1) Imagine you have a dataset with missing values. How would you approach this problem in Excel? 2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?

📍Basic Excel Functions: SUM(): Adding numbers in a range. AVERAGE(): Calculating the average of a range. COUNT(): Counting the number of cells in a range that contain numbers. COUNTA(): Counting the number of non-empty cells. IF(): Conditional statements to return a value based on a test. VLOOKUP(): Vertical lookup to find a value in a table. HLOOKUP(): Horizontal lookup. MATCH(): Finding the position of an item in a list. INDEX(): Retrieving the value of a cell based on a row and column number

10 Must-Have Excel Skills for Professionals in 2025 1. Pivot Table 2. Xlookup 3. Pivot Charts 4. Flash Fill 5. Quick Analysis 6. Power View 7. Conditional Formatting 8. Moving Columns into Rows 9. IF Formulas 10. Auditing Formulas

Most used functions in Excel

Excel Interview WHAT THEY SAY: "Advanced Excel skills" WHAT THEY ACTUALLY TEST: VLOOKUP/XLOOKUP usage Pivot Table comfort Basic formulas Data cleaning approach Problem-solving process Business Case WHAT THEY SAY: "Data analysis presentation" WHAT THEY REALLY WANT: Can you explain simply Do you ask good questions Can you structure analysis Do you focus on impact Are you confident with data ⤵️

Excel Roadmap For Data Analysts

100 Most Important Excel Formulas

Struggling with messy data in Excel? Here’s how to clean it up: If you’re dealing with unorganized data in Excel, here are some quick steps to clean your sheets: 1️⃣ Trim Function: Eliminate leading & trailing spaces easily. 2️⃣ Remove Duplicates: Use the Data tab feature to delete duplicate rows. 3️⃣ Text to Columns: Separate combined information into different cells. 4️⃣ Filter Blank Cells: Quickly find and manage empty cells. 5️⃣ Clean Function: Remove non-printable characters for a tidier dataset. Like for more ❤️ I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Hope this helps you 😊

1. Ctrl + N: To create a new workbook. 2. Ctrl + O: To open a saved workbook. 3. Ctrl + S: To save a workbook. 4. Ctrl + A: To select all the contents in a workbook. 5. Ctrl + B: To turn highlighted cells bold. 6. Ctrl + C: To copy cells that are highlighted. 7. Ctrl + D: To fill the selected cell with the content of the cell right above. 8. Ctrl + F: To search for anything in a workbook. 9. Ctrl + G: To jump to a certain area with a single command. 10. Ctrl + H: To find and replace cell contents. 11. Ctrl + I: To italicise cell contents. 12. Ctrl + K: To insert a hyperlink in a cell. 13. Ctrl + L: To open the create table dialog box. 14. Ctrl + P: To print a workbook. 15. Ctrl + R: To fill the selected cell with the content of the cell on the left. 16. Ctrl + U: To underline highlighted cells. 17. Ctrl + V: To paste anything that was copied. 18. Ctrl + W: To close your current workbook. 19. Ctrl + Z: To undo the last action. 20. Ctrl + 1: To format the cell contents. 21. Ctrl + 5: To put a strikethrough in a cell. 22. Ctrl + 8: To show the outline symbols. 23. Ctrl + 9: To hide a row. 24. Ctrl + 0: To hide a column. 25. Ctrl + Shift + :: To enter the current time in a cell. 26. Ctrl + ;: To enter the current date in a cell. 27. Ctrl + `: To change the view from displaying cell values to formulas. 28. Ctrl + ‘: To copy the formula from the cell above. 29. Ctrl + -: To delete columns or rows. 30. Ctrl + Shift + =: To insert columns and rows. 31. Ctrl + Shift + ~: To switch between displaying Excel formulas or their values in cell. 32. Ctrl + Shift + @: To apply time formatting. 33. Ctrl + Shift + !: To apply comma formatting. 34. Ctrl + Shift + $: To apply currency formatting. 35. Ctrl + Shift + #: To apply date formatting. 36. Ctrl + Shift + %: To apply percentage formatting. 37. Ctrl + Shift + &: To place borders around the selected cells. 38. Ctrl + Shift + _: To remove a border. 39. Ctrl + -: To delete a selected row or column. 40. Ctrl + Spacebar: To select an entire column. 41. Ctrl + Shift + Spacebar: To select an entire workbook. 42. Ctrl + Home: To redirect to cell A1. 43. Ctrl + Shift + Tab: To switch to the previous workbook. 44. Ctrl + Shift + F: To open the fonts menu under format cells. 45. Ctrl + Shift + O: To select the cells containing comments. 46. Ctrl + Drag: To drag and copy a cell or to a duplicate worksheet. 47. Ctrl + Shift + Drag: To drag and insert copy. 48. Ctrl + Up arrow: To go to the top most cell in a current column. 49. Ctrl + Down arrow: To jump to the last cell in a current column. 50. Ctrl + Right arrow: To go to the last cell in a selected row. 51. Ctrl + Left arrow: To jump back to the first cell in a selected row. 52. Ctrl + End: To go to the last cell in a workbook. 53. Alt + Page down: To move the screen towards the right. 54. Alt + Page Up: To move the screen towards the left. 55. Ctrl + F2: To open the print preview window. 56. Ctrl + F1: To expand or collapse the ribbon. 57. Alt: To open the access keys. 58. Tab: Move to the next cell. 59. Alt + F + T: To open the options. 60. Alt + Down arrow: To activate filters for cells. 61. F2: To edit a cell. 62. F3: To paste a cell name if the cells have been named. 63. Shift + F2: To add or edit a cell comment. 64. Alt + H + H: To select a fill colour. 65. Alt + H + B: To add a border. 66. Ctrl + 9: To hide the selected rows. 67. Ctrl + 0: To hide the selected columns. 68. Esc: To cancel an entry. 69. Enter: To complete the entry in a cell and move to the next one. 70. Shift + Right arrow: To extend the cell selection to the right. 71. Shift + Left arrow: To extend the cell selection to the left. 72. Shift + Space: To select the entire row. 73. Page up/ down: To move the screen up or down. 74. Alt + H: To go to the Home tab in Ribbon. 75. Alt + N: To go to the Insert tab in Ribbon.

10 Must-Know Excel Time Functions ⏱️ 🟢 NOW() – Current date & time. 🟢 TODAY() – Current date only. 🟢 HOUR() – Extracts the hour. 🟢 MINUTE() – Extracts the minute. 🟢 SECOND() – Extracts the second. 🟢 DATE() – Returns a specific date. 🟢 TIME() – Returns a specific time. 🟢 TEXT() – Format date/time. 🟢 DATEDIF() – Difference between dates. 🟢 NETWORKDAYS() – Counts workdays between dates.