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 Heads waffle chartBombs 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 worksheetand turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.

I’m often asked my opinion about Prezi as an alternative to PowerPoint. My answer – meh.

I can’t even get worked up enough about it to dislike it. Except now, after having seen a number of Prezi presentations I do have a Pavlovian nausea response to Prezi’s default swooping motion animations.

From my point of view; watching a naive user (my son) using Prezi for the first time really exposes all its’ faults. The intense focus on the visual. There is apparently no way of storyboarding a presentation in a text only view. This makes presentations that tend to not have logical flow. It is also difficult repurpose content from Prezi. At least without moving to the paid subscription model.

And while Prezi does have attractive templates, it squanders its’ most obvious asset – motion transitions; by not guiding users on how to use them effectively. Its’ other obvious positive features, shared collaboration and its online workspace/storage are also available in other products like Google Slides.

I might look to Prezi for visual inspiration, but if I had real work to do I’d chose a different presentation package.

You’ll note that the RGB values for the colours are listed, and this is because prior to PowerPoint 2013, the eyedropper tool was not available. I also find it tremendously helpful to note what I use each colour for, so that when I open this file in a couple of years from now there will be a little less detective work.

In my last post, I mentioned I was working on a Jeopardy game in PowerPoint. In this game I want to present a series of visual clues before the answer is revealed. The audience is presented with the foreign cover for a popular book and has to guess the name of the book.

Can you guess the book, by seeing its foreign (Greek) version cover?

I want to slowly reveal the English book cover, by gradually making the foreign cover more transparent. With this particular cover, I also wanted to crop the foreign cover image to reveal additional clues. Each clue will be revealed by a click of the mouse.

Hmm is this a problem? I can not control image transparency in PowerPoint, there is no option for this in the Picture Toolsmenu.

Nope, no problem at all. You can control image transparency by:

Create a shape the same dimensions as your picture.

Remove the outline for the shape.

Change the fill option to Picture or Texture Fill and insert the picture file.

Transparency will now be available

Its’ interesting that placing a picture inside a shape allows you to manipulate that picture as if it was a shape. This concept allows me to play with things like irregularly shaped (non-rectangular) images as well.

I’ve just been working on a PowerPoint template for a Jeopardy style game. I inherited this template, and as frequently happens a little cleanup is necessary to ensure the PowerPoint template works as desired.

To help you visualize the problem – a picture of the game board

The Game Board

Each square hyperlinks to a separate slide with the question (and answer).

I felt there were a number of improvements I could do to make the presentation easier to use and maintain. I won’t go into every change today, but a couple of changes involved hyperlinks
(shortcut key Ctrl + K, if you are editing 25 hyperlinks, then the reason for using a shortcut key becomes obvious).

The first maintenance problem I ran into was that the previous designer had applied the hyperlink to both the shape ANDthe text on the shape (now there are 50 hyperlinks – if you are counting).

Shape with text on top

They did this for a very good reason; that the text on a hyperlinked shape does not change state like normal hyperlink does (the state change shows if the link has been visited or not).

So if the slides the shapes are linked to are reordered or edited, the links have to be painstakingly tracked down and edited and since essentially the links are layered one on top of each other it is a real pain.

I had a better plan. Move the button shapes to the Slide Master (after creating a layout designed for the Game Board slide). Then insert text placeholders (yes, 25 of them) for the dollar values. Position the placeholders over each button. No hyperlinks here.

Now moving back to the Game Board slide in Normal View, I can hyperlink the text box. Text boxes behave differently from shapes, and do change state to show the link has been visited.

Another advantage of the text placeholder is that if the user inadvertently moves the text boxes, the Reset command will snap them back into position. (A definitely plus when editing 25 text boxes).

The other visual difficulty I had, was with the colours of the hyperlinks themselves. They didn’t have a strong contrast with my (new) button colour, and the visited colour was still (kinda) visible. I wanted a strong link colour and once visited I wanted the link to disappear. I could add animations, but why bother when I could solve both problems easily by changing the link colours in the Color Theme.

Theme Colour Panel PowerPoint 2016

Here is the theme colour panel after I adjusted the Hyperlink and Followed Hyperlink Colours.

The colours in the theme were picked after playing with the free https://coolors.co/ app I also got some good advice from this article. The image at the top of the article is the colour palette created by the Coolors.co app – translated into RGB. I usually add this information as a layout in the slide master.

The Rijkmuseum in Amerstdam has digitized its collection. All of its works are free to use. Its’ policy “If you use our images for publication, then we request that you acknowledge the source (Rijksmuseum, Amsterdam). We would also like to receive a copy of the publication for our library.”

Kaboom pics claim to fame, is that the colour palette of each photo is extracted for you, useful if you are planning coordinating backgrounds or print materials.

Fancy Crave All photos published on Fancycrave are licensed under the Creative Commons Zero (CCO) license which grants you an irrevocable, nonexclusive copyright license to download, copy, modify, distribute, perform, and use photos from Fancycrave for free, including for commercial purposes, without permission from or attributing the photographer or Fancycrave.

The Rijkmuseum in Amerstdam has digitized its collection. All of its works are free to use. Its’ policy “If you use our images for publication, then we request that you acknowledge the source (Rijksmuseum, Amsterdam). We would also like to receive a copy of the publication for our library.”

Normally, people save their PowerPoint presentations in the default format. However, once you are on the final version of you presentation consider using the PowerPoint Show format. Saving your PowerPoint presentation as a show is easy. Use the Save As command and use the Save As Type list to show all the possibilities. Select PowerPoint Show and save as normal.

PowerPoint 2010 Save as Dialog. Not many changes here.

The show will be saved in a different file format, using the .ppsx or .pps file extension.

The result is a change in behaviour when the file is opened. Double-click on the file and it will launch immediately into Slide Show view. Much slicker than starting the presentation, allowing the audience to view your notes, finding the slide show icon and starting the presentation. If you have a presentation that uses timed transitions and you are worried about the presentation running away on you, remove the timing from the first slide. Use a mouse click to advance to the rest of your timed slides once you are ready to start.

If you need to edit your presentation, start PowerPoint and use it to open the show. You can edit the file as you would normally. If you wish to convert it back to a regular presentation, use the Save As command and save it in the normal file format.

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

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
9Or
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.