+91 – 88617 28680learning@flexmind.co

Microsoft Excel training

Excel training

[vc_row][vc_column][vc_custom_heading text=”Microsoft Excel 2016″ font_container=”tag:h2|text_align:center” use_theme_fonts=”yes”][vc_tta_tabs][vc_tta_section title=”55165AC” tab_id=”1452956078465-87424af1-3ec4″][vc_column_text]

Introduction to Microsoft Excel 2016


Course Duration: 8 Hours


Module 1: Creating a Microsoft Excel Workbook

This module explains how to create workbooks in Microsoft Excel.

Lessons

  • Starting Microsoft Excel
  • Creating a Workbook
  • Saving a Workbook
  • The Status Bar
  • Adding and Deleting Worksheets
  • Copying and Moving Worksheets
  • Changing the Order of Worksheets
  • Splitting the Worksheet Window
  • Closing a Workbook

Lab 1: Creating a Microsoft Excel Workbook

  • Creating a Microsoft Excel Workbook
After completing this module, students will be able to:
  • Start Microsoft Excel.
  • Create a Microsoft Excel workbook.
  • Save a Microsoft Excel workbook.
  • Add and delete Microsoft Excel worksheets.
  • Copy and move worksheets.
  • Split the worksheet window.
  • Close a Microsoft Excel workbook.

 

Module 2: The Ribbon

This module explains how to work with the Ribbon.

Lessons

  • Tabs
  • Groups
  • Commands

Lab 1: Exploring the Ribbon

  • Exploring the Ribbon
After completing this module, students will be able to:
  • Work with tabs.
  • Work with groups.
  • Work with commands.
  • Understand which groups and commands are found on which tabs.

 

Module 3: The Backstage View (The File Menu)

This module explains how to work with the Backstage View aka the File menu.

Lessons

  • Introduction to the Backstage View
  • Opening a Workbook
  • New Workbooks and Excel Templates
  • Printing Worksheets
  • Adding Your Name to Microsoft Excel
  • Managing Workbook Versions

Lab 1: The Backstage View (The File Menu)

  • Open a Workbook
  • Select, Open and Save a Template Agenda
  • Print a Worksheet
After completing this module, students will be able to:
  • Open a Microsoft Excel workbook.
  • Start a new Microsoft Excel workbook.
  • Use Microsoft Excel templates.
  • Print a Microsoft Excel workbook.
  • Personalize your copy of Microsoft Excel.

 

Module 4: The Quick Access Toolbar

This module explains how to work with the Quick Access Toolbar.

Lessons

  • Adding Common Commands
  • Adding Additional Commands with the Customize Dialog Box
  • Adding Ribbon Commands or Groups
  • Placement

Lab 1: The Quick Access Toolbar

  • Customize the Quick Access Toolbar
After completing this module, students will be able to:
  • Add common commands to the Quick Access Toolbar.
  • Add additional commands to the Quick Access Toolbar.
  • Move the Quick Access Toolbar.

 

Module 5: Entering Data in Microsoft Excel Worksheets

This module explains how to how to enter data in Excel worksheets.

Lessons

  • Entering Text
  • Expand Data across Columns
  • Adding and Deleting Cells
  • Adding an Outline
  • Adding a Hyperlink
  • Add WordArt to a Worksheet
  • Using AutoComplete
  • Entering Numbers and Dates
  • Using the Fill Handle

Lab 1: Entering Data in Microsoft Excel Worksheets

  • Entering Text and Using AutoComplete
  • Entering Numbers and Dates
After completing this module, students will be able to:
  • Enter text in Microsoft Excel worksheets.
  • Add or delete cells in worksheets.
  • Add an outline for your data.
  • Enter a hyperlink in a worksheet.
  • Use AutoComplete.
  • Enter numbers and dates in Microsoft Excel worksheets.
  • Use the Fill Handle to add data to cells.

 

Module 6: Formatting Microsoft Excel Worksheets

This module explains how to format Excel worksheets.

Lessons

  • Hiding Worksheets
  • Adding Color to Worksheet Tabs
  • Adding Themes to Workbooks
  • Adding a Watermark
  • The Font Group
  • The Alignment Group
  • The Number Group

Lab 1: Formatting Microsoft Excel Worksheets

  • Customize a Workbook Using Tab Colors and Themes
  • Working with Font Group Commands
  • Working with Alignment Group Commands
  • Working with Number Group Commands
After completing this module, students will be able to:
  • Select a range of cells within a worksheet.
  • Hide worksheets.
  • Add color to workbook tabs.
  • Add themes to worksheets.
  • Apply bold, italics, and underlining to text.
  • Add borders to cells.
  • Change text and cell colors.
  • Change the font and font size.
  • Align text.
  • Wrap text.
  • Indent text.
  • Merge and center text.
  • Change number formats.
  • Change date formats.

 

Module 7: Using Formulas in Microsoft Excel

This module explains how to use formulas in Excel.

Lessons

  • Math Operators and the Order of Operations
  • Entering Formulas
  • AutoSum (and Other Common Auto-Formulas)
  • Copying Formulas and Functions
  • Relative, Absolute, and Mixed Cell References

Lab 1: Using Formulas in Microsoft Excel

  • Working with Formulas
After completing this module, students will be able to:
  • nter basic formulas.
  • Use AutoSum to sum data with one click.
  • Understand absolute, relative, and mixed cell references.
  • Copy formulas and functions.

 

Module 8: Working with Rows and Columns

This module explains how to work with rows and columns.

Lessons

  • Insert lesson titles in a bulleted list.
  • Inserting Rows and Columns
  • Deleting Rows and Columns
  • Transposing Rows and Columns
  • Setting Row Height and Column Width
  • Hiding and Unhiding Rows and Columns
  • Freezing Panes

Lab 1: Working with Rows and Columns

  • Working with Rows and Columns
After completing this module, students will be able to:
  • Insert rows and columns in Microsoft Excel worksheets.
  • Delete rows and columns in Microsoft Excel worksheets.
  • Transpose rows and columns.
  • Change row heights.
  • Change column widths.
  • Hide and unhide rows and columns.
  • Freeze panes.

 

