Quick Start Excel: Getting Started With Excel | Jennifer Bailey | Skillshare

Quick Start Excel: Getting Started With Excel

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

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (1h 21m)
    • 1. Introduce Yourself

      1:15
    • 2. Naming And Saving a Workbook

      1:55
    • 3. How To Add Data To A Speadsheet

      3:53
    • 4. How To Create And Format Titles

      7:21
    • 5. How To Use The Autofill Function In Excel

      2:28
    • 6. How To Add More Basic Formula

      5:16
    • 7. How To Assign Cell Formats

      4:28
    • 8. How To Use Absolute Referencing

      5:50
    • 9. How To Use Auto Sum And Average Formula

      3:18
    • 10. How To Add Borders To Define A Table

      11:30
    • 11. How To Print Your Spreadsheet Print Settings

      9:53
    • 12. How To Sort Simple and Related Data

      8:09
    • 13. How To Insert Headers and Footers

      5:13
    • 14. Tip #1 How to Add Up a Column or Row With Shortcut Keys

      1:18
    • 15. Tip #3 How To Change The Names and Colours of Worksheet Tabs

      2:29
    • 16. Tip #4 How To Change The Colour of The Gridlines

      2:44
    • 17. Tip #2 How to Insert Current Date and Time Using Shortcut Keys

      1:02
    • 18. Tip #5 How To Change The Colour of Text and Fill of Cells

      2:33

About This Class

Microsoft's Excel 2013 for Windows is a very useful and powerful piece of software - but it can appear daunting if you have never used it before. Jennifer will teach you step-by-step how to create a basic spreadsheet (using the example of a basic timesheet). She covers how to format cells, enter data, autofill, basic formulas and absolute referencing which will get you started quickly.

By the end of this introductory course, Jennifer gets you feeling confident about creating your first basic Excel spreadsheet.As an example she uses a timesheet so that you can use this yourself to calculate your hours worked, overtime, net and gross pay.

Transcripts

