# Reverse pivot tables

#### thomasbeard

##### Board Regular
Is there any way of reversing a pivot table without the orignal data source? i.e. convert a table that has several columns for a particular field into a worksheet that replicates this field in multiple rows and enters the data into single column? Thanks in advance. Tom

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi Tom

In this example I defined 3 names so that the formulae are easier to read:

Code:
``````PTCols	=Sheet3!\$C\$2:\$F\$2
PTRows	=Sheet3!\$B\$3:\$B\$7
PTVals	=Sheet3!\$C\$3:\$F\$7``````

The formulae for the PVTable reversing, starting in B10
Code:
``````B10 =INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))

C10 =INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))

D10 =INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))``````
Copy Down.

I hope that this example will get you started.
PGC

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=8><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=8><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=8><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols))) selected>B10<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C10<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D10<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B11<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C11<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D11<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B12<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C12<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D12<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B13<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C13<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D13<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B14<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C14<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D14<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B15<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C15<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D15<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B16<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C16<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D16<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B17<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C17<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D17<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B18<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C18<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D18<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B19<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C19<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D19<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B20<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C20<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D20<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B21<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C21<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D21<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B22<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C22<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D22<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B23<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C23<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D23<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B24<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C24<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D24<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B25<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C25<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D25<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B26<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C26<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D26<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B27<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C27<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D27<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B28<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C28<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D28<OPTION value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)))>B29<OPTION value==INDEX(PTCols,1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>C29<OPTION value==INDEX(PTVals,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols)),1+MOD(ROW()-ROW(\$B\$10),COLUMNS(PTCols)))>D29</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==INDEX(PTRows,1+INT((ROW()-ROW(\$B\$10))/COLUMNS(PTCols))) name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">

Replies
1
Views
230
Replies
3
Views
382
Replies
2
Views
236
Replies
1
Views
88
Replies
5
Views
315

1,219,807
Messages
6,150,348
Members
450,952
Latest member
Zung

### 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