A Bright Approach to Learning Excel
A Bright Approach to Learning Excel
If you are learning to use Microsoft Excel, this is a great place to to help you get better at using Excel. Join our private email list and learn how to:
Just enter your information to the right and click “Get free updates!”
In part 1 of this series we took a look at VLOOKUP formula basics. If you want to learn how a VLOOKUP formula works, or know someone else who is struggling with VLOOKUPs, check out the first article “Discover a simple way to understand how VLOOKUP formulas work in Excel“, and come back to this article.
Here in part 2, we have a 14 minute video that explains three different ways to write the VLOOKUP formula for exact matches. If you have never used the COLUMNS function or MATCH function with VLOOKUP, you are in for a treat because you will find out how to turn the standard VLOOKUP into a dynamic formula. As usual I have included a sample workbook with a table of fictional employee data for you to download and follow along with the examples.
What do super-heroes have in common? When I think about super-heroes a lot of comic book characters come to mind: Batman, Superman, Spiderman, the X-Men, the Fantastic Four, the Avengers… and what sets these guys apart from run of the mill folk like you and me is that they have special abilities and powers.
When called to fight the “bad guys”, superheroes swoop in to save the day with decisive action. But more than just taking action, because their unique abilities and powers allow them to do things that normal people can’t. Some super-heroes are born with their unique powers, others have their abilities thrust on them by accident, and some develop skills through years of training.
If I’m going to single out one super-hero to focus on here, it’s got to be Batman. Born a “normal” guy with no special abilities (just very rich parents), it took Bruce Wayne years of practice with extraordinary teachers to reach a level of mastery that allowed him to turn himself into Batman.
And just like Bruce Wayne’s path to becoming Batman, the path to becoming an Excel hero is not an instant transformation. It took years of dedication and mastery of the right skills and technology before the Batman emerged as the Dark Knight ready to save Gotham City from hordes of evil-doers. And yes, you can become an Excel Hero by taking a similar path.
Take your first step on the journey by reading on, and make sure you check out my Q&A session with Excel Hero Academy creator, Daniel Ferry.
Before we get started let me explain that VLOOKUP formulas are simple when you know how, but it can take a while to “get it”. A year or so after I started to learn Excel I stumbled across my first VLOOKUP formula. It was written by someone else, and I must have spent half an hour fiddling about with different inputs to figure out how it worked.
Back then the internet wasn’t quite so easy to search as it is today and I could not find any good online video tutorials, so I decided to look up Microsoft Excel’s Built-in Help on VLOOKUP. For a newcomer to Excel like I was back then, the instructions were hard to understand. It took me several months before I could write VLOOKUP formulas in my sleep.
Fast forward to the present day and I know that it’s easy to understand the VLOOKUP function when it’s explained onscreen using a good example. I really wish that someone explained the VLOOKUP function to me the way I’m going to explain it to you. It would have saved a lot of time and headache!
In our 13-minute video we explore a simple address book analogy to demystify VLOOKUPs and then check out how to use VLOOKUPs to perform exact matches. After that we examine the difference between exact matches and approximate matches and find out how to assign letter grades to students based on their percentage marks using the VLOOKUP function.
There is a lot of demand for Excel users who can create Dashboard reports, and I have received many emails from people all over the world who say they want to learn more about creating Dashboards in Excel. In my recent article on creating dropdowns for dashboards, I showed some simple techniques for creating interactive charts using dropdown menus in Excel. This just touches the surface of what is possible.
You probably know that to create awesome Excel Dashboard reports requires a thorough knowledge of many Excel techniques, but also principles of good visual design, and learning this practical knowledge takes a significant investment of time. If you have been looking for a course to teach you how to create your very own Excel Dashboards, then you should check out my review of the excellent online Dashboard course created by Mynda Treacy at MyOnlineTrainingHub.com.
Picture this scene on an interstate. You are standing in a rest area and hear a loud engine roar coming towards you. A powerful red sports car drives into view, revving really hard. It moves past you pretty slowly and continues to rev hard in first gear, so loud that you need to cover your ears to stop going deaf!
At this point you’re probably thinking to yourself “The driver doesn’t know how to drive that car!” as he continues to rev the engine hard in first gear, instead of changing up to second and third. Now keep that picture in mind as I ask the question “Are you using Excel in first gear?”
Let’s assume you know most of Excel’s menus and shortcuts, and are able to use many of Excel’s powerful features such as Pivot Tables and Charting. But if you don’t know anything about macros and VBA, your ability to use Excel is still pretty limited. You to do everything by hand, step by step. It’s like you can’t take Excel’s powerful engine into second or third gear.
Let me show you a quick video to introduce the Macro recorder in Excel 2010, and how we can use a simple recorded macro to change number formats quickly.
Image Credit: Racing car model courtesy of Buckey
Do you remember the story of Ali Baba and the 40 thieves? The poor woodcutter Ali Baba is in the forest one day collecting firewood, when he overhears a group of 40 thieves. They have come to hide their treasure in a secret cave. But this is no ordinary cave, as it only opens when the secret magic words are uttered.
Quietly hiding himself high in a tree, Ali Baba watches the thieves, loaded with glittering gold and shiny jewels, as they say the magic words “open sesame”. The entrance of the cave rolls wide open, the thieves enter the cave and unload all their treasure, then they close the cave again with the magic words “close sesame”. Ali Baba’s mouth hangs open as he imagines all the riches and priceless gems waiting inside that cave. His heartbeat quickens and his palms get sweaty, because now he knows the magic words to open the cave!
Did you know that just like Ali Baba, you have come across a secret cave filled with treasure – not any ordinary treasure but special “Excel” treasure. I’m sure that you will find the articles and links in this secret cave to be very valuable indeed! To enter this secret cave, all you need to do is say the magic words “I love Excel!”
Image Credit: Treasure Chest courtesy of chancey
Are you looking for ways to make your Excel spreadsheets interactive but you don’t want to use VBA? For instance, what if you want to create a simple Excel dashboard where you make selections from a drop down menu, and Excel immediately updates the dashboard charts without any programming or manual copy and paste to re-run the figures?
Let’s look at Excel’s built in drop down menus, which you can use as the basis for impressive and interactive charts and dashboards. We’ll see how you can create drop down menus with two methods: 1) Data Validation and 2) Combo Box Form controls. By changing the selection in your drop down menus you will drive automatic chart updates.
And here’s a bonus – you’re going to learn a neat trick to display an image that changes to match what you select in your drop down menus. This looks great when you want to spice up a dashboard with custom graphics.
We prepared a workbook with examples of each method. Download it here and get ready to learn about drop downs for interactive Excel charts.
“Power Tips for Microsoft Excel” is now out. After months of careful research and weeks of beta testing, we are happy to announce that you can download “Power Tips” today.
Look at the time you spend in Excel doing relatively simple repetitive tasks and you’ll understand that using Excel effectively will boost your productivity.
Power Tips for Microsoft Excel is an eBook and set of videos that helps you learn simple secrets of doing repetitive tasks faster in Excel. It’s based on more than 10 years of personal experience with Excel.
Today I’d like to thank every visitor who has come to Launch Excel in our first year. We reached a nice milestone this week with over 1,000 email subscribers who have joined to receive our regular emails.
Thank you everyone who has already subscribed! And if you haven’t subscribed yet we recommend you get on our list today because you’ll learn lots about Excel.
Image Credit: City People at Sunset courtesy of Laura Leavell
In this article I’m going to do a quick round-up of the Best of Excel on the Web.
This last month has been pretty intense as I’ve been working on the launch of “Excel Power Tips”. The Ebook and Videos should come out next month (fingers crossed). In the mean time check out the teaser video and sign up for a 50% early-bird discount before it’s too late by heading over to the Power Tips page.
Just because I’ve been busy doesn’t mean there hasn’t been great Excel content published elsewhere on the web.
So here are some of my favorite posts, articles and videos that I found in the last couple of weeks.
Check out the Products page where we have collected a valuable range of paid tutorials and templates to help you become an exceptional Excel user.