Arrange columns by alphabetical column names

[jewelsharma]

Is there a quick way to re-arrange the data (in a Table, as in defined Excel Table) so all column names/headers appear alphabetically?
(in my case, the data has as many as 50 columns, so it is kinda time-consuming to drag and drop them around to get them in that order).

in the attached sample, though the column headers are numbers, but should suffice the purpose.

[sandy666]

Do you mean something like this?

[jewelsharma]

Yes, that's what I'm after. What's a quick/easy way to achieve this.

[sandy666]

You have Ex2016 so you have PowerQuery also

• Go to Data - Show queries
• On the right you'll see Query window and table there
• Right click on this table and select Edit
• in new PowerQuery window on the right side you will see steps how it was done

or another way

• select your source table (ctrl+a)
• copy
• create new sheet (will be deleted later)
• in A1 right click and paste as values
• don't remove selection
• copy pasted values
• go to the blank cell and right click then paste TRANSPOSE
• don't remove selection
• then DATA - Sort (sort by column A - smallest to largest)
• copy this range
• then again paste TRANSPOSE somewhere in sheet 2
• copy this range , go to your table
• paste as values (it will change table to range)
• recreate table again if you wish
• delete sheet 2

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

[samba_ravi]

Other way
select entire data > Disign (from main tab)
under design > Convert Range
then sort data -- Data > Sort > Options > Sort Left to Right > Sort by Row1 > ok > sort anything that look like a number, as number > ok

Then Convert data range to table -- Insert > Table

[AliGW]

That's a 'hidden' gem, Siva!

[jewelsharma]

Yes, I was doing like that for non-table ranges. But for defined Tables the option to Sort Rows Left > Right is greyed out. Was wondering there's a direct way to work on the table itself, w/o converting back to range.

Notwithstanding, thanks for your answer. I'm sure it benefits the community.

[jewelsharma]

Like the use of PowerQuery to achieve this. Nice work!

[sandy666]

Tables work with columns and content so you can't transpose Excel Table. That is why MS created PowerQuery . This is ETL tool - Extract - Transform - Load
(but you can;t do that in-place)

Welcome and Thanks

[AliGW]

Yes, PowerQuery is a fantastic tool - take the time to get to know it a bit, and you won't regret it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[josephteh]

First convert table to range: Table Tools > Design > Convert to Range

Then sort: Data > Sort > Options > Orientation > Sort left to right > Sort by [select Row 1] > OK