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 wanted to display the seconds to we could also have a look. A custom format for that. So again, we're gonna choose format cells. We're gonna have a look at the customer. We can see here that we have hh h Cole on M m on from our sample. That will give us nine oclock in the morning. If we wanted to add seconds, we could just come up to here. Hit the colon. Hit second seconds hit, Enter. And there you can see that our entry, although we haven't actually changed the entry will be formatted a zero knowing coal on nine hours 000 minutes. Colon and 000 seconds. Okay, so that's how you create a custom format. Let me just go back and choose hours, hours, minutes, minutes because we're not done interested in seconds. Okay, let's let's populate some data. Just so I retain that formatting, I'm just gonna copy those cells over and then change the Times. So Monday we're going to start at nine o'clock on. We'll have lunch at midday. We're gonna have half of their was lunch. So we're going to come back at 12. 30 on. We're gonna finish it. Five cause five is a fantastic time to finish work. I'm just going to copy those down. So there you go. We've managed to custom for matter of times, and we've custom formatted every day. So we have a bunch of data that we can play with. OK. And the next let's we're gonna have a look at Do some simple calculations on time. Okay, So you in the next lecture. 6. 1 - Simple Calculations With Time: Okay, let's start adding in some simple calculations there. The first thing we want to know is how long did we have full inch. So I'm just going to type the head in there off lunch. Andi, subtract in time is is very straightforward. Exercise and excel. So what we're gonna do, is it equals? Tell Excel. It's a formula. We're gonna look at what time we started back in the afternoon, and we're going to subtract what time we finished in the morning and hit. Enter. So they go. You can see very quickly that we had 30 minutes for lunch, and I'm just going to copy that formula down. That's nice and easy. We worked out what we have one we had for lunch, That's where cut how many hours we worked in the morning on and how many hours we worked. I really wish I could type how many hours we worked in the afternoon on bond. What wer