Power Automate for Excel - Create worksheet & Table | Add data to Excel | Read data from Excel

This video will show how to create a new worksheet dynamically, create a table in a given worksheet dynamically, add data to Excel, and read data from excel with a real-life use case example.
Video Chapters:
0:00 - Introduction
01:40 - Video Use Case walkthrough
05:47 - How to get tables from Excel using "Get Tables" Action
13:38 - How to create a worksheet in Excel using "Create worksheet" Action
14:40 - How to create a table in Excel using "Create table" Action
17:15 - How to read data from Excel using "List rows present in a table" Action
18:50 - How to add data to the Excel using "Add a row into a table" Action

[@LauraLopez-pi4dd]

I LOVE YOU. I have been trying my best to get this figured out for a massive reporting project we need at work and this is PERFECT. Thanks so much!

[@criticalwill5875]

I've been banging my head for a week trying to figure this out. I knew it was possible with Power Automate, but couldn't find the documentation anywhere within Microsoft to do this. You solved my problem with this tutorial. Great work and clear explanations. Liked. Subscribed! Thanks!!

[@DeepakShrivastava]

Thank you very much , Glad I could help!

[@dogedingo1535]

I am currently working as a workstudent and this tutorial was so helpful and very easy to follow. Thanks for sharing and uploading this!!! You helped a lot.

[@elcazando5614]

Thank you so much Deepak. Was finding that I couldn't get my row into another table as I was using Value instead of Current item. This helped me SO much! Keep up the great work.

[@DeepakShrivastava]

You are most welcome

[@sharepointtips9330]

Appreciate you so much for the awesome video and thank you! Just looking at the comments section and almost every one is throwing their projects requirements at you.It's absolutely fine to ask but crazy they don't even say thank you after you take your time to answer each question.Kudos!!

[@DeepakShrivastava]

Thank You for watching and keep supporting.

[@thePMknight]

Amazing video! Thank you for putting this together!

[@DeepakShrivastava]

Thanks for watching!

[@prasenjitmandal5486]

Very Informative Video Thank u

[@priynkac-0284]

Thanks a lot for your unique teaching ,It will be more helpful for mw

[@mingmerit1420]

Thank you so much for sharing this man!

[@DeepakShrivastava]

My pleasure!

[@CommonMan1992]

Thank you. This video saved my couple of hours. All the best for your future endeavors.

[@DeepakShrivastava]

Glad it helped!

[@robertstahl8835]

Great tutorial

[@neelimakammila7891]

Thank you for the amazing video, Please could you upload a video on how to filter the data on specific value, extract the info related to value from multiple excel files. Cus_Id(data should be filtered by this field) also read the date range i.e start date and due date. Data should be extracted from multiple files based on 3 headers. CUS_Id, Start Date, Due date. Thank you.

[@DeepakShrivastava]

Thank you for watching!! Great suggestion! I will add this to my upcoming video list.

[@pavankumars9313]

You are super sir

[@DeepakShrivastava]

Thanks a lot 😀

[@lewisli3443]

Thank you so much for giving us the details instruction, I have a question whether the automate can update the data in a new created excel? For example, the flow start with a form and I will upload a excel file when I submit the form, then I want to update the response result to the excel I have uploaded, could you help tell me how to make it?

[@DeepakShrivastava]

Thank you for watching. Yes, you can do it using the "Add row" excel action. But make sure newly created excel should have a table.

[@vidya7453]

can you provide this same method for using power bi data .how to create a tables in different worksheets in excel while export power bi table to excel

[@shaheerkhan5171]

Hi could you show something similar but with data flow as data source?

[@alokchandan2313]

Really helpful

[@DeepakShrivastava]

Glad to hear that

[@alokchandan2313]

@@DeepakShrivastava can we talk on google talk may b

[@vambire22]

Hi, Thanks for your informative Video, i'm trying to add a table row to a dynamic file, everything is going perfectly except that part "Current item" that when I put it in the "Row" it doesn't reflect the row content and I receive blank cells extracted in the destination dynamic sheet with the exact table length. only blanks. please advise on how to get the row data correctly.

[@DeepakShrivastava]