Module 9: Editing Worksheets

This module explains how to edit worksheets.

Lessons

  • Find
  • Find and Replace
  • Using the Clipboard
  • Managing Comments

Lab 1: Editing Worksheets

  • Using Find and Replace
  • Using the Clipboard
After completing this module, students will be able to:
  • Locate information in your worksheet using Find.
  • Use Find and Replace to quickly replace data with other data.
  • Use the Cut, Copy, Paste and Format Painter commands to edit worksheets.
  • Manage comments.

 

Module 10: Finalizing Microsoft Excel Worksheets

This module explains how to finalize worksheets.

Lessons

  • Setting Margins
  • Setting Page Orientation
  • Setting the Print Area
  • Print Scaling (Fit Sheet on One Page)
  • Printing Headings on Each Page/Repeating Headers and Footers
  • Headers and Footers

Lab 1: Finalizing Microsoft Excel Worksheets

  • Preparing to Print
After completing this module, students will be able to:
  • Set margins in Microsoft Excel worksheets.
  • Set the page orientation to portrait or landscape.
  • Set the print area in a worksheet.
  • Scale a worksheet so that all data prints on one page.
  • Add headers and footers to a worksheet.

 

Module 11: Microsoft Excel Features that Were New in 2013

This module explains how to use some of the features that were new in the 2013 version of Excel.

Lessons

  • One Workbook Per Window Feature
  • Using Flash Fill

Lab 1: Microsoft Excel Features that Were New in 2013

  • Using Flash Fill
After completing this module, students will be able to:
  • Use the one workbook per window feature.
  • Use Flash Fill.

 

Module 12: Features New in 2016

This module explains how to use two new features in 2016.

Lessons

  • Tell Me
  • Smart Lookup

[/vc_column_text][/vc_tta_section][vc_tta_section title=”55166AC” tab_id=”1452956078465-ea10243b-f3bf”][vc_column_text]

Intermediate Microsoft Excel 2016


Course Duration: 8 Hours


Module 1: Advanced Formulas

This module explains how to work with formulas.

Lessons

  • Using Named Ranges in Formulas
  • Naming a Single Cell
  • Naming a Range of Cells
  • Naming Multiple Single Cells Quickly
  • Using Formulas That Span Multiple Worksheets
  • Using the IF Function
  • Using AND/OR Functions
  • Using the SUMIF, AVERAGEIF, and COUNTIF Functions
  • Using the PMT Function
  • Using the LOOKUP Function
  • Using the VLOOKUP Function
  • Using the HLOOKUP Function
  • Using the CONCATENATE Function
  • Using the TRANSPOSE Function
  • Using the PROPER, UPPER, and LOWER Functions
  • The UPPER Function
  • The LOWER function
  • The TRIM Function
  • Using the LEFT, RIGHT, and MID Functions
  • The MID Function
  • Using Date Functions
  • Using the NOW and TODAY Functions
  • Creating Scenarios
  • Utilize the Watch Window
  • Consolidate Data
  • Enable Iterative Calculations
  • What-If Analyses
  • Use the Scenario Manager
  • Use Financial Functions

Lab 1: Advanced Formulas

  • Using Named Ranges in Formulas
  • Entering a Formula Using Data in Multiple Worksheets
  • Using the IF Function
  • Using the PMT Function
  • Using the VLOOKUP Function
  • Using the CONCATENATE Function
  • Using the PROPER Function
  • Using the LEFT and RIGHT Functions
  • Using the YEAR, MONTH, and DAY Functions
After completing this module, students will be able to:
  • Name and label cells and ranges of cells.
  • Use names and labels in formulas.
  • Create formulas that span multiple worksheets.
  • Use the conditional IF function and its variants in formulas.
  • Use the PMT function to calculate payments for loans.
  • Use the LOOKUP function.
  • Use the VLOOKUP function.
  • Use the HLOOKUP function.
  • Use the CONCATENATE function to join the contents of numerous cells.
  • Use the TRANSPOSE function.
  • Use the PROPER, UPPER, and LOWER functions to alter the casing of text.
  • Use the LEFT, RIGHT, and MID functions to return characters from the
  • start or end of a string, or a specific number of text characters.
  • Use various date functions.

 

Module 2: Working with Lists

This module explains how to work with lists.

Lessons

  • Converting a List to a Table
  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List
  • Grouping and Ungrouping Data in a List

Lab 1: Working with Lists

  • Converting a List to a Table
  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List
After completing this module, students will be able to:
  • Convert data into tables.
  • Remove duplicates from tables.
  • Sort data in Excel.
  • Filter data in Excel.
  • Use subtotals to automatically total related data.
  • Group and ungroup data.

 

Module 3: Working with Illustrations

This module explains how to work with illustrations.

Lessons

  • Working with Clip Art
  • Using Shapes
  • Working with SmartArt

Lab 1: Working with Illustrations

  • Working with Clip Art
  • Adding Shapes
After completing this module, students will be able to:
  • Add pictures to your worksheets.
  • U use Clip Art to illustrate your worksheets.
  • Insert shapes into Microsoft Excel worksheets.
  • Use SmartArt to illustrate your worksheets.

 

Module 4: Visualizing Your Data

This module explains how to work with charts, objects, and text boxes.

Lessons

  • Creating a Custom Chart Template
  • Inserting Charts
  • Add and Format Objects
  • Insert a Text Box
  • Create a Custom Chart Template

Lab 1: Visualizing Your Data

  • Inserting Charts
  • Editing Charts
After completing this module, students will be able to:
  • Create charts that enable you to visualize your data.
  • Choose what data is displayed in your charts.
  • Show and hide data labels.
  • Show and hide the legend.
  • Show and hide the chart title.
  • Add a picture or shape to a chart.
  • Change the way text displays in a chart.
  • Change the fill color of a chart.
  • Add and format objects in a worksheet.

 

Module 5: Working with Tables

This module explains how to work with tables.

Lessons

  • Format Data as a Table
  • Move between Tables and Ranges
  • Modify Tables
  • Define Titles

