Tutorial: Filtering Data in Excel

In this tutorial, we will create an automation for the following process:

1. Copy the data in a spreadsheet with supplier information to a new sheet.
2. Filter the data to show only the rows with suppliers from the Services and IT industries that were added in the last 10 years.
3. Copy the filtered data to a CSV file.
4. Send the CSV file by email.

We will create a project with the following activities:

• A Use Excel File activity to indicate the Excel file with supplier information.
• A Copy Range activity to copy the data to another sheet.
• Two Filter activities to filter the data according to the desired criteria: one filter for the Industry column, the other for the Supplier Since column.
• A Write CSV activity to copy the filtered data to a CSV file.
• A Use Desktop Outlook App activity to indicate the Outlook account from which to send the email.
• A Send Mail activity to send the email.

1. Step 1: Set up the project and get the necessary files.
   1. Create a new blank project using the default settings .
   2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the file Suppliers.xlsx to your project folder.
2. Step 2: Add the Excel file to the project.
   1. Click Add activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
   2. In the activity:
      • Click Browse next to the Excel file field, and then browse to and select the file Suppliers.xlsx
      • In the field Reference as, enter Suppliers.
        You have indicated that you will work with the file Suppliers.xlsx that is known in your automation as Suppliers.
3. Step 3: Filter the data and copy it to a CSV file.
   1. Click Add activity inside the Use Excel File and then, in the search box at the top of the screen, locate and select Copy Range. A Copy Range activity is added to the Designer panel.
   2. In the Copy Range activity:
      • Click Plus on the right side of the Source range field, and then select Suppliers > Data [Sheet].

      • Click Plus on the right side of the Destination range field, and then select Suppliers > Processed [Sheet].

        You have indicated that you want to copy the data from the Data sheet of the Suppliers file and paste it to the Processed sheet in the same file.

   3. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.

      The data is copied from the Data sheet to the Processed sheet in the Suppliers workbook.

   4. Click Add activity inside the Use Excel File just below the Copy Range activity, and then, in the search box at the top of the screen, locate and select Filter. A Filter activity is added to the Designer panel.
   5. In the Filter activity:
      • Click Plus on the right side of the Source range field, and then select Suppliers > Processed [Sheet].
      • Click Plus on the right side of the Column name field, and then select Range > Industry.

      • Click the Configure Filter button. In the Filter window, make sure Basic filter is selected, and then:

        • Click Plus on the right side of the Value field, and then select Text. In the Text Builder, enter Services, and then click Save.
        • ClickAdd to add a second value.
        • Click Plus on the right side of the second Value field, and then select Text. In the Text Builder, enter IT, then click Save, and then click OK to close the Filter window.

          You have indicated that you want to filter the data in the Processed sheet to show only the rows with the values Services or IT in the Industry column.

   6. Click Add activity inside the Use Excel File just below the Filter activity, and then, in the search box at the top of the screen, locate and select Filter. A second Filter activity is added to the Designer panel.
   7. In the second Filter activity:
      • Click Plus on the right side of the Source range field, and then select Suppliers > Processed [Sheet].
      • Click Plus on the right side of the Column name field, and then select Range > Supplier Since.

      • Click the Filter button. In the Filter window:

        • Select Advanced filter.
        • From the Operator drop-down menu, select > (is greater than).
        • Click Plus on the right side of the Value field, and then select Text. In the Text Builder, enter a date from 10 years ago, for example 5/5/2009, then click Save, and then click OK to close the Filter window.

          You have indicated that you want to filter the data in the Processed sheet to show only the rows with dates after 5/5/2009 in the Supplier Since column.

   8. To make the filters more easily identifiable, edit the name in the upper bar of each one. For example, use Filter Industry for the first and Filter Supplier Since for the second.
   9. Click Add activity just below the Use Excel File activity, and then, in the search box at the top of the screen, locate and select Write CSV. A Write CSV activity is added to the Designer panel. Alternatively, you can also add this activity inside the Use Excel File activity, just below the last Filter activity.
   10. In the Write CSV activity:
       • Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter result-, and then from the Plus menu on the right side of the Text Builder select Project Notebook (Notes) > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to result-Excel Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be result-Excel Date!YYYYMMDD.csv.

       • Click Plus on the right side of the Write from field, and then select Suppliers > Processed [Sheet].

         You have indicated that you want to create a CSV file in the project folder whose name contains the text result- and today's date and that you want to copy the data in the Processed sheet to it.

         
         

         

         
         
4. Step 4: Email the CSV file.
   1. Click Add activity below the Use Excel File activity, and then find the Use Desktop Outlook App activity in the search box at the top of the screen and select it. A Use Desktop Outlook App activity is added to the Designer panel.
   2. In the activity, the default email account is already selected in the Account field. If you want to use a different account, select it from the drop-down menu.
      In the Reference as field, leave the default value Outlook as the name by which to refer to the account in the automation.
   3. Click Add activity inside theUse Desktop Outlook App activity, and then, in the search box at the top of the screen, locate and select Send Email. A Send Email activity is added to the Designer panel.
   4. In the Send Email activity:
      • Click Plus on the right side of the From account field, and then select Outlook.
      • Click Plus on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself. If you leave the Is draft option selected, the automation does not send the email, it instead saves the email to the Outlook Drafts folder.
      • Click Plus on the right side of the Subject field, and then select Text. In the Text Builder window, enter List of filtered suppliers for , and then, from the Plus menu on the right side of the Text Builder, select Project Notebook (Notes) > Date [Sheet] > Today [Cell]. The final text should look like this: List of filtered suppliers for [Excel]Date!Today. Click Save to close the Text Builder.
      • Click Plus on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example Please see attachment.
      • For Attachments, select Files, then click Plus on the right side of the field, and then select Text. In the Text Builder, enter the file name in the same way you entered it in the Write CSV activity: result-Excel Date!YYYYMMDD.csv. One way you could do this is to select all the text in the Text Builder of the Write to what file field in the Write CSV activity, copy the text, and then paste it to the Text Builder of the Attachments field.
        
        

        

        
        
   5. Click Save on the StudioX ribbon to save the automation, then click Run to execute the automation.

   The data in the Processed sheet is filtered, then copied to a CSV file that has today's date in the name, and then the CSV file is emailed.

   Download example