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.

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.

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.

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.

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.

This post is originally from 2018. If you want help with the newest and classic features in Excel & PowerPoint drop me a line at catharine@mytechgenie.ca

December 16, 2019December 5, 2019

PowerPoint: Use the Grey Space

The grey space around your PowerPoint slide is a useful area.

Picture of the workspace in PowerPoint — The grey space around a PowerPoint slide is used to hold instructions.

You can put it to work in a couple of ways:

Park items you’re not working with but still want to have on hand, like an image that you want to keep in the file for use later.
Put instructions for other users here.

Does your organization need easy to use, on brand presentation templates? Drop me a line at catharine@mytechgenie.ca, and let’s talk.

December 5, 2019

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

I have slightly updated this post from when I first published it in 2015. If you need help creating your next presentation, email me at catharine@mytechgenie.ca or join me Friday, June 5/2020 from 9am – 12pm at Medicine Hat College for Plot A Great Talk, a 3 hour seminar dedicated to helping you create your best ever presentation. Here’s the .

November 1, 2019November 1, 2019

Three Presentation Mistakes

This is a reissue of an article I originally posted on March 7, 2019 on the WebGenii Consulting website. I think it will continue to be relevant for some time to come.

Last weekend I attended the Southern Alberta Library Conference. I really enjoy this conference, the speakers are great and the topics really relevant to my volunteer work with the Redcliff Public Library. So what does this have to do with presentation mistakes? It was interesting to see the kind of presentation mistakes that speakers who are good at presenting make.

Mistake Number 1

Our old friend – too much text on the slide. Even good speakers do this, even though they shouldn’t. I suspect because they worry about leaving something out of their presentation.

All the sentences, all the text — All the text, nothing missing. (Dummy text courtesy the Bacon Ipsum generator)

Once again, I’d like to join my voice to all the presentation experts telling you NOT to put all your text on the slide. But, I know it will happen anyway, so what can we do to improve a slide like this?

Remove Bullets

Bullet points add nothing — Same slide – fewer bullets

If you are going to write full sentences with punctuation, then bullets are completely unnecessary. They take the viewer’s eye away from the content of the sentence. Save bullet points for sentence fragments, which is what they are designed for.

One Sentence Per Slide

Help the audience focus its’ attention by restricting yourself to one sentence per slide at a time.

Position Sentence Text

Control Text Wrapping — Control text wrapping to effectively position your message

There is no rule in PowerPoint (or any presentation software) that requires you to use the default text wrapping. Add line breaks to force text to wrap for greater readability and easier recall. Notice how the ham jumps out from the rest of the text, when it is forced onto its’ own line. Think about the part of the sentence you wish to emphasize and add line breaks accordingly. Also, if the sentence is on its’ own slide, there will be room to do this.

Mistake Number 2

Smart Art can cause problems of its’ own. In particular, the seductive way it shrinks text to fit into the graphic makes people forget to edit. (See mistake number 1)

Smart Art can be a problem — Smart Art isn’t as smart as you think

Also, the default colour schemes means a lovely rainbow of colours. How is this a bad thing you ask? Well, inevitably you get a colour combination like point three in the graphic above. White text on a yellow background. That’s readable on a computer monitor, but when projected onto a screen it doesn’t have enough contrast.
The rainbow effect above, does something else as well. It wastes the potential usefulness of those colours. Colour is a great way of adding organization and hierarchy to a presentation. In the slide above, perhaps green refers to free-range meat, blue to fish, yellow to poultry, red to spicy foods, and I have no idea what pink would refer to. Because there is no organization being used here, just the random default applied by Smart Art.
Ignoring the organizational impact of colour, is like leaving money on the table.

Mistake Number 3

This last mistake is a little bit of mistake 1 AND mistake 2 combined, and it comes from using Smart Art process graphics like the one below:

Every time, a process graphic like this leads to the speaker saying “I know this is hard to read, but”. Hmmm, yes it IS hard to read, but I can understand the desire to help people understand the flow of a process. So why not introduce your process in a series of slides like this:

Showing a step in a process — Introduce your process in a series of slides.

In this sample slide I’ve taken the process and reduced to a smaller graphic in the top left corner. Here it will act as a map to show people where we are. I’ve toned down the colours of the steps that are not being talked about on this slide. I’ve left the bright blue alone, because we are talking about the blue step on this slide. I’ve cut out the blue step and enlarged it, so the text will be easier to read. It is easy to imagine each step in turn being featured on a separate slide and highlighted on the map.

Once again, thanks to everyone who spoke at the Southern Alberta Library Conference. I learn a lot about how to be a better library board member every time I attend. And, if you are a resident of Alberta; consider volunteering in your local library. It really is the best volunteer gig around. Such a positive environment that really makes a difference in the community!

I offer presentation design services and coaching. Feel free to send me an email.

My updated (November 2019) email address is: catharine@mytechgenie.ca

M	T	W	T	F	S	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31