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.


Download Practice Workbook

You can download the practice workbook from here.


3 Simple Ways to Edit CSV File in Excel

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.

Dataset to Edit CSV File in Excel


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.

Employ File Tab to Edit CSV File in Excel

  • Secondly, select Open.
  • Thirdly, select Browse.

Open CSV File in Excel to Edit

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

Open Dialog Box to Edit CSV File in Excel

  • 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).

Text Import Wizard - Step 1 of 3 to Edit CSV File in Excel

  • After that, select Next.

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

Text Import Wizard - Step 2 of 3 will appear to Edit CSV File in Excel

  • 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.

Read More: Open CSV File in Excel Without Formatting (2 Easy Ways)


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.

Format CSV File in Excel to Edit

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

Adding New Column to Edit CSV File in Excel

  • 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.

Adding New Rows to Edit CSV File in Excel

  • 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.

Changing Alignment to Edit CSV File in Excel

  • 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.

Changing Cell Styles to Edit CSV File in Excel

  • 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.

Changing Font to Edit CSV File in Excel

  • 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.

Changing Fill Color to Edit CSV File in Excel

  • 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.

Changing Font Color to Edit CSV File in Excel

  • 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.

Adding Borders to Edit CSV File in Excel

  • 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.

Removing Gridlines to Edit CSV File in Excel

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

Read More: Formatting CSV File in Excel (With 2 Examples)


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.

Save CSV File in Excel

  • 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.


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.

Use From Text/CSV Feature in Excel to Edit CSV File

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

Import CSV File in Excel to Edit

  • 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.

Import Data Dialog Box to Edit CSV File in Excel

  • 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.

Add More Data to CSV File in Excel to Edit

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

Adding New Name to Edit CSV File in Excel

  • 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 Import Text File to Excel Automatically (2 Suitable Ways)


Similar Readings


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.

Edit CSV File in Excel Using Legacy Wizards Feature

  • Secondly, select Option.

Selecting Option to Edit CSV File in Excel

  • 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.

Excel Options Dialog Box to Edit CSV File in 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).

Insert CSV File in Excel Worksheet to Edit

  • 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.

Editing CSV File in Excel

  • Then, create a new column for calculating Total.

Creating New Column to Calculate Total to Edit CSV File in Excel

  • 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)

Using SUM Function to Edit CSV File in Excel

  • 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.

Read More: How to Import CSV into Existing Sheet in Excel (5 Methods)


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.

How to View CSV File in Excel

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.


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. For more articles, stay connected with ExcelDemy.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo