How to Create a Table in Excel with Multiple Columns

We have a column with First and Last Names, ID, and their respective marks in a single column, starting with B4. We will convert this into a table with 4 columns, each for First Name, Last name, ID, and Marks, respectively.

Method 1 – Apply a Formula with OFFSET, COLUMNS, and ROWS Functions

Steps:

Insert the following formula in cell D4 and press Enter.

=OFFSET($B$4,COLUMNS($D$4:D4)-1+(ROWS($4:4)-1)*4,0)

Copy the formula down using the Fill Handle icon to cell D10.

Copy the formula right to cell G10 in a similar way.

Select a cell in the data and press Ctrl + T. This will open a little window named Create Table.
Mark the My Table has headers checkbox and press OK.

You’ll get a notification box.

Formulas or rich data types in the header row will be removed and converted to static text. Do you want to continue?

Hit the Yes button.

Here’s the output.

Formula Explanation:

In this part, we will break down the following formula we have used above.

=OFFSET($B$4, COLUMNS($D$4:D4)-1+(ROWS($4:4)-1)*4,0)

(ROWS($4:4)-1)*4

The ROWS function returns the number of total rows in $4:4 range, and it’s 1. If it would be $4:6, the function would return 3.
Output: 0

COLUMNS($D$4:D4)-1+(ROWS($4:4)-1)*4

The COLUMNS function works in a similar manner.
Output: 0

OFFSET($B$4, COLUMNS($D$4:D4)-1+(ROWS($4:4)-1)*4,0)

Here, we specify the arguments of the OFFSET function, so the formula becomes OFFSET($B$4, 0,0). It means OFFSET will advance to 0 rows and 0 columns forward from cell B4, and return that cell value. As you copy the formula rightward and downward, the relative references inside the formula change accordingly, and the OFFSET function will return corresponding cell values.

Method 2 – Use Power Query in Excel to Create a Table with Multiple Columns

Let’s assume that the column contains lots of blanks and unwanted characters.

Steps:

Select any cell in the column.
From the Data ribbon, select From Table/Range.
A small window will be opened. Ensure the My table has headers checkbox is unmarked.
Press OK.

It will bring up the power query editor.

Under the Add Column section, go to the Index Column option and click on the drop-down.
Select From 0.
Repeat the process to create a third column.

We need to divide the third column by 4 (as we are working with 4 columns). Select that column.
Click Transform, then go to Standard and use Modulo.

Enter the number of columns, in this case 4.
Hit OK.

Go to Add Column and select Custom Column.

Enter the following in the Custom column formula box.

if[Index.1]=0 then "First Name" else if [Index.1]=1 then "Last Name" else if [Index.1]=2 then "ID" else "Marks(%)"

Hit OK.

We need to duplicate the second column (‘1’ in the image) to get the fifth column (‘4’ in the image). With the column selected, go to Add Column and select Duplicate Column.