How to Create a Dynamic Table on Excel Data Using Power Automate | 2022 Tutorial

Hi Everyone,

In this video, I demonstrate how to create a dynamic table using Excel data in Power Automate. Just a note, any column in the first row will get override as table headers. Read more in the forum post below.

Formula: =OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),14)

If you enjoy the video feel free to like, comment, and subscribe!

#PowerAutomate #Microsoft #Excel

[Zsebtetu]

Hi Michael, thanks, I was looking for a solution like this! Thank you

[wrangtang]

Thanks for making this video Michael, helped me solve a problem at work. keep up the good work

[LUCAA]

Excellent, that solved my problem. Thanks!!!

[VishalThakur-hhpm]

Hey this was great, couldnt find this anywhere!

[guyinazo]

Thank you for this! We have a daily excel file coming out of our CMS that I am using to look up employee qualifications for a power app. And before you ask, I have looked at using SharePoint list, Dataverse, SQL, but the company does not want to do down the licensed path. Either way, this file is up to 200, 000 records, but the columns are always the same. The file is being delivered WITHOUT a table and I was looking for a way to create the table in Power Automate. This should do the trick.
New sub here, by the way.

[RUSTYPLE]

what does the "103" part in the formula do? Why is it needed?

[hitakshirajput]

Hi Michael, I have 4 files and I don't know how many columns and rows in it so I need to give a table range dynamically using power automate so how to give a range for columns and rows. Please give me a solution.

[neelmodi]

Hi, this video helps a lot to me. Can you also help me in table range like you have written =OFFSET(....some data...., 4) instead of 4 as a column can I set it as dynamic like if my sheet contain 10 column but it will prepare by taking as 4 column(as i am uploading multiple file to the onedrive and everytime i have to change the column). So, can you help me in that if possible, it also helps a lot to other people.

[MatameisterZ]

Easier than Using OfficeScripts, simple and powerful

[Kriti-]

Hi, thanks for the explanation.
How can we do the same from power Automate desktop?

[rupikayelamarthi]

Hey this video is very much useful but i have this requirement where i need to extract email id's from the excel which is located in the onedrive and send a form to fill to all the email Id's present in the excel form and receive their responses. Can you assist me on how to proceed with this requirement...

[srinivasmanohar]

Hi Michael will this work if i have 3 worksheets in excel file. if not what can i do? kindly let me know.

[golfnutt]

This is awesome but when I change out the table range to use your formula I get an error.
The argument is invalid or missing or has an incorrect format.
Formula: =OFFSET(Page 1!A1, 0, 0, SUBTOTAL(103, Page 1!$A:$A), 25)
This seems to be the only issue. Can you help?