ExcelCEO Excel® 2016 Training - Details

Benefits of Learning Excel with ExcelCEO

- See more than 750 specific screenshots and screentips to compare training to - taken specifically from Excel 2016
- Dozens of additional screenshot aides to increase familiarity with Excel 2016 program
- Train using more than 50 simple and complex, time-saving functions, including powerful combinations of functions
- Work on more than 45 specific Excel projects for true hands-on training — developed from decades of real-world experience
- Explore Excel functions, formulas, Office Ribbon features, and dialog boxes with Review Questions
- Prove project accuracy and learning retention with chapter exams
- Earn up to 40 NASBA-certified CPE/QAS CPE credit, awarded by chapter, by completion
- Establish yourself as an Excel Master by completing the Comprehensive Project using skills gained throughout the Excel 2016 course

Excel 2016 Training Setup

ExcelCEO Excel 2016 is designed as self-study training with easy-to-follow, step-by-step instructions for completing hands-on projects. Throughout each chapter, a paragraph will instruct you to complete Review Questions or the chapter exam. Review Questions checkpoints will test your knowledge to that point as well as providing new learning opportunities. You are encouraged to take the Review Questions in a hands-on approach to learn firsthand many of the menus and functions of Excel applicable to the chapter. Since Review Questions are not graded, do not be concerned if you are not familiar with the question material in advance.

Each chapter contains a chapter exam to test your completion and understanding of the chapter material. Many questions will refer to chapter projects outlined during the specific chapter. For best results, make sure you complete the projects correctly, and compare your spreadsheets to the screenshots, where shown. In order to complete each chapter, a minimum score of 70% is required. NASBA allows up to three attempts per chapter to pass an exam to qualify for CPE credit. Once you have successfully passed a chapter exam, your CPE certificates will be available for printing in the Certificates section of your ExcelCEO student profile.

Chapter 18 contains the Comprehensive Project for testing how well you understand the advanced skills taught in this course. Completing this Master section is to be qualified as an ExcelCEO Excel Master, and does not have CPE credit associated with it. Successful completion of the Comprehensive Project and Chapter 18 exam will add you to the Graduation Verification as evidence that you have mastered Excel 2016 from ExcelCEO!

Excel 2016 Basics

Begin with the basics of Excel to familiarize yourself with the program, to refresh your memory, and to expose you to useful tips and tricks many Excel users do not know exist. With ExcelCEO Excel 2016:

• Recognize how to navigate through the Office Ribbon tabs and groups
• Understand the structure, function, and locations of Excel cells.
• Develop simple formulas
• Identify functionality of columns and rows
• Expand cell display options with multiple formatting functions

Formatting

• Identify the function of and uses for the Underscore character
• Utilize the Format Painter icon in single use and double-click operation
• Recognize how to apply AutoSum to Excel data
• Establish criteria for handling formula errors
• Apply Absolute, Mixed, and Relative references to cell ranges
• Expand capabilities with cell appearance formatting
• Create templates for repeatable project use

Simple Graphics and Flowcharts

• Add functions to the Quick Access Toolbar
• Create a new folder in which to save projects
• Utilize shapes and objects within an Excel workbook
• Recognize functions and uses for Text Boxes
• Enhance report appearance using WordArt
• Identify and fill the structure of a Flowchart

Sorting, Subtotaling, and Filtering

• Simultaneously work with and across multiple workbooks
• Complete simple and custom sorting operations
• Apply Subtotals to report range
• Establish text, number, search, and advanced filters to data
• Filter data for unique values

Printing

• Learn to include titles on reports to be printed
• Add custom Headers and Footers to a report
• Specify non-contiguous ranges and hide rows and columns in print ranges
• Use Page to Fit functionality and save an Excel file as a PDF

Intro to Formulas and Functions

• Identify correct functions of arithmetic and comparison operators
• Create simple and nested IF() formulas for conditioned results
• Recognize parts of the Insert Function dialog box
• Develop an Assumptions page for workbook variables
• Establish Named Ranges for Excel tables regularly used in formulas
• Apply text operators
• Recognize uses of the TEXT() function

Text Functions

• Identify common functions
• Utilize the FIND() and SEARCH() functions
• Extract cell data with the LEFT(), RIGHT(), and MID() functions
• Control text data appearance using UPPER(), LOWER(), and PROPER() functions
• Determine and control length of cell data portions using LEN() and TRIM() functions
• Modify data formatting for calculations using the VALUE() function
• Establish patterns for the Flash Fill feature

Financial and Math Functions

