Microsoft Excel Essentials

 

(Course Delivery:18-20 Hours, Online)

Pre-Requisite for the course:Candidate is expected to have reasonable familiarity with Excel or any other spreadsheet program and writing some Basic spreadsheet Formulas.

PART-1         ABOUT MICROSOFT EXCEL

• Understand what EXCEL is and EXCEL’s journey so far.
• What is EXCEL used for, different scenarios.
• Office 365 Excel for Desktop Vs Excel Online.
• ‘Modern Excel’ architecture intro.

PART-2         EXCEL FUNCTIONS AND FORMULAS

• Conventional Excel Formulas.
• Formula Categories.
• Formulas Syntax and learn to use intellisense help in functions.
• Some Math, Statistical, TEXT and Logical functions.
• Lookup formulas.
• Conditional statements and if simple ‘if’ statements.
• Nested ‘ifs’.
• SUMIFS and COUNTIFS functions.
• New ARRAY and SPILL functions (2020).
• Create a dynamic report with Array Functions.
• Formula Auditing& Watch Window.
• Dealing with formulas Errors and external links in workbook.
• Using Named Ranges in formulas.

PART -3        ORGANIZE YOUR DATA FOR ANALYSIS.

• Best practices for organizing your data.
• Bring everything together with lookup functions VLOOKUP, HLOOKUP and XLOOKUP.
• Filtering data with FILTER option. What is happening behind the scenes?
• SUBTOTAL function to total visible rows.
• Re-visit FILTER function (Array function)
• Transform your data range into a TABLE. (Insert TABLE)
• Advantages of using a ‘TABLE’.

PART -4        PIVOT TABLE.

• Why a Pivot Table?
• Create your first Pivot Table.
• Manipulate Pivot Table report with the fields.
• Contextual Menu, Refresh and Areas on Pivot Table.
• Pivot Cache and what is happening behind the scenes.
• Add Calculated Field in Pivot Table.
• Filter your Pivot Table report with Slicers.
• ‘Grand Total’, ‘Sub Total’, ‘Show Total As’options.
• Quick discussion on –
  • Using External Data as Source and
  • Using DATA MODEL as a source.
• EXCEL Charts

PART -5        END OF THE COURSE SUMMARY AND CONCLUSION.