Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 3NWMHTwp89/YpSd7avDs7c
Power Automate - Building Flows
Suggested answer

Adding Filters to an Excel Spreadsheet

Like (0) ShareShare
ReportReport
Posted on 4 Mar 2025 09:49:24 by 6
In Power Automate Desktop, is it possible to turn on the filters in the top row of a data set. 
 
I've successfully created a flow that adds records from an SQL query to a spreadsheet and then adds a row which contains the column titles. What I'd like to then do is add filters to all of the columns so that when the user opens the spreadsheet, they can filter the data as they see fit. 
 
I tried the below, but it didn't seem to do anything. 
 
  • Suggested answer
    Nived_Nambiar Profile Picture
    17,554 Super User 2025 Season 1 on 09 Mar 2025 at 13:13:34
    Adding Filters to an Excel Spreadsheet
    Hi,
     
    for showing filters in excel data in sheet - you need to create a table so that user when opening the excel file can use the filters to filter the data.
     
    Since there is no direct action in power automate desktop to create the table, so we need to rely on running vb script using Run VBScript action in power automate.
     
    Note - Make sure to run this action after all excel actions have been done, i.e after close excel action.
     
    See below 
     
     
    The first two variables stores excel file path and excel sheet respectively.
     
    Now in Run VBScript action, use the script like below
     
     
    ' This code has been generated by AI. Original prompt:
    ' I need a script which creates a table for data present in a range in excel sheet. I need the range of sheet where data is present , it should not be passed always 
    Dim objExcel, objWorkbook, objWorksheet, objRange, objListObject
    
    ' Create an instance of Excel
    Set objExcel = CreateObject("Excel.Application")
    
    ' Open the workbook
    Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")
    
    ' Set the worksheet (1 means the first sheet)
    Set objWorksheet = objWorkbook.Worksheets("%ExcelSheet%")
    
    ' Find the last used row and column
    lastRow = objWorksheet.Cells(objWorksheet.Rows.Count, 1).End(-4162).Row ' -4162 is equivalent to xlUp
    lastCol = objWorksheet.Cells(1, objWorksheet.Columns.Count).End(-4159).Column ' -4159 is equivalent to xlToLeft
    
    ' Define the range where data is present
    Set objRange = objWorksheet.Range(objWorksheet.Cells(1, 1), objWorksheet.Cells(lastRow, lastCol))
    
    ' Add a ListObject (table) to the range
    Set objListObject = objWorksheet.ListObjects.Add(1, objRange, , 1) ' 1 is equivalent to xlSrcRange and xlYes
    
    ' Save and close the workbook
    objWorkbook.Save
    objWorkbook.Close
    
    ' Quit Excel
    objExcel.Quit
    
    ' Clean up
    Set objListObject = Nothing
    Set objRange = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
     
    This will create the filter in excel data in sheet like this -
     
     
     

    Thanks & Regards,
    Nived N

    Stay connected:
    LinkedIn | YouTube | Blogs

    Was this answer helpful?
    If yes, please mark it as the solution by selecting the checkbox in the discussion thread.
    Your feedback motivates me to keep contributing. Thank you!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 57

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 47 Super User 2025 Season 1

#3
rzaneti Profile Picture

rzaneti 29 Super User 2025 Season 1

Overall leaderboard
Loading started