Excel for Beginners - The Complete Course

Summary

本视频是《Excel初学者完整课程》的一部分，旨在帮助观众掌握Excel的基本操作。视频从创建工作簿和保存文件开始，详细介绍了电子表格的解剖结构，包括列、行、单元格和范围的概念。接着，视频教授了如何输入单元格值、使用自动填充功能、编辑和格式化单元格内容。此外，还介绍了Excel中的公式和函数的使用，如何创建图表，以及如何打印和发布工作簿。视频内容丰富，适合所有级别的Excel用户，帮助他们提高工作效率和数据处理能力。

Takeaways

• 📝 Excel 初学者完整课程，适合任何水平的用户，承诺通过视频学习有效使用 Excel。
• 📋 介绍如何在 Excel 中创建工作簿、保存以及理解电子表格的基础知识。
• 🔍 讲解了如何在 Excel 中选择单元格、输入数据、使用填充手柄和进行基本的数据操作。
• 📊 演示了如何使用公式和函数来执行计算，包括 SUM、AVERAGE 等基本函数。
• 🖌️ 强调了格式化单元格、行和列的重要性，并提供了一些快速格式化的技巧。
• 🎨 介绍了如何创建和修改基本图表，以及如何通过图表设计和元素来增强图表的表现力。
• 🖨️ 讨论了打印选项和预览，包括如何调整页面布局和打印设置以适应不同的打印需求。
• 📄 说明了如何将 Excel 文档保存为 PDF 或其他格式，并提供了发布和共享文档的多种选项。
• 🔗 提供了如何使用名称框和命名范围来简化公式和函数的引用。
• 🔄 展示了如何使用自动填充手柄快速复制和应用公式。
• 👨‍🏫 推荐了其他 Excel 教程视频，以便用户可以进一步学习和提高他们的 Excel 技能。
• 💡 强调了使用快捷键和工具栏上的按钮来提高 Excel 使用效率的重要性。

Q & A

Excel 初学者完整课程的目的是什么？

Excel 初学者完整课程的目的是教授初学者如何有效使用 Excel，无论他们的经验有多少，通过观看视频和跟随操作，学习 Excel 的基础知识。

如何在 Excel 中创建工作簿？

在 Excel 中创建工作簿，可以点击“新建空白工作簿”或者选择模板来打开并使用。

Excel 工作簿中的“工作表”和“工作簿”有什么区别？

在 Excel 中，工作表（也称为电子表格或工作表）是单个的表格，而工作簿是包含一个或多个工作表的集合。

Excel 中的单元格是如何定义的？

Excel 中的单元格是通过其所在列的字母和行的数字来定义的，例如，单元格 E10 是位于列 E 和行 10 的交叉点。

如何在 Excel 中输入数据？

在 Excel 中输入数据需要首先选中单元格，然后键入数据，并通过按 Enter 或 Return 键确认输入。

Excel 中的自动填充功能是如何工作的？

Excel 的自动填充功能可以识别数字或文本的模式，并自动将该模式应用到选定的单元格范围中，以避免重复输入。

如何在 Excel 中清除或复制单元格内容？

在 Excel 中，可以通过选中单元格或单元格范围，然后使用 Ctrl+C 复制，使用 Ctrl+V 粘贴，或者选中单元格后按 Delete 键清除内容。

Excel 中的公式和函数有什么区别？

公式是用于执行计算的表达式，通常以等号（=）开始，而函数是预定义的公式，用于执行特定任务，如求和（SUM）、平均（AVERAGE）等。

如何在 Excel 中使用名称框快速引用单元格或范围？

在 Excel 中，可以使用名称框（位于窗口左上角）来查看或输入单元格或范围的名称，以便快速引用和操作。

Excel 中的格式刷功能如何使用？

在 Excel 中，可以使用格式刷（位于“开始”选项卡的“剪贴板”组中）来复制一个单元格的格式，并将其应用到其他单元格或范围。

如何在 Excel 中调整列宽和行高？

在 Excel 中，可以通过点击列或行的边界，然后拖动来调整列宽或行高。也可以通过双击边界来自动调整列宽以适应内容。

Outlines

00:00

📚 初学者的Excel完整课程介绍

本段落介绍了一个面向初学者的Excel完整课程，强调无论用户对Excel的经验有多少，都能通过观看视频和跟随操作来学习使用Excel。视频承诺将教授创建工作簿、保存文件、理解电子表格的基本结构和Microsoft Excel的布局。

05:02

📋 Excel工作簿和单元格的基本操作

这一部分详细介绍了如何在Excel中创建工作簿、保存文件、理解电子表格的解剖结构，包括列、行、单元格、活动单元格和范围。同时，还讲解了如何在Excel中输入数据、使用填充手柄和进行基本的数据编辑。

10:02

🔄 编辑和移动单元格数据

本段讲解了如何在Excel中编辑单元格数据，包括修正拼写错误、使用Shift键和Tab键移动活动单元格、清除和复制单元格内容。此外，还介绍了如何使用自动填充手柄来避免重复输入数据，并展示了如何插入新行和列以及添加列标题。

15:02

📊 学习Excel公式和函数

这一部分专注于Excel中的公式和函数，教授如何创建动态公式、使用相对和绝对单元格引用、处理错误消息和警告。介绍了SUM函数来计算总和，并解释了如何使用自动求和功能。此外，还提到了其他常用函数，如AVERAGE、MAX、MIN和COUNT。

20:03

🖨️ Excel打印和发布选项

最后一段讨论了Excel中的打印和发布选项，包括如何预览打印结果、调整页面布局、使用页面分隔预览来管理页面分隔符，以及如何将工作簿保存为PDF或其他格式以便于分享和发布。还提到了如何使用OneDrive进行文件共享和发布。

Keywords

💡Excel 初学者

视频针对刚开始学习使用Excel的初学者，提供了一系列基础教程和操作指南。

💡工作簿

Excel中的工作簿是由一个或多个工作表组成的文件，用户可以在其中创建、编辑和保存数据。

💡单元格

单元格是Excel工作表中行和列的交叉点，可以包含数据、公式或引用。

💡公式

