Automate Microsoft 365

Automate your work with Microsoft 365 and enjoy the breaks you deserved

When Selena showed me the Excel file she used to capture the training records, I immediately felt the pain she was experiencing. The file she showed me contained the recordings of a training session that she was managing. That is the file for only one course. The different tabs indicated the session for the course.

When a staff signed up for a course, she had to go into the folder that stored the files of each course. When she opened that file, she had to select the tab for the specific run and capture the staff details into the worksheet.

When her boss asked for the response for a course, she would have to open the file and count the participants for the course run.

Whenever a department head asked how much budget are they left with, she would have to open another file which she had to diligently update at the end of the course.

Information was everywhere and only she knew how the entire process worked. If she was not around, nobody could perform the update for her.

When I shared my proposed solution, she was very excited and immediately felt heavy burden being offloaded from her. Read on to learn about my solution

My Solution for Selena

To help her become more proficient in managing her training records and reports, I suggested the following changes.

  1. Create an MSForm and let the staff register the program(s) using the form. MSform captures the details into an Excel spreadsheet. In this way, Selena does not have to transfer the sign up details into the spreadsheet.
  2. Use Power Automate to create an email to automatically send the details back to the staff who registered for the course. She can also send the form to the respective department head for his/her approval.
  3. Using Power Automate, update the worksheet when the supervisor approves the course signup.
  4. Using Power Query, the course fees can be extracted and captured into the department budget worksheet. In this way, department heads can instantly know how much budget is left after approving the course.
  5. Through Excel Power Query, Selena can combine all the details from multiple files (Actual and Budget) and automately prepare monthly training report for her boss and the respective department heads.
  6. Using Power BI, a monthly report can be prepared for the department heads to access it anywhere anytime.

Do you have a similar problem like Selena? Talk to us and we are happy to offer you a solution too.