Excel in Microsoft Power Automate - Beginners Tutorial

This video tutorial shows how to update Excel with Microsoft Power Automate. Learn how to update multiple sheets simultaneously, add advanced conditions, and run Office Scripts - VBA Macros on steroids.
📂 Download the course materials: andersjensen.org/lesson/micro...
VIDEOS FOR YOU:
🔹 Microsoft Power Automate Beginners Course: • Microsoft Power Automa...
🔹 Advanced Invoice Processing in Power Automate: • Advanced Invoice Proce...
POWER AUTOMATE GROUPS:
👨‍👩‍👧‍👦 Discord: / discord
- Join my network with 4400+ Automation/RPA developers, where we solve Automation/RPA problems, network, and help each other upgrade our careers. It's free!
👩🏻‍🎓 LinkedIn: / 12566435
👨🏻‍💻 Facebook: / 754059285247921
FOLLOW ME:
💼 LinkedIn: / andersjensenorg
📸 Instagram: / andersjensenorg
👨 Facebook: / andersjensenorg
🐦 Twitter: / andersjensenorg
💌 Email Newsletter: and
ersjensen.org/email-newsletter
0:00 Prepare the Data
Download the Excel book from the course materials. Then, place the Excel book in OneDrive so that Power Automate can access it. Our data is formatted as a table, which is required for Power Automate to read.
03:49 Read from an Excel Sheet
Use the List rows present in a table action to read the Excel data into Microsoft Power Automate. The result is a JSON, from which we can extract the information later.
8:04 Sum Values in Rows and Columns
Use the Apply to each action to iterate through Excel rows. Then the Update a row action adds two cells in each row together. Then use the Key Column and Value to map the data. Finally, create a Power Automate expression using the add function to add the two Excel values.
15:19 Subtract Values in Rows and Columns
The sub function can subtract two numbers in Excel with Power Automate.
18:22 Multiply Excel Values
The mul function can multiply two numbers in Excel with Power Automate.
20:13 Add a Row to an Excel Sheet
The Add a row into a table action adds a row to an Excel sheet.
23:00 ODATA Filtering
The ODATA filter approach in Power Automate is the simple approach. We can use the equal (eq), not equal to (ne), startswith and endswith. And we can't combine the filters.
25:59 Advanced Excel Filtering
You can use a Filter array action to completely customize your filter on the Excel data with Power Automate. And you can even combine the filters.
33:41 Excel Lookup
Use the Get a row action to perform an Excel Lookup. With a condition, we can handle errors in case the value doesn't exist. The condition uses a contains function, where we dynamically look for the lookup value. If true, we do the lookup afterward.
45:54 Update Multiple Excel Sheets
The Power Automate solution for updating tables on multiple Excel sheets is a bit more advanced. The Get Tables action will retrieve all tables from an Excel book. Then we iterate through each row in each table.
59:06 Provide the Item Properties
In the Update a row action, we need to update the fill the Provide the item properties parameter. It should be in JSON format.
1:06:29 Office Scripts and Power Automate
With Office Scripts, we can create advanced Power Automate solutions. We record our actions in Excel online and automatically have them as scripts. The scripts are written in TypeScript, a superset of JavaScript. I recommend learning TypeScript because Office Scripts are the future (VBA Macros will die). Not only can we create advanced Excel automation, but our Excel data doesn't need to be formatted as a table anymore. Edit the Office Script to take ingoing arguments, e.g., a sheet name. From Power Automate, use the Run script action to run an Excel Office Script.
#powerautomate #powerplatform #office365

[@andersjensenorg]

Have you seen the Facebook group, Microsoft Power Automate Professionals, where we network and solve PA problems together: facebook.com/groups/754059285247921/ 🔥🙌

[@samokjn]

A heartfelt thank you from Morocco.

[@andersjensenorg]

My pleasure 😊 Kind regards, Anders

[@sbrutcher]

Thanks so much for this video! Everything is explained clearly and the pace is perfect for a beginner.

[@andersjensenorg]

Hey sbrutcher, I'm so happy to hear - thanks a lot 🙂🙌

[@grzegorz2852]

god bless you man! WE have been learning a lot from u !

[@andersjensenorg]

Thanks a lot for all the supportive comments 🙏😊

[@holdemonly]

This is just awsome!!! Thank you so much! Create job! My applauds

[@andersjensenorg]

I'm so happy to hear, thanks a lot 🤩🙏

[@pabloredondo6662]

Great tutorial, at 14:44 I couldn´t see dynamic content and looking why that happened, sounds funny, it was because of the resolution was to close and didn´t appeared in the screen for that reason. Thank you, Anders, another time giving value with your knowledge. Thank you, a lot!

[@andersjensenorg]

Hey pablo, thanks a lot 😀 My next shopping is a bigger screen (16:9 resolution), so I can record in 4k 🔥

[@suraj_singh321]

Kudos great content sir 👍

[@andersjensenorg]

Thanks a ton, Suraj 🙏🙌

[@suraj_singh321]

Can you make a video on VBA code run using power Automate desktop 1.run main macro function simple 2. Single or multiple parameter macro run

[@suraj_singh321]

Hii Anders sir make a video on data scraping of Amazon or Flipkart website while scraping the Hyperlinks data getting scraped in an unstructured format make a video on this as soon as possible

