Transfer data from one excel worksheet to another automatically without vba

With more than 30 million users worldwide, Excel is the most popular spreadsheet software in the world. Thanks to its variety of analytical and collaborative features, Excel is one of the best tools to store, manage and share large amounts of data both individually and with teams.

Although Excel offers a lot of great features, when it comes to data management, you still have to carry out a lot of these processes manually. This includes transferring your data from one worksheet to another. There are a few ways to do this within Excel, such as copying and pasting or using a simple formula.

However, manually transferring data across different sheets can not only be time-consuming but can also create more room for error. Using an automated alternative that you can still use within Excel may be the best solution to save time and ensure data accuracy.

Let’s explore in more detail how to transfer data from one worksheet to another in Excel, and how the Sheetgo add-in is a great alternative to automate this entire process.

Transfer data from one sheet to another in Excel

Why transfer data in Excel?

There are many benefits of transferring your data to a single Excel worksheet:

• Manage data in one place: Rather than having to deal with multiple tabs or files of data, you can have everything in one place for easy access, monitoring, and analyzing.
• Access data from different locations: You or your team may have multiple folders and storage locations. This can be difficult to pinpoint specific data. Transferring everything to one place keeps everything extremely accessible.
• Avoid duplicate content: Making a copy of spreadsheets to access the data can become messy and inefficient. Transferring data directly is a more organized way to get the most out of your Excel worksheets.

For example, let’s say I am a marketing manager who needs to create a performance report. Rather than having multiple tabs or files of marketing data from different years, I can transfer this data to a single worksheet. As a result, I have a clear overview of everything I need, ready to start creating my report.

How to manually transfer data in Excel

There are many different ways to transfer your data in Excel, depending on the type of Excel tool you’re using and where your files are stored. There are more complex ways to transfer data such as using a VBA script, but this is too technical for most spreadsheet users. Here are 2 very simple ways to manually transfer your data into Excel.

Copy and paste

Copy and pasting is perhaps the easiest method to transfer your data from one sheet to another in Excel.

Simply highlight the cells you wish to copy, right-click and select Copy or use the keyboard shortcut Ctrl/Cmd + C. Navigate to the Excel worksheet where you want to transfer your data. Click on a cell, right-click and select Paste or use the keyboard shortcut Ctrl/Cmd + V.

Worksheet reference

Similar to copy and paste, this method uses a worksheet reference to transfer the data from one sheet to another.

In the sheet where you want to add your data, click on a cell and enter ‘=’. Then, navigate back to the sheet containing the data you want to transfer and click on the first cell containing the data, then click Enter. You should now be back in your original worksheet and see the new value. Click on this new value and use the bottom right-hand corner of the cell to expand it across to transfer the rest of your data.

Please note: For this method, you may need to reformat any cells containing any formatting. In this example, I need to reformat columns A and D back to a date and percentage format.

Although these two methods are extremely easy and useful, they do come with limitations. Firstly, you’re more likely to cause data inaccuracy by manually copying and pasting data or inputting your reference. What’s more, one Excel file can store up to 2GB of data. If you continue to transfer more and more data into a single worksheet, your file could potentially crash and you can lose your data.

How to transfer data in Excel automatically

Transferring data automatically is not only a more streamlined, efficient process, but it also leaves no room for human error. The Sheetgo add-in for Excel allows you to automate the entire transfer process from start to finish, directly within your worksheet.

Why use Sheetgo?

The Sheetgo add-in offers a range of benefits when it comes to automated data management within Excel:

• Save time: Replace manual transferral methods with a start-to-finish automated solution, so you can save hours of work every month.
• Avoid manual error: With an automated system, there is no room for human error that’s usually associated with manual processes.
• Keep data up-to-date: Sheetgo creates connections between your spreadsheets and allows you to schedule automatic updates so that whenever one spreadsheet is changed, the rest of your connections will automatically update accordingly.
• Control access to private information: Sheetgo gives you 100% control over the way you manage your data. Use intelligent features such as the Filter feature to send specific data to your desired location.
• Replace large Excel files with connections: Create an efficient system of connected spreadsheets to keep track of where data is sourced from and where it’s sent.

The best part is that you can experience all of these benefits for free! Sign up to Sheetgo for free using the button below.

Sheetgo add-in

How to transfer data from one Excel worksheet to another

Before you begin, it’s important to note that in order to transfer data in Excel automatically with Sheetgo, you need to upload your Excel files to an online folder. This can be OneDrive, Sharepoint, or Google Drive.

Let’s use the example mentioned above. I have yearly blog insights in an Excel workbook, a tab dedicated to each year. I want to combine and transfer these tabs into a single worksheet so that I have a clear overview of everything to create a report.

Step 1: Open the Sheetgo add-in for Excel

Log in to Sheetgo by clicking on the blue button below.

Sheetgo add-in

You will see that you have the option to log in with your Microsoft, Google, or Dropbox account.

It’s possible to link Excel files stored on the same cloud storage folder or on different platforms. This means you can share data and connect to colleagues’ files stored on OneDrive, SharePoint, Google Drive, or Dropbox.

