Quick Start Excel: How to Create an Accountancy-Style Spreadsheet | Jennifer Bailey | Skillshare

Quick Start Excel: How to Create an Accountancy-Style Spreadsheet

Jennifer Bailey, Experienced off-line and on-line tutor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
25 Lessons (2h 11m)
    • 1. 1

      1:15
    • 2. 2

      2:48
    • 3. 3

      3:08
    • 4. 4

      7:17
    • 5. 5

      4:25
    • 6. 6

      7:02
    • 7. 7

      6:30
    • 8. 8

      5:07
    • 9. 9

      3:30
    • 10. 10

      7:16
    • 11. 11

      4:42
    • 12. 12

      1:34
    • 13. 13

      4:25
    • 14. 14

      5:48
    • 15. 15

      6:17
    • 16. 16

      5:03
    • 17. 17

      2:55
    • 18. 18

      3:29
    • 19. 19

      6:54
    • 20. 20

      6:30
    • 21. 21

      8:01
    • 22. 22

      13:32
    • 23. 23

      5:27
    • 24. 24

      3:18
    • 25. 25

      4:37

About This Class

Jennifer Bailey will take you step-by-step in the process of creating your own financial/ accountancy-style spreadsheet which can be used for business and personal purposes. In the process you will learn how to use Microsoft Excel techniques such as creating drop-down lists, If Statement, Absolute Referencing etc. This is more advanced than my Getting Started with Excel course.

Transcripts