[@bevimaga]

Amazing video! Everything is so clearly explained. I have an extra question regarding a small hurdle I've encountered when using dynamic tables. In my case, the spreadsheets are protected, and users can only modify selected columns. If I remove the protection, my workflow is fine. However, when I set the protection back, I get the following error message: "The request is forbidden by Graph API. Error code is 'AccessDenied'. Error message is 'You cannot perform the requested operation.'" Do you have any ideas on how to work around this, or must I resign myself to leaving the workbooks unprotected?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@e.dnorth]

Thanks for this video! So helpful as always! one thing I note, for Run Scripts I don't have a field for Active Sheet. Does anyone know if that is a change that was implemented since this video came out?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 6000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@bmg526]

Great video! It’s posible to fill a cell, if there is no an ID columns? Perhaps looping up in w columns

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 7000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@azolotaiko]

Hi Anders! Love your videos! Thank you for all the work you put into them! I ran into an issue where I want to change calculation mode in excel from auto to manual in the beginning of the flow and then back to auto once the flow is complete. No matter how much I tried, I can’t get it working. I have updated scripts as you have shown here, I have recorded scripts. I tried in same and different browsers windows. It is just not working. Can you please suggest something? Thank you!

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@suraj_singh321]

Excel VBA Series videos of Excel from basic advance like comparing 2 datatable using Macro Power automate desktop 2022 👍

[@andersjensenorg]

Have you seen the last part of this video, Suraj? Office Scripts are the new VBA 😊

[@PennyLi-ek3hk]

Thanks for your wonderful video, but what if there is no table in the excel, if it is possible to generate a dynamic table? The count of the row and column are unknown.

[@andersjensenorg]

Hey Penny. No, not with the Excel activities, but then you can generate an Office Script instead and invoke it from Power Automate. Kind regards, Anders

[@OliverHumphreys-lq9vd]

Great video! Looks like the course material is gone now though - I only see a file about Pepsi employees. 😞

[@andersjensenorg]

I'm so sorry, Oliver. Thanks for letting me know, it's now fixed 😊 Kind regards, Anders

[@suraj_singh321]

Hii Anders sir can you make a video on Dynamic row count in PAD

[@andersjensenorg]

Hey Suraj, thanks a lot for all your support. A video on that is not on the to-do, and I'm not 100% sure, what you mean? Can you post your query on the Discord (with an elaboration)? Then we solve it. The Discord link is in the Description. Kind regards, Anders

[@suraj_singh321]

@@andersjensenorg sir I mean to say in an excel file the rows are continuously changes as per data that dynamic count only that's it 👍

[@khalledmohammed3360]

Thanks for the video, but in the minute "44:20" you mentioned in the concat there are three things and you didnt put anything after ' "ID": " ', also you didnt put in the third one after variables('RowLookup'), ' "" ', i didn't get it why we made three if we concate only ID with variables

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 11,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@MrMegadogman11]

Hello there, is there a way to extract data from a pivot chart with power automate? In my case, power automate just finds "normal charts" in my sheets but I can't fetch data from pivot charts. Thank you in advance!

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 7000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@gbu03]

Is it possible to remove conditional formatting in excel using automation flow

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 8000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@sairaghavendrakoneru5686]

#andersjensenorg we are converting the Excel data to a table, then only Power Automate is able to read... but when we get a file from an email, we will save that to the Onedrive folder and I want the Power Automate should convert it to a table to process further steps within that flow is there any possibility for that? Your suggestion is a great help, Thanks in advance.

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 8000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@gordonrekko3078]

How do I paste clipboard contents into a designated cell in Excel?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 6000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@huleshsahu]

In the first step at 5:35, I'm not getting list of folders instead I'm getting list of files. There the template file I'm not getting. Please suggest. Or can I do complete operation in Power Automate Desktop?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@dpwood44]

So, I can't get past the step of copy/paste into the expression bar.. add(float(item/TotalSales": "@items('Apply_to_each')?['SalesStore1']),float(item/TotalSales": "@items('Apply_to_each')?['SalesStore2'])) .... It keeps telling me the expression is invalid.. I try to click Ok multiple times like you said, but it won't let me proceed the same as you in the video.. Any tips?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 6000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@abinashjena7275]

Hy, getting same issues. Is there any solution?

[@srikanthkatla308]

Could please explain how to do the same thing using an automated cloud flow😢

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@brettcarroll3614]

Is there anyone that can help me build a cumulative excel report based on data coming in from an email every day?

[@andersjensenorg]

Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 10,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): kzbin.info/www/bejne/roipq2CJbpuLfdE Kind regards, Anders

[@anaheredia6226]

Unable to follow with 2024 Power automate :(

[@andersjensenorg]

Untick the "New designer" toggle 😊

[@blueskyblogs7532]

Hi it showing me error while loading the excel

[@andersjensenorg]

Hey Blue sky blogs Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: kzbin.info/www/bejne/lYK1emCPpJebbrc Have a great day. Kind regards, Anders

[@64210187]

Edit: Why are you starting off doing something and then talk about what you will do with office scripts (last tab) "on steriods"... what is the objective and what is it that you will do for this session... It seems you just dove and provided zero context...

[@andersjensenorg]

Thanks for the feedback 🙌