# Highline Excel 2016 Class 01: Excel Fundamentals: Efficiency, Data, Data Sets, Formatting

Welcome to Highline Excel

2016 class video number one. Hey, if you want to

download this Excel workbook– Busn218-Week01–

1 and follow along, click on the link

below the video. Hey, this is exciting. This is Highline’s

full Excel class. And we are using

Professional 2016 version. This is our first video. We will cover

Excel fundamentals. Here is a list of our

topics for this video. But before we look

at these topics, let’s go over to the

sheet 218 Pre-req. Now this is for

Highline class Business 218, which is called

Spreadsheet Construction. We will cover Excel

2016 Professional from beginning to end. And we will learn how to

make calculations and perform data analysis. In essence, we will learn

all about Excel 2016. Now the prerequisite

for this class is the Highline

class Business 216. Or you can watch the videos

from this Highline class at this YouTube playlist. Now, you must have one of these

versions, either Excel 2016 standalone, Office 2016

Professional, or Office 365 Professional Plus. They actually call it Pro Plus. Now I have links here

to each one of these. The standalone,

that’s if you want to buy just the single Excel. Office Professional,

that has the full suite. Or you can get Office 365

Professional Plus or Pro Plus. And the 365 just means

that this version will be updated regularly. And since Office 2016 came

out in September 2016, they’ve already added some cool

new functions and features. So if you want the best

version, get the 365 version. Now when you get

that version, you’re going to have to be sure–

and I’ll remind you of this later when we get to the topics

on PowerPivot and data model– but you want to make sure and

go to File, Options, down here in Add Ins, you want to

select Column Add In, Go. And be sure and select

PowerPivot for Excel. Once you do that, then

you’ll have this ribbon tab that we’ll get to

talk about later. One other amazing new

thing in Excel 2016 on the Data ribbon tab, there

it is– Get and Transform. In earlier versions, we had to

download and install something called Power Query. This, Get and Transform,

is Power Query. And we’ll get to learn about

that amazing new 2016 feature. Now, let’s go over and

look at our topics. This video video,

number one, we’re going to go over how

we’re going to do things in this class, what

types of things we’re going to do in this

class, and then we’re going to talk about

data, data sets, formatting, including the ever

important number formatting. Now let’s go over

to the sheet E and E because the goal

of this class is to develop effective and

efficient solutions in Excel for making calculations and

performing data analysis. Now, in order to

understand what this is, we’re going to define

effective and efficient. So effective just means, hey,

accomplished the stated goal. Efficient means

accomplished the goal with the minimum

number of resources and have the accomplished

goal have the ability to adapt to future changes. So every solution

we make in Excel, we will learn how to do it

efficiently and effectively. Now effectively

is pretty much did we get the right answer, right? So if our goal is to count the

number of Fords from over here, well, that is not an

effective solution. Because I can count, it

looks like there’s two Fords. And if I put this

formula in edit mode, it will simple mistake, right? We didn’t highlight

the whole column. But that is not an

effective solution. The effective

solution, of course, is highlight the full column. Another example of

effective– if our goal is to calculate the deduction

at a 7.65% tax rate, notice this formula, gross times

tax rate rounding to the penny, and this one both give

us the correct answer. So this is effective. But that right there

has to decimals hidden. That is not an

effective solution. When I look here, I should

be able to visually see exactly what the tax rate is. This is a simple fix. I can simply come up

to the Home ribbon tab and increase the decimal. But that is an example. Not effective based on number

formatting, not effective based on building an

incorrect formula. Now let’s scroll down

and talk about efficient. Remember, efficient is

accomplished the goal with a minimum

number of resources and have the accomplished

goal have the ability to adapt to future changes. Example one, if our goal is

to count the number of cars that each sales rep sold,

well, if I’m looking over here at Joe. So Joe sold a Ford,

a Ford, and a Toyota. So over here, all three–

one, two, three– solutions show the effective answer,

the correct answer. Joe sold two Fords, zero

Hondas, and one Toyota. Ah, but this is not efficient. Typing criteria into a formula. This actually violates

Excel’s golden rule, which says any time a

formula input can change, you put it into a cell and refer

to it with a cell reference. But look at this, this person

had to type all of this in. That takes a long time. If our resource is time

to create a solution, this is not an

efficient solution. This also is not efficient. This person forgot to

lock the ranges here. Both of these– look at this. Every one of these formulas had

to be typed out individually. Not efficient. It takes us a long time

to create the solution. Whereas over here,

F2, we have the ranges locked so we can

copy it down and over for both of the ranges. And we refer to our

criteria or conditions with cell references. This one formula can be

copied down and over. And it works in all cells. That is an efficient solution. Let’s look at

example number two. If our goal is to calculate

the deduction at 7.65%, well, this formula is effective. It gives us the right answer. And it’s efficient. This one is not. Not only does this violate

Excel’s golden rule, that number is a

number that changes. So we should put it into

a cell and refer to it with a cell reference. The problem with a

spreadsheet filled with– and this is called

a hard coded value– but if you have a spreadsheet

with hard coded values, it’s just too hard to change

and update the solution later. I can see this on the face

of the spreadsheet, which is easy for me to understand. But even better, if I need to

change it, I simply type 8.75%, and instantly, that updates. This does not. Now I’m going to

Control-Z. This set up here has the ability to easily

update when things change later. Another example of not

efficient– that formula is not efficient. It’s adding this column. This one is. Now, this one takes

too long to create. This one, much faster. But also, this will not

adapt to future changes. So if I were to insert

a row– right click, Insert– and watch this. I’m just going to

type the number one. I haven’t hit Enter yet. Watch these two formulas

when I hit Enter. That one, of course,

is efficient. It’s allowing us to change the

structure of the spreadsheet. And it totally updates. That one has no idea that I

just updated by inserting a row. Escape, and now I’m

going to Control-Z-Z. Now, let’s go look

at our third example. And I’m going to

scroll over here. We’re talking about efficient. Our goal is to add this column. This is what I don’t want to do. I don’t want to type

S-U-M, open parentheses, and then highlight

the entire column. Wow, that took a long time. And then I’m going to scroll up. And then I’m going to put

close parentheses and enter. Yes, it’s effective. It gets me the right answer. But it’s not efficient. I’m going to use keyboards,

because keyboards are fast. If we’re building

a solution here, total that column and use

the keyboard Alt-=to open up autosum. I click in the top cell to

highlight all the way down to the bottom. I use Control-Shift

and down arrow. And now, to jump back

to the active cell, I use Control-Backspace. That is efficient. I hit Enter– efficient

and effective. So all the solutions

we build in this class will be efficient and effective. Now we want to go

over to the next sheet and talk about the two

things that Excel does. I’m going to the

sheet What Excel Does. It’s very simple. Broadly speaking, right,

Excel makes calculations like calculating the

tax rate or a deduction and does data analysis,

converting raw data into useful information. Here are examples

of calculations. And notice, this formula,

F2, calculates the deduction. Two cells multiplied

together and rounded. That is a numeric calculation

or a number formula. Excel can also

make calculations, F2, a logical formula. I’m asking the question

with this formula, is Meryl over the

maximum call limit? That is a logical formula that

delivers a true or a false. If I change this to 12,

that logical formula only has two possible answers. Control-Z. Down here,

we have a formula that’s joining first and last names. So when I hit F2, that is an

example of a text calculation or a text formula. We’re joining one cell,

a space, and another cell using the join

operator, the ampersand. Those are examples

of calculations. Examples of data analysis and

the definition of data analysis is very simple. Convert raw data into

useful information for decision makers. We will have a proper data set

with records in rows and field names at the top. And either with a pivot

table or with formulas, we can go from all

of the raw data into some useful

information that will help us, in

this case, determine the amount that each sales

rep sold for each product. Raw data into useful

information for decision makers. Those are the two

things that Excel does. And we will spend

the whole quarter making calculations

and performing data analysis in an

effective and efficient way. Now let’s go over to

the sheet Structure. Just to remind you, in Excel,

there are columns and rows. Columns are letters. Rows are numbers. The intersection is a cell. In this case, it’s B8. All of the cells make up the

worksheet, or simply sheet. Down here, we have sheet tabs. Each one of those tells

us the name of the sheet. All of the sheets together

create our workbook. That workbook has a file name. And it is up here. Now, two things

about these sheets. We can actually use

keyboards– Control-Page Down or Control-Page Up. So I’m on structure. If I use Control-Page Down,

I’m moving to the right. If I use a Control-Page Up,

I move through each sheet moving to the left. Now one totally awesome

trick in these huge workbooks with lots of sheet tabs is

we come over to the arrows. And this moves the sheets,

but not the active sheet, just exposes more

sheets down here. Watch this, I can right

click those scroll arrows. I right click. And instantly, a huge list

of all of my sheets come up. And I can click on any one

and jump to any one of these. If I go to Custom Number

Format Notes, click OK, instantly, I’m on the

sheet Custom Format Notes. Right click, jump back to

Structure, and click OK. All right, I’m going to use my

scroll arrows to expose more, but not move, the active sheet. Now as we’ve already

talked about in terms of efficient Excel solution

creation, Keyboard sheet. We are definitely going

to use keyboard shortcuts in this class. Alt-=, click in the top cell,

Control-Shift-Down Arrow, Control-Backspace, and Enter. If I want to highlight

this whole column, Control-Shift-Down

Arrow, Control-1 to open up Format Cells. I can select Currency to

decimal symbol my negative and click OK. Or any highlighted

button in a dialog box can be enacted

with the Enter key. Now I can come down to the

bottom, Alt-=, and Enter. There are some other

important keyboards. But I do want to

mention Alt keyboards. Now we are going to use a few

Alt keyboards in this class. But let me show you what’s so

amazing about Alt keyboards. Since there’s not a keyboard

shortcut like Control-P for pivot table, normally, if

we wanted to make a pivot table, we have to go to Insert. In the Tables group, we

have to click on Pivot Table to open up Create

Pivot Table Dialog Box. Well, if this is

something you do every single day or

many times every week, Microsoft made Alt keyboards for

everything in the ribbon tabs. And you can teach

them to yourself. Now watch this. I’m seeing the Insert. In fact, I’m going

to click on Home. And my goal is to get to Insert. Watch this. Hit the Alt key. And what happens? The screen tips

pop up and tell you which key you have to hit

to get to whichever ribbon. Now these are succession keys. So I hit Alt and then let go. And now watch this. I’m going to hit N and let go. And now I can see there’s a V.

So V, in succession, Alt-N-V. So I’m going to try

it– Alt-N-V. That’s how it will open Create

Pivot Table Dialog Box. All right, so we’ll use

keyboards in this class. Now we’re scrolling along here. We’re going to talk

about some terminology and what raw data is

in Excel and data sets. But I want to first start by

clicking on the Data Analysis Terms sheet. Now, data analysis and

business intelligence terms. We’re going to be using these

terms throughout the quarter. Data analysis is defined

as converting raw data into useful information

for decision makers. Business intelligence,

which has approximately the same definition, but

with a few extra words, business intelligence

is converting raw data into useful, actionable

information, oftentimes in the form of a dashboard

for decision makers in a business situation. So really, the difference

between these two is the word actionable. And that just means you

can act on the information to make some decision. Dashboards is just a final

end user report, oftentimes with charts and tables. And I added for decision

makers in a business situation because we’re always going to

be in the business context. So really, I see both of

these terms– data analysis and business intelligence–

as synonyms for each other. It really always is how do

we get useful information to make decisions or take

actions based on the raw data. Now raw data is defined as

data in its smallest form that allows Excel data analysis

features and Excel data analysis techniques to work. Now, it’s not hard to

understand raw data. Let’s go over to

the sheet Raw Data. Here we have a column

with addresses. It would be very hard

to sort by zip code or do a pivot table by zip

code or extract using Filter with zip code or city or state. Whereas when we

take that raw data and break it into

a smallest part, are features like sorting,

filtering, pivot tables, and things like that,

and formulas that do data analysis type actions work when

we have data in smallest bits. Now let’s go back over

to Data Analysis Terms. We have raw data. The proper data set– none

of our Excel data analysis features are going to work

unless we have a proper data set, also sometimes called

a proper table format. And it simply means field

names in the first row and records in subsequent rows. Let’s go over to the

Excel Proper Data Set. Now in Excel, there

is one extra thing we have to be careful of–

field names in the first row. That means for any

data set, we have to have a name at the top

of the column that says what data goes in this column. Numbers for sales go here. Text items for sales

rep go in this column. Not only is it so we put the

data in the correct column, but later like pivot

tables, Power Query, there will always be

a field list listing each one of our field names. That way we can ask

questions and query and do data analysis

upon particular columns or combination of columns. Records and subsequent

rows– each one of these is a record, or in this

case, a transaction. And in Excel, it’s

very important. Number three, we have empty

cells or Excel row or column headers around the

entire data set. Now here’s Excel row

headers that are numbers. But notice, the rest of the

data set has empty cells. Now, if we had

extra data over here that was like a node or a

formula or a calculation, data analysis features like

sort, filter, pivot table, and Power Query may

not work correctly. So Control-Z. We’re always

going to leave those empty cells around our data set. Now two things about

a proper data set. This is a proper data set. And so is this. But notice, this has a unique

identifier or primary key in the first column. There are no duplicates. We are collecting bits of raw

data for each transaction. In Business 216 when we

studied Access, the database, we talked about primary

keys and unique identifiers. If you’re a student at

Highline, your unique identifier is your student ID. That way in the first

column with student IDs, they will not mix up your

grades with somebody else. So definitely, this

is a proper data set. But look at this. This is also a proper data set. There is no unique identifier

in the first column. There are definitely

duplicates in the first column. Hey, for us in

Excel, either type of proper data set, whether it

has a unique identifier or not, will work. Now, in some cases,

as we’ll see later, when we’re building

relationships between tables, then it’s going to

become important that the lookup table

has a unique identifier, a primary key, or no

duplicates in the first column. All right, now let’s go back

over to Data Analysis Term. Raw data, proper

data set– well, that’s the ingredient for us

before we do our data analysis. But guess what. When we get the raw data

sitting in our proper data set, sometimes we need to

clean the raw data or transform the data sets. Now cleaning the raw data

means fix unusable raw data so that it can be used

to perform data analysis. Examples– removing unwanted

characters like extra spaces, add needed characters,

split data apart, join data together, or

other cleaning goals. So oftentimes, we’re going to

have to clean our raw data. And as we mentioned at the

beginning of the video, Get and Transform, also

known as Power Query, is just brilliant

for doing this. Also, transforming data sets. That means fix

unusable data sets so that it can be used

to perform data analysis. Examples– sometimes we need

to filter out some of the data, combine multiple tables, merge,

append, or unpivot data sets. We might have to add, remove,

or filter columns in data sets. And there’s other

transformational goals we’ll have also. So any time we’re talking about

data analysis and business intelligence, we

usually import the data, clean it, transform it,

then we have our raw data sitting in the proper data set. And actually, I put

import data down here. This is usually, at

least in Power Query, they Get and Transform

feature we’re going to use– we actually

will connect to external data sources to import it. Then Power Query will allow

us to clean it, transform it, before it imports it into Excel. But importing data

is very simple. We import data from

external sources– and it can be a single table

or multiple tables or sources– into either Excel or

Power Pivot’s data model, or also we’ll see an example of

Power BI Desktop, another data analysis business

intelligence tools. All of this– there

is all of our terms. That’s what we’re going to

do for much of the quarter. And the goal of data analysis

and business intelligence is always create useful,

updatable, actionable information for decision makers. Now, before we go on to talk

about number formatting, we want to talk about a

couple more important aspects of data and tables in Excel. Now, I’m going to jump ahead. I’m going to go to

Excel Data Types. Now, Excel doesn’t really

have a real data type like we learn in

Business 216 when we were doing Access databases. But there is a

type of data type. And there are a few

different data types. We can have text. So we have the word “Excel”

here or the word “rad”, right? Notice that it’s always going

to be aligned to the left. That is the default

alignment for text. As soon as we see something

aligned to the left, we’re going to assume that

Excel considers it text. Now there is no problem. You can do stuff like this. Actually, it is a problem. I’m going to Control-Z-Z. We

will never, or very rarely, use alignment. Because we lose the ability

to see what type of data Excel thinks it is when we

start applying alignment. The only time we’ll

apply alignment is on the finished reporting

product, the useful information end of it. The other data type is numbers. Those, we could type

in a number like 12. It’s always going to be

aligned to the right. So that’s our visual cue

from the default alignment that Excel thinks

these are numbers. We also have logical or

Boolean values, true or false. They will always be

centered and all caps. It doesn’t matter if you

