Convert table into in single column in excel

In this topic I want to share a beautiful trick to convert a table data in to a single column. I have faced such problem while arranging the data in desire format.

I have taken example of 4 rows and 7 columns table with numerical values for better understanding. From the example you can easily understand that the desired result arranged by placing the values by going across column till the last values then go to starting value of next row.

There are many ways to achieving this. You can do it by copy-paste the value of one row at a time but it will take lot of time for larger table.We'll get the result by using 2 different method.

Method 1 : By Using of Index Function

In this method  it requires some preparations. But on the other hand, it’s one of the faster way. Let’s see what you need to prepare. Basically you have to create the column and row number in additional helper columns. 

As per above table (4 rows X 7 columns).

Row helper Column values are  each row number repeated by (number of column) times.

Column helper column values are repeated the data set (starting  with 1 to number of columns) by (number of row) times.

This is how created a helper column for the above example. In this way, you will also create a helper column according to your table.

Now using the Index function

=INDEX($A$2:$G$5,I2,J2)

The formula pulled the data by using the values of both helper columns. There are some "0"  in the results which have come due to the empty space of the table. You can delete the 0 by filtering.

You have seen how you can transform table data into a single column by row wise. Similarly you can transform by column wise.

For this, you have to do a little change in the helper columns. Below the screenshot of example of transform the data table to single column by column wise.

Method 1 : By Using of Pivot table

Here we will learn how to transform table data into single column by using pivot table.This is also a very easy method and it is beautiful trick for large tables.

For this also you have to do some basic preparation. As we know that the table heading for the pivot table should be maintained. Here is the screenshot of previous data table with heading.

In this trick, you have to follow some steps.

First you need to select the table by dragging technique or using shortcut key (Ctl+A).

Go to Insert menu and select PivotTable. and click on OK.

Here the screenshot of pivot Table will appear in another worksheet.

Now select all column (from C1 to C6) one by one and place in sorting manner Row Labels. 

Right now your pivot table data is in the form of our desired output, but it does not look like a proper output. Copy the pivot table data now and paste as "value" in another cell.

Now filter the cell written blank and delete them. Take your table data transferred to single column. 

In this trick, you have transferred your table data (row wise)  into a single column.

But transfer your data (column wise) , you have to transpose your table first and then follow all the same steps.

  

 

MID Function in Excel

The MID function returns the character from the middle of a text string where a staring position and length are mentioned. If you ever need to display some characters from a string, then the MID function is best for you. It is one of the popular functions of excel and widely used in a complex formula.

Syntax of the MID function is

MID(text,Start_num,num_chars)

Text: The text from where you want to extract characters.

Start_Num: It is also called the starting position. For example, If you want to extract "soft" from the string "Microsoft", here start_num will be 6. #VALUE error will come if this argument is zero.

Num_chars: This argument shows how long our result will be. In the above example, the num_chars is 4.

In the above example, you easily find that if the start_num and num_chars are zero the MID function gives a blank result.

Char Function in Excel

The Char function returns the character specified by the code number from the character set for your computer. The character set varies from the operating system to the operating system. The main purpose of char function is that get a character from the number.


Syntax of the Char function is

=CHAR(number)

In the example given above, you can see that every number (A number range from 1 to 255 )has been converted into a character with the help of char function. When we want to convert a number more than 255 its gives an #VALUE error.

Numbervalue function in Excel

Numbervalue is a function that converts a string into a number in a Locale-independent way.


Syntax of the Numbervalue function is

=Numbervalue(Text,[decimal_separator],[group_separator])

Arguments

Text: The string that you want to convert.

Decimal_Separator: It is optional. The character that separates the integer and decimal part from the number.

Group_Separator: It is optional. The character that separates the group from the number.

            

Value Function in Excel

The value function is a function that converts a text that represents a number to number. The value function will change the same text that has a numeric value in the cell.


Syntax of the Value Function

=VALUE(Text)

Here text arguments must be Number, Date, and Time format.

When you try to convert a non-numeric text it gives a #VALUE error.

Text Function in Excel

The Excel TEXT function is a function that converts a value to text in a specified format.

Syntax of the Text function is

=TEXT(value,format_text)

A number format is a special code to display the value in the desired format. It changes only its display without changing the original data. This is very useful for automating formatting.

There are many Build-in formats present in excel. I have used some build-in formats, you can use any of the formats.

Fixed Function in Excel

Fixed Function in excel is a function that rounds a number to the specified numbers of decimals and returns as text with or without commas.

Syntax of the Fixed function is

=FIXED(Number,[decimals],[no_commas])

Arguments

Number: The number you want to round, you can provide the number directly or reference of number.

Decimals: It is optional, After decimal how many digits you want to round the number, you can mentions here. By default, it is 2.

No_commas: True for preventing commas and False for not preventing commas. By default, it gives the result with commas.