Understanding Office 365 Plans

May 27th, 2015 by datapig 6 comments »

To my dismay, I'm constantly being asked which version of Office 365 is the best for Excel analysts. Like everyone else, I typically provide a semi-vague answer which was most certainly pulled from some website somewhere. Well, I recently stumbled on this nifty TechNet site that outlines the differences between the various versions of Office 365.


Unlike the generic comparisons we typically see, this interactive workbook shows all the detailed features of the Office and SharePoint realms – and which version contains them.


I took a shot at summarizing the parts of the matrix that are most important to business technologists like us.

The following table shows each of the key features we typically care about, and the Office 365 plans that seem to be the most relevant to our line of work.


A few things I noticed:

  1. If you're a small business owner that needs Power Pivot/Power View functionality, the Office 365 Business plans don't fulfill that need. If you purchase a Business plan, and you find that you need the "Power" tools (other than Power Map), you'll need to purchase the Office Professional Plus stand-alone edition. The Office Professional Plus stand-alone edition is basically the traditional "buy a disk" model.
  2. If you're a small business owner and you want to build Access Web applications and publish Power Pivot reports to the web, it looks like you'd be better off using the Office 365 Enterprise E3 plan. The Business plans seem pretty useless (in my opinion).
  3. If you're a student or you work in the Education field, you're golden. The Office 365 Education E2 plan gives you virtually all you need for only about $5 per month! All you need is an email address that ends in edu.
  4. If you work in a corporate environment, you will probably have access to at least the 365 ProPlus or Professional Plus stand-alone versions of Office. If not, you can get up to speed by purchasing an Office 365 ProPlus subscription, or springing for the Office Professional Plus stand-alone edition.
  5. As I understand it, you only get the Preview version of Power Map with the Office Professional Plus stand-alone edition.

Any other observations?

Too Black for Excel

May 19th, 2015 by datapig 7 comments »

You know what bothers me? As you can tell from the ambiguously racist title of this post…black font.

I know it sounds strange, but the default font in Excel is somehow too black. The contrast of the white background and the really dark black font color annoys me. In a time when we've moved away from the standard VB colors, why does Excel default to RGB(0, 0, 0)?


Lately, I've made it a point to tone down the default black color in my spreadsheets. Although the difference is subtle, I think my reports look better both on the screen and in print form.


For example, in the screenshot below, the table on top is formatted with the default Excel black RGB(0, 0, 0). The bottom table is formatted so that the font color is RGB(75, 75, 75).


Here is another example without the gridlines. See how harsh the black looks in the top table? Toning down the black font makes a subtle, but positive, difference.


Here's another example. Which looks better to you?

To me, the font color on the left looks very 1997. The text on the right feels more modern.


Now if you think I'm being a little mental, I understand.

Not all of us can have the superb style and taste that flows through my ample thoroughbred physique.


If you agree with me, you may be wondering how you can change the default Excel font color.

Unfortunately, there does not seem to be a setting that lets you simply select a default font color. But there is a workaround. You can format a blank workbook and save it as your Startup workbook.


Step 1: Modify the 'Normal' Cell Style

Open a new workbook and then right click the Normal cell style found on the Home tab.

Select the option to Modify…


This will open the Style dialog box where you can choose to apply your own formatting to Numbers, Font, Borders, etc. Click the Format button and then format the font color. I chose to apply a custom color RGB(75, 75, 75).


Step 2: Save the Workbook as your Startup Workbook

Once you confirm all your formatting changes for the Normal cell style, you can now save the workbook as your Startup workbook.

To do this, you will need to save the workbook in the XLSTART directory on your computer.

This directory is typically found at this path:



If your XLSTART directory is not there, or you can't find the XLSTART directory, you can find the actual path using the Immediate Window.

1. Open the Visual Basic Editor (press Alt+F11)

2. Activate the Immediate Window (press Ctrl+G)

3. In the Immediate Window, type: ?Application.StartupPath

4. Press Enter

You'll see where your XLSTART directory lives. Save your workbook to the path shown.


At this point, you can close Excel, open it back up, and then open a new workbook.

You will see that Excel opened your preformatted workbook with your toned-down font.


Well, that's my rant on black. Time to go get some black coffee.

Using the Immediate Window to Get a List of File Names

May 13th, 2015 by datapig 10 comments »

Here's a trick to get a quick list of files from a certain directory.

Open the Excel or Access Visual Basic Editor and then press Ctrl+G to see the Immediate Window.

Enter the following syntax and then press Enter. Be sure to change the directory (in red) to the path of your directory.

