Intermediate Microsoft Excel

Skill Level
Intermediate
Duration
12 Hours
Language
English

Overview

This course is tailored for participants who perform regular analysis of business information (product/service performance, operational expenses, payroll/wage processing, bank reconciliations, tracking employees’ info, etc), and expect exceptional figures and trends to be easily spotted as a basis for decisions (e.g. overdue invoices, sales over target, missing/repeated records, etc).

Completing this course will help you with:

  • Data cleansing techniques.
  • Prepping your data for analysis.
  • Data mining and analysis techniques.
  • Visual presentation of data (charts and pre built conditional formatting).

Course Prerequisites

The learner should not just be a regular Excel user but MUST be well equipped with Excel Essentials concepts.

Learning Path

  • How to fill blanks.
  • Checking your data for duplicates.
  • Comparing two lists.
  • Using Text functions (LEFT, RIGHT, TRIM, CONCATENATE, LEN, FIND, PROPER, UPPER, LOWER) to return the number of specified characters from a given text or join two/more text strings; Text to column feature to parse data.
  • Nested IF function for data segmentation.
  • Retrieving and reconciling data made easier by use of VLOOKUP.
  • Creating a drop down list.
  • Enhancing readability with named ranges.
  • COUNTIFS your friend for conditional data analysis.
  • Troubleshooting errors: How to solve #value, #ref, #N/A, #DIV0.
  • Manipulating dates (MONTH, TODAY, YEAR, DATE, YEARFRAC, TEXT, WEEKNUM, EOMONTH).
  • Summarizing data using PivotTables:
    ᵒ Creating PivotTables.
    ᵒ Formatting PivotTable number formats, subtotals and grand totals.
    ᵒ Creating and formatting Pivot Charts.
  • When and how to use combination (dual axis) charts.
  • Applications of Conditional Formatting Highlight records based on
    progress/status.
  • Using data bars as in cell charts to compare values.
  • Goal variance indicators using icon sets.

What people are saying

Are You Ready To Get Started?

Course Categories

Other Courses you may be interested in