Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
so in our last video
we just saw examples of
our three different data categories
that we've been talking about in Excel
and as a reminder
these categories are text
numbers and formulas
within each of these categories
there are several different data types
and this is especially the case
when we're talking about numbers
as you know
numbers can represent a whole bunch of different things
numbers may be currency
like US dollars
numbers might be times and dates
numbers might be percentages or fractions
and so there's a possibility that
even when we're entering
in data in one of our categories
we may have confusion
about what those numbers represent
in this video
what we're gonna be talking about
is we'll preview a few of these different data types
and we'll learn how to understand
how Excel is interpreting these data types
so that we know
whatever data we're putting into a spreadsheet
we're thinking about it the same way Excel is
in vice versa
this is a very important topic
we wanna make sure that Excel is
thinking about our data the same way that we are
and I'll give you a little example here
to show how important this is
so if we just go into a blank Excel sheet and
let's say we're interested in entering in a fraction
and so we type in 1/2
we can already start thinking about
the different ways Excel may view this data
this number
and what it might do with it
and so we're thinking of this as a fraction 1/2
is Excel thinking of it as a fraction
or is it maybe thinking of it as a date
it might be January 2nd
it might be February 1st
depending on where we live and how we write our dates
we can even imagine that
because we've been working with formulas and equations
Excel might actually calculate this value out
and give us a value of 1/2
point five as a decimal right
so there are lots of possibilities for confusion here
so let's just see what Excel does
when we enter in this value of 1/2
now we see the whole point of this video
when we typed in a fraction
Excel immediately interpreted this
in some kind of date format
what this is
is this is an issue of self formatting
we can resolve this issue through self formatting
and by using self formatting
we can help avoid these kinds of miscommunications
you can see that this can be a very important source of
errors in Excel
if we think data represent something else
than how Excel is interpreting it
so in this video
we're gonna learn how to
identify and avoid this problem
and this is going to save us a lot of trouble in
the long run
so as we explore cell formatting
we're going to start with a
very simple spreadsheet here
and we'll start with a review of
our three different data
data categories
and see how they work with data we've been working with
and then we'll move on to the situations
where we're more likely to have errors
and we'll find out how to fix and avoid those errors
so we've been dealing with temperature data
you saw that
that's going to be one of the projects we're working on
and one of the text categories that we can talk about
is we have months associated with temperature data
and so we'll just start out with the month of January
here I'll make sure I spell it properly
and then I'll give you another example
where text doesn't have to be a single word
it might be notes
it might be a sentence
we might have addresses
for example
or just some kind of note
I'm gonna say
my name is Kevin
and what we see is this is formatted as text perfect
nothing strange going on here
we can also add in numbers
we've been dealing with temperatures
and you may recall that the high daily temperature
on average for the month of January where I live
is about 51 degrees Fahrenheit
and the low temperature is about 30 degrees Fahrenheit
so I can enter those numbers in as well
one thing you'll notice immediately
and this is part of how Excel is doing itself
formatting is it's telling us that it's
seeing different things in these data
it's justifying text data
to the left you see
the January is on the left hand side of that cell
and it's justifying or aligning miracle data
number data to the right
so Excel is sending us signals here
about how it's interpreting these values
this is important for us to catch up
to catch on to
because this is how we communicate with Excel
and Excel communicates with us right now
everything is great
it sees our text as text
it sees our numbers as numbers
remember that we can also do formulas
we start formulas with an equipped with an equal sign
and maybe we just wanna calculate the difference again
between this high and low temperature in January
51 - 30 so we have a formula here
but when we enter that in
what we see is we get the result
we get that numerical value justified to the right
and so Excel is telling us hey
here's the answer
it's a number and so
I'm putting that on the right hand side of the cell
we can also take advantage of cell referencing
and we can do the same thing
where we subtract the value in C3
from the value in value in B3
and we get the same result
these are numbers
the answer is a number
the underlying data is a formula however
and we can always look up into this formula bar
to see what that formula is
but if the result
of the formula of the equation is a number
will have those data presented to us
as a number
all of this is a feature of Excels
General cell formatting
so let's quickly explore what the cell format
of these cells is
we'll click on one of these cells
we'll click on January
we will right click
and now we'll go down to format cells
this is going to give us a menu
telling us how that cell is formatted
right now it's formatted in the general cell format
no specific number format
and as you can see
it handles text just fine
we can also look in this number here
also the general format and so
we just Learned that the general format handles numbers
and it handles text
that's great
what about with formulas
let's click on this cell again
right click
format cells again
we see this is a general format cell
so the general format in Excel
is the default cell format
and it can handle text
it can handle numbers
and it can handle formulas like we've been dealing with
this is great
you can see we have a bunch of other cell formats here
and this is where self formatting can become
really important
and where it can lead to
and help us avoid errors in our spreadsheets
so let's explore in a little more detail
this issue of self formatting
so as we saw in the introduction of this video
it's dates where things can get more complicated
so let's work with some dates here
and I'll start just by entering in
a typical date format of January 1st, 2021
and we'll see that Excel recognizes this is a date
it pushes it to the right as if it's a number
and it does a little bit of formatting there
it actually changed how the date looked
I entered in 1 slash 1
and it changed to 0 1 slash 0 1
and that's because Excel is doing some formatting here
so we'll go back to our Format Cells menu
hey and Excel has identified that I entered in a date
and it used this default date format
that I have set on Excel
but you see
we have numerous other date formats we can choose
we can even have Excel calculate the
day of the week for us
which is really pretty cool
we change that
and we find out
or were reminded
that January 1st this year was a Friday
that's really cool
but the critical thing is
now that this cell is formatted as a date
it's going to stay as a date
that's really critical to understand here
so if I delete this
the date is gone
but the cell is still formatted as a date
so if I enter in another number
say the No. 23
Excel converts that to a date
this is a huge and important source of errors in Excel
and it's because of this cell formatting
once Excel changes the cell's format to one
kind like date
it's going to stay that way
unless we change it back
so if we actually want the cell to be a number
we need to make sure it's in the general
or number format
we'll go with the general format
the default
now I can enter in 23
and it will stay as a No. 23
but since we're working here with dates
I'm gonna convert that back to the date
and there we have it
I'll go back to having the day of the week in there
right click down to format cells
and then we can adjust how we want things to look
beautiful and remember
even when we input the fraction 1/2
Excel interpret that as a date
January 2nd
it looks like it's saying here
so what do we do
if we actually want a fraction to be a fraction
well that's another job for self formatting
and so let's go here to our other
area of the Excel spreadsheet
where we're gonna be dealing with fractions
here we will preemptively change the cell format
to make sure
that it's going to reflect what we want it to
in this case
it's going to reflect fractions
we'll go down to format cells
we will choose that we want it to be a fraction
we'll be fine with one digit
we don't need two digits
or three digits for this fraction
because we're just going to do 1/2
beautiful keeps it as a fraction
doesn't calculate it as a proportion
doesn't convert it into a date
because we said we wanted that to be a fraction now
if we do want Excel to calculate that as a proportion
right 1/2 being point five
of course we can just tell Excel to do it as a formula
and here we'll see
it calculates that is 0.5
because it's always going to
output the results of formulas as numerical values
as decimals
as opposed to fractions
so hopefully
this is starting to make sense
that there are certain data types
where Excel is going to say aha
I'm going to assume this is a date
and put it into a date
we can fix and change that using self formatting
and then there are other times
like for fractions
we want to change self format
self formatting
to make sure it reflects
the kind of data we're inputting
a final example of this is with currency
maybe we want this number to not just reflect a number
maybe we want that number to reflect
a value of currency
and so again
this is a job for cell formatting
right click
format cells
this time we'll
we'll select currency
this is very common in a lot of spreadsheet
applications
where we're dealing with dollars and cents
in this case
we'll deal with US dollars
but you can see we have a whole bunch of different
currencies to choose from
we'll keep two decimal places
because that makes sense for many currencies
where we have cents
in dollars or other smaller fractions of currencies
and once we convert that
boom now it looks like a currency again
this is really fantastic
because it makes explicitly clear
what the data are that we're working with
it's not just a number
this is currency
so there's some critical information
for you on cell formatting
maybe the most important thing to remember is that
for some types of data
Excel is going to convert the cell formatting for you
this happens most frequently with dates
and if you don't want that to happen
then the solution is to go into your right click menu
choose format cells
and change that cell formatting to be
how you want it to be
understanding this is really critical to successfully
having a spreadsheet that doesn't have errors in it
again this is a very common source of errors
and I'm hopeful that this information will help you
avoid some of the frustrations
and errors that come along with learning Excel
and now you'll have the
opportunity to practice some of these skills
dealing with cell format
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.