File = Dir("C:\MyDirectory\"): Do Until File = "": Debug.Print File: File = DIR(): Loop


Your reward will be a list of all the files in that directory. You can now copy this list and use it as you see fit. » Read more: Using the Immediate Window to Get a List of File Names

Ten Simple Tricks to Speed up Your Excel VBA Code

April 13th, 2015 by datapig 16 comments »

I'm currently on a project to improve the performance of a particular workbook.

Part of this task involves speeding up a few of the macros in the workbook.

While I'm thinking about this stuff, I'd like to share a few simple tips for speeding up your Excel VBA code.

Some of you will know all of these and more. So feel free to comment and share any other performance tips you may have.


Halt Sheet Calculations » Read more: Ten Simple Tricks to Speed up Your Excel VBA Code

VBA to See Who Last Updated a Workbook

April 7th, 2015 by datapig 7 comments »

Have you always wanted to prove that your co-workers are morons intent on making you look bad? Well today, I'll share a trick that will make all you paranoid schizophrenics very happy.


A friend of mine asked if there is a way to easily see who last updated a particular workbook.

Apparently, he works in an environment where multiple people are editing workbooks on a share drive.

Of course, he knows that the workbook properties will show who last saved changes, but he wants to see that info as soon has he opens the file.


My answer to him was to hijack the Excel Title Bar. The Title Bar usually only shows the name of the workbook. But with a bit of simple VBA, » Read more: VBA to See Who Last Updated a Workbook

Excel Power BI Boot Camp in May

March 23rd, 2015 by datapig 2 comments »

Hi Folks. I've been busy wrapping up the final touches to a book that will come out in May.

I don't have time for a full post, but there's always time for a crass commercial message.


I wanted to give you a heads up on our new BI training scheduled for May in Dallas Texas.

This 3-day event is aimed squarely at Excel analysts who find it increasingly necessary to become more efficient at working with the new Microsoft BI tools like Power Pivot and Power Query. 

Bill Jelen (MrExcel) and I will guide you through the mysterious world of Microsoft's new BI Tools from a business analyst's point of view, introducing you to the rich set of tools and reporting capabilities that can be leveraged to more effectively synthesize data into Business Intelligence Dashboards. » Read more: Excel Power BI Boot Camp in May

Pull your Global Address Book into Excel

March 6th, 2015 by datapig 6 comments »

A friend of mine recently asked if there is a way to create an Excel table with employee information from the Outlook Properties dialog box for all employees in his Global Address Book. The Outlook Properties dialog box (seen below) gets its data from the company Active Directory. Active Directory is a special kind of database that is typically used to store organizational data such as employee names, user IDs and location information.


It's often useful to have a table of employee names and contact info. I have pulled this kind of table together in the past with some VBA code. But today, we don't have to. We can use Microsoft's free Power Query Add-in to reach into the Active Directory and extract data like email address, employee, department, and telephone number.


Today, I'll show you the steps you need to take to create a refreshable alpha roster of employees and contact info. » Read more: Pull your Global Address Book into Excel

Automatically Delete Pivot Table Drilldown Sheets

February 24th, 2015 by datapig 6 comments »

I don't know about you, but my favorite thing to do is to pass off a built in Excel feature as one of my own genius report development ideas.

Me: "I made it so that you can double-click on any number inside the Pivot Table to drill into details"

Client: "ooh….nice feature Mike. You're a genius!"

Me: "Well what can I say...I give because I love."


Well that nifty trick of double-clicking inside a Pivot Table to drill down has a side effect; it leaves behind lots of worksheets. In most cases, we don't want to keep these sheets. In fact, they often become a nuisance, forcing us to take the time to clean them up by deleting them. This is especially a problem when you distribute pivot table reports to users who frequently drill into details. There is no guarantee they will every clean up the drilldown sheets. Although it is unlikely these sheets will cause issues, they can clutter up the workbook.


Today, I'll share with you a little VBA that removes the junk drill-down sheets left behind from double-clicking on a Pivot Table. » Read more: Automatically Delete Pivot Table Drilldown Sheets

Text Match and Fuzzy Lookup

January 28th, 2015 by datapig 7 comments »

I would bet that every one of us has seen Customer lists from separate sources that can't be matched up because of spelling mistakes and inconsistent naming conventions and abbreviations. In fact, there are consulting firms whose entire business revolves around helping organizations reconcile their "Master Data". I can't count how many times I've seen entire projects launched to create a "gold source for master data". Yet here we are; manually matching up addresses, customer names, or people names from different data sources.


Well today, I'd like to introduce you to a free tool from Microsoft that can help ease your pain a little. This tool is a free Excel Add-in called Fuzzy Lookup. The Fuzzy Lookup Add-in enables you to perform similarity analysis to match textual strings that mean the same thing but are not spelled exactly the same. With this tool, you can match things like customer addresses from two separate data sources, or even find imperfect duplicates in a single dataset.

. » Read more: Text Match and Fuzzy Lookup

Business Intelligence vs Data Science

January 26th, 2015 by datapig No comments »

Hello all! Belated Happy New Year! I've been busy working on a new book and a few other projects. It's time to get back to blogging.


I recently saw this article explaining the difference between BI Analysts and Data Scientists.

On the surface, this article highlights the operational differences between BI Analysts and Data Scientists. But at the core, it's a view into how the BI industry has changed over the last few years. Here's a graphic from that article.


As I look at this graphic, » Read more: Business Intelligence vs Data Science