Trouble summarizing data

L

Linda4me

New Member
Joined
May 22, 2011
Messages
31
Hello,
I am using Excel 2007 and have the following data pulled from a query.

<table width="512" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:21.0pt" height="28"> <td style="height:21.0pt;width:48pt" valign="top" width="64" align="left" height="28">
clip_image002.gif
<table cellpadding="0" cellspacing="0"> <tbody><tr> <td class="xl65" style="height:21.0pt;width:48pt" width="64" height="28">Year - Long Name</td> </tr> </tbody></table> </td> <td class="xl65" style="border-left:none;width:48pt" width="64">Month - Long Name</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Segment</td> <td class="xl65" style="border-left:none;width:48pt" width="64">HDW</td> <td class="xl65" style="border-left:none;width:48pt" width="64">SFW</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Services</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Credit</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Other</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-left:none;width:48pt" width="64">February</td> <td class="xl66" style="border-left:none;width:48pt" width="64">US</td> <td class="xl67" style="border-left:none;width:48pt" width="64">426,793</td> <td class="xl68" style="border-left:none;width:48pt" width="64">9.00</td> <td class="xl68" style="border-left:none;width:48pt" width="64">8.84</td> <td class="xl68" style="border-left:none;width:48pt" width="64">9.14</td> <td class="xl68" style="border-left:none;width:48pt" width="64">8.54</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">February</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">Americas</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">369,202</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.01</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.85</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.16</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.58</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">February</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">EMEA</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">57,591</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.93</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.72</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.04</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.31</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">March</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">US</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">539,052</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.01</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.85</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.16</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.55</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">March</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">Americas</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">473,992</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.02</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.87</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.17</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.58</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">March</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">EMEA</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">65,060</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.94</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.73</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.06</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.34</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">April</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">US</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">755,912</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.99</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.82</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.12</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.51</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">April</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">Americas</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">680,008</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.99</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.83</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.13</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.53</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none; width:48pt" width="64" height="20">2010</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">April</td> <td class="xl66" style="border-top:none;border-left:none;width:48pt" width="64">EMEA</td> <td class="xl67" style="border-top:none;border-left:none;width:48pt" width="64">75,904</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.95</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.73</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">9.03</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">8.33</td> </tr> </tbody></table>
I would like the data to look like this to allow filtering. Is this possible in a pivot table? Thanks in advance!
<table width="448" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="7" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64" align="right">2010</td> <td style="width:48pt" width="64" align="right">2010</td> <td style="width:48pt" width="64" align="right">2010</td> <td style="width:48pt" width="64" align="right">2010</td> <td style="width:48pt" width="64" align="right">2010</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Segment</td> <td>Metric</td> <td>Feb</td> <td>Mar</td> <td>Apr</td> <td>May</td> <td>Jun</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">US</td> <td class="xl65" style="width:48pt" width="64">HDW</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">US</td> <td class="xl65" style="border-top:none;width:48pt" width="64">SFW</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">US</td> <td class="xl65" style="border-top:none;width:48pt" width="64">Services</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">US</td> <td class="xl65" style="border-top:none;width:48pt" width="64">Credit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">US</td> <td class="xl65" style="border-top:none;width:48pt" width="64">Other</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Americas</td> <td class="xl65" style="border-top:none;width:48pt" width="64">HDW</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Americas</td> <td class="xl65" style="border-top:none;width:48pt" width="64">SFW</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Americas</td> <td class="xl65" style="border-top:none;width:48pt" width="64">Services</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Linda4me,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:
 
Upvote 0
Linda4me,

The layout below is what you want?

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2010</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>April</TD><TD>February</TD><TD>March</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Americas</TD><TD>Soma de HDW</TD><TD style="TEXT-ALIGN: right">680.008,00 </TD><TD style="TEXT-ALIGN: right">369.202,00 </TD><TD style="TEXT-ALIGN: right">473.992,00 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de SFW</TD><TD style="TEXT-ALIGN: right">8,99 </TD><TD style="TEXT-ALIGN: right">9,01 </TD><TD style="TEXT-ALIGN: right">9,02 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Services</TD><TD style="TEXT-ALIGN: right">8,83 </TD><TD style="TEXT-ALIGN: right">8,85 </TD><TD style="TEXT-ALIGN: right">8,87 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Credit</TD><TD style="TEXT-ALIGN: right">9,13 </TD><TD style="TEXT-ALIGN: right">9,16 </TD><TD style="TEXT-ALIGN: right">9,17 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Others</TD><TD style="TEXT-ALIGN: right">8,53 </TD><TD style="TEXT-ALIGN: right">8,58 </TD><TD style="TEXT-ALIGN: right">8,58 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>EMEA</TD><TD>Soma de HDW</TD><TD style="TEXT-ALIGN: right">75.904,00 </TD><TD style="TEXT-ALIGN: right">57.591,00 </TD><TD style="TEXT-ALIGN: right">65.060,00 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de SFW</TD><TD style="TEXT-ALIGN: right">8,95 </TD><TD style="TEXT-ALIGN: right">8,93 </TD><TD style="TEXT-ALIGN: right">8,94 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Services</TD><TD style="TEXT-ALIGN: right">8,73 </TD><TD style="TEXT-ALIGN: right">8,72 </TD><TD style="TEXT-ALIGN: right">8,73 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Credit</TD><TD style="TEXT-ALIGN: right">9,03 </TD><TD style="TEXT-ALIGN: right">9,04 </TD><TD style="TEXT-ALIGN: right">9,06 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Others</TD><TD style="TEXT-ALIGN: right">8,33 </TD><TD style="TEXT-ALIGN: right">8,31 </TD><TD style="TEXT-ALIGN: right">8,34 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>US</TD><TD>Soma de HDW</TD><TD style="TEXT-ALIGN: right">755.912,00 </TD><TD style="TEXT-ALIGN: right">426.793,00 </TD><TD style="TEXT-ALIGN: right">539.052,00 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de SFW</TD><TD style="TEXT-ALIGN: right">8,99 </TD><TD style="TEXT-ALIGN: right">9,00 </TD><TD style="TEXT-ALIGN: right">9,01 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Services</TD><TD style="TEXT-ALIGN: right">8,82 </TD><TD style="TEXT-ALIGN: right">8,84 </TD><TD style="TEXT-ALIGN: right">8,85 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Credit</TD><TD style="TEXT-ALIGN: right">9,12 </TD><TD style="TEXT-ALIGN: right">9,14 </TD><TD style="TEXT-ALIGN: right">9,16 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right"></TD><TD>Soma de Others</TD><TD style="TEXT-ALIGN: right">8,51 </TD><TD style="TEXT-ALIGN: right">8,54 </TD><TD style="TEXT-ALIGN: right">8,55 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0
Linda4me said:
Hello Hiker95, It is Excel 2007.

