Microsoft Excel Basics - Creating a Sales Report

1. Introduction: Hi there and welcome to this Microsoft Excel Basics course. This course has been made for beginners and it's project-based will work to get it from beginning to end to complete a task that had been set as if we're in the workplace. This task will be a simple task which is going to be to complete a salvageable. Throughout the journey of completing this task, we will learn some of the very basics of Excel, such as absolute and relative cell references. We'll learn how to create a basic table and how to make it look good with some formatting. We will also learn how some of the basic formulas and functions work, such as sum, average, and count. And finally, we'll put it all together with some great looking charts and graphs. So if that sounds good to you, then I look forward to seeing you in the course. 2. An Overview of Microsoft Excel: Welcome to this beginner's introduction to Microsoft Excel. I'm currently using Office 365. However, this tutorial will still be relevant for all versions of Microsoft Excel. But it's probably best if you go version that's later than 2016 at least. So we have a blank workbook here. And at the top I just want to draw your attention to the ribbon. And this is the big bar up the top. Generally, when we're working in a blank workbook, would normally be on the home tab of the ribbon. Different tabs on the ribbon that we can go through. And we'll be looking into them in more detail as the course goes on. But for now, the Home tab is where you find the main buttons such as Cup. Copy, where you can bold text, highlight cells, change the color of text, the alignment of texts, different font formatting, and conditional formatting, inserting and deleting rows. And as we go along we can see the Insert tab. Obviously this is where you can insert items into the workbook, such as shapes, pictures, charts, and maps. And then we've got the Page Layout tab, which is where we organize our page. Make sure it's laid out in a nice design and style, such as the margins or the orientation. Ok, and then we've got the Formulas tab. I didn't really use this too often because you normally manually write your formulas in the formula bar, which is just here. However, you can use this to create formulas if you wish. Ok, and then we've got the datatype. So this is where we would import data. So for example, we can click on Get Data and we could import it from whichever type of file that we want. Such a workbook, a PDF, or a.txt or.csv file. We can also import data from the web or from other tables. One of the main things that I normally use this tab for is data validation. But look at that later. Ok, so that's mainly for the tabs at the top there, the main sort of things that you would use those four. So this is the main work area, is where all of our cells are and each cell is referenced. So for example, this cell is A1 and this would be b1. And then if we went down the rows, it would be b2. And across the column, for example, again, it would move to C2 and you can see it changing up here. In this box. We've also got different tabs inside an Excel workbook. And if we look down at the bottom, we can see this is a tab. We can change the name of a tab just by clicking in it. For example, we're going to be creating a self reports. So we might want to type in Sales Report. And then if we just click away, it saves the text in there. We can also add new tabs by clicking on the plus. And as soon as we click in, a new tab will come along. And obviously at the moment there is nothing in EVA. But you can, for example, create some data in this step, jumping to the next tab, and this is a blank sheet. So we can put data in there to let's just delete that for now. And then to delete the tub, or we need to do is right-click to delete and then delete. So I also wanted to just quickly bring your attention to the Quick Access Toolbar, which is up here. And this Quick Access Toolbar gives you a few little functions that you can use to make your life a little bit easier if you wish. For example, you can quickly save a file, undo, redo. And I've put M one which is Save As if I wanted to save as file in a specific location with a specific name for example. So if you want to customize this and add more, or you need to do is click on the little down arrow. And you can select from some of the main selections here. For example, you could pick email and this would give you the functionality to quickly press on here. And then I would bring up your email client and you could send this workbook off in an email very quickly to remove it, or we have to do is click on it again. There's also more commands. If you go down here and click in the More Commands section. And you've got plenty to choose from, from here. Alright, so let's close this. One other little bit to just be aware of is down here in the corner we've got the zoom bar. If for example, our text is quite small, we want to zoom in or we need to do is click on the little plus and it will enlarge the size of the cells. As you can see. 3. Working with Text and Formulas: There's the first part of a four-part calls and each course has its own scenario that we'll be working towards the giver. So we're going to start with a blank workbook in this course. So there's no need to download any files. You're going to just work along with me as we go through it. So let's take a look at our first scenario and what it says. Dear colleague, I would like you to prepare a report based on the first quarter cells results. It should contain the following analysis. A monthly breakdown for our cinema, restaurant, bar and shop. The high sales amount, lower sales amount, an average sales amount per site. And a bar chart showing the total sales per site. I've also been made aware their shop is not performing as expected recently. Therefore, please provide me with a chart that outlines a decline in its performance. And once you've completed the report, please print it and leave on my desk. So we need to make a monthly breakdown for the cinema, restaurant, the bar on the shop that shows the south. So let's try and do their first in a little table. So from my experience when I've been sad task in the workplace, I think it's always best to get the data in the spreadsheet first and then focus on the design of the table. I think it's best to do it this way because it will save you are adjusting the table's format if you don't get the sides of the table right first time. So let's start with the basics and put the numbers in first. And then we'll look at the formatting of the table after. Because then we know that should be the final layout of our table. So let's put a title him first. So what we're going to do is just bring this column in a little bit and just type a tied to on the top. We're going to call it sells report. And to make this down down, we're going to bold and underline it. That makes it clear that it's the title. So little shortcut to bold text is controlled and b. So you can press that, put it back on and controlling you for underlined. So control you control you. Okay. Now that we've got the title, but let's move down a couple of cells and start preparing our table so that we can put in the financial results. So let's start with the titles of the table. So the first title is going to be sales has it's a sales table. And then let's put the mumps in. So it's the first quarter. So let's say January 2020 will move along. February 20, March 20. And we'll put a total column on the end. Now we need to put the Sal's units into. Let's move along and put in the first one, which is a cinema and the restaurant, the bar, and the shop, and will also put total on the bottom. Now we can see that this restaurant is likely going over. This line. T is just going over. So to enlarge all of this column, all we need to do is double-click on the end where we see this cursor come up. You can see the change just there. So double-click and I'll enlarge this column to the largest amount of text that you've got in a cell in this column. So as you can see, it's fit. The biggest one we have, which is the title. So now let's put the sales and Alps in. You can make numbers up for this if you wish, but it's probably best if you follow me along so you can compare your results with my results. So let's start off. We have the cinema in January and let's say 15 thousand. And then in February, 17 thousand and March 19 thousand. Let's move down to the restaurant. The restaurant is 5 thousand for January, 4 thousand for February, and 6 thousand for March, and the bar, 4,500 for each month in the first quarter. The scenario says for the shop that there's a decline in performance. So let's bake it decline. So we'll use 2 thousand in January, 1500 and February, and then it goes down to 11200 in March. As you can see up the top here. And dates are formatted by themselves. And it is clearly showing up as an abbreviated date. So he said it's a custom short date if we want to change the format of the date. So all we need to do is highlight them. And we can click up here into the number former area. And as you see, it can give us two choices, short and long day. So let's pick an edge. You can see it brings numbers back, but we don't want that. We want to make a custom date as it wants. So let's go down to more number formats. And let's track this over. And we want to have a quick look through a days to see if there's anything that we want in here in the date section. So this one looks similar to what it was. And if that's what the one we want to use, we can just click OK on that and they will all change back to that. So that's an easy way of just amending the dates. If you need to know to start formula in Excel, all you need to do is press the button and then you can type in a number for example. So let's add up this column. So if we type in our first number, which is 15 thousand, and then you can press plus 5 thousand, plus 40,500 plus 200 thousand. Then we'll get the total. So we now have the total of this column, which is 26,500. But the only issue with this is that if one of these numbers in this column must change, this total will not change in line with that. So as you can see, if we were to delete the 4,500, the total still remains the same. That's just control and lead to undo that and bring it back. So what we really want to be doing is creating a formula that can change by itself if any of the numbers are amended. And the way we can do that is we can start the formula again with equals. And instead of typing in the manual numbers and using a calculator, we can reference the actual cells that we want to add up. So if these cells change, the numbers are still picked up. So we can start off with this, his cell S4. So for example, if we were to type in C four, it would pick that setup. Or to make it easier, we can just click in the cell. So if we press plus and we go for another cell, this one will be C5. We can click in S5, And as you can see, Excel is picked up S4, S5. And let's just add the last two. So C6 and C7. And now we have the whole column summed. So again, if we were to delete something from the column, let's remove the 4,500 again. What's the total? You can see that it changes by itself. Then let's undo that and bring it back. So instead of. In the cells manually because sometimes there might be lots and lots of data, lots and lots of rows and numbers. You can use the sum formula. So this is a very basic function. So let's quickly use that. So to start that, all we need to do is press equals type in some. And it tells you a brief description of what it does. It adds all the numbers in a range of cells. So then we open the bracket to start the formula. And then we just highlight exactly what we need. So now instead of it being c4 plus C5 plus the fixed plus the seven is called a colon there instead, which is a range C42, C7. So we're summing this whole range. And then to finish this function, though we need to do, is press the close bracket and then enter their accent was very clever and can make your life easier. Now once you see this plus symbol, or you need to do is click and drag it too long. And it will carry the formula along by itself. And as you can see, if we double-click in here, we can see it's setting up the correct numbers that we want it to, which is perfect. Another benefit that using the sum function has is that if we were to insert a new row, for example, say we had a New South site that came into the business and we wanted to insert there and put the sales figures in. So we highlight the row, click on Insert. And then if we were to put some numbers in, you would see that it still picks these numbers up and it changes. And as you can see, if we click into the formula and the formula is expanded in line with the increase in the row. So it's gone from C4 to C7, S4 to cAe. Okay, let's delete that and put it back because it was just highlight the Row and click on delete. So let's finish off by adding the totals for the F column. So we will just quickly put the function for some in equals sum. And to make it a little bit quicker, once you've written some, you can just press tab and that automatically initiates the function as well. So highlight these and then close the bracket. So something else we can do rather than dragging the cell down and we see the little cross is we can control and see which its copy highlight where we want to paste. And then control V and paste the formula into where we want it. You can see it does the same thing as dragging the cell down. And let's just copy this into the bottom one as well. So we know that from earlier, a relative reference moves in line with where we copy and paste or drag the formula two. But say we want to keep a cell locked, then we need to make that an absolute reference so it knows not to move anymore. So let's take a look at an example. Let's say I want to know what percentage of sales the cinema was for January out the total cells. Now I compress equals to start the formula. Click into the cinema, then press the slash, which means divide and divide it by the total, and then press enter. Now, I can see that this is a percentage. It's about, it rounds up to 57%. So if I click in there and I just clicking the percent number format, we can see it changes to 57%. Now, if I were to do the same thing as we did earlier and drag this down, you can see we get an error. And that's because we've moved the cells relatively down, one for each. So this cell has gone down by one. Therefore, both of these to move down by one. But we don't want that to happen. What we want to happen is we want this cell to stay in the total and this cell to move down. So how would we do that? Let's delete this and try. So all we need to do is lock the cell that we want to stay where it is. And that would be C8. So we want see eight to stay there no matter where we copy and paste or drag or anything like that. So how do we do that? We click into the cell and we press F4. And now that will walk through row and the column. So we can see the Column C is locked, so that's not moving anywhere. And the eight is also locked. All we need to do is press enter. And now what's the difference? As we drag down? C four moves down S5, But CA is locked, so it stays where it is. And that will be the same for the rest of them. So let's just drag this down. And as you can see, S4 has moved to C7 and C8 stayed where it is. So that's the difference between absolute and relative references. 4. Using Basic Functions : Okay, so the next bullet point that we need to complete from the requirement is that we need to see the highest tells Mount, lowest 1000 MT and the average sales amount per site. Now, I used formulas all the time in the workplace. They're so important in helping you get tails completed more efficiently. So I strongly suggest that you try your best to learn these formulas from the start. If you're new to Excel, as it would definitely help you out in the long run. There are also lots of shortcuts that you'll see me using this course. So try to take note of them as well. Another suggestion is to try and get away from using the mouse as much as possible. Try to make a habit of using the keyboard. Because if you can use the keyboard for most of your work in Excel, you'll be so much more efficient and so much quicker. So let's do that. Let's type them in. Highest, the lowest, and the average. Perfect. So now let's look at a new function. So we need to get the highest value for the cinema. So what we'll do is we'll use the max function. So again, press equals type in max, and as you can see explains, it returns a largest value and a set of values. So we're going to open the bracket and we're going to highlight the cinema. And then we're going to close the brackets. Perfect, we have a largest number, as you can see, it's 19 thousand. Perfect, it's done the job. And we want to do that for all of the sites. So let's just drag this down and now we want to get the lowest number. So again, that's just typing equals. And this time it's called min. I'm gonna hit tab. And I'm going to highlight all of the values for the cinema and close brackets. And I'm going to, instead of dragging down this time, I'm gonna show you another little shortcut. And all we do is double-click when we see the cross. And it's even quicker than it does it for us. Now let's move on to the average. So we wanna get the average value from January, February, March, and their problem as expected, the same again equals average. This returns the average. Hit tab. I'll let them more close the brackets, press enter. And then that's just double-click and drop that, drop that down. And there's one more function that I just wanted to show you, just so you know, it's here. And that's called the count function. And all this does is count how many items there are in a range. So that's typing count. And it counts the number of cells in that range that contains numbers. So how many cells, for example, in this rained contains numbers? I can see that's nine. So it should come back with a nine. When I close the brackets. Perfect. This is mainly used with nested IF formulas. So it's a little bit more complex, but we'll see that later on in the more advanced courses. 5. Amending Data: So now let's have a quick look at data inside the worksheet. Now, as I showed you earlier, let's say we wanted to add another row. And we would do is highlight the row by clicking in bar number five, for example. And this highlights the whole world right to the end of the worksheet. And then we would click, Insert up the top here. And that would create a new row for us. And if we wanted to delete it, we can press Delete. And as we already explained earlier, this also will increase the range in a some formula or any of the formulas. And the average, the lowest, for example. So let's undo that. We've Control Z, and we can also do the same thing for columns. So if we click on column C and we want to insert a new column just before see, click on the Insert button and that you can see a new column appears. And let's just delete that. Now also wanted to show you how we can move this data. So for example, quite simply, we can just grab the data like this. Look for this icon to come up, this cursor, and then we click it and we can drag it around. So let's move it down here and you can see that you can move it wherever we want. That is quite simple. And let's just move it back. And instead of doing that, what we can do is cut this with control x. Choose where we want it to go. So click in the cell where we want it to go, which is a little bit of a little bit of a quicker way, and then paste it. And it'll go straight down there. Let's undo that. Obviously instead we can just take all of this data, copy it with Control C, and just paste it with control V, and we'll get an exact duplicate. Now, something to just bear in mind is if the formulas are absolute references as we looked at earlier, they will be stuck there when you copy and paste. So let's make this an absolute reference. So we'll highlight it and we'll press F4. And now this is locked. And now watch what happens when we copy and paste this. Can you see that this one is still linked to this old table because it's fixed. Whereas these ones that are relative references, they've come with the table. So it's just something to be aware of. If you've got fixed references in there, they will stay where you fix them. So let's delete that and, and that's just undo the fixed reference in there. And one of the thing that I wanted to show is that we can hide and unhide columns and rows. So for example, let's highlight the C column. What we need to do is right-click Hide, and that's C Column will disappear. And you can see that there's a hidden column because there's just this little bar in between the B and the D. Now to get it out, or you need to do is highlight around the hidden column, right-click and unhide. You could do the same for more than one. For example, we can highlight all of these rows right-click, Hide, and they'll disappear. All of the formulas will still be linked. And what changed? They're still be picking up from the columns that are hidden and just unhide them wherever I click unhide, another way that you can hide and unhide columns is highlighting the columns that you want. And go into the data tab, clicking group. And then you get this little ball that comes above it. And if you click on the little minus sign, it hides away and bring it back. To get rid of this, all you need to do is keep it highlighted or highlight them again. You can see the dots which ones are grouped, and then ungroup. And it'll disappear. 6. Formatting Data: Okay, so now let's have a look at tidying up this data. It was quite messy at the moment, everything that I've line. So first let's go and add some totals to make it in line with this table under here for our highest, lowest, and average values. But the reason I've left this is I want to show you a little shortcut, which is the autosome feature. And you can find the up here in formula. And autosome. Now we need to do is click in the cell where you want it to auto sum, go up to alter some, click on it. And it guesses exactly what you want toward to some, most of the time it's quite good. But the cells do need to be to give a thirt to pick that up as you can see as well. It's ignored the title, so it's quite clever. And then just enter and it's got it for you. You can do for more than one cell as well. And there's actually a shortcut for this, which is 4t equals and it does it for you just like that. So let's have a look at formatting the text now. So we want to make these tie tools all look a bit better. So for the titles, what we're going to do is highlight these. And we'll go into bold them. And we want to, we want to center the title. So we're just going to the Home tab, alignment area and click Center. Now we also want to do this for the B column. So let's just highlight the B column and central line that and in fact, we want to do that for all of the text and numbers. So let's just highlight the more AND just sent to them as well. Perfect. Now we want to bold the total rows as well. So let's just highlight them. Control and be as bold, All of the total rows. Now let's make the numbers look a little bit better as well. So I'm going to highlight this table. Control, hold control. If there's a gap. And you'll highlight somewhere else. And I want to highlight this table. And what I'm going to do is pick a currency for this one. So we're going to use US dollars. So what I'm going to do is pick US dollars. But I don't want the decimal places because it looks a bit messy. So we're going to go on here and decrease the decimal places by two. Now we want to bring these columns nicely aligned. So all we need to do to align the columns into double-click on them. For example, is total. There's space for this total one and we want, we don't want that. So if we just double clicked in here, it will come in line. And as you can see, there's also space for these, but it's quite slow doing it one by one. So instead, all we need to do is click on this little pointed out the top and it highlights the whole worksheet. And then any one of these, we can click in any one of these columns and double-click. And you'll see it will align all of them nicely. But now they look a little bit to type. So we want to increase the gap between these just a little bit. So let's highlight them all. And we specifically want to make just these four a little bit wider, so we highlight them and move the width slightly. Maybe a little bit more, probably about there. And that was a bit better. And let's just do the same for these free as well. And perfect as looking a bit better. And we also want to add some borders as well. So let's boulder this table and to make it nice and quick, we gotta highlight the table. And this is where we find our borders just up here. And we're going to click and little drop-down arrow. And we'll give it, gonna give it a thick outside border, which looks quite good. And for totals, we can do the same. Now we've already selected it. We don't need to click on the drop down arrow again. So let's just click in this little box. And that gives us our total here, a border as do the same for this total. And finally, Russia's border off the sales area. Now let's very quickly put some borders on this little table over here. All around. I think we also need a little border. Let's not speak for the titles. So let's just highlight all of the titles and give it a bottom border. Now let's just make this look a little bit better by points and color into the table. So let's take the titles. And we're going to pick a blue color. Something like this. Now we can't see our texts, so we just need to turn that to white. And there we go there standing out now. And now let's just bring these in slightly. Perfect. That's looking good. Now to make our life a little bit quicker, if we want to copy this format onto this table, rather than highlighting and changing the font color again, all we need to do is highlight an area of the same size. Click the Format Painter, and click in the first left cell. And as you can see, it does it all for us. And finally, let's just bold these totals up, down that. And let's just bring these two tables slightly closer together. Ok, perfect. 7. Creating Basic Charts and Graphs: Okay, so now we're on to the third in our scenario, which is to create a bar chart showing the total cells per site from my experience when creating bottles, two really important to make sure that we said a great color palettes. This will make sure that just stand out and look good. And the design should also be consistent with all of our other charts. So it's easy for the recipients to read and analyze our charts. We also need to think about the layout of our charts. We need to make sure that we think about where they will fit on the page before we print it. We don't want to print a messy disorganized report, and this is going to give us a bad image. Therefore, I think presentation is key in this area. So to get started on this, all we need to do is highlight the area that we need in the bar chart, which is going to be ourselves, our dates and values. And then we go to the insert tab, and we're going to click on bar charts and click a 2D clustered column bottom-up. Let's bring this bar chart over. Now this bar chart is quite plain looking. We need to make this look a little bit better. So first let's just change the tide tool. This is going to be the title for quarter one Monthly Sales. So let's type that in there. So I also think we need to bold this. So let's bold that control and B, and then let's just move it over to the left. Also, we don't need these numbers down aside because we're going to have data call us or show the numbers. So let's just highlight them and delete. These lines. Don't have to Great Eva. So click in there, hit delete. Let's make this chart for the whole width as well. So let's just leave it like that. So something that's important for charts is to make them stand out and look good. So we need to think of a good, safe colors that will work well together. These colors, a great graphic, we can do better than that. So let's pick the cinema to be the yellow color. So let's change the color of this cinema bar by clicking into a cinema bar. And I'm coming up to the colors. And let's pick this yellow, gold, gold color. And that looks quite good. And let's change the restaurant to more of a stranger to a black black color, maybe a darkish gray, something like that. And maybe the bar, slightly darker gray. The mortar already is something like that. And finally, let's change the shop to maybe a light blue color. And we want to put the amounts for each bar on top. So all we need to do is click into the chart, go to chart design. Add Chart Element, Data Labels and outside. And now to make these look a little bit better, let's just bowled them all. Perfect. So now let's make these bars are cool, a bit thicker. So what we do is pick one, double-click on it. Let's change the gap width down to about 100%. Is quite good. Okay, and finally, let's just bold the rest just finished departure off. So let's pull the date and the titles of the sites. Perfect. So now let's move on to the next requirement. We've been asked to provide a chart that outlines a decline in the shops performance. So probably a line chart is probably the best chart we can pick for that. So let's take the shops performance. So we'll take the sharp highlight that press control, highlight the titles, go to insert and let's look for a line chart. So here we are. 2d line chart, perfect. We can see the downward trend in the shop. Okay, so let's move this over. We're going to put it around here. Let's make it about half the size somewhere here. And let's change the title as we did before. And we'll call it the shop sells trend. Now let's bold the title that has moved this over to the left. Again. Let's delete the amount and the gridlines. Perfect. Let's make this a little bit smaller as well. Let's polled the titles. And let's increase the font as well because they're quite small. And suddenly a small chart. So maybe let's make this 12, perhaps a little bit bigger. Stri 14, that was better. Now we need data call x again. So we're going to chart design, Add Chart Element, data labels, coolers, and we want them above. Perfect like that. And it's bold those as well. So let's change the line color and we want it to be in line with the shop from the bar chart. So it's easy to understand that it's the same data. So let's click on it. We want to take the whole line, double-click on it. Go over here to format data series. Click on the field bucket. And let's change the line color to this I think is the second one down. Yeah, perfect. Also, we want to make it a little bit bigger. So let's increase the width to about 4. That looks good. Okay, perfect. Now we can see the downward trend. Now there's other types of charts as well that we can include in this report and I'm going to it's not required, but it's always good to do a little bit extra. So let's do another line chart. So we can very clearly see the data for quick to one per each site in a total. So we're gonna do the same again. We're gonna highlight all of the sites and highlight the totals. Go to insert, let's pick a barter. But we're gonna pick a bar chart that's on its side this time. Perfect. Let's track this in and resize it. Let's resize this one as well. We'll finish off the sides and once we've finished all of the charts, but let's just make it something like that for now. As per usual, clique the grid lines. Let's delete the amount at the bottom, and let's get this chart title. Let's call it let's call it total quarter one being the snails TO to q one business. Okay. Unless bold this, let's make it aligned to the left. As with the other chart. Now let's bold these titles. Perhaps it's making a little bit bigger. Maybe 12. Notice too much. Let's make it size ten years. Okay. Now let's feel the lines for the boss. So let's click in the shop. And that's Philip. Perfect. The bar is a grey color, picker gray color. This one. And the black color for the restaurant, that one, and the gold color Finally, for the cinema. Just up here. Now, all we need to do is we need to make these bars the same thickness, at least look a similar thickness as the bar charts above. So we click into the series, move over to the format data series again and again, bring the gap width down to 100 and see how that looks. Okay, maybe we can make that a little bit more. Let's try 80. Maybe a bit more. Try 60. Yeah, that looks better. Okay, so let's put the amounts inside the bars so we can tell how much sales had been made for the total of quarter one. So we go up to the top chart design, Add Chart Element, data labels inside base. Perfect. And let's make these or bold. So control B. Let's increase the font to make them a little bit bigger. Say about 11. But we can't see these totals in the gray and black very well. So we need to click inside these ones individually and just change the font to white. Okay, so it looks like we need to make this area a little bit bigger to make sure we can fit in all of the numbers in these two as they want and we can't see them now. So let's increase the column. To give us a bit more space. We just track them along a little bit. Like this. Looks like a little bit more. And we've got that looked good. Okay, and let's increase the size of this chart very slightly to be in line with the table above. And we will move these over a little bit. So I've clicked on them both and I'm using the right hour. And that's just move this one to the end. And I think we can make this one a little bit bigger. Let's get, OK, let's create one more chart and finish this off. And this chart is called a treemap. And we're gonna do that for the cool to one sales total. So let's pick the names again. And let's pick the totals. Let's insert. And let's go to this one here. Treemap. Perfect. Let's put it down on the bottom. And let's resize it to fit nicely. Let's give the title name. Caught one cells, totals. Unless bold, that leave that in the center this time it looks fine now. And we don't need these. Delete them. Let's change the colors very quickly. We wanna go to home and make this one that gold color. Let's make this one the darker gray. And the bar is the slightly lighter color. And finally, we just need to do the shop for the light blue color. And that's just bold. Titles control and be. Let's make that font slightly bigger so we can read them. Click on them all. Change these to about ten. And shop one. I think we managed to make this text black because we can't read it properly. That looks better. And cinema can also be changed to black. Bar form also looks a little bit small, so let's just change that to ten as well. So finally, let's just put some dark borders around the charts and line them all up and we're about done. So let's click on the chart. Go to Format, Shape, outline. Make that black. Perfect. Looks good. And the same for the rest. Let's make Demo Doc outline. We want to make sure that they're the same size, these two here. So what we can do is click into the chart and we can see the exact size up the top here in size. So we can see this one on the right is 2.32 inches high and 4.18 inches wide. Let's check the one on the left. 2.32 high and 4.01 width. So let's reduce this one down to the same as the one on the left. So let's reduce this down to 4.01. And now let's just line this up. And I think what we can do is actually make these gaps quite big in the middle. Actually make these a bit, a little bit wider. So let's go for 0.10 on them both. And let's line this one backup. That looks better. Let's bring this one up. Looking pretty good to me. And that's just highlight them and bring them all up a little bit more. 8. Printing a Worksheet: Okay, so our final requirement was to print their full. So let's have a look at doing that. What we need to do is come down to the bottom right of the screen in this area. So Page Break Preview, click on that. Now let's zoom in a little bit as we were on 60%. So let's go into about 100% is fine. And now we can see the breakdown of pages. Let's drag this out a little bit so we can see what's happening. Now these dotted lines are the area for the page. So all we need to do is move this over from this page. And let's bring this in a little bit. Now we don't actually need a page too. So let's track that over to get rid of it. And then we go, this is what will be printed. We click print. So we can do that by control and P. And as you can see, we have this printed in a nice area on the sheet. I think we can make it look better. So all we need to do is scale it and fit sheet on one page. And there we go. It's as simple as that to print a document in Microsoft Excel to make it look good. And then to get back to normal view, or we need to do is come down here and click back to normal view. One of the thing when designing charts or reports, it might be good to come to the Page Layout tab and remove the grid lines because a good length can be slightly distracting. So now we can see exactly what it looks like on a blank piece of paper. 9. Congratulations! Your Assignment!: Okay, so now it's your turn. I want you to use all your great skills you've just learned to create this report. And I want you to put them into practice with an assignment that I'm now setting you. Now as we know when we went through the course, this shop was declining. So we want to know why that happened and potentially what products cause that. So I'll want you to have a go at working out by creating some new charts and graphs, just how we did in this example. And this is the assignment that you've been set. It says the student, as we have seen in the previous sales report, that the shelf performance has been declining. I've extracted the results for the main sells items for the first quarter. And below is the table for the sales results for the first quarter for bread, milk, sugar, and newspapers for January, February, and March. And it goes on to say, I would therefore like you to prepare a report that outlines which items in the shop and not performing as expected. It should contain the following visual analysis. A chart that shows the trend of each product for the quarter. And I've given a little hint or line graph might be useful for that one to charts that show the volume of the sounds of each product in January, March. So we are trying to highlight that perhaps one product was selling a lot in January and then come the end of the quarter wasn't. So a tree map or a pie chart might be useful for this. And finally, a chart that shows the average sales of each product for January, February, and March. So you're going to have to use your average formula there. And at line in the chart, the average sales for each product in January, February, and March. And once you've completed this report, please upload it to the assignment section of the course so it can be shared with all of the other students have taken this course and we can all share our feedback. And finally, Thank you so much for joining me on this course. You've got any questions at all. Please leave them and I'll get back to them as soon as possible. And we'll see you in the next course.