Description

Demonstrate your mastery of Excel by becoming a Microsoft Office Specialist Expert. This course prepares you for the Excel Expert exam for Office 2019 and Office 365 (MO-201).

Tags

Syllabus

Syllabus IntroductionProve your Excel skills as a MOS ExpertWho should take this courseUsing the exercise filesUsing the challenge files1. Microsoft Office Specialist (MOS) Expert Exam OverviewThe different kinds of Microsoft Office Specialist certificationsPrepare for the testOverview of the 2019 MOS Expert exam format and learning objectives2. Manage Workbook Options and SettingsEnable macrosCreate simple macros and copy to another workbookReference data in other workbooksManage workbook versionsProtect workbooks and worksheetsConfigure formula calculation optionsManage commentsConfigure editing and display languagesChallenge: Manage workbook options and settingsSolution: Manage workbook options and setting3. Manage and Format DataFill cells using Flash FillFill cells using advanced Fill Series optionsCreate custom number formatsConfigure data validationGroup and ungroup data and use subtotals and totalsRemove duplicate recordsCreate and manage custom conditional formatting rulesCreate conditional formatting rules using formulasChallenge: Manage and format dataSolution: Manage and format data4. Create Advanced FormulasUse AND(), OR(), and NOT() in nested functionsUse IF() and IFS() in nested functionsUse SWITCH() in nested functionsUse SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functionsUse COUNT() and COUNTIF()Use MAXIFS() and MINIFS() in nested functionsLook up data using VLOOKUP() and HLOOKUP()Use the MATCH() and INDEX() functionsUse NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functionsSummarize data from multiple ranges by using the Consolidate featureUse What-If Analysis Goal Seek and Scenario ManagerCalculate financial data with NPER() and PMT() functionsTroubleshoot formulasChallenge: Create advanced formulasSolution: Create advanced formulas5. Manage Advanced Charts and TablesCreate and modify dual-axis and combo chartsCreate and modify box and whisker and funnel chartsCreate and modify histogram and map chartsCreate and modify sunburst and waterfall chartsCreate and modify PivotTablesCreate and modify PivotChartsChallenge: Manage advanced charts and tablesSolution: Manage advanced charts and tables6. Final ChallengeChallenge: Full-length MOS Expert sample exam for Excel 2019Solution: Full-length MOS Expert sample exam for Excel 2019ConclusionNext steps Read more