Hi, Thanks for watching. I think you need to share more details on flow to get help and i would suggest the best place to get that is the Microsoft Power Automate community forum powerusers.microsoft.com/t5/Get-Help-with-Power-Automate/ct-p/FL_GeneralDiscussions If you can ask there and send me the link to your question, I or someone else would definitely help you.

[@Shiva_1435]

Thank you Deepak. Is it possible to create an excel file in sharepoint from flow? If possible can you please explain. Thank you!

[@DeepakShrivastava]

I know you can create Excel using Power Automate Desktop, i need to check if we can create using cloud flow or not.

[@dragonkungfu19901991]

bro, add ins ask for organization approval to use

[@carlosurrego3354]

Hello Deepak Shrivastava, I am writing to you from Colombia. I really like your videos, I have a question, in share point I have stored an excel file with information, but from this file I only want to take certain columns and rows and create a dynamic table and send it to an email

[@DeepakShrivastava]

Thank You. your excel should have data in a table to allowis flow to read it. once you have that you can use filters in excel action to get the data that you need and send in emaim.

[@tejassripad1170]

Hi Deepak I want to merge multiple excel files existing in share point document folder to one Master file and each time new file is updated in share point folder Flow need to pick up the latest excel file & copy to my Master file. Can you please help.

[@seenivasansubramanian5398]

I am working on a flow, where list of files in a folder should be pasted in a Excel Table. But, I'm getting only first 20 files details out of 53 files in the folder....Any idea ?

[@patriciorios3474]

if the user of the project 1 deletes a row from the excel report when updated the second report will delete that row as well?

[@DeepakShrivastava]

Hi Patricio, Thanks for watching but i am not very clear with your question.

[@hephzibahsamuel7]

Hi, I have stored Excel files in SharePoint. Now I want to read excel data and need to store in dataverse table dynamically using power Automate flow. Can you please help me out, how to do this? Thanks in Advance!!

[@mikefortuna5517]

Great video. Thanks Deepak. I'm having trouble at the first Compose step/ When I add "?;['Name']" at the end of the expression, I get an invalid expression error. Can you tell me what I'm doing wrong?

[@TheVirkk]

same issue i am facing...

[@NathanLAlvares]

"?;['Name']" not this. But this "?['Name']"

[@paular7261]

I did a mock of this and i think something is a little off. It never created a new workbook not sure why.... But im more focused on the add row section.... So my final doc has 5 lines, id 1 thru 5 with a column that says new in comments. My project file ( which i want to use to update rows that are present in the final doc) has updated comments for item 1 thru 4 ( same id numbers which i believe is needed) id 5 is deleted and one new row entirely, id 6 ... With your flow, all it did was add the rows from project under the last line in final (id5). How do you get the original rows to update? I dont want to keep adding the same rows over and over. Id6 adding makes sense because its a totally new id, but why did id 1 thru 4 add again as new lines? Is there a way to update line 1 thru 5 and just add new row 6?

[@jumanahalqurashi352]

hi, i need to get detailed info from email to excel with specific columns i need, for example, the signature of the sender, company name, country, mobile.... can you guide me? thank you

[@DeepakShrivastava]

Hi , I would suggest you to look at powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity here. You can also question here and someone will help you. It’s better platform to share more specific details.

[@gayathrij9098]

I have a flow for creating and updating an items but the column formatted as a Number in SharePoint not accepting the Blank values. Can you please help me to fix this issue.

[@DeepakShrivastava]

It seems I may need more details to help you. have you tried asking your question at -powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity .I am confident someone will for sure help you finding the solution.ask there. i will also try to answer there.

[@unbiasedUBR]

How to delete multiple rows in a table or how to replace old data with new data

[@rahulthukral5909]

Hi can we automate contract expiry and contract rate amendment through power automate?

[@DeepakShrivastava]

Hi Rahul, could you elaborate a bit more as I am not very clear with your question.

[@steve_account]

I want to do this but only for the table. Like If the table in the file currently exists. Not specifically the entire file. So like if table doen't exist create table. But if table does exist do not create table. Is this possible? if so how?

[@DeepakShrivastava]

If I understood you correctly, you could use the "Get Tables" action, and if you get the result, you can process if notable returns, you can the "create table."