Lab 1: Working with Tables

  • Creating and Modifying a Table in Excel
After completing this module, students will be able to:
  • Format data in Excel as a table.
  • Modify Excel tables.

 

Module 6: Advanced Formatting

This module explains how work with formatting options in Excel.

Lessons

  • Applying Conditional Formatting
  • Working with Styles
  • Creating and Modifying Templates

Lab 1: Advanced Formatting

  • Using Conditional Formatting
  • Working with Styles
After completing this module, students will be able to:
  • Use conditional formatting to display cells differently based on their values.
  • Quickly format tables using styles.
  • Format cells using styles.

 

Module 7: Microsoft Excel Features that Were New in 2013

This module explains how to use some features that were new in 2013.

Lessons

  • New Functions in Excel 2013
  • Using New Chart Tools
  • Using the Quick Analysis Tool
  • Using the Chart Recommendation Feature

Lab 1: Microsoft Excel Features that Were New in 2013

  • Using the New Excel Functions
  • Using the New Excel 2013 Chart Tools
  • Using the Quick Analysis Tool
After completing this module, students will be able to:
  • Use the new chart tools in Excel.
  • Use the Quick Analysis tool.
  • Use the Chart Recommendation feature.

 

Module 8: Features New in 2016

This module explains how to use the new charts in 2016.

Lessons

  • New Charts
  • Treemap
  • Sunburst
  • Histogram
  • Waterfall
  • Box and Whisker
  • Pareto

Lab 1: None

  • None
After completing this module, students will be able to:
  • Use the new charts in Excel 2016

[/vc_column_text][/vc_tta_section][vc_tta_section title=”55167AC” tab_id=”1452956371031-cb9ed825-35ea”][vc_column_text]

Advanced Microsoft Excel 2016


Course Duration: 8 Hours


Module 1: Using Pivot Tables

This module explains how to use Pivot Tables.

Lessons

  • Creating Pivot Tables
  • Inserting Slicers
  • Working with Pivot Tables
  • Inserting Pivot Charts
  • More Pivot Table Functionality

Lab 1: Using Pivot Tables

  • Working with Pivot Tables
After completing this module, students will be able to:
  • Use pivot tables to analyze data.
  • Edit pivot tables.
  • Format pivot tables.
  • Insert pivot charts.
  • Use Slicers.

 

Module 2: Auditing Worksheets

This module explains how to audit worksheets.

Lessons

  • Tracing Precedents
  • Tracing Dependents
  • Showing Formulas

Lab 1: Auditing Worksheets

  • Tracing Precedents
  • Tracing Dependents
After completing this module, students will be able to:
  • Audit formulas.
  • Trace precedents to determine which cells impact a specific cell.
  • Trace dependents to determine which cells are impacted by a specific cell.
  • Remove precedent and dependent arrows.
  • Show formulas.
  • Check for errors frequently found in formulas.

 

Module 3: Data Tools

This module explains how to work with data tools.

Lessons

  • Converting Text to Columns
  • Linking to External Data
  • Controlling Calculation Options
  • Data Validation
  • Consolidating Data
  • Goal Seek

Lab 1: Data Tools

  • Converting Text to Columns
  • Using Data Validation
  • Consolidating Data
  • Using Goal Seek
After completing this module, students will be able to:
  • Convert text to columns.
  • Use Data Validation to restrict the type of data that can be entered into a
  • cell.
  • Consolidate data from a number of different ranges into one new range.
  • Use Goal Seek to figure out the value to input to obtain a specific result.

 

Module 4: Working with Others

This module explains how to do various tasks associated with working with others in Excel.

Lessons

  • Protecting Worksheets and Workbooks
  • Tracking Changes
  • Marking a Workbook as Final

Lab 1: Working with Others

  • Password Protecting a Workbook
  • Password Protecting a Worksheet
  • Password Protecting Ranges in a Worksheet
  • Tracking Changes
After completing this module, students will be able to:
  • Password protect a workbook.
  • Password protect a worksheet.
  • Password protect ranges in a worksheet.
  • Use track changes.
  • Accept and reject changes.
  • List all changes on a new sheet.

 

Module 5: Recording and Using Macros

This module explains how to record and use macros.

Lessons

  • Recording Macros
  • Running Macros
  • Editing Macros
  • Adding Macros to the Quick Access Toolbar

Lab 1: Recording and Using Macros

  • Recording a Macro
  • Adding a Macro to the Quick Access Toolbar
After completing this module, students will be able to:
  • Run macros.
  • Edit macros.
  • Add macros to the Quick Access Toolbar.

 

Module 6: Random Useful Items

This module explains how to do some random useful tasks in Excel.

Lessons

  • Sparklines
  • Preparing a Workbook for Internationalization and Accessibility
  • Importing and Exporting Files

Lab 1: Random Useful Items

  • Inserting and Customizing Sparklines
  • Importing Text Files
  • Copying Data from Excel to Word
  • Copying Charts from Excel to Word
After completing this module, students will be able to:
  • Insert sparklines into worksheets.
  • Customize Sparklines..
  • Prepare a workbook for internationalization and accessibility.
  • Import text files into Microsoft Excel.
  • Copy cells from a worksheet into Microsoft Word.
  • Copy and paste a chart into Microsoft Word.

 

Module 7: Microsoft Excel Features that Were New in 2013

This module explains how to use some new features that were new in 2013.

Lessons

  • Using Slicers to Filter Data
  • Creating a PivotTable Timeline
  • Creating a Standalone PivotChart
  • Workspaces in Excel 2013

Lab 1: Microsoft Excel Features that Were New in 2013

  • Filtering Data with Slicers
  • Creating a Timeline
After completing this module, students will be able to:
  • Use slicers to filter data.
  • Create a PivotTable timeline.
  • Create a standalone PivotChart.

 

Module 8: Features New in 2016

This module explains how to some new features new in 2016.

Lessons

  • PivotTable Updates
  • Ink Equations
  • Multi-Select Option in Slicers
  • Quick Shape Formatting
  • Sharing with SharePoint or OneDrive

