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

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

Alan Jarvis, Everything Is Easy, Once You Know How

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

Alan Jarvis, Everything Is Easy, Once You Know How

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
100 Lessons (9h 18m)
    • 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 - POWER USER - Dynamic Charts

      10:27
    • 58. 3 - Horizontal Dynamic Named Ranges

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      8:28
    • 77. 5 - Conditional Formatting

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      12:46
92 students are watching this class
  • --
  • 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.

6,835

Students

10

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.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

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.

phone

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