[@MrBabuji123]

i need to add rows from email attachment to destination excel file being kept in one drive. Can you guide me ?

[@DeepakShrivastava]

you should use get attachment and get the content out of it. once you have the content you can easily add that to Excel. May be i will create a video on this later.

[@HappyPuertorrican]

@@DeepakShrivastava I’d also like to see this. I have been trying to do exactly this, but I can’t seem to make it work. Please help!

[@sonamscookingtherapy8809]

Hello Deepak, I need some help with populating Microsoft Word document using power automate. Can you help?

[@DeepakShrivastava]

Hi Sonam, I have created a video on this kzbin.info/www/bejne/nWXIoYt3pbqlgpI , hope this help.👍

[@shankhasen8947]

Hi, I want to email my boss the some specific lines when the value in an excel goes down of certain level.plesse guide.unable to run the flow

[@DeepakShrivastava]

Thanks for watching. check out my new video on the similar topic- kzbin.info/www/bejne/lWK5gol4gbOCjJY

[@arulmanohar]

Any way to get table headers from excel using power automate?

[@DeepakShrivastava]

Thanks for watching. You can get the header name , but you need to write some expressions to get the headers name. I may create a short video on it.

[@LaNocheHerentals]

how to solve the problem if you have an excel file with columns and rows but it is not yet seen as a table? Then the step will not recognize a "table"

[@DeepakShrivastava]

Thanks for watching. I have explained here how you can create a "table" kzbin.info/www/bejne/q5_NmYmci52pfcU. So if the data is in a table, you should create the table first.

[@jaydeepkarhadkar]

Hi Deepak, this was an informative video, but I am looking for something else. I want to download excel file through outlook to the sharepoint and then fetch that file into power automate. But the challenge is the filename is changing with every email and hence I cannot declare a filename in excel activities. CAN YOU PLEASE HELP WITH THIS PROBLEM?

[@DeepakShrivastava]

I will see if i can find a workaround for it. Thanks for watching.

[@jaydeepkarhadkar]

@@DeepakShrivastava I somehow managed to use create file action and then use the file id in next steps.

[@ResponsibleXI]

Can we create the excel file in SharePoint dynamically through Power Automate flow?

[@DeepakShrivastava]

To create a new excel you have to use power automate desktop. I am planning to create a video on it soon👍🏻

[@ResponsibleXI]

@@DeepakShrivastava It would be really helpful if you could include choosing excel file, table and columns dynamically.

[@cristianmeza4205]

Hi, when i create the table, the range genered error, i used: 'MyWorksheet'!A:1B1 ... Can you help me? Please

[@DeepakShrivastava]

Hey, it looks expression is incorrect, use this: ‘MyWorksheet’!A1:B1

[@cristianmeza4205]

@@DeepakShrivastava Hi again, I tried this option and it doesn't work either

[@DeepakShrivastava]

@@cristianmeza4205 interesting it should work with this. may be some other issue.

[@HarishS12137]

Hi, I have a simple request I want data from SharePoint list to be updated to excel whenever new rows are added to the list.. I was able to achieve this.. but now the list has gone past 1.5 million rows and not able to fetch the new data to excel.. is there a solution for this? I need a new worksheet to be created whenever the number of rows is exceeded.. like pagination to be done

[@DeepakShrivastava]

Hi Harish, i am sorry but i am not very clear with your request.

[@HarishS12137]

@@DeepakShrivastava what happens when the number of rows exceed more than 1048576 (max number of rows in 1 excel sheet) in the excel sheet?

[@DeepakShrivastava]

I think we don’t have much options once it hit the excel limit. Only option I can think is creating new excel (you are already doing it I believe )

[@HarishS12137]

@@DeepakShrivastava Thanks for the suggestion, yes currently I have configured a new excel sheet whenever the rows exceed the excel limit.

[@akshaysahay9939]

Hey Deepak, is there somehow I can connect with you?

[@DeepakShrivastava]

hey , you can connect me on twitter @deepak_s22 or LinkedIn

[@noodlebrains]

This doesn’t work for me. The add row keeps over writing itself when another sheet is added because there is a common column in new sheets.