Excel Essentials: Level 2 - Intermediate/Advanced

Excel: From User To Super-User - Learn To Use Excel Like The Professionals Easily,And Step-By-Step
4.7 (9 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,082 students enrolled
$19
$100
81% off
Take This Course
  • Lectures 82
  • Length 7 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 11/2015 English

Course Description

Over 8,500 students are already enrolled in my courses. Join them, 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 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!

Alan

What are the requirements?

  • An understanding of the basics of Excel
  • A PC, a copy of Excel 2007, 2010 or 2013, and a willingness to learn!

What am I going to get from this course?

  • 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

What 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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Welcome To The Course!
Welcome, And Thank You For Choosing This Course
Preview
01:58
Section 2: Project 1 - Creating A Data Entry Screen To Populate Multiple Templates
04:49

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.

02:13

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.


04:08

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

06:02

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!

02:35

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.

08:06

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:

  • MROUND
  • FLOOR
  • CEILING


Powerful stuff!

08:28

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

03:39

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!

11:34

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!

02:21

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

05:41

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
  • MINUTE

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

03:11

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.

04:42

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!

04:47

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.

08:28

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


12:10

This is a big lecture, but stick with it...it 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:

  • CONCATENATE
  • TEXT
07:11

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:

  • FORM CONTROL: CHECKBOX
  • CELL LINKING
11:48

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 here...you 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:

  • CONDITIONAL FORMATTING: LIMIT TO LIST
  • DROP DOWN BOXES
  • IF (LOGICAL TESTING)
01:40

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

04:40

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 to...so they can't break all your formulas!

Features and functions in this lesson:

  • CELL PROTECTION
  • WORKSHEET PROTECTION
  • LIMITING WHAT THE USERS CAN SEE, AND CHANGE
01:06

Time for a little more practice!

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

Tasks:

  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
Section 3: Project 2 - Building A Database With Excel
04:16

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:

  • VLOOKUP
03:54

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)
  • PASTE SPECIAL: TRANSPOSE
03:47

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:

  • DATA VALIDATION: LIMIT TO LIST
06:12

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!

02:01

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

02:37

EVEN MORE DATA!

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

03:33

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!

05:25

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

02:19

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.

02:58

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!

06:02

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.

01:10

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!

06:44

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.
06:49

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:

  • VLOOKUP
  • CHOOSE
03:47

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:

  • MATCH
  • INDEX
08:19

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:

  • SUBSTITUTE
03:33

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:

  • WILDCARDS
09:39

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:

  • CONCATENATE
  • HELPER COLUMNS
04:45

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:

  • ARRAY FORMULAS!!!!
  • INDEX
  • MATCH
05:04

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:

  • HLOOKUP
13:33

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:

  • ARRAY FORMULAS
  • IF
  • SMALL
  • ROW
  • INDEX
04:05

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

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

07:25

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:

  • COLUMN
  • HELPER ROWS or COLUMNS
09:52

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

04:28

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:

  • CONCATENATE
  • COUNTA
  • COUNTIF
Section 4: Section 3: Named Ranges
03:44

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:

  • NAME BOX
  • NAMED RANGES
02:53

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:

  • NAME BOX
  • NAMED RANGE
  • VLOOKUP
02:34

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:

  • NAMED RANGES
  • NAME BOX
  • DEFINE NAMES: CREATE FROM SELECTION
04:55

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:

  • NAMED RANGES
  • NAME BOX
  • DEFINE NAMES: CREATE FROM SELECTION
  • VLOOKUP
08:06

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 compensate...it will all happen automatically!

Functions used in this lecture:

  • DYNAMIC NAMED RANGES
  • COUNTA
  • OFFSET
05:22

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

10:27

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 far...cool stuff!

Section 5: Project 4 - What Can I Have For Dinner?
01:25

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!!!

03:32

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:

  • HYPERLINK
05:37

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:

  • MACROS
04:13

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:

  • MACROS
  • MACRO POP-UP
  • FORM CONTROL: BUTTON
01:39

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.

04:34

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:

  • CONDITIONAL FORMATTING
  • MATCH
  • ISERROR
  • ISBLANK
02:50

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

Functions used in this lecture:

  • FORMAT PAINTER
02:55

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:

  • INDIRECT
07:16

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:

  • MATCH
  • &
  • INDIRECT
02:35

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:

  • &
  • INDIRECT
  • MATCH
  • ISERROR
  • IF
04:06

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
  • COUNTA
  • CONDITIONAL FORMATTING: ICON SETS
03:16

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:

  • HYPERLINK FUNCTION
00:59

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?

01:45

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!

02:13

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!

Section 6: Project 5 - Using Excel For Gantt Charts...Timelines And Project Plans!
07:39

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:


04:21

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:

  • VLOOKUP
  • IF

Simple!

08:28

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
05:32

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

04:51

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

Section 7: Bonus Section - Just For Fun
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:

  • RANDBETWEEN
  • VLOOKUP
  • CONCATENATE
  • NAMED RANGES
Section 8: Student Questions Answered!
06:42

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!

09:02

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!

Enjoy.

Alan

02:39

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.

14:47

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!

06:35

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!

10:02

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!

Alan 

02:25

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 values...so 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!).

Enjoy!

Alan

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Alan Jarvis, Everything Is Easy, Once You Know How

Over 8,500 Happy Students 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 Excel...you'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.

Alan

Ready to start learning?
Take This Course