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 nee