Power Pivot eBook Coming Soon

It's been a long time coming, but we are putting the finishing touches on the third installment of our free 'DIY BI' series. Consequently, we are excited to announce that the Power Pivot eBook will be officially released on Tuesday, July 3, 2018!

Power Pivot eBook

This brand new book will feature five of Ken's top tips, tricks, and techniques for Power Pivot, including:

  • Hiding fields from a user
  • Hiding zeros in a measure
  • Using DAX variables
  • Retrieving a value from an Excel slicer
  • Comparing data using one field on multiple slicers

Power Pivot eBook


About the 'DIY BI' Series

This free eBook series is available to anyone who signs up for the monthly(ish) Excelguru email newsletter. The series includes four books, one edition each for Excel, Power Query, Power Pivot, and Power BI. Each book contains five of our favourite tips, tricks, and techniques which Ken developed over years of research and real-world experience.

DIYBI eBook Series

We first launched this series in the spring of 2017 with the Excel Edition, and the Power Query edition followed later that summer. You can read some more about why Ken decided to create this series in his initial blog post about it.

The Excelguru Newsletter

The monthly Excelguru email newsletter features the latest updates for Excel and Power BI, as well as upcoming training sessions and events, new products, and other information that might be of interest to the Excel and Power BI community.

Don't Miss Out, Get Your Free Copy of the Series

If you're not already a newsletter subscriber, you can sign up here. We will send you the Excel Edition right away, and the Power Query Edition a few days later. All of our current and new subscribers will receive the Power Pivot edition once it is released on July 3, 2018. Be sure to keep an eye on your inbox for the new book.

We will be continuing to work on the fourth and final book, the Power BI Edition, over the coming months so stay tuned for details!

Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required

Naming Conflict Fun

Jeff Weir published a post at DDOE last week on global names freaking out when a local name is encountered.  It reminded me that I ran into something similar when I was testing text functions in Power Query a while back; a naming conflict when I created a table from Power Query.

Interestingly, I can replicate this without Power Query at all using just native table functionality.

Set up a Table

To begin with I created a very simple table:


Then I gave the table a name.  In this case, for whatever reason, I chose “mid”:


Enter Wonkiness (Naming Conflict)…

Okay, it’s a weird table name.  I get that.  But in my original example I was comparing Power Query’s Text.Range function with the MID function, which is why I named my table mid…  anyway…

Add a new column and type in =MID


You can see that we’ve plainly chosen the MID that refers to the function, not the table.  I even set the capitalization correctly to make sure I got the right one.  Now complete the formula:



And press Enter:


Nice!  Apparently Excel is too smart for it’s own good and overrules the interpretation of built in functions with table names, resulting in a #REF! error.

Fixing the issue

The solution to fix this should be pretty obvious… rename the table.  When you do, you’ll see that it also updates the formula:


Plainly Excel was very confused! So now we just need to fix the formula:


And we’re good.  🙂

End Thoughts

To cause this issue from Power Query, you simply need to give your query a name that conflicts with an Excel function (like MID).  When it's loaded to an Excel table, that table inherits the query name as the table name.

The naming conflict issue has probably existed since tables were implemented in Excel.  It’s not good, but at the same time, it’s taken me a long time to trip on this, as I don’t usually use a table name that conflicts with a built in function name… at least not one that I use.

Long story short:  Avoid naming your tables (or Power Queries) after Excel function names.  😉

Quick Tip – Flip Numbers From + To – (Or Vice Versa)

I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here's how:

  • Copy the range of numbers
  • Right click the destination and choose PasteSpecial
  • Select Values and Subtract:

  • Say OK and voila:

Now interestingly enough, if there are values in the cells, it overwrites them. If there are formulas in the cells, it subtracts them from the formulas. Kind of odd that it isn't consistent.

(And of course, if you subtract a negative number, it turns into a positive too.)

Excel Events Calendar!

Hi everyone,

As per my last post, I recently set up a free public Excel Help Forum on my website. I'm pleased to say that over the past week and a bit we've attracted 50 new users, and now have over 40 threads with over 200 total posts on the site. Not bad for 10 days!