Excel中的公式是用来执行计算的表达式，通常以等号（=）开始，可以包含数字、单元格引用和操作符。

💡函数

函数是Excel中预定义的公式，可以快速执行特定任务，如SUM、AVERAGE等，用于数据分析和处理。

💡格式化

格式化是指改变Excel中单元格、行、列的外观和布局，包括字体、颜色、对齐方式等。

💡图表

图表是数据可视化的一种方式，Excel允许用户根据数据创建各种类型的图表，如柱状图、饼图等。

💡打印和发布

Excel提供了多种打印和发布选项，允许用户将工作簿输出为纸质文档或电子格式，如PDF。

💡自动填充

自动填充是Excel中的一个功能，可以识别数据模式并自动将该模式应用到其他单元格，加快数据输入过程。

💡数据校正

数据校正是指在Excel中更正或清理数据，以确保数据的准确性和一致性。

💡快捷键

快捷键是键盘上的特定键组合，用于快速执行Excel中的命令，提高工作效率。

Highlights

本视频是针对Excel初学者的完整课程，无论你的Excel经验有多少，都可以学习如何有效使用Excel。

Excel的界面布局包括工作表、列、行、单元格、活动单元格和范围。

Excel工作簿由多个工作表组成，每个工作表可以包含超过17亿个单元格。

通过Excel的填充柄和自动填充功能，可以快速复制和扩展数据模式。

Excel公式和函数的使用，可以进行复杂的计算和数据分析。

Excel的格式化功能可以改变单元格、行、列的外观，使其更易于阅读和理解。

Excel的图表功能可以将数据可视化，便于分析和展示。

Excel的打印和发布选项允许用户将工作簿以不同格式保存和共享。

Excel的自动求和功能可以快速计算一系列数值的总和。

Excel的自动更正功能可以帮助纠正常见拼写错误，并提高输入效率。

Excel中的绝对和相对单元格引用，对于创建动态公式非常重要。

Excel的命名范围功能可以方便地引用和操作特定的数据区域。

Excel的查找和替换功能可以快速更新工作表中的特定数据。

Excel的格式刷工具可以快速复制一个单元格的格式并应用到其他单元格。

Excel的页面布局视图可以帮助用户预览和调整打印输出。

Excel的自定义缩放功能可以将整个工作表调整到一页纸上，方便打印和查看。

Excel的快速访问工具栏可以自定义，以便于快速使用常用功能。

Transcripts

[Music]

this is excel for beginners the complete

course it doesn't matter how little

experience you have in excel i promise

you that as you watch this video and

follow along that you will learn

everything you need to know to get

started using excel effectively don't be

intimidated by excel you can do it and

regardless of what version of excel

you're using this video is what you need

to get started using excel in this first

section we're going to look at how to

create workbooks in excel and how to

save them and also we'll learn the

basics of what i call the anatomy of a

spreadsheet and also the layout in

microsoft excel let's get started so

whenever you start up excel it should

take you first to a screen similar to

this one you may see a list of recently

used spreadsheets but you'll also have a

way to create a new blank workbook and

i'm going to click here just to show

that there will often also be templates

that you can click to open up and use

you can also search and there's a button

here to more templates but i'm just

going to click blank workbook to open up

a brand new completely blank workbook in

microsoft excel and before we create

anything in this workbook let's talk

about the anatomy of a spreadsheet when

you're working in microsoft excel you

will always have at least one sheet you

can see here in the lower left it says

sheet1 but it's possible to have

multiple sheets and all of those sheets

collectively together are called a

workbook right now my workbook just has

the one spreadsheet in excel

spreadsheets are made up of columns you

can see this is column e this is column

c this is column k and when i select the

letter for that column the entire column

gets selected we also have rows in excel

this is row 3 this is row 9 this is row

10. so every column has a column letter

and every row has a row number now the

intersection of a column and a row is

what we call a cell so for example this

cell here is the intersection of column

e and row 10. and an excel workbook can

contain over 17 billion cells when you

click on a cell that becomes the active

cell now one of the most powerful things

about the cells in excel is that each

one can be described by the intersection

of its column and row so for example

this is cell f8 and that's how you say

it f8 what cell is this this is cell l4

and this is cell b7 now because of this

fact that you can describe every single

cell in microsoft excel there's a lot of

exciting possibilities that come about

because of that and you'll see that as

you begin using excel more the next part

of the anatomy of a spreadsheet is range

a range is a collection of cells that

are generally grouped together so i've

clicked and dragged to select a group of

cells in excel this is a range and we

can also describe every range in

microsoft excel once again using the

column letters and the row numbers the

way you do this is you start in the

upper left corner of the range and you

describe that cell so d4 through and

then you go to the lower right corner

and describe that cell j14 so the

description of this range its reference

basically is d4 through j14 and in excel

the way you indicate through is with a

colon so d4 through j14 this is very

important and will become more and more

useful and important as you keep using

excel so that is the anatomy of a

spreadsheet we have columns we have rows

we have cells the cell that you've

selected is the active cell and we have

ranges and all of this is done on a

sheet or spreadsheet or it's also called

worksheet you can add more worksheets by

clicking this plus sign now i have two

sheets now i have three sheets and the

collection of all of these sheets

together is what we call a workbook now

in addition to the anatomy of the

spreadsheet it's also important to

understand the layout that we have to

work with in microsoft excel as you use

excel you'll notice that there are tabs

across the top of the screen generally

you'll start on the home tab but we also

have the insert tab page layout tab data

tab etc whenever you click a tab the

tool buttons that you have here below

change and this part of the layout is

called the ribbon so if i click the

formulas tab i get the formulas ribbon

if i click the view tab the ribbon

completely changes now i have the view

ribbon

and each ribbon is divided up into

groups you can see the groups are

separated by lines so i have a charts

group i have a tours group a comments

group a tables group etc now not all of

the options can fit in such a small

group for example my charts group is not

big enough to hold all of the options so

some of the groups have what i call a

launch button or sometimes it's called a

dialog launcher but i'll just call it a

launch button not all of the groups have

these launch buttons but if you click on

a launch button it'll open up with even

more options than could normally fit in

the space provided on the ribbon in

addition to the tabs the ribbons and the

