In this post I will show you how you can filter data inside a table in Microsoft Excel.

The process is really simple and easy. It all relates on action called “Send keys” with configuration telling the action to send them to a specific window. First, when you open Excel file, you need to turn on filtering. This can be done by sending keyboard shortcut Ctrl+Shift+L. This in PAD should be expressed as {Control}({Shift}(L)).

Next you need to put a focus on a cell, that contains header for the column you want to filter (or in other words – where the small button to open filter dialog is visible).

Once focus is set, you need then to send another keyboard shortcut, this time: Alt+Arrow down. In PAD it would be: {Alt}({Down}).

Then use “UI selector” to get UI elements of the specific parts of the filter dialog box, eg. search box and OK button. Once you have them, simply build logic around them, like populating search box with a term you need to use for filtering and then pressing OK button.

And that’s it!

Below you can find a code, that once you copy and paste to your PAD will turn into 7 actions together with selectors, so that you can try this on your own. Good luck!

Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\USER\\Downloads\\book.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''A''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Control}({Shift}(L))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 1 EndColumn: $'''B''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Alt}({Down})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
UIAutomation.PopulateTextField TextField: appmask['Window \'book.xlsx - Excel\'']['Edit \'Type field name to search for\''] Text: 2 Mode: UIAutomation.PopulateTextMode.Replace ClickType: UIAutomation.PopulateMouseClickType.SingleClick
UIAutomation.PressButton Button: appmask['Window \'book.xlsx - Excel\'']['Button \'OK\'']

# [ControlRepository][PowerAutomateDesktop]
{
  "ApplicationInfo": {
    "Name": "ClipboardControlRepository",
    "Version": "1.0"
  },
  "Screens": [
    {
      "Controls": [
        {
          "AutomationProtocol": "uia3",
          "ElementTypeName": "Edit",
          "InstanceId": "85bbf960-d360-4df5-97e7-49f73a6e04fe",
          "Name": "Edit 'Type field name to search for'",
          "SelectorCount": 1,
          "Selectors": [
            {
              "CustomSelector": null,
              "Elements": [
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIToolWindow"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Menu 'NetUIToolWindow'",
                  "Tag": "menu"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIDismissBehavior"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "UI Custom 'NetUIDismissBehavior'",
                  "Tag": "custom"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUITextbox"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": "Type field name to search for"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Edit 'Type field name to search for'",
                  "Tag": "edit"
                }
              ],
              "Ignore": false,
              "IsCustom": false,
              "IsWindowsInstance": false,
              "Order": 0
            }
          ],
          "Tag": "edit"
        },
        {
          "AutomationProtocol": "uia3",
          "ElementTypeName": "Button",
          "InstanceId": "509b6bbb-8b91-4426-81a1-459b9be42b3e",
          "Name": "Button 'OK'",
          "SelectorCount": 1,
          "Selectors": [
            {
              "CustomSelector": null,
              "Elements": [
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIToolWindow"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Menu 'NetUIToolWindow'",
                  "Tag": "menu"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIDismissBehavior"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "UI Custom 'NetUIDismissBehavior'",
                  "Tag": "custom"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIButton"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": "OK"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Button 'OK'",
                  "Tag": "button"
                }
              ],
              "Ignore": false,
              "IsCustom": false,
              "IsWindowsInstance": false,
              "Order": 0
            }
          ],
          "Tag": "button"
        }
      ],
      "ElementTypeName": "Window",
      "InstanceId": "2497b5ce-8aac-4541-b166-85d9d2dfed34",
      "Name": "Window 'book.xlsx - Excel'",
      "SelectorCount": 1,
      "Selectors": [
        {
          "CustomSelector": null,
          "Elements": [
            {
              "Attributes": [
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Class",
                  "Operation": "EqualTo",
                  "Value": "XLMAIN"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Enabled",
                  "Operation": "EqualTo",
                  "Value": true
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Id",
                  "Operation": "EqualTo",
                  "Value": ""
                },
                {
                  "Ignore": false,
                  "IsOrdinal": false,
                  "Name": "Name",
                  "Operation": "EqualTo",
                  "Value": "book.xlsx - Excel"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": true,
                  "Name": "Ordinal",
                  "Operation": "EqualTo",
                  "Value": -1
                },
                {
                  "Ignore": false,
                  "IsOrdinal": false,
                  "Name": "Process",
                  "Operation": "EqualTo",
                  "Value": "EXCEL"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Visible",
                  "Operation": "EqualTo",
                  "Value": true
                }
              ],
              "CustomValue": null,
              "Ignore": false,
              "Name": "Window 'book.xlsx - Excel'",
              "Tag": "window"
            }
          ],
          "Ignore": false,
          "IsCustom": false,
          "IsWindowsInstance": false,
          "Order": 0
        }
      ],
      "Tag": "window"
    }
  ],
  "Version": 1
}