How to Create a Table in Excel with Multiple Columns

If you want to create a table in Excel; with multiple columns, you are in the right place! In this article, we have discussed how to use Excel formula and Power Query Tool in 2 different methods to do this. So stay tuned with us!

Watch Video – Create a Table with Multiple Columns in Excel

Create a Table in Excel with Multiple Columns: 2 Simple Methods

First of all, let’s introduce to the dataset on which we will apply the upcoming methods in this section.

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

Sample Dataset to Create a Table in Excel with Multiple Columns

So, without any further discussion, let’s explore the ways.

1. Apply a Formula with OFFSET, COLUMNS & ROWS Functions

In the first method, we will combine OFFSET, COLUMNS & ROWS functions to make a formula with which we will create a table with multiple columns and rows. Later we will convert that to an Excel table. Execute the following steps to do this.

📌 Steps: 

  • First off, insert the following formula in cell D4 (or as you wish. You have to change the cell reference according to that.) and press ENTER.

Apply a Formula with OFFSET, COLUMNS & ROWS Functions

  • Now, copy the formula downward using the Fill Handle icon till cell D10.

Drag Down

  • After that, copy the formula rightward to cell G10  in a similar way.

Drag Horizontally

  • After that, select a cell in the data and press CTRL+T. This will open a little window named Create Table?.
  • Then, mark the My Table has headers check box and press OK.

Make a Table

A pop-up window will open up. And a message like the following will be shown.

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

Formula in the Header Removed

And that’s all. Have a look at the following image for output!

Final Table

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

Read More: How to Create a Table with Merged Cells in Excel 

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

Assuming that the column contains lots of blanks and unwanted characters. To handle the hustle, we can use the powerful Power Query tool.

📌 Steps: 

  • First and foremost 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.
  • Now, press OK.

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

It will bring up the power query editor.

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

  • In the power query editor under the Add Column section, there is an Index Column option with a drop-down list. After clicking the drop-down menu select From 0. 
  • And repeat the process once again to create a third column similar to the previous second column.

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

  • We will divide the 3rd column by 4(as we are working with 4 columns).
  • So, for that reason, we have to go to: click Transform and then go ahead under Standard and use Modulo.

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

  • Enter the number of columns, in this case, ‘4’. And then hit OK.

Modulo Pop UP

  • We are going to add another column so in the Add Column there is a Custom Column and so in this Custom Columns pop up we are going to add some formulas.

Add Custom Column

  • It’s going to be an if-then statement:
if[Index.1]=0 then "First Name" else if [Index.1]=1 then "Last Name" else if [Index.1]=2 then "ID" else "Marks(%)"
  • Now hit OK.

If-then Statement

  • So like in the following image we are going to duplicate the second column(‘1’ in the image) to get the fifth column(‘4’ in the image). Under the Add Column, select Duplicate Column like 3 in the image.

Duplicate Column

  • Now we will divide the 5th column by column number 4. So we have to Add a Column and then from the Standard button to Divide(integer).

Divide by Integer

  • This window will pop up. We will enter 4 for the column number. And hit OK.

Divide by 4

  • 2nd, 3rd, and 5th columns will be removed as they are just for calculations.

Remove Column

  • Now we will remove the upper 4 columns like in the image as they have been already created. In the Home ribbon, we will have Remove Rows and click Remove Top Rows.

Remove Top Rows

  • We are going to get removed the top 4 rows by entering 4 in the pop-up Remove Top Rows box and just hit OK.

4 Rows Removing

  • Now, select the middle column and go to Transform, and after that Pivot Column.

How to create a table in Excel with multiple columns

  •  After selecting the Pivot column the below image will appear. In the Advance Options select Don’t Aggregate. Now click OK.

Don't Aggregate

  • There is a redundant column marked in the image actually we are going to remove the first column just by clicking the right mouse button on the first column and hitting Remove.

How to create a table in Excel with multiple columns

  • Then the expected table shown below will appear.

How to create a table in Excel with multiple columns

  • Now we load the table by hitting Close & Load under the Home tab.

Loading Table in Worksheet

  • This table will be loaded into the worksheet.

Final Table

Read More: Create Table in Excel Using Shortcut

Download Practice Workbook

You can download the practice workbook from the following download button.

Concluding Words

So we have discussed how to create a table in Excel with multiple columns. However, if you have any queries regarding this, please ask us in the comment box. Any feedback is welcome.

Related Articles

<< Go Back to Make a Table | Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF