What is Excel?
Microsoft Excel is one of the most simplest and powerful software applications. It lets users do quantitative analysis, statistical analysis with an intuitive interface for data manipulation, so much so that its usage spans across different domains and professional requirements. It is quite different from Basic Excel, the focus for the user is more on DSUM, DCOUNT, Pivot Table, Pivot Chart, Formulas, Functions, and Macros.
Course Overview?
This short course focuses on more advanced features beyond the Microsoft Excel: Intermediate. Advanced analysis tools including data linking, data consolidation and outlining and summarizing are covered, as are Pivot Tables, Lookup Functions and some key automation features such as macros. Learn how to import data, create Data Tables and Scenarios for What If analysis, techniques to validate data, and create form controls such as List and Combo Boxes to make data entry easier.
This course aims to provide experienced Excel users with proficient skills in developing more complex formulas, list analysis using a variety of tools and creating simple macros as well as features and tips to assist efficiency
Course objectives
By the end of this course, you should be able to:
- modify Excel options
- import data into Excel and export data from Excel
- use data linking to create more efficient workbooks
- group cells and use outlines to manipulate the worksheet
- create summaries in your spreadsheets using subtotals
- use a range of lookup and reference functions
- use the Data Consolidation feature to combine data from several workbooks into one
- create, use and modify data tables
- create and work with scenarios and the Scenario Manager
- construct and operate PivotTables using some of the more advanced techniques
- create and edit a PivotChart
- use a variety of data validation techniques
- create and use a range of controls in a worksheet
- create recorded macros in Excel
Course contents
- Understanding Excel Options
- Personalising Excel
- Setting Advanced Options
- Linking Between Worksheets & Workbooks
- Updating Links Between Workbooks
- Grouping and Outlining
- Creating a Manual Group
- Grouping by Columns
- Summarizing and Subtotaling
- Creating Subtotals
- Using Subtotal
- Creating Nested Subtotals
- Format Subtotals
- Using Subtotals with AutoFilter
- Creating and Using Relative Names for Subtotals
- Date and Datedif functions
- Using TODAY, NOW and DAY Functions
- Using EOMONTH and WEEKDAY Functions
- Using YEARFRAC and EDATE Functions
- Understanding Data Lookup Functions
- Using Index & Match
- Combine Index and Match
- Reverse lookup using Choose function
- Using INDIRECT
- Understanding Data Consolidation
- Consolidating with Identical layouts
- Creating a Linked Consolidation
- Consolidating from Different Layouts
- Consolidating Data Using the SUM Function
- Understanding Data Tables and What-If Models
- Using a Simple What-If Model
- Using One-Variable and Two-Variable Data Tables
- Scenario manager
- SOLVER Tools
- Understanding Scenarios
- Creating a Default Scenario
- Creating Scenarios
- Using Names in Scenarios
- Displaying Scenarios
- Creating a Scenario Summary Report
- Merging Scenarios
- PivotTable
- Inserting a PivotChart
- Changing the PivotChart Type
- Using the PivotChart Filter Field Buttons
- Moving PivotCharts to Chart Sheets
- Understanding Data Validation
- Creating a Number Range Validation
- Testing a Validation
- Creating an Input Message
- Creating an Error Message
- Creating a Drop–Down List
- Using Formulas as Validation Criteria
- Circling Invalid Data
- Removing Invalid Circles
- Copying Validation Settings
- Using SUMIF, SUMIFS, COUNTIF and COUNTIFS Functions
- Using IF with Text and Numbers
- Nesting IF Functions
- Using logical functions (AND & OR)
- Using IF with AND & OR
- Introduction to Macros in Visual Basics for Applications
- Understanding Types of Controls
- Understanding How Controls Work
- Create an Advanced Charts Using Controls
