Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:01
okay, in this lesson, I'm going to try to explain to you what data types exist in excel
00:10
and how you can create or use custom data types and create more.
00:16
So let's not beat around the bush and switch directly to excel.
00:21
And we have a simple table here, and some of these ah numbers cells have been already performative. And I want you to see
00:32
what it means to use different data types of formats. So let's look at first this still here we have some calculation off fuels
00:43
used spent Sorry, purchased and and how much money did you spend?
00:49
And, uh
00:51
so here we have
00:54
ah,
00:57
number amount of fuel in leaders purchased for somebody in his car. And you see, here we have 29 point into a tree.
01:06
Now, when we right click on this cell
01:07
and go down and present form, it sells
01:14
the first tab in this newly opened window or box is
01:19
what kind of number do we have in this cell so it can be general,
01:23
and in this case, it will just display what you have typed. So they have no specific number for Mitt.
01:30
But because this is the amount of fuel. So it's, um, number. We wanted to be a number, so we can use 1000 separator.
01:40
Of course, if it's ah if its purchase of AH fuel for car, it makes no sense to put 1000 separator because you're probably you don't have a car in which you can buy more than
01:53
1000 liters arm or in your tank, so it's not going to happen. But let's leave it like this and the You have different types off
02:05
off choices for negative numbers. So if you can have it, for example, for accounting purposes, if it's a negative number, you're not going to purchase a negative number off leaders for your car off fuel. But if it's some kind of accounting table,
02:22
it's sometimes it's usual to not put minus in front of number, but make it
02:28
appear red or you can put it in in brackets or parenthesis is, or you could put it in
02:36
parentheses and also make it red. So there are
02:39
different standards,
02:42
and you can choose how the negative numbers will be displayed.
02:46
Uh, but
02:47
the most important thing is how many decimal places you want. So if it's some kind of financial calculation. It is normal to have two decimal places
02:57
now. In some countries, for example, you don't use two decimal places. In most of calculations, you go with zero,
03:05
and then them Excel is using standard rounding conventions. So if it's a below 0.5, then it's going to be rounded to lower number. If it's Ah, Bob. So, for example, is 53 is going to be around 2 30 but only but is displayed. So this is important.
03:22
The number in the cell remains the same, so
03:24
let's make it back to
03:27
29.3
03:29
We see here 29
03:30
and let's reformat the cell to show exactly two decimal places. So this is about the number.
03:38
So then we have a price. And imagine that you're in Europe like I am
03:44
If you're not. Of course, if you're in Europe, you don't have to imagine. And then the price is 31 euro
03:51
for the 29.23 liters off fuel, which is possible in some countries,
03:58
and, uh, then you then you former to this cell,
04:03
and this health has has been preform. It'd for a currency. So it's um,
04:10
it's, Ah, it's a 31 the content of cells 31. But what you see is 31.0 and then space and then euro sign.
04:20
And you can also choose how many decimal places you want there to be.
04:26
And you can choose the currency, of course, And you can. You can have yours for all the countries in Europe that use euros, or it can be in some
04:38
other currency. So if you select, for example, pounds in United Kingdom, it will say £31. The fact is that the number still is 31 so you have just chosen
04:50
how it's going to be displayed.
04:53
So because we're doing euros,
04:57
let's, uh, let's go back to euros
05:00
with just duty and they will not look for these things. Okay,
05:04
so the currencies euro and this box, this cell should be formally dis text. So let's go to form. It sells.
05:13
It's general, but we wanted to be text, so we don't want it to be
05:18
general format. We just wanted to be a text, and then we say OK,
05:26
and, uh and that's it.
05:30
And then we have the exchange rate and degenerate is, for example, in the other feet. So we're goingto say here that this is
05:40
equal
05:41
two
05:44
this cell here
05:46
and we have this and we have formula here that says
05:51
C two times he too. So this is dollar value. So the
05:57
the
05:58
Shane Gray between euro and or is this an Sorry? This should be divided. So I made this minute was preparing. So this makes sense. So 31 you're always 33.99
06:13
and then
06:15
that should be formatted as
06:17
dollars
06:20
because now we're looking for this.
06:28
So let's just leave it like this.
06:31
And we have it displayed like it is properly displayed when you have in the dollar value.
06:38
So this is it. And now we hear can enter date. So let's say this is this was purchased
06:43
on seventh of Remember.
06:46
So it's going to be 11 slash seven slash 2019
06:51
and we present her and we get this. Now imagine that we're not in United States, that we nurse another country and the numbers are the dates are displayed differently.
07:04
So we go here and we have it
07:08
said his date and, for example, we want to have the whole year and just leave it like it is. So it's going to be 11 7 2019
07:16
However, if we go again to form a ting cells
07:21
and then click on some other country,
07:25
let's say Germany,
07:33
this is it. But
07:36
yeah. So this is one away that the date can be represented, but also,
07:44
um,
07:45
in Germany and in most of Europe,
07:48
um, you put the first and then month and then year.
07:55
So you get this,
07:58
or if you want to go with the full year for month,
08:03
you can
08:07
choose something like this.
08:09
So we have four digits for a year,
08:13
and this is how you form it?
08:16
A cell. So you're not for mating data in a cell, you're formulating the cell itself.
08:22
And then let's say that you have here the number or, for example, he were here. Have ah,
08:31
date and you want to display
08:35
differently then what is
08:37
available?
08:39
So then you go to custom,
08:43
and then you have a different things and you want it displayed, for example,
08:48
like this
08:50
year, years year year? No, Sorry.
08:54
Um, you wanted, like, they day
08:58
and then
09:00
hyphen and then month month
09:03
and then for
09:05
digits for a year,
09:09
and it will be displayed. This and this is what a custom data type is.
09:13
So you choose how you wanted to be displayed. You put it in the form of thing of a cell. So, for example, here, if you want to change something about
09:26
the number of how the number is displayed, you can go to custom,
09:31
and then you can have
09:35
your way's off defining it. So you can, for example, have it like this, but without comma that separates for thousands. And this is how it looks. So it doesn't change anything. But if we had here 22,000 229
09:54
it will be this plate like this.
09:56
So without the decimal comma,
09:58
so
10:00
you can play with this and you can find the best way to perform at your data and to create your custom data for months if needed. So this this is an example of custom data former, that is not standard in excel.
10:15
Ah, and this pretty much concludes this lesson

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor