Challenge: Create advanced formulas - Microsoft Excel Tutorial

Contents

• Introduction Introduction

  • Prove your Excel skills as a MOS Expert

    58s
  • Who should take this course

    50s
  • Using the exercise files

    59s
  • Using the challenge files

    46s
• 1. Microsoft Office Specialist (MOS) Expert Exam Overview 1. Microsoft Office Specialist (MOS) Expert Exam Overview

  • (Locked)
    The different kinds of Microsoft Office Specialist certifications

    3m 10s
  • (Locked)
    Prepare for the test

    6m 20s
• 2. Manage Workbook Options and Settings 2. Manage Workbook Options and Settings

  • Manage macros

    6m 43s
  • (Locked)
    Reference data in other workbooks

    7m 15s
  • (Locked)
    Manage workbook versions

    5m 12s
  • (Locked)
    Protect workbooks and worksheets

    7m 20s
  • (Locked)
    Configure formula calculation options

    3m 22s
  • (Locked)
    Challenge: Manage workbook options and settings

    1m 7s
  • (Locked)
    Solution: Manage workbook options and settings

    7m 37s
• 3. Manage and Format Data 3. Manage and Format Data

  • Fill cells using Flash Fill

    2m 45s
  • (Locked)
    Fill cells using advanced Fill Series options

    5m 47s
  • (Locked)
    Create custom number formats

    6m 41s
  • (Locked)
    Configure data validation

    7m 1s
  • (Locked)
    Group and ungroup, use subtotals and totals

    4m 51s
  • (Locked)
    Remove duplicate records

    3m 14s
  • (Locked)
    Create and manage custom conditional formatting rules

    8m 20s
  • (Locked)
    Create conditional formatting rules using formulas

    7m 29s
  • (Locked)
    Generate numeric data by using RANDARRAY()

    6m 26s
  • (Locked)
    Challenge: Manage and format data

    33s
  • (Locked)
    Solution: Manage and format data

    11m 24s
• 4. Create Advanced Formulas 4. Create Advanced Formulas

  • Use AND(), OR(), and NOT() in nested functions

    4m 56s
  • (Locked)
    Use IF() and IFS() in nested functions

    5m 43s
  • (Locked)
    Use SWITCH() in nested functions

    4m 23s
  • (Locked)
    Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions

    4m 43s
  • (Locked)
    Use COUNTIF(), COUNTIFS(), MAXIFS, and MINIFS

    5m 51s
  • (Locked)
    Look up data using XLOOKUP(), VLOOKUP(), HLOOKUP(), INDEX(), and MATCH()

    7m 19s
  • (Locked)
    Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions

    8m 31s
  • (Locked)
    Summarize data from multiple ranges by using the Consolidate feature

    4m 46s
  • (Locked)
    Use What-If Analysis, Goal Seek, and Scenario Manager

    5m 56s
  • (Locked)
    Calculate financial data with NPER() and PMT() functions

    4m 19s
  • (Locked)
    Use the SORTBY() and FILTER() functions

    4m 30s
  • (Locked)
    Troubleshoot formulas

    8m 16s
  • (Locked)
    Challenge: Create advanced formulas

    30s
  • (Locked)
    Solution: Create advanced formulas

    14m 31s
• 5. Manage Advanced Charts and Tables 5. Manage Advanced Charts and Tables

  • Create and modify dual-axis and combo charts

    4m 16s
  • (Locked)
    Create and modify box and whisker and funnel charts

    2m 48s
  • (Locked)
    Create and modify Sunburst, Histogram, and Waterfall charts

    6m 45s
  • (Locked)
    Create PivotTables and modify field selections and options

    6m 47s
  • (Locked)
    Create and modify PivotCharts

    5m 15s
  • (Locked)
    Challenge: Manage advanced charts and tables

    28s
  • (Locked)
    Solution: Manage advanced charts and tables

    5m 23s
• 6. Final Challenge 6. Final Challenge

  • (Locked)
    Challenge: Full-length MOS Excel Expert sample exam

    45s
  • (Locked)
    Solution: Full-length MOS Excel Expert sample exam

    20m 43s