Excel for Beginners - The Complete Course
Summary
TLDRThis comprehensive tutorial offers beginners a step-by-step guide to mastering Microsoft Excel. Starting with the basics, such as creating workbooks, understanding spreadsheet anatomy, and navigating the Excel layout, the course progresses into entering and managing cell values, utilizing the powerful fill handle feature, and correcting or clearing cell contents. It delves into formulas and functions, teaching how to create dynamic formulas for calculations and use built-in functions like SUM and AVERAGE. The importance of cell references, both relative and absolute, is emphasized. The video also covers formatting techniques for cells, rows, and columns, and introduces the Format Painter and Auto Format tools for consistent styling. Chart creation is simplified with quick charting options and customization through the Chart Design feature. Lastly, the course explores print options, including page layout adjustments and the use of print preview, as well as various publishing options like saving as PDF, sharing, and different file format exports. The instructor encourages further learning with additional tutorials for intermediate users and other Office tools.
Takeaways
- ๐ Start with Basics: Understanding the anatomy of a spreadsheet, including columns, rows, cells, and ranges, is crucial for effective Excel use.
- ๐ Workbooks and Sheets: A workbook can contain multiple sheets, each called a worksheet, and is the primary document in Excel.
- ๐ข Entering Data: Learn to enter, edit, copy, and clear cell contents using keyboard shortcuts for efficiency.
- ๐ง Auto Fill Handle: Utilize the auto fill handle for quick data entry by extending patterns or copying cell values down a column.
- ๐ Text Entry: Know the difference between clicking and double-clicking a cell to edit text without overwriting existing content.
- ๐ข Formulas and Functions: Master the use of formulas (starting with '=') and functions (like SUM, AVERAGE, MAX, MIN, COUNT) for calculations.
- ๐ Relative vs. Absolute References: Learn when to use relative (e.g., A1), absolute (e.g., $A$1), and mixed references in formulas.
- ๐ Formatting: Customize the appearance of cells, rows, and columns using number formats, text alignment, and font styles.
- ๐ Chart Creation: Easily generate charts from selected data using the 'Alt + F1' shortcut or the chart tools in Excel.
- ๐จ Printing and Publishing: Explore various print options, including page layout adjustments, and the ability to publish as PDF or other formats.
- ๐ง Advanced Features: Familiarize yourself with additional features like autocorrect, find and replace, and named ranges for more efficient data management.
Q & A
What is the first step in using Excel effectively as a beginner?
-The first step is to create a new workbook and familiarize yourself with the basic anatomy of a spreadsheet, including columns, rows, cells, and the layout in Microsoft Excel.
How do you create a new blank workbook in Excel?
-When you start up Excel, you can click on 'Blank Workbook' to open up a new, completely blank workbook.
What is the term used to describe the intersection of a column and a row in a spreadsheet?
-The intersection of a column and a row is called a cell.
How can you describe a range of cells in Excel?
-A range of cells is described by using the column letters and the row numbers, starting from the upper left corner cell and ending with the lower right corner cell, separated by a colon (e.g., D4:J14).
What is the term for the collection of sheets in an Excel workbook?
-The collection of all sheets in an Excel workbook is called a workbook itself.
How can you add more worksheets to your Excel workbook?
-You can add more worksheets by clicking the plus sign at the bottom of the sheet tabs.
What is the purpose of the Quick Access Toolbar in Excel?
-The Quick Access Toolbar provides quick access to some of the most commonly used features in Microsoft Excel, allowing for more efficient workflow.
How do you enter data into a cell in Excel?
-To enter data into a cell, first select the cell, then type the data and press Enter or Return on the keyboard to confirm the entry.
What is the Fill Handle or Auto Fill Handle used for in Excel?
-The Fill Handle is used to identify patterns in data and extend those patterns, avoiding repetitive data entry tasks.
How can you correct a misspelled word in a cell without erasing the entire content?
-Instead of clicking on the cell, double-click inside the cell to get the cursor inside, then use the arrow keys to move the cursor to the misspelled word and make the correction.
What is the difference between clicking on a cell and clicking in a cell in Excel?
-Clicking on a cell selects the entire cell, and typing will overwrite the existing content. Clicking in a cell allows you to edit the content without overwriting it.
How can you clear the contents of a cell in Excel?
-You can clear the contents of a cell by right-clicking on the cell and choosing 'Clear Contents', or by using the 'Clear All' button in the Editing group on the Home tab of the Ribbon.
Outlines
๐ Introduction to Excel Basics
This paragraph introduces the Excel for Beginners course, assuring learners of any experience level that they will gain a solid foundation in using Excel. It covers the creation of workbooks, saving them, and understanding the spreadsheet's anatomy, including columns, rows, cells, and ranges. The layout of Microsoft Excel is also introduced, including the ribbon, tabs, and navigational elements like the name box and formula bar.
๐ Entering and Editing Cell Values
The second paragraph focuses on how to enter data into cells, use the fill handle for auto-filling patterns, and edit cell contents. It discusses the difference between clicking on a cell (which selects the entire cell) and clicking in a cell (which allows you to edit the cell's content without overwriting). The paragraph also covers navigating within a spreadsheet using the enter, shift+enter, tab, and shift+tab keys, and techniques for copying, clearing, and inserting new rows and columns.
๐ Understanding Cell References and AutoFill
This section delves into the importance of cell references in Excel and how they can be used to create formulas. It explains the concept of relative cell references and how they change when using the AutoFill handle. The paragraph also discusses absolute cell references, which remain constant regardless of where the formula is copied, and partially absolute references. Error handling in formulas and the use of the formula bar for editing are also covered.
๐งฎ Using Formulas and Functions
The fourth paragraph introduces formulas in Excel, starting with the equals sign and basic arithmetic operations. It differentiates between static numbers and dynamic formulas that update with cell changes. The paragraph also explains how to use cell references to create formulas and the importance of the AutoFill handle for extending formulas. Functions like SUM and AVERAGE are introduced, along with their application in calculating totals and averages for a range of cells.
๐ Charting Data in Excel
This section covers the basics of creating charts in Excel. It demonstrates how to use the Alt + F1 shortcut to generate a chart from selected data and how to modify the chart type for better representation. The paragraph also discusses adding chart elements like titles and axis labels, and the importance of selecting the right amount of data for effective charting.
๐จ๏ธ Printing and Saving Workbooks
The final paragraph discusses the options available for printing Excel workbooks, including page layout adjustments, print preview, and the use of page breaks. It also covers saving the workbook in various formats like PDF, CSV, or as an Excel template. Additionally, the paragraph touches on sharing and publishing options, such as uploading to OneDrive or saving as an HTML web page.
Mindmap
Keywords
๐กExcel
๐กWorkbook
๐กCell
๐กFormula
๐กFunction
๐กRange
๐กAutoFill Handle
๐กAbsolute and Relative Cell References
๐กChart
๐กFormatting
๐กPrinting and Publishing
Highlights
Excel for Beginners course promises to teach everything needed to start using Excel effectively, regardless of prior experience.
Introduction to creating workbooks, saving them, and understanding the basic anatomy of a spreadsheet.
Explanation of how to use the fill handle or auto fill handle in Excel to extend patterns and avoid repetitive tasks.
Demonstration of entering cell values, including the difference between clicking on a cell and clicking in a cell for editing.
Overview of how to clear and copy cell contents, and the use of the formula bar for entering formulas.
Introduction to cell references, including relative and absolute references, and their impact on formulas.
Explanation of how to use the SUM function to add up a range of cells and the convenience of the AutoSum button.
Discussion on other common functions such as AVERAGE, MAX, MIN, and COUNT, and how they can be used in Excel.
Guidance on modifying the formatting of cells, rows, and columns, including number and text formats.
Tutorial on using the Format Painter tool and Auto Format feature for consistent and efficient formatting.
Basic chart creation by highlighting data and using theๅฟซๆท้ฎ (shortcut key) Alt + F1.
Explanation of print options, including page layout adjustments and print preview for fitting data onto pages.
Details on saving the workbook in various formats such as PDF, Excel Template, or CSV, and the option to print to PDF.
Introduction to sharing and publishing options, including uploading to OneDrive and attaching the workbook as an email attachment.
Course completion and next steps, including further learning with the Beginner's Guide to Microsoft Excel and upcoming intermediate courses.
Availability of additional Excel tutorial videos covering in-depth topics like charts, format painter, and various functions.
Encouragement for viewers to like, follow, subscribe, and turn on notifications for new video releases.
Options for supporting the channel through Patreon, purchasing merchandise, or using the thanks button below the video.
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
Browse More Related Video
5.0 / 5 (0 votes)