Microsoft Excel for Finance & Data Analytics

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.

👉Basic Excel Commands🚀 🚀Use these shortcuts to grow your career😎 Ctrl + C: Copy Ctrl + V: Paste Ctrl + X: Cut Ctrl + Z: Undo Ctrl + Y: Redo Ctrl + S: Save Ctrl + A: Select all Ctrl + F: Find Ctrl + H: Replace Ctrl + N: New workbook Ctrl + O: Open workbook #excel

Roadmap to learn EXCEL Step 1 - Master Basic Formulas Step 2 - Data Visualization Step 3 - Pivot Tables and Analysis Step 4 - Advanced Functions Step 5 - Automation with Macros Step 6 - Power Query and Power Pivot Step 7 - Collaboration and Sharing Step 8 - Excel Tips and Tricks #excel

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

Complete Roadmap to learn Excel in 2025 👇👇 1. Basic Excel Skills:    - Familiarize yourself with Excel's interface and navigation.    - Learn basic formulas (SUM, AVERAGE, COUNT, etc.).    - Understand cell referencing (absolute vs. relative). 2. Data Entry and Formatting:    - Practice entering and formatting data efficiently.    - Explore cell formatting options for a clean and organized dataset. 3. Advanced Formulas:    - Master more advanced formulas like VLOOKUP, HLOOKUP, INDEX-MATCH.    - Learn logical functions (IF, AND, OR).    - Understand array formulas for complex calculations. 4. Pivot Tables:    - Gain proficiency in creating Pivot Tables for data summarization.    - Learn to customize and format Pivot Tables effectively. 5. Data Cleaning:    - Acquire skills in cleaning and transforming data.    - Explore text-to-columns, remove duplicates, and data validation. 6. Charts and Graphs:    - Learn to create various charts (bar, line, pie) for data visualization.    - Understand chart formatting and customization. 7. Dashboard Creation:    - Combine charts and tables to build basic dashboards.    - Explore dynamic dashboards using Excel features. 8. Macros and VBA:    - Dive into basic automation using Excel macros.    - Learn Visual Basic for Applications (VBA) for more advanced automation. 9. Power Query:    - Introduce yourself to Power Query for enhanced data manipulation.    - Learn to import, transform, and load data efficiently. 10. Advanced Excel Techniques:    - Explore advanced features like Goal Seek, Solver, and Scenario Manager.    - Master the use of data tables for sensitivity analysis. 11. Real-world Projects:    - Apply your skills to real-world projects or datasets.    - Practice solving analytical problems using Excel. Remember to practice consistently, as hands-on experience is crucial for mastering Excel. This roadmap will provide a solid foundation for your journey into data analysis using Excel. 5️⃣ Free resources to practice Excel https://www.w3schools.com/EXCEL/index.php https://bit.ly/3PSorPT http://learn.microsoft.com/en-gb/training/paths/modern-analytics/ https://t.me/excel_analyst/52 https://excel-practice-online.com/ Join for more: https://t.me/free4unow_backup ENJOY LEARNING 👍👍

Excel Cheatsheet

🗂Performing calculations on data Excel workbooks gives you a handy place to store and organize your data, but you can also do a lot more with your data in Excel. One important task you can perform is to calculate totals for the values in a series of related cells. You can also use Excel to discover other information about the data you select, such as the maximum or minimum value in a group of cells. Regardless of your needs, Excel gives you the ability to find the information you want. And if you make an error, you can find the cause and correct it quickly. Often, you can’t access the information you want without referencing more than one cell, and it’s also often true that you’ll use the data in the same group of cells for more than one calculation. Excel makes it easy to reference several cells at the same time, so that you can define your calculations quickly. Keep tuned as i guide you through procedures related to streamlining references to groups of data on your worksheets and creating and correcting formulas that summarize an organization’s business operations.

Perfect 😃

Hey guys, Today, I’m covering some Excel interview questions that often pop up in data analyst roles 👇👇 1. What are the most common functions used in Excel for data analysis? - SUM(): Adds up values in a range. - AVERAGE(): Finds the mean of a range of numbers. - VLOOKUP() / XLOOKUP(): Searches for a value in a table and returns a related value. - INDEX-MATCH: A more flexible alternative to VLOOKUP, allowing lookups in any direction. - IF(): Performs logical tests and returns one value if TRUE, another if FALSE. - COUNTIF(): Counts the number of cells that meet a specific condition. - PivotTables: For summarizing, analyzing, and exploring large datasets. 2. What is the difference between VLOOKUP and XLOOKUP? - VLOOKUP is an older function used to find data in a vertical column and return a value from another column to the right. Example:

  =VLOOKUP("A2", B2:D10, 3, FALSE)
  

- XLOOKUP is more powerful, offering the flexibility to search both vertically and horizontally, and it doesn’t require the lookup value to be in the first column. Example:

  =XLOOKUP(A2, B2:B10, C2:C10)
  

Tip: Explain the limitations of VLOOKUP (like not being able to search left or needing sorted data for approximate matches) and how XLOOKUP overcomes them. 3. How do you create a PivotTable in Excel, and why is it useful? A PivotTable allows you to summarize large amounts of data quickly. Here’s how to create one: 1. Select your data. 2. Go to the Insert tab and click on PivotTable. 3. Choose where to place the PivotTable. 4. Drag and drop fields into the Rows, Columns, Values, and Filters sections. 4. What is conditional formatting, and how do you use it? Conditional formatting is used to change the appearance of cells based on their content. It helps highlight trends, patterns, and outliers. For example, to highlight cells greater than 1000: 1. Select the range of cells. 2. Go to the Home tab, click on Conditional Formatting. 3. Choose Highlight Cell Rules > Greater Than and enter 1000. 4. Choose a format (e.g., cell color) to apply. 5. How do you handle large datasets in Excel without slowing it down? Here are some strategies to improve efficiency: - Turn off automatic calculations: Use manual recalculation to prevent Excel from recalculating formulas every time you make a change.

  File > Options > Formulas > Calculation Options > Manual
  

- Use fewer volatile functions: Functions like NOW(), TODAY(), and INDIRECT() recalculate every time a change is made. - Use tables instead of ranges: Structured references in tables are more efficient. - Split large datasets: If feasible, split your data across multiple sheets or workbooks. - Remove unnecessary formatting: Too much formatting can bloat file size and slow down processing. 6. How do you use Excel for data cleaning? Data cleaning is one of the first and most important steps in data analysis, and Excel provides multiple ways to do this: - Remove duplicates: Easily eliminate duplicate entries.

- Text to Columns: Split data in one column into multiple columns (e.g., splitting full names into first and last names).

- TRIM(): Remove extra spaces from text.

- FIND() and SUBSTITUTE(): For locating and replacing specific characters or substrings. 7. What are some advanced Excel functions you’ve used for data analysis? Aside from the basics, some advanced Excel functions you might mention include: - ARRAYFORMULA(): Allows multiple calculations to be performed at once. - OFFSET(): Returns a range that is offset from a starting point. - FORECAST(): Predicts future values based on historical data. - POWER QUERY: For data extraction, transformation, and loading (ETL) tasks. I have curated best 80+ top-notch Data Analytics Resources 👇👇 https://topmate.io/analyst/861634 Like for more Interview Resources ♥️ Share with credits: https://t.me/sqlspecialist Hope it helps :)

🗂Formating Cells Know how to: ~ Change The font Size , ~ Change Font style , ~ Add a Table ~ Do alignment of data...

🗂Formatting Cells Deleting a cell’s contents doesn’t delete the cell’s formatting. To delete a selected cell’s formatting, on the Home tab, in the Editing group, click the Clear button (which looks like an eraser), and then click Clear Formats. Clicking Clear All from the same list will remove the cell’s contents and formatting. 💥Many of the formatting-related buttons on the ribbon have arrows at their right edges. Clicking the arrow displays a list of options for that button.

🗂Summary Points to Note, 📩To summarize data by using the IF function 1. Click the cell in which you want to enter the formula. 2. Enter a formula with the syntax =IF(Logical_test, Value_if_true, Value_if_false) where: • Logical_test is the logical test to be performed. • Value_if_true is the value the formula returns if the test is true. • Value_if_false is the value the formula returns if the test is false. 📩To create a formula by using the Insert Function dialog box 1. To the left of the formula bar, click the Insert Function button. 2. In the Insert Function dialog box, click the function you want to use in your formula. 3. Click OK. 4. In the Function Arguments dialog box, define the arguments for the function you chose. 5. Click OK.......