Learn Excel

We now accept the fact that learning is a lifelong process of keeping abreast of change. And the most pressing task is to teach people how to learn. – Peter Drucker

I probably don’t need to tell you that learning Excel can be hard.

Taking a consistent and focused approach can pay dividends, no matter where you are now, where you came from, or what you know.

That’s why I’ve put together this site – to help you learn how to use Excel better. Scroll down to read articles on learning Excel.

And check out these other resources too:

Excel Topics

  • Formulas and Functions
  • Excel Charts
  • Excel Dashboards
  • Macros and VBA
  • Excel Tips and Productivity
  • Product Reviews

Excel VLOOKUP Formula Examples – Including how to create dynamic vlookup formulas

[Image] VLOOKUP Part 2In 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.

Image Credits:
CGI waves courtesy of “gerard79“.
Abstract circle shape courtesy of “iprole“.

Do you want to become an Excel Hero?

[Image] Excel Hero Academy Logo
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.

Discover a simple way to understand how VLOOKUP formulas work in Excel

[IMAGE] VLOOKUP Part 1If you are struggling to understand VLOOKUP formulas or want to explain VLOOKUPs to someone else who is struggling with VLOOKUPs, this article is for you!

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.

Image Credits:
CGI waves courtesy of “gerard79“.
Abstract circle shape courtesy of “iprole“.

Learn how to create awesome Excel Dashboards with MyOnlineTrainingHub [Course Review]

[Image] Video review of MOTH dashboard course
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.

Are you using Excel in first gear?

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

How to use Drop down menus to make Interactive Charts and Dashboards in Excel

[Image] Drop downs for Dashboards

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.

Excel Demo showing how to create a Panorama with Google Maps Street View Images

[Image] Royal Albert Hall
Google Maps has become a very popular tool for finding addresses and directions. I use it pretty much every time I want to find driving directions to a new address. But did you know that it’s possible to export Google Maps and Street View images from the Google Maps service and show them in an Excel workbook?

Getting Google Maps Images into Excel opens up the possibility of creating simple applications that combine the different types of maps onto one screen. In our 5 minute video you’ll see a short demonstration of a spreadsheet that can generate a 360º Street View panorama by combining 4 images of the chosen location.

You can download the workbook to play around with the demo then see how it’s been coded in VBA. And if you tweak the code enough you be able to create a 360º panorama featuring the Royal Albert Hall that looks ‘almost’ as good as the photo above.

Image Credit: Royal Albert Hall by Amanda Slater (licensed under Creative Commons CC BY-SA 2.0)

Working with Data in Excel Part 2: Fine Techniques for Excel Lists, Drop Downs and Data Validation

In Part 2 of this series we’ll look at Excel lists, drop downs and data validation. These are very important areas of Excel that you should master if you want to take your Excel skills to the next level.

If you missed Part 1 you can read it here: Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know

In Part 2 you’ll find handy tips and techniques for speeding up data entry, as well as making sure that data is entered accurately. I’ll also show you a trick for quickly defining multiple Named Ranges very quickly, and explain how Data Validation in Excel can be easily bypassed – and what you can do about it.

[Image] Working with Data Part 2

I have divided the techniques into four different sections:

  1. Excel Built-in Lists – What are they and how to use them
  2. Excel Custom Lists – 3 Practical Use Cases
  3. How to Create Drop Down lists for Data entry using Data Validation
  4. Data Validation of E-mail address and Date of Birth

There is a downloadable workbook with worked examples that you can use to follow along. Download it here and get ready to learn some useful Excel techniques for lists, drop downs and data validation!

Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know

In this article we’ll look at data entry and I’ll show you some important tips and shortcuts that everyone using Excel should learn.

You’ll find these data entry tips handy whether you work with short to-do lists, stacks of survey results or a prolific pile of personnel data.

[Image] Working with Data Part 1

I will focus on four specific areas:

  1. How to use shortcut keys to make data entry faster
  2. Get Excel to speak what you enter to stop you making mistakes
  3. Use the Excel data entry form to make data entry easier
  4. 3 different ways to keep your row headers visible when scrolling down

These 10 tips will help you do data entry faster and more accurately so you have time to do things that are more fun and fulfilling! Read on to discover our Top 10 Excel Data Entry tips.

Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel

Mouse Power TipsTricks in Excel – everyone should learn some. I’ve used Excel for over 10 years and some of the coolest tricks I know are the simplest to learn.

Little tricks can help you become a more powerful Excel user as they simplify and speed up your work. And there’s a delicious range of mouse tricks I’d like you to discover today.

You might say to yourself “Surely I know how to use the mouse in Excel, everyone knows. Why do I need any more tips and tricks?”

I understand the feeling – but actually there are so many ways to really use the mouse effectively in Excel that probably 80% of users are not using the mouse as effectively as they could.

This includes people who love keyboard shortcuts (like me). It pays handsomely to know a couple of mouse tricks that will help you get the job done better or faster. So read on to find out our Top 20 tricks.

How do I stop Excel from resetting my custom number format when I update my Pivot Table?

Excel Pivot Tables are probably the single most powerful built-in feature of Excel. If you analyse lots of data, my guess is that Pivot Tables are one of your favorite tools. They are fast and flexible – they save you lots of time by allowing you to explore data. I often find that checking out…

The Ultimate Guide to Bullet Points in Excel

Would you like to know how to insert a bullet point in Excel? For a long time I thought there was only one way to do this – type a lower-case letter “l” in the font Wingdings to insert a round bullet point character. But now I know that Excel is such a versatile bit…

The Little Known Secrets of Adding Watermarks in Excel 2010

Many people ask the question “How can I add a watermark to Excel?” You know the kind of thing I mean if you’ve ever seen printouts marked “Secret” or “Confidential” or “Draft” in big bold letters splashed on every page at a 45 degree angle. Microsoft’s online help explains that “Watermark functionality is not available…

Give your Spreadsheets the Festive Makeover with a custom Background in Excel

Have you ever been secretly bored with the standard Excel grid? With its hundreds and thousands of empty cells staring right back at you? Did you know there is a way to change your Excel background and brighten up your day! I’ll show you two ways that you can use right now to change the…

What every Excel user needs to know about Macros and VBA in Excel

When you start using Excel you’ll probably do all your work manually because you haven’t found the powerful automation built right into Excel. It was the same for me when I first started using Excel. Entering data, copying and pasting results, formatting and charting – you name it, I did it all by hand. Years…

A Quick Way to Name Ranges and use Named Ranges in Formulas

In this 6 minute video I show you how to quickly name cell ranges with a powerful keyboard shortcut, and also show you how to use three math functions – SUMIF(), COUNTIF() and AVERAGEIF(). These are good building blocks that you’ll use in spreadsheet calculations and building spreadsheet models – I covered their friends SUM(),…

Dicover three basic math functions in Excel

In this 12 minute video I cover three math functions – SUM() COUNT() and AVERAGE(). These are pretty fundamental building blocks that you’ll use often in spreadsheet calculations and building spreadsheet models. If you have any trouble watching the Youtube video embedded above, you can view it directly on Youtube by clicking here. This will…

Excel Keyboard and Mouse Combos Part 3 (Accelerator Keys)

In part 2 of this series we looked at how you can use the keyboard and mouse together to move, copy and insert the cells you selected. In this third article I am going to show you how to use the right mouse button together with keyboard accelerator keys to speed up your workflow. Related…

Excel Keyboard and Mouse Combos Part 2 (Move, Copy, Insert)

In part 1 of this series I showed how you can use the keyboard and mouse together to select ranges and bring up the research pane. In this second article I am going to explain ways to use the keyboard and mouse together to move, copy and shift cells around a workbook by dragging the…

Pivot Table Tutorial: How to flatten a cross tab table

When you have a cross tab table you might want to flatten it to make it easier to analyze. Here is a 3 minute video to show you how to do this using Excel’s pivot table wizard to import multiple consolidation ranges. I also show you how to add the pivot table wizard to your…

Launch Excel is here to help you learn Excel. We provide you with resources to make the learning journey more fun and more effective. Read More About Launch Excel »
Learn how to create Excel dashboard reports.
Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.
Learn how to create Excel dashboards.
Excel Everest