Microsoft Excel Training

Learn to use Microsoft Excel 2016 to improve productivity and efficiency. NR Computer Learning Center (NRCLC) offers Basic, Intermediate and Advance level hands-on training in Excel 2016. NRCLC also offers classes for students preparing for Microsoft Office Specialist (MOS) Exam. Schedule

Basic

Microsoft 2016 Basic – Introduction to Microsoft Excel 2016

In this course, you will learn to create and use an Excel spreadsheet. You will learn how to move through the cells; enter data in a cell; set text, cell and page properties; insert and delete column or row; copy and paste or move data from one cell to another cell; automatically fill data in cell using AutoFill; change page orientation, add customized header and footer and modify page margins. You will also learn to set cell formats for date, time, and percentage.

Topics

Lesson 1: Introduction to Spreadsheet Basics

    • Introduction to Excel
    • The Title Bar
    • Microsoft File Tab
    • The Quick Access Toolbar
    • Name of the working document
    • Minimize, Maximize and close button
    • The Ribbon
    • Worksheet Area (Sheet Tab, Status Bar, Scroll Bar)
    • Moving through cells
    • Using Alt Key
    • Selecting Cell(s)
    • Saving an Excel File

Lesson 2: Formatting Cells

    • Formatting Options
    • Entering and Editing Data in Excel
    • Setting Date Properties of a Cell
    • Modifying Font
    • Aligning the Content in a Cell
    • Formatting Numbers
    • Inserting and Deleting a Column or Row
    • Resizing Columns and Rows
    • Copying and Pasting
    • Editing Content of a Cell
    • Moving Cells

Lesson 3: More Formatting Cells

    • The Format Cells Window
    • Formatting Dates and Times
    • Formatting Percentages
    • Format Painter
    • AutoFormat
    • Using AutoFill

Lesson 4: Page Properties and Printing

    • Page Orientation and Break
    • Setting up Page Margin
    • Headers, Footers, and Page Numbers
    • Preview
    • Printing a Spreadsheet

Project (s)

    • Create An Address Book
    • Create a simple School supply list
    • Create a Product Price list
    • Create an Yearly Calendar
    • Create A Mileage Log Form

Prerequisites

Before attending this course, students must have:

  • Basic computer knowledge, such as keyboard and mouse skills.
  • Basic file-management skills. The student should know how to navigate to folders and files on a computer running Windows 7

After completing this course, you will be able to

  • Start And Exit Microsoft Excel
  • Identify Parts Of The Excel Workbook
  • Distinguish Between Column, Row And Cell
  • Understand Cell Address
  • Enter Data In Cells
  • Save A Working Spreadsheet
  • Enter And Edit Date, Text And Numbers.
  • Insert And Delete Columns And Rows.
  • Format Borders
  • Resize Columns And Rows
  • Delete The Content In A Cell
  • Clear The Format Of A Cell
  • Copy And Paste Content In A Cell
  • Move Content From One Cell To Another Cell
  • Develop A Professional And Visually Appealing Spreadsheet
  • Format Any Text And/or Number In A Cell(s)
  • Apply Format To A Cell Such As Color And Border
  • Use Format Painter to Copy Format from One Cell and Apply To another Cell.
  • Use Autofill Feature To Automatically Fill The Cell Based On A Given Pattern.
  • Use Print Preview To See Before Printing The Spreadsheet.
  • Choose To Print Single Sheet Or The Whole Workbook.
  • Add Customized Header And Footer For All The Pages In The Sheet.
  • Modify Page Margins And Page Orientation.
  • Add Or Remove Page Breaks

Audience

This course is intended for novice information workers who want to start using Excel to manage data.

Intermediate

Excel 2016 Intermediate

Learn to use Excel functions and formula, use conditional formatting, sort and filter data, create graphs, work with pictures and shapes,  create custom styles and format data, locate and validate data and reorder and summarize data. Students who complete this course can be on their way to preparing for a Microsoft Certified Application Specialist exam.

Topics

Lesson 1: Worksheet Management

  • Inserting worksheets
  • Deleting worksheets
  • Naming worksheet
  • Moving worksheet
  • Hiding worksheet
  • Unhiding worksheet
  • Coloring worksheet tab
  • Lab 1: Managing Worksheet