groups and the launch buttons we also

have some other layout features that you

really need to know over here on the

right we have a scroll bar that you can

use to move down your spreadsheet and up

the spreadsheet we also have a

horizontal scroll bar underneath that we

have a zoom slider if i slide this to

the right i zoom in on the spreadsheet

if i slide it to the left i zoom out on

the spreadsheet doing this does not

change the data at all it just zooms in

or out i have some view buttons here in

the lower right corner i can go to page

break preview or the page layout view

but most excel users spend most of their

time in normal view here in the upper

left we have what's called the name box

whenever you click on a cell you can

look in the name box to see the

description of that cell or the name for

that cell and later you'll learn that

this name box can do even more for you

here at the top of my excel layout i

have the quick access toolbar and i have

customized this yours may look different

but this gives me quick access to some

of the most commonly used features in

microsoft excel my save button an undo

button autosum and more here's my title

for this spreadsheet over here i have my

close button it looks like an x if i

click that the spreadsheet will close

and then finally here i have a very

special bar called the formula bar in

many cases this is where you'll go to

enter formulas into microsoft excel so

now that you know about the anatomy of a

spreadsheet and also the layout in

microsoft excel you're completely ready

to begin learning to use excel at this

point i'm going to click file and save

but because this is my first time saving

this document it's having me do a save

as i need to decide where to save this

excel workbook so i'm going to click on

browse and i'm just going to save it in

my documents and i'll just write a name

for it excel for beginners complete

course and save it's time now for the

second segment of this course which is

how to enter cell values in microsoft

excel and let's say i work for a small

business and i've been asked to create a

payroll spreadsheet for the employees

here in column a i'd like to have the

employee id numbers in column b the

employee names and then some information

about their wages the hours worked how

much they are owed etc and anytime in

excel when you want to enter data into a

cell it's important to first select that

cell there's a saying that i heard once

that i use over and over which is select

to effect if i want to affect this

spreadsheet if i want to affect the data

in the spreadsheet first i have to

select something so i want to effect a1

so i'll select it just by clicking on it

once i can now affect it by typing so

let's say the employee id number for

employee 1 is simply 1. i type the

number 1 and that data appears in the

cell but it's not truly entered in the

cell until i tap enter or return on the

keyboard now the data is inside that

cell notice when i tapped enter the

active cell moved down one and that's

great because that's where i want to

type the next number two enter three

enter four enter and i could just

continue to do this down the spreadsheet

but that is kind of a repetitive task

and excel has a built-in feature to make

it so it's not so repetitive there's

something called the fill handle or the

auto fill handle in microsoft excel and

the way it works is that it's been set

up to identify patterns and then to

extend those patterns so i want to show

excel the pattern that is developing

here the pattern is adding one to the

previous number so one two three four

the pattern is clear i'm just adding one

so if i click and drag to select that

range a1 through a4 the pattern should

be clear now i'm going to zoom in a

little bit so you can see this a little

better but if you look in the lower

right corner of this range that i've

selected you'll see a little green

square that's what we call the auto fill

handle or the fill handle if i put my

mouse pointer directly on that little

green square notice that the mouse

pointer becomes a black plus sign now if

i click and hold the click i can drag

down the spreadsheet i'm going to go all

the way to

70 and then i can release the mouse

button and look what it does excel

automatically extended the pattern that

i showed it

next i'm going to click on cell b1

because i want to affect that cell and

i'll type the name of the first employee

and then i'll tap enter and again the

active cell moves down the spreadsheet i

can type the next employee name and i'm

just going to use first names so that

this goes faster and the third name now

you'll notice i misspelled one of the

names this is supposed to be james not

jams so this gives me the opportunity to

teach you a very important concept in

microsoft excel and that is the

difference between clicking on a cell

and clicking in a cell when you click on

a cell once the whole cell is selected

so if i click on jams and i try to fix

this so that it says james the first

letter i type will erase whatever the

contents of that cell already is so

that's what happens when you click on a

cell and type it erases everything

that's already there well what if i've

got five or six words in this cell and

only one word is misspelled i don't want

to have to retype all of it so instead

of clicking on the cell i'm going to

double click on the cell and that gets

me inside the cell now you can see my

flashing cursor and i can use the arrow

keys to move that cursor to exactly the

right place and then i can type and fix

the misspelled word then i can tap enter

on the keyboard and that correction is

entered into the cell now as you're

entering data into excel every time you

tap enter the active cell should move

down but what if you need to move up you

can hold the shift key and tap enter and

that should move you up the spreadsheet

give me a few seconds to finish entering

names and then i'll resume now that i've

finished my list of names i'm going to

click here on cell b1 because i need to

show you in addition to moving down the

spreadsheet by tapping enter and up the

spreadsheet by using shift enter you can

move right and left by using the tab key

so if i tap tab i move to the right now

i'm on cell c1 and i can type in the

hourly wage of julia if i need to move

to the left you can probably guess what

keys i need to press shift tab moves me

to the left so enter is down shift enter

is up tab is to the right shift tab is

to the left so i'm going to tab over to

cell d1 and i'll put in the number of

hours julia has worked in this month

160. i'll tab over the next thing we

need to learn about entering cell values

in excel is how to clear and copy cell

contents so we've already looked at how

to edit them by double clicking but what

if you just need to copy the contents of

one cell to another cell you can click

on a cell or even a range if you prefer

and then hold the ctrl key and tap c to

copy now there are other ways to do this

you could go to the home tab and you

could click this button here to copy the

contents of cell c1 in this case it's

also possible to right click copy but in

most cases simply using ctrl c to copy

and ctrl v to paste is going to be your

fastest way to copy paste so i could

just keep pasting in the number 15 in

each of these cells but remember we have

a tool called the auto fill handle and

it's great to avoid the repetitive

entering of data so now with just cell

c5 selected what is the pattern that the

autofill handle is going to see the only

pattern i'm showing it is one number if

i click on the autofill handle and pull

down the spreadsheet all the way down to

the bottom it's going to extend that

pattern of just the number 15 and it

basically copies that same number all

the way down the page so that's a

shortcut that we can use but what if

this number isn't accurate for all of

these employees maybe amelia is making

