Overview
In today's fast- paced, technical business environment world, numbers meant everything. So it is of utmost importance that we the users, are able to manage the data - statistics, planning, reporting efficiently. We learn how we are doing and discover our next course of action by studying and interpreting numbers. That explains why we need to master the incredibly powerful tool - Microsoft Excel.
Microsoft Excel is the standard spreadsheet application for both the business world and personal use. We will teach you intermediate to advanced features of Microsoft Excel so that you become skilful in using the Excel software.
Who Must Attend
OUTCOME
At the end of this course you will be able to:
- Boost Excel reporting expertise in business, finance, and accounting.
- Perform automated reconciliation for speedy weekly and monthly reporting.
- Leverage on new feature in Excel in Business Analytic.
- Organize data better for better and faster analysis.
- Record and Run Macro to perform repetitive tasks and generate reports.
PREREQUISITES
To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:
- A working knowledge of Microsoft Windows
- A working knowledge of Microsoft Excel
- Able perform simple formula calculation
Please Note: If you attend a course and do not meet the prerequisites you may be asked to switch to appropriate course or to excuse yourself.
DURATION
2 Days (9 am - 5 pm)
METHODOLOGY
Face-to-face learning with demonstrations and practical hands-on.
Training Outline
Day 1
1. 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
2. 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
3. Data Validation
GIGO (Garbage In, Garbage Out) is the fundamental understand on how bad data affect final report. This topic shows the ways in only allowing good data coming 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
4. 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 ways 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)
5. Condition and Lookup
Right management and processing of the data is the key to uncover meaningful information from data. This topic shows us how to use lookup and condition to handle and process our data.
- Using IF, AND, OR, NOT
- COUNTIF vs. COUNTIFS; SUMIF vs. SUMIFS
- Using VLOOKUP
- Reverse Lookup (MATCH & INDEX)
Day 2
6. 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 PivotTable
- 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 PivotTable
7. Conditional Formatting
Many are tracking and check of the data by changing its formatting, such as colour and font. This topic demonstrate 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
8. 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)
9. 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
Hardware & Software
- A laptop is required for this course (PCs not provided)
- Excel 2007 or later is installed
