Microsoft Excel (Advanced)
| Previous | 3 / 10 | Next |
This is 2-Day advanced level of Microsoft Excel training covers the advance features and functions of Excel 2019/ Office 365. You will learn how to quickly analyze data, sort and filter data, perform data forecasting and analysis as well as create simple macros.
Objective:
At the end of the training, participants are able to:
Objective:
At the end of the training, participants are able to:
- Calculate with advanced functions and formulas
- Analyze data using Pivot Tables and Pivot Charts
- Apply advanced conditional formatting and data validation
- Organize a database list by sorting and filtering
- Apply advanced charting techniques
- Share your workbooks
- Create excel macros
Outline:
-
Chapter 1: Advanced conditional formatting and data validation
- Understanding conditional Formatting
- Managing the Conditional Format Rules
- Finding Cells with Conditional Formatting
- Using Data Validation
-
Chapter 2: Using Range Names
- Assigning Names to Group of Cells
- Managing Named Range
- Using Names in Formulas
-
Chapter 3: Working Database and Statistical Functions
- Database Functions
- DCOUNTA()
- DSUM()
- DAVERAGE()
- DMIN() & DMAX()
- Summing and Counting Values
- The COUNT(), COUNTIF(), & COUNTIFS() Functions
- The SUM(), SUMIF(), & SUMIFS() Functions
- The AVERAGE(), AVERAGEIF(), & AVERAGEIFS() Functions
-
Chapter 4 : Working with Logical and Information Functions
- Logical Functions
- Using IF() function
- Performing multiple logical tests
- Combining Logical Functions with AND & OR functions
- Getting Data with Information Functions
- The IS() Function
- Handling Formula Errors with IFERROR(), ISERROR()
-
Chapter 5: Managing, Sorting and Filtering Tables
- Create and Modify Tables
- Sorting and Filtering Data in a Table
- Dealing with Duplicate Rows
- Performing Table Calculations
- Use TotalRow in a Table
- Formatting for First/Last Column
- Using Advanced Filters
-
Chapter 6: Working with Advanced Charts
- Band chart-show performance against target range
- Actual vs budget charts
- Combination two chart types
- Conditional formatting for column chart
- Frequency distribution
- Thermometer chart
- Doughnut chart
- Bullet graphs
-
Chapter 7: Working with Pivot Tables and Pivot Charts
- Building Pivot Tables
- Multi-Layered Pivot Tables
- Fine-Tuning Pivot Table Calculations
- Summary calculations
- Custom calculations
- Calculated fields
- Calculated items
- Sorting and Filtering Pivot Tables
- Using slicer and timeline in Pivot Tables
- Using automatic grouping
- Using relationship detection
- Building Pivot Charts
-
Chapter 8: Working with Shared Workbooks
- Sharing a workbook
- Commenting in Cells
- Setting revision tracking
- Accepting or Rejecting Changes
- Merge workbooks
-
Chapter 9: Loading External Data
- Import a Delimited Text File
- Export Excel Data
- Cleaning data using Power Query
- Splitting columns, adding columns
-
Chapter 10: Working with Excel Macros
- Recognizing the power of Excel Macro
- Identify workbooks that support macros
- Showing the Developer Tab
- Understanding Macro Security
- Recording and running a macro
- Relative and Absolute Recording
- Editing and Deleting Macros
- Variety ways to run macros
Duration:
2 days (14 hours)
Target Audience:
This course is most suitable for people who use Microsoft Excel in their daily work, and know the basic of Excel, and wish to extend their knowledge of the more advanced features and functions of Excel.
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Participant must have a basic knowledge of Microsoft Excel such as know how to start excel program, key in numbers, text, and do simple formatting as well as know the basic excel functions like sum, count, and average.
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Participant must have a basic knowledge of Microsoft Excel such as know how to start excel program, key in numbers, text, and do simple formatting as well as know the basic excel functions like sum, count, and average.