Mark, Yes! That is the format. Any ideas?
Click to expand...

Linda4me,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Lets go (I'll use my example to facilitate):
<o:p></o:p>
<o:p></o:p>

<o:p><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Year</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Month</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Segment</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">HDW</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">SFW</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Services</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Credit</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Others</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">February</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">US</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">426.793 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,84</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,54</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">February</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Americas</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">369.202 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,01</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,85</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,16</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,58</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">February</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EMEA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">57.591 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,93</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,72</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,04</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,31</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">March</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">US</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">539.052 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,01</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,85</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,16</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,55</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">March</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Americas</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">473.992 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,02</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,87</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,58</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">March</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EMEA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">65.060 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,94</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,73</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,06</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,34</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">April</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">US</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">755.912 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,99</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,82</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,51</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">April</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Americas</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">680.008 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,99</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,83</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,13</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,53</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">April</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EMEA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">75.904 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,95</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,73</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9,03</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8,33</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD><TD>***********</TD></TR></TBODY></TABLE>

</o:p>1) With the selected source list (A1:H10), activate the Insert tab and choose Pivottable in group Tables.
<o:p></o:p>
<o:p></o:p>

2) In the Create table dialog box, do the following:
<o:p></o:p>
<o:p></o:p>

-make sure the range in the Table/range is the range selected initially (Sheet1!$A$1:$H$10)<o:p></o:p>
<o:p></o:p>

-in the section Choose where you want the PivotTable to be placed choose New worksheet. <o:p></o:p>
<o:p></o:p>

-Finally, click OK. <o:p></o:p>
<o:p></o:p>

3) Now (in the new worksheet created), in the PivotTable field list panel (in the upper section), drag the field Segment for the Row Label area (at the bottom of the Panel).
<o:p></o:p>
<o:p></o:p>

4) Now, in the PivotTable field list panel (in the upper section) again, drag the field HDW for the Values area (at the bottom of the Panel). Repeat the previous operation to the SFW, Services, Credit and Others fields.
<o:p></o:p>
<o:p></o:p>

5) Now, in the PivotTable field list panel (at the bottom of the Panel), drag the virtual field Values (in the Values area) for the Column Label area (at the bottom of the Panel).
<o:p></o:p>
<o:p></o:p>

6) Now, in the PivotTable field list panel (in the upper section) again, drag the field Year for the Column Label area (at the bottom of the Panel). Repeat the previous operation to the Month field.
<o:p></o:p>

7) Then, with any PivotTable part selected, activate the Options tab and choose Options in the PivotTable group. In the PivotTable Options dialog box, activate the tab Totals and Filters and disable the options to Show grand totals for rows and Show grand totals for columns.

8) Now, turn on the View tab (still on the PivotTable Options dialog box) and disable the options Show expand/collapse buttons and Display field captions and filter drop downs. In addition, check the PivotTable Classic Layout and then click OK.

9) Finally, in the PivotTable, select the cell with the value 2010, on the Options tab, click Field settings in the active field. In the Field settings dialog box, under Subtotals, select None and then click OK.
<o:p></o:p>

Markmzz
 
Upvote 0
Hi Mark,
Thank you so much for responding. Quick question, for step #5, where should I drag the virtual field 'Values'?

5) Now, in the PivotTable field list panel (at the bottom of the Panel), drag the virtual field Values (in the Values area) for the Column Label area (at the bottom of the Panel).
 
Upvote 0
Linda4me said:
Hi Mark,
Thank you so much for responding. Quick question, for step #5, where should I drag the virtual field 'Values'?

5) Now, in the PivotTable field list panel (at the bottom of the Panel), drag the virtual field Values (in the Values area) for the Column Label area (at the bottom of the Panel).
Click to expand...

Linda4me,

Sorry, my mistake (Column Label area to Row Label area).

Now, all step 5) correct.

5) Now, in the PivotTable field list panel (at the bottom of the Panel), drag the virtual field Values (in the Column Label area) to the Row Label area.

Markmzz
 
Last edited:
Upvote 0
Linda4me,

One more fix (in the step 9).

9) Finally, in the PivotTable, select the cell with the value 2010, on the Options tab, click Field settings in the Active Field group. In the Field settings dialog box, under Subtotals, select None and then click OK.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top