• Correctly use financial functions, including PMT(), PV(), FV(), and NPV() functions
• Create and understand an Amortization Schedule for tracking loan payments
• Setup a Proforma Income Statement to forecast future income
• Use Find and Replace functionality to strategically alter data
• Establish RAND() functionality within workbook criteria
• identify uses for INT(), ROUND(), and ABS() number functions
• Recognize structure and functions of SUMIF() and SUMIFS() functions

Date, Statistical, and LOOKUP Functions

• Utilize Date functions including NOW(), TODAY(), MONTH(), DAY(), YEAR(), DATE(), and WEEKDAY()
• Recognize statistical data functions including COUNT(), AVERAGE(), MEDIAN(), MODE(), MAX(), MIN(), COUNTIF(), AVERAGEIFS(), COUNTIFS(), and RANK()
• Operate database functions including DSUM() and DCOUNT()
• Learn basics of VLOOKUP(), Data Validation, Validation Rules, HLOOKUP(), and Text to Columns functions
• Identify the differences between text strings and numbers in a VLOOKUP function

Advanced LOOKUP and Logical Functions

• Learn Advanced LOOKUP capabilities utilizing Absolute References, LOOKUP(), MATCH(), and MATCH within a VLOOKUP
• Identify functional uses for CELL(), IFERROR(), AND(), and OR()
• Utilize Error Finding and Checking

Intro to PivotTables

• Create a simple PivotTable
• Explore the PivotTable Fields List
• Recognize PivotTable grid sections ROWS, COLUMNS, and VALUES
• Add FILTERS to PivotTables grid for increased data readability
• Modify Report Filters for data drill-down
• Enhance a PivotTable with Calculated Fields
• Explore PivotTable Options group
• Sort within a PivotTable

Advanced PivotTables

• Change a PivotTable field’s settings
• Establish complex Calculated Fields within a PivotTable
• Drill-down into PivotTable data
• Setup Search Filters for a PivotTable
• Identify External Data Sources that can be used for a PivotTable
• Connect a PivotTable to an external data source
• Explore the uses of Slicers
• Learn additional PivotTable tricks

Charts, Graphics, and Objects

• Setup a basic chart based on an Excel data range using Chart Elements
• Edit an existing Excel chart’s content and position
• Establish Sparklines for data summary
• Add a data forecasting Trendline to an Excel chart
• Create and modify a Pie Chart for percentage-based data
• Develop a PivotChart based on PivotTable data
• Incorporate SmartArt, and import and embed objects into an Excel spreadsheet

Analysis Tools

• Establish Goal Seek to solve for variables
• Create complex analysis with the Solver function
• Identify Descriptive Statistics for a data set
• Enhance cell appearance based on Conditional Formatting
• Operate Excel in Full Screen mode
• Add Hyperlinks to a workbook for enhanced navigation
• Identify data trends with Quick Analysis

Graphics, Protection, and Sharing

• Create and edit graphics using Paint
• Establish protections for workbook data, including passwords
• Recognize how to share a workbook, track changes made by other users, and consolidate data
• Link data in a workbook to another source

Macros and the Developer Tab

• Identify Macro Security
• Recognize structure of VBA programming within Excel macros
• Create and Run a macro
• Incorporate shortcut keys for operating a macro
• Utilize Step Into functionality within VBA for an established macro
• Edit steps within and troubleshoot a macro
• Create a Command Button for executing a macro
• Add a macro to the Quick Access Toolbar
• Increase user-friendliness of a report using Spin Buttons and Check Boxes

The Web and More Cool Excel Stuff

• Create a simple HTML file and save a single Excel file web page
• Create a web query of posted Excel data
• Filter on dates within Excel
• Incorporate a background image into a selected Excel spreadsheet

Excel 2016 Comprehensive Project

The Comprehensive Project is the capstone of the Excel 2016 training, and is designed to test the skills taught throughout the course. This project focuses on three primary topics taught during the course: PivotTables, the VLOOKUP() function, and nested IF() logic. The Comprehensive Project is setup to imitate a real-world project where instructions are typically minimal. Upon successfully passing the Chapter 18 exam, you would then qualify to be listed as an ExcelCEO Excel Master in the Graduate Verification link at ExcelCEO.com. In addition to a high-quality printed certificate, this Graduate Verification provides additional evidence of Excel mastery you could then reference to a boss, prospective employer, or client.

• Create complex revenue analysis
• Establish formula links between tables to enhance data uses
• Identify data meaning and role within analysis
• Create reporting levels using roll-up data
• Separate revenues by type within PivotTables
• Identify Fixed and Variable expenses