type them in lowercase. When you hit Enter,

it will always capitalize and be centered. Errors are something we

have to deal with in Excel. And sometimes, they are in data

sets or a result of formulas. They are always going to be

centered and capitalized. This one just means divide

by 0, that’s not allowed. Next video, we’ll see

some examples of errors. Another type of data

that we’re going to have to deal with in Excel–

and it’s not really data– it’s an empty cell. So there’s just

nothing in there. And that can sometimes cause

our data analysis or formula calculations to do funny things. So empty cells we’re going

to consider a thing in Excel. So text, numbers,

Booleans or logicals, errors, and empty cells. Now, let’s just look at

why this is important. Notice, I add in this column. It’s working. The sum function

knows what to do. There’s our visual cue. It’s to the right. So we know Excel

thinks it’s a number. We’re assuming that there

is none of this up here. Now what happened here? I’m using the same sum function. But since these numbers

are considered text, the sum function is

programmed to ignore them. And this happens

sometimes when we import data from a

database or copy and paste from a text file or something. Another use of this idea

of data types and default alignment– if I type 15/2/2016

into Excel, when I hit Enter, it’s aligned to the left. Well, dates are supposed to

be serial numbers, right? Well, there’s no 15th month. So if I meant February 15,

I would have to change this. /15– now watch this. When I hit Enter,

the default alignment says, OK, that’s a legal date. Because it’s aligned

to the right. That means it’s

considered a number. Control-Z. As soon

as I type that in and it’s aligned

to the left, that’s your visual cue that

something’s wrong with our date. Same with time– 8:00– and

I’m going to forget the space. That is immediately

aligned to the left. We know Excel thinks it is text. As soon as I add

the space, now I’m entering a legal time value. So when I hit Enter, that

alignment to the right tells me it is a number. Other examples are

20– and I accidentally type two decimals, right? And so that is immediately

aligned to the left. That’s my visual cue

that I had an error in entering the values. If I get rid of it

and hit Enter, boom. Now, next we want to

look at a common mistake. Here is some data

and some formulas. And what they’ve done is they’ve

used the alignment over here. And as soon as they

do that– and again, centered is one of the more

common alignment mistakes that I see– but

as soon as you do that, you lose your visual

cue for tracking down errors. I’m going to Control-Z.

So what’s happening here is that some function

is trying to add this. But there are some numbers over

here that are considered text. Similarly, there

is another number. Control-Y, which

is undo the undo. That is centered and may

be fine for printing. But we lose our ability

to track down the errors with our visual cues. Control-Z. In general, if I

have formulas or the source data is raw data for

pivot tables or charts, I don’t use the alignment. Now, it’s certainly all

right for the final product. Here’s an example of some stocks

and some statistics over here. And when you highlight

this, you can see clearly we’ve used center. But this is something

we’re going to print out. It’s easier to read this way. We’re not going to

use these numbers as any inputs for formulas. So that is fine. All right, so alignment

is very important. Even though we don’t

have a proper data type like we do in a

database like Access, here are the different

types of datas. And by the way, one

last final note. If you remember back to our

study of databases and Access, when we had a

column, we were not allowed to have text and

numbers in the same column. We had to define the

whole column or field as a particular data type. Now, when we get to Power

Query, Get and Transform, and PowerPivot, we

do have the ability, some of the new features,

to define a data type for a column. Now, we want to go over

to the sheet Excel Table and talk about the amazing

Excel table feature. Now, we have a proper data set. And what we’d like

is I’d like to have a formula here, just

the sum function, adding all of these numbers up. But if I add any new

records, I want the formula to update and include a

total of any new records that I add or delete. The way we can do

that in Excel is by converting this proper

data set into an Excel table. Now, you click in a

single cell in the table. And to get to the

Excel table feature, you go Insert, Tables,

and right there. Now, we’re not going to

use the ribbon method. We’re going to use that

keyboard listed right there. And you can see it says

Control-T. So you ready? Control-T. Now, it has

highlighted the correct fields and records because I have

empty cells all the way around. Now, the OK button

is highlighted. So I simply hit Enter. Now I have a bunch of new

aspects to this table. Now before we look at them,

I want to immediately notice that we’re in the Table

Tools Design ribbon tab that popped up when I

converted this to a table. We always want to go over in

Properties and name the table. Now, since I make

tables all the time, as soon as I do

Control-T-Enter, I use the keyboard to name, which

will jump my cursor up here. It’s Alt-J-T-A. And there

you can see it’s highlighted. I’m going to call this

Product Sales, so ProdSales and Enter to register that name. Now a number of things happen. We get formatting. We get drop downs that

have sorting and filtering. But the most amazing

thing is that if we have a formula or a chart or

a pivot table or Power Query, any of the other features

that point to this, the ranges will update

when we add new records. So let’s go ahead

and try this, Alt-=. Now watch what happens. Remember we named the table. And this column is

called Sales right here. So watch as I’m going

to click and drag. And as soon as I

do that, it puts in what’s called the table

formula nomenclature. It’s also called a

structured reference. There is the name of the table. And the field name will

always be in square brackets. If you remember

back to Access, we had square brackets for our

field names in Access database. Now when I hit Enter,

let’s just come down here. Now there’s two ways

you can add new records. One way is to simply

click in the cell directly below the table. And I’m going to type 1/5/2015. And when I hit Tab, you could

see the table automatically expanded to include that. The sale was $300. Now before I hit

Tab, look up here. Now when I hit Tab,

instantly, it updates. It is now looking at that new

number in this new record. This was a Quad. And notice when I type a Q,

it’s got an auto-complete. Now, the other

way to add records is notice I’m in the last

cell in the last record of the table. When I hit Tab, instantly,

it creates a new record. 1/6/2015, Tab,

100, Tab, and Tri. Now, I’m not going to hit Tab. Because I don’t want

to add a new record. I’m going to hit Enter. Now, let’s go up and hit

F2 to put it in edit mode. And sure enough, that

is an expandable range. Let’s make a pivot table. I’m going to click in

a single cell, Alt-N-V. And I want to put it

on this existing sheet. So I’m going to click Location. I’m going to click right over

to the side and click OK. And just like that,

we get our field list. There’s our one,

two, three fields. I’m going to drag the

product name down to Rows. And in a pivot table,

when you do that instantly from the products column,

it shows a unique list, one of each item. Now I drag Sales down to Values. And instantly, because it’s a

number I’m dropping down here, it will default to

the sum calculation. Now, I don’t like row labels. So I immediately go up

to Design, Report Layout, and Show in Tabular or Outline. I’m going to choose Tabular. Now I see the field name. I’m going to leave it that way

without any number formatting. But here it is another object

pointing to this table. Now, you ready? I’m going to click below the

last record, type 1/7/2015, Tab, 500. Now watch what happens

when I hit Tab. We already know

the formula update. But this won’t update yet. But let’s do a Tab. And then this was for Carlota. So I type a C. Auto-complete

looks above and fills in Carlota. And now I hit Enter. Now, the sum function updated. But for pivot tables and later

for Power Query and PowerPivot, we will have to refresh. I’m going to come

over and right click. And because I’m inside the

pivot table, I see Refresh. When I click Refresh,

instantly, it updates. So Excel table feature,

we will be using it a lot. Now I’m going to use

my scroll arrows. And we’re going to scroll over. And now we’re going to

talk about number format. We’re going to click on the

sheet Number Formatting. Now we’re going to look

at a couple examples and then talk about what

number formatting really is. Here we have some sales numbers. And up in the Home ribbon,

in the number group, there’s a drop down

that’s pretty convenient. We can select in this case

either Currency or Accounting. I’m going to select Currency. But notice down here,

there’s a bunch of decimals. And some numbers don’t

have any decimals. So as soon as I click

Currency, what happened? Where did those dollar

signs come from? Where did that decimal

and zero zero come from? Well, let’s click in this cell. And on the surface

of the spreadsheet, I see how many characters? One, two, three,

four, five, six. But look up into

the formula bar. What is really in the cell is

simply two characters– a five and a zero. So there can be a disconnect

between the surface of the cells and what

we see and the number that lies in the cell underneath

the number formatting. Now, look down here. I’m going to click on this. It looks like there’s one,

two, three, four, five, six characters in the cell. But look at up here. There’s is one, two, three,

four, five, six, seven characters in the cell. Now in this case, if we were

never going to add this column, it’s OK to display the number

to look as if it’s rounded. It is not rounded. That number, $57.26,