Lab 1: None

  • None
After completing this module, students will be able to:
  • Understand the enhancements to PivotTables.
  • Use the Ink Equation feature.
  • Use the Multi-Select option in slicers.
  • Use the Quick Shape formatting.
  • Share workbooks.

[/vc_column_text][/vc_tta_section][/vc_tta_tabs][vc_separator][vc_custom_heading text=”Microsoft Excel 2013″ font_container=”tag:h2|text_align:center” use_theme_fonts=”yes”][vc_tta_tabs][vc_tta_section title=”55130AC” tab_id=”1452956581889-467ac1b1-b371″][vc_column_text]

Introduction to Microsoft Excel 2013


Course Duration: 8 Hours


Module 1: Creating a Microsoft Excel Workbook

This module explains how to get started creating Microsoft Excel workbooks.

Lessons

Starting Microsoft Excel

Creating a Workbook

Saving a Workbook

The Status Bar

Adding and Deleting Worksheets

Copying and Moving Worksheets

Changing the Order of Worksheets

Splitting the Worksheet Window

Closing a Workbook

Lab 1: Creating a Microsoft Excel Workbook

  • Creating a Microsoft Excel Workbook
After completing this module, students will be able to:

Start Microsoft Excel.

Create a Microsoft Excel workbook.

Save a Microsoft Excel workbook.

Work with the Status Bar.

Add and delete Microsoft Excel worksheets.

Copy and move worksheets.

Split the worksheet window.

Close a Microsoft Excel workbook.

 

Module 2: The Ribbon

The Ribbon is the display you see at the top of the Microsoft Excel window. It is your primary interface with Excel. It allows you to access most of the commands available to you in Excel. The Ribbon is composed of three parts: Tabs, Groups and Commands. In this lesson, you will learn about all three.

Lessons

Tabs

Groups

Commands

Lab 1: Exploring the Ribbon

  • Exploring the Ribbon
After completing this module, students will be able to:

Work with tabs.

Work with groups.

Work with commands.

Understand which groups and commands are found on which tabs.

 

Module 3: The Backstage View (The File Menu)

This module explains how to work with the Backstage View (The File Menu).

Lessons

  • Introduction to the Backstage View
  • Opening a Workbook
  • New Workbooks and Excel Templates
  • Printing Worksheets
  • Adding Your Name to Microsoft Excel
  • Managing Workbook Versions

Lab 1: Exercises in This Lesson

  • Open a Workbook
  • Select, Open and Save a Template Agenda
  • Print a Worksheet
After completing this module, students will be able to:

Open a Microsoft Excel workbook.

Start a new Microsoft Excel workbook.

Use Microsoft Excel templates.

Print a Microsoft Excel workbook.

Personalize your copy of Microsoft Excel.

 

Module 4: The Quick Access Toolbar

This module explains how to work with the Quick Access Toolbar.

Lessons

Adding Common Commands

Adding Additional Commands with the Customize Dialog Box

Adding Ribbon Commands or Groups

Placement

Lab 1: Customize the Quick Access Toolbar

  • Customize the Quick Access Toolbar
After completing this module, students will be able to:
  • Add common commands to the Quick Access Toolbar.
  • Add additional commands to the Quick Access Toolbar.
  • Move the Quick Access Toolbar.

 

Module 5: Entering Data in Microsoft Excel Worksheets

This module explains how to enter data.

Lessons

  • Entering Text
  • Expand Data across Columns
  • Adding and Deleting Cells
  • Adding an Outline
  • Adding a Hyperlink
  • Add WordArt to a Worksheet
  • Using AutoComplete
  • Entering Numbers and Dates
  • Using the Fill Handle

Lab 1: Exericses in This Lesson

  • Entering Text and Using AutoComplete
  • Entering Numbers and Dates
After completing this module, students will be able to:

Enter text in Microsoft Excel worksheets.

Add or delete cells in worksheets.

Add an outline for your data.

Enter a hyperlink in a worksheet.

Use AutoComplete.

Enter numbers and dates in Microsoft Excel worksheets.

Use the Fill Handle to add data to cells.

 

Module 6: Formatting Microsoft Excel Worksheets

This module explains how to format Microsoft Excel Worksheets.

Lessons

  • Selecting Ranges of Cells
  • Hiding Worksheets
  • Adding Color to Worksheet Tabs
  • Adding Themes to Workbooks
  • Adding a Watermark
  • The Font Group
  • The Alignment Group
  • The Number Group

Lab 1: Exercises in This Lesson

  • Customize a Workbook Using Tab Colors and Themes
  • Working with Font Group Commands
  • Working with Alignment Group Commands
  • Working with Number Group Commands
After completing this module, students will be able to:

Understand the various ways you can format Microsoft Excel worksheets.

Select a range of cells within a worksheet.

Hide worksheets.

Add color to workbook tabs.

Add themes to worksheets.

Add bold, italics, and underlining to text.

Add borders to cells.

Change text and cell colors.

Change the font and font size.

Align text.

Wrap text.

Indent text.

Merge and center text.

Change number formats.

Change date formats.

Show decimals.

 

Module 7: Using Formulas in Microsoft Excel

This is the math lesson, and whether you love or hate math, by the end of this lesson you will agree that Excel makes math easy. We will start by explaining some of the basic concepts you need to understand regarding how Excel does math and then will get into entering formulas.

Lessons

  • Math Operators and the Order of Operations
  • Entering Formulas
  • AutoSum (and Other Common Auto-Formulas)
  • Copying Formulas and Functions
  • Relative, Absolute, and Mixed Cell References

Lab 1: Working with Formulas

  • Working with Formulas
After completing this module, students will be able to:

 Use math operators and the order of operations.

 Enter basic formulas.

 Use AutoSum to sum data with one click.

 Use absolute, relative, and mixed cell references.

  •  Copy formulas and functions.

 

Module 8: Working with Rows and Columns

This module explains how to work with rows and columns.

Lessons

Inserting Rows and Columns

Deleting Rows and Columns

Transposing Rows and Columns

Setting Row Height and Column Width

