All language subtitles for 5Topic 2-2 Excel Data Types Week 1 Getting to Know Excel & Ente-1

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