Course Outline
Lessons
Course Length: 6 hours (1 day)
Managing your data is a key to success in any business. Microsoft Excel is the standard spreadsheet application used extensively worldwide for this purpose. Excel allows users to build and calculate formulas quickly, efficiently and accurately. Excel's database functions enable users to find, extract, and analyze valuable data in order to make effective business decisions.
Learning Objectives:
In this course, you will create advanced formulas, utilize and maintain database records, utilize Named Ranges in your formulas and visualize your data in Charts. You will:
- Enter, edit, and format data to create workbooks
- Work with Named Ranges
- Use IF, OR, PMT, VLOOKUP and XLOOKUP Functions
- Display relationships between formulas and cells using formula auditing tools
- Sort and filter data
- Organize worksheet data with tables
- Visualize data with charts
Target Student:
This course is for individuals who are familiar with the basics of Excel and who wish to learn additional functions and tools to organize and analyze their data more fully.
Course Outline:
Section 1: Working with Range Names
What are Range Names?
Apply Range Names using the Name Box
Editing Range Names
Using Range Names in Formulas
Section 2: Using Specialized Functions
Function Categories
The Excel Function Reference
Function Syntax
Function Entry Dialog Boxes
Using Nested Functions
Automatic Workbook Calculations
Section 3: Analyzing Data with Logical and Lookup Functions
The IF Function
The AND Function
The OR Function
The LOOKUP Function
The VLOOKUP and HLOOKUP Function
The XLOOKUP Function
Section 4: Using Financial Functions
The PMT Function
The FV Function
Section 5: Auditing Worksheets
The Trace Cells Feature
Show and Hide Formulas
Resolve Errors in Formulas
Using the Watch Window
Section 6: Organizing Worksheet Data with Tables
What Are Tables?
Table Components
The Create Table Dialog Box
The Table Tools - Design Contextual Tab
Table Styles
Customize Row Display
Table Modification Options
Section 7: Sorting and Filtering Data
The Difference Between Sorting and Filtering
Sorting Data
Filtering Data with Autofilters
Creating Advanced Filters
Filter Operators
Removing Duplicate Values
Section 8: Using Subtotal and Database Functions to Calculate Data
SUBTOTAL Functions
The Subtotal Dialog Box
Database Functions (DSUM, DAverage)
Section 9: Visualizing Data with Charts
Section 9.1: Inserting Charts
Chart Types
Create Charts
Chart Insertion Methods
Resizing and Moving the Chart
Adding Additional Data
Switching Between Rows and Columns
Section 9.2: Modifying and Formatting Charts
Chart Elements
The Chart Tools Contextual Tabs
Format the Chart with a Style
Add a Legend to the Chart
Add Data Labels to Charts
Create Custom Chart Templates
Section 9.3: Adding Trendlines
Types of Trendlines
Add a Trendline
Format a Trendline
Cancellation Policy
Compuease Cancellation Policy
CompuEase works hard to schedule our training and other services around your convenience. With that in mind we have the following cancellation policy for all booked training. Notice of cancellation must be received by CompuEase 10 working days prior to the confirmed date for a no financial penalty to be incurred. Registrants who wish to reschedule their course may do so at least 10 working days before the commencement of their course providing the course fee is paid within 30 days following the originally scheduled date of the course.
Training Location
Virtual
using RingCentral (Zoom platform) or Teams
.,
.
.
