How to flip subtotal

[excelsishya]

Hi all,

Is there way to flip subtotal which is at top to bottom.The subtotal may have sum or subtotal formula.

Here is sample layout

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 225px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>
</td> <td style="text-align: right;">2009</td> <td style="text-align: right;">2010</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="font-weight: bold;">xxx</td> <td style="font-weight: bold; text-align: right;">15</td> <td style="font-weight: bold; text-align: right;">15</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>a1</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>a2</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>a3</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold;">yyy</td> <td style="font-weight: bold; text-align: right;">7</td> <td style="font-weight: bold; text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>a1</td> <td style="text-align: right;">7</td> <td style="text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold;">zzz</td> <td style="font-weight: bold; text-align: right;">12</td> <td style="font-weight: bold; text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>a1</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>a2</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>a3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>a4</td> <td>
</td> <td>
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B3</td> <td>=SUBTOTAL(9,B4:B6)</td></tr> <tr> <td>C3</td> <td>=SUBTOTAL(9,C4:C6)</td></tr> <tr> <td>B7</td> <td>=SUBTOTAL(9,B8)</td></tr> <tr> <td>C7</td> <td>=SUBTOTAL(9,C8)</td></tr> <tr> <td>B9</td> <td>=SUBTOTAL(9,B10:B13)</td></tr> <tr> <td>C9</td> <td>=SUBTOTAL(9,C10:C13)</td></tr></tbody></table></td></tr></tbody></table>

Desired Output

Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 35px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>
</td> <td style="text-align: right;">2009</td> <td style="text-align: right;">2010</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>a1</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>a2</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>a3</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">xxx</td> <td style="font-weight: bold; text-align: right;">15</td> <td style="font-weight: bold; text-align: right;">15</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>a1</td> <td style="text-align: right;">7</td> <td style="text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold;">yyy</td> <td style="font-weight: bold; text-align: right;">7</td> <td style="font-weight: bold; text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>a1</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>a2</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>a3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold;">zzz</td> <td style="font-weight: bold; text-align: right;">12</td> <td style="font-weight: bold; text-align: right;">12</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B6</td> <td>=SUBTOTAL(9,B3:B5)</td></tr> <tr> <td>C6</td> <td>=SUBTOTAL(9,C3:C5)</td></tr> <tr> <td>B8</td> <td>=SUBTOTAL(9,B7)</td></tr> <tr> <td>C8</td> <td>=SUBTOTAL(9,C7)</td></tr> <tr> <td>B12</td> <td>=SUBTOTAL(9,B9:B11)</td></tr> <tr> <td>C12</td> <td>=SUBTOTAL(9,C9:C11)</td></tr></tbody></table></td></tr></tbody></table>I am using excel 2007.

Thanks

 

[jim may]

Can you access the Subtotals Dialog box? If so look for the Summary Below data option.

 

[Just Jan]

The easiest way I can think of is select the row containing the subtotals, then hold SHIFT drag the row down under the data. Repeat for each relevant row.

 

[excelsishya]

Jim and just thanks for suggestions.

Jim these are subtotal given by formula not created through subtotal function.

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>
</td> <td style="text-align: right;">2009</td> <td style="text-align: right;">2010</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>sub</td> <td style="text-align: right;">12</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>a1</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>a2</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>a3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>sub</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>a1</td> <td style="text-align: right;">5</td> <td style="text-align: right;">5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>sub</td> <td style="text-align: right;">24</td> <td style="text-align: right;">24</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>a1</td> <td style="text-align: right;">6</td> <td style="text-align: right;">6</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>a2</td> <td style="text-align: right;">6</td> <td style="text-align: right;">6</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>a3</td> <td style="text-align: right;">6</td> <td style="text-align: right;">6</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>a4</td> <td style="text-align: right;">6</td> <td style="text-align: right;">6</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B3</td> <td>=SUBTOTAL(9,B4:B6)</td></tr> <tr> <td>C3</td> <td>=SUBTOTAL(9,C4:C6)</td></tr> <tr> <td>B7</td> <td>=SUBTOTAL(9,B8)</td></tr> <tr> <td>C7</td> <td>=SUBTOTAL(9,C8)</td></tr> <tr> <td>B9</td> <td>=SUBTOTAL(9,B10:B13)</td></tr> <tr> <td>C9</td> <td>=SUBTOTAL(9,C10:C13)</td></tr></tbody></table></td></tr></tbody></table>

i tried your suggested method , i can access subtotal dialog box but i have to replace current subtotal then sort and then recreate subtotal .

Isn't there way to just flip existing subtotal.

Just,based on your suggested method i think we have to select 1 subtotal at time then drag.

Currently i using cut and insert cut method.Since data set is not big i can do it manually . however i am interested in knowing is their better method.