Practical Excel Training - Basic Excel for Basic Bookkeeping

1. Courseintro: Hi and welcome to this course. In this course, I'm going to show you the very basics Off Excel, you're gonna learn about a workbook on the features of a workbook. You're going to learn how to enter data, have to work with data, and you're also going to learn how to analyze that daughter along the way, we're going to introduce you to bookkeeping on the minimum records that you should keep. If you're a small business owner or sole trader, I'm going to show you how to keep these records in Excel in what's known as the prime books of entry or the Day books. We have a daybook sample fire for you to download on. We'll do our examples in Excel using this sample file, and you can also download a final, more professional file at the end of this course. This course is broken down into five sections on each section, has a number off digestible and and easy to understand lessons at the end of each section. There's also some practice exercises for you to do, and I entirely recommend that you carry out the practice exercises. So if you want to know more, why don't you hop into the preview and have a look of the course 2. Section 1 overview: Welcome to Section one. This section is a very introductory section to Microsoft Excel on. We're gonna break the section down into tree lessons. In the first lesson, we're going to look at creating a new workbook on. I'm also going to show you around the workbook showing the different parts of a workbook so you can easily navigate around it in section two. I'm going to show you how to save on open your workbook. I'm then going to show you how to select Enter ennstal data on In the third lesson, I'm gonna show you how to use the undo and redo future. And I'm also going to show you how to called Copy and Paste. These are very, very basic elements of excel. And there's no work book too damn note for this particular section as you can use a blank workbook to start with for this. So let's hop into the first last and where we're going to create in your workbook. I'm gonna show you around. Excellent. Show you the different parts of a workbook 3. Excel - The basics 1: in this s and we're going to create a new workbook. I'm also going to show you around the work auction one show your column. Years I'm going to show you what a row is on. I'm gonna introduce you to features that to make you familiar with them. So when they're mentioned laid around on the course, she can quickly navigate to these particular features. So let's hop in on see what it's all about. Let's start by creating a new workbook. So with Excel open licks file, then click. You choose the kind of template you want to create. In this case, we're going to select a blank workbook, but that are plenty of built in templates that you can choose from on the temporary will open in Excel as seen below. So let's hop names straight into excel and have a look at this in action. So here we are now in an Excel file. So as we said to select a new file to open a new file select file, then you want to select new. Then here's the templates that are available for you. Any concerts, further templates on select a blank workbook on that is how you create a new five. What we're gonna do now is look at the parts off the workbook on the name Bucks is near the top left hand corner. You can enter cell data on formula into the formula. Bar on the data is displayed in the working area. The work she tabs along the bottom off the working area, or how you can switch between worksheets, plus the little plus button down here. To add a new worksheet. You can also find information displayed in the green staff aspire at the bottom off the window about the current workbook. The ribbons are tabs can be accessed via the text across the top. Each ribbon contains the relevant options. The home ribbon contains clipboard from type coloring, alignment, formatting styles on editing, and these ribbons can actually be customized. The insert dripping contains tables, illustrations, charts, reports, spark lines, filters, links, texts and symbols on all of the's can also be accessed via keyboard shortcuts. The page layout ribbon contains team's page, set up scale and arrangement on the formula. Ribbon contains the function library, defined names, formula orbiting Andi calculation options. Let's hop Been to Excel now on. Have a look at this in action we are now in Excel on. This is the name box over here, where you can name particular ranges when we look at this late around, this is thief functions or the formula bar, and you can enter text. Inter formulas are interested directly into the formulas bar. Ah, nope. Here we have our ribbons, the different ribbons that we had a look at a few minutes going in the home ribbon. As we said, We've got clipboard. We have fund alignment number styles, cells on editing on. We will look at these in more detail later on. In insert, we've pivot tables, pictures, charts, recommended charts, slicers, textbooks, page layout. We have teams margins. That's all about printing. We also then have arrangement if we want to tie things together on the line things. Our formulas contains our function, library or defined names on our formula, auditing as well as our calculations. Data allows us to a connections to external sources to sort and filter to use data tools such as data validation and to outline, such as Group on own group. The review top gives you your spell check on It also gives you your workbook protection. Your view gives you your different views between normal page break page layout. Custom view. You'll also find your zooms in here on how you can arrange your windows and for his pains within here. The developer ribbon on the power pivot ribbon I have turned down thes are optional. Andi, it's not something that you need to concern yourself with at this particular level. What I'd like you to do now is have a look at the ribbons that are available in Excel Open and you work. Look, and if you have any questions or comments, please post them in the discussion board and I get back to you as soon as possible. 4. Excel - The basics 2: welcome to the second lesson in Section one, and what we're going to look at is saving on opening your workbooks. I'm not going to also look at entering data, selecting data and deleting data because, after all, when you're using Excel, it's about using data. Let's look now at saving our workbook. So to save up what book we need to click file and then click save and then we need to choose a location to choose a specific location. Eking Click boils in the same house Dialog box, which want to do is navigate where you want to save the file. Enter the name you wish to call the file and then click, save, browse to the fire selected and then click open on the fire will open in Excel. So let's have a look at that now. Working in action toe, open a workbook click file and then click open, and then you can choose the location that you had saved the fire from. So here we are now in Excel and let's save this so we go to file, Let's go to save ours. I'm going to browse for where I want to save it to on. Then I'm going to search on my system of where I want to save this on our rename it Basic Excel for basic bookkeeping and then click Save. Now I'm going to just close this file. I'm gonna show you how to open the file. So if you go to file and then you go to open on, wouldn't you goto open? You can go to your computer and then you can then browse for where you put file. So you should know where you actually save the file and we can open the file I'm saving on opening the file is a simple as that. Let's look at the basics now off selecting data. So to select a particular cell, all you need to do is click on it. Click on the row header, which is the number on the side toe. Actually select the full world click on the column header to select a column on If you just want select multiple cells but not an entire row or column click on drug your cursor across the cells in question, you can. You can enter doubt up by selecting the cell and just entering data by typing and the data would win toe What ever sell you have selected on? You can quickly switch between sounds in your workbook by pressing the tab key to advance one column or pressing the enter key toe advance one row. You can also entered outer by selecting a cell on, then typing up into the formulas. Bar data can be deleted by selecting the sale or multiple sales. I'm pressing delete to delete a large amount of data at one time. Click the delete drop down in the home ribbon. So here we are now in Excel and let's have a quick look at what we were talking about. So this is a column thes air. All columns on the's going down this way are all Rose on. How did I select that? Well, I don't that with the mouse on, I don't know by pressing picking on the 1st 1 and then just dragging the night stand. I'm releasing the most potent when I've got to the bottom, and that is me selecting a number of rows and you can select columns and number of columns . You can also then just select cells so we can just drag that across by pulling on the corner. I'm I'm holding down the most button. Or if you want to select multiple cells with the keyboard, if you press shift on the down arrow or across Cairo will allow you select multiple cells that way. So how do we entered data? What if we just pick a cell on? We start typing That is also entering data to delete the doubt. You can just press the delete button, let's and you some more doubt. And if we want to advance across the columns, we press the tab. We can enter some more data on. If we wanted to move down, we would press enter on. That would bring us down to the next row. You can also enter data appear in the formless by our. So I'm just going to say hi there. And you can see that this being entered into the formula bar also goes into the particular cell. Now, if you want to delete a large amount of data, you need to select delete from the home ribbon on. You can delete sales. You can delete. She rolls, she columns. You can even delete the entire she from here 5. Excel - The basics 3: welcome to the final part of Section one. What we're gonna do is we're gonna look at the undo and redo features, and we're also going to look at could copy and paste. So let's hop straight in now and have a quick look we're going to look at using, undo and redo so you can undo in action. And to undo the action. All you need to do is click the undue icon in the quick access to a bar or the keyboard. Short court is control and said you can redo in action. Click the redo icon in the quick access to a bar on the keyboard. Sure, could is control and why you can also use options to help speed up your daily tasks, such as cold copying and paste on the keyboard. Shortcut for court is Controlling Ex for copy is controlling Z and paste is control and be There are also commands on the home tab on on the white click menu, so you may only want to copy or court one instance of data at the time because when you copier could data any existing item on your cut on your clipboard could be lost. So lets happen to examine now and let's have a look at this in our excel. So here we are now back in Excel on What we have is the sheet that we had air your own. So to undo the previous action, open your quick access to about you have your own do, which is also controlling dead. Before you place this, it will tell you what it's going to do. So it saying it's gonna undo the typing in cell before on a keyboard shortcut is control and dead. And if we undo again, it's on do on dawn, the typing in the South previous to that, Now we can redo and you see what happens when we redo the text comes back fairly quickly. Finally, what I'm going to show you is caught copying on pasting, I said there was a number of ways that you can could copy and paste. If you do it with the right click, you'll find your couldn't copy in here, and when you've caught are copied something you can then paste. You'll also find it on your home tab. Appear on your clipboard to see you can click on copy on you can paste appear. In addition to this, your keyboard shortcuts court is controlling, said Copy is control and see on Pieced is Control and V. So let's have a look at some of this in action. So control and X on you can see them little moving lines going around there to show that that cell has actually been selected and control and V to paste. So I quoted on I pasted Control and see Control and V so that's copy and paste. So they're quick keyboard shortcuts that you should get used to using and everything else that we've done in this section. I'd like you to please. Now go over. Make sure you understand. Open an Excel workbook. Play around with it. I'm sure you can entered out a select rolls select particular cells on. If you have any questions, please just post them in the discussion board 6. Review: you have now finished section one, and you've been introduced to excel by now, you should be able to open, save and close a workbook. You should be able to navigate around the areas of a workbook and quickly identify different areas of a workbook. You should be able to select enter under leaf darker, and you should also be able to use the undo redo court copy and paste features. If you're unsure about Annie off the items that we've covered in this, please don't hesitate Post discussion on the discussion board. 7. Introduction to Daybooks, Bookkeeping and Accounting: Welcome to Section two and Section two is about the bookkeeping process, and the bookkeeping process is about recording business financial transactions into the prime books of entry or otherwise known off the day books. There's a number off day books this the sales day book. This. The sales returned a book. There's the purchase daybook. There's the purchase returns Bait Daybook does the payments book is the receipts book on. There's also the journals book in this section. We're going to look at a sample off these day books on rough basic details you need to keep for your business in thes day books. By the end of the section, you will understand the key components off each of these day books that we have in our sample on the reason book book for you to download because you're going to use this moving forward for the rest of the Excel lessons that we'd be looking at in Section Tree and so on . So let's move straight into looking at our first day book on the first day book that we're going to look at is the sales day book on the Sales Returns Day book, which I have combined into one. So let's move on now into the next glasses 8. Sales and sales returns daybook: So the first day book that we have available is the sales of the sales Quite a day book. So we've combined the sales day book on the sales returned a book into one, which is perfectly fine to do on. What we have first recorded is we've recorded the date of the transaction and you should always put the date off your sale on your sales invoice. We then have recorded whether it is a sale or whether it is a return. So we'll use the letter s for sale on awful return. And later on, when we look at analyzing data are quickly show you how you can soar. True ass is on war sales or returns. The next thing we've recorded is the invoice member on your invoice number should always be sequential when you're making sales. So they should start at the beginning number and they shouldn't skip numbers going true on They should always be sequential. They don't have to begin with number one on. They don't have to just contain numbers, but your sales should always contained in a sales invoice number your customer then will you will need to be able to tie yourself back to your customer, so it's quite easy to record your customer details on the sales invoice. We then have your sales tax late. Or this could also be called that depending on where you are on after this, then we're recording the value. So this is the total value. It's the value, including sales tax after this week, then broken the sale down. So we have in this particular sample company. We've got food products that they're selling on. They also have a delivery charge. Andi. They break the sale down pair products, so it's easily identifiable for further analysis later on. Then we have the sales tax and you need to record sales tax, partly because this is a tax authority situation you need to be able to make your returns on became for any sales tax that you have 9. Purchase and purchase returns daybook: The second day book we're going to look at is the purchase on Purchase Returns Day book. And again, I've combined the purchases on the purchase Returns Day book into one particular day book, so you'll see. As with the Sales Day book we have a date column on this is the data off the purchase and you'll find this on purchase, invoice or purchase for seats. Next. We have identified whether it's a purchase or a return with P for purchase, and we have a return down there or for return. We also then have the invoice number. They should also be called invoice or credit note number. You'll find this on the invoice from the supplier, and you will have the supplier detail because you're going to need to know who you have to pay, so you should record the supplier name and any supplier details that you have after this is the sales tax rate or this is your purchase. Text on this will be on the sales invoice from your supplier on the rate will be clearly defined and should also break down the net on the growth off your invoice. After this, we then have the value on our value is the total value off the invoice. This is the growth value, so the gross value is the Net los. Any sales tax were then have some further columns and you can have as many columns as you like toe. Analyze out your purchases so we have cost of sales. We have rent and rate we have like he's on dependent on what your business is. You could have telephone charges. You could have service charges. You could have professional fees. There are a whole bunch of things that you could have in here to analyze ultra cults as detailed as you need to. After this, we then have the sales tax. So this is the sales tax amount on all off the items on the actual purchase invoice. 10. Petty Cash: the next prime book of entry or daybook that we're going to look at it is the petty cash daybook. And when we moved into excel again, we're going to be working on this day book first and then the fire that you have to download. I have some columns missing from this that we're gonna work on. So the first thing that we have again is the data notice that the date is the first thing that we recorded so far in all of our day books. After this, we have a petty cash docket number again. This number like your sales numbers, should be a sequential number, so you can easily identify after this We have the reason. So this is the reason that you're spending your petty cash, then we have the value on the value is the total off the analysis that we have? So the analysis that we have a stationary post diesel kitchen supplies on other on dependent on what your regular petty cash spend would be on will define how you analyse out of petty cash. After this, we also calculated sales tax, and we have the sales tax rate in there. So remember. So far, we've had sales tax on our sales invoices. Under sales returns, we've had tax sales tax calculated on our purchase invoices and purchase returns on were also calculating any sales tax on our petty cash. This way we can make sure that when we have a VAT return or sales tax return to do, we can encompass all sidles on the all expenditure. 11. Bank: the next day book that we're going to look at is the payments daybook. So this is any payments that you make out, whether it be by cash, whether it be by direct debit, whether it be by Elektronik transfer, any payments that you make out should be recorded in your payments. Daybook. So what's your first column on? You should know this from our previous day. Books are first column is the date on this is the date off the payment? This is the date that you write the check. This is the date that you make the direct debit or the electronic transfer. After this, we then have payment type on. The reason that we have payment type in here is if you need to quickly find a transaction you can filter on sort by payment types to make it easier for you to analyze than particular transaction. So the payment types that we have in here we've got d D for director, but we have Elektronik fund transfer, which is the F T, and we also have check, which is C H Q. The payment reference or the check number. You will find a direct debit payment reference on a receipt from your on your bank statement. You'll see any payment reference to a direct debit on also to an electronic transfer on your check number you will be able to find in your checkbook on the actual check. The next column is who the check where the payment is actually to. So who are you making the check payable to? So if it's for wages, are you making the check payable to yourself? Your name Joe Joe would go in here. Is it to the tax authorities? After this? We then put in the reason so we could put in the invoice number. If it's for creditor, we could put in wages. If it's for wages, we put in sales tax. If it's for tax. The next column, then is the bank value. This is the value that you should see very clearly hitting your bank statement. After this, we then have our analysis column, So we have creditors wages on other again. You can put in anything that you want, so the bank value should equal to the analysis value. Let's jump in now and have a look at the receipt daybook. So here we are now in the receipts daybook on again. We start with the date This is the date off the received. We then have our receipt type. So if it was a check if it was a trance fair we have a large mint reference so we can easily track are large mons. So if we have a If we received a check from tree suppliers and we launched them all in as one large mint we put the watchman sleep number in there. If it's a transfer, we could put in direct. After this, we then put in who the payment was received from because we need to be able to If it is a debtor, we need to be able to clear down their invoices and know that that particular data paid for a certain amount, understand what their balances made above. We then have a reason. So this is a reason for your received. It could be that you've got a refund of sales tax. It could be a sales invoice that a debtor old for you After this, you then have the bank value. So this is the amount that was actually lodged to the bank. And then, as with all the other day books, we have an analysis color 12. Review 2: So we've now looked at all of the day books fairly quickly, and I'm just going to refresh some key features off the day books in your sales. Don't forget that you're going to identify whether it is a sale or return. Don't forget that you should have a sales invoice number on sales. Invoice number should be sequential. You should also identify who the sale was made to. This is your particular customer should. Then it came for your sales tax on the total value of your invoice. It is up to you then, on how you further breakdown the analysis of your invoice. Dependent on what? The type of business that you're in, whether your sales distributor, whether your service industry, you can break down the analysis off your particular sales on, you'll always need to include sales tax. So the invoice number here is very important for you to remember. If it is a crab, it you would put in a minus value, not a positive value. Jumping into the purchases returns day book again. You'll notice the exact same features you have the date you're identifying, whether it's a purchaser credit, they'll always be a purchase invoice number, and you get that from your the actual invoice, so you'll notice some unique numbers. Sales invoices and credit notes have unique numbers. Purchasing voices have unique numbers on your petty cash. Documents have unique numbers. Why did these have unique numbers? They have unique numbers because as a bookkeeper, you need to be able to fire on retrieve thes documents easily. And if you don't have a logical sequential number that you could follow, it's going to be very, very hard to pull these documents. I would out of later stage if and when you need them. And there's a number of reasons that you might need them. You might need me to resolve disputes with any suppliers or customers on you might also need them for the tax authorities or the revenue. If you have a tax old it or you're submitting tax returns, so make it as easy as possible for you to file on. Retrieve these documents in which your sales for all your documents by our sales invoice number or credit note number on which your purchases I would fire them by supplier than by invoice number or, by date, your petty cash doctors should also be filed by the petty cash doctor number. So once you've recorded all your transactions and you've broken down the analysis off each of the transactions, you are going to want to be able to actually analyze, thes and produce some meaningful data. So true at the rest of this course, we're gonna go back into excel. We're gonna use thes day books as our basis. We're going to tidy up the day books were going to look at the former writing of the day books. We're going to look at the formulas in the day books on. We're also going to look at analyzing these day books to produce some meaningful information for you. 13. Introduction to working with Data: welcome to Section Tree, which is about working with data. So in section One, we introduced you to excel. We introduce you to a work book and we showed you around a workbook and how to enter under lead data. And in section two, I gave you some sample day books that you could down order. We're gonna work from this directive section by the end of the section. What you'll be able to do is insert and remove cells and rolls. You be able to marriage, split and move cells. You be able to use paste special, finding more place, hide on on hide. We're then gonna break into another lesson where you're going to learn how to use basics, our references on basic formless. I'm gonna show you how to run a spell check. And we're also going to look at formatting texts and sells on. Don't forget, we'll be looking at all of this in excel on and on the theory side. So let's hop into the first off. Two lessons in the section on working with data 14. Working with data 1: Welcome to the first lesson off Working with data. What we're gonna do is look at inserting and removing rows and columns, marriage ing and splitting south and moving cells. We're gonna look at the paste special. We're going to look a find and replace. I'm not going to look at hide on on hide. So to insert a new column right click on the column header and she was insert on a new A column will appear to the left off your selected column to insert a new row, right click on the road, header and choose insert, and a new role will appear. A both selected world to marriage sells. What you need to do is select the cells you want to marriage. Click hold ribbon, then click the marriage in center dropped in oh, then click Marriage sells. The selected sales have now become one large cell Soto on marriage them. Click home, click it marriage cell, drop down arrow and then click or not marriage cells. You can also move cells, so to move sounds. What you need to do is move your cursor to the bathroom border off the cell until the cursor changes into the move I comp you can then drag and drop the selected sell to the new location. So here we are now in our petty cash daybook, and the first thing we're going to look at is inserting a war A column. So if we kick on the d here, if we right click on if we then say insert, it's going to insert a new column to the left and in here we need value because we need the total value and I'm gonna show you how to calculate the value Later on. We also looked at moving a cell. So if we select the cell here on you, see if we move until we get this little cross here, that means that we can move the sale on. We can then just drag it on, drop it. And we looked at inserting the column here, but we can delete a row. So if you select the role header white click on Delete is going to delete the row. And we also looked at marriage ing cells. So if we take this petty cash day book title highlight all the way across two K, I'm in our home women under alignment. We have marriage and center. I'm going to click marriage and center on. What that has done is that now are one cell on. We have centered the text in the middle off. That particular cell pay special is a great function and that allows you to perform tasks that may be tedious to do in other ways. It also pastes data and allows you to perform tasks on the destination cells using the piece to data so selected cell that you want to copy, right clicker unpleasant copy. Select Earl of the Sides containing the data to use. Why click the selection and click special Pieced or paste special? Set your options on in here. We're going to select values. I'm going to say multiply and we're going to select okay on the selected values will then reflect all the changes. So the pay special is a very, very useful to the find Amber police is on the home tab. So if you wish to replace some text you click the home tab, then find on select and then we're police or the keyboard. Shore put court is control and teach you type in what you want to find on what you want to replace it with. Unclip replace all, and when the process is complete, click OK To confirm that result, you can also hired on on hide cells. So we'll click the header and choose Hide on. The column on its data will be hidden from view toe on, Hide the row or column right click on the column header and click and height in your damn low document. There is a worksheet called Spaced Special. What we have here is we have some values and we have a multiplier on. We want to multiply all of these values, using the multiplier as the value. So if we select this out right click on press copy. If we then select all of the's cells here and then right click on, say, special pieced, we want to click the values with the operation off more supplies. It's gonna take event values and multiply that by 1.2 and select okay, on a lof, the values will be updated now. We also looked at a find and replace an honor home. Women all the way over to the right is our find and select or find and replace on. Then here. If we say we're place, let's look for the number 1.44 and replace it with the number 500. Replace out them Replacement was made and you can see appear that's changed to 500 Better looked at, hiding on on hiding. If we right click, we can hide and you'll see it shrunk up that we can pull it down this way using your mouse . So to do this, I left click on drag where we need to select the two of these and then select on hide, and it will on hide the road that it it has hit. 15. Working with data 2: Welcome to the second lesson in working with data. We're gonna look out here is basic formulas on basic cell references. So I'm gonna show you how to enter the sun. Former Andi, I'm going to show you how to enter the summer multiply formula when we movinto actually excel were then also going to look at how to run, spell check and we're going to look at formatting some text and sells. Let's have a look of understanding cell references and formulas, the same references found by examining what column on what Hedda Row that the cell falls under. So to find a sales reference, select itself and then examined the name box. So in our school and plenty, we can see that this answer is so see. And if we go too close to its the intersecting point between the row on the column, sigh references can then be used. Informal US firmness can combine values from different roles for different columns. So, for example, in this we have a units and we have costs and we have total cost. On the furniture that we've put in is a two. So Sarah funds a two multiplied by be to. We would do this by entering that into the formula part, and then we would press enter or we click the check mark icon on the formula bar toe, apply the formula to the Children. Sells almost air functions are pre made operations that use input cells to produce outputs . For example, the sum function calculates the sum total of all cells. So to do this some function what you would do is select a cell types, Um, a breath, a bracket, it select cells. And then you type equal some into the formula bar, and you click and drag over the cells. UNCLOS, the bracket knew, would press enter. In Excite 2000 and 13. There's over 300 functions on their found in the formulas tab on the formulas ribbon. So let's look out, adding the some formula into our petty cash daybook and our values here should be the tunggal off our description. So we need to put in a some so we could say equals and I'm doing this open the former's bar some I typed in some I opened and closed my bracket. Now I'm using the most I'm dragging cross close my bracket on, then I've hit Enter. So what, that has no Joan is That's got the Toko off actual purchase. Now we can do that again, equal some and drag it across. Enclosed my black it hit. And you know, if you have a formula like that, you can also just drag it down to fill it down instead of entering it in every way. Just to show you on the formless bar, all your functions on all your formulas can be found in here your full functions. I'm Formulas Library can be found in here using spow check. You can check the spelling of a work sheet by clicking, review and spelling or by pressing F seven. Click Yes to complete the check from the beginning on, use the dialog box to reserve any errors or ignore and want you complete. You can click finish. You can also format text and sells on. This is done in the home with. You can also get into formatting by pressing control and one and you conform at womb cell or number sells at one. So you just need to select the sounds that you want to former. Changing the fund type the size the color. All you need to do is select the south. Click the front menu on the home tab on a near the options that you want to change onto clear any formatting. Select cells and click Hold, then click clear. This will remove formatting contents off the south will remain unaffected. But remember, when you use clear formatting any number formatting such as current accounting, your time is also going to be removed so we can check our spelling on our view top and we have spelling over here. Do you want to continue checking at the beginning of the street sheet? Say yes. I didn't find any spelling mistakes, so we're good to go. We also have our for a Manning in here, and we're gonna do some formatting now. The first thing we're going to do is we're going to change these values and we're gonna actually change these two currency to see how I don't that straightaway there from the home tab. I'm also gonna take all of these values across here. I'm gonna make them bold, and I'm going to send to them in the particular column just to line them open. Make the sheet actually look better our petty cash daybook. I'm gonna make this writing bigger and I'm also going to make it bold. Another thing that we should look at is this days over here on you See, if we could become the days here in a two pirates showing the correct a format that we would like this is down to formatting. So that we're going to do is you can copy a former on if you go to the home weapon format painter and paste the format into the next cell and that have actually pasted the format force. There's one last thing that we're going to do, and that's another formula, and we need to put in our sales tax. So our sales tax, the formula is going to be the sum of this multiplied by the tax rate. So let me show you have to enter that former so it's going to be equals the some select all the sales and then multiplied by the tax rate on enter. So let me just show you that again. So equals some on I opened my bracket, I put my cursor in here and I dragged across close my bracket. And then we want to multiply that by our tax rate, which we have in column E and hit. Enter on. That's Kark. Elated the tax on this, which then meant our value updated. So keep an eye on the values. This time, as I feel this formula down, you see the way these two values updated on this one didn't because the sales tax rate is zero. So I want you to do now is I want you to replicate the petty cash daybook in the download that you have so now looks the same as the petty catch daybook that I have. 16. Organizing and sorting data introduction: welcome to section for organizing and sorting your data. By the end of this section, you'll be able to insert and remove tables. You be able to use filters and use lists. Will also be able to use data validation to control the user input. In addition to this, we're going to use the auto some and auto fill. We're going to use flash feel and we're also going to use auto complete on to wrap up. Then in the section we're gonna look at inserting headers and footers. This section is broken down into two lessons, so let's hop into the first lesson on begin organizing on sorting our dad. 17. Organizing and sorting data 1: in this s and I'm gonna show you how to insert and remove tables. And I'm also going to show you how to use filters they were going to look at is inserting tables first, So two inside a table. The keyboard shortcut is control and T to delete a table, but to keep the data you need to right click. Select the table, then select Convert to range. Filters are automatically added to tables, and you can also turn on all filters from the data ribbon on. You'll recognize filters from the down arrow on the cells. Click one off the comparison operators, for example, to show numbers within a lower and upper limit. Select between on in the custom auto filter box type, or select the criteria for filtering your data and then select Okay. Comparison operators are the only way to filter by criteria. You said you can choose from items from a list or search for data. You can even filter data by self color or font color. Let's look now are inserting a table with our petty cash daybook data. So let's select the data that we want when you're selecting data for a table you have to make sure there's no empty columns or roles between the data on if you press control and T , it says, Create table. So where is the data for your table on? You'll see these moving lines going around. You can also select. My table has headers as we do have headers in the table and select. Okay, if you want to undo this table design in your designs, slicked back convert to range on its going to select it back to a normal range. But our table formatting is still there. So again, control and t your key Portugal could for table on in your design ribbon that you can convert it back to the range. You'll also see that you have thes filters on the filters will allow you to sort true your data pretty quickly so you can filter by color. You can filter by number. You can do a particular surgery can select by the box. There's also different filters for the sort eighties ed and stuff for when you're using. Text filters are an automatic feature when you're working with tables, but when you're working with normal doubt it. So let's convert this back to a range Our filters air gone weaken Turn on them filters by going to data built er turn on the field here I'm filter to turn off the filter just to let you know as well when you're working with a table To insert a new roto a table you need to press the tab key to move down to a new row in the table. 18. Organizing and sorting data 2: a list is any row or column off data. This could be used in doubt. A validation on lists can also be converted to tables. You can define names for cells, tables, lists from functions. You can define names by selecting cell or ranger cells on in the name box type the name you have chosen. You can then use this name in a formula and Jim to that area of a workbook very quickly to Jim to the area of a workbook with a name. Press F five. To bring up the go to box, select the name you want to doom to and press OK on. You'll move to directly to that area off the workbook. Manage names can be found under the formulas ribbon, and in here you can add edit under lee names to use name in a formula. Type the name and you will see it in the tip box. You can then press tab to selected from the tip box. Data validation allows you only enter pre defined data into a cell. The data validation commands are located on the data tab in data Tools Group on you configure the doubt a validation in the data validation dialog box lets you configure your data validation. You can only enter the pre defined value off the cells. If you try to enter something different, you will get a warning. Let's quickly look at defining and name with the's sales tax. So if we select this here as our sales tax, But where we can do now is we can give this a name and had entered to select the name. Now we have selected the name. Now let's go back to our petty cash daybook on in here. If we press f five on, we'll see. Now we have names in here. We can select this press. Okay. On weaken. Jump straight in here too, Aren't name. If we goto our formulas ribbon. We can also see our name manager in here. And then here we can We can add a new name, added name or delete names so you can basically manage your names in here. Now, let's look at it using the's names in a data validation list. So here we are now in our petty cash table. Come. What we're gonna do is we're gonna add data validation to our sales tax rate. So when the user is importing, they can only select between the pre defined list of sales tax rates that we set up a few minutes ago. So data validation is found on the on the data ribbon under data tools. So first select the cells, then go to data and data validation on select Out of validation on in Here in settings. What we want to do is allow a list, and we know our list name equals sales tax and select injured. And now what we have is thes little drop downs where we can select between our sales tax. Now, in addition to this, we can also put in an input message. Please select the correct read. Please select the sales tax rate from the list. Onda. We can also put in an error alert. So when they make a mistake, please select a rate from the list provided and select. Okay, so now we have an input message when the cell selected, please select the correct read. So let's select the cell. Let Vickery's on the reader's fine. Now let's try and put in a different route. Let's try put in 25% on it says, Please select a read from the list provided retry. And then I can go to my list on I can pick from the list. Now you'll see there that I have selected 13.5% for for summaries and 14% to showing up here. This is down to formatting, and you can see the actual value appear. So if we go to our home, I'm gonna highlight themselves. And I'm just going to increase the decimal place so you can see the actual decimal place. So now if anybody is entering into this day, books they have to select from the pre defined list that you have set up and that's how you used out of validation. 19. Review: you've now reached the end up section for, and I hope that you're getting along just fine with this course and should now be able to insert and remove tables. You should be able to use filters, use lists and use data validation. You should be able to sort your data, and you should be able to use auto feel flash feel. Order some on auto complete, and you should also not be able to insert tatters on insert filters in the next section. We're going to look at analysing your data, but before you move onto the next action, make sure that you carry out all the practice examples that I have set for you. 20. Introduction to Analyzing data: welcome to the fifth and final section in this course analyzing data. So by the end of the section, you be able to insert recommended charts, and you'll also be able to insert recommended pivot tables, will then use thes pivot tables toe analyzed out of by inserting slices on inserting timelines. 21. Analyzing data 1: charts are a great way to quickly analyze your data. So let's hop into example now and have a look at how we can insert some charts. So to insert a chart, the first thing you need to do is select the Ranger South and then click insert on select recommended charts, scroll through the list of recommended charity and select the church that you want to impress. Okay, you will then get some new ribbons. He'll get the design and char tools, which will allow you to modify the chart, and you get these option bulletins next to the charts to open up the task pane. So here we are now in our sales on sales returns or sales credit daybook Andi, As you can see this day book contains some additional detail I'm dependent on the company you're working for will depend on how much detail they want to include. On the extra detail that's now being included in This is the Sales Record column. We've also included a net sales column on this particular company has some MAWR products. They've training services on labour services on. I've added all these for the purpose of showing you additional items that you can keep in your day book on. Also for showing you how you can analyze your data. So let's look at inserting a chart. So if we select her doubt and go to insert on select recommended chart on what Excel will do is recommend list off charts for you and give you detailed for them. So the 1st 1 of showing us some of value by customer and we know our value is there grow sale. So it's going to give us a clustered column chart, which would compare, Finally, was across a few categories. We also have some of net sales by custom, or and this is a little bit more what we want so we can go in and we can select some of net sales on. We'll see we have a new chart after opening. Look on a new shoes on. We have thes chart options on We can go in here and we can design our chart, and we also have our options here for our chart. Elements are access titles or dot and labeled we can change. All these from within here are styled on our colors and so forth. So that's the basics off inserting a chart 22. Analyzing data 2: tables are fantastic function to allow you quickly analyze on view data in the way that you want to analyze it and viewers. So in this section, we're gonna look at working with recommended pivot tables, inserting slicers on inserting timelines. A pivot table will allow you quickly sort and analyze your data. Select the table or range of darker and then click insert on select recommended pivot tables. Scroll through the recommended pivot tables. I'm select a pivot table and click OK on Excel will create a worksheet on. Insert the new pivot table. Using your data on the table configuration, you selected the pivot table fields. Pain on the right. Off the screen modifies the diver that the pivot table will display to filter the pivot table. Just drag field into the filter zone after you bother the filter. Ah, field will appear that you can use to sort the data. So here we are again, back in our sales and sales returns day book on Let's say we wanted to quickly find all off our sales by Let's a sales rep Allyson. We can do it this way. Now if we also want to, then filter by her her tax worker sales tax rates. We can filter in two steps, but that's quite complicated on it. It's very hard to see a summary off the actual information. So what you can uses, you can use a pivot table. So to insert a pivot table, go to insert on, then recommended pivot tables on. What this will do is this is going to recommend the number off pivot tables to you. Weaken. Select any one of them here, some of net sales by customer, some off sales tax, um, of net sales. There's a few items in that one. Let's just take some off net sales by customer, and then we will pip. So this has world. Aren't net sales by Costa merge into a little table? And over here we have our pivot table fields. What we could do is we could add in a filter for the sales record. At the moment, this is all the sales by all the customers by the sales reps. But if we go in here and we just select Alison, that's going to change and just build her. The results by Alison Let's do it by Peter on. We can filter just by Peter. You could also add in another filter, for example, sale sales returns, and we just drag that down to filter. And at the moment we have all sales reps on. We have all sales UN returns. Let's change that to just returns on little. Just bring in the net value off the returns, change its sales, and it brings in the neck value off the sales. Change it toe all, and it brings us back to the total net value. Slicers air, then a great way off analyzing pivot table data further. So to use slicers, clicking a pivot table and then click pivot table tools. Then click. Analyze and then click. Insert slicer The inserts lighter dialogue. First, select the fields and then click OK slices will be stacked on top of your worksheet. So to see what's going on with a field, just click on a field title in this life, and then you can clear the slicers by clicking on the filter symbol in the upper right hand corner off slicer box. You can also create timelines of to create a timeline, place your cursor inside a pivot table and click insert and select timeline the insert timeline Dialogue will box will appear. So just click. OK, select the fields and click OK on the timeline will then appear on your work. Chief, click the drop down menu in the timeline paid to view on select options. So here we are now back in the pivot table on What we're gonna do is we're going to select , analyze on in, analyze where they're going to select, insert a slicer, and we're going to use our sales reps, other slicer. And we're also gonna put in sales and returns as we did with air filter on these two. Then appear stucked up on top of each other and you can move from Rangers screen as such. Now if we want to just look at Alison sales. We just practise Alison, and you can see then that Jon and Peter has come white on. We've also noticed that or over here slightly lighter than the S, which would suggest she's had no returns. Let's have a look at Yarm on. Alison and Peter have gone white on the sales and returns are showing up both in the same color, which means he has a both the sale or return so we could just select Return on. This will bring the stand and just show us the actual returns. And we can clear these filters using this little arrow box here. Now, what else can you do? You can also insert my wife so we go back into analysis, are waiting select timeline for our future and we want to select date because we have a date column and select Select. Okay, Now, what this is doing is this is given us all of the months of the year. We could do it by months, weaken dio by quarters. We could deal by days which would not go to It's too long. Let's go back in here and do it By months on we could you select January. I'm then using a filter. We could just like January for Alison. January for John. We could select February January or we could select February for Peter or for Alison. Well, for Jim, we could then also so do it like waters on the little polling data for the quarters. And we've nothing in 43 So pivot tables are a great way to sort of analyze data quickly different pieces of information that you would like to see from your day books from the goalkeeper has entered for you. 23. Analyzing data: you've reached the end of section five analysing your data, and you should not be able to insert a recommended chart. You should be able to insert a pivot table and then analyze your data using slicers on using timelines. We do have some practice examples for you to carry out Now. If you have any question there any comments, please do post them on the discussion board. 24. Conclusion: you have now reached the end off this course, Onda. We thought you a number of different things through this course. We taught you the very, very basics of excel. We showed you what a workbook is. Have to move around, workbook. We, then Georgia, how to enter data. We showed you had to work with the data that you've entered. And we've also showed you number tools to analyze your data. All of this. We showed you true. A set of day books that we also talk to True. So if you need to record business financial transactions, you can refer back to these day books. We now have an updated a book for you to download wound that has some additional four mounting an additional tool. So why don't you take your day books and see if you can get your day books to look a little bit more like the day books that I now have. If you have any questions, please don't be shy in Post Mom Discussion board as I am here to help you. I really, really hope that you enjoyed this course on artsy in another course