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!
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.
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.
=OFFSET($B$4,COLUMNS($D$4:D4)-1+(ROWS($4:4)-1)*4,0)
- Now, copy the formula downward using the Fill Handle icon till cell D10.
- After that, copy the formula rightward to cell G10 Â in a similar way.
- 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.
A pop-up window will open up. And a message like the following will be shown.
- Just hit the Yes button.
And that’s all. Have a look at the following image for 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.
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.
It will bring up the power query editor.
- 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.
- 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.
- Enter the number of columns, in this case, ‘4’. And then hit OK.
- 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.
- 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.
- 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.
- 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).
- This window will pop up. We will enter 4 for the column number. And hit OK.
- 2nd, 3rd, and 5th columns will be removed as they are just for calculations.
- 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.
- 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.
- Now, select the middle column and go to Transform, and after that Pivot Column.
-  After selecting the Pivot column the below image will appear. In the Advance Options select Don’t Aggregate. Now click OK.
- 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.
- Then the expected table shown below will appear.
- Now we load the table by hitting Close & Load under the Home tab.
- This table will be loaded into the worksheet.
Read More:Â Create Table in Excel Using Shortcut
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.