1. 1: Hello and welcome to the course. I'm Jennifer Bailey from Jennifer s baby dot com, The quick start girl who likes to get things done. I'm gonna be your tutor for the course. What I'd really like to do, though, is get to know you a little bit better. I'd really like it if you could leave a comment letting me know who you are, where you're from, why you chose to enroll on this course on what you hope to get out of it. If you're facing any challenges on the course, for example, don't fully understand something. Or maybe your software is not behaving in the same way as mine. Then please leave me a message. Either in the discussion or privately on I can come back and answer it for you. I also really appreciate it. If you take the time to leave me some feedback on the course, maybe give it a rating so it can help other students choose this course of themselves. In the meantime, I really hope you enjoy the course. I'm Jennifer Bailey. Andi, Thank you for watching 2. 2: Hello. I'm Jennifer Bailey. And in this lecture on give you a quick overview off the accountancy workbook that we're gonna be setting up in the following lectures. So this is how the workbook looks. So our first tab or a worksheet is set up worksheet on. This is where we enter up. This is where we enter in our start of information. So what are financial year is the first date in the financial year on the end date and also our accounts that were using on their starting balances. Now, I have got test data in this worksheet, so it might look a little bit round them and it's not completely filled in. It's just to give you an idea. Next, we have all our months now in the U. K. We have a financial year from April to April, which is why I have in April at the beginning, off the year on April at the end of the year. Now, all of these months look exactly the same on what they do is they give you a table here for putting in your transactions. So you have the date than the transaction, and this is linked to a drop down list. In a reference sheet off the different types of receipts and payments you have a description field on, we have look on dialysis field does easy for me to say on this is also linked to a drop down list for help from our referent sheet on here, you can associate your transaction with either an income or outgoing. He even also record mileage. So if you're traveling for work and you need to claim amount of money per mile, you could put your information in there. You have a reference and you have an amount, and this triggers these accounts here. When you put in your transaction, your analysis basically the information we will be automatically populated on the running balances used the monthly balances and also the startup bounces in the set up sheet. So all of these are exactly the same. What we also have that is the end of year on. This is basically our summary page, where it looks at our total income per different vitamin analysis and also outgoings in our analysis and then a summary table. We also have a mileage sheet where it works out our cost for claiming for mileage. Andi are reference sheet. So in this election, in the following lectures, I'm going to show you how to create this for yourself on show you all the different formulas and techniques I've used. I'm Jennifer Bailey, and I really hope you enjoy the course. Thank you for watching. 3. 3: Hello. This is Jennifer Bailey. And in this lecture, I'm going to show you how to name and save a Microsoft Excel workbook. She confined it again at a later date. The first thing I'd like to do when I'm going to create a new workbook is to give it a name and to save in a place where I can easily find it again. I think it's a very good habit to get into because it means if you have to go out in a hurry, you can quickly save and close down the workbook. If your battery dies on your laptop, least you know you have got a copy there and you're not trying to find what happened to it on your computer on, especially if it's gonna name, like Book One, which Excel gives it automatically. It doesn't always make it the easiest to find. So in order to save it, you just need to go over to file and then come down to save us, and you can choose then where you're gonna save it. If you're not quite sure, you could go to browse and then we need to do is find a suitable place to put it so in this case is part of one of my training courses. So I'm going to put it inside the course name. I've got a folder called Workbooks. I'm gonna give it a name and then I'm going to click Save. So now you can see it says accountancy workbook template rather than book one, which we had before. Now, off that you've done the file, save ours and you want to save your workbook. All you need to do is either click on the disc symbol up here. You could use control s on your keyboard, or you can come to file and safe, which ever is easiest method for you to use. Now, if I type something into one of the boxes and I thought closed down the workbook, you'll see that Excel prompts me to save the workbook. So here you can opt for save if you want to save your changes, or you cannot for don't save if that's what you want to do. So I'm sure the council that now So in this lecture I've shown you how to change the default name. Excel gives a workbook to give it something related to what you working on on to save it in a place she know where to find it. Later date. I'm Jennifer bailing on. Thank you for watching. 4. 4: hell of this Jennifer Bailey. And in this lecture, we are going to put together our yearly set up sheet, which is the starting point for our accountancy workbook. This is where we put the start date on the end date of our financial year on also, the opening balances off, the different accounts were going to be using. The first thing we're going to do is give our worksheet title yet he set up sheet. We're gonna make that bold underlined and increase the size. I'm gonna put in a few Millfield to you. Andi will give make that bolt and also accounts opening balance. Bold. Let's just increase the size if that columns it will fits. So we'll start by putting in our start date and are in date. This is the start date of your financial year on the end date of your financial year. So in the UK, we tend to work from April to April and you'll see that when I start building the worksheet that will probably have We will have to April's one to represent the eight for the beginning of the year on one to represent the April at the end of the year off. So you need to make this work sheet in accordance to how your financial year will work. So my financial year for this workbook is going to be 2000 and 16. 2000 and 17 on our start date will be the sixth on April 2016 and it will finish on the fifth off April 2000 and 17. Now, if you get these kind of hashes, it basically just means that your column is not wide enough your numbers. She just cup two column DoubleClick and will make the column the same size as the widest information in the cell. The next part we're gonna do is we're gonna put in the accounts were going to use. Now, listen is entirely up to you which type of account you use for your business. So, for example, I'm going to use current account PayPal account petty cash credit card on other, but you can use which every counts you use. Okay, Well, when we set up our other monthly worksheets, we'll set them up for the number off account she put in. So think about it before you start, because it's very harder or It's not hard, a bit more time consuming to set it up later. So what would do is if you're only using maybe three accounts of the moment you could insert to spare ones. And you just called him spare or other one or other, to which you can just rename later. So I was gonna put this information in, so next thing we're gonna do is we're gonna look at the cell for months. So when I put the Dayton right click, the moments is general. When I click on where the dates are, it's automatically converted to a day excels. Very clever like that. You're not happy with this format. You just come up to number distal arrow here, and in date, you can choose a different format just by moving the scroll by down and clicking on a different option, you may find that by changing your locale, it will automatically all to the former to your area. So in the UK, we usually put the day of the month than the months and then the ear, which you may have it with the month first and then the day and then the year. If you change this locale here to one of the other options, you may find that it changes your format for you automatically Next. Windows put currency in against these accounts now that because I'm in the UK, I'm using pounds sterling, so I'm using the pound sign. So in order to set your currency, you can come up to this symbol here. So in the home tap still click the arrow and you can pick the currency that you want. Now, if you don't want pounds, dollars or euros, just click on more accounting formats and come across to where it's a symbol. Click on the arrow and picked a different currency symbol. I'm gonna leave is two decimal places, and I will click. OK, you can't actually sending in these cells at the moment because I haven't entered any data . If I push zero, you'll see the currency sign appear with the dash. So the next thing I'm gonna do is I'm just quickly renamed this tab. The sheets taps you come down to. It's a sheet one, you right click on that and then choose rename and then call it set up or something very easy to remember is very good to get into a habit off naming your sheets. No tables, which will do it later. Date in a similar way, Which makes it easier to remember which is what they're called later on, and you'll see why. So in summary in this lecture, we've put together our yearly set up sheet, which is the first part over Constancy Workbook. We've put in the start, date and end date of our financial year on. We've also put in the different accounts we're gonna be using in preparation for putting in the opening balances, including setting the currency on the date formats. We've also renamed the Worksheet Tab. If you'd like to have a look at the worksheet we've set up in the next lecture, I've included an image that you can follow. I'm Jennifer Bailey on Thank you for watching 5. 5: Hello. This is Jennifer Bailey on in this lecture, I'm going to show you how to create a new worksheet, renamed the Tub, and also create a title for the worksheet using a formula which references information on a different worksheet. To create our first month worksheet, we need to create a new tap. She need to come down to next to the set up tab and click on the plus symbol. This will create us a new sheet and we're gonna rename. That's when I right click on it. Click Rename. I'm not gonna call it April. Now, when you create your taps and give them names, try and stick to a consistent naming pattern. So, for example, I've called this April using three letters a p r. And as I create each month, I will create it with the the three letters or the abbreviation off that month. So it keeps consistent patterns, the one we're referencing later on. We will instantly know what that sheet is called without having to go back and have a look at it. Now we're gonna give the worksheet a title. So what we're going to do is in this cell here a one we're actually going to put in April because this is our April worksheet on the year on, we're not gonna just type this in. We're actually going to use information from a previous worksheet, and that's the set up one. So what we're gonna do is we're gonna put equals in to indicate there is going to be a formula put in here, and then we're gonna go to the set up worksheets she'll notice here. It's saying it's referring to the set of worksheet, and we're gonna come down to be four, because this is the cell that we want to reference. So I'm not gonna push, enter and no see, um, back in the April worksheet and I have the date which has been taken from the set up worksheet and sell before. Now, this is no had theme format that I want this title to look like, so I'm gonna have to go in an old to that is automatically been set a date. But I want to change how it looks. Some have come down to the number option and expanded, and then I'm going to scroll down. Never looked to see if the former they want is here, but it's not. So what I need to do is I want to get custom on what I wanted to basically do is have the months but in words. So I'm choosing. Mm. And which should give me April as an A p r and then followed by the year. So that would show 16 because just two digits. Because two wives there, if I use this one, which is what we have now you'll see we've got the day, which is the same 06 followed by the month. Mm. So it's written in New Miracle. And then you can see I've got four wise. So that guesses 2016. So this is what I'm going to select. I will click. OK, And now you see, we have a poor 16 for the title on our worksheet. If I want to change that, what would have to do is I would have to go and change the reference cell, which is this one. The start date in order to change this one. So I'm just gonna make a little bit bigger. I make it bold in this lecture. I showed you how to create in your worksheet by creating a new tab on renaming that tab and also how to create a title for the worksheet using the formula, which reference information from a separate worksheet. I'm Jennifer Bailey and thank you for watching. 6. 6: Hello. This is Jennifer Bailey. And in this lecture, I'm going to show you how to create the main table for transactions for our accountancy worksheet. I show you had to put in all the headings and explain what they do create a table from the information and also how to format the color off the table. We need to start by putting in some headings for our table, and we're gonna start in cell a sex because later I'm gonna puts more information in the top here. So what I'm going to do is I'm just gonna pause the video while I type the headings in Just you don't have to watch me type. So the date heading represents the date off our transaction. So the day that we paid someone you will receive some money in transaction is how it was paid in all paid out. So it could be your credit card. Your current account, your PayPal account, etcetera. The description is a little bit more information about the transaction. So who was it paid to? What was it full? The analysis will link to our end of year sheet, which we're gonna create, so it will categorize the payment for the receipt. We have the Mileage column now. It depends on how you want to put mileage into accountancy spreadsheet Some people might want to charge by the miles they've traveled, so they traveled 10 miles at, and it costs and say, 35 pence a My old. They may want to do it that way, which is why I've left this column in cause that's often how I do it. Alternatively, you may just add a fuel cost or wear and tear of the car Andi attributed to analysis. You can use this column or not. It depends on how you want to treat mileage. When you do your accounts, you then have your reference column on. This is basically it could be an invoice number or a bill number something. I have to give you a little bit more information, and then we have the amount. Now this first part of the table will be where we input information. The second part of the table where I put our accounts in will be automatically calculated now off. Obviously, I've put in current account PayPal account, petty cash, credit card and other, because that is the accounts that I set up in our set up worksheet and you are using different ones. You need to change them accordingly. Here, the next thing we need to do is to convert this information into a table. So all we need to do is highlight the information have included an extra row here, could insert and then table. As you can see, the range is automatically been inserted into this box here. If it's not quite right and you realize you've missed out a self example. You can click on this button here, and it lets you go and change the range and push it again to accept as my table does have headers because we just type them in, you need to put a check in this box, then click. OK, This now opens up and you tap on our ribbon. And if you want to access that tab, you need to make sure that you are inside. You've selected a cell inside the table and then it will appear so I don't want have banded road. I'm going to turn that off. Andi, I don't want this filter symbol showing at the moment, so I'm going to turn that off as well. If you want to turn it back on its very easy, you just check back inside the table and then select what you want from there. If you want to change the color of the table while you need to do is click on a cell inside the table, click on the design tab, which papers up and then come across the table Styles on. You can expand the list by clicking here, and then what you can do is you can just have your mouths over the different styles and pick the one that you like. So I particularly like that one. So I'm just going to select that one now. Because of our table is effectively split into two parts. We have the input part here on the automatically generating part here. What I want to do is I want to change this color from here onwards, so it differentiates the different parts of the table. We can't do it using the table styles because, as you've just seen effects, the whole table she needs comes with the home tab on, then come to the bucket and then you couldn't pick some colors from the scheme here or you can select more colors. So I quite like a purple for my heading. So I'm gonna choose a purple there and then I'm going to start different but paler color here. So if you've already looked at colors, you can select one hell Shea which recent color should be looked at. So I'm quite like pale pink. One thing I just noticed that I haven't added to this table is I haven't credit a total column which we're going to need. So we're slicked back on the table, come up to design and then I'm going to select the total row. So as you can see, it's automatically added a total row on the bottom for us because we had to change the color of this part. The table manually will also have to change the color of the total row. So we need to do is just select that part of the table and then come up to our fill bucket and choose the color from there. So now a table is starting to really take shape. So in summary in this lecture, I've showed you how to set up the main table that we're going to use for our months in our accountancy spreadsheets. I've also showed how to convert it to an excel table on change, the colors both using the table styles on also using the fill option. I'm Jennifer Bailey and thank you for watching. 7. 7: Hello. This is Jennifer Bailey. And in this lecture, I'm going to show you how to add the top section into our accountancy worksheet, which includes things like our monthly incomings and outgoings andare monthly on running balances. We also look at using borders to emphasize certain cells on a worksheet. Let's add some extra sections onto our worksheet. So we're gonna put in a panel from eight to to J three in the same color as we've used here . Should you come up to the bucket on, pick the appropriate come up for their and then I'm gonna have a pink panel here, so it's gonna choose a pale pink color. Okay, Now, what I'm gonna do is I'm gonna put in some titles here because we're gonna have some totals held in these top handles. So the in B to have total monthly incoming on in B three will then have total monthly outgoings. I'm just gonna pause the video type those in. So make those two titles bold. She can use to be up here on the ribbon. Or you can use control Puss be on your keyboard. Let's make the cells bigger because I'm and then what I'm going to do. Some good put border around the's cells here, toe hold and so basic to make the stand out from the rest off the worksheet. So a cup to the borders option, which is here, and it's nice and easy and quick. You could just choose thick outside borders. I don't put aboard around them. I'm thinking. Put in two more titles. This is gonna be the running balance, and this one is going to be the monthly balance. The difference between the teary is the monthly balance is just going to add up. Basically, give a balance. Each of these separate accounts here on this particular worksheet on the running balance will take into consideration are opening balances on the set up sheet, plus the monthly balances and that will carry forward to each of the other months. So we're going to do is we're going to move these titles into the center of these cells. It's pounds across. So for this you want to use merge and center. So basically what is done is converted all these individual cells into one big one. It's gonna make that bold, and then I'm going to the same for the monthly balances, Maj. On center and make that bold. And what I'm gonna do is I'm gonna put in another box so you can either click on thick outside border, which will just put the border in the outside. Let me show you what I mean. Like that, like So it doesn't put the little white lines in between two different shape between each column to In order to do that, it's hard, like them again. Come to borders and come down to more borders, and you can see it showing the black outline already. But what we want to do is convert this line in the center two white line. It's me quite difficult to see because what I change the color all these will disappear to make sure you've got the correct wits in black first. Then come down toe white and slept just the middle line and click. OK, you'll now see, we have a big, thick black line on the outside with some white lines in the middle differentiating, but they are actually slightly bigger than these lines here, but they look okay, so we're not too concerned about that. The myth, affection start probable going. Change it. So let's just do the monthly balances. First, we're going over to the other one. So we come to more borders. I'm looking to have a thick border around the outside, and then I probably picked the wrong think borders west on. Then we're gonna pick the sin line, Mr Right One inside. That's check around. Have a look. Okay, So looks like I got a different thickness outside board us. Not to change, but not sure what this will get to go there. Now it goes. That's much better. So we need to do just quickly check and change the thickness of that line that's going to do that. So this one here trains the color white. And but there we go. That looks much better. Now, before we finish this video, I'm just gonna put in a total for the mileage. And as I explained in the previous video, you may or may not want to include that column, but I'm putting in so I can show you how to use it. Let's make that boat and they will put another thick border around here like so and so in summary in this lecture. I show you had to create the two top parts to our worksheet, which include our total monthly, incoming and outgoing section. Are total mileage are running on monthly balances. It also show you how to use borders to emphasize certain cells within the worksheet. I'm Jennifer Bailey. Andi, Thank you for watching. 8. 8: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to set up the reference shaped, which we're going to use as the data for populating our drop down lists. To create the reference worksheet, we need to create a new worksheet. So we come down to the plus symbol here and click on it on the more rename it and we'll call it breath sheet, then click Enter. Now, what you need to do now is to enter in all the information for the references and these are going to used in our drop down lists within our monthly worksheets. So what I've done in the next lecture, I've created an image off all of the different categories that you need to include. So I'm gonna do some to pause the video now, Andi, type the men and Aiken descript. We talk you through them. So I put in two columns off Information column A is the transactions. So here you can see we have bank receipt, bank payment, paper, receipt, paper payment, cash, credit card and other on. They correspond with our accounts that we set up the beginning on also those which we put into our basically our accounts here, so you need to do is make sure you put them in the order that I've shown you receipt payment receipt payment. Because when you come to reference them later on, it makes it much easier if you followed the same pattern. If you have to bank accounts listed. What you can do is you can put account pun me. You could put current after one of them on savings, for example, after the other one in brackets, we need to make sure you've got different entries. So the top one, for example, might be bank receipt, current bank payment current. That would be bank receipt saving, bank payment saving. So just make sure you've done them exactly like this. But, officer, you can rename them on. If you've got additional accounts, which I haven't got, then just add them on the bottom. The second column that we have here is our analysis column on this is basically analyzing the money that's come in or gone out off our our account Ross spreadsheet. So the top part of the analysis receipts is money that we've received for doing work. So here I've just given them some titles. And I've used the word income just to kind of make it clearer to myself. In case you forget which is receding, which ones payment, you need to make sure that anything in this category here will be linked to money coming in on anything below. This title here will be linked to payment going out off our accounts. So I've just put intuition assessing consultancy other an extra one on the reason for extra one in is in case I start having income from a different source. It basically is a place holder. So I can easily edit the end of here without having to go on ad extra rose. Because once we've set up our formulas, we don't really want to have to go and edit them. So you don't have to do one extra one. You could do several. If you feel that you might be changing your income source. I've also put in here reimbursed expenses because off, see if you've spent something out of your account. But someone has reimbursed you. You can have money coming back in in the same way I've put bank interest received, so it may well be that you receive interest for on your bank account, and you might want to basically look that on your spreadsheet. Now, the second section analysis payments is basically a list of categories which are quite common, that basically show all the different types of headings or sub subjects that you might allocate your money to. Now, I'm not an accountant, but I've found this information, and it works for me to help me categorize my business. So go in and put all your information into your reference sheet. In summary. In this lecture, I've shown you how to set up the reference sheet, which we're gonna use to populate our drop down lists. Now, in the next lecture, I've put an article which has an image which contains all the information you need to set up your own reference sheet. I'm Jennifer Bailey on. Thank you for watching 9. 9: Hello. This is Jennifer Bailey in this lecture. I'm going to show you how to create drop down lists. Now, In a previous lecture, we created our reference sheet, which will act as our data source for our drop down lists. So now I'm gonna show you have to populate a cell with that information. Now we've set up our reference sheet. It's really easy to convert cells to drop down lists. So the first thing we're going to do is go over to a transaction. We want to get into Selby seven and then we'll come up to data to make sure that the data tab open and then come across to data validation. And then when you come to us that allow you want to come to one said having any value, you want to have a list and then we're going to go and get the source. So we've covered this little button in one the previous videos. So basically we have to do is click on that button, allows you now to go and find the information that you need or the range you need to click on ref sheets and then what? We're going to do is highlight well, our payment methods and then click the button again to accept it and then click OK to now we have a drop down list for our transaction, and you do exactly the same for analysis. So Quicken Day seven analysis. Come to data validation. Change this to, say list. We're going get source This time I'm leaving the headings in because it doesn't make it easier for you. When you coming to select the categories, click the button to accept and then click OK, and we know half a drop down list in this cell, too. Now, it might actually want to make this cell bigger now because we can see that it's not gonna fit in like so. Let's just check out transaction as well. Yes, that's absolutely fine. The advantage of using drop down bliss is one. It speeds up the time to enter information into a spreadsheet. But also it means you're getting exactly the right information that you require. Because if you chose the hand type, any of thes entries in here, for example, is quite easy to make a typo or to put it in slightly roll on, that means the spreadsheet won't work, but by using a drop down list, you know, you're getting exactly the information that you want and quickly in summary. In this lecture, I've shown you how to create a drop down list. So basically populating a cell in a worksheet with information from a reference sheet or a separate worksheet. I'm Jennifer Bailey. Andi, Thank you for watching. 10. 10: Hello. This is Jennifer Bailey. And in this lecture, I'm going to show you how to set up the formula, which is triggered by our transactions going using nested. If statements on I'm also gonna cover absolute reference, we're going to put in a formula into our current account sell here and this will be triggered when this cell contains either bank receipt or bank payment. If it contains any of the other receipts for payments, it will do nothing. And when the test of receipt or about payment, it will basically take this number that's in this cell here and enter it into this cell. If it's a receipt, it will be a positive number on. If it's a payment, it will be a negative number because officers and men will be taken out of your account. Now there's a bit for baptism of fire if you've never used if statements before, because we're going to use a nested if statement. But what I'm going to do is I'm going really slowly. You can follow along with may, so start by putting an equal sign in the cell to indicate we're going to use a formula. Then if on what it says here is it checks whether condition is met on returns, one value, if true and another value if false. So we now have to open create an open bracket. So the logical test is going to be in the transaction cell. No, See, the bold writing moves along as we work away through this formula. So if the transaction equals, we'll get a referent sheet and choose bank receipt. So if the transaction cell is the same as a bank receipt comma, so you can see we've now moved a bit further along the formula that amount coma. So if the transaction equals bank receipt, it will put the amount in this cell. So now we have to do the false. So it doesn't say back. We'll see what we do now. Well, this is what we want. Our second if statement. So if open brackets transaction, it cools a payment coma, then put in the amount. But this time, remember, it has been negative. Good payment, negative amount, coma. If it is neither of the's too. So it's not bank payment or bite receipt. Then put a zero in to the cell. So because of open two sets of brackets one here and one here. We need to make sure we close nice of them, then push enter. So as you can see, we could a zero in the cell because we haven't got any information in here for it to trigger it because the A two on the A three refer to a cell on our reference sheet. We don't want those to change because you may have tried it before when you used auto fill with formula, the sale references automatically update themselves. Now, if they do it in this case, it will break our formula. It won't be referring to the correct cell reference. So what we need to do is use something called absolute reference on this is a way of fixing thes cell references. So we need to do is come up to our cell reference and put a dollar sign before the letter, which is basically the column which locks the column on a dollar sign before the number which locks the row. Now, if you and you want to lock one or the other, you can just use one of the dollar sides in the correct place. But we were. We want both the column and the role locked, and we do exactly the same thing for our second reference. The next thing I'm going to do is I'm going to test my formula to make sure it's working correctly. So I'm gonna put 10 in the amounts column. Andi, I'm then going to choose my transaction. So if I go to bank receipt, you can see this cell is updated to 10 on. If I put in bank payment, you can see that now Says negative 10. So we know that the first part of off women are is correct. If I choose one of these other ones, it goes back to zero because we only wanted to change if one of the transactions is the bank receipt or the bank payment. While we're doing this, I'm also going to format up the cells to show currency symbol because currently the former is only general. But we want this to be set for accountancy. So I've come to the currency symbol here. I'm going to choose pound sterling, just English United Kingdom option. But, officer, if you're not in the United Kingdom, you can opt for one of the other four months just by selecting them here. So this puts in your currency symbol and also changes the number to two decimal places as this had a zero in. It puts a dash in summary. In this lecture we've looked at using nested if statements on absolute referencing to put a formula together, which is triggered by a transaction on our accountancy worksheet. I'm Jennifer Bailey on Thank you for watching. 11. 11: Hello. This is Jennifer Bailey on in this lecture. I show you how to set up the transactions for your other accounts on your accountancy worksheet on. We use copy and paste on. Then we go in and edit the references to make it work as well as testing. The awful Miller is correct. We now need to put the formula that we put in the current account cell into the other accounts that we using. No, If you try and use the auto fill to copy the full miller across your find that it's now referring to the wrong columns. So don't use that. What you need to do is copy and paste to use control, see and control V on your keyboard. Alternatively, you can use paste copy and paste up here. Now, you might be thinking to yourself, If we've got PayPal here, why has this now no update itself to show the £10? Well, if you remember in the previous lecture we did something called absolute referencing, and we locked thes cells up here on day are locked to the bank account references. So what we need to do is need to quickly go to our reference page on update these references for each of these different accounts. So that's gonna have a look to see what our paypal account references are. So PayPal receipt is a four on payment is a five. And this is why I said makes you put these in the right order will make life so much easier for you because all you need to do now is go a full a five. Now you can see it has automatically updated, but then feel petty cash. It should be a six seven. Let's just double check that. 67 eight. Okay. Knowing and finally 10 and 11 on. What we're gonna do is we're just go test that now. So start to the top. Bank receipt. Positive number, bank payment. Negative number. Pay power seat. Positive number. PayPal payment. Negative number, cash receipt, positive number. Cash payment. Negative number. Credit card receipt. Positive number payment. Negative number seat. Positive number. A payment. Negative number. So now what we've done we've seen in and we've tested all our formula and to make sure that they're performing the way they should do in summer. In this lecture, I show you how to copy and paste the transaction formula across your other accounts. And then we go in and amend some of the references. So they are correct. We then do some data testing just to ensure that our formula is operating correctly. I'm Jennifer Bailey on Thank you for watching. 12. 12: Hello. This is Jennifer Bailey, and in this lecture, I'm going to show you how to name a Microsoft Excel table, which is very useful when you're having to refer to multiple tables in a formula. When working with Microsoft Excel tables, we have the ability to give them a name on, as we can have a different table on each month of the year, being able to give our table and name so we know which sheet the tables on is really, really invaluable. So in order to name your table, you need to click onto the table. I opens up the design tap on, then come across to where it says table name. I'm going to call my table April one because we've got in April at the beginning of the year, and we're also gonna have an April at the end of our financial year that I will just push enter to select that name. So now we're not click on the table and look up in the design tab. You can see that our table name is April 1 in summary. In this lecture, I've shown you how to name an Excel table, which means referring to it when performing formula isn't awful lot easier. I'm Jennifer Bailey and thank you for watching 13. 13: Hello. This is Jennifer Baby on in this lecture, I'm going to show you how to set up the monthly balances on your accountancy worksheet would also be looking at how we can insert formula using the total road. We're now going to set up our monthly balances. So what you need to do is make sure you have your total rose showing in your excel table. If it isn't, just click to select the table and come up to the design tab and make sure that the total row has a check against it, which will make the total Ro ro appear so. Adding formulas in here is really easy because they just give us a drop down list so we can pick the one we want. So the first thing we're going to do is we can add a total to the current account column. So when you click on the cell, the drop down arrow will appear and you can choose which formula which function you want. So we will the summer function, So this is basically creating us a total. So click on that to select, you'll probably see up here what looks like a relatively complex formula, and all its saying is it's creating a sub total, but is using the some function, which is what the 109 number is here. If you selected a different one. So let's select average. For example, you'll notice that changes to 101 she need to make sure you have 109 which is the sum. And it's basically saying it's adding up the current account column, which is the one we're in here, so looks complex blacks. It's very straightforward. She needed exactly the same thing each off these columns. So as I click on this one, it has a one showing, so you'll probably find Yes, it doesn't have 109 It has 103 so that's incorrect. That's just change that 219 So we have our totals. What we're gonna do is basically take the figures from here and put them in this road, and the reason I've done that is because if you have a spreadsheet with locks of entries, you'll probably find this total row has disappeared off the bottom of the page on. If you want to quickly glance at a worksheet to look at your monthly balances. You don't really want to be scrolling up and down. So this is why I did this row in. So all we need to do now is tell this cell it will see used the figure in here. So you push equals on your keyboard. Click on the cell. Andi, you should have something. Looks like table. Sorry. April 1, which is off. See what? We named our table April 1. And it was looking for the totals. Sell off the current account, which is correct. That's what we've done here. Then push. Enter. I did. Exactly the same thing for this one equals equals the cools vehicles. What we need to do now is I've just noticed we haven't got the currency set appear. So I'm just gonna highlight that like so and then choose currency in summary. In this lecture, I've shown you how to use the total row in an excel table to insert formula. So a some to add up the content off a column and also how to refer to that cell using our monthly balances column on our spreadsheet. I'm Jennifer Bailey, and thank you for watching 14. 14: Hello. This is Jennifer Bailey. And in this lecture, I'm gonna show you how to set up the running balances on your accountancy worksheet on this basically refers to a previous balance on ads on the current worksheets balance. Now we're gonna set up the running balances on the running balance will basically increase over the months off our worksheet on. They'll begin with our opening balances. So as this is a prole, this worksheet nuestra for toe are set up sheet. So we came across to offset up street sheet. So we come across to our set up shades. What we need to do in here is put in some opening balances on what I tend to do is so I can differentiate between the different accounts are put some very simple information in so one to three full five. Once a spreadsheet is completely set up, this officer, when I'll put real data in. But right now, this is test data. So what we need this cell to do because this is the current account is we need this cell to use the value in our set up street, plus our monthly balance for the sheet. So what we're gonna do is we're gonna push equals on the keyboard. We'll go to set up sheet and click on current account. Plus, it's going to add on our totals column that you can use this cell here if you prefer. But because this cell is already referring to this one, I'm sticking with this one's reference. So it's saying, Set up B eight, which is the one we've just chosen on our set up shoot. Plus April 1 inches our table on the total cell in the current account and then click enter so I can see it now, says one, because that is all the information. It s is one, because that is the information that has come from the sitting sheet on. We have nothing currently in our current account. If we change this to, say, current account or bank receipt, you'll now see we have £10 in this cell, which gives us a £10 total. So adds £10 plus the £1 from the set up sheets. You can kind of get an idea of how this works, so now we need to do is just set up the running balances for the other accounts we have so equals pay Powell plus total. It cools cash plus two full, he calls. He cools. There we go so well quickly. Just test this information on. All I need to do is change the bank receipt cell here and I'll move the £10 a cross between the accounts to test it's working. OK, so we have PayPal cash credit card on receipt on what we could do to also test to make sure it becomes negative as well. So let's go back to back payment. So now you see, we have negative £9 because we started with one. We took out 10 PayPal's correct cash credit card on payment. So always worth checking your data is working correctly before you move on on. Just use a little bit of test data toe work that out in summary. In this lecture, I've shown you how to set up a running balance, which is taking the information from our set up sheet on adding on our monthly balance to form a new total. When we start creating our additional months on accountancy spreadsheet, I will show you how to adjust the running balance to refer to a different month as opposed to the set up sheet. I'm Jennifer Bailey and thank you for watching 15. 15: Hello. This is Jennifer Bailey, and in this lecture, I'm going to show you how to set up your total monthly incoming on total monthly out going cells, using a sum if statement to set up our monthly incoming and outgoing totals, we need to use a different type of formula, which we haven't used previously. What we're going to use is a some. If so, this basically means it will add up some data if it meets a certain criteria. So let me show you. So we're typing equals some if on what it says here is, adds the cells specified by given condition or criteria. So let's specify a range Sergen Open brackets are range will be current account. Two Other Now Don't worry, because we got the beauty of using the table is effectively referring to the whole column. If you weren't using a table, it would just have a cell reference in there. So when you added extra sells, it wouldn't automatically take them into consideration. But buying using a table, it will assume any cells in that column. So some if table eight through one current account through to other, because that's what this colon means do something. So we're gonna put in a comma on. We're going to tell it toe, add them up if the numbers or the data in those cells are more than zero. So Epperson speech marks put in the more than sign zero close brackets, I mean, closed the speech marks on, then put in a close brackets. So if we have a receipt into our account, it will be a positive number to be more than zero. So therefore, this formula will pick that up and add up that data. So at the moment it says zero, because the only information we've gotten here is a negative number. So let's change that to a receipt and you'll see that that's his. I did that up and put in a 10 to show that it is picked up on a positive amount. So next we're going to do the opposite or a slightly different way around for the outgoing . Same formula equals some If open brackets range, which is the current account, two other come out open speech marks is less than zero. Close speech marks close brackets. So as we know, when we make a payment, the money will go out of our account, which will be a negative number, which means this summit statement is looking for numbers less than zero and then we'll at the mop. So let's just test that. Change that to a payment and you'll see now that because this has become a negative number , it is picked up. It's an outgoing amount, and it's removed it from our incoming because we've only actually got one row of data were not properly testing are some. If so, what I'm going to do is I'm quickly going to go insert some extra rose into this table so we can put a little bit more data in. So if you go into the table and pushed the tab button on your keyboard, it will automatically of tab along the row and then insert a new ready beneath it. So I'm sure you had a couple, so I'm going to add in. It doesn't really matter what they are. You can just do it randomly sometime. Make sure got to payments into receipts, and nothing's happened yet because I haven't put any amounts in, so I'm gonna put in surrender amounts. So as you can see Now we have a payment of PayPal, so it's going out. So the negative pay pal amount £10. We have a bank receipt of 12. We have a cash receipt off. 13 on. We have a payment of 15 to concede the difference between the positive and negative numbers based on the incoming or outgoing. We can see that our monthly balances have updated themselves, which we created in the previous lecture on Also are running balances have updated themselves, which we created in the previous lecture, and we can also see that our monthly, incoming and outgoing has old to to very instantly. I've got 25 of both, which I couldn't have planned if I tried it. So let's make this 20 and you can see that is correctly updating itself. The last thing I'm going to do it has changed the format of these cells to make them currency because I want the information to show into two decimal places in summary. In this lecture, I show you how to use the sum if statement to add up, arrange off cells that meet a certain criteria. We use this to set up our total monthly incoming on total monthly outgoing cells. I'm Jennifer Bailey and thank you for watching 16. 16: Hello. This is Jennifer Bailey on in this lecture. I'm gonna be tidying up our worksheet before we start duplicating it. So basically making sure our headings air in the right place. We've got all the formula ran that we need, Making sure the column witness of wide enough for holding on our data. That's nothing. Because once we start duplicating our spreadsheet, it makes it a lot more time consuming to put right. Before we start replicating this worksheet to create our other months, I want to do a few tweaks just to make sure that I'm completely happy with the layout on the formatting before I move on. Because once we start copying the worksheet, we don't want to them be during the formatting later on, because we'll have to do it to each new worksheet. Best to start in a place that we're happy, which will minimize the amount of tweaking, will have to do late wrong. So the first thing we need to do is we need to put in the the mileage total, which I haven't done previously, but it's exactly the same way as we did with monthly balances. You click in the cell where you want the some You come down to some Andi, it will total up that come for us. Now we want this total to pitch in this cell so you get equals Vinto, and that will transpose up into that. I'm gonna just the mileage header or title to the left hand side of the cell. So in the home tab, I'm gonna click on right to line to move it over. The other thing that I noticed when I finish recording my last video is when I inserted a row, these cells became blue and these cells became pink on its to do with the way that we created and formatted the table because we use the table styles to do the whole table on, added a fill effectively on top. So you've got a couple of options, Really, you can either manually adjust the color on your this side of the table, which is probably the method are use. But if it's going to annoy you that there might be irregularities with the formatting and the colors, what you could choose to do is just remove the fellow altogether. So highlights the middle of the table. Come across to where the bucket is on. Then choose no Phil. And as you can see, that the heading stay pink, but the middle becomes blue. So you've got two options to use Their I'm gonna kick minus pink unless it starts to annoy me later on. In which case, how properly for benefits. But there we go. The next thing we want to do is just make sure that everything is fitting nicely into the cells, something to make that little bit bigger. So quite a bit of room on the page. The description will need to be a little bit bigger. The reference may need to a bit bigger, but the other should be fine. The thing I'm going to do is I'm going to adjust thes cells here so that all the same size and a nice, nifty way to do that is highlight them and cup to format. Andi column width. And in here you can put the size you want them to bay. So let's try with 15. That might be a title, but to Becks, let's just make that tournament smaller. Let's go with 12. It will depend on how big the numbers you're gonna be using our but now they're all spaced out evenly. If you prefer, you could do the all the same. But you're pretty find that certain cells like the description, might need some more data put into it. Maybe also the reference. So if you're gonna make some of them a little bigger to fit the nice in the page, I suggest you adjust those ones like So before we move on, the last thing I'm going to do is I'm going to remove our test data because I don't really want it to be moved onto are duplicated pages. So I've just highlighted the columns I want to remove Andi. I'm going to delete Table Rose, which leaves me with my date of my heading, etcetera. A clear row that's empty of any information on our total row. So in summary in this lecture, I've shown you how to tidy up your worksheet before we start duplicating it. So we looked at adjusting the column widths, making sure the formula were in the right places on adjusting headings. I'm Jennifer Bailey on. Thank you for watching 17. 17: Hello. This is Jennifer Bailey. And in this lecture, I'm going to show you how to use conditional formatting to make the front off a cell change color if it has a negative number which were the use for our running balances in our accountancy spreadsheet. Why would like to do with my running balances is I would like the number to go red if it goes into into negative. So if I have less than zero in my balance, So which might be indicating that I'm going into my overdraft, For example, I wanted this to change color, and in order to do that, we're going to use something called conditional formatting. So I'm just gonna show you how to do that. So we're gonna select all of our running balances. We'll come up to conditional formatting, and we're going to start a new rule. The option we want change is to format only cells that contain a cell value off less than equals zero. So is less than zero on. This is the bit here where I change what the actual full nothing is going to be so formats . And I'm going to change my color off my writing to my fund, so I'm gonna choose a nice read toe. Highlight it now, You could if you wanted to. You could also choose to change the fill color off the cell to, but I believe it is the writing. Then click OK, and then click. Ok, so what we need to do is test it because we tested in all our videos, so I've gotten amount. So what I need to do is I need to put in a transaction which would take this into negative . It's now you can see that by putting in a payment off £10. It has caused my running balance to go negative. So it's changed color. Now. You may want to do this fuel monthly balances as well, but I'm just going to leave it for my running balances for the time being in summary in this lecture, I've shown you how to apply Conditional formatting toe are running balances on our spreadsheet so that if we have a negative entry or negative number, the font color changes color. I'm Jennifer Baby, and thank you for watching 18. 18: Hello. This is Jennifer Bailey on in this lecture, I'm going to show you the best way to copy or to duplicate a worksheet that holds all its formatting. We're now going to duplicate this worksheet to create on next month in the Siri's. So that will be may. So what we could do is we could choose to copy and paste this spread cheat. So in order to do that, we could highlight it'll. We could choose to copy, create new tap on, then paste it. But I should concede e all our formatting has disappeared, so that would be a bit of a nuisance, because we'd have to go in on just the formatting. And it's not so bad if you're just copying and pasting one worksheet, but because we want additional 12 it is gonna be really time consuming for us. So what we're going to do is we're going to do this in a different way. So it has come back to April, and this time we get rid of the copy and paste. We are going to right click on the tab. We're going to choose, move or copy on. We're going to select the work. Should we want to copy on, Create a copy and click. OK, so you now have April and we have April with two in brackets are for it. And if you look when I flicked between them, that absolutely identical, Which is a bit of a difference from when you look at this one, which we use the traditional copy and paste. So that's what we're gonna do to duplicate our work shades. We're going to use the option within the tab, which is move or copy. So what I need to do now is rename this to May. I'm sticking with the three letters with naming format. I'm just gonna move it toe after April just by clicking on dragging on it. We're not gonna need that sheet one anymore. So I'm just gonna go in and delete that now, before we move on and start replicating this spreadsheet further, What we need to do is we need to adjust our formulas because thes formulas are exactly the same as April. And if you remember, April used the set up sheet in quite a lot off their different settings. So, for example, we want this to update to may we also want our running balances to update from the April spreadsheet and not from the set up spreadsheet. So in the next lecture, I'm going to go through updating the formula on our worksheet In summary. In this lecture, I've shown you how to copy or duplicate a worksheet in a really quick and effective way, which also means that the formatting state exactly how you want it. I'm Jennifer Bailey, and thank you for watching. 19. 19: Hello, This is Jennifer Bailey, and in this lecture, I show you how to update the formula for our month to worksheet. So instead of referring to the set up worksheet, as our April month does, it refers to April, which means that our month and year is updated as well as our running balances. The first formula will go to change is all date and year or month and year, should I say in our top corner here because currently this refers to are set up shoot. But what we wanted to do is we want it to update for the current sheet were written, who wanted to say, May 16 or May 17 or whatever our second month ISS. And rather than referring to the set up sheet, we basically wanted to refer to the April spreadsheet. So we need to do is we need to change this formula and we need to use a different one. So we're going to use date, and this says it returns the number that represents the date in the Microsoft Excel date time code. Now, we're not really gonna worry too much about the date time code, because all we ask it to do is add on one month from the previous worksheet. That's all we're gonna do with this formula. So we need to do date open brackets. Yeah, open brackets now, and he's going to find the year. We need to get a A problem and click on a one, which is basically God. Date in. That we're using is our reference close brackets. So that's a year because a year is going exactly the same. Coma months. Open brackets a proof a one those brackets plus one because we want to add a month olds, it becomes May and serve April Coma day and from brackets close brackets. Now, because we opened a bracket here, we need to make sure we have another bracket here to close off. If you get an error, you'll probably find that you've missed one of the brackets off. So we're gonna push editor now. You can see that it automatically updates to May 16. So you've got eight pro 16 on May 16. Now I've created on your worksheet. We need to make sure we go in and rename the table. So click on the table toe, open up the design tab and then come up to table name. As you can see, it automatically called it April 13 which we don't want. So what we're just going to call this table is may and kick enter to select it. The last formulas that we need to alter on this worksheet you'll be pleased to hear is are running balances Because if you remember, they basically used this set up page as a reference on what we want them to do is we want them to refer to April because they're taking the balance from April on bringing it forward . So let's go and alter these so we'll start by clicking in H three, which shows us that it's set up B eight plus April 1 totals and current account. Because this is the same formula we used in the previous worksheet is not suitable for this one. So what we're going to do is we need to change this. So what would do is we'll type it in from scratch first. So equals eight Prue running ballots plus may total. So, as you can see, we now have the totals from this column added on to the running total off last month's total. So what we need to do now is we need to change the next one. So in order to make it nice and easy for you to follow, I'm just going to this rinse and repeat what I've just done there so equals April PayPal account plus May total. It cools April. I picked it cash plus may trifle he calls Truthful Cools. It's cruel plus total All that's left to do. Really Now, before we move on, it's just to check that the balance rippling through. Okay, so I'm just gonna put some data into April. So I was gonna put in a bank receipt on £10 and we can see that is pulling through there when we go to May weaken. See, the running balance has pulled through properly there. Now, what I suggest you do is just test each of the running balances just to make sure you haven't made any Everest with your formula in summary. In this lecture, I show you how to update the month two of our accountancy worksheet, making sure that our references are looking at our month one, which is April, as opposed to the set up sheet. I'm Jennifer Bailey on. Thank you for watching 20. 20: Hello. This is Jennifer Bailey on In this lecture, I show you how to duplicate your worksheet in order to create or the rest of the months of the year that you need on also how to tweak the formula, such as naming the table, adjusting the date at adjusting the rolling balances. We're now at the stage where we can replicate our worksheet for ALS the extra months we want to include for our financial year on. To do that, we just need to come to may not April, because April, remember that formula are different from May come to May, right Click Movil copy may free to co pay on. What you want to do is do that each month he require. So what? I'm gonna do some screws remind you how to rename the tab. So this will be June, remember? Stick with three letters for the month. Andi, I'm just going to keep creating tops or worksheets until I've got all the ones that I need . So what I'm gonna do is I'm gonna pause the video on, just recreate them. So you're just not sat here watching me do it. I'll be back in a minute, Ashkan. See, I now have April, May, June, July, August all the way through to April the 2nd April of my year. So I got a couple little jobs we need to do, so we need to go into June 1st on, we need to rename our table. So click on the table, come to design on Call it June that's renamed. We need to adjust our date. Now. The way I used to do it when I was creating this formula is I usedto always refer to the previous month. So what I would do is I would change April here to say may Now we can do it that way. So what you do is you just put in here May This is why we name our tables. Because all these ones here, the ones we've copied there were not the ones who renamed yet. And you can make that may like so And that will update. So June now says June, the other option. You've got to do this which it suddenly occurred to me out of the blue today. And I don't know, I didn't think it before. I'll show you on July is actually all you need to do is push plus two or plus three so off . The April is the 1st 1123 So you could make this three and it says July, so use whichever method you prefer. So is properly, quicker and easier. So Russia again on August it would be full because all it's doing is adding an extra month on as we go on the list. So pick whichever those two methods you prefer. The other thing we need to do for each off our months is to update our running balances and make sure that the referring to the right sheet So, for example, here rather this saying April, it needs to say may. So what we need to do is just go in and put in May, and you can do that for each of the running balances. This is why is always a good idea to have a format that you use for naming or tables on your tabs your worksheets, because it makes this job so much easier. So, just to recap, you need to go in. Andi, alter your your date. You need to make sure you've named your table in the design on. You need to make sure you go in and update your running balances. I'll show you just one more as a a sort of review. So I've already done July. You need to make sure that you name your table. So we go to design on. We're going to call this July, and we need to change our burning balances. So we need to refer to a previous worksheet. So that is June, since quite a few little things to change. But it's always been important to make sure I'm in a mistake. Why's that disappeared? Ah ha. Yes, it should be. Do not July. Having data in really does help you pick up if you've made any mistakes. So it's always worth as you've seen me in my videos. Going in and putting some test data in just to make sure there's no errors is much easier to do it as you go along. But then is to do it at the end and suddenly realize that things aren't adding up correctly . Just check up, done those rights. So right, That's okay. So this is the end of this video. Go away now and update the cells need updating for each of your months, and I'll see you in the next lecture in summary. In this lecture, I show you how to recreate all your rest of your calendar months on how to tweak the formula. So we've adjusted the date of the year. We've tweaked toothy running balances as well as naming our table. I'm Jennifer Bailey on Thank you for watching. 21. 21: Hello. This is Jennifer Bailey, and in this lecture, I cover how to set up your mileage worksheet on this is included for those people who claim money per mile for distance troubled for their work, I also look at absolute referencing again a little bit more detail. I'm no gonna create the mileage worksheet. Now, if you don't claim four money, Pam Oil in your business, then you don't necessarily need to do this. But for those of you who do claim in this way, this is why I've included this lecture to show you how to do it. So, first of all, we need to create another worksheet. So we click on the plus bottom on this one would give a title, helps my spell mileage right on. What we're also gonna put in here is mileage cost. So, basically, in here, you put the amount that you're claiming per mile. So I'm gonna put in no 0.45 because that's typical in the UK, I don't convert that to currency. Then under here, I'm going to put a table. So it's going to be I would put each of the months in and what should be able to dio now is also fill that across work so on and business Miles, I want to convert this into a table. So insert table, Yes, well, let's turn the filter button off table that and then each of these columns is going to refer to I should have just used the space out hung on that. We just do this better than that. So for my foot, Kotlowitz, let's try 10. Yes, but should be fine. I just make this one bigger. Now each of these cells here will relate to a sheet on our from each per month. So let me just rename this a second. That's what mileage that's Put it in the right place. You don't want it there. Let's put it in there. Right, so April equals e three. So we need to do is put that formula into each of these cells. So it depends on the way you want to do it. She could go equals my cools, June pickles and so forth. You don't need to watch me do the rest that so it's gonna pause the video and put the rest of those figures in. So I've now filled in all the formula and also what I was doing that I've been in and put some test data in because, as you know, I like to test my formulas. So, for example, in May I've put in two miles June, I've put in three miles, except right now, make sure if you're testing the data, you put your figures in here and not in this box, because otherwise you're gonna be overriding your formula. So the next thing we need to do to this table is we need to add a total wrote on the bottom . So if you remember, you click on the table you go to design. Well, actually, robber in here, which is quickly renamed this table. That's really limit mileage. I'm gonna add a total row onto the bottom. Now, I can add some formula to the table to basically multiply the number of miles in this cell or these cells by this number here. Now, I've kept this number out of the table because office, if the mileage cost alters, you don't want to have your mileage embedded in your formula. You want to go to just change the information in one cell and everything else is updated. So that's for formula. And now this time, without a type in a formula. So you want to type of equals and we want this cell multiplied to use. The Asterix is above the eight on your keyboard times by this. So and now you can see that we've got one times not put for five. Now, what we're gonna do is we can use something could absolute referencing. So what this does we have covered it briefly before is it locks one of the formula in place , so I'll explain what I'll show you. What happens? Apps. I sure what happens if I don't have absolute referencing? So I want to copy this formula across the all the other cells. Lets take it across to, and I'll show you what happens. What happens now is it doesn't reference be three anymore it references. See three on this one. Rep reference on this one references Day three. So what I wanted to do is all of these have still got a referent be three. So that's undo this minute. So if I lock b three by using the dollar sign before the column on before the road When I copy this formula across the table, you will see that all the figures automatically update or could be using these figures will make create our end of year spreadsheet in the next lecture before we move on, I think I'll just convert these bits of information here except off seat money. Aunt, convert them into the currency like so you may choose to have your mileage to more than one decimal places. Well, and to do that, you just highlight the cells. Andi increase. Not as much is that there we go to two decimal places. Or maybe wonder small place. It's up to you. What you want that on this just neatness off by giving this the title in summary. In this lecture, I've shown you how to set up your mileage worksheet, which calculates the cost per mile multiplied by the miles traveled in each month, which could be used in your end of year spreadsheet. I also cover looking at absolute referencing when using formula in a bit more detail. I'm Jennifer Bailey on Thank you for watching 22. 22: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to set up all the tables that we need for our end of year worksheet for accountancy template. We're now going to create our end off year worksheet. So we've only got on months are set up sheet on our mileage done. So our end of you is the last work she we're going to create in this video. Siri's. So we need to do issue to come to the plus sign, click it to create a new sheet we're going to rename that end of year. I'm also gonna move it where I wanted to pay, which is at the end of the months. So I'm gonna puppet there. Because really, these are reference sheets in which we don't need accessible the time, so have a blank sheet. So what we're gonna do to start off with is we're going to create a table. Andi, we're going to start in row six because I'm going to put it's an extra information in the top here, So we're going to do that later. So festival This is gonna be a table far income. So we're gonna call income on the first cell after that will be April on good auto. Fill the headings in like so this will be a poor too on the next thing we're gonna do is rather typing any headings in here. We're going to take thes from our reference sheet is quite quick to do. So you click, equals comes reference sheets, and we're interested in these cells here. So we're gonna put that one in and push enter, and then what we're going to do is we're going to war to fill down now, As you can see automatically. It's the other headings in forests on what this also means is if you then change your reference sheet and you change the titles here, your end of year will automatically update to change. So you don't have to change this separately. This is better. Be updated automatically. So good. All headings in there now. So what we're going to do is we're going to change this into a table, so we're gonna highlight the cells on, but come to insert and we'll convert to a table as we've done this a few times. Now on, I'm also going to give the table name, so we'll call it income. Like so we're also going to add a total road onto here. Total wrote in a mosque in turn the filters off because I don't actually want them at the moment. So underneath this table, we're gonna put in our outgoings. I wouldn't exactly the same on this time. We're gonna go back to a reference sheet. I'm not going to choose this set of information, so I'm just going to fall to fill that down a common before it finishes. So it's been a bit potluck now, to see where I get to the end might be a few more. There we go, but make sure to keep drawings and dividends separate for the time being. OK, so that's our outgoings information. So will convert that into a table call that April 2 and then convert to a table by the total row and turned the filter button off. We're now gonna put in a summary table which basically will total up your income. Andi, also you're out goings as well from the totals here. So what we need to do is put in total income total outgoings Andi, I'm going to put in the months again here. Now you could do the's headings in the snot a different way. So if you don't want to do it like this and you wanted it a slightly different way also includes the year. What you could do is in here, for example, push equals on, then go to each top and click on the heading like so So you could have April 16 May 16 and all of those sort of things. Now I did toy with the idea of doing like that, but it is a little bit more time consuming, So I decided we're just opt for having the month of the year because you are going to know which end of year this is Andi. If you want to make it clearer, you could insert a heading so that you to show you that actually, insert Andi put it equals and go back to your set up sheet on to take that information from there. So maybe that's more appropriate way for you. So there we go. So that makes it clear what your year is so coming back to this table. Total income total outgoings make this into a table table, OK, And turn the filter off turn the total wrote on Onda, renamed the table and oh, yeah, turtles like So now that's basically us sheet set up. Now I'm just gonna quickly tidy this up and delete that extra. Now, in the UK, we have something called National Insurance, which I've added in the top of my table here. Now, I've just been checking online on national insurance Does not count as a business expense. Now, if you don't live in the UK, this is something you won't pay. So what I'm going to need to do is take this out of my outgoings because even though it's an outgoing up my account, it's not a business expense. So when you come to do a tax return, whatever, this isn't technically included in that. So what you're going to need to do is remove that from the table and I'm going to need to put that into a different table. So if you're feeling a tax return, you may want to know exactly how much national insurance you paid. Well, it's just generated an era so intriguing. Nor that so let me just ignore one of these errors because they are going to annoy me. Ignore ever. Okay, let's do one of them Ignore era. Now. The other thing is, if you have claimed for your mileage, remember, we set up our mile it sheet here. If you are you going to use this mileage sheet you can't claim As far as I'm aware And I remember us, I'm not an accountant for both business Miles Andi also fuel and petrol. So if you're somebody who's claiming from miles, you need to have a separate table only because what we start putting the formula in excel gets very upset. If you have a different formula, slap bang in the middle of the table and it will start commit inconsistencies. So I would suggest you do is if you are using fuel and petrol than you, leave this exactly as it is. If you're somebody who needs to claim by the mile, you can choose to remove this row or just leave in Biloxi. You won't have any entries in, but to add an extra table to the bottom here. So what I'm doing due to quickly set up this mileage table on, you can choose whether you wish to use it or not. So let's set this up. So we want to have it's going to set up pretty much in the same way as the other ones. Knowledge. What is this? Very quickly. Uh, oops. So there's all months and and for this table, we're gonna take the information from our mileage sheet. So that's before we do. It's just set this up a table, as you can see, quite like tables. Insert table, turn the header off. Stupid to do that, I meant to turn the filter off like so we're gonna take this information from our mileage sheet. We would take that from here. Yeah, well, just let me feel to that cross. They're upsetting the formulas too much. So that information has now being pulled through from our mileage sheet. And you may have to decide how you claim back this business, Miles, you may want to include it as part of your total income because it may come in as a reimbursed expense. She might have to change the formula slightly to incorporate that, or it might be something that you claim separately for, But that is gonna be how you deal with your mileage for those who need it, And you probably don't need me to show you this, but I'm going to do it. Nonetheless, there's a bit of a recap. I'm just gonna quickly set up another table for national insurance and that quickly set this up. You've seen me do this several times now, so you probably don't need to watch this bit, but I would do it just in case. It's always good to have a bit of a recap on to convert this into a table. Um and, um, inch there we go. Turn off the filter button. Now I've kind of broken where the rule, my own rule. I realized when I was sitting up the name of thes tables and I've called this mileage I have renamed the I that which is terrible. So that is just ridiculous. That's all that out Bend off year. Moyle and I realized I did exactly the same thing. When I set up these two tables, I called them outgoings and incomings. So that change I didn't even rename that one Slapped wrists. Jennifer, End of year out on. That's renamed that one end of year in. Yeah, can. There we go. So I've now got a bit more off a consistent naming formula. So in summary, in this lecture, I've shown you how to set up all the different tables that we need for our end of your worksheet on in the next lecture. What could be inserting the formulas that we need? I'm Jennifer Bailey. And thank you for watching. 23. 23: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to put the formulas in your income table in your end of year. Worksheets on also will test the data to make sure it's performing as we expect in order to create the formula for our income table. We're going to use some if statement again. So if we come across April, what we're gonna do is we're gonna start with equals some. If on what we wanted to do is if a certain range contains a certain value, then it will add them up. So we got some if and we were going to go back to our April worksheet and our range is the analysis. So if this column contains, so use the comma. If it contains this entry, then I want it to enter this amount. So if or if there's more than one entry and war than one, therefore amount in the G column, which is the amount column, it will add them all up. So we disclose brackets to finish and look. It automatically puts the formula all the way down the column full us. And you may notice that when it refers to the whole table. It puts it just in the square brackets here. But if it's depending on a certain row, it puts the act symbol in. And we basically just need to follow exactly the same method as that for May, June, July, August, etcetera. So I'll do one more just so you can see. And then I'll pause the video. Andi, fill them all in. So equals some if the pain from brackets mais analysis comma column Comma is the same as this. Then enter the amount close brackets. So I'm just gonna pause the video now. Why gun fill the rest of these in. So now I've entered in all the rest of the formula. I've also started entering in Santa Stater just to check its correct. So if we go and have a look at April now, if all your tabs aren't showing, you can just move that along like that so you'll shave muscle tip so you get eight proof. I didn't see I've put in to bank payments against tuition. Andi have also put the amounts in. Now what you'll find is if I take out these payments, your end of year will still show 30. But if you saw there, it didn't come out of your point. So you need to be mindful of the fact that you complete all off the information when you're doing the spreadsheet. Now, in a later lecture, I'm going to show you how to use conditional formatting just to check that the information on your monthly sheets is tallying up with that on your end of year because it's quite easy sometimes to put in the wrong information or to miss him information out. What you need to also remember is that if there's something in here that's a receipt, it's going to have to bay one off these income sources. You can't have a receipt in here if it's something to do with your payments because that doesn't work. You can't have a receipt. That's a payment she need to make sure any receipts central bank is matched up with your income on any payments is matched up with your categories in the payment section of the analysis, so I'm quite happy that our formula is working well. So in the next lecture, I will show you how to put in the formula for the outgoings. So in summary in this lecture, I've had showed you how to use the sum if statement in order to calculate the income in the year end worksheet. I'm Jennifer Bailey on Thank you for watching. 24. 24: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to use some, if again, to set up the outgoings table in our end of year in order to set up the formula for our outgoings table on our end of year. Spreadsheet is almost identical to what we've done in our income. So what you need to do is put in equals. I'm using some, if again, bit brackets. We need to come across to April, come to analysis, coma, the outgoing reference. So if it matches this one, that's what it will look for. Coma amount. Close brackets enter right. I'm just gonna run through that again. So go to May equals some If open brackets now, we'll have range criteria and some range. So a range we wanted to refer to is analysis come in Coma. We wanted to pick up where use of home is being used. Coma. And if it does, we want to take the information from the amount column. Close brackets enter. Now I'm going to pause the video and just complete the rest of these months who didn't have to sit and watch me as you can see, I've got it and put some test data in just to check that the information is putting through correctly. Now, I've just realised we haven't got any currency on these cells. Let's quickly apply Cem currency okay on, and we also need to do our totals as well. So let's start as we at the bottom of the page. Let's start with our totals for our outgoings. So if you remember from a previous lecture, we just come into the totals row. Use a drop down arrow on pick some. We basically want that on all of the's cells, Mark. So and we also want it on incoming, like so in summer. In this lecture, I show you how to set up your outgoings table with its formula, using some, if again, in our end of year worksheet. I'm Jennifer Bailey and thank you for watching 25. 25: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to set up the totals table in our end of year spreadsheet on to use simple formula in order to calculate our profit. Now we can set up our total income and total outgoings table. Now I covet thrown a little bit the curveball here because we set up this as a table. If you remember, we set it up on, then we formatted it on. We give it a name. Now, Excel doesn't like having in a proper excel table like this. It doesn't like having different formula in the same column. So what we're actually going to do is I'm going to show you how to turn this table back to a range of cells, and it will no longer be and named and fixed table. But what we've gone is we've got the beauty off having it set up with the banded rose. We've got all our titles across the top. We just need our formula to work for us. So in order to convert it back to a table, we'll bite. Arranged, I say, if you just need to click in the table somewhere. Come up to design and click on convert to range. And it'll say, Do you want to convert the table back to a normal range? Well, yes, we do. So it's now not got some of the same functions that that a insult that an Excel table has. So if you notice now in the totals Ro, the drop down arrow has disappeared. But that's fine because I'll show you how to put that in manually. So what we need to do now is just populate this table. So we put equals and we refer to this cell equals in this. So and we refer to this one here, put enter. Now you can see we have populated it. Now for some reason, it thinks that that should be a date Which office? It shouldn't be. It should be currency. So let's highlight this table and change that to currency on for some reason. Also, it's decide it needs to be bold and underlined, and I have absolutely no idea why it's decided to do that. But let's put that right. So I've turned off the underline and I've changed the funds back to black. Now we're going to use risk or to fill that information down the table like so that guy and we're going to use a simple formula to subtract outgoings from incomings. Now what? I'm gonna quickly do it. I'm just gonna put some income in just so we can see it working. So let's add a couple of extra rose in here now, bizarrely, Did you see on a previous lecture where this decided to play up and change its colors and I had a blue column and all the rest of pink is behaving beautifully now? So there is Sometimes there's no rhyme or reason to it. So let's just put in some income and that's put 50 in the office. No appearing in here because I haven't put the transaction in but the end of year and testing it. That's really no problem. Okay, let's put another ring. Come in. Accessing 100. Okay, let's go back to your end of year. So now the idea is our income is mawr than our outgoings. So we're going to do a simple, very simple formula equals income. Take away outgoings unwto and we can use that formula groups we can use that formula old way across the shoot. Like so it's also indicating where it's a negative because, Officer, we've got no income listed here at the moment. And so in summary in this lecture, I've shown you how to insert formula in short totals table on your year end worksheet. I'm Jennifer Bailey on Thank you for watching.