What is Power Automate
Power Automate is a tool by Microsoft that helps you automate everyday tasks. It’s easy to use, even if you don’t know how to code. With Excel Power Automate, you can send notifications, collect data, and manage approvals automatically. This saves you a lot of time.
Want to learn more? We have a complete guide that shows you how to get started with Power Automate. Click the link here to read it and see how you can make your work easier and faster.
Power Automate in Excel
Power Automate in Excel lets you automate tasks directly within your spreadsheets, making your work more efficient. For example, you can record your working hours using your mobile device and automatically store the data in Excel. You can also automatically move data between Excel and other apps, like SharePoint or OneDrive, without manual effort. This not only saves time but also reduces errors.
Example 1: Post Working Hours
Recording working hours efficiently can save you a lot of time and hassle. This guide will show you how to set up an Excel file and use Power Automate to log your working hours. Follow these simple steps to get started.
Step 1: Set Up Your Excel File
- Create a Workbook: Open Excel and create a new workbook to store your working hours.
- Add Headers: In the first row, add headers: Date, Time, and Task Type (Start or End).
- Create a Table: Select the header row and press Ctrl + T to convert it into a table. Name the table “TableTime”.
To give this table a better name, click the Table Design tab and change the table name to “TableTime”.
- Save Online: Save the file to OneDrive or SharePoint. This is important for connecting it to Power Automate.
Step 2: Create a Flow in Power Automate
- Log In: Go to Office.com and open Power Automate.
- Create a Flow: Click “Create” and select “Instant Cloud Flow”.
- Name Your Flow: Enter “Record Working Hours” as the name. Select “Manually trigger a flow” and click “Create”.
- Add Input: Click “Manually trigger a flow” and add a “Yes/No” input.
- Rename it to “Start?” with the prompt “Yes for Start, No for End”.
Step 3: Connect Power Automate to Excel
- Add Excel Connector: Select “Excel Online (Business)” and choose “Add a row into a table”.
- Define Location: Specify the file location, library, and table name (TableTime).
- Map Fields: Map the fields as follows:
- Date -> Date
- Time -> Timestamp
- Task Type -> Start? (from the trigger input)
Step 4: Test Your Flow
- Install Power Automate App: Download and open the Power Automate app on your mobile device.
- Run the Flow: Click the “Buttons” icon and select “Record Working Hours”. Choose “Yes” for start time or “No” for end time.
- Choose “Yes” for start time or “No” for end time.
- Check Results: Open the Excel file to see the recorded dates, times, and task types. Start times are marked as “True” and end times as “False”.
Step 5: Improve Data Presentation
- Update Task Type: Go back to Power Automate and edit the flow. Use an expression to display “Start” instead of “True” and “End” instead of “False”:
if(triggerBody()['boolean'], 'Start', 'End')💡Make sure you use single quotes, not double quotes like you’re used to in Excel.
Click OK to see the revised Type field.
- Format Time: Use the formatDateTime function to display times in a user-friendly format (e.g., ‘hhtt’ for AM/PM):
To create a function that formats the time, perform the following steps:
- Select Expression
- Click in the fx field and enter formatDateTime()
Here’s where things get a bit tricky.
When we select Dynamic Content, we don’t always see a complete list of the triggers.
If we hover our mouse pointer over the “Timestamp” trigger, we can see the underlying no-so-user-friendly code.
If you don’t want to risk making a typo when you enter this code you can switch to the Code View by selecting the Options (three dots) button in the upper-right and select Peek Code.
We can locate the needed code and highlight it to perform a Copy operation.
- Return to the formatDateTime function and paste the copied code in the parentheses.
- Add the formatting codes that define the look of the time. In this case, we will use the format code ‘hh:mm tt’. The “tt” is for the AM/PM notation.
- Click OK when finished.
formatDateTime(triggerOutputs()['headers']['x-ms-user-timestamp'])
Finally, click the Save button to update the “Record Working Hours” flow.
Step 6: Final Test
This time, we will test the flow directly in the flow editor.
- Click the Test button in the upper-right of the window.
- Select Manually and click Save & Test.
- The option for “Start?” is to be set to ON for a starting time and set to OFF for an ending time. Click Run Flow when finished.
When finished, you can see that the flow was tested successfully as well as how long it took to perform the test.
Returning to the Excel spreadsheet that stores our test results, we see the new entries with the more user-friendly formatting.
Featured Bundle
Black Belt Excel Bundle
Example 2: Create a LinkedIn Post from Excel
This example will be constructed the other way around. We will create a LinkedIn post directly from a row in a table.
Returning to the Office.com website, we begin the first example by creating an Instant Cloud Flow.
We give the name of the new flow “LinkedIn Posts” and select the trigger labeled “For a selected row”. Click Create to continue to the next step.
Defining the Excel source file
As we did in the previous example, we need to define the location, filename, and table from which to extract the link information for the LinkedIn site.
I have an Excel workbook named “LinkedIn_Posts” that contains a table named “TablePosts” to store the URLs and display text for the LinkedIn posts.
We point all the necessary options to the location, file, and table name. Click New Step to continue.
Using the LinkedIn connector
To access the LinkedIn connector, in the Choose an Operation dialog, search for “LinkedIn” and select the connector labeled “LinkedIn v2” (the most recent LinkedIn connector.)
Under the Actions section, select “Share a company update V2 (preview)”
NOTE: At this point, you may need to authenticate to your LinkedIn account with your credentials.
Select the desired company from the Company dropdown list.
For the Content URL field, using the Dynamic Content option, select the URL (Formatted) option. This points to the URL column of our Excel table.
Set the “Visible to Guest” option to “Yes”.
To define the text to be displayed in the LinkedIn post, expand the Advanced Options…
… and select the Text field and point to the Dynamic Content labeled Text (Formatted).
Click Save to complete the process.
Testing the LinkedIn flow
For Power Automate to have the ability to read your Excel files, you need to add the “Microsoft Flow for Excel” add-in to Excel.
If you have not already added this component, perform the following steps:
- Select Insert (tab) -> Get Add-ins
- Search for “Flow” or “Power Automate”
- Click ADD to install the Flow add-in.
This will add a new button to the far right of the Data ribbon labeled Flow.
Uploading the LinkedIn post
To upload the requisite LinkedIn post, select a cell on the row of the table for the desired post and click Data (tab) -> Flow.
Select the Flow created earlier named “LinkedIn Posts” and click the Run arrow.
NOTE: You may need to enter your credentials next if this is the first time you have used this add-in.
Once authenticated, click Continue.
Click “Run Flow” then click “Done”.
If we open our LinkedIn page and navigate to the Posts section, we can see our newly updated post.
Conclusion
Power Automate and Excel can revolutionize the way you work, making your tasks more efficient and accurate. With these tools, you can save time, reduce errors, and focus on more important activities. Start experimenting with Power Automate today and discover how it can streamline your workflow.
Dive into automation and see how these powerful tools can transform your daily tasks, bringing a new level of productivity to your work. Don’t wait—unlock the full potential of Excel Power Automate now!
Featured Course
Automate Excel: Power Query & Power Automate Recipes
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
