Microsoft Excel Essentials: Level 2 - Intermediate/Advanced
4.3 (25 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,159 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel Essentials: Level 2 - Intermediate/Advanced to your Wishlist.

Add to Wishlist

Microsoft Excel Essentials: Level 2 - Intermediate/Advanced

Excel: From User To Super-User - Use Excel Like The Pro's, Easily, And Step-By-Step - Excel 2010 Excel 2013 Excel 2016
4.3 (25 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,159 students enrolled
Created by Alan Jarvis
Last updated 8/2017
Curiosity Sale
Current price: $10 Original price: $100 Discount: 90% off
30-Day Money-Back Guarantee
  • 7.5 hours on-demand video
  • 22 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn To Use Excel Like The Professionals
  • Get Streets Ahead Of Your Competition
  • Build Powerful Advanced Excel Projects From Scratch
  • Create a fully functioning relational database using Excel
  • Create a simple data entry screen to auto-populate complex templates
  • Expoit Excels built in advanced functions to do things "regular" users think are impossible!
  • Build Gantt charts in Excel for project planing
View Curriculum
  • An understanding of the basics of Excel
  • All you need is a PC, a copy of Excel 2007, 2010, 2013 or 2016, and a willingness to learn!

Over 15,000 students in 162 countries are becoming Excel masters with THIS series! Check out the FREE PREVIEW LECTURES and SEE WHY!

THIS course will take your Excel skills to the next level!

  • New "Student Questions Answered" added
  • The HIGHEST RATED Excel Level 2 course on Udemy!
  • Average minutes viewed almost triple that of other Microsoft courses!

So, you have an understanding of the basics, but would you like to use Excel like the professionals? If so, read on…

For students who have already completed Level 1 in this series, or already have a good basic understanding of Excel, what you will learn in this course will supercharge your skills... and your career.


“…very easy to understand and is clear and concise, I highly recommend both of his courses, they have helped me so much at work”Jonathan Balmforth


So, What’s Inside?

  • Over 70 bite-sized lectures
  • 6 hours of quality HD content
  • All the source files for each project
  • Access to me (in case you have any questions)!

You will learn powerful and little known techniques to enable you to build your own powerful spreadsheet solutions. We will do this by creating multiple projects together, step-by-step, and in depth to guarantee your understanding. Each projects builds on the last, so your skills develop cumulatively.

You will learn how to combine multiple functions in a simple way to create powerful solutions that most users believe can't be done in Excel!

Each project in the course is designed to introduce a range of concepts that showcase the true power of Excel, and show you what is really possible.

In project one:

  • The fastest way to create powerful dynamic templates (by cheating!)
  • Custom formatting
  • Manipulating calculations with time and dates
  • Conditional formatting
  • Simple and nested logical testing
  • Building complex and dynamic text strings
  • ActiveX controls (and how to customize them)


“AMAZING - I love the course, and I love the teaching style. If you want to be a super user, get this course!”Beatrice


In project two:

  • Building a fully functional complex relational database
  • A Masterclass in Vlookups (WAY beyond the basics here!)
  • Importing data from multiple sources
  • Controlling user entry


“Fantastic Course - Alan breaks everything down in to simple steps, so you can actually follow it and understand how it all fits together. Can't wait to try some of this out”Nickie Student


In project three:

  • Creating named ranges (and why you would want to)
  • Building dynamic named ranges
  • Getting Excel to create them for you!
  • A powerful trick very few users know about!


“…this course contributes extra features to Level 1 in such a way as to improve the quality of your learning. I will recommend it again for sure :-)” – Alex


In project four:

  • Hyperlinking (so it ALWAYS works!)
  • Creating Macros, and using them to automate your work!
  • Executing Macros from buttons
  • Dropdown lists
  • Building dynamic formulas using Excel’s Indirect function


In project five:

  • Project management made simple with Gantt charting!
  • Have your chart timeline ALWAYS begin at the best date 
  • Nesting multiple logical tests (simply!)
  • Making your timeline completely dynamic by changing a single cell!


Project 6 coming soon!

This isn't just a course with a bunch of formulas, it's hands on guide through real life projects. You will learn how to build complex solutions using the very same methods I use. 

Become the Excel guru I know you can be, with the help of this course!

Enrol now, and I’ll see you in the course!


Who is the target audience?
  • This course is for those who have completed Level 1
  • Have a working knowledge of Excel
  • Those wanting to take their skills to the next level
Students Who Viewed This Course Also Viewed
Curriculum For This Course
86 Lectures
Welcome To The Course!
1 Lecture 01:58
Project 1 - Creating A Data Entry Screen To Populate Multiple Templates
21 Lectures 01:59:19

In this lecture you will learn the importance of proving your concept.

Before you spend hours creating the layout for your spreadsheet, adding graphics, word art, formatting and page layouts, you probably want to see if what you plan to do is even possible! The core concept of this project is:

Can I enter data in one place, and have it populate in other places automatically.

Preview 04:49

In this lecture we will look at planning ahead.

As we will be building multiple sheets that will populate automatically, it's useful to have an idea of which elements:

  1. Must have data entered (the dynamic fields...the ones that change)
  2. Are constant (never change and can be permanently "hard coded" in the templates we will populate)
  3. Are calculations (we can use formulas to calculate the entry)

As we want to make this as time efficient and as easy to use as possible, knowing these three things up front will enable us to enter only the bare minimum information, and then let Excel do all the heavy lifting.

Preview 02:13

Now we have worked out all the elements that will need an entry, we can set up our simple data entry screen!

Preview 04:08

Time to start entering some data!

In this lecture you will learn how to change Excels default formatting for dates and times into something more useful, including a neat trick to instantly tell which day of the week any date in the past or future falls on!

Preview 06:02

Okay, now we have some data entered, it's time to start building the calculations that we will use to populate our templates.

In this lecture we will cover the straightforward calculations on time.

Preview 02:35

We covered the easy stuff in the last lesson, now we're going to get to the more powerful stuff!

We'll take a quick look at the ROUND function, and learn why it isn't useful if you are working with time.

Then you will master these much more useful functions, which will allow you to work in defined increments of time:


Powerful stuff!

Preview 08:06

In this lecture you will learn how Excel deals with dates and times...and it's not how you think!

Preview 08:28

In this lecture, you will master the skill of adding time with Excel.

This may seem simple enough, but I have seen many people give up on this and declare that it can't be done.

It can, and once you learn the trick to doing this, you will ALWAYS arrive at the answer you expect!

Adding With Time

In this lecture you will learn of the three ways to create a template in Excel.

By the end of this lecture you will know the fastest way (to cheat !) and create an exact replica of a template someone else has created in next to no time!

I wish someone had taught me this years ago!

Creating A Template From An Image

In this lecture, you will learn the fastest way to get a template into importing one from a different workbook :)

Importing A Template From An Existing Excel File

Some people report time as time, like 09:30, and some report it as a decimal, like 9.5 (meaning nine and a half hours...or nine hours and thirty minutes!). In this lecture you will learn how to easily work with both formats, and understand how to calculate one from the other.

We will also look at how to strip out just the hours from a time, and how to extract just the minutes from a time using the following functions:

  • HOUR

We won't be using them for our templates, but at some point you may need to know how it's done :)

Converting Time To A Decimal

In this lecture we will start to develop our data entry screen with the fields we need to populate in our second template.

Nice and easy.

A Little Bit Of Simple Data Entry

In this lecture we use boolean logic to conditionally format cells.

This sounds more difficult than it is, and by the end of this lecture you will be able to easily build a simple (boolean) logic test, and use it to determine which formatting is applied to certain cells using Excel's built-in conditional formatting function.

Just so we're clear, the result of a boolean logic test can only have one of two answers: TRUE or FALSE. Simple!

Simple Conditional Formatting For A Cleaner View

In this lecture we will learn how to calculate our travel time, which we will then use to calculate our time away from home in our expenses template, using some other data we have already entered.

Calculating Time Out Of House Using Travel Time

In this lesson you will learn all about logical testing!

This is particularly useful if you want to return a certain value based on a value in a different cell.

You will learn how to do simple logical testing (if this, then that, if not, something else), and also how to nest logical tests so you can test for multiple results...all within the same formula!

Functions in this lecture:

  • IF

Simple Logical Testing And Nested Logical Testing

This is a big lecture, but stick with will be worth it!

In this lesson you will master building perfectly formatted complex text strings. What does that mean? Well, you will not just learn how to join 2 words together with a formula, you'll learn how to create entire sentences (or paragraphs!). What's more, you will learn a trick to change the formatting on a specific area within the string.

This is particularly useful is you need to regularly provide commentary with a report. Rather than writing the whole thing from scratch every time, you can build the shell once, and have all the bits that change update themselves as needed automatically! Very efficient :)

In this lesson you will learn about the functions:

  • TEXT
Building Complex Text Strings With A Formula

In this lesson you will learn about the Form Control: Checkbox.

Checkboxes are those little boxes that you tick (check) or un-tick (un-check). We will also cover a quick cheat so we can replicate the template exactly!

You will learn how to add them, edit them, also learn some neat ways to get them all in the right place precisely without having to drag and drop (very useful).

Features used in this lecture:

A Tick Box Exercise Of Sorts

Another big lecture, but you will learn how to combine several workbook elements to produce something really clever!

We cover a lot of ground in will learn how to link a checkbox to a cell so it either checks or un-checks automatically depending on the value in the cell. Then, to ensure only one box from a range can be selected, we're going to do something really clever...we will build a limited list so the user can only select one of the three choices they should have. Then using that, we will build a logical test to determine which of the checkboxes should be ticked. Phew!

Features and functions used in this lecture:

Auto-Populating Check Boxes

Time for an exercise!

I have deliberately missed out one element in this project, so you can have a simple practice exercise.

The element I have missed out is the date that we submit the expenses. So your mission, should you choose to accept it, is to:

  1. Add an input field to the data entry screen, and
  2. Have that entry automatically fill in the date box at the bottom of the expenses form, and
  3. Format that date in a particular format

This lecture will self-destruct in 1 minute and 40 seconds :)

PRACTICE EXERCISE 1 - Time To Add A New Entry

In this lesson you will learn how to define a worksheet area, so the user can only see the area of the worksheet you want them to see, and even more usefully, learn how to lock your worksheet so they can only enter data where you want them they can't break all your formulas!

Features and functions in this lesson:

Defining A Working Area And Protecting Your Work

Time for a little more practice!

Using the skills you have mastered so far, I want you to finalise the Time Sheet.


  1. Make the cells behind the image white
  2. Make the image the working area (so that's all you can see)
  3. Lock all the cells except the three indicated in the video
PRACTICE EXERCISE 2 - Set Up A Working Area And Limit User Entry
Project 2 - Building A Database With Excel
26 Lectures 02:15:22

Okay, this is where it starts!

Vlook-ups are frequently cited as an advanced skill in Excel, and in this short lecture you will learn what they are, and how to use them simply, and effectively.

Functions covered in this lecture:


Right, if you want to create a database in Excel, you're going to need some data!

In this lesson, you will learn how to use an existing data table to build our project. We'll also cover a nifty trick for splitting your joined up data in to separate columns!

You will also learn how the Paste Special Transpose works!

Functions used in this lesson:

  • DATA: TEXT TO COLUMNS (to split out a column of data)
Step1: Get Some Data In And Split It!

In this lesson you will learn how to limit the input used in the VLOOKUP, so you can only get a value that DOES appear in your table.

You will also learn how to add helpful messages to the cell (to help the user), and how to add an error alert (in case they still get it wrong!).

Functions used in this lesson:

Using Data Validation To Get The Right Input

In this lecture you will learn how to build a simple database using the single data table we have.

As a useful aside, we will create these VLOOKUPs in a block...this is a really fast way of entering a bunch of complex formulas en-masse.

All we do then, is drag them to where we want them!

Let's Build Our Database!

MORE DATA...that's what we need!

In this lecture you will learn how to import another data table from a text file (which you can download).

Importing Data From A Text File


In this lecture you will learn how to import data from a simple old Word file.


The CTRL & V works differently in 2016 (it doesn't paste as it does in the lecture!). Instead, use Paste Special and choose the Unicode Text option...then you're good to go :)

Importing Data From A Word File

In this lecture you will learn how to pull from multiple data sources at the same time!

It may sound complicated, but as you will see, it's really easy!

Pulling Data From Multiple Sources

So now you've mastered pulling data from multiple sources, you can now learn how to pull a VLOOKUP value from another VLOOKUP value you already looked up!

Stay with me...this is really easy to do :)

Using OTHER Look-Ups To Look Up!

In the last lesson we looked at using another value returned from a VLOOKUP as a look up value, and in this lesson you will learn how to cut out that intermediary step and just do the job in one formula.

LOOKUP From A LOOKUP With No Intermediary Step

This is just a short lecture to clarify where the lookup value has to be in your table array (hint: it's always the first column in the table, but not necessarily the first column in the sheet!

Data Arrays Don't Have To Start At A1

At some point when using LOOKUPS, you are going to encounter issues!

This is generally for one reason... data that looks the same, but actually isn't!

We'll cover of these issues, and their solutions in this lecture.

Some Common Reasons VLook-Ups Fail

There are a couple of shortcomings in the Excel VLOOKUP function. The first of which we discuss here...VLOOKUPs can only read data to the right of your looked-up value!

One Inherent Flaw In Vlook-Up

Okay, here's your very first POWER USER lecture in this section!

I've lost count of how many times people have said to me "I wish I could VLOOKUP to the left!".

Now, although this isn't possible using VLOOKUP, it is possible if we combine two of the other built in functions available to us. And in about 6 minutes and 44 seconds, you will know exactly how to do this (and know something your friends don't).

Functions used in this lesson:

  • MATCH: To find a match in a data table (honestly!)
  • INDEX: To return an intersecting value from a row and column in a data table.
POWER USER - A Breakdown Of Looking Up Backwards

In this lecture you will learn ANOTHER way to look up backwards, this time using VLOOKUP, but with another of Excel's built in functions incorporated...

Functions used in this lecture:

POWER USER - The Other Way Of Looking Up Backwards

In this lecture we will apply what we learned in the last lecture in to our main project database (where we have a lot more data to play with).

Functions used in this lecture:

Backwards Look-Ups In Action

If you ever have to write a database that will be used by others, there's one thing you must remember...users don't always do what you want them to!

That's the subject of this lesson. At the end of this lecture, you will know how to trap and deal with inconsistent user entry.

Functions used in this lecture:

POWER USER - Dealing With Inconsistencies In User Entry

In this lecture you will learn how to use VLOOKUP using a partial match!

This is really useful if you don't have the full string you are looking for (like with car registrations!)

You will even learn how to concatenate without using the concatenate function!

Functions used in this lesson:


Up to now, we've looked at looking up from a single value...but what if you wanted to look up from more than one value at the same time?

We will cover the two methods of doing this...the way most people do it, using a helper column, and then the way power users do it, using the dark art of array formulas!!!

Functions used in this lecture:

POWER USER - VLOOKUPs With Multiple Inputs

In this lecture you will learn about array formulas!

Array formulas are slightly different from normal formulas inasmuch as you have to hit CTRL + SHIFT + ENTER to enter them. For this reason, they are sometimes called CSE formulas.

Make no mistake, these are very powerful formulas. Only about 1 in 100 people I have met even know these exist!

Functions used in this lecture:

POWER USER - Looking Up From Multiple Inputs Using An Array Formula

If this short lecture you will learn how to use VLOOKUPs brother function HLOOKUP!

Now you understand VLOOKUPs, you'll master this one instantly :)

Functions used in this lecture:


In this lecture you will learn the holy grail of look-ups: How to return EVERY match (not just the first one!).

Now we are getting into seriously powerful array formula territory, and very few people can do what you are about to learn.

Functions used in this lecture:

  • IF
  • ROW
POWER USER - The Holy Grail - How To Return Multiple Values From A Single LookUp

This lecture covers the two main reasons the multiple return formula fails.

Both are very simple to solve, but to save you the they are :)

What To Look For When THAT Formula Didn't Work

This lecture can save you an awful lot of time fiddling around with changing you column argument in your lookups.

There are two clever ways to do this, and you will master them both by the end of this lecture.

Functions used in this lecture:

The Fastest Way To Modify Your Column Numbers

This was actually a question from a student, Anat (thank you, Anat!)...but it sits so well in this section, that's where it is! The question is so good, I can't believe it's never been asked before!

So, you've built a load of VLOOKUPS off a huge data set, and then someone either inserts a column, or changes the column order...all of your formulas fail because they're now looking in the wrong place! Not anymore, with this simple trick!

POWER USER - Vlook-Ups With Moving Columns

Okay, we've covered a lot of ground in this section, and we're now going to put in all together in our project.

Putting It All Together

In this lecture you will learn how to add an info field to state how many records were queried, and how many results provided a match...just like real databases do!

Functions used in this lecture:

The Finishing Touch: How Many Records Did I Find?
Section 3: Named Ranges
7 Lectures 38:01

In this lecture you will learn how to create a named range, what it's for, and how to use it.

Functions used in this lecture:

A Simple Static Named Range Using A Single Cell

In this lecture you will learn how to make a range of cells a named range, and incorporate it in to a VLOOKUP.

Functions used in this lecture:

Creating A Named Range Using A Range Of Cells

In this lecture you will learn how to use row labels to automatically create a bunch of named ranges automatically.

You will also learn how to quickly recall them using the name box, and how to delete them using the name manager.

Functions used in this lecture:

Using Row Labels To Name Multiple Ranges

In this lesson, you will learn to add multiple named ranges simultaneously using your existing row and column headings.

We'll also look at how to used your named ranges in formulas. and a really powerful trick to perform LOOKUPS without using a look up of any kind!

Functions used in this lecture:

POWER USER - A Magic Trick Using Row And Column Labels

Okay, time for another power user lecture!

In this lecture, you will learn how to create a dynamic named range. That means that as your data expands, you won't have to go back and manually alter you static named range to will all happen automatically!

Functions used in this lecture:

POWER USER - Dynamic Named Ranges

In this lesson, you will learn how to modify your dynamic named range formula to compensate for headings and/ot titles in your data.

POWER USER - What To Do With Dynamic Names Ranges With Titles

Okay, this is actually a question from a student, but it fits so nicely in this section, I've added it here!

In this lecture, we will look at how to create a dynamic chart using the skills we've covered in this section so stuff!

3035 - POWER USER - Dynamic Charts
Project 4 - What Can I Have For Dinner?
15 Lectures 48:55

Okay, you may have looked at the title of this section and wondered "What use is THAT?", will this short lecture will provide some context and tell you what I use it for all the time!!!

Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?

In this lecture you will learn one of the ways to insert a hyperlink to another sheet in the same workbook.

This is easily adaptable to link to a website, or a different document.

Functions used in this lecture:

Hyperlinking To A Different Sheet In The Same Workbook

In this lecture you will learn how to create a macro.

In its simplest form, a macro is a method of recording a set of keystrokes, which can then be replayed exactly the same way at a later time.

