This is a personal post, but it was a HUGE weekend for me where some very cool things happened.  If you’re a fan of Excelguru on Facebook, follow me on Twitter, or are connected to me on LinkedIn, you’ve already seen a bit of this news, but this is a more detailed version of things. 

It started with a soccer game…

We started bright and early, hopping into the car at 7:30 on Saturday morning to head down to Victoria.  Excelguru sponsors the Angry Jellybeans girls soccer team, of which I’m the coach, and we were off to play in the Gorge Soccer Association’s mini jamboree.  Three games over two days, and a good test for our Nanaimo based team to see how they’d fare against the Victoria clubs.  We pulled in shortly after 9:00 and played our first game at 10:00.  The girls came out flying, earning a 6-1 victory in their first match.  Confidence was high, the sun was shining, and it was turning into a great day.

We had some time to kill before our second match at 2:00, so we all hung out in the sun for a while, had a hotdog, and just enjoyed the day.  I hung out with my daughter for a while, just chatting.  I don’t remember most of what we talked about, but I do remember this… “Daddy, why do you have to go to an awards presentation tonight?  I want you to stay with us for the team dinner.”  It’s the kind of thing that kills you, you know?

As a bit of background here, my boss had nominated me for Vancouver Island’s Top 20 Under 40 awards.  The award recognizes the top 20 business and community leaders under 40 years of age on Vancouver Island.  I’d been shortlisted to the top 100, but the awards banquet was Saturday night in Courtenay… about a 3 hour drive from Victoria where we were playing.  Fortunately I’ve got a great co-coach in my friend Scott.  Scott agreed to look after the team dinner, my in-laws got a hotel room and kept my daughter overnight, and Deanna and I were set to head up to Courtenay immediately following the game to make it in time for dinner.

At any rate, what can you do to a question like that?  I looked at her and said “well, sweetie… if someone wants to honour you with an award, you should do everything you can to show up.  It’s only polite.”

She nodded, not happily mind you, but she accepted it.  Then came a question from behind her:  “Coach Ken, what award are you going to win?”

The question to them wasn’t IF I’d win, it was WHAT I’d win. 

The funny part is that I hadn’t really told a lot of people about this at all.  I’d been shortlisted to the top 100, but I was actively trying to convince myself that I would be okay with that, even if I didn’t get recognized in the top 20.  That’s a hard battle to wage with yourself when you are as competitive as I am.  Really hard.  I don’t settle for short of the mark.  The award said Top 20… to me, that was the mark.  If I’d never been nominated, I wouldn’t have lost a minute of sleep over it… but I was… and that meant the prize was top 20.  And yet, I saw the bios of the other finalists, and they were incredible.

I laughed it off, and pretty soon it came time to square off in the next match so we were back to the pitch.

The second game… well… it didn’t start so well.  The girls didn’t play nearly as well as they should at all, and we were losing 0-1 at half time.  Scott and I pulled them in, had a little chat about how they were playing and how we knew they could play.  It was pretty cool, actually… we coached and they listened… and the second half was a different story.  The girls turned it on, scoring three, conceding one, and ending up with a 3-2 come from behind victory.  We were 2-0-0 on the day.

Equally cool though, was what happened on the sidelines.  We play 6v6 (including a keeper).  Since I keep my keeper in net the entire half, that meant I had 5 girls on the field to sub on and off, and 6 on the sidelines.  We’d taken to subbing 3 at a time.  If it was a corporate environment you would have called it a succession plan… and the effect was interesting.  6 of the girls decided that they should do some cheering… Who’s going to win?  Not the King, not the Queen, it’s the Angry JellyBeans!  3 girls went on the field, 3 came off.  And the three who were still on the sidelines pulled in the new arrivals… a new round of cheering started.  Then 3 went on and 3 came off… and the cheering started again… and again… and again…

