Exporting data from Excel means saving or transferring the spreadsheet’s information to another file format (such as CSV, TXT, or PDF). There are several methods available for exporting data to other file formats & among them simple copy-paste, "save as"
option, VBA, object insertion, embedding Excel files will be used in this article.
In this tutorial, you will learn to export data from Excel to another file format with different suitable techniques. There will be alternatives too for some methods so that you can apply the one you prefer. There will be some advanced procedures for exporting data from Excel to vCard & XML format but no worries, we’ll guide you through the appropriate breakdown of the steps & proper visualization.
We have a dataset of football players, and their clubs in the image above and converted it into a CSV(Comma Separated Values) file as shown.
In this blog post, you will learn how to export data from Excel to
- Text or CSV files.
- PDF file (Full or Only Selection).
- Microsoft Word (Using Copy & Paste, Object Insertion, and Embedding Spreadsheet).
- Microsoft PowerPoint (As a Picture).
- Google Sheets.
- vCard (Virtual Contact File)
- XML (eXtensible Markup Language)
- SQL Server.
📒Note: We have used Microsoft 365 to prepare the dataset for this article. You can apply the mentioned methods in versions from Excel 2007 onwards.
What is Exporting Data?
Exporting data from Excel involves saving or transferring the spreadsheet information to another file (CSV, TXT, PowerPoint, PDF, or SQL Database). You can use options like “Save As,” copying and pasting, etc. to export data from Excel.
Why Do We Need to Export Data from Excel?
We need to export data from Excel because-
- Exporting enables sharing Excel data with individuals who do not use Excel.
- Facilitates collaboration across diverse platforms and software.
- Supports the integration of Excel data into external systems.
- Allows the creation of reports in alternative formats (CSV, TXT, PDF) or databases.
1. Using the “Save As” Command to Export Excel Data to Text or CSV File
You can export data from Excel to Text or CSV using the “Save As” command. It is one of the most used methods.
We have a dataset of football players below in Excel. We will export this data to a CSV file.
To export this data using the “Save As” command-
- Click on the File tab at the top-left.
- Click on Save As> Browse.
- Select CSV (Comma Delimited) in the Save As Type box> Choose Folder Location> Type Folder Name> Click on Save.
- If you open the app in Notepad, you will see the exported data as the following image where each data is separated by comma.
Note: If your Workbook contains multiple worksheets, it will only export the data in the Active worksheet. To save all sheets, save them individually using a different file name for each, or choose a file type that supports multiple sheets.
When the dialogue box is like the image below, click on OK to continue exporting.
2. Exporting Data from Excel to PDF
We will go through the ways of exporting data from Excel to PDF without using any converter tool here. So, in this section, we will export full, or part of Excel data (Selection) to PDF file format. As in the previous method, we will use the Save As command here.
2.1. Exporting Full Excel Data to PDF
Suppose, We have a dataset about Employee details as the image below in a worksheet. We will export this whole worksheet into PDF file format using the Save As command.
To export the Excel dataset above into PDF using the Save As command-
- Click on File> Save As> Browse.
- Select PDF in the Save As Type box> Choose Folder Location> Type Folder Name> Click on Save.
- This will save the worksheet as a PDF in the desired location. It will appear as a PDF as the following image.
2.2. Export Part of Excel Data to PDF
Now, we will export the selection (B5:E15) to PDF. It is the same way as mentioned in the previous method. This will export the selection marked by a red border to PDF.
To export to PDF-
- Select the range from B5 to E15.
- Select PDF in the Save As Type box> Choose Folder Location> Type Folder Name> Click on Options.
Then, the Options dialogue box will appear. In there-
- Check the Selection check box under Publish what.> Click on OK.
- Click on Save.
Exported data will appear in the PDF as in the image below.
2.3. Using VBA Macros to Export Full Excel Data to PDF
If you are quite familiar with VBA macros, you can use VBA macros to export your Excel to PDF. Now, we will export the same dataset fully to PDF using the VBA macros. To export-
- Click on Developer ribbon> Visual Basic in the Code option.
- Click on Insert tab> Module.
- In the module, copy and paste the code below.> Save the file as a macro-enabled file.> Press F5.
Code:
Sub SaveAsPDF()
' Define variables
Dim ws As Worksheet
Dim savePath As String
' Set the current worksheet
Set ws = ActiveSheet
' Specify the path and filename for the PDF
' Change "C:\Path\to\save\file.pdf" to your desired path and filename
savePath = "C:\Users\SHUVO\Desktop\HP\HP_02\Output File\Full PDF by VBA.pdf"
' Save the worksheet as PDF
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, Quality:=xlQualityStandard
' Optional: Display a message when the PDF is saved
MsgBox "PDF saved successfully!"
End Sub
dialogue box named Macros will appear, In there-
- Select the statement
"SaveAsPDF"
> Click on Run.
- When the PDF is completed, it will show the message box that shows
"PDF saved success"
.
This will create a PDF into your specified location as in the image below.
3. Exporting Excel Sheets to Word
In this part, we will learn to export data from Excel to Microsoft Word using Copy-Paste, Object Insertion, and Embedding Spreadsheet methods.
3.1. Using Copy Paste Method
To export from Excel to Word without losing any format, you can simply copy the data, and paste it into Word. It is a very simple and basic way to export data.
Suppose we have a dataset as the image below. And we will export it into Microsoft Excel using Copy-Paste.
To export the data-
- Select the data.> Right-click on the mouse.> Click on Copy. (Or simply press Ctrl+C).
- Paste the data into Word (Using keyboard shortcut (Ctrl+V). The exported data will appear in a table as in the image below. Now, adjust the column width to fit the pasted data.
3.2. Using the Object Insertion Method
You can also export the data by inserting the Excel data as an object in Word. In this example, we will export the data (about footballers) below to Word.
To export the data above to Word-
- Open Word> Place the cursor on the page where you want to export the data.> Click on the Insert > Object.
- Click on Create from File in the Object dialogue box> Browse.
- Find and select the worksheet. (Make sure your workbook is opened and place the cursor on the dataset that we want to export.)> Click on Insert.
- Click on OK.
This will export the data from Excel to Word as in the image below.
3.3. Embedding Spreadsheet into Word Document
Here, we will embed the Excel data into Word. It allows us to use Excel without even launching the program.
Suppose we want to add the data table as the following image into the Word.
To embed Excel to Word-
- Open Word.> Click on Insert> Table.> “Excel Spreadsheet” in the menu.
- This will enable a spreadsheet interface into your Word. Now, copy- paste the data to this table or simply type the data in the sheet.
- When done typing or Copy pasting, the data will appear as follows.
Benefits of Transferring Data from Excel to Word Document
The benefits of transferring data-
- Make numbers, tables, and charts from Excel look awesome in your Word document.
- Share your data along with explanations in Word, so it’s not just numbers but a clear story.
- If your Excel numbers change, Word updates automatically.
- Share detailed reports easily by blending Excel’s data with Word’s explanations.
- Have all your important info, including Excel data, in one Word doc for easy handling.
- When you print, Word lets you control how everything looks.
4. Exporting Data from Excel to PowerPoint
You can also export or embed your data to PowerPoint. To do it, you can use Copy-Paste or “Export as a picture” to export the data from Excel to PowerPoint. But we won’t recommend using Copy-Paste, rather we suggest to export the data as a picture.
So here, we have a dataset below. We will export it to PowerPoint as a picture.
To export the data as a picture into PowerPoint-
- Select the data.> Right-Click.> Click on Copy in the menu.
- Open the PowerPoint.> Press Ctrl+Alt+V.> Check Paste.> Select Picture (Windows Metafile) in the As:> Click on OK.
- Or you can Right-click on the mouse.> Click on the Paste as Picture icon in the menu..
This will export the data in the PowerPoint slide as the image below.
5. Exporting Data from Excel to Google Sheets
In this part, you will learn to export data from Excel to Google Sheets. Let’s say, we have a dataset as the image below in Excel. We will export this file into Excel.
To export data from Excel to Google Sheets-
- Go to the following link to open the Google Sheets webpage.> Click on the Blank page option to open a blank sheet.
- Click on File.> Import.
- Click on Upload.> Browse.
- Select the file (i.e., Different Worksheet).> Click Open.
- Again, click on Import Data.> Open Now.
- Exported data will appear as follows in the Google Shorts.
Note:
If you have multiple sheets, it will upload the whole workbook.
Things Lost While Converting Excel Files to Google Sheets:
Some things in the Excel file will not work after converting to Google Sheets are:
- Macros and VBA Codes
- Interactive Controls
- Charts, Split Worksheets, and Linked Shapes.
Format Compatible with Google Sheets:
The supported formats of Excel files are as follows:
.xls, .xlsx, .xlsm,.xlt, .ods, .csv, .tsv, .tab
6. Exporting Excel Sheets to XML
In this portion, we will learn to export Excel sheets to XML files. XML, or eXtensible Markup Language, is a way to structure data using tags. It’s readable by both humans and machines.
To export data from Excel to XML, we need Schema first. XML Schema (XSD) is like a set of instructions for XML documents, telling them how to organize and validate data. It includes rules for elements, attributes, and data types.
In this method, we have a dataset in Excel about Projects. We will export this data to XML.
- Write the schema as follows in Notepad (According to your data).
Note: If you don’t know how to write schema, don’t worry. You have heard the name ChatGPT? Use it to write your schema. To do so, go to ChatGPT web. Then explain your dataset to it like ” Provide me a schema that has X columns named A, B, C etc.”
Where, X=Number of columns in your dataset.
A, B, C are the name of columns name.
Schema Code:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Projects">
<xs:complexType>
<xs:sequence>
<xs:element name="Project" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="ProjectID" type="xs:string"/>
<xs:element name="ProjectName" type="xs:string"/>
<xs:element name="StartDate" type="xs:date"/>
<xs:element name="Analyst" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
- Save it with an extension “.xml” (i.e., Export to XML.xml).> Click on Save.
- Click on the Developer > Source.> XML Maps> Add in the “XML Maps” dialogue box.
- Select the file.> Click on Open.
- Click on OK.
- Select Column header.> Double click on “ProjectID”.
- The first column will appear as follows.
- Repeat the procedure for each column and the data will appear as a table.> Click on the Developer > Export.
- Select the folder where to export.> Click on Export.
The exported data will appear as follows if you open the file in Notepad.
7. Exporting Data from Excel to vCard
Windows has its own feature to create VCF files from Excel. But we need to modify the file extension first. Let’s have a look at the description below. Keep in mind that any unnecessary header may cause you problems.
A VCF, or vCard is like a digital business card. It holds contact info like names, addresses, and emails. You can share it easily through emails or messages, and it helps to add new contacts to phones or email lists hassle-free.
We will convert the dataset that contains people’s names, Emails, and Mobile numbers into a VCF file or simply vCard.
But before we start working, we have to convert these data into a CSV file. To convert-
- Click on the File tab at the top left.
- Click on Save As> Browse.
- Select CSV (Comma Delimited) in the Save As Type box> Choose Folder Location> Type Folder Name (i.e., Contacts)> Click on Save.
This will save all the contact information as a CSV file in that folder as the image below.
Now, we will export this data into a VCF file. To start exporting-
- Click on the Windows icon at the bottom left corner of pc.> Search for the Run option from there. >Click on
- Type Contacts into the Open box in the Run dialogue box.> Click on OK.
This will open the Contacts pane. In there-
- Click on Import.
- Select CSV (Comma Separated Values) in the “Import to Windows Contacts” dialogue box.> Click on Import.
- Click on Browse.
- Select the file. (i.e., Contacts).> Click on Open.
- Click on Next.
- Check the Email.> Select ‘E-mail Address” in the box of the “Change Mapping” dialogue box.> Click on OK.
- Again, Check the Email.> Select ‘E-mail Address” in the box of the “Change Mapping” dialogue box.> Click on OK.> Finish.
Contact will appear as in the image below. In there-
- Click on Export.
- Select vCards(folder of .vcf files) in the dialogue box.> Click on Export.
- Click on “Make New Folder”.> Rename the for “Contact VCF”.> Click on OK.
- Click on OK.
- Choose vCards(folder of .vcf files).> Click on Close.
- All the exported vCard will appear in the following image.
Click on any VCF files in the folder it will show all the details about the person in a vCard.
8. Exporting Data from Excel to SQL Server
In this method, we will learn how you can export data from Excel to an SQL server. We will export the dataset below about Products into our SQL Server. We stored the dataset in the worksheet named Data (For SQL) in the workbook. Before you start working, make sure that you have installed the SQL server.
For demonstration purposes, we have used the SQL Server Management Studiol’19.
Here we will export the data above into Database_A as shown in the image below.
To export the data to the SQL server-
- Open SQL Server Management Studio’19.> Select > Right-Click on that.> Click on Tasks.> Import Data.
- Click on NEXT in the SQL Server Import and Export Wizard.
- In the wizard, select Microsoft Excel in the Data Source> Click on Browse.
- Navigate to the desired folder and select the Excel file. > Click on Open.
- Again, click on NEXT in the Wizard.
- Select Microsoft OLE DB Driver for SQL Server in the Destination > Click on Properties.
- Now, enter the Name of your SQL Server in the “Select or enter a server name” box.> Select Windows Authentication in the “Enter information to log on to the server”.> Click on Test Connection.> OK in the Dialogue box that shows “Test Connection Succeeded”.
- Select Database_A in the “Select the database” box.> Click on OK in the “OLE DB Driver for SQL Server Data Link” dialogue box.> Again OK.
- Click on NEXT in the Wizard.
- Check “Copy data from one or more tables or views”.> Click on NEXT.
- Select the desire sheet Data (For SQL).> Click on Preview to make sure you are exporting the right data.> OK. > NEXT.
- Check “Run immediately” in the Wizard.> Click on NEXT.
- Click on Finish to complete the task.
- When the message “The execution was successful” comes up, click on Close in the Wizard.
- Select ’Data (for SQL)$’ from Database_A.
- Click on New Query.> Write the code “select*from. [‘data(for SQL)$’” in the new query.> Click on Execute.
This will run the code and show the preview of the database that we have exported from Excel.
How to Export Data from Excel and Create a Folder List with File Explorer?
Isn’t it amazing that you can create multiple folder based on the cell’s names in Excel? Yeah, you will learn how you can Export data from Excel and create a folder list based on the cells’ values.
Suppose we have a dataset containing the names of people. We will automatically create a folder for each name using a batch file in Notepad.
- Create a new column and name it “Folder Name”.> Select Cell C6.> Use the Following formula in the cell to add MD (Make Directory) before every name.
=”MD” &” “&B6
- Press Enter.> Copy the data from cell C6 to C15.
- Open Notepad.> Paste the data.
- Click on File.> Save As.> Choose the Folder where to save it.> Name the file.> Use .bat (it creates batch files.) as an extension at the end of the name (e., Create Folder.bat).> Click on Save.
- Double click on this Create Folder.bat file, this will create a folder for each name as shown in the image below.
Download Practice Workbook
You can download the workbook that we’ve used to prepare this article.
In this Excel tutorial, we have learned all the available easy techniques for exporting data from Excel to other files like Text or CSV files. PDF file (Full or Selection Only), Microsoft Word, Microsoft PowerPoint, Google Sheets, vCard, XML, SQL Server.
Hopefully, this article was helpful and easy to understand those methods. Yet, if you have any problem, please drop your comments below.
Export Data from Excel: Knowledge Hub
- Export Excel to Txt
- Export Excel to PDF
- Export Excel Data to PowerPoint
- Export Excel to CSV
- Export Data from Excel to Google Sheets
- Export Data from Excel to Word
- Export Excel to XML
- Export Data from Excel to vCard
- Export Data and Save as Image
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!