Overview:
In this course, students will use Microsoft® Office Excel® 2013 to create spreadsheets and workbooks that they can use to store, manipulate, and share data.
Who Should Attend?
This course is intended for students who wish to gain the foundational understanding of Microsoft Office Excel 2013 that is necessary to create and work with electronic spreadsheets.
Duration:
Classroom Learning - 1 Day(s)
At Course Completion:
Upon successful completion of this course, you will be able to create and develop Excel worksheets and workbooks in order to work with and analyze the data that is critical to the success of your organization. You will:
-
Get started with Microsoft Office Excel 2013.
-
Perform calculations.
-
Modify a worksheet.
-
Format a worksheet.
-
Print workbooks.
-
Manage workbooks.
Lesson 1: Getting Started with Microsoft Office Excel 2013
-
Navigate the Excel User Interface
-
Use Excel Commands
-
Create and Save a Basic Workbook
-
Enter Cell Data
-
Use Excel Help
Lesson 2: Performing Calculations
-
Create Worksheet Formulas
-
Insert Functions
-
Reuse Formulas
Lesson 3: Modifying a Worksheet
-
Insert, Delete, and Adjust Cells, Columns, and Rows
Lesson 4: Formatting a Worksheet
-
Modify Fonts
-
Add Borders and Colors to Worksheets
-
Apply Number Formats
-
Align Cell Contents
-
Apply Styles and Themes
-
Apply Basic Conditional Formatting
Lesson 5: Printing Workbooks
-
Preview and Print a Workbook
-
Define the Page Layout
Lesson 6: Arranging data and filtering
Lesson 7: Formulas
-
Sum
-
Count
-
Max
-
Min
-
Average
-
CountA
-
CountBlank
-
If logical function
Lesson 8: Graphical Presentation using Charts
-
Column chart
-
Bar chart
-
Pie chart
-
Line chart
Overview:
Students will build upon the foundational Microsoft® Office Excel® 2013 knowledge and skills already acquired and learn to create advanced workbooks and worksheets, including advanced formulas, tables, PivotTables, PivotCharts and data filtering.
Who Should Attend?
This course is designed for students who already have foundational knowledge and skills in Excel 2013 and who wish to begin taking advantage of some of the higher-level functionality in Excel to analyze and present data.
Duration:
Classroom Learning - 2 Day(s)
Prerequisite(s) or Equivalent Knowledge:
Excel 2013 – Level 2 (Intermediate)
At Course Completion:
Upon successful completion of this course, you will be able to leverage the power of data analysis and presentation in order to make informed, intelligent organizational decisions. You will:
-
Customize the Excel environment.
-
Create advanced formulas.
-
Analyze data by using functions and conditional formatting.
-
Organize and analyze datasets and tables.
-
Visualize data by using basic charts.
-
Analyze data by using PivotTables, slicers, and PivotCharts.
Lesson 1: Customizing the Excel Environment
-
Configure Excel Options
-
Customize the Ribbon and the Quick Access Toolbar
-
Enable Excel Add-Ins
Lesson 2: Creating Advanced Formulas
-
Use Range Names in Formulas
-
Use Specialized Functions
-
Use Array Formulas
Lesson 3: Analyzing Data with Functions and Conditional Formatting
-
Analyze Data by Using Text and Logical Functions
-
Apply Advanced Conditional Formatting
-
If condition, nested if condition, And logic, OR logic
Lesson 4: Organizing and Analyzing Datasets and Tables
-
Create and Modify Tables
-
Custom Sort Data
-
Advance Filter Data
-
Use SUBTOTAL and Database Functions
Lesson 5: Visualizing Data with Basic Charts
-
Create Charts
-
Modify and Format Charts
Lesson 6: Analyzing Data with PivotTables, Slicers, and PivotCharts
-
Create a PivotTable
-
Analyze PivotTable Data
-
Present Data with PivotCharts
-
Filter Data by Using Slicers
Lesson 7: Data Validation
-
Data Validation
-
Text validation
-
Number validation
-
Customize error message
Lesson 8: Data Protection
-
Protection of Sheet using locking of cells
Lesson 9: Data Consolidation
-
Consolidation with linked
-
Consolidation with Static
Overview:
In this course, students will learn some of the more advanced features of Excel, including automating common tasks, auditing workbooks to avoid errors, sharing data with other people, analyzing data, and using Excel data in other applications.
Who Should Attend?
This course is intended for a student who has experience working with Excel and would like to learn more about creating macros, working with shared documents, analyzing data, and auditing worksheets.
Duration:
Classroom Learning - 2 Day(s)
At Course Completion:
At course completion, students will:
-
Automate worksheet functions.
-
Audit worksheets.
-
Analyze data.
-
Work with multiple workbooks.
-
Import and export data.
Lesson 1: Working with Multiple Worksheets and Workbooks Simultaneously
-
Use 3-D References
-
Use Links and External References
-
Consolidate Data
Lesson 2: Protecting Workbooks
-
Protect Worksheets and Workbooks
Lesson 3: Automating Workbook Functionality
-
Apply Data Validation
-
Work with Forms and Controls
-
Work with Macros
Lesson 4: Applying Conditional Logic
-
Use Lookup Functions
-
Combine Functions
-
Use Formulas and Functions to Apply Conditional Formatting
Lesson 5: Auditing Worksheets
-
Trace Cells
-
Search for Invalid Data and Formulas with Errors Watch and Evaluate Formulas
Lesson 6: Using Automated Analysis Tools
-
Determine Potential Outcomes Using Data Tables
-
Determine Potential Outcomes Using Scenarios
-
Use the Goal Seek Feature
Lesson 7: Presenting Your Data Visually
-
Use Advanced Chart Features
-
Waterfall chart
-
Pareto chart
-
Speedo meter chart
-
Create Sparklines
Lesson 8: Vlookup and Hlookup
-
Advance Vlookup and Hlookup using
-
Iferror function
-
Using data validation
-
Using row, column and match function
-
Using concatenate function
-
Using left, right, mid and search function
-
Using len function
-
Using choose function
-
Using trim function
Lesson 9: Macros and Dashboard
-
Purpose of Macros
-
Recording of Macros
-
Where to save Macros
-
Absolute and Relative Macros
-
Running Macros
-
Custom button, menus icon and keyboard shortcuts
-
Dashboard uses
