Insert section headings into Excel

[votrechien]

Hi All,

We have a spreadsheet of all our products in on our website in an excel document (about 500 items). We have our categories separated with a blank cell filled with a gray background color. See the following spreadsheet:

http://anchoring.com/images/excel-ss.gif

This works fine but it can create problems if we're referencing these cells in another worksheet (especially if we don't want these headings in the other worksheet).

Is there a way to create headings like this which don't actually utilize a cell, in essence they are used only for visual purposes?

I hope this makes sense. Thanks in advance.

 

[venkat1926]

off the cuff suggestion

you introduce section names in the blank rows and hide those rows.

 

[Ruddles]

Keep your data in columns B onwards and place the section names in column A. I can't easily demonstrate using your data as you posted an image of a worksheet which I can't manipulate, but something like this:-

<TABLE style="WIDTH: 151pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=201 border=0><COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 28pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=37 height=17></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=89>

A​

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=75>

B​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

1​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section Name</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

2​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section A</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

3​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section A</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

4​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section A</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

5​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section A</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

6​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section B</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

7​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section B</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

8​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section B</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

9​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section B</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

10​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section B</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

11​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

12​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

13​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

14​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

15​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

16​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

17​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Section C</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item 16</TD></TR></TBODY></TABLE>

 

[votrechien]

Thanks for your guys responses.

Keep your data in columns B onwards and place the section names in column A. I can't easily demonstrate using your data as you posted an image of a worksheet which I can't manipulate, but something like this:-

<table style="width: 151pt; border-collapse: collapse;" width="201" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 28pt;" width="37"><col style="width: 67pt;" width="89"><col style="width: 56pt;" width="75"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl63" style="border: medium none rgb(212, 208, 200); width: 28pt; height: 12.75pt; background-color: transparent;" width="37" height="17">
</td><td class="xl65" style="border: 0.5pt solid windowtext; width: 67pt; background-color: rgb(219, 229, 241);" width="89">

A​

</td><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 56pt; background-color: rgb(219, 229, 241);" width="75">

B​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border: 0.5pt solid windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

1​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section Name</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item Name</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

2​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section A</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 1</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

3​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section A</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 2</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

4​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section A</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 3</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

5​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section A</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 4</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

6​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section B</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 5</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

7​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section B</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 6</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

8​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section B</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 7</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

9​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section B</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 8</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

10​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section B</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 9</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

11​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 10</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

12​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 11</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

13​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 12</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

14​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 13</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

15​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 14</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

16​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 15</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: rgb(219, 229, 241);" align="right" height="17">

17​

</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Section C</td><td class="xl64" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Item 16</td></tr></tbody></table>

The only problem with this is that if we have 300 columns of data, then the worksheet extends 300 columns vertically which is fairly cumbersome.

 

[Ruddles]

You only showed one column of data and my suggestion only adds one more column. Do you really have 300 columns?

I evidently don't understand what you mean.

It's normally helpful to normalise the data so that each record stands on its own, i.e. it contains a record of the category it belongs to. This also means you don't need intrusive category headings and makes lookups much easier.

There's something else going on in the sample you posted - rows 34-40 are missing, for example. If you'd like to post a representative sample of your data, maybe we can understand what we're dealing with.

 

[votrechien]

Thank you again for all your help.

Here's a very rough example of how our worksheet is organized http://anchoring.com/example.xlsx

Basically we have one main worksheet with every conceivable piece of data regarding our products. There's about 30 different fields of data (price, sku, description, etc) and about 300 different products.

We have a few other worksheets we use for things like Wholesale Price Lists. We print these price lists out all the time, so the main idea of separate work sheets is we can adjust things aesthetically (i.e. colours, column widths, etc).

We like to separate some of the product categories with grayed out rows just because it makes navigating the data a lot easier. The problem is we don't always want these headings to appear on the other worksheets.

Hopefully that clarifies things somewhat.

It's very well possible I'm doing things in a completely idiotic way or that we've outgrown our excel worksheet.

 

[Ruddles]

We like to separate some of the product categories with grayed out rows just because it makes navigating the data a lot easier. The problem is we don't always want these headings to appear on the other worksheets.

You could consider this: if you repeat the category on each item line, then you could use a filter to display just the category or categories you want rather than having to navigate around the sheet (in whatever way you do that).

If you don't want the category to appear on other worksheets, you just don't use column A (or column AM or wherever the category is held). Ditto for printing - you just exclude the category column from the print range.

The category is part of the item's data so I would say it should be on the same row as the rest of the item's data.