Outlines

Is it possible to create an outline without subtotals that is 4 levels deep?
e.g. given a spreadsheet with 4 columns, Country, State, City, Zip - can I
create an outline that at the highest level will show countries only, then
expand to states, etc.? I have no data to sub-total.

If I understand what you're asking, you'll have to insert at least one blank
column between each of your data columns.
XL will not permit 2 adjoining columns to become 2 separate groups.

After inserting the blank columns, simply select any *one* of your data
columns by clicking on the letter in the header row, and then:
<Shift> <Alt> <RightArrow>.
Do this to each of the other columns, making sure *not* to include the blank
columns in any of the seletions.
Do nothing with "Country" column.

To "Ungroup", select the entire column and:
<Shift> <Alt> <LeftArrow>.

NOW, is this the effect you're looking for?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Jim Pockmire" <[email protected]> wrote in message
news:%[email protected]...
Is it possible to create an outline without subtotals that is 4 levels deep?
e.g. given a spreadsheet with 4 columns, Country, State, City, Zip - can I
create an outline that at the highest level will show countries only, then
expand to states, etc.? I have no data to sub-total.

This appears to group by columns, not rows - so I am not sure if I explained
this incorrectly or am not following the directions correctly.

In the example below, I would like countries listed, then expanding the rows
for a country would show states. Expanding a state would show it's cities
and so on.


"RagDyer" <[email protected]> wrote in message
news:ODPdESB%[email protected]...
> If I understand what you're asking, you'll have to insert at least one
> blank
> column between each of your data columns.
> XL will not permit 2 adjoining columns to become 2 separate groups.
>
> After inserting the blank columns, simply select any *one* of your data
> columns by clicking on the letter in the header row, and then:
> <Shift> <Alt> <RightArrow>.
> Do this to each of the other columns, making sure *not* to include the
> blank
> columns in any of the seletions.
> Do nothing with "Country" column.
>
> To "Ungroup", select the entire column and:
> <Shift> <Alt> <LeftArrow>.
>
> NOW, is this the effect you're looking for?
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "Jim Pockmire" <[email protected]> wrote in message
> news:%[email protected]...
> Is it possible to create an outline without subtotals that is 4 levels
> deep?
> e.g. given a spreadsheet with 4 columns, Country, State, City, Zip - can I
> create an outline that at the highest level will show countries only, then
> expand to states, etc.? I have no data to sub-total.
>
>

You can use the Subtotal feature, even if you don't have data to subtotal --

Select a cell in the table
Choose Data>Sort
Sort the table by Zip
Choose Data>Sort
Sort the table by Country, State, and City

Choose Data>Subtotals
At each change in: Country
Use function: Count
Add subtotal to: City
Remove check mark from 'Replace current subtotals'
Click OK

Repeat the Subtotal steps for State and City

To hide the Subtotals:
Click the Select All button (to the left of the Column A button)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =ISNUMBER($C1)
(where City is in column C)
Click the Format button
On the Font tab, choose the white colour (or a colour to match the cell
background colour)
Click OK, click OK

Jim Pockmire wrote:
> This appears to group by columns, not rows - so I am not sure if I explained
> this incorrectly or am not following the directions correctly.
>
> In the example below, I would like countries listed, then expanding the rows
> for a country would show states. Expanding a state would show it's cities
> and so on.
>
>
> "RagDyer" <[email protected]> wrote in message
> news:ODPdESB%[email protected]...
>
>>If I understand what you're asking, you'll have to insert at least one
>>blank
>>column between each of your data columns.
>>XL will not permit 2 adjoining columns to become 2 separate groups.
>>
>>After inserting the blank columns, simply select any *one* of your data
>>columns by clicking on the letter in the header row, and then:
>><Shift> <Alt> <RightArrow>.
>>Do this to each of the other columns, making sure *not* to include the
>>blank
>>columns in any of the seletions.
>>Do nothing with "Country" column.
>>
>>To "Ungroup", select the entire column and:
>><Shift> <Alt> <LeftArrow>.
>>
>>NOW, is this the effect you're looking for?
>>--
>>
>>HTH,
>>
>>RD
>>==============================================
>>Please keep all correspondence within the Group, so all may benefit!
>>==============================================
>>
>>"Jim Pockmire" <[email protected]> wrote in message
>>news:%[email protected]...
>>Is it possible to create an outline without subtotals that is 4 levels
>>deep?
>>e.g. given a spreadsheet with 4 columns, Country, State, City, Zip - can I
>>create an outline that at the highest level will show countries only, then
>>expand to states, etc.? I have no data to sub-total.
>>
>>
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html