How to build a leave request and approval system with Forms, Excel and Power Automate

Go to forms.office.com, select New Form and name it PTO/Leave Request Form. Add these questions:

• Full Name: Text, required
• Email Address: Text, required. Under Restrictions, choose Email so only email addresses are accepted.
• Leave Type: Choice with Vacation, Sick Leave, Personal Day and Other, required
• Start Date: Date, required
• End Date: Date, required
• Reason for Leave: Text with Long answer turned on

Use Preview to check the form. You can add more fields if your business needs them.

Add a Choice question called Manager Email Address. This is who will receive the approval request. Type each manager's email address as a separate option, then switch the question to Drop-down.

If you only have one manager, add a single option with their email. With several managers, the employee picks their own manager when submitting the form, and the approval goes to that person.

In OneDrive online, create a new Excel workbook named PTO Tracker. In row 1, add the headers Request ID, Full Name, Email Address, Manager Email, Leave Type, Start Date, End Date, Reason, Status and Approver Comments.

Request ID identifies each request, Status tracks Pending, Approved or Rejected, and Approver Comments stores the manager's feedback.

Select the headers, go to Home > Format as Table, pick a style, tick My table has headers and select OK. Power Automate only works with Excel tables, not plain ranges of cells.

In Power Automate, select Create > Automated cloud flow, name it PTO Leave Request and choose the Microsoft Forms trigger When a new response is submitted.

If your form does not appear in the Form Id list, it is not published yet. In Forms, select Collect responses and choose who can respond (the video uses Anyone can respond, but Only people in my organization also works). Go back to the trigger and select the form.

The trigger only tells you a response was submitted. It does not include the answers. Add the Microsoft Forms action Get response details, select the same form, and set Response Id to the Response Id from the trigger (dynamic content).

Add Initialize variable. Rename the action and the variable to RequestID, set Type to String, and for Value open the expression editor and enter:

guid()

This creates a unique ID every time the flow runs. Later, the flow uses it to find and update the correct row in Excel.

Add the Excel Online (Business) action Add a row into a table.

• Location: OneDrive for Business
• Document Library: OneDrive
• File: PTO Tracker
• Table: Table1

Under Advanced parameters, show the table columns (skip Approver Comments for now, the manager fills that later) and also show DateTime Format. Set DateTime Format to ISO 8601 so the form dates land in Excel in the right format.

Map Request ID to the RequestID variable and map each other column to the matching answer from Get response details. If an answer is missing from the list, select See more. Type Pending in Status. It stays Pending until the manager decides.

Add Start and wait for an approval.

• Approval type: Approve/Reject - First to respond
• Title: Leave Request from followed by Full Name from Get response details
• Assigned to: Manager Email Address from the form (dynamic content)
• Details: employee name, leave type, start date, end date and reason from the form

The manager sees the title in Outlook and all the details in the request, and can approve, reject and add a comment. The flow pauses here until they respond, whether that takes minutes or days.

Add a Condition. On the left, select Outcome from the approval action. Keep is equal to and type Approve on the right.

Type Approve, not Approved. That is the exact value Power Automate returns. If you add a d at the end, the condition never matches and every request goes to the False branch.

In the True branch, add Excel Online (Business) Update a row with the same location, file and table.

• Key Column: Request ID
• Key Value: the RequestID variable
• Under Advanced parameters, show Status and Approver Comments
• Status: Approved
• Approver Comments: Responses Comments from the approval action (under See more)

Power Automate may wrap the action in an Apply to each loop when you pick Responses Comments. That is fine. With First to respond there is only one response, so the loop runs once.

Then add Send an email (V2). Set To to the employee's Email Address from the form, the subject to Your PTO request has been approved, and put the employee's name, leave type, start date and end date in the body.

In the False branch, add the same two actions with different values.

Update a row: same file and table, Key Column Request ID, Key Value the RequestID variable, Status Rejected, and Approver Comments set to Responses Comments.

Send an email (V2): To the employee's Email Address, subject Update on your PTO request, and a body with their name, leave type, start date, end date and the manager's Responses Comments so they know why. You can add the manager's comment to the approved email too if you like.

Save the flow, select Test > Manually > Test, then open the form link and submit a request. The flow run shows it waiting for the approval.

The manager's approval request appears in Outlook and in the Approvals section of Power Automate. The Excel tracker already shows a new row with Pending and no approver comment.

Approve it with a comment. The employee gets the approval email and the row changes to Approved with the comment. Submit a second request and reject it to check the rejection email and the Rejected status.