Lesson 2: Graphic Image

  • Adding clip art
  • Add an image from a file
  • Editing a graphic
  • AutoShapes
  • Create, format, reorder, and align shapes; add text to shapes
  • Create, modify, and format an organization chart
  • Insert a Shape
  • Change the Shape
  • Change Shape Fill/ Outline/Effect
  • Selection Pane (Toggle between shapes)
  • Insert a Smart Art of your choice
  • Add and delete one shape
  • Move around the shapes
  • Change layout/Style/Color
  • Lab 1: Using Autoshape
  • Lab 2: Creating an Organizational Chart

Lesson 3: Formulas and Functions

  • Formulas from scratch
  • Using Autosum
  • Using Function Wizard
  • Using Basic functions: Sum, average, min, max, and count
  • Lab 1: Create Expense report using function sum
  • Lab 2: Summer Classes – Using sum and average
  • Lab 3: Sales Report
  • Lab 4: Working with Formula
  • Lab 5: Using Autofill to Copy Formula

Lesson 4: Conditional Statement

  • Using Conditional functions: If, Sumif, CountIf
  • Using Text functions: left, right, upper, proper
  • Lab 1: Calculate commission for Summer Classes
  • Lab 2: Calculate Commission for Training Classes
  • Lab 3: Calculate Overdue Payment
  • Lab 4: Calculate Student Grades
  • Lab 5: Using Sumif & Countif
  • Lab 6: Quarterly Expense Report

Lesson 5: Conditional Formatting

  • Applying conditional format
  • Viewing formatted data
  • Lab 1: Using Conditional Formatting on Enrollment data
  • Lab 2: Using Conditional Formatting on Pizza Production data

Lesson 6: Absolute vs Relative Reference

  • Absolute Cell Reference
  • Lab 1: Summer classes with Absolute Cell Reference
  • Lab 2: Express Report with Absolute Cell Reference
  • Lab 3: Sales Tax with Absolute Cell Reference

Lesson 7: Sorting, Filtering and Filling

  • Sorting Data
  • Filtering a List
  • Freezing and Splitting a Window
  • Sort increasing order or decreasing order
  • Lab 1: Using Simple Sort
  • Lab 2: Using Advance Sort
  • Lab 3: Enrollment Data: Using Filter
  • Lab 4: Advertisement Expenses: Using Filter

Lesson 8: Chart

  • Chart Wizard
  • Resize a chart
  • Move a chart
  • Revise the chart
  • Formatting the chart legend
  • Previewing and Printing a Chart
  • Displaying and Printing Formula Content
  • Paste a chart to Microsoft Word
  • Create, modify, move & customize Charts
  • Paste Charts into Other Documents
  • Lab 1: Projecting Advertising
  • Lab 2: Projecting Enrollment
  • Lab 3: IT Salary
  • Lab 4: Draw a graph for Budget Worksheet

After completing this course, you will be able to:

  • Name groups of data.
  • Use formulas to calculate values.
  • Summarize data that meets specific conditions.
  • Find and correct errors in calculations.
  • Develop Customized styles.
  • Make numbers easier to read.
  • Change the appearance of data based on its value.
  • Manipulate worksheet data.
  • Define rules for valid sets of values for ranges of cells.
  • Sort worksheet data.
  • Organize and group data into levels.
  • Look up information in a worksheet.
  • Develop workbooks templates
  • Create links to data in other worksheets and workbooks.
  • Consolidate multiple sets of data into a single workbook.
  • Group multiple sets of data.

Prerequisites

  • Before attending this course, students must have attended the Microsoft Excel 2016 Basic course or have an equivalent knowledge of the features and functions covered in the Excel 2016 Basic course.

Target Audience

  • This course is designed for students who wants to develop the skills to improve the productivity using formula, functions, data validation tool, sort & filter tool, customized style and collaboration tool using Excel 2016.

Advance

Excel 2016 Advance

Learn to build the skills to analyze data sets, create dynamic worksheets, use PivotTable & Pivot Chart, develop Macros to automate repetitive tasks, collaborate with others, analyze worksheet data, integrate with Office software and secure your data. Students who complete this course can be on their way to preparing for a Microsoft Certified Application Specialist exam.