It was very cute.  The cool part was this though… our opponents started a cheer of their own… and their coach was wide eyed.  “I’ve never heard these girls cheer before… wow!”  My Angry Jellybeans inspired the other team to do something they’d never done before.  Pretty awesome. 

Game done, and with a 2-0-0 record, Deanna and “Coach Ken” hopped in the car, hoping to go 3-0-0 on the day.

The Top 20 awards

We pulled in to the Old House at about 6:10, and I hopped out of the car still dressed in soccer pants and my Angry Jellybeans hoodie.  Working my way through the tuxedos and evening gowns I reached the front desk.  The clerk asks “Are you checking in”.  I nodded, and he pulls the last folio off the counter behind him.  “You must be Ken… you need to change!”

I laughed, but he was right.  We bolted to the room, changed superman style, and were out the door about 10 minutes later.

After a quick drive to the Filberg Centre, we parked, hoofed it up the stairs and… holy crap!  There was a red carpet rolled out for us… wasn’t expecting that!  I also wasn’t expecting to be given a glass of champagne as I walked in the door either… made me kind of feel like a movie star!

Because of the late arrival from the soccer tournament, we missed most of the mix and mingle time, but we still had time to chat with Russell (Fairwinds Asset Manager), his wife Mary, Jim (my boss) and Julie (Jim’s wife) who had all come out to support me.  A couple of drinks, some chatting about the day, and a little conversation with a couple of nice guys that rounded out our table of 8 made for a good wind down time from the crazy drive we’d just experienced to get there.

They also gave us a nice full colour book of all the nominees and their bios. We started flipping through them… which only helped to make me more nervous!

Next up were a few speeches including one from Premier Christy Clark, who had skipped her son’s hockey game to come and talk to the finalists before the awards.  She had a great speech that had the crowd laughing. Not political, which was nice, but rather focused on us.  You can see some of that in the Chek News coverage.  Next came dinner, then they got to the awards.

Each of the top 100 finalists was given a nice framed certificate to hang in their office.  Here’s a picture of Deanna and I (smartphone shot, so quality not great), with mine:

And then came the announcement that they were going to start announcing the Top 20… Russell slams his hand on the table “A hundred bucks on Ken!”

So here’s all this support around me, all this encouragement, all this faith… and me still trying to convince myself that it’s okay if I don’t make the Top 20.

And then it started to roll out, Oscars style.  They called Troy Wilson.  As he’s working his way to the stage they’re reading a more complete bio.  Troy makes his acceptance speech, and they call another name.  The process repeats and they call another, and another… and another…

And I’m telling myself “It’s okay.  Top 100 of all under 40’s on the Island is awesome.”

… and another, and another…

“You don’t have to WIN everything you know…”

… and another …

“It’s a hell of an achievement, be proud!”

… and “KEN PULS!”…

The words that came out of my mouth at that moment were deep and profoundly inspired…. “Holy Shit.”

I won.

I can actually say that I was a bit in shock.  But I managed to get up there and put together a speech on the fly that I’m pretty happy with.  You can see it here:

And then something remarkably cool happened.  As I was heading back to my table to show off the trophy a young guy stops me… I can’t remember the exact words, but it went something like this:

“Ken, I just wanted to tell you that I took one of your Excel courses a couple of years ago.  You really inspired me, and I decided to pursue my accounting designation.  I’m now enrolled and pursuing my CMA.  I’m a huge fan of your website and spend a lot of time there too.  I just wanted to say thank you.”

To me, that is the ultimate compliment.  I was so stunned I never even got his name, but wow.  That was inspiring.  (If you read this, drop me a line!)

Then back to soccer…

After the awards were all presented we returned to the room to get some sleep, turning in around 12:30 AM.  The alarm was supposed to go off at 6:00 AM (which felt like 5:00 AM thanks to the daylight savings time change that night.)  Thankfully Dee woke up right at 6:00 as neither my phone nor hers actually went off.  45 minutes later we were back on the road heading back to Victoria for the final Angry Jellybeans game vs the Gorge FC host club.

