Transpose a CSV File in Excel

While dealing with data, it can often be troublesome to convert it from one tool to another. People often have issues while transposing certain data from text or CSV files to Excel, or vice versa.

In the example below, we will show how can you transfer data that is stored in a CSV file into an Excel file.

Different File Types

When dealing with Excel, you will mostly stumble upon three file types:

  1. Text File (TXT) – It is simple, plain text created in Notepad or WordPad. If you have some data and you paste it in a simple text file, formatting and styles will not be copied, so you will not have formatting issues.
  2. CSV file – It is also plain text and it is useful as it can be opened in any operating system, every text editor, but also Excel. If you transfer spreadsheet data into a CSV file, it will be separated by commas.
  3. Excel Spreadsheet – These types of files can be opened only by the proper applications, i.e. spreadsheet applications. They are not plain as they have a lot of formatting, and calculation options, as well as possibilities, to present information visually. In these file types, information is presented in cells, rows, and columns.

Transposing Csv File in Excel

For our example, we will create a CSV file with the list of NBA players, their teams, and statistics from one night of basketball: points, rebounds, assists, and turnovers to an Excel Spreadsheet:

Text, table

Description automatically generated

As seen, all of our data is in one column, column A. We will open the new Excel file now, and then go to the Data tab >> Get & Transform Data >> From Text/CSV:

Graphical user interface, application

Description automatically generated

In the window that appears, we will find our file, and then select Import:

Graphical user interface, application

Description automatically generated

A new window will appear that will guide us. In the previous Excel versions, the whole process was a bit counter-intuitive, but with Office 365, it looks way better.

The window that we have now shows us the File Origin, Delimiter (we will choose Semicolon as data in CSV are separated with a semicolon), and Data Type Detection (we can choose among different ones: Based on the first 200 rows, Based on the entire dataset, and Do not detect data types):


Description automatically generated

We have various options for File Origin and Delimiter (colon, comma, space, tab, etc.). We got the preview of our data in the window as well. When we click Load, we will end up with the nice table where NBA players will be presented:

Graphical user interface, table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.