Microsoft Excel Essentials: Level 2 - For Intermediate/Advanced Students - From User To Superuser | Alan Jarvis | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Microsoft Excel Essentials: Level 2 - For Intermediate/Advanced Students - From User To Superuser

teacher avatar Alan Jarvis, Everything Is Easy, Once You Know How

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

99 Lessons (9h 8m)
    • 1. 0 - Introduction, And Welcome To The Course

      1:58
    • 2. 1 - Proof Of Concept

      4:49
    • 3. 1 - Planning Ahead

      2:13
    • 4. 1 - Creating Our Data Entry Screen

      4:08
    • 5. 1 - (Custom) Formatting Dates & Time

      6:02
    • 6. 1 - Simple Calculations With Time

      2:35
    • 7. 1 - More (Useful) Calculations With Time

      8:06
    • 8. 1 - It's About Time (And Dates)

      8:28
    • 9. 1 - Adding Time

      3:39
    • 10. 1 - Creating A Template From An Image

      11:34
    • 11. 1 - Importing A Template From An Existing Excel File

      2:21
    • 12. 1 - Converting Time To A Decimal

      5:41
    • 13. 1 - A Little Bit Of Simple Data Entry

      3:11
    • 14. 1 - Simple Conditional Formatting For A Cleaner View

      4:42
    • 15. 1 - Calculating Time Out Of House Using Travel Time

      4:46
    • 16. 1 - Simple Logical Testing And Nested Logical Testing

      8:28
    • 17. 1 - Building Text Strings With A Formula

      12:10
    • 18. 1 - Before We Move On

      1:05
    • 19. 1 - A Tick Box Exercise (Of Sorts)

      7:11
    • 20. 1 - Auto-populating Check Boxes

      11:48
    • 21. 1 - Radio Buttons as an Alternative to Check Boxes

      13:25
    • 22. 1 - PRACTICE EXERCISE 1 - Time To Add A New Entry

      1:40
    • 23. 1 - Defining A Working Area And Protecting Your Work

      4:40
    • 24. 1 - PRACTICE EXERCISE 2 - Set Up A Working Area And Limit User Entry

      1:06
    • 25. 2 - Simple VLOOKUPs

      4:16
    • 26. 2 - Step1: Get Some Data In And Split It!

      3:54
    • 27. 2 - Using Data Validation To Get The Right Input

      3:47
    • 28. 2 - Let's Build Our Database!

      6:12
    • 29. 2 - Importing Data From A Text File

      2:01
    • 30. 2 - Importing Data From A Word File

      2:37
    • 31. 2 - Pulling Data From Multiple Sources

      3:33
    • 32. 2 - Using OTHER Look-Ups To Look Up!

      5:25
    • 33. 2 - LOOKUP From A LOOKUP With No Intermediary Step

      2:19
    • 34. 2 - Data Arrays Don't Have To Start At A1!

      2:58
    • 35. 2 - Some Common Reasons VLOOKUPs Fail

      6:02
    • 36. 2 - One Inherent Flaw In VLOOKUP

      1:10
    • 37. 2 - POWER USER - A Breakdown Of Looking Up Backwards

      6:44
    • 38. 2 - POWER USER - The Other Way Of Looking Up Backwards

      6:49
    • 39. 2 - Backwards Look-Ups In Action

      3:47
    • 40. 2 - POWER USER - Dealing With Inconsistencies In User Entry

      8:19
    • 41. 2 - POWER USER - Fuzzy VLOOKUPs

      3:33
    • 42. 2 - POWER USER - VLOOKUPs With Multiple Inputs

      9:39
    • 43. 2 - POWER USER - Looking Up From Multiple Inputs Using An Array Formula

      4:45
    • 44. 2 - VLOOKUPs Brother...HLOOKUP

      5:04
    • 45. 2 - POWER USER - The Holy Grail - How To Return Multiple Values From A Single LookUp

      13:33
    • 46. 2 - What To Look For When THAT Formula Didn't Work

      4:05
    • 47. 2 - The Fastest Way To Modify Your Column Numbers

      7:24
    • 48. 2 - POWER USER - VLOOKUPS With Moving Columns

      3:05
    • 49. 2 - Putting It All Together

      9:52
    • 50. 2 - The Finishing Touch: How Many Records Did I Find?

      4:28
    • 51. 3 - A Simple Static Named Range Using A Single Cell

      3:44
    • 52. 3 - Creating A Named Range Using A Range Of Cells

      2:53
    • 53. 3 - Using Row Labels To Name Multiple Ranges

      2:34
    • 54. 3 - POWER USER - A Magic Trick Using Row And Column Labels

      4:55
    • 55. 3 - POWER USER - Dynamic Named Ranges

      8:06
    • 56. 3 - POWER USER - What To Do With Dynamic Names Ranges With Titles

      5:22
    • 57. 3 - Horizontal Dynamic Named Ranges

      11:09
    • 58. 4 - Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?

      1:25
    • 59. 4 - Hyperlinking To A Different Sheet In The Same Workbook

      3:32
    • 60. 4 - Creating Our First Macro

      5:37
    • 61. 4 - Assigning A Macro To A Button

      4:13
    • 62. 4 - Creating A List For Our Dropdown Using A Dynamic Named Range

      1:39
    • 63. 4 - Using A Conditional Format To Know When A Value Is Missing

      4:34
    • 64. 4 - Copying Conditional Formats And Creating Our Drop Downs

      2:50
    • 65. 4 - Building Our Formula...INDIRECT Function

      2:55
    • 66. 4 - Building Strings For Indirect Sheet And Cell References

      7:16
    • 67. 4 - It's A One Or A Zero

      2:35
    • 68. 4 - Working The Percentages And Adding Traffic Lights

      4:06
    • 69. 4 - POWER USER - The HYPERLINK Function (And Problem)

      3:16
    • 70. 4 - Exercise 1 - Fill In The Blanks

      0:59
    • 71. 4 - Exercise 2 - Pretty It Up With A Macro

      1:45
    • 72. 4 - Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT

      2:13
    • 73. 5 - Creating A Gantt Chart Using A Worksheet

      7:39
    • 74. 5 - Building The First Part Of Our Logical Test

      4:20
    • 75. 5 - Multiple Logical Tests At Once Using AND

      8:28
    • 76. 5 - Conditional Formatting

      5:32
    • 77. 5 - Gantt Charts Using The Built In Charting Tools

      4:51
    • 78. 5 - Gantt Charts With Different Colors For Different Criteria

      7:47
    • 79. Bonus - How I Created Randomly Generated License Plate Numbers!

      7:14
    • 80. Level 2 SQA 01 Calls Text Data Or How To Return a Column Title If Value is 1

      6:42
    • 81. Level 2 SQA 02 Calls Text Data 2 This Time Using Text!

      9:02
    • 82. Level 2 SQA 03 Extracting Phone Numbers From A Cell

      2:39
    • 83. Level 2 SQA 04 What Is The CHOOSE Function Really Used For

      14:47
    • 84. Level 2 SQA 05 Casing And Text Functions

      6:35
    • 85. Level 2 SQA 06 Dynamic Charting From A Drop Down

      10:02
    • 86. Level 2 SQA 07 Extracting a Unique List, And Summing The Money!

      2:25
    • 87. Level 2 SQA 08 SUMIF With Dynamic Sum Range

      5:55
    • 88. Level 2 SQA 09 VLOOKUPs With Pictures!

      4:30
    • 89. Level 2 SQA 10 Data Validation With Dependent Dropdowns

      3:42
    • 90. Level 2 SQA 11 Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround)

      8:30
    • 91. Level 2 SQA 12 Kinda A Vlookup From 2 Drop down Lists

      6:47
    • 92. Level 2 SQA 13 Tiered Pricing

      9:00
    • 93. Level 2 SQA 14 Worksheet Protection

      4:39
    • 94. Level 2 SQA 15 The Middle Name Problem

      9:03
    • 95. Level 2 SQA 16 Findng Matches, And Counting Entries

      8:06
    • 96. Level 2 SQA 17 Fee Calculator, or LOOKUPs That Are True, Not False

      5:42
    • 97. Level 2 SQA 18 The IF(s) Functions

      6:01
    • 98. Level 2 SQA 19 Drop Down List, Shifting Ranges

      6:07
    • 99. Level 2 SQA20 Dynamic Calculations From Another Sheet (1080)

      12:46
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

7,882

Students

15

Projects

About This Class

ab36c831

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

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.

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)

In project two:

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

In project three:

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

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

Projects 5 and 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

Meet Your Teacher

Teacher Profile Image

Alan Jarvis

Everything Is Easy, Once You Know How

Teacher

Hi, my name is Alan!

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, interim work and teaching.

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 ... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. 0 - Introduction, And Welcome To The Course: Hello, My name's Alan and on the creator of this course Excel Essentials Level two for intermediate and advanced students. Now, at the moment, I'm assuming that you already have the fundamentals of Excel mastered. That is, You know what a spreadsheet is? You know how to write a basic formula, and you know where everything on the screen is. No need to worry, if not, because we'll go through things step by step in depth anyway. Now, one of the criticisms I hear when I speak to other students, particularly of Excel courses, is that they're taught a bunch of formulas, but they have no context, so they think the formulas are fantastic, but they can't actually see a reason why they would ever want to use them. For that reason, I deliberately structured this course into individual projects. Now you may have a look at the project title and think, You know what? I have no need to work out how to create a relational database, for example. But within each of the sections, there is some powerful techniques that if you just build it from scratch and you follow the course sequentially and complete each of the sections each of the lectures in those sections, you'll see some powerful formulas at work in context on hopefully then you It will be a short leap to be able to say I I see how I can apply this in this project that I'm working on that work all this other project that I'm working on it work. So although you may look at the tiles and think, I don't need that work through it anyway because there are gems hidden in every section. So the aim of this course is to enable you to build powerful solutions using just the standard Excel functions, maybe combined in a way that you wouldn't think to normally. But you can achieve things that people generally don't think that you can achieve with excel. Okay, so thank you again for choosing this course, and I'll see you in the next lecture. Thank you. 2. 1 - Proof Of Concept: Okay, let's get started now. Recovered in level one of this course that one of the most important things that you conduce whenever you begin a new project is the first thing you should do is always rename your worksheet to something more meaningful. And you can see here that I'll rename this worksheet to time sheet and expenses, Master, because that's the name of the project that we're gonna be working on now before you spend hours creating a masterpiece and excel with lots of fancy formatting and tons and tons of data and formulas is proven at the concept that you're trying to achieve on the concept in this project is very simple. What we want to see is can we create a simple data entry screen where we can get a user to enter the band minimum amount of data and take that data and have it populate multiple reports elsewhere in the workbook without having to duplicate all of the work? OK, so we're going to do that very quickly now just so I can see where I'm working. I'm just gonna highlight these cells so I can see where I'm going to drop the formulas we can change it again later. But this is just so we can see where I'm working. So have just selected cell C one. I'm gonna hear equal Satele IQ. So it's a formula. Move over to a one and his enter on. We can see there in the formula Bar that we've now got a formula that say's I am reference in cell A one. Okay, so far, so good. I'm just going to copy that down seven lines. So now I am referencing on this cell, sell a one in this cell cell a two and so on and so forth. So no. When I populate some data into here, hopefully and it does, it populates automatically over here. So I'm just going to use a drag handle and use the auto fill sequence so we can see Yeah, that concept's pretty much proven their weaken type something in here and have it automatically populate over here without having to enter it again. So far, so good. Okay, the next thing we wanna have a look at is, can we do the same in the same she in a different sheet. So what I'm gonna do is add a new sheet to this workbook by clicking the little plus down here. And that's created sheet three. That's unusual. Let me just zoom in so we can see that nice and big older. Now, if you want to be able to see two sheets in the same workbook at the same time, that's entirely possible in Excel. All you're going to do is you're going to come up to the view ribbon just up here. You're gonna hit view to change from the view ribbon and then you're gonna hit new window. Now what you can see here, let me just zoom in again. We can see here is sheet one on behind sheet one. We can see the same workbook again. Now what we're gonna have to do is we're going to come back to the view menu and we're going to hit view side by side on. When we do that, we can see that in this sheets on the left hand side, Review and sheet one and so far in the right hand side were also viewing sheet one. But what we can do, She's click sheet three and there we have two views of the same workbook, so I'm just gonna do exactly the same thing again. But I'm going to do it in B this time. I'm just gonna hit the home button high like that so I can see where I'm working on. I'm gonna hit, equals. And this time I'm gonna flip over to the left hand screen and click a one on they go. We can see that we now have Monday. Just copy the formula down. So whenever I type in here. So if I say apples, Godspell Apple's still can't spell apples, apples and he sent it. We can see that it's being updated here in this this array on the first sheet, we can also see that's been changed over here on sheet three. Okay, let the only thing to notice here is with the formulas. If we're working in the same sheet on, we looked at the formula. We can see it say's equals a one, and that's because it's reference in a one on the same sheet. If we had a look over here, we can see that the formula has changed. It no say's equals sheet. One exclamation point a one on. That's because the way Excel tells which sell a sheet is on. If it's on the same sheet, it doesn't have anything. It just references the cell. So a one over here. But here it says you need to look in the same workbook we were looking for. A different sheet on that sheet is called sheet one. So they go sheet. One exclamation point. A one means look at cell a one on the sheet called sheet one. Okay, so they go near. The concept has proved we can get on with building our data entry screen. So you in the next lecture. 3. 1 - Planning Ahead: again goes, Let's take a quick look at what we're gonna be creating now. It's always useful to know what you're going to be doing ahead of time, so a little bit of planning is going to save you. Alluded time a bit later on. So let's just have a look at this scan of created of the time sheet that we're gonna be using. You can see from this sheet that we have three different bits of data in there. We have Constance those the things that will never change, such as the logo of the company, which I've cleverly made up to be A J consulted. That's me. There's going to be the contact details at the top left hand side. That's going to be constant, So that's never going to change. So all of the Constance weaken just leave in the template, which we're going to create in sheet to shortly now name providing. It's only going to be the one person is going to use. It can also be a constant. We also have the times that we start in the morning that we started finish in the afternoon . Now they're gonna be data entry, things and they're gonna be dynamic. So we're probably gonna want to have those to our data entry screen, cause that could change each time we enter it. Now, the third type of data, we have our calculations. So when we see the total air was worth, we don't need to enter that because we can perform some maths on the start times and calculate what that is automatically. Candidate signature is just going to be a constant. So we can either print the thing and sign it, then scan it and send it off or just type are naming and have that is the signature on the same thing with the client signature. So we've got three types of days that we have constant data which won't change, which we can just build into our template. We have dynamic data, those of the things that we want to enter into our data entry screen on. We have calculations. There are things that we don't need to enter. We could answer it, but what we're trying to do, we save a little bit of time here. So what we want to do is enter the bare minimum amount of information and have everything else calculators automatically. Okay, so let's have a look at how we build those in 4. 1 - Creating Our Data Entry Screen: Okay, let's have a look at building are simple data entry screen there. The first thing we're gonna do is we're going to stick a title in for our worksheet on. We're gonna call it Data and Tree Screen on. And let's just merge in center that Let's have it in green cause greens nice. I like that green. Have it old and increased the font size. If you don't have to do this, take a look. At course one because we recover that fully in course, one on all the different for matins for text and merge in central stuff like that. Okay, so we need a few gaps in here, selects and set some lines on. The first thing we're gonna need is the week ending date service. Just a week and on, and we're gonna be entering that in there. Now. What I like to do is some is to format the cells into a different color for particularly for data entry screens. Because that is an indicator to the user of which sells. It is they should be filling in that we can look a little later. Hey, you can local the other cells, so they can actually feel anything in that you don't want them to. But for now, let's just enter a simple data entry with the Four Matic. So there we go. We want people to enter stuff in there. We need assuming no weeks out some Monday. We need Monday, Tuesday, Wednesday, Thursday for exactly Sunday Onda We condole it. Rest of it because there are only seven days in a week. Now let's insert a little more from the time sheet that were populating. We can have a look at what entries We actually need to tohave the user enter into here. So let's just bring you up on the screen here and we can see what we want to do. So we need a start time in the morning. So we're gonna put something in here. We need to start time in the afternoon. Sorry. We need a finish in the morning. We need a lunch, but we're gonna calculate that. So what we then need is a start time in the afternoon on. We need a finish time in the afternoon. Pretty much everything else we can calculate out. So let's just stick some hellions in there. So we have start on. We have finish on bets. Just copy those. Stick him over there again. If you If you're not sure about the copy and paste in, check out level one of this coarser because we cover all of the basics in there. So what we're gonna do is we're just going Teoh sent to the text over there so we can see that that style. That's finish since the text over there on, we're gonna have this merged and centers will call that working hours. Okay, so they go, we've got a quite Deb. That's a little untidy. So let's just delete that, uh, a small uniform. Okay, so now we have the basics of a data entry screen. So in here, we can see because the cells are in blue. This is where we want the the users to enter the data. We have a weekend in which corresponds to this box. Over here. We have our star times on Monday morning, Tuesday Wednesday, 35% is Sunday, which we're going to populate into here. We have a finish time in the morning, which populates over here. Andare start time in the afternoon and I have finished time in the afternoon. Okay. Oh, I forgot something. We need also insert. We need another one over there. Don't waste. So we need to say am Andi. Let's just copy that. Dump that in their on call that p p And so they go. No, we can see that we have our working hours in this area. Just here. This is the morning. This is the afternoon. This will be our starting. My morning. Finished time. Morning. Start time in the afternoon From this time in the afternoon. Okay, Let's have a look at how we end some data in as time. 5. 1 - (Custom) Formatting Dates & Time: okay, goes now we can have a look at entry in dates on times no. Everyone knows how to enter a date into Excel on s. Today is Thursday the 30th of July. I'm just going to go ahead inside 30 slash seven on when they center, you can see that excels automatically recognize that as a date and chosen its default date formatting options. So it actually saves 30 Dash Jewell now, because we want to use a week ending rather than the than the actual date. Unless today happens to be a Friday and we're choosing our week end as a Friday, what we're gonna have to do is do some calculations to work out what it is now. We could use Excel to do that, or we could just add it up in our head. So if I didn't know that July had 31 days and have to recite the poem, 30 days has September, April, June and November all the rest of 31 all that kind of stuff. But there's a quicker way to do this by changing the format to a custom formatting excel so you can instantly say, actually, 30th of July is a Thursday s. So what we're gonna do is we're going to right click on here on this cell on. We're going to choose format cells. Now. I'm just gonna drag down into shot just there right now. What you can see, the moments have look at date is that we have a variety of different dates available to us so we could see this and it would say, 30th of the seventh. We could say this, and it would say, 30th or 30 July 2015 sonar so forth all the way down. But what we're gonna do is we're gonna allow own custom. So let's just click on the custom, man. We see we have, ah, a bunch of different ways of format in this, and it's very simple. To change it or we're going to do is highlight the type section just here and delete. Now we can see that the sample sales for 2 to 15 which probably doesn't mean much to at the moment. But we're going to cover that in a lecture in this section. Right? So what we want to do to see the if we wanted to see 30 jaw or 30 07 2015 we would type D D , which gives us the date we could type an M and that would say 07 begin to type space and hit y y so that we'd have 37 15 or if we wanted to dash in between we would say D d dash M M dash y Y, and then we can see 30-7 Dash 15 in our sample window. But if we hit three days, we can see that instantly. It goes to Thursday so that we can say Thursday hitter space d d the 30th on. Then we come on to the months now if we hear em, we get one digit. If we eat em em, it will say 07 because July is the seventh month. If we hit 1/3 M, we can see that it will actually change it to say job eso Let's say let's keep it like that . So let's say 30th Thursday, 30th jaw, let's say yeah, yeah, so they go. That's how we build a custom formats. So we say D d d space that will tell us the day of the week. Dee Dee tells us the date on and M shows us the month in a shortened format as a word rather than the number on 15 is the year. So let's just hit. Enter now that's much simpler to do. So no, If it's a Thursday, I can say that. Look, just type 30th of July and that's a Thursday and actually wanted to be a Friday. So it's just add 12 so they go can instantly see that the 31st of July is a Friday because tell the cell tells me that's on. Obviously, today we can work out very quickly every day of the week s. So if we said let's have a look at the 25th of December 2006 or 2016 we can see the Christmas next year falls on Sunday. They know sneezy, so we want the end of the week. So 37th 215 they go. So that's how you do a custom format for your dates now. Time Very, very similar notes. Some people mistakenly entered time as a decimal, so they said 9 30 There was a 9.5 that's nine have nine hours on behalf on our besides, but all times are separated with colon. So if we're going to start our day at nine o'clock, we'll type nine Coolum 00 and hit. Enter. Now, if we w