Intermediate and Advance Excel

Course Code:FS.3
Dates for this course:

1 – 2 March 2017
26 – 27 April 2017
29 – 30 June 2017
13 – 14 July 2017
23 – 24 August 2017
26 – 27 October 2017

Subject: Intermediate and Advance Excel
Course Fee: MYR 2,500



Who Should Attend?

  • This course is ideal for anyone who needs to maintain data within a spreadsheet, be it accounts, customer information, Sales and Marketing, Administration, Procurement, Human Resources, Business Development, Engineering or even the household budget. It is for everyone who needs to use Excel at work and business.

Learning Objectives

  • Boost Excel reporting expertise in business, finance, and accounting.
  • Leverage on new features in Excel in Business Analytic.
  • Record, Run and Edit Macro to perform repetitive tasks and generate reports.

Course Content

Quick Refresh on Excel 101
When the fundamentals are right, we develop the ability towards self-learning. This topic refreshes the fundamentals of Excel.
  • Excel is Giant Calculator
  • The Fundamental Types of Data
  • The Rows & Columns
  • The Workbook and Worksheets
Formulas & Functions
Excel has over 400 functions, and many are not leveraging these. This topic will expose us on the strategies in handling different types of data, and forming our own solutions.
  • Text Functions
  • Date Calculation
  • Relative vs. Absolute References
  • Using Nested Functions
Data Consolidate
Often we are working with multiple workbooks. This topic illustrates ways in consolidate data with ease.
  • Consolidate data within same workbook
  • Consolidate data from multiple workbooks
Data Validation
GIGO (Garbage In, Garbage Out) is fundamental to understanding how bad data affect final report. This topic shows the ways in only allowing good data to come through, and keep bad data at bay.
  • Apply Data Validation
  • Types of validation criteria
  • Setting meaningful Alert Message
  • Provide friendly Input Message
  • Using Formula in validation
  • Creating Own Drop Down (i.e. Combo Box)
  • Removing Data Validation
Database (i.e. Table)
At times, the data can tell a lot of stories. It is up to the users to unearth these stories. This topic guides us to get simple data
  • Sorting Database List
  • Filtering Database List (Auto Filter)
  • Transposing Data from Rows to Columns
  • Using the Text to Columns Feature
  • Checking for Duplicates
  • Showing & Copying Subtotal data
  • Grouping & Outlining Data
  • Viewing Grouped and Outlined Data
  • Using Advanced Filters
  • Using Slicers (Excel 2010 onwards)
Conditional Formatting
Many are tracking and checking data by changing its formatting, such as colour and font. This topic demonstrates the easy way to have dynamic formatting which change according to the actual data and condition.
  • Highlight Top 10 Numbers
  • Using Data Bar
  • Using Icons instead of numbers
  • Adding a Conditional Format
  • Changing a Conditional Format
  • Creating a Custom Format
  • Using Own Formula
  • Delete a Conditional Format
Pivot Table
PivotTable has many built-in functions to make analysing data as easy as few clicks. This topic covers on aggregating data as easy as 123.
  • Inserting a PivotTable
  • Choosing Fields and Grouping Data
  • Changing the Data Displayed and Refreshing the Pivot Table
  • Applying a Style to Your Pivot Table
  • Creating a Pivot Chart from a Pivot Table
  • Creating a Pivot Chart from Data
  • Using Slicers (Excel 2013 onwards)
  • Delete Pivot Table
Chart
Chart seems the final output of any analysis. However it is not the end to make the chart easy to read and interpret. This topic shows the techniques to make chart meaningful and presentable.
  • Creating Chart
  • Modify a Chart
  • Adding and Removing Chart Elements (titles, legend, gridlines)
  • Moving Chart to a Chart Sheet
  • Combo Chart (i.e. bar chart with line chart)
  • Delete Chart
  • Adding and Delete Sparklines (Excel 2010 onwards)
Macro
Many routine tasks in Excel can be automated without really knowing VBA programming. The topic will show us ways to record a usable Macro.
  • Automate routine tasks in Excel
  • Record a Macro
  • Run a Macro
  • Observe how Macro generate VBA
  • Macro Security Setting
  • Assign/ Remove Macro to an object (e.g. image, button)
  • Assign/ Remove Macro to a shortcut key
  • Delete a Macro
  • Considerations to Save Macro

 

Contact Form

 

+60 3 7931 6143