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!

I thought I actually posted about this some time back, but I don’t see it. It’s driven me nuts before, and is doing it again.

I have to print out our massive financial model for a meeting tomorrow. It’s almost 120 pages, and is laid out in a mix of colour and black and white, landscape and portrait settings.

The first time I ever saw this I was floored, as I thought Excel was turning each page just to drive me nuts. As it happens, it’s a little more predictable (although no less annoying.)

When I print my model, here’s the output of the pages:

Colour pages (landscape):

Colour pages (portrait):

Okay, so no biggie. I’d rather have the top of the colour landscape pages on the left so I didn’t need to turn them before putting in a left hand binding, but at least it’s consistent. At least, it is until I start mixing in some non-colour worksheets:

Black & white (landscape):

Rotated 180 degrees from the colour worksheet! What the heck is that about? (Yes, Top should be written the other way up, I just wanted this to be easy to read.)

Black & white (portrait):

Seriously? 180 degree rotation from colour again?

Run that out for a bit…

So now picture that you’ve got 120 pages, mixed colour and black and white, mixed landscape and portrait. I need to bind at the left, and want the top of the pages to be consistently like the colour landscape and portrait black and white, so the I can bind them on the left side. I’m going dizzy trying to sort this out!

There’s no setting to change this that I can find, so it’s a process of going through each pile of paper, one sheet at a time, to make sure none get missed. What a waste of time!

I’m not sure if this is a printer thing, or an Excel thing. It did it in Excel 2010, and is doing it in 2013.

I’m curious, if you have a colour printer, do you see similar effects?

This drives me crazy. I’m not sure if it was the move to Excel 2013, or if it was something else…

I have this massive model, and we print it out on 11×17 paper. Somewhere, sometime, Excel decided that this is a “Custom page size”, which causes me problems. I need to reset all the pages to 11×17. Easy right? Select all the worksheets, go to Page Layout à Size and choose 11×17.

Not so fast… if you do that, it replicates ALL the print settings including orientation, margins, fit to x pages by x pages, etc.. Nasty stuff. You can actually see why when you record a macro to change the paper size. This is what I get:

This is what I need:

Actually, even more pointed, this will fix it without messing all my other settings up:

I know why the code records as it does, as Excel doesn’t know what settings I truly need, so it records the current state of all PageSetup stuff. But I sure wish when I tried to update one print setting via the user interface that it gave me the ONE I changed, not everything.

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.)