Extract Data for Microsoft Excel
How to convert rows and export them to new worksheet(s)
- Open the spreadsheet with the rows you want to extract.
- Select the table and click on the Extract Data icon to run the tool.
If you click on any cell in your table and run the program, it will select the entire table automatically.
- You will see the add-in window listing all columns you have in the table. The 1st row content column next to Columns shows the first item of each column in your table. It can be useful if your worksheet doesn't have headers so you can see what kind of information is in the columns.
- Select the columns with the data you need to export to a new location. All columns are selected by default. You can untick the checkboxes next to those you don't want to copy.
If you have a lot of columns in your table, take advantage of the Select All and Unselect All buttons to instantly tick or untick all the checkboxes in the list of columns.
- It is also possible to indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it doesn't, you can check My table has headers.
The Group by option allows choosing a column with the key values for grouping the extracted data. For example, you have a worksheet with employee information. You want to extract employees into a new workbook by their manager. So each workbook will contain every employee data for only one manager.
You can group by values in a certain column even if this column is unchecked in the list on the add-in window.
Do the following to use the Group by option:
- Enable the Group by feature on the options section by ticking the checkbox next to it.
- Select the column with the key values in the drop-down list.
It can be helpful to add column headers so that the data you export is labeled. You can turn on the Add column headers option by ticking the checkbox next to it.
If you check the Add column headers option while My table has headers is unchecked, you will get column labels instead of their headers.
- Select the Skip empty cells option to avoid empty cells in the extracted data. You can do this by checking the box next to the Skip empty cells option.
The Skip empty cells option will be disabled when the Group by feature is used.
- Tick the Autofit checkbox if you want the new columns with extracted data to automatically adjust the width and display the contents in full.
- Choose if you want to copy the data to the current or to a new workbook. To do this, click on the small arrow next to the Workbook option and select Current or New from the list.
- Select To multiple worksheets if you want to extract each data set to a separate worksheet.
Here is how your data may look like when you select to group by post and extract the rows to multiple sheets:
Choose To one worksheet to have the extracted data in one spreadsheet.
- If you want to name worksheets after values in a certain column, select this column from the Name by drop-down list.
The Name by option will be disabled if the Group by feature is checked.
- ABOUT US