CEF 認可 Certificate for Microsoft Excel in Business

Certificate for Microsoft Excel in Business

Module 1: Get started with Excel Tools

• Using Excel Template for quick tasks
• Modify Templates for future use
• Locating Data in cells
• Identify and control cell formats
• Sheets Pagination, Printing and Views
• Sort data in multi-levels

Module 2: Defining group of data

• Select data using Define Name function
• Creating Name using Name Manager
• Reusing Name by Excel Name Box
• Formatting cells using Tables
• Referencing cells in Tables
• Calculating cells in Tables
• Removing data duplication using Tables
• Filtering data in Tables
• Creating Excel Formula by naming a table
• Summarizing data using Tables & Names
• Collaborating Tables & Name with Excel Functions Library
• Importing Data from different source
• Linking Data from cell, sheets and files understanding the prerequisites of Subtotal
• Using Subtotal to summarize data
• Applying multi-level subtotal
• Creating a Total row
• Using outline to control Subtotal
• Generating subtotal using SUBTOTAL()

Module 3: Visualizing Information

• Creating Cell Styles
• Using Customized Cell Style
• Categorizing data using Conditional Formatting Rules
• Visualizing data by Data Bar, Color Scales and Icon Sets
• Customizing Conditional Formatting Rules
• Writing formulas and cell references for Conditional Formatting
• Building Conditional Filtering
• Managing Conditional Formatting Rules

Module 4: Validating Information

• Understanding the prerequisites of Data Validation
• When and Where should we use Data Validation
• Applying Data Validation rules
• Adding cell tip
• Controlling levels of Data Validation alerts
• Understand the different of levels of Data Validation
• Using Data Validation in business environment
• Create custom Data Validation rules using Excel Formulas
• Understanding the role of reference in Data Validation
• Copying Data Validation from one cell to another
• Integrating Name and Table with Data Validation

Module 5: Generating Reports with Pivot Tables

• What is a Pivot Table?
• Where and when to use Pivot Table?
• Preparing data for Pivot Table
• Top 3 Important guidelines for creating Pivot Table
• Understanding Pivot Table Fields
• Modifying layout and appearance of Pivot Table
• Handing NULL and ERROR in Pivot Table
• Filtering data in row and column
• Grouping data by Data, Time or Values
• Handling with Subtotals, Grand Totals in Pivot Table
• Using Timeline on Pivot Tables
• Using Slicer on Pivot Charts
• Creating Calculated Fields
• Changing Data Source in Pivot Table
• Updating Data Source in Pivot Table
• Case Study in Pivot Table in business

Module 6: Composing Formula using Excel Functions

• What is Excel Function?
• Rules of using Excel Functions
• Tracking cell Dependents and Precedents
• How Data and Time Functions help in Project Management
• Retrieving data by Lookup and Reference Functions
• Controlling content by Text Functions
• Using IS function for Error Handling
• Automating formula using Logical function
• Integrating List, Name and Function Library
• Creating Macro using Recorder
• Modifying Macro by Developer tools

Module 7: Designing Charts

• Understanding different types of Chart
• Handling perquisites of Chart
• Designing Column Chart, Bar Chart and Line Chart
• Building Graphical Column Chart using imported images
• Customizing Chart styles and layout
• Configuring Chart bounding and scale
• Designing Radar Chart, Combo Chart
• Adding Shapes and Arrows on Chart
• Saving Chart Templates
• Reusing Chart Templates

Module 8: Reading data by analytical tools

• Reading data from Freeze Pane, Split and Switching window
• Using Goal-seek for cost estimation
• Generating forecast report by Scenario Manager
• Preforming quick calculation by Data Table
• Managing Comments in cells
• Tracking Data history