Outcome
At the end of this course you will be able to:
- Perform quick analysis with advance sorting and filtering
- Crafting formula that able to decide based on certain criteria.
- Uncover hidden details using Pivot Table analysis.
- Presenting findings visually using charts.
- Record and Run Macro to perform repetitive tasks.
- Simulating and comparing various scenarios
- Performing various calculation that are criteria driven
- Effectively Working with Data from External Files
- Troubleshooting and auditing accuracy of a formula
- Aware of new functionalities in handling data more efficiently
- Protect workbook and worksheets from authorized access or changes
Prerequisites
To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:
- Attended Excel Intermediate or able to demonstrate relevant
competencies
- A working knowledge of Microsoft Windows
- A working knowledge of Microsoft Excel
- Able to perform simple formula calculation
Please Note: If a delegate attends this course and does not meet the prerequisites the respective delegate may be asked to switch to different course.
- Working with External Data
When external data is required in processing, this topic shows you the quick and manageable approach to get the data in. In future, you just need to refresh to get the updated data.
- Differentiate Delimited and Fixed Width Text Files encompass
- Importing a Text File
- Refreshing Data from Text File
- Changing Location of Text File
- Setting Options to Refresh External Data
- Referring Cells of another Excel File
- Refreshing External Linked Cells
- Setting Options to Refresh External Link
- Consolidate Data
It’s not easy to manually calculate data from multiple worksheets or workbooks and placed in single worksheet, this topic shows you the fast way.
- Consolidate Data Using 3D Reference
- Using Data Consolidate Option by Position
- Using Data Consolidate Option by Category
- Merging Data from Multiple Data Sources
- Consolidate Category with Custom Categories
- Advance Data Filtering
Long list of data does not serve good purpose until we are able to zoom into specific details we want. From there, we perform further analysis. This topic demonstrates the various ways in zooming into data that we want.
- Turning AutoFilter on or off
- Using Wildcard in AutoFilter
- Using Wildcard Search
- Filtering Data that Uses Wildcard Characters
- Using SUBTOTAL function to calculate filtered data
- Using Advanced Filter
- Setting Up Advanced Filter Criteria
- Copying an Advanced Filtered Result to Another Location
- Using Advance Filter to Output Specific Columns
- Using Advance Filter to Output Unique Data
- Formula Auditing
At times, the formula is getting more complicated. This topic shows us the way to understand and troubleshoot a formula.
- Showing All Formulas in a Worksheet
- Using Shortcut Keys to Show Value of a Formula
- Tracing Precedent Cells
- Tracing Dependent Cells
- Using Shortcut Keys for Tracing Precedent and Dependent Cells
- Stepping Through Formula Evaluation
- Recognizing Common Formula Error Codes
- Defined Name for Range
With increasing usage of formula, regular cell references are not easy to understand. This topic shows ways to simplify cell reference and make it easy to read.
- Quick Way in Defining Named Range
- Defining Name by Selection
- Using Named Range for Navigation
- Using Named Range to Make Readable Formulas
- Editing Named Range
- Shortcut To Show Named Range
- Quick Way in Replacing Named Range in Existing Formula
- Defining Name at Global or Local Scope
- Retrieving Values from Intersection Named Ranges
- Removing a Named Range
- Using Logical Functions
When we need perform certain criteria checking before deciding, often this is performed by using eyeballs. It is counterproductive and high risk of mistakes. This topic shows us how to use logical checking to handle our data.
- Understanding Logical Comparison Operators
- Understanding IF Function Components
- Using Simple Recipe to Craft IF Function
- Performing IF Function on Number Data
- Performing IF Function on Text Data
- Performing IF Function on Date Data
- Create Nested IFs Formula
- Aware of AND, OR, NOT Functions
- Calculating Data with Single Logical Expression with COUNTIF & SUMIF
- Calculating Data with Multiple Logical Expressions with COUNTIFS & SUMIFS
- Using Database Functions
When we are constantly needing to perform calculation on different criteria on a data, that’s time we use database functions. This topic shows us on ways to construct criteria table and get it work.
- Understanding Criteria Range
- Constructing Criteria Range
- Using DSUM Function to Summarize Values
- Using DCOUNT Function to Count Number of Rows
- Using DMIN Function to Retrieve Minimum Value
- Using DMAX Function to Retrieve Maximum Value
- Rapid Data Analysis with PivotTable
PivotTable has many built-in functions to make analysing data as easy as few clicks. This topic covers on aggregating data as easy as 1-2-3.
- Prework Prior Creating PivotTable
- Creating and Using a PivotTable
- Understanding Interface of PivotTable and PivotTable Field List
- Adding, Moving, Removing Fields in PivotTable
- Reorder Fields in a PivotTable
- Filtering and Sorting Data within a PivotTable
- Grouping Text Data in a PivotTable and Renaming Groups
- Grouping Date Data in a PivotTable and Renaming Groups
- Grouping Number Data in a PivotTable and Renaming Groups
- Ungrouping Data
- Copying PivotTable
- Deleting PivotTable
- Creating PivotChart from PivotTable
- Creating Slicers for PivotTable
- Linking Slicers to Multiple PivotTable
- Chart
A picture tells thousand words. Chart can present analysis easily with its visual. This topic shows the quick ways to create, edit and maintain charts.
- Quick Way of Creating Recommended Chart
- Adding, Editing, Removing a Data Series
- Managing Various Chart Elements
- Formatting Chart Elements
- Creating Combo Chart
- Changing the Chart Type for a Specific Data Series
- Adding a Secondary Axis to a Chart
- Moving Charts to another Location
- Hiding Unavailable Data using NA() Function
- Cloning Charts
- Deleting Charts
- What If Analysis
Whenever you have a goal to achieve, you would want to compare various possible scenarios. With What-if analysis, you can quickly perform such simulations.
- Using Goal Seek to Perform Simple Simulation on Single Cell
- Storing Values from Various Cells with Scenario Manager
- Comparing Various Scenarios in Scenario Summary
- Creating One-Dimension Data Table
- Crafting Two-Dimension Data Table
- Passwords & Protection
Over the time, our files are growing and same as its criticality. This topic shows the ways to protect specific areas in worksheets and entire workbook.
- Password Protecting Cells and Worksheets
- Allow Specific Cells for Entry in Protected Worksheets
- Hiding and Unhiding Formula in a Protected Worksheet
- Setting Various Options in Protected Worksheet
- Adding and Removing Password Protection to a Workbook
- Using Macro
Many routine tasks in Excel can be automated via Macro recording. The topic will show us ways to record a usable Macro.
- Recording Macro to Change to Different Scenario
- Recording Macro to Lock/ Unlock Worksheet
- Assign/ Remove Macro to a Shortcut Key
- Assigning a Macro to a Button on the Quick Access Toolbar
- Saving a Workbook containing Macros
- Deleting Macros
- Bonus: Quick Introduction to Power Query, Power Pivot and Power BI
Excel has evolved and improvised for decades. It has many features yet to be fully utilized. This topic is to demonstrate the undermined powers in Excel, where they can dramatically power up your analysis capabilities.
- Quick Evaluation on Power Query
- Quick Run Through on Power Pivot
- Quick Review on Power BI Desktop
Note: The outline coverage may be adjusted to meet the actual training pace.
