Prepare Your Report & Dashboard Systematically (Intermediate)

This Course aims to enhance the knowledge and skills of general business users with basic experience in Excel, or attended “Excel for Business Users: Manage Your Worksheets Effectively” (beginners) course previously. Through this problem solving based course, the Course Participants will learn the systematic ways of organizing reports and dashboards. Furthermore, they will learn more advanced functions that can used to perform more complex calculations, such as calculating Turnaround Time (TAT) and aging.

For Course Participants that need to supplement their reports with illustrations, they will learn how to select and insert appropriate charts in their reports (and the charts will automatically be updated when entering fresh data!).

Interested in this course? Please send an email to and we shall notify you when the course is available!

Session 1: How to Organize Report Systematically?

This Session introduces the concepts of organizing reports systematically, including how to segregate report parameters, data and calculations into separate worksheets. By using a sample dashboard worksheet, the Course Participants learn how to link information from multiple worksheets and check the precedent (which cells are used to calculate this value?) and dependent (which cells use this value for other calculations?) of a specific cell.

Session 2: How to Insert Charts in Your Reports?

By using a given set of data, this Session covers the techniques used to summarize the data and present in charts, as well as modifying the appearance of charts. Furthermore, this Session also discusses how to select an appropriate chart, based on types of information to be presented.

Session 3: How to Perform More Complex Calculations?

This Session covers advanced formulas used to perform more complex calculations, such as looking up specific values from a table, count / sum a selected field based on a given sets of conditions and combine numbers / strings from multiple cells. In addition, Course Participants how to setup formulas that will change dynamically when copying across a range of cells.

Such techniques are useful when preparing a report (e.g. aging reports) with groupings.

Session 4: How to Automatically Highlight Critical Information?

This Session introduces another useful techniques in Excel, i.e. Conditional Formatting, which is used to highlight crucial information – such as highlighting cells with values exceeding a particular benchmark (e.g. highlight report cases that exceed target TAT).

Furthermore, Course Participants also leans how to insert Comments in a particular cells, without affecting the calculations.

Session 5: End-of-Course Project

This End-of-course Project allows the Course Participants to practice the knowledge and techniques learned from the previous sessions, on individual or group basis. This Project helps the Course Participants to strengthen the areas that require further improvements.

Sample of Dashboard

Interested in this course? Please send an email to and we shall notify you when the course is available!

Photo Credit: rawpixel on Unsplash