Call : (+91) 968636 4243
Mail : info@EncartaLabs.com
EncartaLabs

Microsoft Excel

In Microsoft Excel - Essentials (MO-200) training course, you will gain the knowledge and the confidence to create, format, enhance and print simple spreadsheets. Also learn to use formulas and advanced functions, create and format tables, sort and filter, visualize your data with charts.

The Microsoft Excel - Advanced (MO-201) training course will teach you how to analyze and report on data frequently, work in collaboration with others to deliver actionable organizational intelligence, and keep and maintain workbooks for all manner of purposes. You will also learn how to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions that will put the full power of Excel right at your fingertips.

By attending Microsoft Excel - Essentials (MO-200) workshop, delegates will learn to:

  • Perform calculations
  • Modify a worksheet
  • Format a worksheet
  • Print workbooks
  • Manage workbooks
  • Work with functions and lists
  • Create advanced formulas
  • Work with custom view

By attending Microsoft Excel - Advanced (MO-201) workshop, delegates will learn to:

  • Perform advanced data analysis, collaborate on workbooks with other users, and automate workbook functionality.

For Microsoft Excel - Essentials

  • Solid working knowledge of Windows.
For Microsoft Excel - Advanced
  • Attend Microsoft Excel - Essentials course, or equivalent working knowledge or equivalent experience.

The Microsoft Excel - Essentials (MO-200) class is ideal for:

  • Who wish to gain a foundational understanding of Microsoft Office Excel to create and work with spreadsheets.
The Microsoft Excel - Advanced (MO-201) class is ideal for:
  • Who are experienced Excel users and have a desire or need to advance their skills in working with some of the more advanced Excel features.

COURSE AGENDA

Microsoft Excel - Essentials (MO-200)
(Duration : 2 Days)

1

Create, Open and Save Workbooks

  • What is Excel?
  • Create a New Blank Workbook
  • Create a Workbook From a Template
  • Open Excel Files
  • Get Help
  • Save Workbooks
  • Navigate a Workbook
2

Explore the User Interface

  • Backstage View
  • Ribbon Overview
  • Mini Toolbar
  • Status Bar
  • Shortcut Key Tips
  • Contextual Tabs
3

Work with Data

  • Enter, Edit and Delete Data
  • Data Selection Methods
  • Use Autofill and Autocomplete
  • Cut, Copy, Paste and Paste Special
  • Resize Columns and Rows
  • Insert and Delete Columns, Rows, Cells
  • Use Undo, Redo and Repeat
  • Spellcheck and AutoCorrect
  • Use Find, Replace and Go To
  • Use SmartTags and Options Buttons
  • Add, Edit, and Remove Comments
4

Format A Workbook

  • Format Using the Home Ribbon
  • Format Using the Mini-Toolbar
  • Format Using Dialog Boxes
  • Use and Modify Conditional Formatting
  • Use the Format Painter
  • Create and Modify Styles
  • Add, Name, Move, Delete and Colour Sheets
5

Charts

  • Create and Format a Chart
  • Modify Chart Layout and Structure
  • Change Chart Types, Options, Location and
  • Data Source
  • Save a Chart as a Template
6

Printing and Viewing A Workbook

  • Use the View Ribbon
  • Split and Freeze
  • Manage Multiple Windows
  • Use Page Layout, Print Preview and Basic
  • Print Options
  • Add Headers and Footers
7

Creating Formulas

  • Apply Range Names
  • Adding Range Names Using the Name Box
  • Adding Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas
8

Use Specialized Functions

  • Function Categories
  • The Excel Function Reference
  • Function Syntax
  • Function Entry Dialog Boxes
  • Using Nested Functions
  • Automatic Workbook Calculations
  • Showing and Hiding Formulas
  • Enabling Iterative Calculations
9

Use Text Functions

  • The LEFT and RIGHT Functions
  • The MID Function
  • The LEN Function
  • The TRIM Function
  • The UPPER, LOWER, and PROPER Functions
  • The CONCATENATE Function
  • The TRANSPOSE Function
10

Use Logical Functions

  • Logical Operators
  • The AND Function
  • The OR Function
  • The IF Function
11

Lookup / Date / Financial Functions

  • Lookup Functions
    • The LOOKUP Function
    • The VLOOKUP Function
    • The HLOOKUP Function
  • Use Date Functions
    • The TODAY Function
    • The NOW Function
    • Serializing Dates and Times with Functions
  • Use Financial Functions
    • The IPMT Function
    • The PPMT Function
    • The NPV Function
    • The FV Function