more than 15 dollars an hour i could

click on that cell and just overwrite it

with another number tap enter and that's

a very fast way to do it but there are

times when you need to not only delete

the information that's there by either

overwriting it or tapping the delete key

or backspace key on the keyboard but

sometimes you need to completely clear

everything that's in that cell not just

the data but maybe also the formatting

and other information you can do that a

couple of different ways one is to right

click on the cell and choose clear

contents but one of my favorite ways is

to select the cell or range of cells and

then go up here to the home tab on the

home ribbon in the editing group we have

this button and if you click the arrow

next to the button you can clear all or

just clear the formats contents

hyperlinks etc i'll click clear all and

everything is completely wiped clean out

of that range i'll hold ctrl and tap z

to undo that but it's important that you

know about that feature now as i'm

building this spreadsheet i'm realizing

it's going to be hard for me to remember

exactly what each of these columns of

data represents 15 what 16 what 160 what

i should have put in column titles or

headings so let's look at how to insert

new rows and columns into excel if i

need another blank row above row number

one what can i do well all i have to do

is right click on the number one and

choose insert if i need a new blank

column all i need to do is right click

on let's say column a and choose insert

and i get a column to the left of column

a i can also insert columns in between

data so i'll right click on d insert now

i get a blank column between c and now e

and i can do the same with rows i'm

going to undo all of that to get back to

this point where i just have a new blank

row above my data and i'll click here on

cell a1 and i'll call this employee id

i'll use tab to move over to cell b1 and

i'll type name i'll use tab to go over

to c1 and i'll type hourly wage and i'll

tap tab to move over to d1 and i'll type

hours worked i'll tab over to e1 and

type taxable income at this point i'll

tap enter on the keyboard now i

understand a lot better what all of the

data in these columns is about now you

probably noticed that not all of my data

in row number one actually fits inside

the columns for example in c1 hourly

wage doesn't really fit in the space i

have provided for it so let's look at

how to fix that i could click and hold

the click between the letter c and the

letter d column c and d and then i could

drag that column to make it so the text

fits and i could do the same with column

a column d column e but let me show you

a faster way if i click and drag across

from a all the way to e actually

clicking on the column letters and then

if i double click between any two of

these column letters let's say between b

and c i'll double click what happens is

all of the columns are resized to the

perfect width so that the data that's

inside those columns will fit that looks

a lot better now as i'm entering data in

excel from time to time i may need a

little help with that for example let's

say that the standard hourly wage

increases from fifteen dollars an hour

to fifteen dollars and fifty cents an

hour how could i quickly update all of

this information one way to do that

would be to use find and replace so here

in excel if i go to the home tab on the

home ribbon in the editing group you'll

find find and select you may see the

text or you may just have a magnifying

glass if you click on the arrow next to

that notice that there's an option for

find and there's also replace i'm going

to select replace and this lets me find

what let's say 15 and replace it with

15.5

and i'll select replace all all done we

made 70 replacements i click ok and

close you'll notice that all of the 15s

have been turned into 15.5 now there is

one problem with that notice employee id

number 15 and so i can just update that

manually so that is a great time saver

if you want to keep your hands on the

keyboard more instead of using the mouse

to go over here and click on find and

replace you could hold ctrl on the

keyboard and tap h and that brings up

the find and replace option generally

speaking anytime you can keep your hands

on the keyboard and not use the mouse

you'll be able to work more efficiently

in excel one other trick for entering

cell values and data into microsoft

excel that you'll definitely want to

know is how to use the autocorrect

features so if i go here to file and

choose more i can go to options and here

in my excel options i could go down here

to proofing and here you'll see

autocorrect options change how excel

corrects and formats text as you type if

i click this button it lets me adjust

some of these settings so for example

right now if i type a day it will

automatically capitalize the first

letter of that day and there's some

other settings here that you can change

but down here you'll notice that there's

a replace blank with blank and there's

already some examples here of common

misspelled words and how they'll be

automatically corrected so let's say i

need to type the name of my youtube

channel quite often instead of typing

the beautiful name technology for

teachers and students over and over and

over

what i could do is i could just type t4

tas and then i'll tab over and this will

make it so that i can type t4 tas and it

will automatically be replaced with the

most descriptive and catchy name in the

history of youtube channels i'll click

add click ok let's try it out i'll click

ok again so now if i click on a cell and

type t4 tas tap enter it's automatically

auto corrected to be the full name of my

youtube channel i'm going to undo that

but definitely think about the

autocorrect features in microsoft excel

and adjust them to your advantage i've

now updated some of these hourly wages

and i've put in all of the hours worked

for each employee one last thing i want

to show you about entering values and

managing the data that you put into

excel is i want to show you how to move

the contents of cells we've already

looked at how to copy paste by clicking

on a cell or range ctrl c to copy ctrl v

to paste you could also do a cut paste

ctrl x to cut ctrl v to paste and that

essentially moves the data from one cell

to another but another way to do the

same thing is just to click on a cell

and then put your mouse on the very edge

of that cell any of the edges you'll see

that the mouse pointer becomes cross

with arrows coming out the ends at that

point you can click and hold the click

and then drag the contents of that cell

anywhere you want it to go and then

release the mouse button i'm going to

undo that with ctrl z i just want you to

see that you can do the same thing with

a range now that i've selected the range

i go to the very edge of that range

click and hold the click and then drag

the data where i want it to be release

the mouse button and the data has been

moved again ctrl z to put it back with

that we're now ready for the third

segment of excel for beginners complete

course in this segment we're going to

focus on formulas i would like to

calculate the total taxable income for

each employee and for that i need to

multiply each employee's wage by how

many hours they worked so i could do

that in my head or i could use the

calculator and then just put in the

information here but that is exactly one

of the things that excel does best excel

is a spreadsheet tool yes but it's also

a calculator it has built in pretty much

all of the calculator functions and

operators that you would need so here on

cell e2 i'm going to create my first

formula in excel whenever you're

creating a formula you need to start by

typing equals when i first started using

excel this was confusing to me until i

thought about algebra in my algebra 1

class that i took i remember learning

about variables and there would be

formulas like x equals and then it would