Hiding and Unhiding Rows and Columns

  • Freezing Panes

Lab 1: Working with Rows and Columns

  • Working with Rows and Columns
After completing this module, students will be able to:

Insert rows and columns in Microsoft Excel worksheets.

Delete rows and columns in Microsoft Excel worksheets.

Transpose rows and columns.

Change row heights.

Change column widths.

Hide and unhide rows and columns.

Freeze panes.

 

Module 9: Editing Worksheets

This module explains how to edit worksheets.

Lessons

  • Find
  • Find and Replace
  • Using the Clipboard
  • Managing Comments

Lab 1: Exercises in This Lesson

  • Using Find and Replace
  • Using the Clipboard
After completing this module, students will be able to:

Locate information in your worksheet using Find.

Use Find and Replace to quickly replace data with other data.

Use the Cut, Copy, Paste, and Format Painter commands to edit worksheets.

 

Module 10: Finalizing Microsoft Excel Worksheets

This module explains how to finalize Microsoft Excel Worksheets.

Lessons

Setting Margins

Setting Page Orientation

Setting the Print Area

Print Scaling (Fit Sheet on One Page)

Printing Headings on Each Page/Repeating Headers and Footers

  • Headers and Footers

Lab 1: Preparing to Print

  • Preparing to Print
After completing this module, students will be able to:

Set margins in Microsoft Excel worksheets.

Set the page orientation to portrait or landscape.

Set the print area in a worksheet.

Scale a worksheet so that all data prints on one page.

Add headers and footers to a worksheet.

 

Module 11: Microsoft Excel 2013 New Features

Excel 2013 provides some new features to make working with spreadsheets easier and more efficient.

Lessons

One Workbook Per Window Feature

  • Using Flash Fill

Lab 1: Using Flash Fill

  • Using Flash Fill
After completing this module, students will be able to:

Learn about the one workbook per window feature.

Learn how to use Flash Fill.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”55131AC” tab_id=”1452956581889-6c8fd777-3cff”][vc_column_text]

Intermediate Microsoft Excel 2013


Course Duration: 8 Hours


Module 1: Advanced Formulas

This module explains how to work with advanced formulas in Excel.

Lessons

  • Using Named Ranges in Formulas
  • Using Formulas That Span Multiple Worksheets
  • Using the IF Function
  • Using the PMT Function
  • Using the LOOKUP Function
  • Using the VLOOKUP Function
  • Using the HLOOKUP Function
  • Using the CONCATENATE Function
  • Using the TRANSPOSE Function
  • Using the PROPER, UPPER, and LOWER Functions
  • Using the LEFT, RIGHT, and MID Functions
  • Using Date Functions
  • Creating Scenarios

Lab 1: Advanced Formulas

  • Using Named Ranges in Formulas
  • Entering a Formula Using Data in Multiple Worksheets
  • Using the IF Function
  • Using the PMT Function
  • Using the VLOOKUP Function
  • Using the CONCATENATE Function
  • Using the PROPER Function
  • Using the LEFT and RIGHT Functions
  • Using the YEAR, MONTH, and DAY Functions
After completing this module, students will be able to:
  • Name and label cells and ranges of cells.
  • Use names and labels in formulas.
  • Create formulas that span multiple worksheets.
  • Use the conditional IF function and its variants in formulas.
  • Use the PMT function to calculate payments for loans.
  • Use the VLOOKUP function.
  • Use the HLOOKUP function.
  • Use the CONCATENATE function to join the contents of numerous cells.
  • Use the PROPER, UPPER, and LOWER functions to alter the casing of text.
  • Use the LEFT, RIGHT, and MID functions to return characters from the start or end of a string, or a specific number of text characters.
  • Use various date functions.

 

Module 2: Working with Lists

This module explains how to work with lists in Microsoft Excel 2013.

Lessons

  • Converting a List to a Table
  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List

Lab 1: Working with Lists

  • Converting a List to a Table
  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List
After completing this module, students will be able to:
  • Convert data into tables.
  • Remove duplicates from tables.
  • Sort data in Excel.
  • Filter data in Excel.
  • Use subtotals to automatically total related data.
  • Group and ungroup data.

 

Module 3: Working with Illustrations

Adding illustrations to Microsoft Excel worksheets is a great way to improve their look and feel, and also to illustrate your points. Fortunately, it’s easy to add pictures (your own or choose from a large library of pictures provided by Microsoft) and shapes.

Lessons

  • Working with Clip Art
  • Using Shapes
  • Working with SmartArt

Lab 1: Working with Illustrations

  • Working with Clip Art
  • Adding Shapes
After completing this module, students will be able to:
  • Add pictures to your worksheets.
  • Use Clip Art to illustrate your worksheets.
  • Insert shapes into Microsoft Excel worksheets.
  • Use SmartArt to illustrate your worksheets.

 

Module 4: Visualizing Your Data

This module explains how to use techniques for visualizing your data.

Lessons

  • Inserting Charts
  • Add and Format Objects
  • Insert a Text Box
  • Create a Custom Chart Template

Lab 1: Visualizing Your Data

  • Inserting Charts
  • Editing Charts
  • Changing the Layout of a Chart
  • Changing the Style of a Chart
  • Adding a Shape to a Chart
  • Adding a Trendline to a Chart
  • Adding a Secondary Axis to a Chart
  • Adding Additional Data Series to a Chart
  • Switch between Rows and Columns in a Chart
  • Positioning a Chart
  • Modifying Chart and Graph Parameters
  • Watching Animation in a Chart
  • Showing, Hiding, or Changing the Location of the Legend in a Chart
  • Show or Hiding the Title of a Chart
  • Changing the Title of a Chart
  • Editing Charts
After completing this module, students will be able to:
  • Create charts that enable you to visualize your data.
  • Choose what data is displayed in your charts.
  • Show and hide data labels.
  • Show and hide the legend.
  • Show and hide the chart title.
  • Add a picture or shape to a chart.
  • Change the way text displays in a chart.
  • Change the fill color of a chart.
  • Add and format objects in a worksheet.

 