When the girls found out that I had won they all surrounded me and gave me a great big Angry Jellybeans cheer, which was really touching.  We then had a chat about our goals for the final game, which ended up with the girls deciding that they were going to “win it for Coach Ken”.

My girls are a talented team, and they play hard.  But what I saw on the field on Sunday was remarkable.  They did everything that Scott and I had coached them to do all season long.  They spread out, they were aggressive, they run into open space, they passed to each other, they connected and they scored… and scored… and scored…  At 6-0 Scott and I were just about to implement our “3 passes before shooting rule” when they scored again.  So in the rule went… and they scored again… and again…  By half time it was 9-0.

We came back on after the half with the girls very clear that they had to connect 5 passes before they were allowed to shoot on net.  It was actually great.  There is nothing more amusing that seeing the look on a parents face when one of your players is standing in front of an open net, and they turn and pass the ball because they’ve only completed 3 passes of the required 5. The Gorge girls also spread out and got more aggressive.  Because of that they did manage to score a goal as well, which was great.  The final score ended up with an 11-1 win for us.

Again though, my girls led a 1 hour cheering session through the game.  And just after half time Gorge responded.  Out of nowhere I hear one of my players say “they just cheered us back… come on, let’s have a cheer off!”  And sure as anything they did.  They’d cheer, wait for the other side to respond, and launch into a follow up… over and over again.  It was such a weird counter-point… we were creaming them on the field, and they were still having FUN.  And that is just plain awesome.

My only regret the whole weekend is that I let my girls run the score up as high as they did before putting in the passing rules.  I never want to beat a team by 10 goals, as that is disheartening for anyone.  I want these girls, no matter the team, to play soccer year after year.  But at the end of the day I can’t help by feel that this time the girls on the other side had fun anyway.  They were all smiles when they shook our hands, and the coaches from the other side thanked me for my actions in stopping the slaughter while still letting them play.  It really felt like a win-win.

I can’t begin to say how proud I am of my Angry Jellybeans.  They played amazing, and they inspired a bunch of kids to have a great time and have fun in ways that their coaches haven’t seen before.  It was an inspiring end to a great tournament, and a great weekend.

And in the end…

On Saturday night I became part of a very exclusive club… one I’m proud, honoured, humbled, and flattered to be a part of. (As this was the first year of the event, there are only 20 members to date.) What’s amazing is that I never pursued this, I just did what I do. With my Excel stuff it’s what I love to do, with my other stuff what I feel that I need to do, but regardless, I was recognized for just being me, and that is pretty cool.

And on Sunday I saw the culmination of something very cool in my amazing Angry Jellybeans.  Not only the wins, but the fact that they inspired others in ways that seems like it could only come through some kind of magic.

To all of those of you have liked my Facebook statuses, LinkedIn updates, tweeted me and expressed congratulations on my award through any other manner, Thank You. It means a great deal to me.  I truly feel like a lucky man right now. 

Just a quick reminder if you’ve been sitting on the fence… Chandoo’s PowerPivot online course registration closes in just over 12 hours (midnight Pacific Time, 2013-02-15).

Don’t get left behind… Click the image below to sign up!

No really, that is it’s name!

I was trying to figure out why pressing F4 wasn’t toggling from relative to absolute referencing in my Excel formulas, and was starting to think I found a bug in Excel 2013.  But then other stuff started to get weird too…

• F4, which I use to toggle absolute and relative formulas all the time seemed to do nothing.  But when I was just in a worksheet, and not the formula, it opened a new workbook.
• F2, which I use to get into formula editing mode, started undoing my last action… which is really scary as I still don’t know if I un-did anything important. I only actually realized this after I typed a formula, clicked on it, pressed F2 and it went away!
• Alt F11, to get in the visual basic editor did nothing at all.  What the…?

After finding out that it was doing the same thing in a new workbook in Excel 2010, I realized that this was bigger than just Excel.