be y plus 10 divided by 3 or whatever

that's basically what we're doing here

so whenever i type equals before i type

it i think e2 and then i type equals so

cell e2 equals whatever comes next and

here i could just type in 13.9 and then

i could put in multiplied by or times

and in excel we use an asterisk for

multiply so 13.9 multiplied by 158 and

then i can type enter on the keyboard

and that is the taxable income for julia

so that's one way to do a formula in

excel you can type in the numbers you

can use plus signs minus signs asterisks

for multiply forward slash for divide

and then just tap enter and that formula

will be executed but there's another way

to do the same thing and it's a better

way again i need to start by clicking on

the cell and then typing equals but this

time instead of typing in the numbers

i'm going to type in the cell references

if you remember in excel we can describe

every cell in this spreadsheet we can

name it basically and remember we do

have this name box in the upper left

corner to help us with that so equals

and i'm going to name or describe this

cell so that would be c2 so i'll just

type in c2 asterisk for multiplied by d2

and i'll type in d2 now you'll notice as

i typed in those cell references excel

highlighted them in different colors so

i can see exactly what i'm about to do

now i'll tap enter on the keyboard and i

get the same result but this time it's a

little better the reason why it's better

is because this formula now is dynamic

if i change the contents of one of these

cells let's say it's a mistake julia

doesn't really have a wage of 13.9 it's

more like

14.25 i can tap enter and look at the

taxable income it's going to change so

this is a superior way to create a

formula in excel there is one other

method that i want you to be aware of i

could type equals and then instead of

typing the cell references c3 asterisk

d3 i could just click on the cell so c3

asterisk and then click on d3 tap enter

and my formula produces results now it

looks like the contents of cell e2 and

cell e3 are simple numbers it looks like

the contents of e2 is 2251.5

and the contents of e3

2480. but that's not actually true if i

double click on e2 you can see that the

contents of that cell is just a formula

what about e3 double-click the contents

of that cell is a formula so we see the

results of the formula but what actually

is in the cell is a formula and let's

talk about the type of formula that we

have in both cases these are relative

cell references what that means is when

i say c3 multiplied by d3 what excel is

interpreting that as being is just to

multiply whatever is in two cells to the

left multiply that by whatever is in one

cell to the left so watch what happens

now when i use the auto fill handle if

you remember the autofill handle lets me

extend a pattern if there is no pattern

what it does is it copies the contents

of a cell so i'm going to use the

autofill handle and i could click and

drag all the way down the spreadsheet

like i have in the past but i want you

to see a shortcut if you have a table

basically of data that's all together

instead of clicking and dragging on the

autofill handle it is faster just to

double click on the autofill handle and

it automatically extends down the page

so look at that because i use the

autofill handle i don't have to keep

creating formula after formula after

formula my formula was copied and

extended down the spreadsheet because i

used the autofill handle now how come

that worked the reason it worked is

because of what i said a minute ago

excel is interpreting these cell

references as being relative not

absolute so c3 excel just interprets

that as two cells to the left d3 one

cell to the left multiply the two

together you get a result so down here

when i copied that formula down using

the autofill handle it just adjusted

those cell references it's still two

cells to the left multiplied by one cell

to the left it's no longer bothering

with c3 and d3 it's moved on to c4 times

d4 what about down here double click c7

multiplied by d7 the reason this works

is because these are relative cell

references now what if i wanted to

calculate the amount of taxes to be paid

now i understand this isn't how it's

normally done but let's pretend and

let's say the tax rate is seven percent

and i'm going to enter this as a decimal

.07 and i tap enter let's use a formula

now to calculate the taxes to be paid

i'll click here on cell f2 and in my

head i'll say to myself cell f2 and then

i type equals the taxable income of

julia in this case multiplied by the tax

rate h1 i'll tap enter on the keyboard

and you can see the results this worked

beautifully but what happens when i use

the autofill handle and double-click to

extend that down the page it didn't work

why didn't it work let's look at these

formulas because i used relative cell

references here in my original formula

excel is looking one cell to the left

and multiplying it by two cells to the

right and one above so when i copied

that formula down the spreadsheet it

continued that pattern it's looking one

cell to the left two cells to the right

and one up and because these cells are

empty it's like multiplying by zero so

i'm going to fix this formula by

clicking on cell f2 and in excel anytime

i want to extend a formula work on a

formula what i like to do is click on a

cell and then instead of editing it here

by double clicking on it and making some

changes it's easier and better in most

cases to just select the cell and then

go up here to what's called the formula

bar this is a safer easier better place

to edit and work with your excel

formulas so right now these are relative

cell references i want to change the h1

reference to become an absolute cell

reference the way i do that is by

putting a dollar sign in front of the h

and a dollar sign in front of the one

and then tap enter on the keyboard and

what those dollar signs do is they force

excel whenever this formula is copied or

used to always refer exactly to column h

and to row one so that part of the

formula no matter how far down the

spreadsheet i copy it it will remain

fixed on cell h1 let's try it now i'll

double click on the autofill handle it

copies down the page let's see if it's

working it looks like it is if i double

click on any of these formulas you can

see they're all referring back to cell

h1 and that's where the tax rate is kept

so that's a very important concept to

understand the difference between

relative cell references like this one

and absolute cell references like this

one and cell references could be

partially relative and partially

absolute i could have left the dollar

sign off the h and just kept the dollar

sign on the one there are two more

things you need to know about using

formulas before we move on the first is

that from time to time you may make a

mistake in your formulas for example if

i tried dividing the contents of cell e2

by zero that's not possible it's an

error i tap enter on the keyboard and

i'll get an error message error messages

look something like this often with

hashtags or other symbols sometimes

you'll be warned that what you're

entering may be an error for example if

i do it this way i get a warning do i

want to accept their correction yes or

no

and so if you see things like this

that's okay this is excel trying to warn

me that i've created something that is

producing an error and in most cases

i'll go to the formula bar to fix that

error and finally it's important to know

that you can change the name of a cell

and also the name of a range so for

example i could click here on f2 and

drag all the way down the spreadsheet

and then i could go here to the name box

and i could click and i could change the

name that's in that box to be something

like taxes owed and i'm not going to put