Module 5: Working with Tables

This Working with tables in Excel 2013 can help you organize your data. In Excel, you can group data together into a table and then manipulate that table.

Lessons

  • Format Data as a Table
  • Move between Tables and Ranges
  • Modify Tables
  • Define Titles

Lab 1: Working with Tables

  • Creating and Modifying a Table in Excel
After completing this module, students will be able to:
  • Format data in Excel as a table.
  • Modify Excel tables.

 

Module 6: Advanced Formatting

This module explains how to use advanced formatting techniques in Excel 2013.

Lessons

  • Applying Conditional Formatting
  • Working with Styles
  • Creating and Modifying Templates

Lab 1: Advanced Formatting

  • Working with Styles
After completing this module, students will be able to:
  • Use conditional formatting to display cells differently based on their values.
  • Quickly format tables using styles.
  • Format cells using styles.

 

Module 7: Microsoft Excel 2013 New Features

Excel 2013 provides a number of new functions and tools to help make working with worksheets easier and more efficient.

Lessons

  • New Functions in Excel 2013
  • Using New Chart Tools
  • Using the Quick Analysis Tool
  • Using the Chart Recommendation Feature

Lab 1: Microsoft Excel 2013 New Features

  • Using the New Excel Functions
  • Using the New Excel 2013 Chart Tools
  • Using the Quick Analysis Tool
After completing this module, students will be able to:
  • Use new functions available in Excel 2013.
  • Use the new chart tools in Excel 2013.
  • Use the Quick Analysis tool.
  • Use the Chart Recommendation feature.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”55132AC” tab_id=”1452957932147-32a93901-d25e”][vc_column_text]

Advanced Microsoft Excel 2013


Course Duration: 8 Hours


Module 1: Using Pivot Tables

This module explains how to work with pivot tables.

Lessons

  • Creating Pivot Tables
  • Inserting Slicers
  • Working with Pivot Tables
  • Inserting Pivot Charts
  • More Pivot Table Functionality

Lab 1: Using Pivot Tables

  • Working with Pivot Tables
After completing this module, students will be able to:
  • Use pivot tables to analyze data.
  • Edit pivot tables.
  • Format pivot tables.
  • Insert pivot charts.
  • Use Slicers.

 

Module 2: Auditing Worksheets

This module explains how to audit worksheets.

Lessons

  • Tracing Precedents
  • Tracing Dependents
  • Showing Formulas

Lab 1: Auditing Worksheets

  • Tracing Precedents
  • Tracing Dependents
After completing this module, students will be able to:
  • Audit formulas.
  • Trace precedents to determine which cells impact a specific cell.
  • Trace dependents to determine which cells are impacted by a specific cell.
  • Remove precedent and dependent arrows.
  • Show formulas.
  • Check for errors frequently found in formulas.

 

Module 3: Data Tools

Data Tools in Microsoft Excel are simply tools which make it easy to manipulate data. Some of them are intended to save you time by extracting or joining data and others perform complex calculations. This lesson covers the most commonly used Data Tools.

Lessons

  • Converting Text to Columns
  • Linking to External Data
  • Controlling Calculation Options
  • Data Validation
  • Consolidating Data
  • Goal Seek

Lab 1: Data Tools

  • Converting Text to Columns
  • Using Data Validation
  • Consolidating Data
  • Using Goal Seek
After completing this module, students will be able to:
  • Convert text to columns.
  • Use Data Validation to restrict the type of data that can be entered into a cell.
  • Consolidate data from a number of different ranges into one new range.
  • Use Goal Seek to figure out the value to input to obtain a specific result.

 

Module 4: Working with Others

This module explains how to use features that are useful when sharing and working with others.

Lessons

  • Protecting Worksheets and Workbooks
  • Tracking Changes
  • Marking a Workbook as Final

Lab 1: Working with Others

  • Password Protecting a Workbook
  • Password Protecting a Worksheet
  • Password Protecting Ranges in a Worksheet
  • Tracking Changes
After completing this module, students will be able to:
  • Password protect a workbook.
  • Password protect a worksheet.
  • Password protect ranges in a worksheet.
  • Track changes.
  • Accept and reject changes.
  • List all changes on a new sheet.

 

Module 5: Recording and Using Macros

As you work with Excel, you may find that you repeat certain operations frequently. Instead of performing each step of the operation, you can create a macro that stores these steps. You can then run the macro, or let Excel perform the steps for you, saving you both time and effort.

Lessons

  • Recording Macros
  • Running Macros
  • Editing Macros
  • Adding Macros to the Quick Access Toolbar

Lab 1: Recording and Using Macros

  • Recording a Macro
  • Adding a Macro to the Quick Access Toolbar
After completing this module, students will be able to:
  • Record macros.
  • Run macros.
  • Edit macros (kind of).
  • Add macros to the Quick Access Toolbar.
  • Manage macro security.

 

Module 6: Random Useful Items

This module explains how to use some useful tricks and tools in Excel.

Lessons

  • Sparklines
  • Preparing a Workbook for Internationalization and Accessibility
  • Importing and Exporting Files

Lab 1: Random Useful Items

  • Inserting and Customizing Sparklines
  • Importing Text Files
  • Copying Data from Excel to Word
  • Copying Charts from Excel to Word
After completing this module, students will be able to:
  • Insert sparklines into worksheets.
  • Customize sparklines.
  • Import text files into Microsoft Excel.
  • Copy cells from a worksheet into Microsoft Word.
  • Copy and paste a chart into Microsoft Word.

 

Module 7: Microsoft Excel 2013 New Features

This module explains how to use some new features to help you efficiently and effectively create and edit workbooks.

Lessons

  • Using Slicers to Filter Data
  • Creating a PivotTable Timeline
  • Creating a Standalone PivotChart
  • Workspaces in Excel 2013

Lab 1: Microsoft Excel 2013 New Features

  • Filtering Data with Slicers
  • Creating a Timeline
After completing this module, students will be able to:
  • Use slicers to filter data.
  • Create a PivotTable timeline.
  • Create a standalone PivotChart.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”55142AC” tab_id=”1452957995138-d277fd5d-85dc”][vc_column_text]

