Category Archives: MS Office

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 – Making a Mask

Creating a mask effect in PowerPoint is easy, once you’ve located the Shape Combine command. You can add this command to the Ribbon or the Quick Access Toolbar.

Below, you see it being added to my toolbar.Adding the Shape Combine Command to the Toolbar

Adding the Shape Combine Command to the Toolbar, alternately look for the Combine Shapes command as more options are available.The command will not be active until there are two shapes selected. Below, I’ve created a blue rectangle and a red oval. The oval shape will be cut out from the rectangle.Blue rectangle with red oval positioned for the cut out.

Blue rectangle with red oval positioned for the cut out.You may prefer to add the Combine Shapes command instead. More options are available as you can see below.

Select the shapes you wish to combine.
Both shapes are selected, so the Combine Shapes button is active

The result of the Shape Combine command, a rectangle with an oval “hole: in the center.The result of the Combine Shapes Command

The result of the Combine Shapes CommandOnce the mask is created, you can dress it up. Below, I’ve changed the fill to an image of a leafy forest floor.The forest floor has a hole in it.

The forest floor has a hole in it.Now I can layer whatever image I wish (in this case a frog) under the mask. You can animate the layer underneath the mask. Can you image a wheel of creatures rotating into the viewpoint in the center of the mask? That would be great for a talk about ecology!

Can you spot the frog?
Can you spot the frog?

 

Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Reminder Dialog box with highlighted Dismiss All button
Oops! I hit the Dismiss All button!

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Highlighted Search bar
Use the Search bar in the calendar view
Close up of search entry
Close up of search entry

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

Closeup of the Search Tools view
Closeup of the Search Tools view

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

Search by the month name if you don't know what date.
Search by the month name if you don’t know what date.

You can then reopen the item and reset the reminder.

Fixing an annoyance in Outlook 2010

The scenario:

You like to flag your mail for Follow Up on a specific day. But Outlook always defaults to setting the reminder at the end of the day. Can this be changed to the beginning of the day?

It makes sense that if you want a reminder set for Today, that the default time for that flag is set to one hour before the end of your work day (as defined in your Calendar settings).

It makes sense that flagging a reminder for one of the pre-defined future dates (Tomorrow, This Week or Next Week) uses the start of your work day as the default time.

It makes NO SENSE that flagging a Custom date reminder reverts the default time to one hour before the end of the work day.

To change this default to the start of your work day.

Finding the Quick Click menu
Finding the Quick Click menu

Click on the drop-down arrow on the Follow Up button on the Home Ribbon.

Select Quick Click

The Quick Click dialog box
The Quick Click dialog

The Set Quick Click dialog appears. Choose Tomorrow as the default. Click the OK button.

Done. Future custom reminder times will now default to the start of the work day – not the end.

 

 

 

 

Google Slides vs PowerPoint for Social Media

Over the last few weeks I’ve been sharing tips for using PowerPoint for social media. Which leads naturally to asking about using Google Slides the same way.

And Google Slides offers comparable features; in particular it does support saving slides as JPG’s and custom dimensions.

What doesn’t Slides have? The photo editing tools and artistic filters that come with PowerPoint are not available, so you’ll have to pre-edit in another application.  And you’ll find that your capabilities are limited on the mobile versions of Slides, so you’ll be forced back to the desktop version.

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.

PowerPoint: Make it Fit

I’ve mentioned it before, but PowerPoint can be a handy graphic editing tool. Especially when you want to combine pictures and text. The question is;  how do you get perfectly sized pictures every time?

Most people don’t play with the Page Setup options other than to swap their slides between a 4:3/16:9 ratio. However you can input your own custom dimensions.

Navigate to the Page Setup Dialog box
Make your own Custom Sized PowerPoint Templates

Go to the Design ribbon and open the Page Setup dialog box. The last choice in the Slides sized for drop down is Custom. Once this is selected you can input your own dimensions (within limits). To create the Twitter header photo template, I researched the dimensions of a Twitter header photo (500px by 1500px). Then I used this website to convert the pixel measurement to centimeters. The website also converts to inches, if that is your preferred measurement.

Now I can easily size my photos for Twitter! (Or Facebook, or Pinterest etc.)