Apparently I fat-fingered the F Lock key on my (Microsoft Natural) keyboard, which tells the keyboard to start using alternate commands.

Gave me a bit of a heart attack on a Friday afternoon!

I blogged earlier this week about Chandoo’s new PowerPivot course, where I’m a guest lecturer.  Well guess what… I’m everywhere!

This morning Rob Collie published my first guest post on his blog, on the topic of Determining the Effective Tax Rate using PowerPivot.

I don’t think it’s a secret to anyone who reads this blog that I’m a big fan of PowerPivot.  It’s got some huge capabilities, and I’m convinced that it will be a game changer in the BI landscape.

The challenge though, is that PowerPivot isn’t just a “pick it up, it’s easy” kind of thing.  Take it from an Excel pro, you’re going to need some help to get up to speed with it and really make it sing.  Well good news… Chandoo is releasing the first online PowerPivot course, and registration starts now!

Here’s the details of what Chandoo is going to cover:

What is in this course:
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from the familiar Excel interface. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Who should sign up for this course?
This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.
Please note that you should be familiar with Excel & Pivot Tables and running at least Excel 2010 to enjoy this course.

Important Dates:
- Course registration opens on – 6 Feb 2013
- Registration closes on – 15 Feb 2013
- Classes begin on – 18 Feb 2013
- Classes end on – 31 May 2013
- Online access is valid until – 18 August 2013

The total cost is $247, and you also get a bonus copy of Rob’s DAX eBook (which you’re going to need).

Chandoo is no stranger to online training, having run his online Excel School for some time now.  But what makes this one even better is that, not only does it cover all the awesome content you need to get up to speed, it also includes a couple of guest lectures by some pretty cool people.  One is Rob Collie of www.powerpivotpro.com and the other is… me!  (I’m really looking forward to being a part of this!)

So what are you waiting for?  Click the image below to sign up!

This past weekend was a good one for Excelguru.

On Thursday evening we travelled over to Vancouver and stayed at the Westin Grand, downtown. I have to say that I quite enjoy the Westin hotels, and this one was no exception. Exceptional service, clean and comfortable room, and central to everywhere that we needed to be, which was awesome.

Friday morning we were up and off to Blink Media Works to shoot 3 more Excel videos for the Certified General Accountant’s PD Net. I shoot all the Excel specific on-screen footage in the Excelguru offices, but I’m on screen in full HD when going through PowerPoint slides doing introductions and wrap-ups for each of the examples. It’s always an interesting thing being on camera, as it’s not forgiving at all. Unlike a live audience where, if you make a mistake you can just laugh it off, you can’t do that. Naturally, with 3 videos being done, there’s no way to memorize all the material (it would detract from the “realness” even if we did, I think), so I’ve got bullet point notes for each slide. Forgot one, reshoot. Forget the points you want to make, reshoot. Stumble over a couple of words, reshoot. It’s actually pretty hard work!

Regardless, we managed to get through all 3 in one day, which was awesome. In my impression it was the most comfortable, relaxed and on-key shoot we’ve had yet. What I didn’t count on though, was how dehydrated I felt after the shoot was over. Even though I was drinking water all day, I guess the lights and talking cause you to lose a LOT of fluids. (I pretty much spent most of Friday night pounding down the water to get re-hydrated for Saturday’s live course.)

When we broke out of the shoot the sun was shining, so we decided to walk back to the hotel. That was a really nice way to stretch the legs, get a little exercise and chat about the experience. We also took the opportunity of being right downtown to walk to the Keg in Yaletown for dinner. This may seem weird, but things aren’t all laid out in walking distance in Nanaimo, so this was a bit of a novelty to us.

Saturday morning was a bit drizzly, so we cabbed it down to the Terminal City Club for that morning’s session on Data Cleanup and Summarization, hosted by the Certified Management Accountants. I always enjoy teaching there too. The staff are fantastic and easy to work with, the facilities are always impeccable, and it just feels good to be in such a classy place. J

