Placeholder Image

字幕列表 影片播放

  • 00:00:00,000 --> 00:00:04,740 Welcome to Excel Basics Number 1.

  • This is the first video in a series

  • of Excel Basic Videos 1 to 25.

  • Now, in this first video, we're going

  • to talk about the Excel Grid, Formatting, Formulas, Cell

  • References, and Page Setup.

  • Now, this is a continuation of my Office 2016 video series.

  • This is video number 13 in that series.

  • Now, here's our list of topics.

  • And there are a lot of amazing topics.

  • Even just this one first video will give you

  • a lot of basic skills to accomplish tasks in Excel.

  • Now, this is our goal.

  • We're actually going to create a grade sheet--

  • names, assignments, numbers, and then formulas for average,

  • total, and even percentage grade.

  • All right.

  • I'm going to close this.

  • And here's our system of folders.

  • And of course, now that we're on to Excel,

  • we're going to be saving all of our files to 04 Excel.

  • Now let's open up a blank Excel workbook.

  • I'm going to click on the green x.

  • Now, just as in Word and PowerPoint,

  • the blank workbook is highlighted.

  • I can either click with my mouse,

  • hit Enter, or use the Escape key.

  • And here's our blank workbook.

  • The very first thing we do is we look up to the title bar.

  • Book 1 is not a good name, so we use our keyboard for save as--

  • F12.

  • Now, we're going to navigate to our folder.

  • And there it is--

  • 04 Excel.

  • We're going to click down in the file name and we're going

  • to call this EB for Excel Basics 01 dash--

  • and this is going to be a Gradebook.

  • There's our name.

  • The file extension by default in Excel is dot xlsx,

  • and that's fine for us.

  • I'm either going to click Save or hit Enter

  • to enact that Save button.

  • Now, I want to zoom in, but, of course, just

  • like int Word and PowerPoint, instead of using our Zoom

  • bar down on the status bar, I want to hold Control and roll

  • my wheel.

  • Now, the first thing we need to talk about

  • is the structure of Excel.

  • Notice up at the top, I hover my cursor--

  • DEF.

  • If I click on the E, that's a column.

  • Columns are represented by letters.

  • If I go over to the rows--

  • 2, 3, 4 5, and click on 6, numbers represent the rows.

  • Now, the reason that's so important

  • is because the intersection of a column and a row

  • is called a cell.

  • Now, the name of that cell is E6.

  • We can actually see the name of this cell by looking up.

  • This is called the formula bar.

  • And all the way on the left in the formula bar,

  • if you hover your cursor, is the name box.

  • We can see that that cell is named E6.

  • So the column is the letter.

  • The row is the number.

  • And I can click in any cell--

  • click there, and I know it's G6.

  • Now, the reason that knowing letters are columns,

  • numbers are rows, is because later, we'll

  • have to refer to our cell in formulas.

  • And knowing that E is the column and 6

  • is the row will be very helpful.

  • So if that's a cell and these are all cells,

  • then all the cells together make up what's called a Worksheet.

  • Now, we call them sheet for short.

  • Now, notice the little plus right there.

  • Sometimes we need new sheets.

  • We might need one for January, February, March.

  • You can simply click the plus, and there's

  • a new sheet inserted.

  • Now I can click back between the sheets.

  • I'm going to click on sheet 1.

  • Now, these are called Sheet Tabs.

  • Click back on Sheet 1.

  • Sheet 1 is not a good name for our sheet tab.

  • So to rename our sheet in the sheet tab,

  • we simply double click.

  • Now we can name this--

  • I'm going to name it GradebookFall17.

  • Now, if I come up to the ribbons and try to use something,

  • everything is grayed out.

  • That's because-- just like over in Windows Explorer--

  • we have to hit Enter to register that name.

  • So I hit Enter.

  • Now column, row, cell, sheet.

  • That's called a Worksheet tab.

  • All of the Worksheets together make up

  • what's called a Workbook.

  • And a Workbook has a name.

  • The name of our Workbook is EB01 Gradebook.

  • So this is called a Workbook file.

  • Now, the next thing we want to talk about

  • before we start entering data and numbers and formulas

  • is just something very basic.

  • Hey, this cursor right here--

  • that white thick cursor with a black shadow--

  • it's called the selection cursor, and here is why.

  • If I click in the middle of a cell and hold the click

  • and drag--

  • oh, look at that.

  • I can select or highlight cells.

  • So this is the selection cursor.

  • Now I'm going to select cell A1.

  • There's two other cursors we want

  • to learn about in this video.

  • If I hover right at the edge, that's called a move cursor.

  • Now, most of the time, the move cursor will get us in trouble.

  • But we need to know the difference

  • between selection, move, and--

  • right in the lower right-hand corner, that little green box--

  • that's called a Fill Handle.

  • If you move your selection cursor or move cursor

  • right over the Fill Handle, you'll see a crosshair.

  • Now, I like to call it--

  • instead of a crosshair, I like to call it an Angry Rabbit.

  • Now, we'll learn a lot of amazing tricks that we can do--

  • not with our selection, not with our move,

  • but with our Angry Rabbit cursor.

  • Those three cursors, we'll see in this video.

  • Hey, let's use our selection cursor.

  • Oh, we already have cell A1 selected.

  • I'm going to type something.

  • I'm going to type Data.

  • Now, to put something in the cell--

  • whether it's text, number, a formula--

  • you have a number of different options.

  • If you want to put the thing in the cell

  • and move the cursor down, you use the Enter key.

  • If, on the other hand--

  • I'm going to select the cell.

  • And remember, just like Word, if we have a word selected

  • and I want to replace it, I do not need to hit the Delete key.

  • I simply start typing.

  • I'm going to type Name.

  • Now we have something in the cell,

  • and we do not want to hit Enter to put it in and move

  • our cursor down.

  • Because we want to enter data across the columns,

  • I want to put the thing in the cell

  • and move my cursor to the right by hitting the Tab key.

  • Now, we're going to fill this out later with student names,

  • but we need quiz 1, quiz 2, quiz 3, and then test 1,

  • test 2, test 3.

  • So in cell B1, I'm going to type quiz space 1.

  • Now, instead of using Enter to put the thing in the cell

  • and go down or tab to go to the right,

  • I actually want to put the thing in the cell

  • and keep the cell selected.

  • So to do that, we use Control Enter.

  • Now, if you remember back to Word,

  • we used Control Enter a lot for page break.

  • But we're going to use Control Enter even more over here

  • in Excel because a lot of times we

  • want to put something in the cell and keep the cell

  • selected.

  • Now the reason we want the cell selected is we

  • might want to add formatting.

  • Or in our case, we want to copy it.

  • Now, remember, that little thing on the lower right hand corner

  • is called a Fill Handle.

  • And if you move your cursor over the Fill Handle,

  • that's the Angry Rabbit.

  • Now click-- that's a left click--

  • and drag.

  • Notice it's giving me a green box covering C1, D1.

  • Now let go.

  • Look at that.

  • Excel has so many magic tricks for our Angry Rabbit.

  • Anytime you have text and a number,

  • if you use your Angry Rabbit to copy it,

  • it will increment the numbers.

  • Now let's do the same thing over here.

  • I'm going to click in cell E1 with my selection cursor.

  • Test space 1.

  • My goal is to put the thing in the cell

  • and keep the cell selected, so I use Control Enter.

  • Now, hover my cursor over the Fill Handle--

  • not the move cursor, not the selection cursor.

  • It's the Angry Rabbit.

  • Click and drag all the way to G1.

  • Let go and look at that.

  • Now, I've got to come down below here

  • and show you a couple of other amazing tricks for that Angry

  • Rabbit.

  • I'm going to click in cell A12 and type J-A-N--

  • that's short for January--

  • Control Enter to put the thing in the cell

  • and keep the cell selected, point to the fill handle.

  • And when you see your Angry Rabbit, click and drag.

  • Now, that is amazing.

  • I'm building my calendars over here in Excel,

  • definitely not in a Word table.

  • It gets better than that.

  • If you put any date into a cell--

  • 10 slash 10 slash 2017--

  • that's a date.

  • I'm going to use Control Enter to put the thing in the cell

  • and keep the cell selected.

  • Now I'm in the hover my cursor.

  • And when I see my Angry Rabbit, I'm

  • going to click and drag down.

  • And look at that.

  • That is a quick way to get a bunch of dates--

  • for example, if you're building a schedule.

  • That is amazing.

  • The Angry Rabbit does many more tricks.

  • That's just a few of them.

  • Now let's continue with our Gradebook up here.

  • I'm going to use my selection cursor and select H1.

  • I'm going to type Total tab--

  • to put the thing in the cell and move to the right--

  • percentage grade, and now I'm going to hit Enter.

  • Now, we're going to have names here of students.

  • But I want a row at the top to tell me

  • the maximum score for each quiz and for each test.

  • And then I want to add them all up

  • so I know the maximum points possible in the class.

  • So right below Name, I'm going to type Max Tab.

  • Now, quizzes are worth 20 points each.

  • So 20 Control Enter-- because I want

  • to put the thing in the cell and keep the cell selected.

  • And let's see what happens if I try to copy this using my Angry

  • Rabbit--

  • 1, 1, and let go.

  • Oh.

  • It's going to give me just the number 20, which happens to be

  • exactly what we want.

  • Up here, that trick worked because there

  • was text and a number.

  • Down here it worked because internally, Excel

  • is programmed to create lists of months and increment dates

  • by day.

  • But if you use your Angry Rabbit on just a plain number

  • or a plain text item with no number, then it just copies.

  • That's exactly what we wanted there.

  • Man, that Angry Rabbit does a lot of amazing tricks.

  • I'm going to type 100, Control Enter,

  • and I'm going to use my Angry Rabbit to click and drag.

  • So each one of the tests will be worth 100 points.

  • Now, down here, we'll enter in the student

  • scores for each assignment.

  • But before we do that, in the name column,

  • I need to write each student's name.

  • The first student--

  • Sioux, Enter because I want to put the thing in the cell

  • and move my cursor down.

  • I'm entering data vertically into a column.

  • The next student name--

  • Abdi.

  • Enter.

  • Then Chin.

  • Enter.

  • Tyrone.

  • Enter.

  • SheliaDawn.

  • Enter.

  • Mo.

  • Enter.

  • Gigi.

  • Oops.

  • Backspace, backspace.

  • Gigi.

  • Enter.

  • Now, that's the end of the student names.

  • But in the very bottom row, I'm going

  • to eventually create a formula to calculate the average.

  • So I actually need to put the word average here and Tab.

  • Now, I want to notice something about the text

  • or words that we entered and the numbers that we entered.

  • In Excel, the default alignment for text

  • is always going to be to the left.

  • The default alignment for numbers

  • is always going to be to the right.

  • Now, later in the class, we'll see the importance

  • of that default behavior.

  • Now, you could totally highlight, and in the Alignment

  • group, start changing the alignment.

  • But that's rarely a good idea unless you're

  • building the very final report for printing.

  • The fact that numbers are aligned to the right,

  • text is aligned to the left, will help us track down

  • errors later in this class.

  • All right.

  • Now, the next thing we need to do

  • is I actually want to add some style formatting to our grade

  • table.

  • Style formatting is-- for example, up in the Font group--

  • and by the way, this alignment has

  • nothing to do with paragraph-level formatting

  • like we learned in Word.

  • Font has nothing to do with character-level formatting

  • like we learned in Word.

  • Everything in Excel is cell formatting.

  • I click on a cell, or I highlight a bunch of cells,

  • and I add formatting to cells.

  • Now, the first thing I'd like to do

  • is notice at the top of each column, we have name, quiz 1,

  • quiz 2.

  • That tells us what goes in this column.

  • And I want to add some formatting that's

  • different for the first row with our column headers

  • than the rest of the table.

  • So with my selection cursor--

  • not the Angry Rabbit.

  • Selection cursor.

  • Click and drag.

  • We've selected all of our column headers.

  • Now I'm going to come up in the Font group,

  • and there's a dropdown that pours paint into our cells.

  • It's actually called Fill Color.

  • I'm going to select red.

  • Now I'm going to go to the Font.

  • And notice it has the letter A, so we

  • know that this is going to add color

  • to the text or the numbers.

  • Click the dropdown and select what you want.

  • Now, I'm going to keep it automatic here, and just--

  • I want to remind you something we talked about in Word.

  • If I print this out right now, it's

  • going to be hard to read the words with a red fill.

  • Now, the trick is-- and we learned this over in Word--

  • is if you squint your eyes and you cannot really see

  • the difference between the text and the fill,

  • then you know you need to increase the value difference.

  • Now, if I was going to use red, I would highlight all of this

  • and use font color white.

  • Now, I'm going to, with this highlighted,

  • come up and select that dark blue there.

  • Now, I'm also going to highlight with my selection cursor

  • the second row, because these numbers are not

  • going to change.

  • We're going to change all the numbers in here for scores.

  • But these I want a different color also.

  • That will tell us this is the max row.

  • So instead of going up to the fill bucket--

  • here's a great trick.

  • We saw this in Word also.

  • I can right click.

  • And look at that.

  • That's the mini toolbar.

  • And in Excel, it has a few different things.

  • I'm going to click the dropdown and select yellow.

  • Now, I would like to select the entire table.

  • And notice I'm using my selection cursor

  • to select the whole table.

  • Now, my goal is to add borders.

  • But I've got to show you a great trick.

  • If you have a table--

  • and notice, our table is defined by a bunch of column headers

  • until there's nothing, and then a bunch of data

  • until there's nothing.

  • If you have a table like that, or a region in your spreadsheet

  • like that, and you want to select everything,

  • there is a keyboard for that.

  • Now, I'm going to use the asterisk key on my number pad

  • and use Control Asterisk.

  • If you don't have the number pad,

  • then you have to use 8 on the regular number pad.

  • So you have to use Control Shift 8.

  • That is an awesome keyboard.

  • Imagine if we had 100 students, or 360 rows

  • for what's called an amortization table in finance.

  • The ability to select the whole table quickly with a keyboard

  • is awesome.

  • Now we can come up to the Font group

  • and click our drop down for all borders.

  • Now, the next thing we want to do

  • is we want to enter all of the student's grades.

  • Now, we could simply enter like this.

  • And I'm just going to do this quick--

  • 3 Enter 3 Enter.

  • I'm just entering dummy data.

  • But notice when I get to the bottom,

  • then I have to take my cursor and come back up to the top

  • and start entering my data.

  • So when we have a rectangular range like this to fill in,

  • if we select it in advance, then--

  • watch this-- just hit your Enter key.

  • When it gets down to the bottom, it knows to jump to the top.

  • So I'm going to hit Enter Enter Enter.

  • And when I am at the bottom, Enter jumps to the top.

  • So this is going to be a entering data trick.

  • All right.

  • Are you ready?

  • I'm going to enter the scores for the first column.

  • Sioux got 15, Enter.

  • Abdi got 19, Enter.

  • Chin, 16.

  • Tyrone, 20.

  • SheliaDawn 19.

  • Mo, 19.

  • Gigi, 11.

  • Now when I hit Enter, it jumps to the top.

  • All right, I'm just going to enter the data,

  • and you can enter the data into your own sheet,

  • always hitting Enter.

  • And then the last score for quiz 3 for Gigi is 19.

  • When I hit Enter, it jumps up to test 1.

  • All right.

  • So we'll enter test 1 scores.

  • 81, Enter.

  • 92, Enter.

  • I'm going to enter all the test scores using Enter.

  • 00:19:41,430 --> 00:19:44,770 And so when I enter the last test score,

  • I'm in the furthest cell away from our upper left hand

  • corner.

  • So when I hit Enter, it jumps back to the top.

  • So that is a great trick if you're entering data

  • into a rectangular range.

  • Now, our next goal is to create a formula that will add.

  • Now, I'm going to click in cell H2.

  • And our goal is to add all of these.

  • That means we get to create our very first formula.

  • Now, the very first formal, we're actually

  • going to use a Built-in Function called SUM.

  • If we go to Home and over to Editing--

  • I'm going to show you this only once because later,

  • we'll use a keyboard.

  • Up in Editing, there's the Auto Sum.

  • Now, hover your cursor over Auto Sum.

  • And notice that it says some, and then,

  • in parentheses, Alt Equals--

  • that's the keyboard.

  • Now, this first time, all I'm going to do

  • is click the Auto Sum.

  • Later, we'll use the keyboard.

  • And this is our first formula.

  • All formulas start with an equal sign

  • as the first character in the cell.

  • After the equal sign, you can put

  • all sorts of different things.

  • And throughout this class, we'll learn

  • lots of different formulas.

  • This particular formula uses the SUM function--

  • S-U-M. That's just a built-in function

  • that is programmed to understand that in this range of cells,

  • it's going to take all those numbers and add them.

  • Then, we have open parentheses for built-in functions.

  • And functions like SUM, AVERAGE, MAX to find the biggest,

  • PMT to calculate a loan payment.

  • But all functions will have open parentheses.

  • And then notice, it says number 1, number 2.

  • We're only using the first argument

  • because we have just one range.

  • But the function will always have this screen tip,

  • where it lists the different items that can

  • go into the built-in function.

  • For us, it's a range of cells.

  • Now, it says, B2--

  • that's the name of that cell right there--

  • and then colon, and then it has the name of that cell

  • right there, G2.

  • So it really means add up all of the cells

  • from that cell all the way to this last cell.

  • And then it has a close parentheses.

  • Now, I'm going to hit enter, and there it is.

  • Automatically, it calculates 360.

  • Now, I want to show you why we made this calculation in Excel

  • rather than used a handheld calculator.

  • There's our total 360.

  • But guess what.

  • We can change any one of these formula inputs--

  • because remember, if I put this cell in Edit mode

  • using the F2 key, that formula is looking

  • at that entire range of cells.

  • I'm going to click escape, but I can

  • change any one of these formula inputs,

  • and instantly, the formula result will change.

  • So for example, quiz 1, it's not worth 20.

  • It was worth 30 points.

  • Now, I'm simply going to select the cell,

  • type 30, and before I hit Enter, watch over here.

  • Now I'm going to hit Enter, and instantly it optics updates.

  • Test 1, it actually was worth 50 points.

  • So I type 5 0.

  • And when I hit Enter--

  • Enter-- the formula instantly updates.

  • That is really the beauty of Excel.

  • We can build a formula once.

  • Any time our formula inputs change, the formula updates.

  • Now, actually, the guys who invented the spreadsheet--

  • Bricklin and Frankston-- they created the first spreadsheet

  • called VisiCalc all the way back in 1980.

  • And they called it a visual calculator.

  • That way, I can simply type anything visually

  • into a input cell, and our calculation, when I hit Enter,

  • updates.

  • Now I'm going to change quiz 1 back to 20 and Enter.

  • Now I want to create the rest of our formulas

  • to add each student's total.

  • I'm going to click in cell H3.

  • Now, we're going to put the SUM function into cell H3

  • and then copy it down.

  • But this time, we're going to use the keyboard

  • to put the SUM function in.

  • And the keyboard is Alt equals.

  • Now, I put the equal sign--

  • in all formulas start with the equal sign--

  • SUM function, open and close parentheses, and look at that.

  • It put the range in.

  • Now, any time you have cells--

  • either a single cell-- we'll see that later--

  • or a range of cells in a formula--

  • we call those Cell References because the formula

  • is referring to the cells to go and get the data.

  • Not only that, but when you use the keyboard for the sum

  • function, notice that Excel guessed

  • what the range of numbers were.

  • Now, it usually guesses right, but you have to verify.

  • Once in a while, when we use the SUM function,

  • it might not grab the right number of cells.

  • So you always want to--

  • Alt equals-- verify that the range is correct,

  • and then you can enter your formula.

  • Now, our goal is to enter the formula,

  • and then keep this cell selected, and copy it down.

  • So I'm going to use Control Enter.

  • Now, I'm going to point to the Fill Handle.

  • And when I see my Angry Rabbit, I'm going to click and drag.

  • And just like that, that is so much faster

  • than a handheld calculator.

  • Now, I want to make sure when I copy

  • a formula to go to the last cell and use F2 to put

  • your formula in Edit mode.

  • And we're verifying that the formula is

  • looking at the correct cells.

  • Now I'm going to hit Enter.

  • All right?

  • So we have calculated the total for each student.

  • Now we need to come over here and we

  • need to calculate the average for quiz 1, quiz 2, 3, and so

  • on.

  • Now we're going to click in cell B10.

  • And if we're going to calculate the average,

  • we need to add everything up and divide by the count.

  • Now, luckily, like the SUM function for adding,

  • when we're calculating an average,

  • we can use the built-in function AVERAGE.

  • Now, there's no keyboard for the AVERAGE.

  • And in fact, there's 450 functions in Excel,

  • and the SUM function is the only one with a keyboard.

  • All right.

  • So that means we need to know how to type out our formula.

  • Well, if we're doing a formula in a cell,

  • the very first character in the cell has to be an equal sign.

  • As soon as we type an equal sign as the first character

  • in a cell, it tells Excel, this is a formula.

  • Now, just as they named SUM function SUM,

  • they gave the AVERAGE calculation

  • a smart name for the built-in function.

  • It's called AVERAGE.

  • Now, I want to type A-V after the equal sign.

  • And look at that.

  • There's a drop down with a list of possible functions that

  • start with A-V. Now, that little icon f of x--

  • that's the icon for built-in function.

  • Now, if we type E-R because we know the name of the function,

  • then, as soon as we see highlighted in blue the AVERAGE

  • function-- the function that we want--

  • I use the Tab key to enter that function in,

  • get the parentheses, and show our screen tip.

  • Now, I'm going to click Escape because I

  • want to do that again and show you something else.

  • Escape will always revert back to whatever was in the cell

  • before we put it in Edit mode.

  • So when I click Escape, because there was nothing when we

  • started, now there's nothing.

  • Now let's try that again.

  • Equals A-V.

  • Now, if you don't know the exact name of the function

  • and you're trying to guess, you can actually

  • go through each one of these functions

  • and read the screen tip.

  • Now, this says Returns the average

  • of the absolute deviations.

  • No, no, no.

  • That's for statistics.

  • So I'm going to use my down arrow

  • to go to the next function in the list.

  • It's highlighted blue, so a screen tip comes up--

  • returns the average arithmetic mean.

  • That's exactly what we want.

  • Now, if we were to down arrow and read each one,

  • the last one average ifs reads, finds average--

  • arithmetic mean--

  • for the cells specified by a given set

  • of conditions or criteria.

  • Now, later in the class, we'll use that function.

  • Now I'm going to up arrow.

  • Average is the one I want.

  • So I'm going to hit Tab.

  • Whenever you see your function highlighted in blue,

  • you use tab.

  • Now, just like the SUM function, it has number 1, number 2,

  • and we can put either single cells or ranges of cells.

  • Now, notice, when we open this up,

  • it didn't automatically highlight the cells

  • like the SUM function.

  • No problem.

  • Our selection cursor works perfectly.

  • I can click and drag.

  • Now, one thing about dragging is-- oh, I made a mistake.

  • Just don't let go of your click, and then it

  • doesn't matter where you are.

  • You can keep moving it until you get the correct range.

  • What you don't want to do is do not highlight the same cell

  • that the formula is in.

  • You do that, you will get a circular reference error.

  • So the dancing ants--

  • as long as you keep that click, they'll keep dancing.

  • And when you see the correct range, let go of the click.

  • Now, there we go.

  • Close parentheses.

  • We have the correct range.

  • Our goal is to put the formula in the cell

  • and copy it-- so I Control Enter, point

  • my cursor to the Fill Handle.

  • When I see my Angry Rabbit, I'm click and drag.

  • Now I'm going to drag it only over to test 3.

  • And just like that, it's calculated our average.

  • Now, remember, any time you copy a formula,

  • you have to go to the last cell and hit

  • F2 to verify that the range of cells or whatever formula

  • inputs are the correct ones.

  • And for us, those are correct.

  • Now I'm going to hit Enter.

  • Now, I'm going to come back over to cell B10

  • and select cell B10.

  • Now, notice, the formula shows us the formula result.

  • So the average for quiz number 1 is 17 points.

  • But any time you have a cell selected,

  • you can also look up in the formula bar,

  • and you'll be able to see the formula you created.

  • So simultaneously, we can see formula result and the formula

  • up in the formula bar.

  • Now, another important thing we need

  • to understand about Excel formulas is notice,

  • we enter that formula and copied it over.

  • Any time we copy a formula, we have

  • to think about the cell references carefully.

  • So I'm going to select B10 and use the F2 key to put

  • this cell in edit mode.

  • Now, notice, if we just read this formula exactly

  • left to right--

  • well, there is an equal sign.

  • All formulas start with an equal sign

  • as the first character in the cell.

  • We are using a built in function the function we're using is

  • average.

  • That function will add them up, divide by the count.

  • And then there's the open and close parentheses,

  • which all functions will have.

  • And then there is a range of cells.

  • Now, that range of cells can be read B3 to B9.

  • And for this one formula in cell B10, that's correct.

  • But because we're copying the formula,

  • we do not want to think of B3 to B9 as B3 to B9.

  • Really, what is it?

  • That range of cells is always going

  • to look at the seven numbers directly above the cell that

  • has the formula.

  • Now, if it really was B3 to B9 when we copied it, watch.

  • Tab F2.

  • If it really was B3 to B9, it would still say, B3 to B9.

  • But no.

  • Look at that.

  • That is simply amazing.

  • It totally moved from the B3 to B9 to C3

  • to C9, which is great, because then we

  • don't have to recreate our formula each time.

  • Now, I'm going to hit Tab and F2 a few times.

  • And I want you to watch how the blue range moves

  • as we copy our formula.

  • Tab F2.

  • Now that's the D column.

  • Tab F2.

  • That's E3 to E9.

  • Tab F2.

  • F3 to F9.

  • Now, there's a special name for this type of cell reference.

  • When we copy our formula and the cell references move,

  • we call those Relative Cell References.

  • That means from the formula's point of view--

  • relative to the formula, where am I going to look?

  • I'm always going to look seven cells above.

  • Tab F2.

  • So when I read this formula, I read

  • it equal sign, built-in function,

  • average, relative cell reference.

  • That's always going to look seven above.

  • All right.

  • Relative Cell References.

  • I'm going to hit Enter.

  • Now, that's going to become important,

  • because we're going to create our percentage grade formula.

  • And we're going to have to put relative cell references

  • and absolute cell references.

  • Now, before we learn about that new cell reference called

  • Absolute, let's just see if we can make a calculation here

  • that for Sioux will calculate her percentage grade.

  • Now, for every student in this column,

  • the calculation is going to be the same.

  • I'm taking the total points that they earned

  • and I'm going to compare it to the total points for the class.

  • Now, the way we do that is we take student's total score

  • divided by the total possible.

  • Now, I'm going to start with Sioux.

  • And this is the first time we're making a formula that doesn't

  • use a built-in function.

  • Of course, equal sign starts all formulas.

  • When you type that equal sign as the first character

  • in the cell, you're telling Excel, I want to do a formula.

  • Now, just as we did with the Average function,

  • we're allowed to take our cursor and click anywhere.

  • Now, that's not correct.

  • That's not correct.

  • That's not correct.

  • But the same thing holds, as we saw with our average function.

  • As long as the dancing ants are moving,

  • you're in full edit mode.

  • I definitely want Sioux's total.

  • If I mistakenly took her test 3 score,

  • man, she would not be happy.

  • Total-- now, that's a Relative Cell Reference.

  • As I copy this formula down, it totally will move,

  • which is what we want, of course, because as the formula

  • copies down, we need the next total for Abdi, then Chin,

  • Tyrone, and so on.

  • Now we need to divide so we're going to use the division

  • symbol forward slash.

  • There's a forward slash next to the Shift key.

  • But oftentimes, it's easier to use the number pad for our math

  • symbols.

  • There is a division symbol on the number pad.

  • So I'm going to divide it by.

  • And now, just as we did with the individual student's score,

  • I'm allowed to select the maximum total for the class.

  • Now, when I hit Enter, we're going to get a decimal.

  • And any time you compare two numbers using division,

  • where the numerator--

  • the top number-- can either be equal to 360 or the max,

  • whatever it is, or less, you're going to get

  • a number between 1 and 0.

  • So this means if we were to convert it to a percentage,

  • it would mean that this person got about 80%

  • of the total points in the class.

  • Now, we'll officially convert it to percentage number format

  • later.

  • But now, I want to come down and do this for Abdi.

  • By the way, look at that.

  • [INAUDIBLE] Abdi's name.

  • F2 to put it in edit mode, and backspace.

  • B-D-I tab.

  • Abdi.

  • All right.

  • Let's do the formula for Abdi.

  • Equal sign, and I can use my mouse to select a cell--

  • or range of cells--

  • divided by-- and I can use my mouse

  • to select the maximum total.

  • If I hit Enter, that's the correct decimal.

  • So Abdi got about 86%.

  • Now, let's try the next formula for Chin.

  • But instead of using our mouse to click and get

  • cell references, I'm going to click in I5.

  • Equal sign starts all formulas.

  • But now I want to use the arrow keys on my navigation pad.

  • I'm going to use left arrow divided by on the number pad,

  • and then up, up, up, arrow, and left arrow.

  • Oftentimes, if the cell references

  • you're trying to put into your formula

  • are close to the cell with the formula,

  • then your arrow keys can be faster than using your mouse.

  • Enter, equal sign, left arrow, division, up,

  • up, up, left, Enter.

  • Now, we do not want to do each one of these individually.

  • So we're going to highlight those formulas

  • we've just created and we're going to use the Delete key.

  • Now let's try this again.

  • Ready?

  • I'm clicking in cell I3.

  • Equal sign, left arrow, division, sign, up arrow,

  • left arrow.

  • Now I want to Control Enter and try and copy this formula.

  • Control Enter.

  • And then I'm going to click and drag.

  • Now, definitely, Tyrone is super happy.

  • He just broke the mold.

  • He just broke the rule.

  • Remember, if these points can only be 360 or less,

  • somehow, he got more than one.

  • Now, if we go to the cell and hit F2,

  • you can see the reason why.

  • These are both Relative Cell References.

  • That blue one is always looking one to the left.

  • And the orange one is always looking one

  • to the left and one up.

  • I'm going to click Escape.

  • Let's go to the top cell.

  • F2.

  • That one works.

  • But because these are both Relative Cell

  • References, Enter F2, that one's not going to work.

  • Now, I'm going to hit Enter, F2 Notice, Enter F2, those two

  • cells are moving the whole way down when we copy our formula.

  • And that's not what we want.

  • Escape.

  • Highlight these.

  • Delete.

  • Now, let's try this again.

  • Equals, left arrow, that's a Relative Cell Reference.

  • As I copy my formula down, I definitely

  • want the formula to see the blue cell to the left.

  • Now, division symbol, up arrow, left arrow.

  • We know that that's not going to work.

  • But now, there's a secret key on your keyboard

  • that will lock this.

  • So as we copy down, it will not move.

  • That H2 will always be looking at H2.

  • And the secret key is the F4 key.

  • I'm going to hit the F4 key.

  • Now, those dollar signs were arbitrary.

  • Back when the very first spreadsheet

  • was created, the guys who created it,

  • they just said, hey, we got to put some symbol there

  • to indicate that now this is locked.

  • When we copy the formula down, that orange cell will not move.

  • All right?

  • So you ready?

  • Control Enter.

  • And now Angry Rabbit.

  • Click and drag.

  • Always, when we copy a formula, we go to the last cell in here.

  • F2.

  • That is amazing.

  • We have one Relative Cell Reference and one Absolute Cell

  • Reference.

  • I often refer to this as a Locked Cell Reference

  • because it's locked on H2, wherever we copy this.

  • And think about that.

  • That's pretty amazing.

  • That means not only can we create an average formula,

  • where we had Relative Cell References,

  • and when we copied them, we wanted them to all be relative,

  • but now we have the ability to have a cell reference

  • that's not locked-- or Relatives Cell Reference--

  • and a cell reference that's locked--

  • or Absolute.

  • I'm going to hit Enter.

  • Now, before we add number formatting

  • to make these appear as percentages,

  • we've got to talk about--

  • in the Home ribbon tab Number group--

  • we've got to talk about what is Number Formatting?

  • Now, before we format these, I'm just

  • going to come over to cell K3, type January sales, Enter.

  • And January sales were $5,622.50.

  • Control Enter.

  • And now I'm going to format this so it shows up as a currency.

  • So no problem.

  • This is called Number Formatting.

  • That's different than our Style Formatting.

  • Number Formatting will only affect our numbers.

  • Now, there's a bunch of built-in options here.

  • Also, we have a dropdown.

  • [AUDIO OUT] I'm going to click on Currency.

  • There's also Accounting.

  • We'll talk about both of those in a later video.

  • But let's just select Currency.

  • And the first thing I want to notice

  • is Number Formatting already saved us a bunch of clicks.

  • We didn't have to type a dollar sign.

  • We didn't have to type a comma separator.

  • And we didn't have to type the zero.

  • That's three clicks that we saved.

  • So Number Formatting will help us reduce the number of clicks

  • when we're entering numbers.

  • Now, we have to look at this Displayed Number

  • and compare it to what's up in the formula bar.

  • Look at that.

  • There's no dollar sign, comma, or zero.

  • If we look at the actual cell where

  • the Number Formatting is applied,

  • this is only what is displayed.

  • This is what we see on the surface of the spreadsheet.

  • If we look up here, we can clearly

  • see that only 1, 2, 3, 4, 5, 6 characters

  • are actually in the cell.

  • Over here, we have 1, 2, 3, 4, 5, 6, 7, 8, 9 characters

  • displayed.

  • So many times, when you use Number Formatting,

  • there will be a disconnect between what

  • we see on the surface of the spreadsheet

  • and what's actually in the cell.

  • Now, here, it's not going to really cause any problem.

  • Is just awesome that we saved some clicks.

  • But let's apply some more Number Formatting Up

  • in the Number Group--

  • wow, look at that.

  • The decreased decimal.

  • I'm going to click it once and twice.

  • And look at that.

  • Now, the Displayed Number is totally

  • different than the actual number that

  • sits underneath in the cells.

  • So that means you have to be careful

  • when you use Number Formatting.

  • And we'll see lots of examples later in the class.

  • And we'll learn how to be perfectly careful when

  • we use Number Formatting.

  • Now, one thing you want to avoid saying is-- remember,

  • we use decreased decimals--

  • there's the actual number.

  • 0.5 is still in the cell.

  • So you don't want to say this rounded the number,

  • because it didn't.

  • All it did was display the number differently

  • than the underlined number.

  • Now, we're going to be careful here and increase

  • our decimals-- click, click--

  • and get back to our properly displayed currency.

  • 00:46:07,740 --> 00:46:11,399 Now, Number Formatting can do all sorts of amazing things.

  • Let's highlight all these numbers, and up in the number

  • group, I'm not going to click that button.

  • If you click that button, it will display percentage Number

  • Format with zero decimals.

  • If that's what you want, you can use that button.

  • I'm going to come up and use the dropdown and select Percentage.

  • You can already see a preview.

  • It will show two decimals.

  • I'm going to click that.

  • And there they are.

  • Now, we want the ability--

  • if you want more decimals, you can use these buttons.

  • And later, we'll learn about the Format Cells dialog box.

  • But just be aware, once we applied that Percentage

  • Number Formatting, those unrounded decimals

  • are still underneath in the cell.

  • Now, for us, since we created those decimals with formulas,

  • we cannot look up in the formula bar to prove that there's some

  • rounded, large set of decimals there.

  • This just displays it that way.

  • If, in fact, you have a formula and you really

  • want to see what's in the cell, you

  • have to reverse the Number Formatting.

  • And the way you do that is you go up to the Number Group.

  • And we click the dropdown.

  • And the General Number Formatting

  • is the Eraser that will erase any Number Formatting that we

  • may have applied to the cell.

  • So when I click general, even though there's a formula there,

  • we can see all the decimals.

  • Now I'm going to Control Z to undo

  • that because I want to leave these as formatted percentages.

  • Now, there's two more things we're

  • going to learn in this video.

  • The next thing is when I print this,

  • I would like to print all of this,

  • not any of this other stuff off to the side.

  • Now, what many people do in Excel is they use Control P.

  • And that opens up the Print dialog box and Print Preview.

  • Now, lots of times, people don't bother to look over here.

  • They just click the Print button.

  • They go to the printer, and then it's

  • printed across a bunch of pages.

  • It's not what they wanted.

  • So Control P, always look at the preview.

  • This definitely is not what we want.

  • And not only do I want to get rid of all that stuff,

  • but if you look down here, there's

  • actually stuff on page 2.

  • And if I click forward, there's that little extra bit

  • off to the side.

  • Now, if we want to get out of Backstage view here--

  • or print--

  • we click escape.

  • Now we're going to talk about Page Setup.

  • Now, we did a lot of Page Setup in Word.

  • And in Word, there was a different ribbon tab.

  • Over here in Excel, it's called Page Layout.

  • I'm going to click on Page Layout.

  • But look at this.

  • Just like over in Word, there's a group called Page Setup.

  • Now, the buttons and the things we

  • can do in Excel for Page Setup are much

  • different than over in Word.

  • But Page Setup group--

  • there's our dialogue launcher.

  • Now, just as we learned over in Word,

  • if you use a particular feature a lot,

  • then you want to teach yourself the Alt keyboards

  • to open up a particular feature.

  • Now, if we hit the Alt key, notice

  • that it gives us a single letter in a screen tip for each one

  • of the ribbon tabs.

  • So if I want to open up Page Layout,

  • I have to click P. I'm going to tap P. Now

  • I see that the dialog launcher has SP,

  • so in order to open that up, SP.

  • Now I'm going to click Escape because we all

  • saw this a bunch of times earlier in the class.

  • The same keyboard in Word works here in Excel.

  • When we want to get to Page Setup Dialog Box

  • to have complete control over how this sheet prints,

  • we use the keyboard Alt PSP.

  • Now, there's four tabs--

  • Page, Margin, Header and Footer, and Sheet.

  • We're going to start with Page.

  • I definitely want this to print out landscape.

  • Now, the way you can decide is usually

  • if it's wider than it is tall, then landscape will look good.

  • If the page is too big and you want to fit it,

  • then we can do one page wide by one page tall.

  • Now, this wasn't over in Word.

  • But over here, we can definitely use it.

  • That's not what we want here, though.

  • I want to increase the percentage.

  • Now, I'm not exactly sure which percentage I need here,

  • so I'm just going to try 125.

  • If it's not exactly what I want, I'll

  • come back and fix it later.

  • Now, I'm going to go over to margins.

  • And just like in Word, we can set the margins for top,

  • right, bottom, left.

  • We also set how far from the edge of the piece of paper

  • the Header or Footer will be printed.

  • Now, most of the time those are fine.

  • If you really need to fine-tune it, use those.

  • Otherwise oftentimes, we can just say, center on the page.

  • Now, if you have a small table, it kind of

  • looks funny if it's centered vertically also.

  • So we'll just say, horizontal.

  • Now we go over to Header and Footer.

  • And in Excel, we have Headers that the top, a dropdown

  • for certain options, Escape, and then a button

  • to get to Custom Header.

  • Then down at the bottom, we have a preview of our Footer,

  • a dropdown for certain items, Escape, and then a button

  • to get to the full Custom Footer.

  • Now, we're going to click on Custom Footer.

  • But before we do that, there's a great dropdown.

  • And we saw this over in Word.

  • If we select page 1 of question mark, that's the same as page

  • x of y that we saw over in Word.

  • I'm going to click on page 1 of question mark.

  • Now, already, we see a preview there.

  • Now, we only have one page here.

  • But I'm going to show you this great often used Footer

  • here, even though we have only one page.

  • If we had 10 pages, it would say, 1 of 10,

  • 2 of 10, and so on.

  • Now, I'm going to click Custom Footer.

  • Right in the middle, it shows you the secret code

  • that will dynamically create our Footer

  • for however many pages we have.

  • Now, there's a left, center, and right section.

  • You can actually just type something like, Your Name.

  • You could actually highlight your text

  • and click the Font button.

  • And you could add something like Bold and 12.

  • Click OK.

  • Oftentimes, companies will put their company name here.

  • We can also come to the right section.

  • And there's a bunch of options here.

  • That will print out just the single page number.

  • That will print out the total number of pages.

  • That will print the date-- and I'm

  • going to click on that for date.

  • That will print the time.

  • That will print the full file path name.

  • That will print the Excel Workbook name.

  • There.

  • I'm going to click Insert Sheet.

  • But before we do that, I'm going to click right

  • before the code for the current date.

  • And now I'm going to click the little icon for please

  • give me the sheet name.

  • But now, I'm immediately going to type Space Dash Space.

  • You also could insert a picture.

  • Now, I'm going to click OK.

  • Hey, look at that.

  • There's our preview.

  • The last tab in Page Setup--

  • Sheet.

  • There is an awesome option.

  • Print Area.

  • Now, this is a text box.

  • And that little icon means that we can collapse this text box

  • and highlight cells in the sheet.

  • Now, all we have to do is make sure our cursor is

  • in print area, then simply click in cell A1.

  • And with our selection cursor, click and drag.

  • It's the same as our formulas.

  • If we make a mistake, just don't let

  • go until you highlight just the range you want.

  • Now, later in the class, we'll see how

  • to use rows to repeat at top.

  • Now, just as an example, if we had 100 students,

  • I could highlight just row 1 and 2 right there,

  • and on every page, it would print those Headers at the top.

  • Columns can be repeated also.

  • Here are some other options for different items you can print.

  • We don't know what comments or errors are yet,

  • but later we will.

  • And then if you have many pages on one sheet,

  • you can decide to print down and then over, or over and down.

  • I'm going to reclick that.

  • All right.

  • Page Setup.

  • Now I'm going to click OK.

  • Now I'm going to Control P. And that is looking beautiful.

  • Now, I got to tell you a little story.

  • Back in 2001, I had a tax accounting job

  • at Broderick Consulting in Berkeley, California.

  • And the first day I came in, Mary Broderick-- who

  • was an awesome accountant-- asked

  • me to work on some Excel spreadsheet

  • where I needed to enter data and print it out.

  • And all I did-- this was like an hour into my job--

  • I typed everything in, did a little Page Setup like this,

  • printed it, and brought it over to her desk.

  • And she was totally impressed, just with this simple Page

  • Setup that it looked so nice and professional.

  • Not only that, but fast forward to today,

  • October 14th, 2017, one of the students

  • in my class, Enoch yesterday, said

  • he did exactly the same thing at work.

  • Did some simple Page Setup, printed it out,

  • and his boss said, wow, that looks really great.

  • So simple task like this, yet very important and can get you

  • noticed at work quickly.

  • Now, I'm not going to click Print.

  • I'm going to click Escape.

  • Now, another potentially important thing

  • about a template like this is if I click in this cell

  • and look up in the formula bar, that's 100,

  • click in that cell, that's 55.

  • But notice that is a formula.

  • So is this.

  • And if I click down here for the average row, that's a formula.

  • Sometimes, it's nice to format the cells that

  • have formulas differently than the cells that have raw data.

  • Now, I have a convention that I've

  • been using for decades to help me so I don't accidentally

  • come when I use this next time and highlight everything

  • and delete.

  • My convention is I'm going to highlight

  • all the cells with formulas.

  • Now, we're going to use the Control

  • key to select a second range--

  • not next to each other.

  • Back in Word, in Windows Explorer,

  • we use the Control key many times

  • to select items not next to each other.

  • But now we have those ranges selected.

  • Right click.

  • And I'm going to use the mini toolbar.

  • Click the dropdown for my paint bucket to fill with color.

  • I don't see the color I want.

  • So I'm going to click More Colors.

  • And in the standard, I'm very carefully

  • going to click that green right there.

  • Now, you can pick whichever color you want.

  • Click OK.

  • But that is going to be helpful.

  • Now, I clearly know where the cells with formulas

  • are, and the ones that do not have formulas.

  • Now our last task is, now that we have our template--

  • I can see down here, this is for Gradebook Fall 17--

  • but I want to copy the entire sheet over so that I can use it

  • for winter 2018.

  • Now, the beautiful thing about copying an entire sheet

  • is that it will copy everything here--

  • the formulas, the formatting, and the Page Setup.

  • If I were to simply highlight this, copy, paste over here,

  • it wouldn't paste the Page Setup.

  • So watch this.

  • The long way to copy a sheet is to right click the sheet--

  • don't right click the cell or over here.

  • You want to right click the actual sheet and point to Move

  • or Copy.

  • Now, the nice thing about this Move or Copy dialog box

  • is that I'm allowed to Move or Copy this to somewhere

  • in this workbook-- or if I click the dropdown, a new workbook.

  • Or if I had multiple other workbooks open,

  • I could select any one of those.

  • Now, for us--

  • I'm going to click Escape--

  • we want to copy it to a new location in this workbook.

  • Now we need to select where we're going to copy it

  • within this workbook.

  • And notice it says before sheet.

  • So it's going to be copied before sheet 4.

  • And most important-- we need to check Copy.

  • If we don't check Copy, it will move it.

  • But I want a copy so I check it.

  • Click OK, and there's my new sheet.

  • Now I can double click and rename this Winter 2018

  • and Enter.

  • Now, the fastest way to copy this

  • to some other location in this workbook is this--

  • we're going to point to this sheet,

  • and I want you to click and drag up.

  • Now, notice there's a piece of paper sitting under my mouse.

  • And if I come over to the side, there's a little black arrow--

  • little black downward-pointing arrow.

  • That says I'm going to move it here.

  • Now, right now-- watch this--

  • I'm going to move it.

  • I can see the arrow pointing when I let go of my click,

  • it moved it.

  • Now I'm going to move it back.

  • Click, drag up, I see the piece of paper,

  • I see the downward-pointing black arrow, I drop.

  • That moved it.

  • The way you copy it is I click, drag up,

  • I see that piece of paper, but now I hold control.

  • The control gives me a plus.

  • That plus says I'm copying it.

  • Now, I'm going to make sure my triangle is

  • pointing after winter, and I let go of my mouse--

  • not control key.

  • Now I let go of control.

  • And there I have copy.

  • Now, that might take a little practice,

  • but it is the fastest way to copy a sheet.

  • Now, I'm going to double click this and call this--

  • and I'm going to call this spring 2018 and Enter.

  • All right.

  • That was an epic introduction to Excel.

  • We saw how to enter text data and number data.

  • We saw how to do Stylistic Formatting.

  • We saw how to create formulas using

  • built-in functions like the SUM, like the Average function.

  • We also saw F2 how to create formulas that have Relative

  • and Absolute Cell References.

  • We talked about Number Formatting

  • and how Number Formatting was a facade.

  • We did control P--

  • beautiful Page Setup-- and Escape.

  • We even saw how to copy sheets over.

  • Now, our next introduction video will

  • be all about adding and counting with different types

  • of formulas.

  • All right, if you like this video,

  • be sure to click that thumbs up, comment,

  • and sub, because there's lots more videos to come.

  • All right, we'll see you next video.

00:00:00,000 --> 00:00:04,740 Welcome to Excel Basics Number 1.

字幕與單字

單字即點即查 點擊單字可以查詢單字解釋

B1 中級 美國腔

Excel基礎知識1:Excel入門1:格式化、公式、單元格引用、頁面設置。 (Excel Basics 1: Introduction To Excel 1: Formatting, Formulas, Cell References, Page Setup)

  • 21 0
    Li Sang Chen 發佈於 2021 年 01 月 14 日
影片單字