Note: this sheet is available in the Startup File in our store.
Purpose
Most attempts I have witnessed to create an outline in Excel have ended badly. It is clumsy to promote, demote and rearrange a hierarchical list of items, and to keep them enumerated logically.
I built a tool years ago, one of my early VBA projects, that creates row groups based on a column of level numbers. Groups are good for collapsing and expanding levels, but they’re designed for data summarization, not outlining. To get users to adjust, or to protect them from needing to, is probably more effort than the benefit you or they get back.
Word is a far superior tool for outlining. It is simple and intuitive. It auto-increments with Enter and auto-indents with Tab. You can cut and paste and it auto-adjusts. You can define different formats for each level and can easily switch and combine letters, numbers, roman numerals and bullets.
I could stop this effort before it starts by declaring Word as the go-to outline tool. Let’s start by clarifying the purpose. If you intend to write paragraphs, the first question is why? – it’s an outline! – but if so, Word is the obvious choice. If you just need a quick ordered list of short phrases as a guide for some project, you might choose Excel. This enables you to combine it with other sheets and calculations, or to program an interactive interface.
You might end up with an unintentional outline, as you start listing items and realize later that some are subordinate, and that the list needs to be ordered.
This project will serve to demonstrate some formula and table techniques, and a little cool math. I’ll use a table to auto-generate outline numbers, with whole numbers at the top level, and each lower level adding a decimal place.
The Math
First, create or imagine a single-level numbering table. Two columns, one auto-generates ordinal whole numbers, the other is for whatever text you want to list. With relative name Above defined, the numbering formula is Item = N(Above)+1.
Whatever you do to this table – add rows at the bottom, insert rows at the top, drag rows up and down – they will always stay in numerical order. You wouldn’t want that with data, but we’re not talking about data, we’re just listing items. The list keeps itself straight, like a Word outline.
Now, to make this multi-level with decimals, I add another column for level. Level is a manual entry, starting with 1 at the top and increasing with each level. Like priority, level numbers go up as levels go down in significance.
Now, levels see-saw down and back up, and down again. So I have two requirements:
When the level number increases, add a decimal digit to the previous item number
When level number decreases, remove a decimal and increment the previous level item
Fun challenge! First point, increasing levels. Working inside out, I start again with N(Above). Instead of adding 1, I add 10^(1-level). Starting at level 1 it means adding 10^(1-1) = 10^0 = 1. Level 2 will add 10^(1-2) = 10^-1 = 0.1, and so on. This can be notated 10^(1-[@level]) or POWER(10,1-[@level]).
Second point, now I need to handle decreasing levels. To remove decimals is to round, and I want to round down. The number of digits I want to end up with is [@level]-1. This gives me 0 decimals for level 1, 1 decimal for level 2, etc.
=ROUNDDOWN(N(above)+ POWER(10,1-[@level]),[@level]-1)
I confess to a geek thrill upon discovering this formula. Later in testing, I modified the formula to protect against missing level:
=IF(N(Above),ROUNDDOWN(N(Above)+POWER(10,1-MAX([@level],1)),MAX([@level],1)-1),1)
I would still go to Word to write an outline. This one is limited to nine consecutive items at each level except the top, and is harder to format and harder to read than Word. But to play out the use case we’ve defined let’s embellish a bit, while illustrating some other points.
Slicers
If you have a long list of items in a table you can scroll it, or you can manually filter by one of the columns, but this is where it’s nice to slice. A table slicer is really nothing more than a shortcut to a column filter, with some key advantages. One advantage is that it presents the unique values in the column as buttons, which can be sized and arranged in a vertical, horizontal or rectangular configuration. Another advantage is that it can expose the values from hidden columns. This means that you can create and set up columns especially for this purpose.
As previously described, the disadvantage of Slicers is the space they consume. This is weighed against the advantages to provide utility without clutter. Layout can be a bit like making a jigsaw puzzle. Slicers are objects like shapes or pictures, and can easily be hidden in the Selection Pane or by Ctrl+6 while building. Once positioned they can be locked in place by selecting “Disable resizing and moving” in the Position and Layout format section.
I decide on two slicers. First I’ll slice by the top-level items. For this I’ll create a slicer column, first, to group the items by their parent level-1 title, and second, to sort the slicer buttons logically. The objective is to build the slicer button text from the whole-number item and its description. To do this I’ll round down the item number, convert it to two-digit text, and concatenate the description.
=TEXT(ROUNDDOWN([@item],0),"00")&" "& INDEX([description], MATCH(ROUNDDOWN([@item],0),[item],0))
Now that I have a group column to slice, I add the slicer. With the active cell in the table, I click slicer on the Table Design tab, and select the group column. I’ve left space to the left of the table for it, and I start stretching it out to fill the space.
The slicer need not be too tall, but as long as there’s space I’ll use it. In fact, the screen looks better with the slicer covering the visible screen height, about 30 rows. It hides the useless space beneath, like tucking in your shirt. In most cases this will also avoid the scroll bar that appears when the slicer fills up.
The second slicer is a small one for level. This will mostly be used to collapse the outline by selecting level 1, or levels 1 and 2, etc. It wouldn’t make sense to collapse to level 2 without level 1 – unless it’s used together with the item slicer.
Since level is a numeric column there’s no transformation needed, I add the slicer directly. This one will be horizontal at the top, and I give it ten columns. I’ll probably never use more than four levels, but the buttons are small and I have the space. I adjust the height together with the row height, just tall enough for the header and buttons with no cropping.
Slicer formatting can be customized. First see if the slicer style gallery has one you like. To change, right-click the style in the gallery and select Duplicate... This opens a window of options to format every element of your custom copy. Once saved, to make further changes, right-click it in the gallery and select Modify…
To complete the layout, I lock both slicers in place as described above, turn off the table filter, and hide the group slicer column.
Conditional Formatting
I’ll use Conditional Formatting here to bold level 1, and to indent level>1 with a dotted leader. I could create a Name Manager alias such as Level = Outline[@level], but in this case I’ll just use unstructured reference to [level] in column C. I select [description], then create four rules
When $C4=1 set bold font
When $C4=2 set number format " . "@
When $C4=3 set number format " . . "@
When $C4>3 set number format " . . . "@
Comments