I had ten people for the course on Saturday, who seemed to enjoy the material. As I normally plan my hands on courses for about 20, we ended up going off topic a few times, as we had time to follow some topics I hadn’t planned on covering. We also ended up with about 45 minutes of great questions and discussions at the end of the session as well. I always love that, as it really makes me think and approach things from angles I might not be used to. The best part of teaching any of these sessions though, bar none, is when the attendees leave excited about Excel and wanting to learn more about different areas that they haven’t worked with. Very cool stuff!

About a week before the session, a colleague had also invited us out to lunch. I’d never actually met Tony before, so it was great to go and chat with him.

Tony and I have very similar backgrounds as we’re both Certified Management Accountants, and we’re both very solidly branched into the IT field as well. Our discussions primarily revolved around how integrated the roles of IT and accounting are becoming, yet how most people don’t seem to recognize that combined value for some reason. When you see job postings it’s generally for an accountant OR a systems person, but not a hybrid of both. While that may make sense for large businesses, especially since both roles are complicated and technical in their own ways, for a small business it’s important to have an IT person that understands the impact of their decisions on the overall business process and cycle, and an accountant that understands the benefits and drawbacks of the current and future IT enhancement plans.

Having said that, if you’re looking for IT work, and want it done by someone who understands the overall impact to your bottom line, Tony runs a company called Abakox Solutions Inc, and that’s what they do. J

All in all, it was a great weekend. I met a lot of great people and got to talk about Excel in depth. Tough to beat that!

I hope your weekend was a good to you!

It’s been crazy busy over here the past couple of weeks.  I’ve been hard at work creating 3 training videos for a client, (we shoot in Studio on Friday,) preparing for my next live course (this Saturday,) and trying to keep the forum posts all answered… among other things…

Excelguru has a new host

The biggest news, as far as the site is concerned though, is that we’re running on a new host.  My former host kept sending me messages about the server usage, asking me to block users and shut things down, as I was too busy.  They suggested that, if I didn’t want to do that, I should upgrade to their dedicated server plan.  I have to say I was a little shocked by that, especially when they told me it would move me from $4.95 per month to $164.95 per month.  While I love hosting the forums and site, that was a little much to swallow.

Instead, after some research and conversations with my friend Simon, I’ve moved the site to host with URLJet, a company that specializes in vBulletin and WordPress hosting.  They’re more expensive than what I was paying, certainly, but what a difference in service.  I signed up and within 10 minutes they called me to talk through the migration scenarios.  The site migration was finished, tested and working within 4 hours, all done by them.  They reviewd it, made some performance tuning changes, and recommended a few for me to do too.  Very cool, and all done at no extra cost.  I have to say that so far I’m really pleased with them.

We’ve been running on URLJet for a week now, and the site seems more responsive and forum posts are picking up.  I’m not sure if that’s coincidence (probably), but it’s getting to the point where I could certainly use more people on the site.  So if you love Excel or just want to learn, we have posts that need answering! 

The old blog is back!

As if moving my site and optimizing it for me weren’t enough already, URLJet also fixed the WordPress blog that I’ve been locked out of for 1.5 years too!  While I was planning on using the vBulletin blog, WordPress is way more flexible, and I didn’t want to lose all the old content so…

With this blog back live, I’m going to resume posting here.  I ported the two articles back from the vBulletin blog that were worth saving, so if you see those in your feed, just ignore them. 

A new article

In celebration of the whole thing, I also created a nice new article on the site as well.  If you’ve ever been confused about how conditional formatting rules are applied in Excel 2007+, I’ve written up a VERY detailed, step by step article that will walk you through exactly what happens.  You can find that by clicking here.

Facebook contest

My facebook page at http://www.facebook.com/xlguru is creeping ever closer to 100 likes.  I’d really like to make that milestone, so I announced last week that I’d give away a copy of my Magic of PivotTables video course to the 100th like.

