- CSV files store data in lines of text which correspond to the spreadsheet’s rows. The data is separated by delimiters such as commas, semicolons, or tabs, for example.
- The “From Text/ Data” option is probably the best conversion method for converting CSV files to Excel.
- The “Text to Column” option can also be used to convert files from the former format to the latter.
Spreadsheets have become a staple of office work and are often used to review or process large amounts of data. If you spent time working with spreadsheet-processing applications, like Microsoft Excel or Google Sheets, you’ve likely stumbled upon a comma-separated values file, commonly known as CSV.
CSV files are a great way of exchanging structured information between compatible and incompatible software. However, to a beginner, or someone who has never encountered CSV files before, they can be surprisingly confusing to work with.
In this guide, we’ll explain how to convert CSV files to Excel using several different methods, along with information about CSV files, like why they’re used and how they work. So, let’s begin.
#1: Convert CSV to Excel using the “From Text/ Data” Option
Importing CVS files into Excel using the From Text/ Data option is perhaps the best conversion method. We say this because it allows you to process tabular data stored in CVS files, which use delimiters other than commas. Delimiters separate pieces of information; in most cases, the information is delimited with commas, semicolons, spaces, or some other symbol or character.
Step 1: Start Microsoft Excel
Microsoft Excel is perhaps the complete spreadsheet processing software available in the current market. Its prominence is attested by its cross-platform presence; besides Windows OS, various Apple OSes and Android use Microsoft Excel to process spreadsheets. For this method, you need Microsoft Excel installed on your computer. Start Microsoft Excel, and start a Blank workbook.
Step 2: Import the CSV File
Once the new spreadsheet is open, select Data from the ribbon, and then click on the From Text/ CSV button on the upper-left-hand side of the window. An Import Data dialog box will appear. From there, select the CSV file you want to import and click on Import in the lower right section of the Import Data dialog box.
A new dialog box will appear, asking you to define certain parameters within the imported data, like File Origin, Delimiter, and Data Type Detection. Under Delimiter select the delimiter used in your CSV file; it should be Comma by default. Once everything’s set, click on Load in the lower right-hand section of the dialog box.
Step 3: Convert to Range
After loading the CSV file, Microsoft Excel will convert the data and load it into a table. If it’s not already selected (and it should be after loading), click on the newly created table. This will change the ribbon selection tab to Table Design. If the current formatting works for you, you can leave the table as is.
However, if you want to format the data within the spreadsheet differently, ensure that the Table Design is selected, then select Convert to Range, and then confirm the action by clicking OK. Your table is now gone, but the formatting still remains. To clear the table-style formatting, select the entire table, go to the Home tab in the Excel’s ribbon, and click on the Clear drop-down menu.
The menu is typically located in the upper right-hand corner of the screen, in the Editing field, and once it’s open, click on Clear Formats. This will convert your data into plain text in your Excel spreadsheet.
Step 4: Save the File
Once you’ve made the necessary changes to your spreadsheet, you can save it as an Excel file. Just click on the File tab in the ribbon (located in the upper right-hand corner), and select Save or the Save As option. When the Save/ Save As dialog box appears, give your file a name and click Save. Your CSV file is now converted into an Excel file.
#2: Convert CSV to Excel using the “Text to Column” Option
Most online guides on how to convert CVS to Excel state that you can use the Save As option to convert the file. And they’re not wrong; opening the CSV file in Excel and simply clicking on File > Save As will certainly change the file extension. But it won’t format the data adequately because of how the data within the CSV file is structured — we promise we’ll discuss that later.
For now, let’s convert CVS to Excel using the Text to Column option.
Step 1: Open the CSV File
There are actually two ways to open a CSV file with Excel, and we’ll start with a more direct approach. Locate the file in your File Explorer (assuming you’re using Windows PC), and double-click the file to open it. If you have Microsoft Excel installed on your computer, the system should recognize it as a default processing software for CSV files.
The alternative to the first method would be to right-click the file to open a context menu and select Excel from the Open With drop-down menu. The second method, however, assumes that you’ve already started Excel and are ready to locate and open the file.
Start by selecting the Open option (CTRL+O), and choose Browse from the Open side-pane to display the Open dialog box. Next, select All Files in the File Type drop-down menu to display your CSV file, select it, and click Open.
Step 2: Convert Text to Column
Once Excel has opened your CSV file, you’ll notice that your data is separated into the appropriate rows. However, it’s not separated into the appropriate columns. Instead, the data is saved in the first column of each row. Luckily, you can automatically sort the data by using the Text to Column option.
Go to the Data tab in the ribbon. Select the rows of data that you want to convert, and click on the Convert to Text option located in the Data Tools field. This will open a Convert Text to Column Wizard that will guide you through the process.
Under the Original data type, choose Delimited, and click Next. In the next section, check the box next to the type of delimiter your CSV file uses (our example uses a comma). The Data preview field will allow you to see the data being assigned to corresponding columns. Click Next, choose the spreadsheet destination, and click Finish. Your data is now separated into the corresponding columns.
Step 3: Save the File
Despite the fact that you’ve sorted and formatted the data, you’re still processing a CSV file. To save the file in Excel file format, click on the File tab and select the Save option. When the Save dialog box appears, give your file a name, and choose Excel Workbook in the Save as type drop-down menu. Your CSV file is now converted into an Excel file.
#3: Convert CSV to Excel using Google Sheets
Microsoft Excel is a fantastic program, but its only downside is that it’s paid software. This means that the first two methods imply that you already own a licensed copy of Excel. However, if you don’t own one, you can easily convert CSV to Excel using Google Sheets.
Step 1: Import the CSV File
Log into Google Sheets using your Google credentials. If you don’t own a Google account, you might have to create one. After you’ve logged in, select the Blank option to create a new spreadsheet. Once the new spreadsheet is open, click on the File menu in the upper right corner of the page, and select Import.
This will open up an Import file dialog box. Click on the Upload tab, and either drag and drop your file or click on the Select a file from your device button to locate the file and upload it. Google Sheets will ask you about the import location and separator (delimiter type) it should use to derive data. You can leave the delimiter selection drop-down at Detect Automatically and click on Import data.
Google Sheets will automatically separate the data into its appropriate cells. Now you can save the file.
Step 2: Save the File
Once you’ve made the necessary changes, if any, go to File, and choose Microsoft Excel (.xlsx) from the Download drop-down menu. Choose the save location for your file, and now you’ve successfully converted the CSV to an Excel file.
Important note: The simplicity of this method begs the question of whether we should use Microsoft Excel in the first place. To be entirely honest, Google Sheets is usually a preferred application for collaboration, but Microsoft Excel is a superior product.
This is especially true if you’re conducting statistical analysis and visualization. Excel can also be used offline, whereas Google Sheets requires an Internet connection.
What is a CSV File Format?
As stated at the beginning of our guide, CSV files are simple text files that contain tabular data. They’re mostly used to exchange structured information between incompatible software solutions. Of course, this implies that both solutions can process CSV files.
What makes CSV files particularly interesting is the way they store data. Data is stored in lines of text and separated with delimiters (commas, semicolons, tabs, or other characters). These lines of text correspond to rows in the spreadsheet.
It’s important to note that delimiters determine the corresponding columns. So, for example, if specific information is listed in the second line of text after the fourth delimiter, it will be found in the B5 cell in Excel — assuming that you took cell A1 as your starting cell.
How to Convert CSV to Excel: Final Thoughts
Though they seem like unnecessary extra work, CSV files are great for sharing tabular information between different pieces of software. We hope our guide has helped you learn how to convert CSV to Excel, so you don’t have to rely on predatory online solutions that might compromise your data.
Interested in other helpful tips? Click on the links below:
- How to Delete Instagram Messages Permanently: Do you wish to delete a single message or an entire conversation? The procedure has been outlined here.
- How to Download Your Data From Instagram, With Photos: Do you know you can manage your data on Instagram? Find out how to do so in this article.
- How to Convert JPG to SVG in Easy Steps: Do you want to convert your image file to another format that will ensure that picture remains as detailed as can be no matter what size you make it? Find out how to do just that, in this article.