Microsoft Excel 2013 PowerPivot


Course Duration: 8 Hours


Module 1: Introduction to PowerPivot 2013

This module explains how to enable PowerPivot in Excel 2013. It also reviews the PowerPivot workspace and gives a brief explanation of the various actions available in the workspace.

Lessons

Lab 1: Enable PowerPivot

  • Enable PowerPivot within Microsoft Excel 2013
After completing this module, students will be able to:
  • Enable PowerPivot

 

Module 2: Building Data Models

This module begins with defining a data model and how it is used to create PivotTables and PivotCharts. The Manage Data Model window is discussed in detail in this module. In addition, the various methods for adding data to the model are covered.

Lessons

  • Review Data Models
  • Create Data Models

Lab 1: Create a data model

  • Import data from multiple data sources.
  • Use the manage data model tab to maintain the data model.
  • Refresh data used in PivotTables and PivotCharts.
After completing this module, students will be able to:
  • Import data from multiple data sources.
  • Use the manage data model tab to maintain the data model.
  • Refresh data used in PivotTables and PivotCharts.

 

Module 3: Create a PivotTable and PivotChart

This module covers the difference between tables and charts and the various options that are available. In addition, this module will walk the user through creating a basic PivotTable and PivotChart. PivotTable Tools and PivotChart Tools contextual tabs are covered in this module. These tabs allow further customization to the basic PivotChart and PivotTable.

Lessons

  • Determine chart or table
  • Create a PivotTable
  • Create a PivotChart

Lab 1: Create a basic PivotTable

  • Insert a PivotTable
  • Add field to values drop zone
  • Add field to columns drop zone
  • Add fields to rows drop zone

Lab 2: Use the analyze tab to change your PivotTable

Lab 3: Use the analyze tab to change your PivotTable

  • Change the name of the PivotTable
  • Change the way amounts display
  • Explore the Show group and its effects on your PivotTable

Lab 4: Create a basic PivotChart

  • Insert a PivotChart
  • Add field to values drop zone
  • Add field to legend drop zone
  • Add fields to axis drop zone

Lab 5: Use the analyze tab to change your PivotChart

  • Change the name of the PivotChart
  • Explore the Show group and its effects on your PivotTable

Lab 6: Use the design tab to change your PivotChart

  • Apply Chart Layouts
  • Apply Chart Styles
  • Apply Chart Type

Lab 7: Use the format tab to change your PivotChart

  • Apply Shape Fill format
  • Apply Shape Effects format
  • Apply WordArt Style format
After completing this module, students will be able to:
  • Create and update a PivotTable.
  • Create and update a PivotChart.
  • Use the contextual tabs to customize PivotTables and PivotCharts.

 

Module 4: Use DAX in PowerPivot

This module introduces DAX – Data Analysis eXpressions language and its use in creating complex calculations to be used in PivotTables or PivotCharts. DAX operators and formulas are discussed. Calculated columns and calculated fields are defined and used in this module.

Lessons

  • Review and define DAX
  • Discuss context used in DAX
  • Define calculated columns
  • Define calculated fields

Lab 1: Create a calculated column with data from the same table

  • Create a calculated column
  • Enter formula
  • Rename the column from the default name
  • Review the effect of the column on the PivotTable

Lab 2: Create a calculated column with data from a different table

  • Create a calculated column
  • Use Related expression to link data from another table
  • Rename the column from the default name
  • Review the effect of the column on the PivotTable

Lab 3: Create a calculated field

  • Add calculated field using AutoSum
  • Add calculated field by entering formula
After completing this module, students will be able to:
  • Use DAX to create complex calculations.
  • Create and use a calculated column in a PivotTable/PivotChart.
  • Create and use a calculated field in a PivotTable/PivotChart.
  • Understand the various functions that DAX provides.

 

Module 5: Enhance PivotTables and PivotCharts

This module covers using KPIs (Key Performance Indicators), slicers and hierarchies to further enhance your PivotCharts and PivotTables. In addition the slicer tools options contextual tab is covered. This tab allows customization to the display of the slicers.

Lessons

  • Define key performance indicators (KPIs)
  • Define slicers
  • Define hierarchies

Lab 1: Create a KPI

  • Create a new KPI
  • Create a new PivotTable to use the KPI

Lab 2: Create slicers

  • Create a new slicer
  • Move the slicer
  • Use the slicer to update the PivotTable
  • Review the interaction of two slicers on the PivotTable

Lab 3: Create hierarchies

  • Create a new hierarchy using two different methods
  • Add the hierarchies to the PivotTable
  • Review the effect of the hierarchies on the PivotTable
After completing this module, students will be able to:
  • Create and use KPIs in a PivotTable/PivotChart.
  • Create and use slicers in a PivotTable/PivotChart.
  • Create and use hierarchies in a PivotTable/PivotChart.

[/vc_column_text][/vc_tta_section][/vc_tta_tabs][vc_separator][vc_custom_heading text=”Microsoft Excel 2010″ font_container=”tag:h2|text_align:center” use_theme_fonts=”yes”][vc_tta_tabs][vc_tta_section title=”50476BC” tab_id=”1452956623087-3072ff5a-61ff”][vc_column_text]

Amazing Macros (Microsoft Excel 2007/2010)


Course Duration: 8 Hours


Module 1: Using Macros

Th This module explains how to run a Microsoft Excel® macros. Topics such as how to set macro security, running a macro with shortcut keys and using the visual basic toolbar to run the macro is discussed.

Lessons

Opening a Workbook Containing Macros

Running a Macro

Using a Shortcut Key

Using the Visual Basic Toolbar

Opening the Visual Basic Application Window

Using the Visual Basic Application Window

Lab 1: Using Macros

  • Understand how to execute Macros.
After completing this module, students will be able to:

 

Module 2: Recording Macros

This module explains how to create Macros through usage of recording.

Lessons

Recording a Macro

Assigning a Shortcut Key

Using Relative References

Deleting a Macro

Lab 1: Recording Macros