I've spent a lot of time configuring different options to try and make this forum as consumable as possible for people. In addition to the basic forum functionality, some of the modifications include:

  • Serious anti-spam solutions to keep the board all about questions and answers, without having to wade through garbage
  • Facebook integration, for those of you who like to tell your friends what you're doing 😉
  • RSS feeds, for those who like to keep up to date on the topics, and drop in when you see one that interests you
  • Tapatalk integration, for those who want to keep up with the site on a mobile device

And those are just the big ones. There's been a lot of tweaks under the hood to make the experience as optimal as possible, and a few others are coming soon.

The latest thing I've worked on is adding what I hope to be THE Excel Events Calendar on the internet…

I'd like to invite everyone to use the Calendar on the forum as a public Excel Events Calendar. If you have, or know of, an Excel event in your area, please post it for the world to see. I'd like to make this the most comprehensive Excel training calendar on the internet.

There are only 2 rules I'd like to attach to this at this point:

  • Put the location of the event in the title, if applicable. The goal is that people will be able to find events that they can attend in their own backyard, worldwide.
  • Only the date(s) of event should be posted. (I don't want a post each week reminding people.) If there is a registration cutoff date, please post it in the event information.

I think if everyone observes the above, the calendar should remain relevant and helpful to everyone.

Events I would expect to see include training courses (live or online), new book release dates, conferences that deal with subject matter relevant to the users on this site. (Be it BI, SQL, Sharepoint and more, so long as it has an Excel flavour, it counts.)

If you are a trainer, teacher, publisher, or whatever, I invite you to participate. Register for the site, if you're not already, and post your event to the Excel Event Calendar. The more up-to-date and accurate we can make this, the more our community can rely on this being the source to come to for training courses. The more that happens, the more likelihood you'll get signups. The more signups you get, the more likely you are to want to put on more training events, and the more the users win. It's a self-fulfilling cycle that is in all of our best interests.

Oh, and one final word on this to everyone. I am not doing this to solicit commissions, affiliate links or funding in any way. There is already enough advert links on the site. This is about people helping people in our Excel world.

New Excel Help Forums at Excelguru.ca

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time... creating my own Excel forums.

I've been absent from the online forums for the last few years, and I've greatly missed it. With the amount of teaching I've done over the past few years I've constantly been referring people to other forums and blogs, and I've received several comments from those people asking about a forum on my own site. So during this time I've been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I've been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I've put off for my job over the past few years.

To this end, I've begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I've also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it's important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You'll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you're trying to do. After all, a picture saves 1000 words, right?

And if you're an expert, or even if you'd just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won't always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you'd prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

Question on your modeling practices…

So I've been working on a massive model over the past year, and recently recorded a webcast on modeling practices. In the course I cover some of my philosophies for making sure that the model lends itself to attracting valid data, namely I colour all input cells green and tell my users that green means go. This is a practice that I've followed for many years, and it's served us very well at work.

When working on my model though, just coding the input cells isn't enough. I need to enforce different looks for different cell types, and for this I reached to styles to help me manage consistency across the workbook. My custom styles gallery looks like this:

All the DE_ styles are data entry cells. The Head_ styles are for headers, and the SYS_ styles are for formulas and text that I don't want the users to change.

For a long time this worked out really well for me, but then I had to implement a change… I had to link to data in an external file. I try not to do that, and I need to be able to see this data in my model, but I didn't have a style to do this. Excel 2010's gallery has some default styles shown here:

As an accountant, I just can't have a block of Linked Cells with double underlines all the way through. Double underlines are for totals, not accents. So I ended up building my own style for this too:

I think it's equally ugly, but it does draw your attention to the fact that they're pretty dangerous.

I'm just curious as to what conventions you use. Do you highlight links in any way? Do you draw attention to internal links (to other sheets) or only external? What other things do you try to draw attention to?

Working with Styles

If you've followed my twitter feed over the past few months you've seen that I've been working on a couple of pretty large financial models. These models give us the ability to change a large variety of inputs in order to predict our real estate development over the next 25 years (through the build-out of our remaining lands), as forecast the effect of the real estate sales on our operating divisions (golf course, marina and fitness centre.) To give you an idea of their scope, they consume over 170 pages of 11x17 paper when printed.

This project was actually pretty neat in that I was able to sit down with people, scope it out in full, and apply every best practice to it as I was building. I'm really proud of these, and even based my recent course for CGA off the techniques that I used in these works.

In the end, I built two parts of the finished model, and inherited a third piece that had been built by someone else.

One of techniques I used was to apply Styles to the model. (If you're not familiar with Styles, then have a quick read of this article on my site.) As it turns out, that was a very smart move. Earlier this week I got the call that my headers needed to be changed, and I was provided an RGB colour scheme. In the model components that I built it took less than five minutes to update the Styles to the new colour scheme and the whole model was updated.

In the other model, the one I inherited, well… I'm still working on updating all the pieces. This time, however, I'm actually updating it with styles so that I'm not caught again if there is another change.

What I’ve been up to lately

My blog posts have been a little scarce (understatement) over the past couple of weeks. This is because I've been working pretty hard on a couple of webcasts that I'm going to be shooting tomorrow in Vancouver for the Certified General Accountants.

We're doing two webcasts on Financial Modeling. The first will be on introducing dynamic components to the model, and the second focuses on techniques to make the model stable and last as it is passed off to other users.

I've done many live Excel courses, and love doing them, as I get to see the students and interact with them one on one. This is going to be a new experience for me though, as this is a pre-recorded session with live Q&A afterwards. The company producing the webcasts has been great to work with so far, and after our conference call yesterday I can't wait to see how these are going to turn out. This should be very cool.

For anyone interested, the landing pages for the webcasts are here:

Running Office 32bit with 64bit

I recently migrated to the 64bit edition of Microsoft Office 2010. My main reason for doing this was that I wanted to make use of the RAM in my machine with PowerPivot, but it certainly didn't come without a bit of pain. The most notable parts there were that I had to convert a bunch of 32bit API calls in my VBA to 64bit compliant calls while preserving 32bit compatibility for the other machines in my office. Despite my initial intimidation here however, some good friends helped me out and I was up and running within a few hours, and I know believe I understand how to migrate the rest of my code easily. (I may post on that in the near future.)

One challenge I did face though was that you cannot run Office x64 on the same machine as Office x86 (32 bit). To install Office x64 you must uninstall any 32bit version of Office programs first. This means Visio, Project, Office 2007, Office 2003, etc…

My challenge with this is that I teach courses and like (need) to have multiple versions of Office installed so that I can teach in the appropriate versions. I could use a full blown virtual machine, but the problem here is that I find it inconvenient when trying to teach. I can't flip back and forth between the app and my presentation easily, my presenter mouse doesn't work in the VM… it just doesn't work.

So when I installed Office x64, I was a little disappointed. I really wanted to run it side by side with Office 2003 and 2007, but I couldn't.

But then, in a discussion with one of my staff yesterday, we ended up chatting about Windows XP mode in Windows 7. It IS a virtual machine, but it allows you to run an app installed in the VM as a program from the host (Windows 7) desktop. Here's a shot out of my start menu of the applications I installed in the Windows XP Mode VM:

So this is pretty cool. I've been able to have Excel 2010 x64 open and running on my laptop, and I was able to successfully launch Excel 2003, Excel 2007 and Excel 2010 (32 bit) all together.

The Windows XP mode apps all run on the virtual machine, so they are segregated from my host operating system which makes this possible. I also installed SmartIndenter and MZTools in the VM, and those both show up in the VBE for the apps when I launch it.

I'm really impressed with this. Granted it's far from perfect application level virtualization, but it allows me to do what I need. All the issues I mentioned above are solved with this. I can run all the versions of the app together, and they seem to run seamlessly.

If you are running Windows 7 and want to check this feature out, here's the link: http://www.microsoft.com/windows/virtual-pc/download.aspx