Tag Archives: Presenting

Eat Your Waffles

Ok, don’t eat the waffle chart

In a previous post, I discussed making a Button Bar Chart. That whole process really inspired me to think about simplified charts for presentations.

Which got me thinking about Waffle Charts.

4 category waffle chart
Note how the smallest group stands out

Waffle charts are excellent for looking at data sets where the smallest numbers are the important ones. You can use colour (as I have above) to make those numbers stand out.

But oddly, I don’t see people using a lot of waffle charts in their presentations. And there is no template for a waffle chart in Excel.

You can find some interesting ideas about building Excel waffle charts for dashboard purposes and I recommend this article to you: Interactive Waffle Charts in Excel

However, I was looking for something different. Something that wouldn’t have me counting and colouring cells manually (shudder).

Building the Waffle

I chose to build the waffle chart using a series of conditional formatting rules. The first step was creating the formula to count the cells of the waffle.

Waffle chart base formula
Counting the cells in a 100 grid waffle

In case the picture is a bit small, the formula used here is:
=(MOD(ROW()+8,10)*10)+(COLUMN()-2)+1

This uses the row and column position of the cell to count from 1 to 100 in a 10 by 10 grid.

I then built on that base formula with this monster formula:

=IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=’5 Category Waffle’!$A$2,’5 Category Waffle’!$A$2, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=(‘5 Category Waffle’!$A$2+’5 Category Waffle’!$A$3),’5 Category Waffle’!$A$3, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$4),’5 Category Waffle’!$A$4, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$5),’5 Category Waffle’!$A$5, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$6),’5 Category Waffle’!$A$6,0)))))

The sheet BTW is called 5 Category Waffle.

5 category formula
The 5 category waffle formula result

The formula checks the position number of the cell generated by the base formula and sees if it is less than or equal to the number of values in each category in column A. It then returns the value of the category in each cell.

Because I wanted to put symbols in the cell like these examples.

talking head icon used in waffle chart
Talking Heads waffle chart
bomb icon waffle chart
Bombs waffle chart

I took that monster formula and made it into a named formula.

 

 

This made building the conditional formatting rules much easier to do(simply because the conditional formatting dialog is so cramped).

Lastly, I built a series of conditional formatting rules to change the background colour of the cell based on the value returned by the formula. For the waffles using symbols, the rule formats the colour of the font, instead of the background.

A couple of additional pointers

  • To create a perfect grid, switch the view in Excel to Page Layout View. Page Layout View uses the same measurement scale for both row height and column width.  Set your measurements here.
  • For the symbol waffles, use the File> Options>Advanced> Display Options for this worksheet and turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.

 

PowerPoint: Like toppings on pizza

You may never have looked at Outline View in PowerPoint. But, if you have presentation that has text you should check it out. Working in Outline View is not only the fastest way to build the  outline, it creates a more robust and easily edited presentation as well.

By default, when you add text in Outline view, the text is placed in a text placeholder. Placeholder text is easier to edit than text in text boxes.

Here is a little experiment you can do.

Start by adding some text in Outline view. The default Layout “Title and Content” is used.

Examing the text in Outline View
Looking at a slide in Outline View. The text appears in both the Outline View and the slide.

 

Here is what the slide looks like in Slides View, again the text is the same in the  Slides View panel and in the slide itself.

The Slides View panel
The Slides View panel

Now try changing the layout to one without a content placeholder. The text remains in Outline View and on  the placeholder in the slide.  Then, move the text placeholder around and resize it.

Now, change the layout back to “Title and Content” and you’ll find the placeholder snaps back to its original position and size. If you tried recolouring the text, press the Reset button (just underneath the Layout button) and it too will revert to the default appearance set by the placeholder.

Now, compare this with the behaviour of text in text boxes.

This text is in a text box. Note that it does not appear in Outline view.
This text is in a text box. Note that it does not appear in Outline view.

This text is not connected with the placeholder on the slide. It is “floating” on top of the slide “like toppings on a pizza”  in the poetic words of one of my former coworkers.

This lack of connection can make it harder to manage in the long run.

Text box text in slide with placeholders.
Text box text in slide with placeholders.

Note what happens when I change the layout to  “Title and Content“. The text box is actually floating underneath the placeholder.  What a pain for editing! Resetting the slide has no impact on text in text boxes. Also, you’ll notice that the text is not visible in Outline View, so none of those tools are available for editing either.

Does that mean that I never use text boxes?

Of course not, I use text boxes when I want to create text that will remain independent of the general formatting rules for the presentation. But since consistency in formatting is a sign of a professional presentation, I use text boxes sparingly.

LATCH

Faced with designing a PowerPoint presentation and you don’t know where to begin? Try using LATCH to organize your material. First proposed by Richard S. Wurman (who also founded TED); LATCH offers a method of organizing your information. LATCH is an acronym that stands for; Location, Alphabetically, Time, Category, Hierarchy. Mr. Wurman’s brilliantly simple idea is that all information can be organized using one of these frameworks.

Some examples of LATCH are useful:
Organizing by LOCATION:

  •  Maps
  •  Diagrams; for example an anatomy diagram labeling parts of the body.

Organizing ALPHABETICALLY:

  • Telephone books
  • Filing systems
  • Indexes

Organizing by TIME:

  • Schedules (for example, a bus schedule)
  • A manufacturing process
  • Historical information

Organize by CATEGORY:

  • Retail stores organize their goods by category
  • Libraries separate their books into Fiction, Non-Fiction and other categories

Organize by HIERARCHY:

  • Best to Worst
  • Lightest to Heaviest
  • Military Command structures

You might enjoy watching the following: https://www.youtube.com/watch?v=Tgi1JQGHENI#t=12

Some kinds of information can be organized using more than one of these methods. For example a bus schedule is better understood if a map accompanies it. As the author of a presentation it is your job to figure out which method is best for your presentation or if multiple methods would bring greater clarity.

Using LATCH can help the presentation flow better and it can also help users recall more information, more effectively. Psychological studies have determined that when presented with a list of information, people can remember roughly 7 items (plus or minus 2 ). And that the longer the list is, the better chance people have of forgetting everything. So if you have 12 things to tell people, how can you help them remember?

When people have longer pieces of information to remember, they divide that information into “chunks“ that are easier to remember. Think about the telephone number 867-5309 . If you are trying to memorize that number, is it easier to remember?

8
6
7
5
3
0
9
Or
867
5309

By “chunking” the number you reduce a longer list into 2 items.

When my husband was in university he enrolled in a course that he wasn’t really looking forward to – “The Biology of Invertebrate Animals”, because he knew that there would be a lot of memorization. But his professor did something interesting; at the end of discussing each animal, he would talk jokingly about how they would cook that animal in China (he was Chinese). The humour helped of course, but he was also categorizing the animals in an interesting way “Animals We Eat” vs. “Animal We Don’t Eat”.

In some ways, this categorization was completely artificial – students weren’t tested on Chinese recipes after all. But usefully, it provided an interesting category system that helped students to “chunk” the information and retain it. Even now, many years later, my husband can recall invertebrate information because of this categorization system.
By organizing your information using LATCH, you help your audience group it into meaningful chunks, so they will retain more information.


[†] On a bad day, a very short list.

[‡] Now you have the Tommy Tutone song stuck in your head. https://www.youtube.com/watch?v=ON56AKnqbog