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:

arrows

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

pipeline

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.

Advertisements

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.

How to clean Excel Cells from extra blanks

Simple problem: Importing external data to Excel Cell with values that include extra spaces either at the front or at the back of the values. I.e. Instead of having values like “My Value” the actual value is ”   My Value   “.

So how do we normalize the values?

Excel provides a function called Trim for this purpose exactly, the function receives a single value and returns the value with spaces trimmed on the left and right side of the value, while spaces in the middle of the string remain untouched.

Example Result
Trim ("   My Value") “My Value”
Trim ("My Value   ") “My Value”
Trim ("   My Value   ") “My Value”

See the Microsoft documentation about the Trim function here.

In addition there are function to remove spaces only at the front or only at the back of the value, see LTrim, RTrim, and Trim Functions.

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

Thoughts about the ‘Dirty Little Secret’ Of Microsoft’s Cloud Business

Business Insider wrote a piece about the ‘Dirty Little Secret’ Of Microsoft’s Cloud Business (Read more: http://www.businessinsider.com/microsofts-cloud-business-the-good-the-bad-the-ugly-2014-11#ixzz3J9nr18dq).

In essence the write-up suggests that Microsoft is pushing an inclusion of free-cloud-capacity to its huge on-premises customer base as part of large on-premise enterprise renewals. And that later on as part of Microsoft’s internal revenue allocation the renewals are allocated partly to the cloud services. This can potentially explain Microsoft reported $4-billion-mark in revenue from the cloud services.

Free Cloud

I am no expert on revenue reporting but that sounds somewhat “creative” way of looking at the numbers. To add to that, the other critical aspect that is suggested in the article is that a lot of the free-cloud-capacity remains not used, what in the good old days of on-premises software we used to call shelfware.

Here is the little contribution I can bring to the table.

Over the past couple of years (and this started before Microsoft’s new CEO era), i have witness several conversations within IT managers at large North America corporations, where they were debating about what to do with the free-cloud-capacity from Microsoft. Sometimes these discussions became almost comical. Trying to find a good use for the extra capacity without a real business imperative driving it. Driven by IT and not by the Business. And with a label of temporary associated with it as the free-cloud-capacity has expiration date associated with it.

One can wonder if the transition from free-cloud-capacity to a paid-cloud-capacity will be as smooth as Microsoft would want. I suspect that this is going to be a dramatic question for Microsoft’s future success.

Outlook for Mac missing support for dual time zones

One of the things one discovers when moving from Windows to Mac and still using the Microsoft Office package is that the Windows and Mac packages look similar but they are for sure not the same. There are number of small features here and there throughout the package that are missing on the Mac.

Outlook Calendar support for dual time zone is one of these examples.

The Outlook Calendar on Windows can support two timezone (see here how to set it up). It is very useful feature and has been available on Windows for many years. When enables the Calendar shows on the left side a dual hour stripe instead of the single timezone that is enabled by default. Not a huge thing but still it is a very convenient feature. For me it is important as I am located in the US but working closely with a team in India. Every time i setup a meeting it is very useful to remind myself what is the time for the people on the other side of the line.

When i switched to Mac and installed Outlook 2011 for the Mac i was surprised to find out that dual timezones is not supported on the Mac. Doing some searches it was confirmed that this is indeed missing feature (see here for example of discussions on this).

Last week i did the upgrade to the new rev of Outlook that is called “Outlook for Mac for Office365”. It is positioned as an equivalent to Outlook for Windows 2013. One of the things i was hoping to get from the upgrade was more consistent feature parity between Office for Windows and Office for Mac. And specifically the much needed, dual time zone support. But it is not there. At least as of November 2014 this is a missing feature.

There are other questions regarding the upgrade such as in what way it is connected to Office365 (as my company is using Exchange installed on-premises) but that is a point for another post.