Topics:

1. Using Formula and Functions

  • Excel Functions
  • Relative Cell Reference
  • Absolute Cell Reference
  • Using Conditional Statement
  • Using SUMIF, SUMIFS functions
  • Using COUNTIF and COUNTIFS functions
  • Use VLookup & HLookup to display the list of information by looking in a set of data
  • Look for exact match or closer match.
  • Exercise: Using Function Sum
  • Exercise: Using Absolute Cell Reference
  • Exercise: Using IF function
  • Exercise: Using IF function to identify OverDue Invoices
  • Exercise: Using IF to Calculate Students Grade
  • Exercise: Using SumIf, SumIfs, CountIf and CountIfs
  • Exercise: Using PMT Function to Calculate Monthly Payment
  • Exercise: Working with Formula
  • Exercise: Expense Report
  • Exercise: Commission Calculation
  • Exercise: Using VLOOKUP Function
  • Exercise: Using HLOOKUP Function for Daily Call Record
  • Exercise: Using HLOOKUP for Postage Rate Calculation
  • Exercise: Using Match function

2. Data Validation

  • Create a dropdown list for a set of cells to pick the right set of data.
  • For a particular set of cells, set the rules to insert values
  • Exercise: Using Data Validation

3. Data Analysis

  • Sort increasing order or decreasing order
  • Covert a range into a table
  • apply the test filter
  • Apply conditional formatting to the table
  • Exercise: Using Simple Sort
  • Exercise: Using Multiple Column Sort
  • Exercise: Using filter
  • Exercise: Using Conditional Formatting tool
  • Exercise: Pie Aggregate Analysis
  • Exercise: Pie Production Analysis

4. Using Scenarios and Goal Seeking

  • User What-if Analysis
  • Create and view multiple scenarios
  • Summarize scenario results in a separate worksheet
  • Get a Desired Result Using Goal Seek
  • Use Goal Seek to determine a solution
  • Exercise: Pie Production Analysis

5. Using PivotTable and PivotChart

  • Introduction to Pivot Table
  • Analyze Data Dynamically Using PivotTables
  • Filter, Show, and Hide PivotTable Data
  • Create, Edit & Format PivotTables
  • Filter a PivotTable by using multiple methods
  • Show and hide details in a PivotTable
  • Create a formula that references PivotTable data
  • Apply a number format, PivotTable style, banded rows, and conditional formatting
  • Create Dynamic Charts Using PivotCharts
  • Create, update, modify, and filter a PivotChart
  • Exercise 1: Using Pivot Table & Pivot Chart

6. Creating and Using Macros

  • Introduction to Macros
  • Working with Macro
  • Enable and Examine Macro
  • Display the Developer Tab
  • Create/Record Macros
  • Edit a Macro
  • Run Macro from a Macro Dialog box
  • Add macro buttons to the Quick Access Toolbar
  • Exercise: Macro to Format Attendee List

7. Collaboration & Integration

  • Add, display, review, and delete comments
  • Save a workbook as a PDF
  • Protect Workbooks and Worksheets
  • Password-protect a workbook, a worksheet
  • Share Workbooks
  • A Quick Look At The Backstage View
  • Track Changes
  • Merging Working
  • Managing Comment
  • Encrypting the file
  • Password protecting the file

At Course Completion

