EXCEL POWER USER QUICK GUIDES
by Szilvia Juhasz
"Laminate Worthy" Quick Guides for the Excel Power User. Includes:
- Shortcuts & Timesavers for the Power User
- Quick Reference Guide for Lookups and Conditional Calculations
Rick: Welcome to our special guest straight out of Canada, Mr. Ken Puls of excelGuru.ca. I first became aware of Ken a year and a half ago when I was stalking his Twitter feed and Facebook page. He was about to get 100 Facebook page likes and he said whoever is my hundredth like I am going to give away a pivot course.
Ken: So Rick unliked my page and waited eight hours so he could be the hundredth person to like the page.
Rick: Ken finally said I wasn’t the hundredth and even if I was, it was kind of cheesy because I was stalking him for eight hours. Say hello to everyone, Ken.
Ken: Hello, my name is Ken Puls. I live and work up in Canada in God’s country on Vancouver Island. My day job is controller and director of IT at a resort. And in the evenings and weekends, I put on my superhero cape and do Excel work. I spend my time writing website articles and teaching how to use Excel better. Fun stuff.
Jordan: Tell us about ExcelGuru?
Ken: What specifically? It all started as a hobby, believe it or not. Back in 2000, when I started working at the resort, I was trying to figure out how to get some things done and I stumbled onto VBA. I started teaching myself and I ran into a problem and couldn’t figure some things out. Our head office came in and cut our staff by a third. We needed to get more work done with less staff. I was the guy in the office who knew most and the only way was to get more done was to learn VBA. So I spent a lot of time in forums. I felt guilty about all the knowledge I was stealing from these groups for free so I decided I needed to give back to the community and reinvest. I built a website to show some of my samples. That’s where it started.
Rick: What is the top 30 under 30 award?
Ken: It was the 20 under 40. I’m not under 40 anymore either. I submitted an inch of stuff I’ve done so clearly it was awarded by weight! Not to cheapen it, they were looking at what people have done and how they’ve helped people in the local community in different areas. I like to think that I’m the only person on the list who can say they helped someone with their business sending things out of our atmosphere. I have an example where NASA used some of my code to monitor power levels on the space station. Pretty cool.
Rick: I noticed that it’s hard for me to go to a training site without hearing your voice.
Ken: Good. Excellent.
Rick: Weird. I look at the credits and there is Ken Puls. What training do you deliver on the web?
Ken: Not nearly enough. I have done a curriculum with certified general accountants up here through their website where we go into a professional studio with hair, lights and make up. I built a curriculum for a company in Australia called Go Skills. There are about 36 videos of 5 to 10 minutes each. I have my own Pivot Table video and some YouTube video tips. I’ve lost count. I’m showing up everywhere.
Rick: Particularly the Go Skills stuff. This guy is everywhere.
Ken: I enjoy doing that.
Oz: What kind of things go into building a curriculum?
Ken: Blood, sweat, and tears.
Oz: What kind of questions would you ask and how do you set yourself up so you can deliver?
Ken: Basically, I approach it either feature first like how do I teach them how to use features or building a curriculum that teaches going Point A to Point B on a specific topic. For instance, in order to use pivot tables, we have to teach people how to clean up their data, then build tables, and then we’ll build pivot tables. If we take a broad brush course for Excel, we are going to focus on general stuff. Different things are going to different courses. Personally, I like to have good examples when I teach. People really resonate with examples. Techniques are good, but I need to see how to put it into practice. For me, I like to use the example of liquor store stats; beer, wine or cider. When you start using alcohol, people set up and listen…as Rick drinks his beer.
Jordan: I had to develop a curriculum and there is always this tension between what I want to put in and who’s producing it. I want to put in too much. Is there ever tension with what you want to cover? What do you do about that?
Ken: I have been lucky about that. The groups that approached me have been tolerant. They have asked what it is going to take and how much time will it need. One of the curriculum we build is 10 courses for CGA with more in the pipeline. I’m still hopeful it will all be completed. There are forty to fifty 1-2 hour courses in the pipeline. Go Skills had a different target. The focus was five minutes videos of different topics. When I wrote the Ribbon X book (RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig) with Robert Martin he named me “verbose man.” You have to scope back. There is can-do, absolutely have to do, and stuff that falls on the cutting room floor.
Oz: It is disappointing to me and students when I’ve packed so much there’s very little room for questions. I’m OK with leaving some stuff out.
Ken: It depends on the medium you’re using. When you are recording videos for someone, you don’t have the opportunity to do questions. It is totally different. I love live training. When I teach, it turns into an eight hour course. Things slow down and people are asking questions. When you start getting those questions back, it brings up other topics. It adds flavor to the live training.
Oz: Immediate feedback is good.
Ken: The tough part is when you record video, you set up a script. But when you teach live, you know when they get lost. Last week, I did a webinar and I’m speaking to them live through screen sharing and I can’t see them so it’s tough because you don’t know what is going on. The most difficult to teach is in a webcast. I still enjoy it. But if I’m learning, I want to be in a classroom with instructor or with a book instead of webcast.
Oz: I like sitting in a classroom or with a paper book. I don’t like putting the question in that chat box and getting a half-assed answer.
Rick: When you started ExcelGuru, you said this was an effort to give back. Can you talk about the evolution of ExcelGuru? It was a big jump to create a forum. How did that developed for you?
Ken: Originally it was four pages when it started. I bought my own domain and had a front page. I tried to teach myself how to write HTML, but that’s not where I want to be. The deal was how do I get content out there? I put up four examples like using VBA to move data to access etc. and as time went on, I started to add things. But HTML was too hard so I found an open source content management system. Then I could focus on writing my content instead of coding my content. I added a WordPress blog. I wanted to have a place where I could throw some random content or rants. I built the blog and attached it to the site. This was all done on a very cheap Linux hosted program. I used open source software and spent about two dollars a month. I eventually decided to start my own forum when I had 60 to 70 items. Someone at Excel MVP said just do it. So I added the forum. Baby steps! I do worse at selling stuff on my website, which I should be doing. I seem to spend a lot of time working for other people than working for myself. It has evolved over time. We’ve been around for 10 to 12 years. I see people that started with a free blog from WordPress and they’re doing great. This huge mega thing called the Internet makes it easy now.
Rick: What were your biggest steps going to the forum?
Ken: I’ve been lucky. The website was an interesting step because I had to learn HTML. When I added the blog, that was another learning journey. The whole time I’m figuring this out, I am learning about Excel. The forum was a huge step because that was the first piece of software that I had to pay a licensing fee for. I installed it and it took a ton of time to get it set up, configured and working the way I wanted it. I’ll be out front and say that if it weren’t for the people there posting and volunteering their time to answer questions, it would be a shadow of what it is today. I’m very thankful for everyone who contributes. I wish I had more time to contribute myself. There’s a good core group of people that do a ton of work on the forum. Huge shout out to the moderators. When we got to the forum, software was the linchpin. I realized that learning and programming web language was not my forte. I love business intelligence and VBA and Power Query and Excel formulas. I have had to learn many other languages, because I’ve been too cheap to pay other people to do it.
Ken: I think it’s good. Microsoft’s role going forward is shifting to web content from the desktop. What I tell people is the role of Excel business intelligence report developers is changing. We are becoming programmers. We are going to write our solutions on desktop in Excel. The dream is that it will just work; integration with SkyDrive, push to Power BI. I want to publish into a web platform so somebody can be on the beach in Mexico reading it on their mobile device and interact. The challenge is that my data is stored in my SQL server and not accessible. Now with Power BI coming out, they fix that data connector allowing you to build your data connector locally that pushes it up to a Power BI site. That is starting. Right now, I send my reports out with automated PDF’s, so if it doesn’t happen I’ll continue that. I don’t think the world is going to revolve around the web.
Oz: Why do you say that?
Ken: Some people have a concern about internet connectivity. For example, someone said they have concerns about refreshing because they have no Internet connectivity in Africa. I have to be able to have my stand-alone solutions. So that is a concern for people.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.
"Laminate Worthy" Quick Guides for the Excel Power User. Includes: