How to Extract Data From Excel – Manually and Automatically
Excel is a versatile instrument that allows you to manage your data in multiple ways. These vary from keeping track of your metrics to performing advanced calculations and building comprehensive dashboards with the help of native functionality. However, for some purposes you might need to extract data from Excel. For example, to load it into a database or to create a backup copy of your information, or simply to transfer it to another workbook.
In this article, we explain how you can extract data from Excel in different ways – from downloading it manually to exporting it automatically on a schedule with a third-party tool.
What are the main methods to extract data from Excel?
In Excel, data extraction is usually the process of extracting specific portions of data from a larger dataset. There are different methods that allow you to do this.
- Manual selection. In this case, you manually select portions of data that you need to extract, copy them, and insert into another spreadsheet or app.
- Filtering data. When you use this method, you apply a filter that extracts the necessary data according to your criteria. If you opt for the advanced filter, then it can also insert the filtered information into a new sheet.
- Using functions and formulas. As an alternative to filters, you can extract specific data from a spreadsheet using Excel’s functions and formulas. The advantage of this method is that formulas can update your filtered information automatically if there are changes in the main dataset.
- Using third-party tools. There are data integration solutions that allow you to connect your spreadsheet to another Excel workbook, as well as to other apps. Such a tool can extract data from Excel automatically according to the schedule you set. We’ll explore this in more detail in the How to extract data from Excel automatically section.
When should you use each of these methods? This depends on your specific needs and situation. For instance, manual selection is the most simple and straightforward of the methods, but it’s not very convenient for large datasets or repetitive tasks. Filtering data is a good way to extract exactly what you need when there are a lot of different criteria. At the same time, using filters is a one-time action. If you want your filtered data to be updated automatically and your dataset changes all the time, then functions and formulas will be a better fit here.
And, finally, if you are extracting data for building reports or data visualizations, then using a data integration tool will be a reasonable choice. This will allow you to streamline your data flows, blend data from different sources in one place, build self-updating dashboards, and more.
How to extract data from Excel manually
As we’ve already mentioned, manual extraction is the simplest extraction method. To use it, you need to open your dataset and select the data you want to extract.
For example, we have a comprehensive marketing campaign report, but we want to extract only the list of campaigns and their statuses. It will look like that:
Then, copy the selected data and paste it into a new spreadsheet or another app where you need to work with that information.
Alternatively, you can place the extracted data on a separate sheet and then export it in the CSV format. Then you will be able to import this CSV file into another app.To export data with this method, go to the File in the main menu, and then select Save a Copy.
In the next step, select the data format you need. In our example, it’s CSV, but you can find many other options on the list, including .TXT and PDF. Choose where you want to save the file and click Save. The CSV file will appear in the selected location.
How to extract filtered data from Excel
Method 1 – How to use the basic filter and the FILTER function
One of the simplest ways to extract filtered data from an Excel spreadsheet is to select the filtered information, copy it, and paste in a new worksheet or another app. Let’s see how to do this. In the screenshot below, you can see an example dataset listing ad campaigns with different statuses.
Let’s filter only Paused campaigns to transfer them to another sheet. Press Filter in the main menu and then click the drop-down arrow above the column with the filter criteria – in our case, it’s the column with the campaign statuses.
In the next step, we need to select the filter criteria. In this case, it’s the paused status. Then you can press Apply Filter or just close this window if the Auto Apply option is enabled.
Once this is done, you can see the filtered data. Now we have only paused campaigns on the list.
Another option is to do this with the FILTER function. For this, you can use the following formula: =FILTER(range;include;[if_empty]). Here, “range” stands for the range of cells you want to filter, “include” stands for the value you are looking for. Instead of [if_empty] you can just put zero.
For example, =FILTER(A1:A65;E1:E65>20;0) will return the list of the campaigns with a budget over 20.
Here’s the filtered data:
The filtered data can be copied and pasted into a new spreadsheet or document. Alternatively, you can extract the filtered information in other formats using the instructions in the section above, How to extract data from Excel manually.
Method 2 – How to extract data from Excel based on criteria with advanced filtering
Another method is to use the Advanced Filter functionality. To do so, create a new sheet in the workbook with your dataset and place there filter criteria. In our example, we want to extract only enabled campaigns. So our filter criteria consist or label – Campaign status, and the desired value – Enabled. Here’s what it looks like:
After this, go to the Data tab and click Advanced in the Sort and Filter section.
You will see the Advanced filter menu. Select Copy to another location, then click the arrow on the right of the List range field. Then, select your whole dataset. The cell range in the List range field will appear automatically.
Then, click the down arrow on the right to return to the previous menu.
After this, press the arrow on the right from the Criteria range field. Switch to the new sheet where we already placed filter criteria and select the two cells containing the label and its value (in this example, Campaign status and Enabled).
Finally, click the arrow on the right from the Copy field and select any empty cell on the new sheet with the criteria. Then, press OK.
After this, the filtered data will be imported into the new sheet. In our case, we got the list of all campaigns that have the enabled status:
Now you know how to use the advanced filter and extract data from Excel based on criteria. But it’s worth remembering that data filtered in this way won’t be updated automatically when your dataset changes. However, you can achieve this if you filter data using functions and formulas instead.
How to extract specific data from a column in Excel
There are several ways to extract specific data:
- Filtering data. You can check the instructions from the previous section to see how to filter data in Excel and transfer it to another place.
- Using functions. There are various functions that can help you with that, from VLOOKUP to such as LEFT, MID, and RIGHT. Which function to use, depends on the complexity of actions you want to perform with your data.
As an example, let’s see how to extract specific data using the INDEX and MATCH functions. When we use these functions together, we can, for instance, look for a campaign number in one column and extract the matching campaign’s title from another column.
The formula syntaxis: =INDEX(column_to_extract_from,MATCH(value_to_match, column_to_match_against,0))
Here, column_to_extract_from is the range of cells in a column from which you want to extract data, and value_to_match is a corresponding information you use for the search – in our example, value_to_match is a campaign number. And column_to_match_against is the column where you need to find your value_to_match – in this case, it’s the column with campaign numbers.
For our dataset, we can use the following formula to extract the title of the campaign with the 117 ID number:
=INDEX(B4:B20;MATCH(117;C4:C20;0))
As a result, the formula returned the title of the campaign 117 in the cell containing the formula:
This is a useful method when you need to extract specific information from a column. However, if the column contains several items matching the specified value (for example, several campaigns with the same ID number), this formula will only return the first item. If you want to get a list, it’s better to use filtering.
How to extract data from an Excel cell
In addition to extracting data from the columns, you might need some tips for transferring data from a particular cell. In some cases, you might have mixed data in a cell and need to extract only part of this data. Let’s have a look at how to do this.
How to extract partial data from an Excel cell
For example, you have a cell containing several words – like name and surname, or name and position, or a good type and its price. In such a case, you can extract data from an Excel cell by using such functions as LEFT and RIGHT.
Here’s how to use the LEFT function. The basic syntax is simple:
=LEFT(cell_number;number_of_characters)
For example, =LEFT(A3;4) will extract the first 4 characters on the left from the A3 cell.
But what if you need to extract the first word from every column, but the number of characters in these words are different? Then, it’s better to use a more complex version of the formula:=LEFT(cell_number;FIND(" ";cell_number)-1)
For example, =LEFT(A2;FIND(" ";A2)-1) will find the first space character in the A2 cell and return all the characters on the left to the space.
In this example, we used this formula to extract the first name from the cell.
You can also use the RIGHT function to extract the last name from the cell using this formula: =RIGHT(A4;LEN(A4)-FIND(" ";A4)), just place your cell number instead of A2. In this case, the LEN function determines the total length of the string in the A4 cell, and then we subtract the first word, which we determine in the same way as in the previous case.
As a result, the formula extracted the second name from the cell:
How to extract data from Excel automatically
In the previous sections, we’ve explained how you can extract data from Excel manually. However, you can also do this automatically with the help of a third-party solution. If you need to extract data often, then automation is a reasonable choice as it allows you to save time, streamline processes, build self-updating reports and dashboards, and more.
In our example, we’ll show you how you can extract data automatically on a schedule with the help of Coupler.io – an all-in-one data analytics and automation platform. In addition to data analytics & workflow automation services, Coupler.io provides a handy data integration solution. The latter allows you to export data automatically from 60+ business apps to Excel, Google Sheets, and BigQuery. In our case, we will use it to extract data from Excel. This solution was designed for business users, so you don’t need any tech background to use it.
Here’s how to export your Excel data with the help of Coupler.io.
We are going to extract the data related to all of the campaigns that have the status “Enabled”:
- Sign up for Coupler.io (no credit card required). You can use your Microsoft account to sign up in one click.
Go to the My importers tab and click Add new. Select Excel as a data source and choose the destination for your data. In our example, it’s also Excel as we want to transfer the data to another workbook. Alternatively, you can import your information to Google Sheets or BigQuery.
- Connect your Microsoft account. If you used it to sign up for Coupler.io, then it should be already connected.
- Then, specify the file and the sheets you need to extract data from.
- By default, Coupler.io will extract all data. If that’s not what you need, you can specify the range of cells to extract.
- If you want to export data from several spreadsheets to one place, click Add one more source and repeat the previous step for the new file. Alternatively, you can add other apps as additional data sources.
- Then, select the workbook and sheet where you want to import your data. If you want to transfer it to Google Sheets or BigQuery, this step is similar for these two destinations as well.
- Select the preferred import mode. While it won’t affect the first data import, it will be important for the scheduled updates. Select Replace if you want Coupler.io to write fresh data over the previous version of the dataset. Choose Append if you want to place the updated dataset under the previous one without overwriting it.
- After this, specify a schedule for the automated updates and press Save and run. That’s it! Coupler.io will regularly transfer fresh data from your Excel spreadsheet to the destination file.
- Here’s an example of the transferred data:
If you want to export filtered data automatically, you can create a separate sheet where data will be filtered with formulas, functions, and/or filters, and then specify this sheet as your data source.
Which data extraction method is the best?
In this article, we’ve explored various methods that you can use to extract data from Excel for different purposes. You can do this by using filters, functions and formulas, and even by setting an automated integration with the help of a third-party tool. The particular method that is best for you ultimately depends on your tasks and goals.
Filters are a good choice if you need to quickly extract the data for a task that is not recurring. If you want to filter data on a permanent basis, then it’s better to use formulas – once applied, they will update your filtered data automatically if your main dataset changes.
If your goal is to transfer the extracted data from Excel to another app and you need to do it regularly – then you can consider using Coupler.io. It allows you to quickly export data to another Excel workbook, Google Sheets, or BigQuery. In addition, it can update your Excel data in the destination file automatically according to the schedule you set.