After completing this course, students will be able to:

  • Analyze Data Using Descriptive Statistics
  • Sort increasing order or decreasing order
  • Covert a range into a table
  • Apply the test filter
  • Apply conditional formatting to the table
  • Create and view multiple scenarios
  • Summarize scenario results in a separate worksheet
  • Get a Desired Result Using Goal Seek
  • Use Goal Seek to determine a solution
  • Get a Desired Result Using Goal Seek
  • Use Solver to determine a solution
  • Create a dropdown list for a set of cells to pick the right set of data.
  • For a particular set of cells, set the rules to insert values
  • SUMIF, SUMIFS, COUNTIF, and COUNTIFS
  • Showing mathematical operations by satisfying one or multiple criteria.
  • Using V Lookup / H lookup
  • Look for exact match or closer match.
  • Analyze Data Dynamically Using PivotTables
  • Filter, Show, and Hide PivotTable Data
  • Create, Edit & Format PivotTables
  • Filter a PivotTable by using multiple methods
  • Show and hide details in a PivotTable
  • Create a formula that references PivotTable data
  • Apply a number format, PivotTable style, banded rows, and conditional formatting
  • Creating & Using PivotChart
  • Create Dynamic Charts Using PivotCharts
  • Create, update, modify, and filter a PivotChart
  • Enable and Exam Macros
  • Create and Modify Macros
  • Run Macros When a Button is Clicked or a workbook is Opened
  • Examine, step through, and run a macro
  • Record, edit, save, and run a macro
  • Add macro buttons to the Quick Access Toolbar
  • Create Hyperlinks
  • Create internal and external hyperlinks
  • Add, display, review, and delete comments
  • Save a workbook as a PDF
  • Protect Workbooks and Worksheets
  • Password-protect a workbook, a worksheet
  • Share Workbooks
  • Add, display, review, and delete comments
  • Save a workbook as a PDF
  • Protect Workbooks and Worksheets
  • Password-protect a workbook, a worksheet
  • Share Workbooks
  • Define multiple alternative data sets.
  • Use Goal Seek to get the desired result.
  • Use Solver to find the optimal solution.
  • Use descriptive statistics to analyze data.
  • Use PivotTables to analyze data dynamically.
  • Ability to Filter, show and hide PivotTable data.
  • Ability to Edit and Format PivotTables.
  • Ability to Create PivotTables from external data.
  • Ability Create charts & customize the appearance of charts.
  • Ability to find trends in data.
  • Use sparklines to summarize data
  • Use PivotCharts to create dynamic charts.
  • Use SmartArt to create diagrams.
  • Create shapes and mathematical equations.
  • Create, modify, debug and run a macros.
  • Run macros when a button is clicked.
  • Run macros automatically when a workbook is opened.
  • Include Office documents in workbooks.
  • Create hyperlinks.
  • Share workbooks.
  • Manage comments.
  • Track and manage colleagues’ changes.
  • Protect workbooks and worksheets.
  • Authenticate workbooks.
  • Save workbooks for the Web.

Prerequisites

  • Before attending this course, students must have Excel 2016 Intermediate course or equivalent.

Target Audience

  • This course is intended for people who can already use formula in spreadsheet and want to use Excel to analyze data and develop scenarios to meet the organization’s goal.

MOS Core

Microsoft Office Specialist (MOS) 77-272- Excel 2016 Core Exam

Microsoft Office Specialist (MOS) Exam 77-272 is design to test candidate with fundamental understanding of using the Excel 2016 software to manage data. The candidates will be tested on their ability to create and edit a workbook with multiple sheets for a variety of purposes and situations. Some of the examples of using Excel Spreadsheet include managing budget, performance chart, sales report, marketing data, contact information and more.

Audiences: Information workers including students, data entry, financial advisor, bookkeeper, insurance broker, data analyst, instructors and more.

Topics

  • Creating and formatting worksheets and workbooks
  • Creating new workbooks using templates and importing files
  • opening non-native files directly in Excel
  • saving files to remote locations, saving in alternate file formats
  • adding, copying, moving and hiding worksheets; Changing worksheet tab color
  • Searching for data within a workbook; using Go To and Name Box
  • changing workbook themes, Appending data to worksheets; inserting hyperlinks
  • inserting, deleting, adjusting height & width of columns and rows
  • changing workbook views and modifying page setup
  • splitting the window, using freezing panes and zoom; displaying formulas
  • Setting a print area; printing individual worksheets
  • setting print scaling, configuring workbooks to print
  • inserting watermarks, headers and footers
  • setting data validation and adding values to workbook properties
  • customizing the Quick Access toolbar and Ribbon; assigning shortcut keys
  • recording simple macros and managing macro security
  • finding and replacing data, copying and pasting data, using AutoFill tool
  • expanding data across columns
  • inserting and deleting cells, Merging cells, modifying cell alignment and indentation
  • changing font and font styles, using Format Painter, wrapping text within cells
  • applying Number format, highlighting, cell styles; Applying conditional formatting
  • transposing columns and rows; creating named ranges, creating outline
  • collapsing groups of data in outlines; inserting subtotals, Moving between tables and ranges
  • adding and removing cells within tables, defining titles, applying styles to tables
  • banding rows and columns, inserting total rows, removing styles from tables
  • Filtering and sort multiple columns, removing duplicates
  • Utilizing references (relative, mixed, absolute), referencing cell ranges in formulas
  • Using functions – SUM, MIN, MAX, COUNT, AVERAGE, SUMIF, AVERAGEIF, COUNTIF
  • Using String functions – RIGHT, LEFT, MID, TRIM, UPPER, LOWER, CONCATENATE
  • creating, resizing and modifying charts and graphs
  • Inserting and modifying text boxes, WordArt, SmartArt, sparklines and images
  • adding and modifying borders, style, effects and location of objects