is not actually the number in the cell. And if we added them, it

would not be adding $57.26. It would be adding all of

those extraneous decimals. And we’ll talk about what to

do if we need to round later. In our prerequisite

class, you learned all about the round function. And we’ll talk about

that in a little while. Now here’s another example. What about over here? 25 plus 50 plus 10 plus 15. Well, of course, it’s 100. But in Excel, we

get to do magic. 25, 50, 10, and 15,

the exact same numbers, hey, we get to make it equal 99. There is no way. As soon as you see

this situation, you have to suspect

number formatting. Now, I’m going to highlight

all these numbers. And the eraser for

number formatting is we go up to Number. And there’s the dropdown,

general number formatting. general number

formatting will wipe away any number format

applied and show you what really sits in the cell. Now I’m going to

click on that General. And sure enough, over here

there were some decimals. And all we did was

decrease the decimals. So when I come up and

go Decrease Decimals, that is a number formatting

that displays fewer decimals. If I were to click on this

50, it’s not really 50. It’s 49.50. Formulas will not look

at number formatting. They always will look at

the underlying number. So sum is functioning on

whatever numbers there are plus the decimals. But over here, same thing. It just happens to be that

the numbers are displayed as they also sit in the cell. So our conclusions about number

formatting– number formatting can display numbers on the

surface of the cells that can be different than the

underlying numbers that sit in the cell. Right here, we’re displaying

the numbers exactly as they sit in the cell. Over here, we’re displaying

the number differently than it sits in the cell. Our second conclusion for

number formatting– formulas make their calculations

on the actual numbers in the cells, not the

numbers that are displayed on the surface of the cell. So that sum function is

looking underneath that number formatting and adding

all of the numbers, including all the decimals. Remember, you can

think of it this way. Number formatting is a facade. A facade just means

something on the surface. Like your house is painted

beautifully, but on the inside it’s messy. At Halloween, you might be super

sweet, wonderful person inside. But you might have a

Halloween witch or zombie mask on the surface which is

different than the inside. Remember, what you see is not

always what is in the cell. Another important example

of number formatting– we’re going to learn the

keyboard for today’s date. If you’re ever entering

transactional data and you’re entering stuff for

today’s date, it’s Control-;. So Control-;, and boom. It puts 4/5/2016

right in the cell. Now actually, watch this. I’m going to click Escape. Because notice, the

number formatting is general before I do anything. In fact, general is the default

for all cells when you open up an Excel workbook unless

they’ve been altered, of course. All right, so you ready? Control-;. As soon as I– and I’m

going to use Control-Enter because I want to put

that thing in the cell and keep the cell

selected– so ready? Control-Enter. Wow, look at that. So when I enter today’s date

whether using a keyboard or typing it out, it will

always apply the date number formatting. Now I’m going to come down

here, Control-;, Control-Enter. And now, instead of going up and

applying general to see really what is in the cell like this,

I’m going to use the keyboard. It’s Control-Shift either

Tilde or Grave Accent. This key is to the left of

the standard number 1 key. So you ready? Control-Shift-Grave

Accent-Tilde. What? 42,465 sits under April 5, 2016. Yeah, and you all know this

from your prerequisite class. That is the number of days

since December 31, 1899. And why is it? Well, it’s so we

can do date math. Now, before we do some

examples of date math, if I have the number

one, two, or 41,521, these are the actual dates if

we were to apply date number formatting. Now, just a second

ago, we did a date. And then did

general to erase it. But you can do the

reverse to prove it. These numbers, if we apply

date number formatting– and I’m going to use the short

date– boom, there it is. Control-Z. What this

means is under every day is a number that we

can do date math. Remember, if this is

8/26/2013 and this is 8/21, this date is further

through history, which means the serial

number underneath is bigger. So we’re allowed to

calculate the number of days an invoice is late– equals the

larger date minus the earlier date. And when I hit Enter,

it tells me five days. Now, the reason– and

I’m going to highlight Control-Shift-Tilde to look at

the underlying serial number date, the number of days

since December 31, 1899– of course, bigger number

minus smaller number, there’s exactly five

between the two. Now, Control-Z. When

you’re doing invoices, of course when I

take 26 minus 21, the 21, which is the

first date, is subtracted. That 21 is not included. And that is the way

standard invoicing works. Now, if we have a

different situation, project start date

and project end date, if we want to figure

out how many days total for the project,

equals– and watch this. I’m going to use my arrow keys

to get the cell references. And then I’m going to type

subtraction and arrow, arrow to get that other

cell reference. The rule is if your cell

references are close in, instead of using your

mouse to get them, you should use your arrow keys. Now, that won’t give

me the correct date. Because the 21st

will not be included. So any time you have

the first day included, you have to add one back in. Control-Enter. And so total days for

the project is six. Another great use–

and look at this. I had this left in there. I didn’t want to

have it like that. If we have the loan

issue date, 2/20/2016, and the loan is outstanding for

10 days, I can do date math. I can take the serial

number date that sits underneath that

date number formatting and just add a

regular old number. And so now if I took the

loan out on the 20th, I add 10 days, Control-Enter,

that is not number of days. That is called

the maturity date. That is the date that I

must pay back this loan. So date math, number formatting,

incredibly important in Excel. Now let’s go look

at another example. And this we did in our

prerequisite classes also. Any time you type a time– so if

I come over here and type 8:00, and I forget the

space– we talked about this earlier– that

is text aligned to the left. But if I include

the space, Excel knows that this

should be a number. And by the way, look. I haven’t entered this

number as a time yet. So it’s still general. But when I Control-Enter, time

number formatting, custom time number formatting, is applied. That number is

aligned to the right. It is a time value. Now time values represent the

proportion of one 24-hour day. So underneath 8:00 AM

is 8 divided by 24. Now if I apply general,

Control-Shift-Tilde or Grave Accent, sure

enough, 8 divided by 24 is 1/3 or 0.33333. Control-Z. So all

times are going to be whatever the

time is divided by 24. Now this is important to know. Number formatting,

underlying number. Because if we go to

create a formula, remember formulas act on

the underlying numbers, not the number formatting. So if I were to take equals the

later time minus the earlier time, Control-Enter, it will

add that custom time number formatting. It looks like it’s right, right? 3 and 1/2 hours. But if I have a formula

over here that says, hey, $25 times 3 and 1/2 hours, that

should be something like $82.50 or something. This person’s going

to be totally unhappy. $3.65 for 3 and 1/2 hours? Well, we’re going to wipe

away the number formatting to see what’s underneath–

Control-Shift-Tilde or Grave Accent. Of course, 3 and 1/2

hours divided by 24 is exactly that decimal. So anytime you are

doing time math and you want the

actual number of hours, we force subtraction

to happen first. Remember, our order

of operations, right, by parentheses? And we multiply times 24. Now, 24 is an example of a

number that will never change. So we can just type it

right into our formula. And instantly, I

get, hey, 3.5 hours. And oh, I can’t do

math in my head. I said $82.50. It’s $87.50. So number formatting

is a facade. This is yet another

important example. Our formulas act on

the underlying number. So in this case, we had

to know that and create a formula that was with respect

to the underlying number. All right, other examples

of number formatting. But first, I want to ask the

question, what is a percentage? Now, all of you should

know what a percentage is. There’s the pre-requisite

class Business 216. Also, if you had Business

Math, Business 135. But a percentage is simply a way

of displaying parts out of 100. So I always think

of it this way. Let’s think of tax. If the tax rate is 9.95%. I think that’s the

tax rate in Seattle. So I’m going to say,

hey, for every $1.00, I have to pay 9.95 pennies. Now, how many

pennies are in $1.00? 100. So the question is how

many parts out of 100 are we required to pay? Well, it’s simple division. I say 9.95 divided by 100. And so what I get as a decimal. And that is 0.0995. Now, sometimes it’s hard to

quickly see and understand a decimal, right? Especially in terms

of the tax rate. So we can display

this underlying number as a percentage. Now, here’s what it does. And we learned this

in many other classes. But when you apply percentage

number format, even when you were taught how to do

it by hand, remember, you had to take the

decimal and slide it two positions to the right. So the decimal should be

right after that first nine. Then we add a percentage symbol. That is the process to

display the underlying number as a percentage. Hey, I’m just going to

go up and I am not going to use that button right there. That button when I click it,

sure, it adds a percentage. But it displays no decimals. That is a huge source of

