Category: Outlook

Posted on

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.

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.

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.

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 Heads 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.

 

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

Posted on

A Quick Export View from Outlook

I’m currently advertising for an Assistant TechGenie, so unsurprisingly there are a ton of email responses hitting my email box (what is surprising; how few are from Canadian Citizens).

After I’ve sorted out the responses into a folder of likely candidates, I want to create a list of names and email addresses that I can refer to as I move through the process. The question – is there a quick way to do this from Outlook?

The answer is yes, once you build yourself a custom view. If you haven’t played with custom views in Outlook, you really should take a moment to appreciate the simple way they can add productivity to your email tasks. Today, I’m at a machine using Outlook 2016, so you might find older versions of Outlook a little different.

Create a Table View

Outlook supports numerous types of views but for this task, I’m using Table View as I can then copy and paste the information directly into a spreadsheet.

  • go the the View ribbon
  • Click the Change View button
  • Select Manage Views, the Manage All Views dialog box will appear
Outlook Manage All Views Dialog Box
Manage All Views
  • Click on the New button, and the Create a New View dialog box will appear
Outlook Create a New View dialog
Create a New Outlook View
  • Name your view with a meaningful name, since you will want to reuse it. Make sure Table view is selected, and make it available to all your folders. Click OK.  If you are following along, at this point you will notice that I have already removed the columns I don’t want.
Select only the columns you want in your final spreadsheet
Remove all the columns except the ones you plan on using

But there is one column I do want – the sender’s email address. And you will not find it in the lists of available columns. Instead, try this trick I picked up from espacecode.com

Add a Custom Column

 

  • Use the New Column button, the New Column dialog will appear.
  • Name the column (the name can not be “email”).
  • From the Type drop down list, choose Formula.
  • Paste the following formula into the Formula field: 
     IIf(InStr([SearchFromEmail], "@") = 0, "", Right([SearchFromEmail],len([SearchFromEmail])))
  • Reorder the columns in as desired.
  • Click on the OK button to complete this step and the Advanced View Settings: Your View Name will appear
Outlook Advanced View Settings dialog
The Advanced View Settings Dialog

Fine tune your View

  • Click the Other Settings button and the Other Settings dialog will appear
Outlook - Other Settings dialog
The Other Settings dialog box
  • Make sure that the Reading Pane is turned off as well.
  • Click on the OK button twice to return to the folder view. If you have Message Previews turned on for this folder, turn them off. The result should look as follows:
Outlook-SpamViewResults
Here are the email addresses of spammers exposed.

Using Your Custom View

You can now copy this information directly into a spreadsheet and easily make a list of names and email addresses. And since this is a named view, when the task is done and I want to return to my preferred email view, I can do so. But I can reapply the view at any time.

Are you looking for methods to handle your email overload? Drop ME an email catharine@mytechgenie.ca and we can build some simple tools (like this one) to help you manage your email more effectively.