FAQ   |   Register For Exam

MOS Expert

Microsoft Office Specialist (MOS) 77-728: Excel 2016 Expert Exam

The exam is design to test candidate’s expertise in using Excel 2016 software to manage data. The candidates will be tested on their ability to advance feature of Excel 2016 such as collaborating work when working in team, linking worksheet and workbooks, protecting worksheet & workbook, using advance formulas and functions, developing customized formula, developing customized formats, and using Pivot table, Pivot chart, sparkline, slicer and goal setter to analyze data. Some of the examples of using Excel Spreadsheet include managing budget, performance chart, sales data, marketing data, contact information and more.
Audiences: Information workers including students, data entry, financial advisor, bookkeeper, instructors, data analyst and more.

Topic

    •  Manage and Share Workbooks
      • tracking changes, managing comments, identifying errors, troubleshooting with tracking, displaying all changes, retaining all changes
      • modifying existing templates, merging multiple workbooks, managing versions of a workbook, copying styles from template to template, copying macros from workbook to workbook, linking to external data
      • setting tracking options, limiting editors, opening workspaces, restricting editing, controlling recalculation, protecting worksheet structure, marking as final, removing workbook metadata, encrypting workbooks with a password
    • Apply Custom Formats and Layouts
      • writing custom conditional formats, using functions to format cells, creating advanced filters, managing conditional formatting rules
      • modifying Tab order among workbook elements and objects, displaying data in multiple international formats, modifying worksheets for use with accessibility tools, utilizing international symbols, managing multiple options for Body and Heading fonts
      • creating custom formats (Number, Time, Date), creating custom accounting formats, using advanced Fill Series options
      • creating custom color formats, creating and modifying cell styles, creating and modifying custom templates, creating form fields
    • Create Advanced Formulas
      • utilizing the LOOKUP function, utilizing the VLOOKUP function, utilizing the HLOOKUP function, utilizing the TRANSPOSE function
      • utilizing the NOW and TODAY functions, using functions to serialize dates and times
      • creating new PivotCharts, manipulating options in existing PivotCharts, applying styles to PivotCharts
      • utilizing the IF function in conjunction with other functions, utilizing AND/OR functions, utilizing nested functions, utilizing SUMIFS, AVERAGEIFS, and COUNTIFS functions
      • utilizing the watch window, consolidating data, enabling iterative calculations, utilizing What If analysis tools including Goal Seek, utilizing the Scenario Manager, using financial functions
    • Create Advanced Charts and Tables
      • adding trendlines to charts, creating dual axis charts, creating custom chart templates, viewing chart animations
      • creating new PivotTables, modifying field selections and options, creating a slicer, grouping records, utilizing calculated fields, formatting data, utilizing PowerPivot, managing relationships

FAQ   |   Register For Exam

MOS Pathways

Pathways

Microsoft Office Specialist (MOS) is an industry standard certifications in Microsoft Office. MOS certifications prepare students for future success in technology-driven workforce environments. Following is the pathways to MOS certification in Excel Core and Excel Expert.

NRCLC Excel 2013 Certification Pathway2

Online

Online Excel Course, study guide & practice test (1 year)

Excel BasicExcel 2016 Basic

Excel Intermediate BannerExcel 2016 Intermediate

Excel Advance BannerExcel 2016 Advance

Private Lesson

For information regarding private lesson, Call (714) 505-3475.