I'm currently working on a table which has 900+ columns and 20 rows. For filtring and query search purpose, I want to convert it into a vertical table with the help of cell reference or pivot or any other live data conversion techniques. How can that be achieved?

3 Answers

You can use TRANSPOSE function, function argument should be entry table selected.

to make function work you need to CTRL+SHIFT+ENTER that is insert it as array formula over selected range of cels (900 rows and 20 columns)

HOW TO: select 900 rows and 20 columns, press F2 type =TRANSPOSE(select table here) and press CTRL+SHIFT+ENTER.

Reference

1

Select the table, hit Ctrl+C, select a cell below row 21, hit Alt => E => S => V => => E which will transpose your table into 900 rows by 20 columns.

This is an old Excel 2003 shortcut that still works.

If you want the Alt keystrokes for later versions it would be Alt => H => V => S => V => E

2

1. Select the data in the table

2. Copy

3. Go to the cell down the given table

4 Select Paste-Transpose from the Menu

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy