Moving to officewizkid.com

Over the years, the Microsoft Office tips and tricks and more specifically the tutorials for Microsoft Excel have been an important part of this blog. At some point all of these entries deserve a separate blog to focus only on that.

Last month, i started a migration process to move all the Office related entries from this location to a new home: blog.officewizkid.com a place dedicated to Microsoft Office conversations.

Hope to see you soon visiting that site.

– Dave

Excel Bi-Directional Temperature Converter

A revised version of this content is now available at blog.officewizkid.com


I recently received a question on how to develop a bi-directional temperature converter in Excel. Sounds simple enough but the trick here is the bi-directional statement.

The requirement is as follows:

Let’s assume that Cell B4 represents temperatures in Fahrenheit and Cell D4 represents temperatures in Celsius. Changing one of them will need to automatically change the other one.

The end result should look like the following:

Excel Temperature Converter

Developing an expression that can do a one-directional conversion form Fahrenheit to Celsius is easy. Just type in D4 the expression:


=(B4-32)*5/9

Developing an expression that can do the reverse (still a one-directional conversion) form Celsius to Fahrenheit is easy. Just type in B4 the expression:


=D4*9/5+32

But doing both is much tougher. It creates bi-directional dependency between two cells that as far as i can tell can not be done with simple expressions.

The workaround I found is by going down-level to the VBA level and writing a macro to handle a “worksheet change” event. The event will be triggered every time a cell is changed, making this solution a little bit heavy when dealing with large Excel files. It also requires the Excel to be saved as .xlsm or Excel Macro-Enabled Workbook.

Here is a capture of the code:

Excel Worksheet Change

Let’s review these statements one by one.

First please open the VBA editor in Excel (see old post about that) and use the drop-down lists to first select “Worksheet” and then “Change”. This will generate for you an empty Private Sub called Worksheet_Change.

The logic here is not too complex and has two parts:

Check that the B4 (or D4) cell was part of the Target change range, for that we check if the intersection between Target and “B4” is not empty.


If Not Intersect(Target, Range("B4")) Is Nothing Then

Next we use the “ActiveSheet.Range” function to select cell “D4” and set its value based on conversion calculation as a function of “B4” value.


ActiveSheet.Range("D4").Value = (ActiveSheet.Range("B4").Value - 32) * 5 / 9

Once completed, we can exit from that Sub.

 

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…

Basic Pivot Table Example

Microsoft describes PivotTables as “A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both reports enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable and PivotChart reports.” (see here).

And indeed PivotTable is a query tool that quickly generates reports on top of a base table of data. Once you get the basics, it is easy, interactive and very useful tool to know.

As always, let’s see that ability in a simple example:

Let’s assume we have a detailed report of product sales transactions. The report has a lot of information about each transaction, including the product code, the geo, the channel, and the amount.

sales data

And we want to run reports and analyze this date. We do that by creating Pivot Table like this:

Select the entire data area and go to the “Data” menu and select “PivotTable…”. A menu like below will open:

PivotTable dialog

Adjust the range if needed and for this simple example just leave all other parameters without a change.

The result is a new sheet that looks like this:

PivotTable Empty

Now we can dynamically select items to display and to summarize.

The simplest example: Let’s create a report of the total sales by GEO.

1. In the “PivotTable Builder” dialog, Drag & Drop the “Geo” field to the “Row Labels”.

2. Drag & Drop the “Price” field to the “Values”. By default the Count function will be applied.

3. Click on the little “i” near the Price field and a new dialog will be shown like below. Select there the function “Sum”.

PivotTable Function selection

4.Format the PivotTable column to show nice $ formatted data.

You should get a report like this:

Basic PivotTable report

Now let’s extend this example and create a report showing Total and Average price by Geo/Channel.

We will continue from the previous report.

1. Open again the “PivotTable Builder” dialog by either clicking on the previous report or by navigating to the PivotTable tab option and clicking on the Builder icon.

2. Drag & Drop the “Channel” field to the “Row Labels”. Make sure it is the second row on that list.

3. Drag & Drop the “Price” field to the “Values”. Should now have two lines under “Values”.

4. Adjust the second “Price” line to use Average function. At this stage the Builder dialog should look like this:

PivotTable Builder 2

5. Format the result report as needed.

You should have a report like this:

Basic PivotTable report 2

We are now masters of the simple PivotTable example.

Excel COUNTIF Condition Examples

A revised version of this content is now available at blog.officewizkid.com


Excel COUNTIF is one of these functions that are simple enough to understand the concept but make you scratch your head when you try to do something less trivial with the condition part.

Sure, simple cases are simple enough:

How to count all the cells in a range A1:A100 that have a specific value called “MyValue”:

=COUNTIF(A1:A100,"MyValue")

But how do you scale to more complex scenarios. E.g. How do you calculate COUNTIF with a condition that references another cell?

Here is a very nice blog with multiple examples reviewing the hidden potential of the COUNTIF function.

How to use Excel COUNTIFS and COUNTIF with multiple criteria

How To Use Excel Tutorials

A lot of what I have been writing about in this blog is related to Excel. A growing number of tips, tricks, happy moments and frustrating moments. At this stage there is enough content to try to collect that content in one place with some sort of organization. So here it is!

Check out how-to-use-excel.manha10.com

I plan to continue to write additional content in this blog and also make updates to the site as kind of a catalog of the entries. I hope you’ll find this useful.