1. Introduce Yourself: Hello and welcome to the course. I'm Jennifer Bailey from Jennifer s baby dot com, The quick start girl who likes to get things done. I'm gonna be your tutor for the course. What I'd really like to do, though, is get to know you a little bit better. I'd really like it if you could leave a comment letting me know who you are, where you're from, why you chose to enroll on this course on what you hope to get out of it. If you're facing any challenges on the course, for example, don't fully understand something. Or maybe your software is not behaving in the same way as mine. Then please leave me a message. Either in the discussion or privately on I can come back and answer it for you. I also really appreciate it. If you take the time to leave me some feedback on the course, maybe give it a rating so it can help other students choose this course of themselves. In the meantime, I really hope you enjoy the course. I'm Jennifer Bailey. Andi, Thank you for watching 2. Naming And Saving a Workbook: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to save a new time sheet by an appropriate name in a place where you know you confined at a later date. The first thing that I like to do when I start a new spreadsheet is to save it and give it a name and put in appropriate place. So I confined later. As you can see, it's automatically called Book One. But I want to give it a name that I can recognize, which will help me find it later date. So I need to do now is that you could file on, then save as. And here you can give it a name. So I'm gonna call this Pepsi. I'm gonna call this time sheet. And also, what I might do is after that put dates, it makes it nice and easy to find. So I'm gonna put the 13th of the 10th 6 And I know that is my most up to date time sheet, and I'm gonna click. Save on. This is where I'm putting it in my teaching documents. I t excel. So click save. Now you can see that's been named time sheet 13 10 06 Onda. We know exactly where it's being safe from now on, or you need to do is click on this disc button here to save your spreadsheet as you go or use control on s on your keyboard. Alternatively, you can come to the file menu and click Save in this lecture. I've shown you how to name and save a document and excel Onda How to save your progress as you're working on your document. I'm Jennifer Bailey. Andi, Thank you for watching. 3. How To Add Data To A Speadsheet: Hello. I'm Jennifer Bailey. And in this lecture, I'm going to show you how to input simple numerical data into a spreadsheet on also how to copy and paste them down columns as well as entering simple formula. The next I'm going to do is add some data into the spreadsheet. So, Officer, the hours work may vary from week to week, but just for the starting point on this spreadsheet, I'm gonna put eight hours in from Monday to Friday forces after day and two for Sunday, and then I'm going to put a rate pay in, so I'm going to £6.31. Andi, I want that to be the same up to Friday, but for Saturday, I'm gonna put in a time and 1/2 rate. So anyone who worked on a Saturday would time and 1/2. So we take this rate pay, and we're gonna multiply it by 1.5 to calculate what the rate would be. So in order to do that, using a formula we're gonna type equals on I could use this figure here. £6.31. So let's do that. £6.31 multiplied by 1.5. We push enter to accept that formula. So we now have different rates. So it's £9 on 46 a half p. And then for Sunday we're going to put in double time so we can do exactly the same again. So equals £6.31 multiplied by two. So we now have Tom and 1/2 on the Saturday on double time rate on a Sunday. Now, in a later lecture, I'm gonna show you how to make this a lot more simple. But for the purpose of this exercise, I'm just showing you how to create formula using a number. So we need to do now is we're going to have to copy this information down for the rest of the column because every single week is gonna be the same. The rate paid is not gonna water. So I'm gonna highlight the group off the pattern of information we've put in on by coming to the corner into the cross. I'm going to copy it down old way down the column and I shall see it's the spreadsheet is understood that I have £56.30 ones and then I've wanted this £9.46 a half on a £12.62. It's repeated it all the way down. This column says a good way off, copying a pattern off numbers or formulas down and list. The next I'm gonna do is we're going to do the hours worked. I could do a different technique this time. I'm gonna copy and paste it, so have highlighted the cells I want to copy. I come up to copy here, or you can put control C on your keyboard and you'll see it's got this dotted line around indicating which air is going to be copied. And I'll come to Monday here and I'm gonna push paste on. I'm gonna repeat this process all the way down the column and you know, so I just keep pushing pace. They don't have to copy again. There we go, Officer. This one is a little bit long. We don't need these last digits, so I'm gonna just push, delete and get rid of those. So in summary in this lecture, I've shown you how to add numerical data into a spreadsheet on how to copy and paste it so that formula and form, your patterns are copied down. A column. I've also showed you how to input some very simple formula. I'm Jennifer Bailey, and thank you for watching. 4. How To Create And Format Titles: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to add titles on subtitles how to use the merge and sent option. How to embolden Underline center change, color on wrap text. So the first thing I'm gonna do now is put a title on my spreadsheet. So in order to enter any information onto a spreadsheet, you need to click on the cell where you want that information to be entered on. All you need to do is click on the cell on this big black box appears on. That is where your information will be put. So I'm gonna put my title in cell a one and I'm just going to give it the title time sheet on this. You can see the information appears here on also appears here so you can edit it here and you can edit it here as well. Because if you have a lot of information sometimes using this bar, he makes it a lot easier. Because if you have something in the other columns, this will be hidden from sight. Where's up here? You can see a great long piece off space where you can put your information on. Then you can click OK to accept that text or you could just push the enter key on your keyboard. The next thing I would do because this is a title, I'm gonna make it stand out a bit more so I can push the B to embolden it. Or you can use control plus B on your keyboard. You can underline it or control, plus you on your keyboard. I can also make it bigger, too, so I can click this button here and make my title bigger. No, I should see my title doesn't fit into this column anymore. So there's two different ways of making the column wider. I can either hover my mouse over the join between the two columns, hold on my left mouse button and then drag the column wider. Or if you wanted to automatically perfect the information you put in the column. You just double click on the join like so now, because it's good. My title. I actually wanted to span several of these columns, so what I'm gonna do is I'm gonna select a one by holding down my left mouse button, and I'm going to highlight up to column G, but I'm going to use the merge and center button. So now this becomes one big sell rather than several. I'm now gonna add to the other titles or subtitles I want to use on my spreadsheet on. I'm gonna start these at a three somewhere type in date. And then I used my right arrow key to move across to the next cell. I use my right arrow key again to move across. So now I've entered my titles in and they're not looking that great moments we're gonna try and make thes took a little bit better. So I'm gonna start by highlighting my titles, making them bold in the same way you did before. And now we're going to either have to make the's columns wider. So I can do is obviously I can double click to make them fit the text like so. So what we could do instead is we could wrap the text. So, for example, where is his hours worked? I could select that cell and choose to wrap the text. So, as you can see from this helpful box pops up, it basically means that one word is gonna peer beneath the other in the cell. Now, as you can see, nothing's actually happened. It hasn't appeared to happened. But if I make this column smaller, the work has disappeared, and I can make that paid by making my row bigger. So what I need to do is come across between the three of the four on hold on my left mouse button on drag it and then my hours worked. Appears there. I think I'll do the same thing with the deductions as well. So to wrap the text on, then I'm going to make the column a little bit narrower and that appears now. They're not looking awfully even at the moment, so let's just quickly adjust some of these. When we start putting more data in, we can see what actual which we want them to pay. But it's making a little bit idea. For now. I think it may also quite nice to have thes centered, so I'm gonna highlight them again on this time. I'm going to use this alignment option you could have left and lined, which is what they are now. You can have centered on. You can have right aligned, but I won't mind to be centered somewhere. Click on this middle one here. I think I might also change the color of the writing as well from thes headings. So we cannot This option here This is where I can change the font color on I'm gonna choose blue if it like and also change the font to see if we come back to date and highlight the tub titles again. This is our font type here on. We have a whole long list off. Other options are available to say, Let's choose a Let's choose accord. No, I don't want that one. There's try Cam Bria. So now we have a different font. And if you want to change the size in with, are heading here We used this increase formed button, but you can also choose a slight size from here. So if we make them 14 for example 12. As you can see as we scroll through the sizes, you can see the effect that it has on your text. Seriously, that's 12. So in this lecture we looked at adding headings and subheadings how to use the wrap text and merge and center options how to change font face, font size and font color as well as emboldening on underlining. We also looked at how to make columns wider on Rose higher. I'm Jennifer Bailey. Andi, Thank you for watching. 5. How To Use The Autofill Function In Excel: in this lecture, I'm going to see how to use excels, auto fill function to put sequential dates and days of weeks into a spreadsheet where now can add the dates of our month into this column here so good for the first day to the months like So Now if I was to copy and paste it, I would click on the cell. But control, see, on my keyboard come to the next cell, put Control V. When then I would have a duplicate dates. But what we want is what sequential dates. So what we need to do for this? If you come to your cell, you click on it. You put your first date or your day into the cell and then you come to the corner. Andi, when you get the corner, your mouse point will change to a a plus sign. She now click and hold on, drag down. And as you can see, the dates are increasing as I scrolled down the screen. So I'm gonna stop when we get to the end of October. There we go. We automatically have the dates filled in. We can repeat this method for days of the week. So if you type in the first day of the week and then come to the corner now, another wave effectively copy and pasting. If you could hold down the control button and then click on the corner, Unisys additional little plus sign there and click and drag. Then we get Monday, Monday, Monday, Monday, Monday, which we don't want. So what we're gonna do is do exactly the same process. Come to the corner where it's a single plus click are left mouse button on, drag it all the way down to the end of our months. And there we have our dates and our days. We just increase the size of that column by double clicking it. So all fits nicely. There we go in summary. In this lecture, I've shown you how to use the auto fill function to add sequential dates and days of the week to a spreadsheet, which differs from using the copy and paste function. I'm Jennifer Bailey on Thank you for watching 6. How To Add More Basic Formula: Hello. This is Jennifer Bailey. In this lecture, I'm going to show you how to use a more basic formula. So use multiplication subtraction on the use of brackets on. We look at the difference between adding a cell reference Onda number into a formula. Now go start adding the formula for our gross pay deductions and net pay columns. So are gross. Pay is basically our hours worked multiplied by our rate of pay. So we need to do in this cell is type in equals to let excel. No, we're going to do a mathematical formula on this time. We're going to use just sell references and you can enter these by just clicking on the cell. You want to use the most vacation sign on, then selecting the second cell, and now you can just click the tick. Now, the beauty of this is when you use a cell reference, so C four multiplied by default. When you copy this formula down the column, excel automatically updates the formula. Um, actually, other rose. So we know click on this last cell. You can see that it's multiplied C 34 by D 34. Which of these two cells here, which makes this total correct. So this time we're gonna look at our deductions column on we're gonna find out what 25% is off our gross pay, which is this cell here. So when you're doing formula, you can use brackets in the same way issued use brackets of you doing any mathematical problem on paper. So what we're gonna do is gonna type equals open brackets on this Tamargo type in e four. But you could easily click on the cell a four divided by. So this time we used the backslash key 100 close brackets most applied by 25 and push enter . So that's now calculated 25% off this cell here, and you don't have to use the brackets. There is an easier way to do this particular some. So let's take that out. So equals e four multiplied by 25%. Both will give you the same answer. And again, we copy and paste the formula down the column. Lastly, we're gonna work out on net pay. So are net pay Is our gross pay Take away our deductions so I type in equals gross pay minus f forged abductions push, enter and then again, we can copy the formula down the column. Any updates accordingly. So we just check. We are E four minus F 34. The beauty of using cell references over numbers in formulas isn't means they can update themselves if we go back at the top here now. For example, if on this Monday we didn't work eight hours, we've made a mistake, we can now change the eight to a seven. And did you notice the gross pay? The deductions on the Net pay automatically updated themselves if we'd use actual numbers in here. So say, for example, we had typed in seven multiplied 6.31 that would take away the advantage off begins to update all spreadsheet by using cell references. It just refers to the cell in the figure in there rather than the actual value. It's not fixed. I just took that back there so all of these can be updated automatically by changing our hours worked. Now, in a later video, I'm going to show you how to set your rate of pay to rather you rate paving it every single row here. I'm gonna show you how to use absolute referencing, which means if your rate of pay goes up rather than having to go back and change in all, the formula here will just change in one place and it will update the rest of the spreadsheet. In this video, we've looked at adding formula to all spreadsheets with use, multiplication and subtraction on Vorsah used brackets to perform a formula. I'm Jennifer Bailey and thank you for watching. 7. How To Assign Cell Formats: Hello. This is Jennifer Bailey, and in this lecture, we're gonna be looking at choosing and setting sell four months. So we first start entering data into a spreadsheet is generally set as general here, which generally means no specific format. So our time sheets, text and all our headings offset as general Now, as we started entering the date into the cells, Excel recognize that we were in putting a date. So it's it's set it as being a date format. They have different options. As Faras Date is concerned, you could look at short dates on long dates. It's my click on more number four months. There's a whole selection off different options as far as displaying date is concerned on. This may very depend on your location. For example, in the UK, we tend to put did the day and then the month and then the year where some countries put the month first, then the day then the year. But it is no has set is how you want or you need to do is come into the list here and select the one that you're after laughs. You don't forget, you'd have to repeat that for the whole of the column. So what you could do is you can slip the whole of the column first, then go in and format the cells. Next, we've got our day of the week. Now, that is also fine to be set, US General, because its text now here are hours worked. It set us, general, but I think that would be much better. Set is a number. So what I'm gonna do is I'm just going to select all the cells. I'm gonna come up here to number on in the same way as with the date when I should do the different options that you can choose. You can alter how it display. So, for example, you can change the number of decimal places on if there's a thousands separator as well. But also, you can choose to have negative numbers in bread or with a minus sign so you can customize how the data shows in those particular cells. Now, I'm gonna leave as two decimal places because it's quite possible you might get powered but paid per 15 minutes worked, so that would make sense to have the decimal POTUS showing you can actually come up to these two buttons and you can decrease the decimal. I increase the decimal toe. How you want it to be just using these buttons here. The next cell format that we're going to set is our rate of pay, gross pay deductions and net pay on. They're all going to be currency because we're talking about money here. So I'm gonna select all the cells that I want to change and set them its currency, that you're not going to see a change on the blank ones. At the moment when we start entering data, they will automatically have a currency symbol added. Now, the quickest way of setting these is just a click on the button here which automatically sets it toe accounting, which offsets the currency signed to the left of the cell. You could also increase on decrease the decimal places in the same way as we did with the our worked. She could do that here or alternatively, you can click on the square here and come in on auto decimal places on your symbol. If you prefer, you can use currency, but it just gives you a couple of other options here of how to set your negative numbers on . It just changed the way that the currency symbols displayed. So, for example, if I click OK now, it puts the currency's symbol right up against number. But for ease of reading, you probably prefer to use the accountancy option so that it's just toggle that back to their also you have, ah, drop down arrow on the side of this button. She can quickly change the currency symbol much easier than having to go into the main menu . In summary. In this lecture we've looked at setting cell formats on, we've looked at ST General Number and currency, accounting for months. I'm Jennifer Bailey and thank you for watching. 8. How To Use Absolute Referencing: Hello. This is Jennifer Bailey. And in this lecture, I'm gonna show you how to use absolute referencing on this is a way of anchoring a cell or a series of cells so they know automatically updated when you say copy and paste formulas on. We can use this to make our time sheet more efficient. So now we're gonna go to this time sheet to make it work more efficiently. For example, if this is your time sheet and you went into work tomorrow on the off to pay rise with your spreadsheet the way they should have to alter the rate of pay all the way down this column to reflect your pay rise. So what we can do is we can use something called absolute referencing to make our life a lot easier. So I wanted ISMs could insert a couple of rows in here. I'm gonna put right. Andi was going to make that a little bit smaller that, like the Bold either I shall just make it smaller. There we go. Right. So we're gonna do is we're gonna take the rate payout of this column and we're gonna put it here instead. So let's move are ready to pay, which is £6.31 will put that there. There's change that as well make a little bit smaller. There we go on. Let's give it a pound sign as well. There we go. So what we're gonna do is we're gonna delete this rate of pay column because we don't need it anymore, cause our rate is up here. So we're gonna do is we're going to, right click delete, and I've deleted the entire column. Now all our formulas have gone a little bit crazy because we've taken their reference way. Because if you remember thes formula were referencing the rate pay column, we've removed it. So what we need to do is we need to correct these formula. So the c six, but is correct. But we need to make horses reference. We need to refer to the cell here. So what we're gonna do just as a demonstration, I'm going to type in this cell reference. So be three. Now, these have updated themselves. And now correct. But if I copy this down, as we have done in previous videos, your notice that it doesn't work right now, The reason being is. Can you remember that the formula automatically up to update themselves as you copy, then paste them down? Well, that's fine for the C seven. But now the second part of our formula, which was refined, who are ready to pay is now referring to this cell here, not to this one as we want it to. So we need to do is going to do something with this part of the formula to make that stay the same. So what we're gonna have to do is we're going to have to anchor this formula. So what I do is if I go to the formula and I put a dollar sign before the B that will unca the b the column off the formula on if I put a dollar sign before the three and that will anchor the row off the formula. So now when I copy and paste this down your notice that it's correct. So the C seven updates, but the be on the three remained fixed, so that's what's worked all the way down here. So the next thing we need to do is you need to decide where you want to put your time and 1/2 or your double time in for the purpose of this tutorial, I'm going to put it back in on the Saturday and Sunday again. So I'm going to on Saturday. All I'm gonna do is tag on the of here. Another multiplication groups. Another multiplication sign on 1.5. Let's just do that. Sunday, I'm going to make that to, and what we can do is we can copy and paste. All of that information will basically repeated, so every Saturday and Sunday will become time in half on double time. So as you can see, all our formulas are reinstated again. They're all working fine. Let's just double check up things right. Glass, metal, green signs here. Just double check to see what that is in what appears to be fine. But it was worth having a little looks if it's a working properly, so there we go. It probably doesn't like it because it's different formulas in the middle, but they're all fine, so we can ignore the Evers for that again. So now you can see we put a much more efficient time shoot. So if we ultra right pay in here now says, Make it £6. 50 everything automatically UPS updates itself in accordance. This one cell were saved it all for a lot of time in updating every single value in the column. So in summary in this lecture, I've showed you how to use absolute referencing, which involves anchoring a cell or Siris of cells. And in the case of our time sheet, it's made our work easier, as we've only had to change one cell. Andi automatically, other cells could be updated, so therefore make it much more efficient. I'm Jennifer Bailey. Aunt, Thank you for watching. 9. How To Use Auto Sum And Average Formula: Hello. This is Jennifer Bailey, and in this lecture, I'm going to show you how to use it. Auto some to calculate the total amount off a range off cells on also the average formula, when now can add a total cell so we can add up our total gross pay. Our total deductions on our total net pay so we need to do is go to the bottom of the columns. I need to push end to move, damn it with further does these last three columns here? We want to have a total, and it's really easy to do in Excel or you need to do is click the auto some button here, and it will highlight the cells it thinks should be included in the auto some, and it automatically puts a former in few. So equals some on this in brackets means from E four to E 34. And if you're happy with that, you just push enter and automatically puts in. And Excel has automatically assumed that we want to have 25% off this cell calculated and also the result off E 35 minus deductions and is put it in there automatically for you. How clever is that? So just type in total in here to remind us what we've done. And the next thing we're gonna do is average. So we're gonna work out what are average gross pay is So what we can do is this time is we can use the average formula. She just type equals on this time you start, take typing in average. And as you can see, Excel prompts us with some examples of average formula we could use. You now need to put in the cell range. You want to work at an average four. So we open our brackets on. We want we don't want to include this number here because obviously that will throw our numbers out. You want to go from e 34? I'm just gonna highlight the range that I want to use. Like so when you can just type in the range like so I'm gonna close the brackets and push enter now automatically. We have our average of that range. So £45.59 was our average gross pay on Excel again has automatically calculated with our average 25% deductions. Is Andi Also our average take home pay on that pay. So in summary in this lecture, I've shown you how to use the auto some feature in Excel Toe Add up a column off figures on also how to use the average formula to. We've also seen how clever Excel could be by predicting what types of formula and results we want to see. I'm Jennifer Bailey and thank you for watching. 10. How To Add Borders To Define A Table: Hello. This is Jennifer Bailey on in this lecture. I'm going to show you how to define your table using borders. Now, before we get started adding borders to our table, I just want to quickly explain the difference between grid lines on borders. Now, when you first open up your worksheet or your workbook, you'll see thes pale grey lines that separate your columns and your rose and basically defined the cells on. These are called grid lines, and you can choose to turn them on and off. Now, if you want to do that, you need to come to the page layout tab on. Don't come across to where it says sheet options. And here is where you can choose whether you want to prince them or just view them Slimness , Turn these off for a moment. So the moment the grid lines are being viewed by un checking the box here, they have disappeared. Now the cells are still there. It just means you can't see where the boundaries are because you've turned the grid lines off. Now. I usually work with my grid lines switched on, so I'm gonna turn my back on now if I come across the print preview. So go to file and print. You'll see that my grid lines are not showing. So if I print this document now, I have no grid lines, which for a table you do need. I think you need some sort of defining lines to make it look like a table rather than just a series of numbers. And it doesn't make it look a lot nicer and neat and tidy. A on it will make certain bits of information stand out. So to print it, you need to go back and we go back to our page now tap and come across the sheet options, and this time you put a check in the print box. So when you go for the print preview this time, you will see that the grid lines are now showing. So as you can see, the grid lines do make our work. She looked like a table, but actually it doesn't define certain aspects of it, so it doesn't separate out said that the main table here from the rest of the worksheet or highlight the total an average is as a border word. So let's go and add some borders to our time sheet. Now what we're going to do is we're gonna change the view off our page when working with Borders, because in normal view, which I'm in now. So to get there you go to view and you can see the workbook views. Currently, I'm in normal for you on what happens in normal view is the headings here are very, very close to the edge off the workbook. Which means if you're trying to add borders around the edges off the work book, you can't see them very well. So we're going to change the view that we're looking at. All right, So what we're gonna do is we're going to change from normal view to page layout view, which will make it a lot easier. So in order to add a border, we need to come to the home tab. And we want this button here which basically lets his at Borders. Now, the first thing we're gonna do is broken. Add a border to our time sheets sell. Now, if you remember, this is one cell that we merged in an earlier video, which is why it spans the other cells. But effectively one cell. So what we're gonna do is we're gonna go to the preset board options to click on the arrow by the side of the button option. And here we have a selection off preset borders to speed the job up so she can see you couldn't ads the bottom board a top left or right. You could have all borders outside border stick, borders, etcetera. On what we're gonna do to make it nice and easy for this header. We're just gonna pick thick outside border and click. And now you'll find that our header has got a thick outside border around it. Now, that's a very quick and easy way of adding a border to a cell. The next thing we're going to do is we are going to add a border to the rest of the table. So in order to do that, I'm First of all, I'm going to select the table. So I've taken my point. The top left cell at the top of the table. I'm gonna click and hold down my left mouse button and with my touch pad all my mouse, I'm going to highlight the rest of the table just above where the total row is now. If I was to click on the button here, this part that will automatically add my previous choice of border to the rest the table. Now, I don't want that. So I'm gonna come down to the drop town arrow on this time I don't want to use. And if the preset selections I'm gonna come down toe where it says more borders Now, here I can basically select a different line style, and I can choose exactly where I want to put it. So what you need to do when you do this is pick the line style first. So I'm gonna pick this thick line and I want to put it on the outside of my table. The stick border. Now you can do it one line at a time by clicking top border bottom, border left and right. You can do that. And the reason you can do it independently Issue may want have different thickness or different styles of borders on different edges of yourselves or alternatively, because we want the whole of the outside of our table toe have this border is actually easier to click on the outline button and automatically puts that board around the outside forests. Now, the next thing I'm going to do is I'm going to choose a finer line to go in the middle here . So I'm gonna pick this line here, so I'm going to select the line style. Now, I could either click this button on this, but, um, if I show you to put thin lines on the inside or to make it quicker, I can just click inside and it'll put it on there for me. If I'm happy with those settings, I'm just gonna put Okay, you'll now see, I have a thicker line on the outside on a fine a line on the inside. It's really, really easy. Okay, so the next part we want to add something to is our total and our average rose. So I'm gonna highlight them. Remember, click your left mouse button and drag. So this time I'm going to pick another preset. I'm going to pick one with a fine or single top line on a double board at the bottom, like so let's see how that looks. Now. I'm not hundreds unhappy with that, because, as you can see, it hasn't added a border down the left or down the right. Andi, I think would be quite nice, actually, to have a double line across the top here. So that's quickly old that some highlights he again, we'll come back to my border options come down to more borders. And this time I'm gonna pre select the double border I'm gonna put on the top like that shit replaces the single line with a double on. I'm going to choose our fact border again, and I'm gonna put it down the left, and then I'm gonna choose the fine border, and I'm gonna put that in the middle. She could mix and match all the borders that you like. Then click. Ok, let's see how that looks. I'm quite happy with that. If I am, I actually choose not to have any boarders. Two here. So that's easy enough to fix. I highlight the two cells they want to change for the two Rosell. The two columns come to the border options come two more borders. On this time, I can choose to turn off the inside borders and quick. Okay. Let's see how that looks. What? I'm quite happy with the table looking like that. So the next thing I'm going to do is I'm going to do something with my rate of pay. We're gonna highlight that on and I'm going to pick thick outside borders again. That looks pretty good. I think I'm actually going to show you one more technique and I'm gonna alter this header around the time sheet. So we'll click back in this cell. I'm going to come down to more borders. And what I'm gonna do this time is I'm going to change the color of the header. I'm gonna choose red, so remember you to pick your color or your style before you apply it to the borders. I'm gonna go for the fact border, and I'm going to add the fact board is in, like so. Of course, I could have used the outline button as well. Then click. Ok, so this is basically how are time sheet now looks now, I'm gonna show you in a print preview because I think it would just be easier to see. Okay, so now you can see we've got or the board is showing around our table Now. I've also got the grid lines printing out, and I don't think it be appropriate to have these grid lines point printing out. So I'm just going to switch them back off again. If you remember is page layout tab sheet options on turn off the print? There we go. How much more effective does that table look? Using borders, rather grid lines. So in this lecture, we've looked at the difference between grid lines on borders. We've looked at how to turn grid lines on and off and how to print them or not. We've also looked at how to add a border to a single cell or collection of cells on also, how to change the color of the borders on the thickness, the lines. I'm Jennifer Bailey. Aunt. Thank you for watching. 11. How To Print Your Spreadsheet Print Settings: Hello. This is Jennifer Bailey. And in this lecture, we're gonna look at printing your spreadsheet on what to do if it doesn't fit on the page. So look at changing the page orientation, altering the margins, changing the scaling as well as printing a selection and setting a print area. Once you completed your time, she your spread sheet. More often than not, you're going to want printed off. She have a hard copy off off your spreadsheet. So the tricky thing is trying to make sure that the rights bits of information appear on the page when you're printing out. So I'm in normal view. So if you remember from previous video, we could change our view off our work sheet by going to view and work. But views and the moment I'm in the normal view and you might be thinking, Well, how do I know which bits going to print? On which bit isn't because often I got a big workbook here and actually could have lots and lots of blank cells and columns and rows on on the bottom of my spreadsheet. Now here's office in my time sheet, my table. And if you look There is a faint dotted line down between columns G and H actually over here between P and Q. And also I scrolled down to blow my table. There's also a dotted line across here. Now these lines show where the natural page break occurs on our workbook. Now, at the moment, our spreadsheet is very neatly within those lines. But what could we do if our spreadsheet spans, say, page and 1/2? So I'm going to show you what to do. So for the purpose of this exercise, I'm just going to make my time sheet a little bit fatter so that it goes over whether natural line would bay that if I go to page layout view, you can see what has happened. Basically, this is one page on my spreadsheet has appeared overlapped on to the next page so we could alter this in all print settings. So let's assume that this is how we want our spreadsheet or our time sheet to look on. We can't meeting up the columns anymore. We can't make them smaller. We don't want to reduce the font. Really? OK, so you go to file, come down to print now Somehow we've got to get all of our spreadsheet on one page, because offices you can see there's a column missing here and you can tell it's spanning more than one page, because here it says one page off to there's a couple of different things you can do. The first thing which might work is to change the orientation off the page. So this is Portrait, which means these air the long sides with short sight at the top is a four paper. We can change it the other way round by rotating our paper by 90 degrees. So what we can do is where is his portrait? Orientation could use the drop down arrow here and go to landscape, and you'll now see that all of our columns fit on the page. Unfortunately, it's chopped off the bottom, still with the rows, so we know that isn't an option for this spreadsheet, so that's put it back to port trait. The next option we've got is to change the size of our margins. So these are our margins, these white spaces that occur around the edge off the spreadsheet. We can probably make them narrower, so if we come toe where it says normal margins. It gives us some dimensions here and clip on this arrow. A quick way of reducing our margins is to choose narrow. And as you can see, this margin has shrunk. Andi automatically are spreadsheet has condensed itself or the margins of condensed himself to fit the spreadsheet onto the paper. Now that is quite a good way. If you can use that method, use that method first because it's not shrinking your spreadsheet in any way. It's just make it fit on the paper better. So let me just return that back to normal. Now the other option you have is the scaling, which is his bottom option here. The moment there's no scaling, which means it's it's full size. So what we can do is we know that our rose fit on the page. Fine is the columns that are the issue, so what we can do is under scaling. We can choose fit all columns on one page. So what is effectively going to do is it's going to make sure it's condensed your columns so they fit. If you have the problem that it's both your columns and you'll rose that don't fit. You can go to fit on one page, and it will basically shrink the columns on the roads so they fit. You can use fit all rows of one page if it's not the columns that it is the Rose, if that makes sense, so scaling a fit on one page or fit columns one page would work really well with this time . Shades. Now during all those things is is great if you haven't got much information on your workbook or in your worksheet. So if, for example, you had another table here, Excel is looking for the information on your worksheet, and it's going our world. I can see this information here. This is what I want to print. If there's only a selection off information that you want to print, what you can do is you could highlight the information you want to print out, so save excitable. You. So say, for example, you didn't want to print the header on the rate of pay. You could highlight the table. If you remember, you click your left mouse button and drag to highlight, and you can go back to file and print on what you can choose. Instead of printing active sheets, you can see if you notice here are time she had the right to pay showing You can change that to print selection. So all it's going to do now is print the bit you've highlighted, which is very handy to know she didn't have to print the whole of a table. You can just print a portion of it. And if you're gonna print that portion on a regular basis, what I would suggest you do is you use the set print area option and in order to do that, which saves you having to keep highlighting the peace of the spreadsheet. We want you come to page layout on set print area. So what will happen is now without it selected, go to file on print and then offset print. Also the wait. Why is it no there is because I haven't selected anything. I just come back to active sheets and you'll see my set Prince area. So without the header Andi, the rate of pay which I set in my print area is the only bit that appears. So you don't have to keep going back and highlighting what you want to print each time you can set. It is a print area. One last thing before I finish this video. If you want to clear your print area, you've just set because you've changed your mind. Or you want to print a different piece of the spreadsheet, come back to the page layout, top print area and then clear print area. So now when you go back to a print menu file print, the whole time sheet is back again. The last things you need to do before you print is make sure you've selected the printer that you want to send your document to. And if it's not the one that's appearing at the top of the list your default printer, you need to click on the drop down arrow and pick a different one. You can choose the number of copies you want, and then you need to click on the print button. So in summary in this lecture we've looked at printing are spreadsheets and how best to get it to fit to a page. If it is bigger than a standard page size, such as altering the margins, changing the page orientation and scaling, as well as printing a selection and setting a print area. I'm Jennifer Bailey. Andi, Thank you for watching. 12. How To Sort Simple and Related Data: Hello. This is Jennifer Bailey. And in this video, I'm going to show you how to sort data on your Excel spreadsheet. So we'll look at very simple sorting, such as oldest values to newest values on alphabetical izing or reversing the alphabetical order off a list on also how to sort related information such as information in a table. Excel makes it very easy for us to rearrange our data using its sort function. I'm gonna show you two different ways off sorting information. I've got our time sheet spreadsheet open in front of us. But before I show you how to change that one, I want to show you how to re arrange a simple list which does not have related data because in our table off see each as we go across each row, the information in the columns are related, so it's a bit more complex, so I'm just going to show you how to do it when there's no related data. So what we're gonna do is I'm gonna put a series of numbers in, like so I'm gonna highlight my list and for this you need to be in the data tap and you need sort and filter. Now, when you've got a simple list like this, you can use the eighties ed and said to a Buttons, At the moment, we have got the lowest number at the top, so its smallest to largest. So this button will have no effect on our list. It all all we can use the said Toe a button, which sorts the largest to the smallest. So I click this button. Now you'll see we now have 54321 I'll show you again or quickly ate a set, and it will reverse it now. This would also work the same with words. So if I put some are just used letters for the Democrat to demonstrate this, So a B C D e A. I don't think there's any more is giving us that. So would show you again with this once a B C. D. So it could be, um, words already in alphabetical order that already sorted a to set. Have one set to a You just click the said to a button, and it reverses thumb so it wouldn't matter if you had two days in there, but Safer probably had dog and dig. Let's Dogan diggin they are. It will put the words in alphabetical order. That's a simple way to sort and filter a column off information. Now, going back to our time sheet we have are related information. So we have to make sure we do. Is we include all the columns and rows that related to each other because otherwise what will happen is our information will get in a muddle and we'll find, for example, that date doesn't correspond with our day anymore, which doesn't recall respond to the hours work and the gross pay. So we need to make sure we do. Is we highlights or collect all the information to go to, To say, to excel. This is all it related information. When you're sorting it, you need to keep it That corresponds with the information is related to so will highlight our table. I'm not gonna include the average on the total cause I don't want them to be sorted. I want them to stay where they are, and I'm gonna come across to the sort button which lets us be more precise with what we're actually wanting to sort of how we want to sort it now because I ve highlighted the header row when I highlighted my table. You need to make sure you put a check in this box. Otherwise you'll find that your header will appear somewhere in the middle of your table, which is not what you want to happen. So the first thing we're going to do is we're going to choose which column we're going to sort by and we want to rearrange our date. So we're going to choose the Date column on we're going to sort by value. Andi, At the moment we have oldest to newest, so we're going to reverse that by picking newest toe oldest on clicking. OK said, now you can see that you've got the newest date at the top, descending down to the oldest at the bottom. Now you don't have to sort by just one criteria. You can actually sort by more than one. So let me put our table back to where it wasit. Just click the undo button. So I'm gonna click the sort button again. So this time I'm going to choose to sort by day of the week and then as a secondary level, I'm going to choose to sort by hours worked. So what I'm going to do just to make this a bit easier to see because I've just realised that all my Mondays have eight hours in them. I'm just going to change some of this information. I'm gonna make a lot The first Monday, seven hours on the second week. I'm going to make them six hours, just cute, so you can see the difference. So let's highlight that again. Sort. And I'm going to sort by day of the week. I want a to Zed. So be alphabetical ized the days of the week. And then I'm going to add a new level, which means all do day of the week first, and then it will sort the second choice afterwards. I'm gonna choose hours worked on this time. I want the smallest to the largest. So let's click OK and see what happens. So now you can see because off seat Friday is nearest a going down athletically alphabetically, Friday has been sorted. So all the Fridays it together then or the Mondays it together, Saturday's etcetera. But if you look, you can also see that the hours of work are sorted as well. It's not random. The hours of work, as we've specified in our sort, have been put from the smallest to the largest. So in this lecture we've looked at sorting simple data, so a simple list off numbers or words and alphabetical eyes in them, or put them in numerical order. We've also looked at sorting related information in a table on making sure that the information stays with everything that's related to. And we've looked at sorting athletically on numerically but also doing primary and secondary sorting. I'm Jennifer Bailey. Andi, Thank you for watching. 13. How To Insert Headers and Footers: Hello. This is Jennifer Bailey. And in this lecture, I'm gonna show you how to insert a header and a footer into the spread shades. We're gonna insert a picture into the header and adjust the margins to get it to fit on. We're gonna insert page numbers into the footer. So the first thing we need to do is go to our page layout, view off a workbook. So in order to do that, you need to make sure you go to the view tab, then workbook views. At the moment, I'm in normal. We need to go to page layout. The next thing you need to do is where it says add Header is click in that square. This will automatically open a new tab called Hedren for two tools design. So click on that tab to open up those commands. Now you can have preset designs for both the header and a footer. So you click on the drop down arrow. You can see what they are. She could have page numbers, sheet numbers, the file path for where you've stored documents, the Sheikh Naim etcetera on those options are there for both. The header in the footer, but we're gonna put a picture in. So what we're gonna do is we're gonna come across to where it says picture and click. I would choose a picture from a file, and I'm going to use my quick start Excel logo. So if selected it by clicking on it and then I'm going to select insert. Now, when I click off the header, it puts my logo onto my time sheet. Now the margins. Because of the margins being set, it seems to overlap the rest of my spreadsheet, which is not what I want. So let me show you how to adjust the margins, to get that to fit properly, to come to page layout and margins and come down to the drop down arrow below. And then, instead of picking one of the preset margins, were gonna come to custom margins. So here we could alter the top, the bottom that left and the right or the header on the foot of margins. Now you think we would alter the head of food, the header margins, but actually what we have to do is we have to move the top margin, which an increase it in size, which effectively will move the spreadsheet further down the page. So let's increase the top margin. Let's take it to round about five and click. OK, that's not quite enough. Let's increase that a little bit more So five put full. Okay? And now we can see that the logo fits in our header. Now we're going to do offer toe, so we need to scroll down the page on where it says, Add furter. We need to click in that box, which again opens up our design for headroom for two tools on this time. What we're going to do is we can use page A number of pages. Now, if you want to insert date time, file path file name sheets, name except or you do is you click on those buttons on automatically. It will appear in this box here, but we're going to use page numbers because that's what we use probably the most often, So we're gonna click page number off number off pages. So what will happen is when are spread. She is generated and printed. It won't say Amber sand page off Amberson pages What it will do. It will insert in that space page, say one or two off the number of pages in your printing selection. So on this worksheet, we've only got one page. So say one page off one. So when I click off the footer, you can see it says one off one. So what will happen is if we generate an extra page on our work shoot that will become to and then this number will increase, depending on which pages been printed. So in this lecture, I've shown you how to insert a picture as a header on a document on also how to insert page numbers as a footer. I'm Jennifer Bailey, and thank you for watching. 14. Tip #1 How to Add Up a Column or Row With Shortcut Keys: Hello. This is Jennifer Bailey. And in this video, I'm going to show you how to quickly add up a range off cells in a column or in a row without having to type in a formula, also without using the auto some button. So, as you can see, I have a column of cells here that I want to add up. So what I do is I come to the first sell in the range. I click in the top cell, hold my left mouse button down and drag to highlight the column. But I also include an extra blank cell of the bottom, which is where the total is going to go. The next thing you need to do is push. Olt equals on your keyboard. And as you can see, the some formula is automatically entered. We could do exactly the same thing for a road. What we're gonna do is gonna highlight the road, including an extra cell to the right. I'm gonna push. Ault equals on my keyboard. And as you can see, the formula is automatically added. I'm Jennifer Bailey, and thank you for watching 15. Tip #3 How To Change The Names and Colours of Worksheet Tabs: Hello. This is Jennifer Bailey. And in this quick video, I'm going to show you how to create multiple worksheets, how to rename them and have to change the tabs color. So, as you can see, we've only got one sheet in our spreadsheet the moment it says here, sheet one and there aren't any others. So it nor discreet a second worksheet in your workbook. You come down to this plus sign here and click, and it opens up her second worksheet so you could do that a couple of times. Have a good now, a safe example. Working months of the year, you may want to rename them, say, January, February, March, April, etcetera. So, in order to rename them softly sheet one sheet to shoot three doesn't really explain what's on that sheet. So come to the sheet, put the push the right mouse button on your keypad, un come up to rename and you're seen. Now the writing has kind of got this gray box over it. That means you can start typing all overwrite it. So jam push enter to select what did exactly the same with sheep, too. Same with three and the same before. Okay, now it's much easier to pick them up with a glances. They're different colors, so let me show. You have to do that. So start with January again, right? Click a mouse button on top of the tab, come to tap color, and then pick whichever color you fancy that you'll only see the color when you click off the tap. So as I move across to the February tab, you'll see the changing color. So right click again Tep color. There we go to the same in March. They could pick whatever tell you want. I'm just picking the ones at the top of the palate just to make life easy. And then we came. So now you can see I've renamed the taps I've created and change the color to make it easier to identify the different worksheets within my workbook. I hope you found this tip useful. I'm Jennifer Bailey and thank you for watching and 16. Tip #4 How To Change The Colour of The Gridlines: Hello. This is Jennifer Bailey. And in this quick start tip video, I'm going to show you how to change the color off your grid lines. Now, when you open up your workbook or worksheet, you'll see these pale grey lines which mark out where the rose on the columns and the cells are now, even if they're not showing the cells and the rose and the columns are still there. Now, if you can't see your grid lines, what you need to do is you need to go to page layout and where it says sheet options. Make sure you have you checked so far, untech this or uncheck it. You're seem that your grid lines or migrant lines have disappeared. So what we're gonna do is going to change the color of those some good tender back home now in its do issues, come over to file, come down two options and then advanced and then keep scrolling down until you get to display options for this worksheet, and you can actually pick which worksheet you want to change the color of the grid lines. On that, what you need to do is come down to here, where it says grid line color. You have another option to turn off your grid lines here. So show grid lines. You contend the on off there, too. On this is the grid line color. So you need to do is click on the button. I'm pick another color. So let's pick red, and then we're gonna click OK to save that. And now you can see I have read grid lines instead on the beauty of this is these will actually print out in this color too. So if I quickly go over to file is going to see another little tip here and come to print, you notice that my grid lines are actually showing. And for that reason, we need to make sure we turn them on to print. So we go back to our worksheet, come over to the page layout tap Andi, put a check under grid lines in the print option. You'll now find that when you go back to a file and preview print preview, the grid lines are now showing, and those will actually print out looking like that. I hope you've enjoyed that video. My name is Jennifer Bailey. Andi, Thank you for watching 17. Tip #2 How to Insert Current Date and Time Using Shortcut Keys: Hello. This is Jennifer Bailey. And in this quick start tip video, I'm going to show you how to insert the current date on the current time in tow. Worksheet. So we need to do is go to a cell we want, insert it, select the cell and concert the date you push to control key on a semi colon on your keyboard, and it inserts the current date. If you want to insert the current time, what you need to do is push control and then shift. And then the semi colon colon key. And there you have the current time inserted in a worksheet. I hope that's useful for you. My name is Jennifer Bailey. Andi, Thank you for watching. 18. Tip #5 How To Change The Colour of Text and Fill of Cells: Hello. This is Jennifer Bailey. And in this lecture, I'm gonna show you how to change the color off text on also, how to change the color off the cells. Now, this is a little bit of a recap because I showed you how to change the color off the texts in lecture three. But I thought it fitted very well in with changing the color off the cells. So I thought I would do it as a bit of a revision exercise. So the first thing I'm going to do is I'm going to change the color off my rates of pay. So I'm with a click and select the two cells. I'm gonna come up now, make sure in the home tab for this, and we'll come across to where there's a pitch for bucket with the color underneath and up , and then letter A with a number underneath. Now, if you want the color that is underneath the letter or the bucket to be added to the cell, you just click on the button. However, if that's not the Culley want, you need to come to the drop down arrow and select a different color. So I would like a dark blue for this. So I'm going to slip my dark blue color, and now you'll see that my rate off pay has been changed to a dark blue. Now I'm gonna do is I'm going to change the colors off the cells in the total on the average rose. So I'm going to select both of those rose, and this time I'm gonna come up to the bucket. Now, as you can see, it's already read, and this is the color I was going to pick. So what I can do is I can just click on the buckets and automatically the cells are filled with that color. If I didn't want that color in the same way as I did with change in the color of the text, I've come down to the little arrow by the side, the bucket, and select a different one. So in this short lecture, I covered how to change the color of a text on new spreadsheet and also how to change the color off the cells. I'm Jennifer Bailey, and thank you for watching