You will learn how to add the developer tab to your ribbon (if you don't already have it), take a look at the VBA (Visual Basic for Applications) window and see the "code" we just created, and finally take a look at one of the ways to "execute' our macro.

Functions used in this lecture:

Creating Our First Macro

In this lecture you will learn of two other ways to call your macro into action.

One by selecting it from the "Macros" pop up, and, probably more powerfully, to assign your macro to a form control button.

You will then learn how to replicate that button on every sheet so you can execute your code from anywhere in the workbook.

Functions used in this lecture:

Assigning A Macro To A Button

In this lecture we will add a dynamic named range from our ingredients list, that we can then use in a drop-down list we can use for selection.

Creating A List For Our Dropdown Using A Dynamic Named Range

In this lecture you will learn how to use formulas in conditional formatting to show instantly any items that do not appear in the dynamic named range we just created. This way we can instantly see if any "new" ingredients need to be added.

You will also learn the steps I go through to build formulas for use in conditional formatting rules, and test them so you're sure they work!

Finally, how to add additional tests so you can copy the conditional formats across blank cells without them being affected.

Functions used in this lecture:

Using A Conditional Format To Know When A Value Is Missing

In this short lecture, you will learn how to copy your working conditional formatting across multiple sheets.

Functions used in this lecture:

Copying Conditional Formats And Creating Our Drop Downs

In this lecture we will cover the first of the elements of our complex formula to calculate if a particular ingredient appears in a particular recipe.

The formula is complex, and made up of some functions we have used before, and one we haven't: INDIRECT.

This is a particularly powerful function, as it allows you to use a string to reference a particular cell, or sheet and cell, or workbook and sheet and cell.

Functions used in this lecture:

Building Our Formula...INDIRECT Function

In this lecture you will learn how to build strings to create sheet and cell references which can then be used by other formulas. Incredibly useful to master!

You will learn how to spot the syntax of sheet references, and how to combine different elements to produce a finished working "path" to use in your formulas. This is the next stage in building the formula we began in the previous lecture.

Functions used in this lecture:

  • &
Building Strings For Indirect Sheet And Cell References

In this lecture, you will finish the formula by ensuring it can only return a 1 or a 0 (it is in the recipe, or it's not).

You will do this by utilising the formula we have built so far, and wrapping a couple of other functions around it!

Functions used in this lecture:

  • &
  • IF
It's A One Or A Zero

In this lecture we will look at how we use our 1's and 0's to rank our likelihood of making each of the recipes with the ingredients we have.

Finally, you will learn how to apply a traffic light icon set to our results (very useful for dashboards!).

Functions used in this lecture:

  • SUM
Working The Percentages And Adding Traffic Lights

In this lecture you will learn about the hyperlink function.

We have already added hyperlinks to this project, but the hyperlink function has some...quirks...that make it incredibly frustrating to use.

This is one of those instances where the built in help function does not help much, and even an hour on Google wouldn't get you very far!

This lecture will ensure you are ready with a solution whenever you need to use this function!

Functions used in this lecture:

POWER USER - The HYPERLINK Function (And Problem)

This is the first of 3 challenges for you to complete using the skills you have learned in this section.

Your task is to use the hyperlink function and finish off the entries in column C.

Sounds easy, right?

Exercise 1 - Fill In The Blanks

This is the second practice exercise:

Decide on a formatting scheme you are happy with for the recipe tabs, and then record it into a macro, and then use that macro to "execute' all the same changes on each of the subsequent recipe sheets!

Exercise 2 - Pretty It Up With A Macro

This is the final practice exercise. And though it sounds like a simple VLOOKUP, the task is to use the skills you have learned in this section to build your data array (where you want to look) using the sheet names in column A.

Hint: you will need to need to build a string that looks like the data array, and then incorporate the INDIRECT function to make it work!

Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT
Project 5 - Using Excel For Gantt Charts...Timelines And Project Plans!
5 Lectures 30:51

There are two methods of creating Gantts in Excel: Using a worksheet, or using a built in chart.

In this first lesson, you will lay the groundwork for designing a Gantt chart in a worksheet using the data from the HR file we used earlier.

You will lean a way to automatically start your Gantt dates where they should be, and you will also learn a really nifty trick to make the project instantly scalable!

Functions used in this lesson:

Creating A Gantt Chart Using A Worksheet

In this lecture we will look at building the first part of our logical test to return a binary output (it's either a 1 or a 0). We will do this by returning the start date using a VLookup, and then wrapping the first logical test around it.

Functions used in this lecture:

  • IF


Building The First Part Of Our Logical Test

In this lecture we will add the second part of our logical test...the part where we test the end date.

To do this, we will need to know how to test multiple criteria in one logical test.

Functions used in this lecture:

  • AND
Multiple Logical Tests At Once Using AND

Now the formulas are in, we just need to apply a little bit of conditional formatting, and we're done!

Conditional Formatting...Where The Magic Happens!

In this section we will look at the other way of creating a Gantt chart in Excel, by using the built in charting tool.

Gantt Charts Using The Built In Charting Tools
Bonus Section - Just For Fun
1 Lecture 07:14

Just for fun, this is how I created the random made up list of car registration plates we used in section 2.

Functions used in this lecture:

How I Created Randomly Generated License Plate Numbers!
Student Questions Answered!
9 Lectures 01:02:37

The first student question in level 2 from Jon. Jon asked:

Hi Alan, I have a scenario and I'm wondering whether it's possible to create formula. I have three columns of data, in columns A, B and C have values, what I'm trying to work out if it's possible to use a formula to look in A (Calls),B (Text) and C (Data) to determine whether there is a value in these separate columns so in essence what I want it to do is to say do these columns have zeros or do they contain values, if they contain values return the header names of each column in a string in column D or if they all return zero leave blank. Hope this makes sense. Many thanks Jon

In this video, I'll take you through the process of creating a complex formula step by step to do just this!

Calls Text Data - Or How To Return a Column Title If Value is >1

Okay, another excellent question from Jonathan...he asked:

Hi Alan, I am wondering if it's possible to use the formula that you created but instead of values there's text in each cell? Many thanks Jon.

And here is the answer!



Calls Text Data 2 - This Time Using Text!

Okay, time for another Student question answered video!

This questions was about extracting a mobile phone number (always 11 digits) from a cell...the problem being it can be ANYWHERE in that cell.

Extracting Phone Numbers From A Cell

Back in the database section, we looked at how to use the CHOOSE function to get a "backwards Vlookup". A couple of days ago, someone asked what the CHOOSE function really does. As that was too big a question to answer in the Q&A, I thought a new lecture was in order!

Here, we will look at 4 different uses for the CHOOSE function, and see how it can be used for several very useful things...enjoy!

What Is The CHOOSE Function Really Used For?

I had a question this morning from one of our community regarding casing...specifically, how do you make the first letter of a word in a cell appear in uppercase. I know we cover text manipulation in Level 2, but I figured it may be useful to give you a quick heads up on the basics of text manipulation, so that's what I did!

In this new lecture, which you will find in the Student Questions Answered" section, we will look at Excel's built in functions to convert text to upper case, lower case and proper case. Then for good measure, we'll take a look at extracting parts of a cells text from the left, the right and the middle, and finally we'll look at how to build the complex formula to capitalise only the first letter of the text in a cell.

That's quite a lot packed into 6 and a half minutes!

Casing And Text Functions

Okay, time for another student question answered...and it's another great one!

This question is from Ruan, and Ruan asks if it's possible to create a dynamic chart from a table, and have the chart update depending on which value is selected from a drop down list on a different sheet! I told you it was a good one :)

Now this would be simple if you could use the INDIRECT function for a data series. We'd just build a dynamic string, and reference that, and hey presto, it's done. Unfortunately, Excel doesn't allow you to use the indirect function this way, so I had to put on my thinking cap and find a different solution!

We cover a lot of ground in this 10 minutes, but luckily it's all stuff you've already learned...just put together in a different way!

Anyway, enjoy, and as always let me know if you have any questions!


Dynamic Charting From A Drop Down

Another Student question...this one from Doug V.

Doug has a big column of data with names, and next to it, dollar amounts.

What we need to do is extract a unique list of values from the main list, and then add a sum for each of those unique all the individual amounts against each entry for that name are summed up neatly against our smaller, unique entry list. Nice and easy (when you know how!).



Preview 02:25

I had a question from Michael today, technically it was a VBA coding question, but it could be solved with a formula. Here's the scenario:

We have a data table, and we want to summarise it based on 2're thinking, "Yeah, that's just a SUMIFS, Alan"...BUT the data set has the two criteria in different axes!!!

It was such an interesting question, that I added a lecture to talk you through it :)

SUMIF With Dynamic Sum Range

Another great student question from Karen!

It's actually a 2 part question, the second part is about automation of creating PDF documents, and I'll cover that in Level 3, but this part most comfortably fits right here in Level 2!

What Karen wanted to know is, can you VLOOKUP, or something similar, to pull a picture?

Well, yes you can! And here's how...


Vlookups With Pictures!
Thank You - Your Special Bonus!
1 Lecture 00:00

Get your next great course, at an amazing discount only available to existing students!

Get Your Next Course Now!
5 pages
About the Instructor
Alan Jarvis
4.6 Average rating
1,111 Reviews
15,153 Students
6 Courses
Everything Is Easy, Once You Know How

Over 15,000 Happy Students, in 162 Countries Are Enrolled In My Courses!

Hi, my name is Alan, and I have almost thirty years of experience in teaching various subjects and have held senior management positions at several blue chip and Times Top 100 companies. I now specialise in consultancy and interim work.

My love for Excel began soon after the program was released. Back then, we really only used it for typing tables in our production planning department. If you can believe it, I was taught to then calculate with a calculator and TYPE the answers into'd be surprised how many people I meet who still do this!

There were two turning points for me in my journey. The first was a chance conversation with one of our accountants when I was complaining how slow it was to keep having to flip between worksheets to see different bits of data. He introduced me to VLookups and a new world of possibilities opened up. I found that I need never have huge paper accountancy pads (remember those?) and a calculator to produce a production plan again! Suddenly, I could produce my plan in 15 minutes, rather than the day and a half it used to take. I began delving into other features of Excel, and finding ways to incorporate them into the planning "system" I had created, and remember this was back in the day when the internet hadn't been invented! Through trial and error, I refined my Workbook until I could chomp through days of work in seconds, and what's more, using these powerful features of Excel, I could optimise our production runs to get more output from the same working week, something that had never been done before. Promotion soon followed, and I became the "go to guy" for anything Excel related. This was the 90's.

Then, in manufacturing, came the MRP revolution. We had an army of programmers developing a system for us. This was my second turning point. I was awestruck with coders. The skill they had, and how clever they were, and that they were smart enough to go to university in the first place. I wasn't like that. I had no formal education beyond primary and secondary school, but a really cool programmer named Mark let me into a secret...

He said "If you can describe, step by step, how to cross the road without getting killed, you can be a programmer". I recorded my first macro, and took a peek under the hood. I deconstructed the VBA code, and played with it until I understood what everything did. Then I changed it to make it do what I needed it to do.

Since then, I have developed accountancy systems, simple planning systems, full blown MRP systems and scores of stand alone "helper" systems to simplify and speed up work in almost every department in every company I have worked in. Some of the solutions I created 20 years ago are still being used today! If you doubt the power of Excel, it may surprise you to learn that you can run the procurement of a £1.3b (yes, billion) company with an Excel file with a bit of coding behind it. This is a skill that people will pay highly for!

Not everyone wants to go that far, so over the years I have taught people to create efficient solutions for themselves, either with or without coding. Those who took the time to learn have since had multiple promotions, or have started their own businesses! At the very least, they are getting more work done in less time with less headaches :)

If you are a complete beginner, just want to polish up your skills, or want to create sophisticated super efficient solutions for your business or personal life, my series of courses will help you achieve that. All you need is a little time, and a willingness to learn...I'm looking forward to working with you.