Recording a Macro

Deleting Macro

After completing this module, students will be able to:
  • Record Macros
  • Assign Shortcut Key to Macro
  • Using Relative Reference in Macro Recording

 

Module 3: Editng Macros

This module explains how to edit macro in the Visual Basic for Application Editor.

Lessons

  • Writing a New Macro
  • Entering Macro Comments
  • Copying Macro Commands
  • Editing Macro Commands
  • Typing Macro Commands
  • Running a Macro from the Code Window
  • Using the Debugger

Lab 1: Editing Macros

  • Editing Macro Commands
After completing this module, students will be able to:
  • Writing a New Macro
  • Entering Macro Comments
  • Copying Macro Commands

 

Module 4: Custom Button In Quick Access Toolbar

This module explains how to assign custom button in QAT.

Lessons

  • Create a custom button
  • Adding a button to Quick Access Toolbar
  • Change a button image
  • Delete a custom button

Lab 1: Custom Button

  • Adding custom Button in QAT
After completing this module, students will be able to:
  • Create a custom button
  • Deleting a custom button

 

Module 5: Adding Form Controls To A Worksheet

This module explains how to add and work with form controls to worksheet.

Lessons

  • Create a Macro button
  • Copy a Macro button
  • Assign Macro to Pasted Macro
  • Format a Macro button
  • Rename a Macro button
  • Moving/Sizing a Macro button
  • Deleting a Macro button

Lab 1: Adding form control to worksheet

  • Create a Macro button
After completing this module, students will be able to:
  • Create a marco button
  • Format a macro button

[/vc_column_text][/vc_tta_section][vc_tta_section title=”50449BC” tab_id=”1452956623087-ccee4f25-5847″][vc_column_text]

Useful Formulas and Functions (Microsoft Excel 2007/2010)


Course Duration: 8 Hours


Module 1: Making Data Work For You

This module explains how to understand and apply Excel basic formulas and functions.

Lessons

  • Formula basics
  • Using cell references
  • Copy formula without changing cell reference
  • Transpose formula
  • Using nested functions

Lab 1: Making Data Work For You

  • Formula basics
  • Using cell references
  • Copy formula without changing cell reference
  • Transpose formula
  • Using nested functions

 

Module 2: Making Data Work For You

After completing this module, students will be able to:
  • Understand and apply formula basics
  • Using cell references
  • Copying formula without changing cell reference
  • Transpose formula using paste special
  • Using nested functions

 

Module 3: Statistical and Logical Functions

This module explains how to use logical functions including CountIf, Sumif, If, IsError.

Lessons

  • Perform calculation using CountIF
  • Perform calculation using SumIF
  • Perform calculation using AverageA
  • Using IF function to prevent division by zero
  • Using IsError function to avoid error display
  • Creating multiple conditions using nested IF
  • Using logical function OR, And

Lab 1: Statistical and Logical Functions

  • Perform calculation using CountIf, SumIf, AverageA
  • Using If function to prevent division by zero
  • Using IsError function to avoid error display
  • Create multiple conditions using nested IF
  • Using logical function OR, AND

 

Module 4: Statistical and Logical Functions

After completing this module, students will be able to:
  • Perform calculation using CountIf, SumIf, AverageA
  • Using If function to prevent division by zero
  • Using IsError function to avoid error display
  • Create multiple conditions using nested IF
  • Using logical function OR, AND

 

Module 5: Lookup and Reference Formulas

This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.

Lessons

  • Using Vlookup to find specific data
  • Using Hlookup to find values in rows
  • Using Match and Index to retrieve data

Lab 1: Lookup and Reference Formulas

  • Using Vlookup to find specific data
  • Using Hlookup to find values in rows
  • Using Match and Index to retrieve data
After completing this module, students will be able to:

 

Module 6: Text Formulas

This module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.

Lessons

  • Changing case of text
  • Append text and numerical value
  • Convert imported text format into numbers
  • Break imported date field into individual columns

Lab 1: Text Formulas

  • Changing case of text using Upper, Lower or Proper formula
  • Append text and numerical value

 

Module 7: Text Formulas

After completing this module, students will be able to:

Lessons

  • Changing case of text using Upper, Lower or Proper formula
  • Append text and numerical value
  • Convert imported text format into numbers
  • Break imported date field into individual columns

 

Module 8: Date and Time Formulas

This module explains how to make use of calculate the difference of two given Date fields and to perform calculation with Time fields.

Lessons

  • Perform addition to Date fields
  • Calculate difference between two Dates
  • Perform calculations with Time fields
  • Copying a Table from a Web Page

Lab 1: Exporting and Importing Data

  • Perform addition and calculate difference between two dates
  • Perform calculations with Time fields

 

Module 9: Exporting and Importing Data

After completing this module, students will be able to:
  • Perform addition and calculate difference between two dates
  • Perform calculations with Time fields

 

Module 10: Array and Database Functions

This module explains how to apply and use advance formula including Array, Frequency and Database functions.

Lessons

  • Using Array Formulas
  • Calculate the difference between Maximum and Minimum values
  • Using Frequency function to Count responses
  • Using Database functions DSum and DCount

Lab 1: Array and Database Functions

  • Using Array Formulas
  • Calculate the difference between Maximum and Minimum values
  • Using Frequency function to Count responses
  • Using Database functions DSum and DCount

 

Module 11: Array and Database Functions

 

Module 12: Array and Database Functions

After completing this module, students will be able to:

 

Module 13: Efficiency Tips

This module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.

Lessons

  • Shortening worksheets names
  • Protecting cells containing formulas
  • Using Data Validation
  • Displaying Formula syntax
  • Using Auditing Tools for errors checking
  • Tracing precedent and dependent
  • Adding comments to worksheet

Lab 1: Efficiency Tips

  • Understand the advantages of shortening worksheet names
  • Protecting cells from amendments by others
  • Using Data validation to improve data entries
  • Using Auditing Tools for checking errors
  • Adding useful notes by commenting worksheet

[/vc_column_text][/vc_tta_section][/vc_tta_tabs][/vc_column][/vc_row]