errors for people who don’t know about number formatting. Now I’m going to Control-Z. For

us, it would have been easy. We could have just

increased the decimals. But since oftentimes

we’re dealing with two decimals

for a percentage, the built-in in the Number

Group dropdown is Percentage. And it will always,

if you click this, show percentages

with two decimals. And sure enough, that

number formatting displaying on the surface of

the spreadsheet or the cells is displaying the decimal

and added a percent symbol. Now, many times you will

get a number like this. It is perfectly all right

in all of your calculations for budgets and taxes

and invoice and payroll to leave it like that. But if you want to

apply percentage number formatting, no problem. We simply go up and use

that if you want to. Or we can Control-1 to

open up Format Cells. And the Percentage

number formatting is one of the options here. And sure enough, we can

change the decimals. I’m going to leave it as two. There is OK. I’m going to hit Enter to

enact that highlighted button. Now I want to specifically

look at a number of important examples

for percentages. Oftentimes, you see the

number 0.03 in the cell. Or maybe you even

type it in because you know the tax rate is 3%. So you type that in. And then you come up and you

apply the number formatting. So there it is. The big mistake– and this is

one of the more common mistakes throughout all of

Excel history– the person doesn’t know

about number formatting. So they type a 3,

fully expecting when I go up and apply

number formatting that I’m going to get 3%. But no way. That number

formatting obeyed you. Control-Z. It took the decimal

which is after the three and went– slid it two

positions to the right and added the number symbol. So we don’t want to do that. If you’re going to type the

number in, first type 0.03. Now, there is a way

to avoid that if you pre-format your cells. And I’m going to

go up and apply. And notice, we don’t

have a preview here. But it will always from this

dropdown apply two decimals. Now watch this. If I– and I can see it’s

pre-formatted– if I type a 3, that percentage symbol pops up. It means that the cell

has been pre-formatted. Now I’m typing what looks like

an integer or a whole number 3. But when I enter that,

underneath is 0.03. And it’s properly

displayed as 3% So I pre-formatted. I’m totally allowed to type a 3. You’re also allowed– and I

can see the pre-formatting– to type 0.03. And when you hit Enter,

that will work also. Now in either case, when

I typed a 3 or 0.03, because percentage number

formatting was applied, if I Control-Shift-Grave

Accent-Tilde to look at what’s under the number

formatting, they both have three

parts out of 100. Control-Z. A couple other things–

you can format as you type. So if you want 4.5%,

right now, if I enter it, it is 4.5, whole

number 4, 0.5 decimal. But if I type a percentage

symbol– and you can look up here. I haven’t hit Enter. So it was general

number formatting. But I’ve typed a

percentage symbol. So when I Control-Enter,

I’m telling Excel to format this as a percentage. As I type, I can see clearly

this is a percentage. Underneath that number is 0.045. One other important

aspect– and we already saw this– this one

of the most common errors in Excel. Somebody has a proper decimal

for a tax rate or budget or whatever it might be. And they come up and they

use this button here. Boom. If they don’t know about

number formatting as a facade, they’re totally tricked. They think it converted

their number to 3%. Of course, if I look at 100

times 3%, I should get $3.00. But that formula is not going to

look at that number formatting. Totally going to look

underneath at that 0.025. I could clearly fix that by

simply clicking the Increase Decimal. That’s how we want to leave it. Remember, as we

talked about earlier in the video, effective

and efficient solutions. We do not leave something

that will confuse the user of the spreadsheet. If it’s 2.5, we want

to leave it 2.5. Hey, we’ve talked a lot

about number formatting. Most of this is review. But guess what. We’re going to talk about

something new that has to do with number formatting. We’re going to click

on the sheet CNF. And CNF stands for

Custom Number Formatting. That just means

instead of using one of the built in categories

for number formatting either from this drop down or

Control-1 on the number tab from this list of categories,

we can create our own custom number formatting as

long as we type the code. Now, I have a whole sheet

with notes over here with all the code. But at this juncture

in the class, we’re just going to look

at date, time, decimals, and percentage

number formatting. And we’re going to start with

the easiest number formatting to learn, which is the

number formatting for dates. Now, when we get to typing

out our code, it’s easy. M means month. D is day. And Y is year. Now let’s just go

learn a good trick to teach ourself about

custom number formatting. There’s already date

number formatting applied on this cell. If I Control-1, I can see

on the Number tab Date. I’ve selected this top one. And by the way, there’s a

bunch of subcategories here. You can pick whichever

one you’d like. And notice up at the

top, there is a preview. But we’re going to

select this one. And if we want to know what

the custom number formatting is for whatever built in category,

we just simply going down to custom. And in the Type text

box, there it is. It’s simple– m/d/yyy. Now I’m going to click Escape. And we’re going to

click on the cell below. And now we’re going to try

this for ourselves, Control-1. We come over to

Number, down to Custom. And in the Type text box, we

simply highlight– and watch this. This is always going to give

us a sample or a preview. I’m going to delete this. And sure enough, if there’s

no custom number formatting, it shows us the

underlined serial number. Now let’s have a little fun. Let’s type M. Wow, even

though the serial number is in the cell, if

I type a single M, it’ll just show me the

number for the month. Type another M and

a third M. Wow, it shows us the three letter

abbreviation for month. Let me type a fourth

M. And no way, it will display the

entire month name. Now let’s backspace. If I type a D, just the day. Now, two Ds shows

us a leading zero. And that will work

for month also. 3 Ds gives us the abbreviation. And four Ds gives us

the full day name. Let’s backspace. If I take just a Y, it shows

me a two-digit abbreviation for year. If I type three, it gives

me the full year, or four. Now what I want to do is

simply in this cell right here display just the day. So I’m going to take DDDD. That is our first custom

number formatting. We are displaying a serial

number to show just Thursday. So when I click OK, it

displays on the surface of the spreadsheet Thursday. Underneath, yes there is a date. And under that, there

is the serial number. Why would you ever

want to do that? Maybe you’re making a

schedule or calendar and you want to

display the day name. Also later on in

the class, we’ll see that it’s

important sometimes to figure out how many events

happened on a particular day. And custom number

formatting will help us figure that out from a

serial number what day it is. Now let’s try a different one. Click in the cell below,

Control-1, come down to Custom. And in the type, I want

to show the entire day. So I put four Ds. And I’m allowed to type

a comma and a space. And now I want the abbreviation

for month, so three Ms, Space, the day, so a single D,

comma, space, and then YYY. And you can combine this however

you want to get your own custom number formatting. So when I click

OK, it is displayed as if there is text for

Thursday and November. But really, underneath,

there is that serial number. Now, time– H is for hour. M is for minute. S is for second

separated by a semicolon. So here I’m going to

Control-1 and look at what the custom

number formatting. An H, a colon, and an AM/PM. If I were to delete

this, sure enough, there’s the serial number. If I type just H, it

shows me just the hour. Just M, just the minute. Just an S, we don’t have any. So it shows zero. Now in this case, I want

to display a leading zero. So I’m going to type

HH and a colon, MM. And now I see 09:57. Now I want to show

either AM or PM. So I do a space,

AM/PM, and there we go. When I click OK, it displays the

leading zero, hours, minutes, and an AM or a PM. If I type 3:00 PM–

by the way, remember, if you don’t type

in times correctly, that becomes a text value. But as soon as you

add that space, it is a time value

aligned to the right. And that works. Now, what if you didn’t

want to show AM/PM, but you wanted military time? No problem. Control-1, I come

down to Custom. I highlight this. I simply typed HH:MM. And that will give us military

time, three hours past noon. Click OK. Now, if someone decides

to put 3:00 space PM, guess what, it’ll enter. But it will display

as military time. You can see up in the formula

bar it actually is always going to be stored with the AM or PM. But our number formatting will

display it in military time. Now we want to talk about

displaying number of decimals. This is similar to clicking

these buttons over here. Click in the cell, Control-1. I’m going to come

down to Custom. And now, highlight General,

type a zero, a decimal. And watch this. If I type four zeros, that’s as

if we clicked Increase Decimal four times. So in this case, I want to show

always the number of pennies. Now what this zero

says is please show me significant and

insignificant digits. So the 4 is significant. The 0, since we don’t

have a digit there, is considered insignificant. Click OK. Now it displays as, 1.40. But remember, now

that we have those two 0’s there, if I

were to type in 1.755, this will be displayed as 1.76. Control-Z. Now let’s see what

