Automating a weekly report in Excel
A report rebuilt by hand every week from a Microsoft Forms export, now produced in three minutes instead of half an hour. Here is how the workbook works.
Tell me about your projectWhere it started
Every week, one person collected the answers from Microsoft Forms, sorted them, then copied them into a formatted table. It took about half an hour. Since everything was typed and laid out by hand, a typing mistake could slip into the table, and its layout was not guaranteed to be the same from one week to the next.
What the workbook does
The user's part comes down to three steps.01
Paste the export
The Microsoft Forms export is pasted into a dedicated sheet of the workbook.
02
Enter the week and two dates
The user types the week number (36, for example), which becomes the table header: "WEEK 36". A start date and an end date are entered too, so only the form answers between the two are kept.
03
Click the button
A VBA macro builds the table, always with the same layout.
Built-in checks
The workbook shows an error message and does not build the report until the data is right. Three cases are checked:
- an incorrect week number
- an incorrect date format
- an empty form sheet
What changes
- Fewer mistakes: sorting and formatting are no longer done by hand.
- The same layout every week, and a professional look that holds up from week to week.
This tool was built during an in-house assignment.
« The workbook Kristofer set up lets me produce my weekly report effortlessly, with no risk of typing mistakes. »
What about your business?
A form export, a file taken out of a piece of software, a table to rebuild every week or every month: describe the task in two lines and I will tell you whether it can be automated.
A report you rebuild every week?
Which file, which task, how often. That is enough for me to tell you whether it can be automated and how much work it represents.