Outlines

  • Thread starter Thread starter Jim Pockmire
  • Start date Start date
J

Jim Pockmire

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!
==============================================

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