happens if– Control-1, down, and Custom. There’s that 1.35. If I typed in 0.0, I’m limiting

the number of decimals. Click OK. So even though we see displayed

1.4, up in the formula bar is 1.35. Now, the 0 shows significant

and insignificant digits. If you only want to

show significant digits, you can use the pound sign. So let’s try it– Control-1,

come down to Custom, and in the Type text

box, I type you #.##. Now notice, I can

already see the preview. That pound is there in case

I type an extra decimal. But it will not display it

if there is nothing in there. So when I click OK, 1.4. If I type 1.35, the

35 is displayed. If I type 1.455, again, because

I only have two pound signs, it’s only going to show

me two significant digits. Control-Z. The last custom number

formatting we want to look at is percentage. And we will use the 0’s. We simply have to add

a percentage symbol. Now this is 0.3575. If we format it as a

percentage, it’s 35.75%. Control-1, come down to Custom,

and in the Type text box, 0.00. And notice that without

the percentage symbol, it’s just showing how

many decimals to display. But as soon as I type that

percentage symbol, boom, it slides the decimal over and

adds the percentage symbol. Click OK. Similar to our

0’s for displaying only a certain number

of decimals, Control-1, we can come down to Custom. And in the Type text box,

0.0 and a percentage symbol. And you can already

see the result there. Click OK. It is displayed as 35.8%. But really, 35.75%. And under that is 0.3575. So this is our first glimpse

at custom number formatting. Now we have a little

bit more power to display the

numbers however we want on the surface

of the spreadsheet. We saw back over

here– remember, the number one idea or

concept is number formatting is a facade. Hey, now, we want to go over

and talk about style formatting. So I’m going to click on

the sheet Style Format. Now, style formatting

is simply adding things line bold, fill color, font

color, borders, and more. Now we have some guidelines

for style formatting. Number one, use

the minimum amount to effectively

deliver the message. Be consistent. That means if you’re going

to pick a particular color and font for field names,

be consistent and always use that particular

style formatting. All numbers should have

the same number of digits. That’s, again, being consistent. And units must always be

indicated with either number formatting or labels. So if it’s money, you need

to point out it’s money. Percentages, use percentages. Now there’s two

schools of thought for stylistic formatting. Minimalism school–

that school says, hey, I’m going to have bold

for the field names at the top. But why do you think they

have those gray lines? I’m not going to add

any extra borders. And I’m going to very

rarely use fill or font color or things like that. The more than minimal

school says, hey, I’m not just going to add bold. I’m also going to add perhaps

font color or fill color and maybe I’ll

add black borders. All right, here is

the minimal school. I’m going to use the keyboard

Control-B to add bold. And I am going to add

some number formatting. These are accounting numbers. So I’m going to highlight

all the numbers, Control-1. Here’s the Format

Cells dialog box. I’m going to choose Accounting

and choose to show two decimals with a dollar sign. Now, we didn’t talk about

accounting number format. But there are a

couple things that distinguish it from currency. One is negative numbers

show up in parentheses. 0’s show up as dashes. Not only that, but

the dollar sign will always be

fixed on the left. Now this doesn’t look

too minimal to me. So I’m going to highlight the

minimal numbers and Control-1 and choose to show

no dollar sign. Click OK. So I’ve indicated the

unit up here at the top and for the bottom line number. That is an example of minimal. Now let’s do less than minimal. I’m going to Control-B,

come up to Font group. And for fill, I’m going

to use the Fill Bucket. I’m going to use dark blue. And then for font color,

I’m going to use white. Now, I’m going to

Control-Z-Z. There’s another way we can do this. When we highlight

we can right click. And look at that. Up pops the mini toolbar. I can select Fill from

here, and then Font Color. The rule for fill

and font color is there has to be a big

enough value difference. So for example, if I choose

red fill with black font, that is not going

to print correctly. If you squint your

eyes at this and you can’t see a very big

difference in value between the font

color and the fill, you know the difference in

values is not big enough. I’m going to go back to my

dark blue and font white. Now I’m going to highlight the

whole table using the keyboard Control-*. And I’m going to use the

asterisk on the number pad. If you’re using the

normal number pad, you have to do Control-Shift-8. I like Control-* on the

number pad because instantly, it will highlight

the current table. Now, I’m going to do Control-1

to get to Format Cells. And really, Format Cells

dialogue box has everything. It has number formatting. It has alignment. It has font, all sorts

of things, borders, fill. And I’m going to

go back to Borders. And I’m going to select Outline. That does just the outside. You can see right here

it gives us a preview. And then inside, that does

all the vertical lines and all the horizontal lines. Click OK. Now I’m going to add some

other types of borders. In accounting, oftentimes

we have a line, a dark line, right there saying I just did

a calculation on the numbers above. Because this is a formula

adding the total expense. And so I’m going to highlight

just the total expense line, Control-1. And on the Borders tab, I’m

going to select the border. Then I’m going to

select the color. In this case, I’m

keeping it automatic. And then I’m going

to come over here. And notice, I want

the line on the top. So I’m going to

click the very top. Similarly, you could have

used that button right there. Click OK. Well, that didn’t change. Oh, I have to click

off to the side. So there, that dark

line in accounting means I just did a calculation

on the numbers above. Similarly, down here,

there is net income. We did a calculation

on the number above. Notice, that line

doesn’t mean adding. Because it could be

adding in this case. But certainly, it

could be subtracting, or in cost accounting,

there’s all sorts of different

calculations you can do. I need that dark

line at the top. And then I need a double

line at the bottom. That double line means

this is the bottom line. This is the number I

was trying to calculate. Control-1, and now I

have two different lines. I’m going to select the

line, select the color, and then draw it. I can either click that button

or click right on the top. Now I need to go back to

get my next border– Border, Color, and then Draw. In this case, I could

click that button. Or I always like to

click and draw it myself. Click OK. Off to the side,

and there we go. Now I’m going to highlight

all the numbers, Control-1. On the Number tab, I’m

going to select Currency. That’s the type of

negative number. I need to see the dollar

sign and two decimals. Click OK. Now the difference between

currency and accounting– well, accounting has fixed dollar

sign, zeros are dashes, minuses are parentheses. For currency, we have the choice

of how to display our minus. 0’s show up as 0’s. And that’s called a

floating dollar sign. Notice it floats with

how wide the number is. Now, I’m going to do the same

trick on the inside numbers. I don’t want it as cluttered. Control-1, and the symbol,

I’m going to say none. Click OK. Now there’s all sorts

of different types of style formatting. This is just two

examples– a minimal and a less than minimal. Now, I actually had

some notes up here. It says minimalism school says–

more than minimalism school says– I actually have notes. This is video number one. And so for every single

video, there will be notes. And right next to the Excel

file that you download, you can download these notes. And they’re pretty

detailed notes. This is the table of

content up at the top. If I click on this

style formatting, it will jump to that section. And there are the full notes. And so these notes have a lot

more detail than the notes you see in the spreadsheet. So be sure and download these. And they correspond

pretty exactly to what you see in the videos. All right, we have one

last stylistic example. Here’s a data set. And right off the bat, we

have field names at the top. But notice, the column

widths aren’t wide enough. So I’m going to highlight

with my downward pointing black arrow

the G and click and drag all the way to I. And I’m

going to see what happens if I double click to best fit. Now sometimes that’ll work,

sometimes that won’t work. And why didn’t it work

for this G column? Because best fit

will always best fit to the biggest

thing in the column. So in this case, I probably

want to manually click and drag this. Now, if we want to wrap text–

that means have employee and then name on the next

line, sales and amount on the next line–

we could actually come up and use the

automatic button Wrap Text. That’s a type of alignment. Now notice, it

didn’t do anything. It won’t do anything until

I change the column width. Now once I do that, I have

to come over and double click between 14 and 15. And instantly, now I have

wrapped text in the same cell. Now, if you would like more

control than the Wrap Text button, meaning perhaps

I want product and name on the next line, we can

manually set line break or wrap text. I’m going to put my

cursor, double click right before the P, Backspace. And the keyboard to do a

manual line break or wrap text is Alt-Enter. Now, when you

Alt-Enter, you still have to have wrap text

to get it to wrap. If I didn’t have

this turned on, you could see that wouldn’t work. So I’m going to wrap text. And instantly, that worked. But the break will always happen

after the word “boomerang”. Let’s try the same thing over

here– Backspace, Alt-Enter. Right here–

Backspace, Alt-Enter. Now I’m going to add a little

