The Excel Trick that Saves Me Hours on MIS Reports 💡

Why It Matters

Automating mandatory‑training compliance checks saves hours, improves data accuracy, and enables proactive employee management.

Key Takeaways

• Use COUNTIFS with helper columns to track mandatory training completion.
• Add an 'attempts' column to identify employees with zero training attempts.
• Leverage FILTER, SORT, and TEXTJOIN to list non‑compliant employee names.
• Create department‑level completion rates using UNIQUE, COUNTIFS, and spill ranges.
• Prefer formulas for transparency, but switch to Power Pivot for large datasets.

Summary

The video demonstrates an Excel‑based workflow for answering common MIS compliance questions by merging an employee roster with a training log. The presenter shows how to link the two tables via employee ID, add helper columns, and apply COUNTIFS to count completed mandatory courses and total attempts. Key insights include using a "mand" column to tally passed mandatory trainings, an "attempts" column to flag zero‑attempt employees, and combining FILTER, SORT, and TEXTJOIN to produce a comma‑separated list of non‑compliant staff. The approach also extends to department‑level dashboards using UNIQUE, spill‑range COUNTIFS, and simple percentage calculations. Examples cited feature fictional names—Barack Obama with two completed trainings, Phil Clinton with all three, and Harry S. Truman with zero attempts—illustrating how the formulas surface actionable data. A bonus section builds a departmental completion‑rate table, highlighting lagging areas like Marketing and strong performers like HR. The method dramatically reduces manual reconciliation time, offers transparent, auditable formulas, and scales via Power Pivot/DAX for larger datasets, making compliance reporting faster and more reliable.

Original Description

Give it a try 👉 https://chandoo.org/wp/mandatory-trainings-are-we-doing-them-data-analysis-challenge-002/

Ever wondered how actual data analysis work looks like? In this video, let me show you behind curtains scenes of the kind of questions I get and how I answer them with Excel.

We are looking at two sets of data - training data & employee data and finding answers to questions about compliance.

Specifically,

1) How many employees have completed all mandatory trainings?

2) How many have done at least one mandatory training?

3) Who have not attempted any mandatory trainings?

4) Department-wise report

Learn how to use simple Excel formulas like COUNTIFS to elegantly answer business questions like this for MIS reports, dashboards and data analysis work.