I have to admit that I was hoping I’d be there by now, but then I got the following comment from Rick Grantham on Twitter:

“guess I need to unlike your FB page so I can like it back at the right moment.  Maybe I shouldn’t have told you that ”

I had a good laugh there, but it did make me wonder… so I think I’ll change the game up.  (All those of you who are already Facebook fans can thank Rick (@BIStrategyGuy) for this….)  Here’s the new deal:

Be one of the first 100 likes on the xlguru page for a chance to win a free copy of my Magic of PivotTables video course!

Once I hit 100 likes, I’ll draw a random name from the first 100 people, and then I’ll send you a discount code to download a free copy.  It’s just that easy. 

See, now no reason to wait.  In fact, you might want to hurry, as there are 97 likes as I post this!  (But don’t worry, this won’t be the last one.)

As many readers of this blog will know, I am a huge fan of PowerPivot, and honestly believe that this is THE most important feature to hit Excel since VBA was introduced to the product. And anyone who has ever taken a course from me knows that VBA is the greatest thing to be added to Excel since the grid was mapped into rows and columns.

For those who don’t know, PowerPivot was a free add-in to Microsoft Excel 2010, and is now baked into the Excel 2013 release. It basically allows us to pull data from different sources (multiple databases, text files, web data feeds, excel worksheets and more) into a separate layer of our file, and create relationships between the resulting tables. Basically you can aggregate data from a bunch of different sources and EASILY aggregate it into your very own business intelligence engine.

The amazingly cool part is that it’s not hard to do this. With a little knowledge about relational data (and I mean a little), and knowledge of text functions like these, you can build the columns necessary to relate your data. From there you create the table relationship (PowerPivot even tells you if you try to build it backwards), and you’re off to the races.

The results of this are, quite frankly, groundbreaking. You can then build PivotTables off this data: PivotTables that are sourced from multiple tables at once. No more massive VLOOKUP tables to build one huge data table with everything. It’s simply no longer necessary. And with the variety of sources, you can even pull in and start adding items to your pivots that you never thought of before, like the weather for example. Does your corporate database have that? I doubt it, but who cares? Just source a weather feed from the internet, relate the dates to the dates in your sales table and presto! You can now see how sales were on the sunny days in a month vs the cloudy ones.

It’s amazingly easy to get some cool stuff out of PowerPivot… at least… to a point. To over-simplify things, I’d say that PowerPivot allows you to do four overall things:

1. You can source and relate tables to build your own aggregated data source (a mini BI database if you like.)
2. You can build PivotTables pulling in fields from multiple tables.
3. You can build OLAP formulas, allowing you to pull data for a specific element right into the worksheet, without building a whole PivotTable to do it.
4. You can build super duper wickedly complex and powerful calculated fields for your PivotTables using DAX.

To me, the first three were easy. As an Excel pro with a bit of relational database knowledge, I’ve been able to work through the first 3 over the past couple of years with very little in the way of roadblocks. And by doing so, I’ve built some amazingly cool things for our company. Yet despite this, I’ve always been aware that the real piece de resistance, the part that really makes things sing in PowerPivot is DAX.

What the heck is DAX? It stands for Data Analysis eXpressions, and is the formula language that you use to create what PivotTable users know as “Calculated Fields”. It is unbelievably powerful but, without tutelage, it is unbelievably hard. For almost two years I’ve been struggling to understand DAX.

That ended this past week.

Last week I bought a bought Rob Collie’s new book “DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. When you are ready to take the DAX journey, you MUST have this book.
Rob’s writing style is identical to that you’ll find on his blog at http://www.powerpivotpro.com/. In fact, the only difference is that he doesn’t fill up any real estate with movie quotes. What you do get is a huge amount of insightful information.

I can honestly say that, despite being a fairly accomplished Excel pro, I have been unable to wrap my arms around DAX. But that is over. Four days of working through this book with my own data and I am now making some damn cool stuff that eluded me before.