in any spaces there i'll tap enter on

the keyboard and now that range has its

own special name if i forget what i

named that range i can always click here

on this drop down arrow and it tells me

taxes owed now why would that be

important you'll learn that later but

let me give you a quick preview in my

formula instead of referring just to a

cell like e3 i could refer to a named

cell or a named range so i could type in

taxes owed and then continue my formula

so taxes owed plus one tap enter and

it's added one to each of those cells

now that's not a great example like i

said you'll learn later later on in this

video and in other videos i've created

how you can use those named ranges in

some exciting ways in this next segment

of excel for beginners the complete

course we're going to focus on functions

and many users of excel constantly

confuse formulas and functions you'll

even find those mistakes in other videos

on youtube but there's a definite

difference between the term function and

formula in excel and the first function

that i want to teach you is sum let's

say i want to add up all of the hours

worked and all of the taxable income to

be paid to the employees i could just

browse down the sheet here to the bottom

of the data column d has the hours

worked and i'll click here in d72 and

type equals again thinking in my head

d72 equals and then i'll use my first

function

sum now as soon as i start typing a word

in that cell microsoft excel searches

its database of functions and tries to

find the function that i might be

looking for and there it is it's sum so

excel is suggesting to me that that

might be the one to use it tells me what

it does it adds all of the numbers in a

range of cells perfect that's exactly

what i want but it's possible that i

might want this some if or some ifs some

product there's all of these different

functions that deal with sums and some

of these are amazing you definitely need

to watch my other tutorials on some if

and some ifs sum product is also great

but in this case all i want to do is add

up all the numbers in a range of cells

so equals sum and when you use a

function in excel after typing the

function you put in a left parenthesis

as soon as i do that excel gives me a

pop-up with some suggestions it's trying

to help me and guide me in writing a

good formula using this sum function so

excel is expecting to have a number and

then a comma and another number so i

could put in a number and then another

number to be added together let's say

five comma six and then i should put in

my right parenthesis although it's not

necessary and then i could tap enter and

it adds those two together but in this

case i don't wanna add five and six i

want to add this entire column so how do

i describe this entire range of numbers

that i want to add up well if you

remember we can describe a range by

using the top left corner and then the

colon and then the lower right corner in

this case it's all just in one column so

d2 and then i'll go back down here so d2

and then the colon so through i want to

sum d2 through

d71 i should put in a right parenthesis

but i don't have to i'll tap enter on

the keyboard there's the grand total

hours worked by the employees let's look

at another way to do the same thing

instead of typing the cell references i

could just say this cell and type equals

sum left parenthesis and then i could

have clicked and dragged to select the

entire range all of the numbers that i

want to add up and then back down in my

formula i could have tapped enter and

gotten the same results now because the

sum function is so common in microsoft

excel they've added what's called the

autosum function so instead of even

typing that formula at all i can just

select the cell beneath the data that i

want to sum up and then i simply go here

to the home tab home ribbon in the

editing group this symbol stands for

autosum so i'll click that and excel

automatically figured out what i want to

add up d2 through d71 tap enter and it's

done so in many cases autosum is the

fastest best way to do that at this

point i probably should type something

here like totals colon and maybe i could

click on it and go to the home tab font

group and make it bold maybe i go to the

alignment group and change it to be

aligned right and now i could do the

same formula autosum for taxable income

or instead of redoing the formula i'll

just click on d72 and i'll use the

autofill handle to scoot that over and

the formula adjusted because these are

relative cell references they don't have

dollar signs when i auto filled it over

to the right those cell references

adjusted and it worked beautifully in

addition to the sum function there are

some other functions that are pretty

common and important for example we can

calculate the average let's do that with

cell d73 selected i'll type equals this

cell equals average and you can see that

i do get similar helps as i did with sum

excel explains what this is it also can

tell me about these other average

functions but i'll stick with the

generic average now that i've put in a

function i need to put the left

parenthesis and i'll put in my range d2

through

d71 tap enter on the keyboard there is

the average number of hours worked by

this group of employees in the last

month i can autofill that to the right

and there we have the average taxable

income now if you remember we set up a

named range for the data here in taxes

to be paid let's look at how named

ranges work with formulas and functions

so here on f72 i'm going to say to

myself f72 equals the sum of and i'll

put in a left parenthesis

it's looking for a number i'm just going

to type in taxes owed and then i'll put

in my right parenthesis and notice excel

recognizes taxes owed oh you want to add

up all of the taxes owed and excel knows

what that means because i named that

range so now when i tap enter it just

automatically sums up everything in the

taxes owed range i can do the same thing

with average so f72 equals average left

parenthesis taxes owed tap enter and it

figures it out let's look at three other

important and common functions in

microsoft excel we're going to calculate

the highest hours worked the lowest and

then also we're going to count the

number of employees so here i'll type

highest lowest and number of employees

okay how am i going to figure this out

if i want to know what the highest

amount of hours worked was out of all of

these employees and i also want to know

the highest taxable income of all of

these employees there's got to be a good

way to do that in excel that's easier

than just scanning and looking for the

highest number fortunately there is

there's a function called max so in my

head i'll think d74 equals max left

parenthesis and then i just need to

describe the range of cells to look in i

want to find the highest number the max

number in this range d2 through d71 i

should put in my right parenthesis but i

don't have to tap enter and the highest

number of hours worked in this month by

this group of employees is 208. i can

autofill that over to the right and i'll

just go to both columns now to the right

and now we know the highest taxable

income amount and the highest amount of

taxes due let's do the same with lowest

i think to myself cell d75 equals

instead of max i'm going to put min and

you can see excel is giving me hints

giving me suggestions min returns the

smallest number in a set of values it

ignores logical values and text so

equals min left parenthesis d2 through

d71 tap enter and there's the lowest

amount of hours worked i use the

autofill handle to extend it over to

these other columns of data our last

common function that i'm going to show

at this point in the video is count i

want to count the total number of

employees now you might be saying don't

we already have that yes we do it's

right here but in some cases you don't

have that or you want to count different

parts of a spreadsheet so it's important

to know this in my head i think d76 is

equal to count and there are different

types of counts count a countif countifs

