# Column to Diagonal

#### Nightboy

##### New Member
Hi, I have data in a column, say 100 cells & I want to display it in the same order but in diagonal format, running down left to right. I have no idea how to do this.
thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
OK, Suppose your column of data is housed in cells A1:A10 (I've used 10 data points for my quick example).

In cell B2 put in this formula:

=IF(ROW(B1) = COLUMN(B1), \$A1, "")

now copy this formula over to column J and down to row 10. You shoulw get the values in diagonal.

Now:

1. Select all of the values and do a Copy|Paste Special|Values

2. delete all of the values in column A except for the first value in A1.

That should do it.

HTH

EDIT:: Nightboy, yeah, I meant to say "B1", not "B2"
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-09-15 19:19
This message was edited by Mark O'Brien on 2002-09-16 07:57

Here's one way :-

Dim rng As Range, rng2 As Range, x%
Set rng = [A1:A100]
For x = 2 To rng.Rows.Count
If rng2 Is Nothing Then
Set rng2 = rng(x)
Else
Set rng2 = Union(rng(x).Resize(1, x - 1), rng2)
End If
Next
rng2.Insert Shift:=xlToRight

Thanks Mark, that works although I found I had to put the formula in B1 rather than B2. Also to Jack, I think that your answer might involve VBA (?) which I don't know how to use. I will stick with the normal Excel commands.

Replies
3
Views
267
Replies
5
Views
346
Replies
4
Views
581
Replies
9
Views
720
Replies
0
Views
378

1,217,257
Messages
6,135,502
Members
449,944
Latest member
parag385

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back