Outlook on Mac is slow again :(

A new rev of Outlook is out. I used the auto-update option and always just update the environment when something new is available.

This time it is Outlook Version 15.18. It has couple of new features and the performance of Outlook on my OS X El Capitan Macbook Air is horrendous once again. This is a recurring issue.

They will probably find a fix within few weeks and push another update. It happened before. But for now, Outlook on OS X is almost unusable. Every preview of email takes for ages and you see the OSX Spinning Wait Cursor. You want to open an appointment on the calendar. Same thing happens. It takes time.

Continue to be a disappointing experience to the point i am considering to stop updating Outlook automatically and switch to a once in a year update



Document Instpector on Mac?

I read today a blog entry regarding the hidden information in Office document and what can be done about it (Do You Know What’s Hiding in Your Documents?).

From a personal experience, especially in business negotiation and contracts, it is kind of surprising to see what can be extracted out of a seemingly clean Word document that the author did not really planned to exposed.

Microsoft has a built-in tool to clean all of that called “Document Inspector”. The tool look easy to use and similar functionality is available for Word, Excel, and PowerPoint.

But I was not able to find the tool or the menu option on my Mac… A google search reveal that this capability is simply not there. I’ve seen multiple discussions about this, like this one – Using document inspector to find personally identifiable information.

As a user of a Mac I find these missing capabilities as a real annoyance. I just hope that Microsoft will take that to heart and start to complete these items…

Excel Icon Sets – Simple Example

They say that picture is worth a thousand words and that is even more accurate when dealing with numbers.

Sometimes in Excel you want to show Icons that provide quick meaningful indication for the value of the rows. A simple way to do that is by using Excel’s icon sets conditional formatting.

For a simple example, let’s assume that we have a table with product pipeline week-over-week and we want to provide visual indication if the pipeline is growing or declining using one of the following icons:


Let’s assume our pipeline table looks something like this:


And we want to add an icon beside the “Delta Prev Week” column with the icon.

To do that (on a Excel:Mac 2011) we will use conditional formatting:

1. In the menu select “Format” and then “Conditional Formatting…”. You should see the following popup:

conditional format

2. Click on the Plus sign and select Style as “Icon Sets”.

3. Microsoft provides multiple predefined sets of icons so select the Icon Set that works best for your scenario. In this case we want use the arrows.

4. Setup the value ranges. In this case, everything above zero is green, zero is yellow and everything below zero is red. The result should be something like this:

format rule

5. Finally select the range for activating the conditional formatting by typing directly in the “Applies to” column. In this case I selected the entire column D:

format rule scope

6. The result is an icon assigned to column D with the needed icon:

pipeline with arrows

There are also options to show only the icons and change the order of the icons.

See more information at Microsoft Office Talk here (It was written for Office 2007 for Windows but is still relevant).

Note that unfortunately at least in this release Microsoft does not provide an elegant way to load your own preferred icons. This is unfortunate and requires a weird workaround.

Excel VLookup – Simple Example

VLookup is an Excel search function. See the official definition at the Microsoft Office site. It is part of a family of search functions that includes Lookup, VLookup, HLookup and Match. Each one with a slightly different behavior.

The VLookup syntax seems abstract and complex so instead of explaining the syntax let’s focus on a use case:

Translation from key to its value. Such as in the case of translation from id to name.

For example, let’s assume in one place in the Excel worksheet we have a table of Customers that looks something like this:

Customer TableThe Customer table has pairs of Customer IDs and the associated Customer Names.

Now in a different place in the worksheet we have a Pipeline Table with Customer ID and Opportunity details. Something like this:

Pipeline table with no customer nameNote that in the Pipeline Table we have at column B the Customer ID. The big ask is to translate the Customer ID to Customer Name so that we can see who the customer in a more human readable fashion. See the empty column C above.

The VLookup for cell C2 will be as follows:
=VLOOKUP(B2,'Customer list'!A:B,2,TRUE)

Or in English:

  • First parameter (B2): Take the value of cell B2
  • Second parameter (‘Customer list’!A;B): Search for the first row containing this value in the “Customer list” table that has two columns. Column A and column B.
  • Third parameter (2): When such row is found, use the second column (column B) of that row to place the value at C2.
  • Fourth parameter (TRUE): The last parameter with value TRUE means that the search uses Exact match.

The final result is a new table with the customer name populated:

Pipeline table with customer name

And that is the simple example of when and how to use VLookup.

Excel Global User Defined Functions

Let’s assume you developed a great user defined function (See here and here). The user defined function (UDF) is available for this specific Excel file and works as you would expect.

But what happens when you need to create a new Excel file that also needs to use the same UDF?

For now, you simply have to re-create the UDF manually in every Excel file you create. And that is not really great from couple of reasons: First it is annoying. Additional manual steps to do every time you create a new Excel file. The second reasons is that if you decide to improve the UDF, you now have to go back to each Excel file and manually implement the change in the UDF again. And that is really not fun and a potential bigger issue with multiple revisions of the same functions running around your file system.

Luckily Microsoft provides a way to create Global UDFs. It is global in the sense that it is automatically enabled to every Excel file running in your machine. The trick is to turn the UDF inside an Excel Macro-Enabled-Workbook file (.xlsm) to an Excel Add-In file (.xlam) and save this new file in the right location so it is always enabled.

As a reminder this is our beloved Half function:

Function Half(value)
Half = value / 2
End Function

Instructions below explain how to convert the workbook to Add-In and how to enable it:
1. Edit the Excel Macro-Enabled-Workbook file (.xlsm).
2. In Excel, go to file properties and change the title to a meaningful name. Let’s say “Global-Functions”.
3. And save-as Excel Add-In (.xlam)
Save-as Excel Add-in

4. Go to the Finder and copy the new file Global-Functions.xlam to the Excel’s Add-In folder.
If is a rather “secret” folder and it changes from Windows to Mac and across every Excel version.

In my case for Excel 2011 on Mac it is in this location:
Applications->Microsoft Office 2011->Office->Add-Ins

5. The last thing we have to do is to enable the Add-In. Open Excel. Click on the Tools menu. Select the Add-Ins… option. And a pop-up windows will be opened.

6. Click on the checkbox to enable the Add-In.

Excel Enable Add-In

And that’s it. Now every time you create a new Excel file the UDF will be automatically available.

As always, more information is available in the Microsoft site here.

Excel User Defined Function With Optional Parameters

In a previous post (long time ago) we talked about creating a simple user defined function (UDF) called Half:

Function Half(value)
Half = value / 2
End Function

In this post I would like to talk about UDF that accepts optional arguments.

Here is a simple example for that case. Requirement to build a function named “Div”:

  • The function accepts as one argument named “Value”.
  • The function also accepts a second argument named “divideBy”.
  • As you can guess, “Div” calculates a division of “Value” by “divideBy”.
  • But “divideBy” is optional. If “divideBy” is not present then the function assumes a constant value of 1. I.e. Divide “Value” by 1.

To meet these requirements we need two things:

The first thing we will need is the ability to define an argument as optional: VBA provides a simple way to declare arguments as optional by simply prefixing the argument with the text “Optional”.

The second thing we will need is the ability to know if a given argument is present or not: For that VBA provides another very simple function named “IsMissing”. The function accepts an argument and returns a boolean. Value of “True” means that the argument is not present.

With that we have everything we need to write the function Div:

2015 VBA user defined function with optional parameters

Very simple indeed! Give it a try!

Also read some of the additional information at:

IsMissing Function

Excel on Mac: How to type newline inside a cell?

Here is one of the simple things that just makes life better. Typing a new line within a cell in Excel. Very useful for many reasons but essentially i am talking about the option to write, for example, an mailing address, where the first line is the name of the place, second line is the street address and third line is city and zip code. Something like this:

 Screen Shot 2014-11-08 at 9.40.45 AM

In the Windows version of Excel this was something i was using all the time. The combination of Control + Enter did the trick.

But in the Mac version of Excel this combination does not work. But don’t lose hope. There is simply a different combination that does it:

Control + Option (or Alt) + Enter

And it works just the same!

(I found this information here).