Once you’ve installed the Sheetgo add-in for Excel from Microsoft Appsource, you should be able to access the Sheetgo add-in within an Excel file by clicking on the Sheetgo logo located in the top right-hand corner of your screen, as shown below.

Step 2: Select your Excel source file

Open up a new Excel file in which you want to transfer your worksheet data. Open the Sheetgo add-in for Excel and log in. Now you can begin connecting your Excel sheets to transfer your data.

Start by clicking Select data under the Import data option.

Click on the option Multiple files to import multiple worksheets. Then, click Select files.

It’s time to search for the worksheets you want to transfer to. You can switch the online storage file by clicking on the icon on the top right-hand corner of the sidebar.

Search for your file and double-click to add it. Remember to also use the dropdown menu to select the specific tab where your data is located.

Once added, click +Add another source file to continue adding the worksheets you wish to transfer.

When you have all of your worksheets added as the source files, click Done editing.

Step 3: (optional) Merge your worksheets

If you want to simply transfer your worksheets to another file and keep them as separate tabs, you can skip this step and continue with the next one.

However, with Sheetgo, you can also use the Merge process to combine your worksheets into a single sheet.

Click the + symbol to Add data processor.

You can choose which row(s) to use as your header in your new worksheet. Alternatively, you can opt to remove headers entirely. In this example, I want to keep my first row as the header row.

Once finished, click Done editing.

Step 4: Edit your destination file

As your destination file has already been selected, simply rename the new tab to be created. In this example, I will rename the new tab to “Blog 2019-2022”.

Once finished, click Finish and save.

Step 5: View transferred data

Now that your connections have been made, click Run, and your spreadsheet should update containing your new data.

You should now have a new tab(s) in your Excel file containing the data transferred from your multiple worksheets.

Step 6: Schedule automatic updates

You can also opt to schedule automatic updates between your connections. This means that whenever your source files have been changed, Sheetgo will automatically update your new files with the same data.

In your workflow overview, click the Automate button.

Here, activate the button Run automatically to trigger automatic updates. Adjust the schedule settings to your needs, specifying the dates and times you wish to automate your connection.

Once finished, click Save.

Now, you have successfully transferred your multiple worksheets into a single sheet! Your files will update according to your automation schedule.

How to transfer specific data from one Excel to another

In the majority of cases, users want to transfer their data to have complete control over where specific data is shared and stored.

Sheetgo offers the Filter processing feature which allows you to easily refine your data to a specific condition you set. As a result, only your specific data will be transferred to another worksheet.

In this example, I want to transfer the blog data with a click rate of over 60% into a new tab within the same workbook. I can use the Filter feature to set up my condition, and Sheetgo will transfer the data that fulfills it to a new worksheet.

Step 1: Add new connection

In your existing spreadsheet containing the data you wish to filter, click +Add connection.

Click on Select data under the Export data section.

Step 2: Choose source file and tab

Your current sheets will be automatically selected as the source file. Use the dropdown menu to select the tab containing the data you wish to filter and export.

Then, click Next.

Step 3: Filter your worksheet

Under the Process section, select the Filter option.

Choose Condition as your filter type.

Here, you can adjust the condition according to your needs. For example, if I want to only transfer the blog insight data that has a click rate of 60% or more, my single condition should look something like this:

Column: Column D (Click rate) Criteria: Number greater than or equal to Value: 60.00

Once you’re happy with your condition, click Next.

Step 4: Add destination file

Scroll down to the Send to section to choose where to send your filtered data. In this example, I want to send it to the same spreadsheet I’m currently using, but create a new tab.

Select Single file, then choose to send your data to an Existing file. Search for your Excel file and create a name for the new tab where your data will be located.

Once finished, click Finish and save.

Your new connection will now start updating automatically.

Step 5: View filtered data

You may need to update the connections to update the spreadsheet.

Click Run to update your workflow.

Once done, you should be able to see your filtered data as a new worksheet.

As this connection is part of your existing workflow, this worksheet will automatically update according to the schedule you previously created.

How to import or export Excel data automatically

And there you have it! With the Sheetgo add-in, you can easily transfer your worksheet data from one Excel to another. Not only do you save time and manual effort with this automated process, but you also have 100% control over where you share and store your data. What’s more, as Sheetgo creates connections between your Excel files, they will always be automatically updated by Sheetgo so that you have the most up-to-date version of data.

You can sign up to Sheetgo for free today to get access to the Excel add-in and the Sheetgo web app. All you need to do is sign up with a Google, Microsoft, or Dropbox account!

Sheetgo add-in

As well as transferring data between Excel spreadsheets, did you know that you can transfer data across different spreadsheet files? Check out our articles on how to transfer data from Excel to Google Sheets or how to transfer data from CSV to Excel and vice versa.

Alternatively, take a look at some related blog posts below.

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

How do I automatically move data from one Excel sheet to another?

How do I automatically transfer data from one sheet to another in Excel using macros?

How do you automatically import data into Excel from another Excel file?

How do I pull data from one Excel sheet to another based on criteria?