definitely watch my other videos on

those different functions but for now

just count and you can see what it does

it counts the number of cells in a range

that contain numbers so left parenthesis

once again

d2 through

d71 right parenthesis tap enter and it

counts up 70 different cells in that

range that have numbers if i wanted to i

could autofill this over but it's just

going to give me 70 again so those are

the five or six most commonly used

functions in microsoft excel if you want

to learn more functions in excel check

out my other videos i have lots and lots

of different functions that i show in

those other tutorials and there's more

to come in the future in this next

segment we're going to focus on how to

modify the formatting in the spreadsheet

including how to format numbers and text

we'll also look at formatting cells rows

and columns and a couple of tricks that

will help you do your formatting more

effectively we're going to format some

of these numbers notice that it's hard

to tell the difference between money

dollars in this case taxable income and

just regular hours worked they all just

look like numbers so let's change the

formatting so it's obvious what we're

dealing with to do this i'm going to

click on column c it's going to select

the entire column and then here on the

home tab home ribbon in the number group

i can change the formatting right now

it's just general formatting that's the

default but if i click on this arrow i

can change this to be currency now you

can clearly tell the difference between

the data in column c and the data in

column d now there are times when

instead of using currency

as the number format you might want to

use accounting let's look at the

difference when i click on accounting i

still get the dollar sign i still get

commas and decimals for the cents but

the dollar sign is separated from the

numbers i think it just makes it a

little easier to see the numbers without

getting confused by the dollar sign so

those are two different ways to indicate

that we're dealing with money in these

columns same with this one here i'll

switch that also to accounting now there

are other number formats that you should

look into including dates and times

percentages fractions and more watch my

other tutorials to learn more about the

different number formats what about text

formats i could just leave my text as

general format or i could go down here

and select text and in excel by default

when you have text entered in a cell it

will align to the left of the cell so

all of this text is aligned to the left

if excel interprets what you type as

numbers generally speaking it will align

to the right in the cell so you can see

all of these numbers are aligned to the

right these names are aligned to the

left but you can change some of that if

you want using the alignment options and

also by changing the number or text

formats there are also some more formats

you can click here to see what those are

you can set up some custom formats

there's different time and date formats

that are worth checking out i'm going to

cancel and let's look at some other ways

that we can format our data and

especially let's look at how to format

columns and rows in excel let's say i

want to format this entire first row so

that everything in that row is bolded i

think that's a good idea in this case

because i'd really like to set apart

that row so that people can tell it's

not really part of the actual data these

are column titles or labels so having

selected row one i'm gonna click on the

home tab home ribbon in the font group

i'll just click on this bold symbol now

that entire row is bolded and if i were

to go to let's say cell i1 and if i type

something there you can see that it

comes in in bold formatted just like the

rest of this row other ways that i could

format rows include changing the

alignment of the cells in a row so here

on the home tab home ribbon in the

alignment group i can click this button

to center each cell in row 1 within its

column if i wanted to i could change the

background color for the row and there

are many other format options for rows

and columns in excel if you'd like to

learn more about those please watch my

many tutorials that cover formatting in

excel next i want to show you a shortcut

you can use when you're trying to format

columns rows cells etc in microsoft

excel let's say you get a cell formatted

just the way you want and then later you

decide you would like to also format

other cells in exactly the same way one

trick that you can try is select the

cell that has the formatting you want

and then go to the home tab home ribbon

in the clipboard group you can click

this format painter with that clicked

whatever you click on next will take on

the same exact formatting as the active

cell so i'm going to click here on james

james is now centered and bolded now if

i click on freddy it doesn't work

because the format painter now is

deselected if you want to be able to

click multiple times and apply the same

formatting all you have to do is again

select the cell that has the formatting

you want and then double click on the

format painter and now you can click

click click just continue to click and

add the new formatting to the cells you

can also apply the formatting to a whole

range i'm going to undo all of that with

ctrl z one other formatting trick that

you might want to know is you can use

what's called auto format but first you

need to add it here to the quick access

toolbar to do this just click up here on

the customize quick access toolbar

button and go down to more commands and

then here you can switch from popular

commands to all commands and then just

browse down until you find auto format

there it is so with it selected i can

click add and now it will be added to

the quick access toolbar i click ok and

here it is to use the auto format tool

all i need to do is click somewhere in

my data and then go up here and click

auto format i can browse through the

options for the different formats if i

find one that i like i can just click on

it click ok and that format is added to

this spreadsheet i'm going to undo that

so those are some of the most important

and most common formatting options that

we have in microsoft excel in this next

segment of excel for beginners the

complete course we're going to look at

how to create some basic charts in excel

there are a few different ways to create

charts let's look at one of the very

easiest and to do this the first thing i

want to do is make sure i can see the

important data so i'm going to go down

here to the zoom slider and i'll click

and slide that to the left and then i'm

going to click and drag to highlight the

data that's important to me in this case

i don't really need the tax rate i also

don't need the totals and the average

etc here below just all of that data

there hold the alt key and tap f1 and

look what excel did it did its very best

to create a chart that makes some sense

because i selected all of the data excel

is trying to figure out a way to show

all of it in one chart and it's kind of

hard to do this but you can see across

the bottom here i have a list of names

employee ids and then the bars show

hourly wage hours worked taxable income

taxes to be paid and this is all color

coordinated if i want to i can click on

the chart and then go up here to chart

design and i could change the chart type

so instead of this stacked column chart

type i could switch to a clustered

column click ok let's see how that looks

and if i don't like that i can go back

to chart design change chart type and we

could try a pie chart or some other

chart because i selected all of the data

this is going to be very difficult to

chart all of it all at once i really

should chart only a very specific amount

of data but i wanted you to see how to

quickly add a chart based on the data in

your sheet once you have your chart you

can go up here to chart design and you

can add some chart elements for example

i could add a chart title i could put it

above the chart if i want to and then i

could just triple click on the text

there to select it and i'll just title

this summary chart i could also add

other chart elements like axis titles a

horizontal axis title and i could also

add a primary vertical axis title like i

said this chart is too much it's too

much information in one place but in

many cases you'll be creating a simple

spreadsheet and holding alt and tapping

