Filter Excel from one workbook to the other
Working with Excel spreadsheets gives you endless possibilities for creating powerful data solutions. And when you publish your workbook to Sharepoint, its sharing capabilities make it easier than ever to collaborate in teams. But what if you only want to share certain information from your spreadsheets with specific team members? Or you want to send personal data from one workbook to other files?
Sharing an Excel workbook with colleagues allows for co-authoring, meaning multiple people can work in the same file, with all changes updated in real time. This can be incredible useful, but you have to be careful with access permissions on complex or sensitive documents. Too many “hands” on the same spreadsheet can be risky, especially when you’ve spent a long time creating your spreadsheet and applying formulas to your data.
A solution to this problem is to connect multiple Excel workbooks in a streamlined, automated workflow. This enables you to
- Send filtered data from one workbook to another automatically
- Save time
- Eliminate copy-pasting
- Keep your data safe and separated in individual “buckets”
- Maintain a central master workbook that your colleagues can’t touch
- Distribute specific information to other people’s spreadsheets
Follow the steps below to learn how to filter Excel from one workbook to the other inside Google Drive.
Step 1: Install Sheetgo
Once inside the web application, click on +Create workflow and then Connect to create the filter connection between your two Excel files.
Step 2: Upload Excel to Google Drive
In order to filter one Excel file to the other with Sheetgo, it is required to automatically sync them to your Google Drive.
Set up an automatic sync with Google’s backup and sync software. Simply save your Excel files in a folder on your desktop, find them in your Drive under Computers > right-click on the folder and add it to your Drive.
Step 3: Choose your data source
Next, choose Excel as your data source and search and find your Excel file from within your Google Drive.
This Excel file has the original data that you want to filter and send out to another Excel. In this example, I will filter my sales lead database to a personal sales rep’s Excel file.
Step 5: Filter your data
Click on Continue to come to the filter section of the connection editor.
Here, you have 3 options: filter by condition, filter by query, or filter by color.
My Excel file lists down all the sales leads and their respective owners. In this case, I want to filter out Jackie’s sales leads and send it to her personal Excel file. I use the filter by condition feature and filter Column H: Owner > Text is exactly > Jackie.
Step 6: Select your data destination
The next step is to select your data destination. Either let Sheetgo automatically create a new Excel workbook for you, or select it from inside your Google Drive.
In this example, I choose to to let Sheetgo automatically create a new Excel file in my Drive.
I give it the name “Jackie’s sales leads” and rename the tab to “New leads”.
Finally, click on Finish and save to create the filter connection between your Excel files.
Step 7: Automate the workflow
To automatically send this filtered data from your sales database to Jackie’s personal Excel file, use Sheetgo’s automatic updates.
On the floating bar of your workflow, click on Automate. Here, you can choose between daily, hourly, weekly, or monthly updates.
Automated filtering between Excel files
That’s how you automatically filter Excel from one workbook to the other, in Google Drive. Every new edit to your original Excel file will automatically send filtered data to your newly created file.
Interesting in automating more of your spreadsheet work? Learn how to append Excel and create a historic track of your data.