bit of formatting here. And I do want to show you

one really cool thing. Let’s just format

this first cell here. I’m going to do fill, dark

blue, font color, white. And then I’m going to click. It just happens to be

the right border there. Notice I could select all sorts

of different border options. But I’m going to

click that button. Now what I want to

do is save that. In fact, I’m going to

Control-B. So now we have fill color,

font color, bold, and an outline for the cell. What I’d like to do is

save that as a cell style so I can use it later. That took four clicks, right? But if I save it

as a cell style, I can simply have a single click

to apply all four formatting elements. So I’ve formatted the cell. I come up to the Styles

group, click the More button, and then down here, it

says New Cell Style. Now, it automatically

picked up everything. If you wanted to do more,

you could click here and go through the

Format Cells dialog box and do whatever you want. I’m going to click Escape. But what I’d like to do is

call this Field Name Blue. And now I can click OK. Now, I’m going to highlight both

cells, come up to the dropdown. And notice, I have a

bunch of them here. Because it’s picking up

other times I’ve done this. But there’s the new

one that I’ve selected. That is pretty convenient. And instantly, it is applied. All right, so a little bit

about stylistic formatting. We’ll do lots of

stylistic formatting throughout the

rest of the class. Now I want to come over. And our last topic

is page setup. So here we have an

amortization table. Control-Down Arrow,

looks like it’s 365 rows. Control-Home jumps

up to cell A1. And now I want to look

at what this would look like if I didn’t do page setup. Control-P– and

sure enough, there’s a preview I can click through. And there it is. I would like to do

things like center this. And I’d like to repeat the field

names at the top on each page and have page numbers. There are some options here. I’m going to click Escape. But I’m going to come up

and go to Page Layout. Page Setup Group– and

there’s some options here too. There’s the dialogue launcher. If I click that, it

gets me to Page Setup. But I want to learn the

keyboard– Alt-P-S-P. Now, how do you determine

portrait and landscape? Well, this is much

taller than it is wide. So I’m going to select portrait. You could adjust. Now for us, we’re going to

print this on many pages. So I’m not going

to use this adjust. But if you had one

page and you wanted to blow it up or shrink it

down, you could do Adjust To. What we want to do

is say, hey, Fit To. Now, I always want it

to fit to one page wide. You can already see there’s

a dashed line down here. That means it will

not fit onto one page. So I definitely want

to say one page wide. But I don’t know

how many pages tall. So the trick is highlight

what’s over there and delete it. By deleting it, it

will automatically print out as many as there are. If I were to add more

rows or delete more rows, it would always know to print

out the right number of pages. So one page wide by

however many pages tall. Now, this was the Page tab. Now let’s go over

to the Margins tab. We definitely can set top,

bottom, left, and right, and even where the headers are. We don’t need to do that here. So I’m simply going

to check Horizontal. And that will automatically

center it on the page. Now I want to go over

to Header and Footer. Let’s start with the header. There’s the preview. There is nothing. There’s the dropdown

for built-in headers. And there’s the Custom

button for custom header. Now, we have three sections. We’re not going to

put anything here. So I’m going to click Escape. But for the header– Preview,

built-in, fully customizable. For the footer, I

don’t see a preview. Here’s the dropdown. I am going to select Page 1 of ? The page 1 of ? simply means it’ll print

out– if there’s five pages, 1 of 5, 2 of 5, et cetera. If later you have 10 pages,

it will say 1 of 10, 2 of 10. And we can see down

here in the preview, it’s got our built-in footer. Now I want to

customize this further. So I’m going to come

up to Customize Footer. There’s the code for

that number of pages. Now we start on the left. If you want to show

the date, that’s the code to automatically

print out today’s date. If I do a space, dash,

and space, notice, I can mix type and stuff with

the code from these buttons here. Now, I’m going to

click on this button. And it puts in

the code for time. So it will always

print out the time that I print this workbook. Now I’m going to come

over to the right section. I could put the full file path. I could put the workbook name. I could put the sheet name. Now, I’m going to put

the sheet name here. Now what’s nice about

this is it’s dynamic. It’s linked. If I change this later,

this will update. That is a great button if you

have, for example, a budget with January, February, March. You do the page setup

on the first page. And every time you

copy the sheet over, it will automatically pick up

whatever sheet name there is. All right, I’m

going to click OK. Header and Footer, Preview,

Built-in, Customize. Now let’s go over

to the Sheet tab. Print Area– this

is great if you wanted to exclude some

part of the sheet. So for example, if I only

wanted to print out this part, everything else

would be excluded. Now that is

especially useful when you have calculations or notes

off to the side which are not part of your final report. Now, we don’t have a print area. We’re going to print everything. So I’m going to delete it. What we want to use is

Rows To Repeat At Top. Watch this. I’m going to click right

on Row Header Four. It will automatically

print out those field names at the top of each sheet. You could also do columns

if you had a table that was really wide and you wanted

to repeat names or company names or categories, you could

highlight a particular column. There are some other settings

here that you can read through. You can also determine

which way if you have many tables to print. That’s it for us. All we want is rows

to repeat at top. I’m going to click OK. And now, when I

Control-P, look at that. It is center. I have my footer. When I click Next

Page, look at that. It repeats just that row four. That is quite spectacular. There is our page numbers. All right, I’m going

to click Escape. I’m not going to print. Let’s just come down here. I’m going to double click and

call this Amortization Table, and Enter. Now, when I

Control-P, boom, that is totally dynamic and updates. Escape. All right, so that was a

pretty epic first video. We talked about page setup. We talked about

style formatting. We talked about number

formatting as facade. Sod We talked about

Excel data types. Here’s the list right here. We talked about the amazing

Excel table feature. We talked about terminology,

Excel proper data set, field names at the

top, records and rows, empty cells all the way around. We talked about raw data. We defined some data analysis

and business intelligence terms. We talked about keyboards. We talked about the

structure of Excel. We talked about the

two things Excel does– calculations and data analysis. We talked about our

goal in this class– developing effective and

efficient solutions in Excel. And we started off the class

talking about the version of Excel we’re using. This is Professional 2016. All right, last thing. Let’s come down to the

scroll arrows and right click to open up the dialog

box to navigate through all of our sheets. And we want to scroll down. And I want you to click on the

Homework sheet and click OK. So at the end of

each workbook, there will be homework

problems for you to do. You click on the blue one,

read the yellow cells, complete the problem. The red ones have the answers. But don’t you dare look at

them until you’ve completed. So in this chapter, there’s

six homework problems. All right, now next

video, we’ll talk about more Excel fundamentals. We’ll talk about

formulas and functions. All right, we’ll

see you next video.

I loved your lessons! Could you please recommend a video of yours where I can learn excel from basics to advanced ?

Mike, sem palavras para seus tutoriais. Você é o cara. Obrigado.

Hi I've only Excel-2010. Can anyone tell me if it is useful to watch this video having 2010 version for data analysis? Would be much appreciated.

On 57:57 when you do "h" it shows hours. However, "m" would show month (1), not minutes. Correct?

Hello all, I'm an MS Excel addict and have no intention for rehabilitation (ha ha). Despite being on disability I love to learn more with Excel since I got a newer computer and using MS Office 2016. Will purchase it through my online college I believe for $70.00 or so and it will be worth every dime. I love your video as it goes through the nitty gritty parts of Excel many, like myself, might not know but will.

I will say efficient and very Precise lessons as always – You are the best – THANKS for this great lesson

Hi Dear Professor, at about 10:39, when combining the first name and last name into one cell, can I use the formula of concatenate? And can you tell me what's the difference between concatenate and =A17&" " B17? Thanks a lot!

Hi Guys, if you like the Excel is Fun videos please make a donation to the channel. Email for the PayPal account is [email protected]

Michael has been making GREAT Excel videos for many many many years. Most of the stuff I know about Excel, I learned from him. I am sure he will appreciate a donation, no matter how small. Give something back to the person who makes you an Excel Pro. Thank You. (Disclaimer: this message come from me personally Michael did not ask me to post this)

I tried downloading the Excel file from the link, but Excel says the file is corrupt and cannot be opened.

Best vdo as usual 🙂 Thanks a lot :):)

49:50 when i preformat the excel to percentage, and type 0.03 to the cell, it still comes up 0.03%. mine doesnt work like your? do you know when it went wrong? thank you

Your videos are amazing thank you so much BTW it seems like the workbook wont download for this video i was able to download most of the other workbooks but not this one