What I love about this book is that Rob sat down and carefully thought through how an Excel pro should learn DAX, then started at the beginning. It progresses logically, is very easy to read, and actually gets you there. Yes, you need to practice, but weren’t you going to do that anyway? And when you practice…

There’s something really important between these pages that I doubt you’ll ever see in the advertising. With any programming language (yes, Excel formulas fit that bill, as do DAX formulas) there are two critical things to learn. The first is Syntax (how you do something), and the second is how to debug something. Rob gives you both, which is critical on the path to mastery. He has some nice little charts that explain how filters are applied to the CALCULATE function, (which is like SUMIF on steroids.) With those charts, you can actually follow the steps to figure out exactly why your measure is not doing what you want. (Trust me, it was doing this that actually helped me finally get that Eureka moment that I’ve been looking for!)

Four days. I’ve gone from not being able to make anything but the most simple DAX measure to the point where I can create some pretty cool CALCULATE functions (my new best friend), with nested date/time intelligent functions as well. I’ve been pursuing that for TWO YEARS!

In an hour this morning, I was able to build a nice little “Server Contest” dashboard for our food and beverage department. It has slicers to drive down into sales areas, major groups (entrees vs desserts), and dates. And it will correctly report the week-to-date, month-to-date and prior weekly sales in units, as at the selected point in time. (Previously, I was able to get the “effective date” to work, and my “current month” to work, but I could never figure out how to filter things to give me the correct month to date number if I clicked an earlier date. It always kept returning the real month to date, rather than the effective month to date.)

The dashboard is a beautiful thing (to us anyway), that we’ve never been able to produce until today:

After struggling with DAX for so long, I think it’s fair to say that I wouldn’t have been able to break through this without Rob’s help. I certainly wouldn’t have been able to build the report above in such a short time.

If you’re struggling with DAX, or just getting ready to start the journey get his book. You owe it to yourself.

You can pick it up from Amazon.ca (for all my Canadian colleagues) by clicking here. If you’d prefer to deal with Amazon.com, you can get it by clicking here. And/or from other routes as detailed on Rob’s Website.

I’ve been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

• Connect to a view or table, pulling in ALL columns
• Work through my data scenario until I’ve worked out my logic and solve the issue I’m trying to solve
• Cut the table/view query down to just the required columns
• Add WHERE clauses to cut the data down to just the bare minimum I need
• Deploy to my users

By doing all this, I really focus on trying to optimize the file size and refresh time as much as possible.

As I do this, I cut the number of columns out, then flip from the GUI table view to SQL to add my WHERE clause(s). One thing that I find that really sucks though, is that when I do flip the “Table Properties” to SQL view, it comes out like this:

I would LOVE to see it come out more like this by default:

It would sure make it a LOT easier to read, and a LOT easier to work with.As it is, I now copy my code out of PowerPivot, then head over to Instant SQL Formatter.  Copy, paste, format, copy and paste it back into PowerPivot.  A heck of a lot easier to read, but also an unnecessary pain in the behind.

Hi Folks,

I’ve been busy and not able to blog much lately, but I just go an email from Chandoo that I thought I’d share.

Chandoo is taking another round of students for his VBA classes. I’ve heard very good things about it. Here’s what Chandoo has to say:

So far, we have enrolled 180 students in to this batch. We are eager to enroll as many more as possible during next one day.

We will be closing enrollments for this on September 16 by 12 Midnight Pacific Time. Please tell your subscribers & readers about this so that they can join in time.

The course comes in 4 flavors:

• VBA Classes with Online Access – $97
  
• VBA Classes with Downloadable Access – $147
  
• Excel School + VBA Classes – $247
  
• Excel School + Dashboards + VBA Classes – $347
  

You can sign up for one of this classes by clicking this link.

(FYI, I’m hoping to be back blogging and posting again soon… things have been – well actually… continue to be – crazy! But I should be able to carve some time out of my schedule again shortly.)