f1 will produce a chart that's very

useful to you if you'd like to learn

more about charts in microsoft excel

please watch my many other videos that

focus specifically on creating charts in

excel in this next and final segment of

microsoft for beginners the complete

course i will show you the print options

and the publishing options that you have

in microsoft excel so let's say with the

data that's in this spreadsheet and with

this very confusing chart that i've made

let's say this spreadsheet is ready to

be printed to be handed out and

published for its intended audience and

the first question that i need to ask

myself is will the data in this

spreadsheet actually fit on a printed

page it may not to help with that you

can go to the file tab and go down to

print and that will give you a print

preview here at the right and it looks

like my data fits pretty well on this

page if i scroll down i can see that

page 2 is just more rows of data page 3

is part of my summary chart so that

actually worked out pretty well but if

you remember i had entered the word

hello here in the upper right i'm gonna

go back into excel add that back in and

now i'll go to file print and now take a

look at the results i have page one but

if i go down there's page two and then

there's page three with hello sitting

there and with my chart so if i want to

make sure that this column also is

included with the rest of the data

there's some changes i need to make i

need to prepare this spreadsheet to be

printed one way i could fix this is to

go to the page layout tab and in the

page setup group i could click on

orientation and switch the orientation

of the spreadsheet from portrait to

landscape in many cases that will solve

your printing problems in microsoft

excel so now when i go to file print

look all of my data fits horizontally on

one sheet except for my chart if i go

down to page two that's what it looks

like we've got page three and page 4 is

part of a chart so i still have the

problem of the chart but at least the

data here is fitting horizontally on a

page without having to go to another

horizontal page to the right another

thing you can try when working with

printing options in microsoft excel is

you can go to the view tab and switch

from normal workbook view to page break

preview when you click that it will show

you where your pages are so this is page

one page two page three and it gives you

these blue dashed lines that you can

click on and drag so i want to try to

move the page break to be here to the

far right side beyond my crazy chart

let's switch back now to the normal view

and i'll do file print and let's take a

look i used to have three pages or four

pages worth of data to print now look i

only have two pages this is page one and

the chart fits very nicely with the data

when i click this button to go to page

two there's page two so this is the best

print preview that i've seen so far at

this point i just need to decide how

many copies i want to print let's say

two i need to make sure i've selected

the correct printer i don't actually

have a printer set up so it's going to

print to pdf but generally you would

click here and select your actual

printer from the list there are some

settings to think about i could print

only the active sheets so sheets that

i've been working on that are active or

you could print the entire workbook the

other option is to print a selection so

if i go back to the spreadsheet i could

click and drag i could leave out

employee id maybe and just get this

selection and then go to file print and

switch from print active sheets to print

selection and that's what it will look

like now i think i'll go back to print

active sheets if you want you can choose

to print only page one or if i had a

longer spreadsheet i could print pages

five through seven whatever you want to

do there you can adjust the orientation

if you want the page size etc there is a

very interesting option here at the

bottom custom scaling if you want you

can click on this and you could choose

fit sheet on one page it will shrink the

print out so it fits on one page so i

click that and now the entire

spreadsheet fits only on one page so

that is an option just keep in mind if

you have a lot of data it's going to be

tiny it'll be hard to read okay at this

point i'm going to click print now

remember i have no printer so it's going

to be printing to a pdf and this is a

good option whether you have a printer

or not if you want to turn what you see

here into a pdf that you could email to

people you could publish it to the web

you could include it as an attachment in

an email to someone this is a really

good option so i'll click print and it

asks me where do i want to save this pdf

i'll save it to my downloads folder and

i'll give it a file name and click save

now if i look in my downloads folder

here it is i can open it up and i have

this beautiful pdf that's tough to read

so those are the basics of printing

documents in microsoft excel and you can

use the save as pdf to publish your

document to the web if you'd like other

ways you can publish your document

include clicking this share button in

the upper right i might want to upload

my document to onedrive once i've done

that a copy of the document will be

online and i can easily share it and

send it and publish it if you want to

learn about onedrive please watch one of

my tutorials about microsoft onedrive we

also have the ability to attach this

workbook as an excel workbook or as a

pdf so those are some good share options

and then also here on the file tab if i

choose save as that's another way to

save to onedrive but also look what i

can do here where it says excel workbook

i can click this arrow to change the

kind of file that this is i've been

working in excel and this is an excel

workbook but i could save it as another

type of excel workbook for example a

macro enabled workbook you'll learn more

about that as you watch more of my

videos you could also save your workbook

as an html web page which is an

interesting option you could save it as

an excel template you could save it as a

csv file which is a powerful option and

once again we do have an option to save

it as a pdf so it's important to be

aware of this drop down and the ability

that you have to save your finished

workbook in other formats

so congratulations at this point you

have completed the excel for beginners

complete course at this point you have

everything that you need to know to use

excel effectively yes there's more that

you could learn but you have all of the

basics that you need to be successful if

you want a copy of this workbook that

i've created look in the description

below the video if you want you could

download it and just go to sheet number

two and then follow along with my video

watching it again and doing each step

one at a time and then you could compare

it to what i did on sheet 1. if you're

ready to take your next step in your

excel journey i recommend that you next

watch my beginner's guide to microsoft

excel you may have already seen that but

if you haven't it's important to watch

that video because it is a nice short

overview of some of the basics that

you've seen here but also with some

additional new content in the future i

also will create a video called excel

for intermediate users the full course

so watch for that video to extend your

learning even further and if you're

interested in microsoft word and

powerpoint i will be adding full course

videos for both of those great tools as

well so i look forward to you joining me

on this journey and then also i want you

to know that i have dozens and dozens of

other excel videos that are deep dives

into each of the important aspects of

microsoft excel so you can learn all

about charts you can learn all about the

format painter you can learn about many

of the functions in microsoft excel so

please also watch my individual excel

video tutorials thanks for watching i

hope you found this tutorial to be

helpful if you did please like follow

and subscribe and when you do click the

bell and you'll be notified when i post

another video if you'd like to support

my channel consider clicking the thanks

button below the video or you can

support me through my patreon account or

by buying channel merch and you'll see

more information about those options in

the description below the video