THUMBS UP FOR THE BEST EXCEL TUTORIAL EVER! thank you and keep up with the great work

Dear Excelisfun, First of all i would like to say Thanks for all the work you have done for Excel training through your channel in You tube.The way you teach is amazing.all the videos quality is very good. Thanks a lot…

Hi, can I learn in this course without going through the pre-requisite? I would be fine if I missed only a little information

is it necessary to watch the prerequisite playlist??

Awesome class, i really enjoyed and learnt lot of things. Thank you very much!

You have a big gift and a big heart. Thank you so much for the videos, I am studying diligently! Every time I watch your videos, I pray that God bless you, so I think you will blessed all days of your life. I am enjoying the end of my year with excelis fun. Have a wonderful new year with hope, peace and joyful heart!! Please, keep teaching us excel next year.

Amazing!

I just ordered a free month trial of Microsoft Office 365. Is that all I need? You mention this is Excel 2016 "professional". Is there a specific version I should have gotten?

Amazing!!! Thank you!!!!

I learn so many features and function from your videos. It is amazing . I didn't find any video showing how to remove infinite row and column or stop generating further row or column after last cell of the data. Is there any thing to remove or delete ?

Hi, when i am opening this downloaded file in excel 16, showing message"This file is corrupt cannot be opened"

Thats awesome vedio…👍👍

ANY ONE CAN MENTION ME FOR HIS BASICS TUTORIAL ON YOU TUBE , I MEAN FROM SCRATCH

THANK YOU

I haven't used excel before.

Can I learn these tutorials with having to go through the pre-requisite???

Thank you Thank you!!

Thank you very much for sharing your expertise. The BUSN218 tutorials are the VERY BEST resource for anyone wanting to master Excel. Learning excel can be very frustrating, but the tools (video tutorials, excel spreadsheets with detailed notes and multiple homework exercises, and the PDFs with extensively detailed notes) that you provide make learning excel fun. Excel Is Fun. Thank you. Thank you. Thank you

Hi, I think you are the very good teacher. I want to follow your courses but I get lost in all your different videos. your link https://www.youtube.com/playlist?list=PL3FBEE51974F03CCF even get me more confused. Do you think you can make a road map including all of your videos?

1st video done, I find you a excellent teacher and this will help with my new job. Thank you

WOW!! excellent teacher

Jacked for this. Been using excel my entire career (almost 20 years) but am now getting ready to go out on my own in the consulting world. As a CPA, I want to take my excel skills to the next level to provide value-added services for my clients. Super jacked to get started!

Dear Mr. ExcelisFun,

I love you ..

Excel is Fun and Mr. Excel are the best ever this is the truth

you are the freakin BOSS BROTHER <3

Thank you for your videos mister excel. Your videos helped me.

Hello, can you show me how to split the number and text from one cell. ex. 134564Stacysmithpatient. Thanks

Hey Brother,

would sincerely appreciate and like to request to share that how can we lock our raw data and final data from being edited when the Excel reports needs to be in Sharing folders in Company. You have to let everybody open it for obvious purposes, but there is always a thing someone might destroy or edit it.

Appreciate your videos. Thnx if you can help.

I wish all teachers put in efforts that you did. Honestly simply the best training ever. We really really appreciate the efforts and the simplicity you chose. You have a long time student here! God Bless!

Your videos are the requisites to be a good analyst.

Great info I still have a problem with my keyboard function on my macbook air. How can i customise it to execute the same functions as your spreadsheets I have the 2011 version. Pls help

This is awesome! But I have a problem opening the teaching materials. Every time I try to open it, I fail as lack of space. I can create and open my other excel files. I don't know what to do.

You just got a new fan Sir…

Best Excel training/teacher ever…. Thanks so much!

VERY EDUCATIVE. THANK YOU. REGARDS

awesome!

Thank you very much sir for your wonderful and generous knowledge sharing. I have really learned a lot just from this one video. I'm gonna follow through with the rest of the videos

Videos are amazing. I keep hearing you mention a prerequisites class, is there a video associated with that?

You helped me through my internship! THANKS!

At 9.08, where is the BACKSPACE key?

Hey, did you know that MSFT just removed Save As from Excel 2016 in Office 365?

how can i install power- pivot on my Excel 2016 for Mac?

What YouTube channel do you subscribe to, to learn your stuff ?

Do you have a 2016 Excel Advanced Class?

Outstanding. These Excel videos are superb and comprehensive. I've downloaded all of them to improve my Excel skills.

This is the best Excel tutorial I can find!~ kappa

I love it, out of sight!!!!!!

Your content, delivery and exploration of Excel is top-notch. Thank you for putting your time into educating us. I hope your content is monetized handsomely.

Very Nice video. Though I knew most of the stuff, where can I find more advanced excel videos?

Thank you for creating these training videos and for being so thorough.

ExcelIsFun How can we customize our shortkeys? Because in my Excel, Ctr+Home does not select A1 cell. I have to press Ctrl+Arrows multiple times. 🙁

Outstanding! God Bless you

Really appreciate your effort and time on these videos,, excellent work.

Thank you for this great video list!!!very useful and i love it^^!

Thank you

Best Teacher Ever! thank you Sir

Hi Mike.. thanks for all the excellent videos.. learning a lot from you. In a recent newsletter from Mynda Treacy, she made note that Power Pivot and Power Map are now available in all SKUs of Office 365 and Excel 2016. Here is a Microsoft link: https://support.office.com/en-us/article/Where-is-Get-Transform-Power-Query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16?ui=en-US&rs=en-US&ad=US&fromAR=1. So, my Home Subscription now allows me to activate the COM add-ins for Power Pivot and Power Map. The only difference from the Pro Plus version is that some of the Big Data connections are not available in the Home Subscription (Oracle, Azure SQL Server, SharePoint, etc.). The link above shows the connections available only on Pro Plus. The good news is that for learning through your courses and videos, the Home Subscription now shows the Diagram View of Relationships and you can write DAX formulas. So, very cool to be able to do that without having to change subscriptions. Just thought you might want to make this known to your students. Thanks again. Wayne Edmondson

So far very much similar to Excel Basics Series!!!

Ctrl + Shift + ~ is magic! (37:25)

Is Ctrl + ; Like the today function? Does the date change everyday?

For the drop down percentage, Alt + H + N + P + Enter

Excellent topics. The revision to page setup was needed. CNFs are fantastic!

I really enjoy every your single excels

. All are brilliant!!!

Excellent video with very useful tips and information

Thank you is just not enough to express my gratitude to this SUPER teacher

Thanks for the videos. They’re great!

Great Excel Training. I thought I knew Excel…

As usual like the video then watch it.

Thanks, I really appreciate your content.

Perfectly good teaching. Works also with MSO Excel 2013; one does not need to upgrade, if cannot for some reason. Thank you, Mike!

you are the best!

This guy is AWESOME!!! Just in the first 10 minutes, I knew this was the guy to learn from…

ANother great tutorial

Excellent. Supportive to learn much more about excel. Much appreciate

great coach excellent of you

I would like to thank you.

Best teacher ever, so clear and makes it all so easy and much fun.

Another thing I would like to ask you: I have Excel Home & Student 2016,

which don't include Power Pivot in the COM Add-ins.

How can i manage the classes that requires that?

Already finished BUSN 216, Can I go on with Busn 218 if I don't have it?

Hoping for your advice, and thanks again.

I start a new job in a couple of months looking forward to work through these videos to brush up on my Excel skills

You sir are changing countless lives for the better with your tutorials. Thank you so much.

A Gem! Love it. Thank you!!!!

Regards, Passions and Prayers for such a Nice, Courageous and Kind act of teaching amazingly.

Hi, the links to all videos are not working (Apache/2.2.22 (Ubuntu) Server at people.highline.edu Port 80) is there any reason ?

Sir ! You are really one of the great teachers .

thank you so much

Who is this free? Great work.

Superb information. Thank you.

I tried using the keyboards Alt + = but I get a Windows chime and doesn't do anything. Anyone know why? 🙁

Seriously comprehensive content. So many excellent tips and tricks. Thank you!!!

Amazing Summarize of almost 70% of Basic Videos Playlist! WOW you're really amazing and professional!! As I've said before , thank you can never be enough for you..

Great video! Thank you.

Once again I had to login to thank you! You're an amazing teacher! some time ago I finished with the 25 excel basic videos and I can tell you that I've become a fan from this channel! Thank you so much!