12

Create and Modify Tables

  • Table Components
  • The Create Table Dialog Box
  • The Table Tools – Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options
13

Sort and Filter Data

  • The Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Filter Operators
  • Removing Duplicate Values
14

Use Subtotal and Database Functions to Calculate Data

  • SUBTOTAL Functions
  • The Subtotal Dialog Box
  • Summary Functions in Tables
  • Database Functions
15

Visualizing Data with Charts

  • Create Charts
  • Chart Types
  • Chart Insertion Methods
  • Resizing and Moving the Chart
  • Adding Additional Data
  • Switching Between Rows and Columns
16

Modify and Format Charts

  • The Difference Between Modifying and Formatting
    • Chart Elements
    • Minimize Extraneous Chart Elements
    • The Chart Tools Contextual Tabs
    • Formatting the Chart with a Style
    • Adding a Legend to the Chart
  • Create a Trendline
    • Trendlines
    • Types of Trendlines
    • Adding a Trendline
    • The Format Trendline Task Pane
  • Create Advanced Charts
    • Dual Axis Charts
    • Creating Custom Chart Templates
    • Viewing Chart Animations
Microsoft Excel - Advanced (MO-201)
(Duration : 1 Day)

1

Enhancing Workbooks

2

Customize Workbooks

  • Comments
  • Hyperlinks
  • Watermarks
  • Background Pictures
3

Manage Themes

  • About Themes
  • Customizing Themes
4

Create and Use Templates

  • Template Types
  • Creating a Template
  • Modifying a Template
5

Protect Files

  • Recovering Lost Data
  • The Changes Group
  • Worksheet and Workbook Protection
  • The Protect Worksheet Option
  • The Protect Workbook Option
6

Preparing a Workbook for Multiple Audiences

  • Displaying Data in Multiple International Formats
  • Utilize International Symbols
  • Modifying Worksheets Using the Accessibility Checker
  • Managing Fonts
7

Auditing Worksheets

8

Trace Cells

  • The Trace Cells Feature
  • Tracer Arrows
  • Troubleshoot Invalid Data and Formula Errors
  • Invalid Data
  • The Error Checking Command
  • Error Types
9

Watch and Evaluate Formulas

  • The Watch Window
  • Formula Evaluation
10

Create a Data List Outline

  • Outlines
  • The Outline Group
11

VLOOKUP Review

12

Working with Multiple Worksheets and Workbooks

13

Consolidate Data

  • Data Consolidation
  • The Consolidate Dialog Box
  • Consolidation Functions
14

Link Cells in Different Workbooks

  • External References
15

Merge Workbooks

  • The Compare and Merge Workbooks Feature
16

Export Excel Data

  • The Export Process
17

Import a Delimited Text File

  • The Import Process
  • The Get External Data Group
  • Delimited Text Files
  • Methods of Importing Text Files
18

Integrate Excel Data with the Web

  • The File Publishing Process
  • Publish as Web Page Dialog Box
19

Create a Web Query

  • Web Queries
  • The New Web Query Dialog Box
20

Analyzing Data with PivotTables, Slicers, and PivotCharts

21

Create a PivotTable

  • PivotTables
  • Start with Questions, End with Structure
  • The Create PivotTable Dialog Box
  • The PivotTable Fields Pane
  • Summarize Data in a PivotTable
  • The “Show Values As” Functionality of a PivotTable
  • External Data
  • PowerPivot
  • PowerPivot Functions
22

Filter Data by Using Slicers

  • Slicers
  • The Insert Slicers Dialog Box
23

Analyze Data with PivotCharts

  • PivotCharts
  • Creating PivotCharts
  • Applying a Style to a PivotChart
24

Automating Worksheet Functionality

25

Update Workbook Properties

  • Workbook Properties
26

Create and Edit a Macro

  • Macros
  • The Record Macro Dialog Box
  • Naming Macros
  • Visual Basic for Applications
  • Copying Macros Between Workbooks
  • Macro Security Settings
27

Apply Conditional Formatting

  • Conditional Formatting
  • Conditional Formats
  • The Conditional Formatting Rules Manager Dialog Box
  • The New Formatting Rule Dialog Box
  • Clear Rules
28

Add Data Validation Criteria

  • Data Validation
  • The Data Validation Dialog Box

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 6,000 various courses on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting https://www.encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top
Notice
X