This course is formally known as IT032C : Exploring Microsoft Excel (2016) New Features.

Programme Objective

Microsoft Excel is designed to help you get professional-looking results quickly. The new features let you get away from massive walls of numbers, allow you to draw a clearer picture of your data, provide you with tools to create reports faster and enable you to make more effective decisions.

Excel is a great tool to analyse data and it remains as a reliable and affordable tool for analytics. You can use Excel spreadsheets to monitor revenue patterns, operations, marketing trends, budget and timeline projects, create simple operational dashboards, and keep track of orders, import data, create data models, visualize larger data sets without using other software, present easy-to-read visualizations and accurate calculations, and more. Many industries and businesses continue to emphasize the importance of Excel skills because Excel spreadsheet organizes raw data into a readable format that is easier to extract actionable insights. In coming years, Excel is expected to change even more and handle a bigger range of data.

The course will focus on efficient utilisation of Excel features such as Flash Fill, Quick Analysis, Recommended Charts, Recommended PivotTables, Chart Types (including Combo, Waterfall, Treemap and Sunburst), Slicer, Timelines, 3-D Map, Smart Lookup, Forecast Sheet and Get & Transform. Flash Fill allows you to analyze data, format data, combine data and separate text from a column into multiple columns quickly. Quick Analysis tools provide a quick way to apply conditional format, create chart, insert table and PivotTable, calculate totals and create Sparklines. Timelines provides an easy way to filter Pivot Tables and Pivot Charts graphically by date or time-oriented options.

This workshop will equip the participants with knowledge and skills to improve their experience in using the data analysis and reporting tools of Microsoft Excel 2016 (and later versions).
 

Programme Outline

 

• Using Flash Fill to Manage Data
  • Start Flash Fill Manually
  • Format Numbers
  • Format Dates
  • Split Names
  • Change Text Cases
  • Combine text from Two or More Cells into One Cell
  • Split Addresses

 

• Using Quick Analysis Tools
  • The Formatting Tab è Highlighting data with Conditional Formatting
  • Managing Conditional Formatting Rules
  • The Charts Tab è Visualizing data with Recommended Charts
  • The Totals Tab è Calculating Totals automatically
  • The Tables Tab è Summarizing data with Tables
  • Using the Table Tools è Creating Table and Recommended PivotTable
  • The Tables Tab è Summarizing data with PivotTables
  • Using Recommended PivotTable Tools
  • The Sparklines Tab è Creating in-cell mini charts with Sparklines

 

• Functional Chart Types
  • Let Excel Recommends a Good Chart
  • Creating Combo Chart
  • Creating Waterfall Chart
  • Creating Histogram Chart
  • Creating Perato Chart
  • Creating Treemap Chart
  • Creating Sunburst Chart
  • Using the Chart Icons
  • Using Enhanced Chart Labels

 

• Useful Functions
  • Using Subtotal and Outline
  • Using COUNTIFS, SUMIFS and AVERAGIFS functions
  • Using Database Functions
  • Using SUMPRODUCT function
  • Using VLOOKUP, INDEX and MATCH functions

 

• Other Useful Features
  • A “Tell Me What To Do” Feature
  • Smart Lookup
  • Forecast Sheet
  • Get & Transform

Training Methodology

Lecture style, with hands-on exercises

Closing Date for Registration
1 week before programme or until full enrolment.
 

Intended For

This workshop illustrates an overview of the visualisation and analysis tools in Microsoft Excel 2016 (and later versions). It is for all accounting and financial professionals as well as existing Microsoft Excel users who intend to learn and further explore these powerful features to execute their data analysis and reporting tasks more effectively.

Programme Facilitator(s)