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 if 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 very 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.
Why filter Excel data from one workbook to another
If you want to prevent others from having access to your master spreadsheet, you should filter data from one workbook to another and allow them to access only the information you want to share.
In order to do so, you can connect multiple Excel workbooks with an automated workflow. This enables you to
- Send filtered data from one workbook to another automatically
- Save time
- Eliminate copy-pasting
- Keep your data secure and separated in individual “buckets”
- Maintain a central master workbook that your colleagues can’t have access to
- Distribute specific information to other people’s spreadsheets
Follow the steps below to learn how to filter Excel from one workbook to the other.
Why use Sheetgo to filter data in Excel?
Sheetgo is an all-in-one solution that helps spreadsheet users create automated workflows to transfer, organize, manipulate and report data.
With Sheetgo, you will be able to keep all the data from your master spreadsheet intact while sharing specific data with your colleagues.
What’s more, you can create an automated workflow to update all the connected sheets whenever you update your master spreadsheet.
How to use Sheetgo to filter Excel data from one workbook to another
Filtering Excel data from one workbook to another with Sheetgo is simple and easy. You don’t need to know how to write complex formulas in Excel. You can create automated workflows with just a few clicks.
Step 1: Install Sheetgo
Install Sheetgo by clicking the button below.
Sheetgo
Click +Create workflow and then Connect to create the filter connection between your two Excel files.
At the top of the screen, give your Untitled Workflow a name so you can identify it later.
Step 2: Upload Excel to Google Drive
To filter data from one Excel file to another with Sheetgo, your source file (the file you want to pull data from) must be available online in Google Drive.
Note that you can send the filtered data to Excel files stored in other cloud storage platforms.
Are your files stored on your computer?
If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.
This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.
Step 3: Select your data source
Next, choose Excel as your data source and locate the file from inside your Google Drive.
Your source file is the spreadsheet containing the original data that you want to filter and send to another Excel file. Note that Sheetgo will send filtered data to another fille, but your the source file will remain unchanged.
In this example, I want to filter data from my sales lead database to an Excel file belonging to one of my sales reps.
Step 5: Filter your data
Click Continue to move to the filter section of the connection editor.
Here, you have 3 options: filter by condition, filter by query, or filter by color.
My source file contains a list of sales leads and which sales rep is responsible. 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 option and filter Column H: Owner > Text is exactly > Jackie.
Step 6: Select your destination file
The next step is to select your destination file. This is the file you want to send data to.
You can select an existing Excel file from any of the main cloud storage platforms or Sheetgo will automatically create a new Excel workbook for you. Choose which cloud storage folder you want the file to be saved in.
If you’re sending the data to a new file, give it a name.
Sheetgo will create a new tab(worksheet) in the destination file, containing the imported data. You can rename this in the Tab name box.
In this example, I choose to to let Sheetgo automatically create a new Excel file in my Google Drive.
I give it the name “Jackie’s sales leads” and rename the tab to “New leads”.
Check out your destination file — it now contains the filtered data from your source spreadsheet.
Step 7: Automate the workflow
Now that you’ve set up a connection, you can update the data in the destination file by clicking Run on the floating workflow menu at any time. This will pull fresh data into the destination file, reflecting any changes that have been made to the source data.
To ensure you and your colleagues always have the latest data at your fingertips, automate the workflow.
On the floating menu bar, click Automate. Here, you can choose how frequently you want the data to be updated: daily, hourly, weekly, or monthly.
Automated filtering between Excel files
That’s how to automatically filter Excel data from one workbook to the other.
With one connection, you have just started to build an automated workflow. You can edit the connection, share the workflow with colleagues and add more connections to multiple other files to link and sync data flows between all of your spreadsheets.
Interesting in automating more of your spreadsheet work? Learn how to append Excel and create a historic track of your data.
