How to Edit CSV File in Excel (3 Simple Ways)

You can open a CSV file in Excel and edit the data to get a more clear idea about the file. The main objective of this article is to explain how to edit CSV file in Excel.

How to Edit CSV File in Excel: 3 Simple Ways

For this article, I have taken a CSV file. The CSV file contains the following data. Here, I will import this CSV file into Excel and then show you how you can edit the file in Excel. I will explain 3 different methods.

1. Employ File Tab to Edit CSV File in Excel

In this first method, I will open the CSV file in Excel employing the File tab and then I will edit the file. After that, I will save the file in CSV format. Let’s see the steps.

Step-01: Open CSV File in Excel

In this step, I will open the CSV file in Excel.

Firstly, go to the File tab.

Secondly, select Open.
Thirdly, select Browse.

After that, the Open dialog box will appear.
Select the file type as Text Files.
Then, select the CSV file.
Next, select Open.

Now, Text Import Wizard – Step 1 of 3 will appear.
Afterward, select Delimited.
Then, click on the drop-down option for File origin.
Next, select 65001:Unicode (UTF-8).

After that, select Next.

Here, the Text Import Wizard – Step 2 of 3 will appear.
Next, select Comma as Delimiters.
Then, select Next.

Afterward, the Text Import Wizard – Step 3 of 3 will appear.
Select the Column data format you want. Here, I selected General.
Lastly, select Finish.

Finally, you will see that you have opened the CSV file in Excel.

Step-02: Format CSV File in Excel

Here, I will edit the CSV file in Excel. I will change the formatting to give the file a better look. To begin with, I will add a new column before the table.

First, Right-click on column A.
Then, select Insert.

After that, you will see that you have inserted a new column before the table.

In the same way, add 3 rows before the table.
Then, select the cell range B2:E2.
Next, go to the Home tab.
After that, select Merge & Center.

Then, write the heading you want to give to this dataset in the merged cell.

After that, I will change the alignment of the Excel sheet.
Select the whole sheet by clicking on the marked portion in the following picture.
Next, go to the Home tab.
After that, select the alignment you want. Here, I selected Middle Align.

Now, I will change the alignment of the data I got from the CSV file.
Select cell range B4:E10.
Then, go to the Home tab.
After that, select Center.

Now, I will change the cell style for the heading.
To do so, select the cell.
Then, go to the Home tab.
Next, select Cell Styles.

After that, a drop-down menu will appear.
Select Heading 2 from the drop-down menu.

Now, you will see the cell style of the heading has changed.

Now, I will change the Font Size of the heading.
Select the cell where the heading is.
Then, go to the Home tab.
After that, click on the Drop-down menu for Font Size.
Next, select the Font Size you want. Here, I selected 16.

Afterward, I will change the Fill Color of the heading.
Select the merged cell.
Next, go to the Home tab.
Then, click on the drop-down option for Fill Color.
Further, select the color you want.

Here, I will change the Font Color.
Select the cell where you want to change the font color.
Then, go to the Home tab.
Next, click on the drop-down option for Font Color.
After that, select the color you want.

In the following picture, you can see the final look at my heading.

Next, change the formatting of the other cells as you want in the same way.

After that, select cell range B4:E10.
Then, go to the Home tab.
Next, click on the drop-down menu for Borders.

Afterward, select All Borders.

Now, you will see borders are added to your dataset.

Next, select the whole Excel worksheet by clicking on the marked portion in the following picture.
Then, go to the View tab.
After that, uncheck the Gridlines.

Finally, in the following picture, you can see how my file looks after formatting.

Read More: Formatting CSV File in Excel

Step-03: Save CSV File in Excel

In this step, I will show you how you can save the excel file as a CSV file.

In the beginning, go to the File tab.

After that, select Save as.

After that, click on the drop-down option.
Then, select CSV (Comma delimited).

Next, select Save and your file will be saved as CSV.

Read More: How to Fix CSV File in Excel

2. Use From Text/CSV Feature in Excel

From Text/CSV is a useful feature from the Get & Transform Data group. I will use this feature to edit CSV file in Excel. Let’s see the steps.

Step-01: Import CSV File in Excel

To begin with, I will import a CSV file into Excel.

Firstly, go to the Data tab.
Secondly, select From Text/CSV.

Thirdly, the Import Data dialog box will appear.
Select the CSV file.
Then, select Import.

Here, another dialog box will appear.
Select 65001:Unicode (UTF-8) as File Origin.
After that, select Comma as Delimiter.

Next, click on the drop-down option for Load.
Then, select Load To.

Consequently, the Import Data dialog box will appear.
Select Existing worksheet.
After that, select the location where you want to import the data.
Lastly, select OK.

Finally, you will see that you have imported the CSV file in Excel.

Step-02: Add More Data to CSV File in Excel

Now, I will edit the data I got from the CSV file in Excel. Here, I will add more data to the table.

In the beginning, format the table by following the procedure from Step-02 of method-1.

Then, select the first cell after the table.
Next, write a student’s Name in that cell.

Afterward, press Enter and you will see a new row is added to the table.

Next, enter the obtained marks for that student.

In the same way, I added data for two more students and this is how my final table looks.
After that, you can Save the file as CSV by following the procedure from Step-03 of method-1.

Read More: How to Merge CSV Files in Excel

3. Edit CSV File in Excel Using Legacy Wizards Feature

You can edit CSV file in Excel easily by turning on Legacy Wizards in Excel. In this method, I will explain how you can turn on Legacy Wizards and then use this feature to edit CSV file in Excel. Let me show you how it is done.

Step-01: Add Legacy Wizards in Excel

Here, I will add the Legacy Wizards in Excel.

Firstly, go to the File tab.

Secondly, select Option.

After that, the Excel Options dialog box will appear.
Select the Data tab.
Then, check the FromText (Legacy) option.
Next, select OK and Legacy Wizards will be added to Excel.

Step-02: Insert CSV File in Excel Worksheet

Now, I will insert the CSV File in Excel.

In the beginning, go to the Data tab.
Then, select Get Data.
Next, select Legacy Wizards.
After that, select From Text (Legacy).

Now, the Import Text File dialog box will appear.
Select the CSV file you want to edit.
Then, select Import.

After that, the Text Import Wizard – Step 1 of 3 will appear.
Select Delimited.
Next, select 65001:Unicode (UTF-8) as File origin.
Then, select Next.

Next, the Text Import Wizard – Step 2 of 3 will appear.
Select Comma as Delimiters.
Afterward, select Next.

Then, the Text Import Wizard – Step 3 of 3 will appear.
Select the Column data format you want. Here, I selected General.
After that, select Finish.

Next, the Import Data dialog box will appear.
Select Existing Worksheet.
After that, select the location where you want the data.
Then, select OK.

In the end, you will see that the data from the CSV file is imported to your Excel sheet.

Step-03: Edit CSV File in Excel

In this step, I will edit the data I got from the CSV file. Here, I will calculate the Total marks of the students.

First, format the table by following the procedure from Step-01 of method-1.

Then, create a new column for calculating Total.

After that, select the cell where you want to calculate the Total. Here, I selected cell F5.
Then, in cell F5 write the following formula.

=SUM(C5:E5)

After that, press Enter to get the result.

Here, in the SUM function, I selected cell range C5:E5 as numbers. Now, the formula will return the summation of cell range C5:E5.

Then, drag the Fill Handle down to copy the formula to the other cells.

Finally, you can see that I have copied the formula to the other cells and got my desired results.
Now, you can Save the file as CSV by following the procedure from Step-03 of method-1.

How to View CSV File in Excel

In this section, I will show you how you can view CSV file in Excel. For this example, I have taken the following CSV file. It contains both commas and semicolons. I will show State in one column and Sales Person in another column. Let’s see the steps.

Steps:

First, go to the Data tab.
Next, select Get Data.
Afterward, select Legacy Wizards.
Then, select From Text (Legacy).

Next, the Import Text File dialog box will appear.
Then, select the CSV file you want to edit.
After that, select Import.

Then, the Text Import Wizard – Step 1 of 3 will appear.
Next, select Delimited.
Afterward, select 65001:Unicode (UTF-8) as File origin.
Then, select Next.

Next, the Text Import Wizard – Step 2 of 3 will appear.
Select Semicolon as Delimiters.
Afterward, select Next.

After that, the Text Import Wizard – Step 3 of 3 will appear.
Select the Column data format you want. Here, I selected Text.
Then, select Finish.

Next, the Import Data dialog box will appear.
After that, select Existing Worksheet.
Select the location where you want the data.
Then, select OK.

Finally, you will be able to view the CSV file in Excel.

In the following picture, you can see that I have formatted the data to give them a clear look. But, it is optional.

Download Practice Workbook

You can download the practice workbook from here.

Editing CSV File.xlsx

Conclusion

So, you have reached the end of my article. Here, I tried to explain how to edit CSV file in Excel. I hope this article was helpful to you. If you have any questions then feel free to let me know in the comment section below.

<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!