Excel for Data Analytics - Full Course for Beginners

dat nerds welcome to this full course

tutorial on Excel for data analytics

this is the course I wish I would have

had when I first started as a data

analyst you're going to be working right

alongside me as we Master how to use a

spreadsheet starting with the basics of

functions charts and tables working our

way up to our first portfolio project

we'll then shift gears into advanced

features like pivot tables power query

and power pivot ultimately building our

second and final project analyzing real

world data now to master this tool we're

not going to go straight for 11 hours

instead we're going to break it down

into 10 to 20 minute lessons during this

we'll have exercises for you to learn

while doing not just watching followed

by practice problems to reinforce your

newly learned skills now Excel is the

most popular spreadsheet tool in the

world it's estimated to have over 1

billion users that's one in eight people

in the world and for data nerds it's one

of the most popular skills for data

analysts coming only behind SQL oh and

the same can be said for business

analysts in this Tool's popularity truth

be told Excel was one of the only skills

that I knew when I landed my first role

in data analytics but it was able to

handle everything thrown at me and so

I've been cataloging over the years all

of the most important features to

perform data analytics and I compiled it

in this course and this video is for

absolute beginners you don't need any

analytic or spreadsheet experience we'll

be starting with the first half on the

basic chapters which will build up your

knowledge on the fundamentals with

covering which versions of excel you can

use for the course along with installing

it then we'll get you familiar with

working around how to manipulate a

spreadsheet from there we'll shift into

practical exercises analyzing data using

formulas and functions and then

visualizing it using common charts and

statistical analysis at the end of the

basics chapters we'll put your skills to

the test to build an interactive

dashboard to predict one salary based on

job and location for the second half of

the course we're going to ramp up our

learnings diving into Advanced

Analytical features focusing on using

pivot tables and add-ins to dive quickly

into Data in sites we'll learn power

query to connect to a variety of data

sets and perform ETL or extract

transform and load finally we'll learn

data modeling with power pivot and

perform Advanced calculations with the

Dax Language by the end of the advanced

chapters we'll have built a full data

analytics project analyzing the data

science job market which you'll be able

to share this and the previous project

in order to Showcase your experience

with analyzing data in Excel now I'm a

big believer in open- sourcing education

so this course and all the content

required to complete the course is

completely free I not only get you set

up with Excel but I also provide all the

different Excel workbooks and sheets

needed to complete this course with this

you'll get access to the data sets

needed to make those final projects and

even how to share them now unfortunately

the AdSense Revenue alone from this

course isn't enough in order to support

all the different costs associated with

building this so I have an option for

those that want to support and help out

for those that purchase my supporter

resources you're you're going to get

access to a lot of features that are

going to help speed up your learning all

provided through this custom dashboard

to track your progress you'll get guided

practice problems to perform after each

lesson that will not only provide the

solution but also walk you through how

to get it if you get stuck along the way

you'll have access to a community of

others in order to jump in and comment

and ask for help additionally you'll be

getting my step-by-step instructions

that walk through each of the lessons as

I perform it and finally when you

complete the course I'll email you a

certificate of completion that you can

upload to LinkedIn now one quick shout

out before we jump in and that's to

Kelly Adams she helped me plan out a lot

of the different lessons for this course

along with being the brains behind a lot

of the different practice problems and

frankly if I didn't have help I probably

couldn't have completed this course so

before we go any further with what we

need to and actually diving into this

course we need to First understand what

is Excel and where the heck it came from

so in order to understand this we need

to go back oh a little too far back

ah just right ancient Babylon when we

used to trade livestock like it was

crypto now it's during this time that we

started recordkeeping and we didn't have

paper so we used Stone and we partition

it into rows and columns during the time

of the Romans they began to perfect this

even further with accounting eventually

we get some advancements in technology

we start getting this on paper this is

when the term spreadsheets gets the

introduction this maintained that

familiar row and column format in order

to catalog different things spread AC

across different sheets spread sheet

fast forward to the 1900s and we pack

rooms full of underpaid people in order

to maintain and keep track of all the

different transactions on paper

spreadsheets with the Advent of

computers in the late '70s we started to

see our first spreadsheet softwares vial

and Lotus 123 then our boy here decided

to revolutionize the world little

bit okay not with that but with this I'm

Bill Gates chairman of my

Microsoft in this video you're going to

see the future since its launch in 1985

it's been wreaking havoc in the

spreadsheet software Community

dominating market share and to continue

to dominate over the years Microsoft has

added more and more features it

initially started out to where you'd

only be using it for the cells of

entering different formulas and forming

quick calculations along with getting

different charts and Analysis shortly

thereafter it was upgraded with pivot

tables and that's my secret weapon to

quickly analyzing data as I no longer

have to remember which comes first and

index and match now VBA or Visual Basic

for applications was included in the

mid90s and it's a programming language

in order for you to automate task in

Microsoft applications now we're not

going to waste any time in this course

learning VBA frankly I feel it's

outdated you should learn python instead

and there's newer tools that actually

automate the process of data analysis

like powerquery this was first

introduced as 2010 and then rebranded to

get and transform and then rebranded

again to power query sort of similar to

what Google does with renaming products

anyway this bad boy is like washing down

a couple caffeine pills with a shot of

espresso it can ingest and clean so much

data in the blink of an eye hardcore

data nerds call this ETL or extract

transform and load power pivot was also

introduced during this time of power

query and it's like putting your

spreadsheets on steroids this allows us

to perform data modeling on data sets

greater than a million rows greater than

what Excel actually holding the

spreadsheets and combined with the power

of Dax or data analysis Expressions we

can supercharge our calculations fast

forward to today and there's been two

other major features added to excel

co-pilot which is basically chat GPT

inside of Microsoft Excel and python

Excel which is basically python inside

of excel anyway co-pilot is great wait

that's a lie so I do believe AI chat

rots are great at helping us out when we

get stuck but I don't want you rely on

that to actually learn this technology

of Excel and for Python and Excel you

need to know well python if you don't

know this yet it's completely useless

now with all these features it can make

it seem like Excel is overwhelming which

I completely get that but when you focus

on the basics and work from there I

think it makes a lot easier to learn it

it's also why this course is almost 11

hours long all right enough with the

history lesson let's actually get into

the course material and what you're

going to need for this also we're going

to be going over what data set or what

data we're going to be analyzing for the

project for this with the link provided

below you can navigate to this which is

the GitHub repo that has all the

different folders and files needed to

take the course now don't understand if

you're not familiar with GitHub we're

going to walk through this this pane

here basically outlines all the

different folders that you have access

to and if I navigate in something like

resources I can see I have a data sets

folder images folder and even a problems

folder so for those that purchase the

course practice problems you have access

to the problems inside of here and

they're broken down by chapter along

with the lesson in addition to that

resources folder you can see numbered

here we have each of those eight

chapters and if we navigate into

something like spreadsheets intro we

have a workbook for each one of the

lessons so you want to download this

file you just navigate to it click the

three dots and click download but have

an alternate method coming up in a bit

inside the workbooks I provide a blank

template for you to go through and

actually fill in and we'll be getting to

what's in this final sheet of actually

being filled in now as we move into the

advanced chapters they're going to have

something like the data sheet or you're

going to use the data from the data

sheets in order to do different

operations and we'll put those in

different sheets as well so how do we

get these files well the easiest way is

to come up here to this code and go to

download zip with the file downloaded

all you need is to unzip it and then

from there it has all the different

folders with the appropriate workbooks

inside of them now after going through a

lesson I then have practice problems for

those that purchase the course perks to

go through here's the course dashboard

that you'll get access to that breaks it

all down for the problems based on the

chapter itself and then by the lesson

and inside of each of these lessons is

multip multiple different problems for

you go through and work the other perk

that you'll receive with those practice

problems are the course notes these

break down the concepts in a similar

format of all the different chapters and

lesson here's the one on Excel install

which is going to be what we're covering

next but it provides all the different

background on all the different material

that be covering this and it's in the

same format that I'm covering it in the

video so you can follow right along just

as a reminder there's no requirement to

purchase these practice problems or

course notes just helps support me

anyway what are we actually going to be

covering in this data analysis that

we're going to be doing inside of excel

well you're going to be taking the role

of a job Seeker in exploring what are

some of the top paying roles along with

skills of data nerds for this we're

going to use the data from my app dat

nerd. Tech that is collected to this

point up to 3 million jobs it tells

based on a job title and also on a

location what are the top skills and it

not only tells us the salary of these

skills for a particular job but also the

salaries of the jobs themselves now the

main data set we're going to be using

for the majority of this course is this

one here inside the data sets folder of

data job salary all this data set

includes over 30,000 job postings from

2023 and it includes a wealth of

information such as company name salary

and location as we go through these

examples I'm going to be doing it from

the perspective of a data analyst which

is their top job in the data set but as

shown here there's a lot of different

other job titles that you can check out

and use as well so feel free to deviate

additionally I'll be primarily focusing

on the United States but there's a lot

of different countries in there as well

so feel free to plug in your home

country and analyze this instead now

with any course you're probably going to

get stuck along the way and so how do

you get help for this well I don't

recommend just jumping into the comment

section and waiting for somebody to help

you out instead I recommend using a chat

bot like chat GPT in it you can provide

whatever era you're seeing and it will

help you out and guide you along the way

on what to do and there's other great

options as well such as gemini or even

Claude so feel free to use whichever one

you're most comfortable with all right

if you haven't done so already it's your

turn now to go in and download that

GitHub repo with all the different

workbooks needed for this course in the

next lesson we're going to be getting

into installing Excel and mainly

understanding what are the different

versions that you can actually get with

Excel and which one you need for the

course with it I'll see you

there let's now actually get into

working with Excel so in this lesson

we're going to be going through how to

actually inst install Excel onto your

computer assuming you don't have it but

before we get to that for those that

maybe have Excel or an older version of

Excel or have different computers we're

going to actually go through what are

the preliminary requirements you need to

have or set up in order to be able to

have the Excel you need for this

course now here's a breakdown of the

different chapters within this course

that is the rows here and then for the

columns are the different micro Micosoft

products that you can get in order to

have Excel now if you're running Excel

on a Windows machine either through

Microsoft 365 Microsoft Office at home

and student or even an older version of

excel up to about

2010 you're going to be fine with

completing all the different course

content however if you have the Mac

version or Mac operating system and

Excel is installed directly on that

operating system you're not going to be

able to complete the Advanced chapter

specifically on power query and on power

pivot along with the project and it's

similar as well for Microsoft 365 online

as you won't also be able to complete

the Advanced Data analysis section now

if you have any of these first three

versions of excel installed on your

computer you can skip to the next lesson

if you want I'm just be going through

before the install process of breaking

down each of these different versions so

you understand your options what you can

get so let's get into breaking down all

these different versions available first

up is Microsoft

365 now with Microsoft 365 you're going

to get a host of different Microsoft

applications not only Excel but also

things like word PowerPoint and even

Outlook and there's two major plans I'm

going to recommend for this either the

family plan which allows you to give out

these keys for these different services

to up to six people or a personal plan

which allows you to give it to well

yourself now I do want to call out that

if you're a college student or maybe you

work for a big Corporation you may have

access to a free Microsoft 365 plan so

if you're in college check with your

college and if you're working for a

business check for your business if you

have access to this so you don't have to

pay money for it but regardless of that

if money is an issue Microsoft 365

family offers this free one-month trial

which I think you can complete this

course within a month so technically you

could do this for free if you don't want

to get charged you will need to actually

cancel before the end of that 30 days

and at that point you'll still have

Microsoft Excel installed on your

computer just everything will be in view

only mode you won't actually be able to

edit any of the different spreadsheets

that we've operated on during this

course let's now move into Microsoft

Office home and

student now this bad boy is the

alternate recommendation I'm going to

give you if you don't want to pay for a

Microsoft 3 365 subscription this is

only a onetime purchase and it gives you

keys to Microsoft Office so you can

install all the different Microsoft

products of excel word and PowerPoint

onto your computer for the low low price

of $150 similar to Microsoft 365

subscription this will not only work on

a Windows machine but it will also work

on a Mac machine Let's now move to this

last option because it's sort of in the

bundle of it of Microsoft 365 online

now this version of Microsoft 365 is

completely free but sort of a catch to

this here I am on my web browser logged

into Microsoft 365 online and I have

access to all the different apps within

the browser including something like

Excel so we can go to it now this

version looks very similar to the

version that you can actually install

the applications on your Windows or Mac

machine there are limitations like a

disuss before about power query and

power pivot so you're going to be

limited if you're trying to follow along

in this course when we get to those

Advanced chapters also the layout on the

web browser version of this app is much

different from that that's installing

your computer so I'm not going to be

providing any support on this course on

actually actually how to navigate this

so you're going to have to figure that

out yourself so we've discussed

everything except for these Mac versions

of Microsoft 365 and office so here's a

quick recap of all the different

features and cost of the three major

versions of Microsoft that you can get

in order to get Excel on your computer

for this personally I'm using the

Microsoft 365 family plan because it

includes all the different features that

I need and it also I save cost because

I'm splitting with my brother who now

that I think of it is actually paying

for it but it provides everything that I

need and so it's the one I'm

recommending for this

course now before we get into the

install I want to briefly show what are

the differences between using Mac with

Excel installed Vice windows and Excel

installed on it anyway here's Excel

installed on my Windows operating system

and Excel on this operating system is in

my opinion the flagship product from

Microsoft so they're investing all of

their effort and resources into

designing this application to make it

the best possible and then from there

Excel online and then Excel for Mac are

really just copycats of this anyway the

two main differences and the problems

I've run into in the past that Excel for

Mac doesn't have are in this data tab I

have a lot of different data sources I

can choose from and that's specifically

related to our power query lesson and

then finally it has power pivot which is

just completely non-existent on Excel

for Mac now here I am on a Mac machine

and we can see that it looks very

similar to before but there's a lot of

limitations that we're going to find

with this specifically going back to

that power query not a lot of different

sources you can choose from and then

yeah Power pivot is just completely

non-existent you may be like Luke I have

a Mac machine what do I need to do in

order to have the most premier version

of Excel and use for this well for that

I recommend installing a virtual machine

and virtual machines like parallels

shown here allows you to host a

different operating system on your Mac

machine this Windows example that I was

showing earlier if I actually expanded

out you can see in the background here

I'm running this on a Mac machine and I

have full capabilities en able to carry

out and running Windows on this now I've

been paying for and using parallels over

the past 3 years and I can tell you the

support and the offers from it are

perfectly fine and I love using it now

personally I'm using the Parallels

Desktop Pro Edition but you can get by

with just using the standard edition now

they also have this onetime purchase

that you could do which is 129 but it

doesn't get any further updates and I

really like how it actually updates and

fixes any bugs that may run into now the

other reason why I like parallels is

because it has this coherence mode I

have this blue little icon that I can

click up at the top to go into coherence

mode and then wait for it it allows me

to access any of those windows inside of

my windows vers virtual machine inside

of Mac so here is Excel running right

here inside my Mac and this is not only

limited to Microsoft Excel but also

products like powerbi which I'm using

pretty frequently as a data analyst I

can also run this into coherence mode

but enough about

that now that they got that out of the

way let's actually get into installing

Excel via in your Windows machine or on

your Windows Virtual Machine so the

first thing we need to do is navigate

over to

microsoft.com and I'm going to click up

here to Microsoft 3 365 we're going to

be going through setting up the free

30-day version so I'm going to click

this of try for free and from there

start my one month trial it's going to

ask me to sync my data I'm assume you

don't have it I'm also going to assume

you don't have an account so we're going

to create one I'm going to put in my

email

address and then from there create a

password after providing some personal

information you're going to need to

verify your email with the code they

send you now to be clear this is the

Microsoft 365 family plan which after

that 1 month trial it's going to be

charging you at

$99 every year so if you're just one

person and you're trying to switch to

the personal plane after this you'll

need to do that at the end or near the

end of those 30 days from there like any

company they're going to ask for some

payment methods I'm going to just go

ahead with PayPal PayPal's all set go

ahead and do more paperwork of adding

Bell and address and with that I can

start trial and pay later so now that

I'm logged in I want to install the

desktop app so it gives me access to

right here it's going to go ahead and

begin this it's going to ask if want to

allow this app to make changes to your

device yeah I trust them so only took a

few minutes and all the different

Microsoft 365 office apps were installed

so I just come down to the search bar

down here type in Excel let's pop it

open make sure it's working and in order

to get started you need to sign in in

order to verify that it's your

subscription so I put in my email and

password and already forgot my

password now I'm resetting my password

and now I'm all set up all right and we

got agre to some lawyer talk of

accepting licensing agreements at this

point I'm pretty worn out of going

through this process so I'm just going

to click through everything I'm not

going to send any optional data

personally I don't like to do that I

don't want to personalize right now and

it looks like I'm finally done all right

I'm into it and now that we're into

Excel we can see up here it should have

your name or your account that you're

going into and go in here into the blank

workbook all right so that basically

concludes this lesson on installing

Excel I do want to show real quick how

easy it is to actually cancel your

membership should you want to go about

just getting the free version or the

free 30-day trial and you want to cancel

it before any if I go back to my account

I can go in here to manage

subscriptions and here I'm inside my

Microsoft account which tells me I'm

subscribed to Microsoft 365 family I can

share it with up to zero to five people

and for that I just click on it and I

can copy a link and provide it to

whoever I want to share it with we're

going to cancel it so we can go to

manage subscriptions right here and all

we got to do is click cancel

subscriptions it's going to have me

confirm that I do want to cancel this

family plan makes me scroll all the way

to the bottom after showing me all these

different prices that I could get

instead and I'm going to say yeah I

don't want my subscription and as I'm

filming this on August 27th it basically

says hey you still have access this for

30 days until September 26th so still

technically have access to it so if you

haven't done it already it's your turn

to now go and install Microsoft Excel

the one of the options that I've shown

here in the next chapter we're going to

get into a spreadsheets intro to get you

familiar with how to actually use all

the different functionality or graphical

unit or interface gooey of excel with

that see you in the next

one welcome to this chapter on an intro

to spreadsheets and this chapter has

three different lessons in order to

understand what we're covering those

three different lessons we need to

explore some vocabulary with it so let's

jump into Excel for this lesson we're

going to be focusing on worksheets and

that is basically as you can see this

tab here called sheet one that is how to

manipulate these different cells within

this worksheet or also known as a sheet

in the next lesson we're going to be

going into workbooks so workbooks

basically captures either one sheet like

this one sheet one if I add another one

sheet two so it encapsulates multiple

different sheets within this program of

Excel and then finally in the third

lesson of this chapter we're going to be

moving into the ribbon which is up here

at the top and has a bunch of different

functionality to extend into those

spreadsheets along with using this file

tab up here that has a whole bunch of

features within it as well now this

chapter was designed for those that may

not have experience with using Microsoft

Excel before so if you don't fall in

that category as in you've used excel in

your job and you're pretty familiar with

all those different features I just

shown you can feel free to skip this

chapter and then move into the next one

on functions along with all those

different practice problems but if

you're not comfortable with that stick

around we're going to get into

it all right so the first thing you need

to do is open up that first Excel sheet

in the files you should have downloaded

from GitHub on onecore worksheets inside

of here I have an original sheet that

allows you to actually go in and fill in

everything we're going to be doing and

manipulating during the course of this

lesson then if you get lost along the

way or want to peek ahead to see what

we're actually going to do you can

actually scroll over here or select the

final sheet to see that now I want to

make this as big as possible for you to

see so I'm going to go ahead and close

out this ribbon up here and you can just

do that by double clicking on any one of

these different items up here and then

from there I also want to zoom in so I'm

going to come down here to the bottom

right and I'm going to just zoom in to

about 200% and scroll on over now inside

the spreadsheet it has all these

different cells and it's organized in a

manner where it has rows and the rows

are labeled with numbers 1 2 3 all the

way down to about a million and then we

have the columns and the columns are

alphabetical and they all go all the way

to where they start duplicating where

they'll put another letter in front of

the other and it'll go all the way

through xfd so let's practice some data

entry here I have a table we're going to

be filling in for this lesson basically

has all the different skills associated

with it and then I want you to actually

go through while we're going through

this and you don't have to provide the

values I do you can if you want we're

going to be filling it in based on our

difficulty when we made have started it

or level and then filling out some other

self formulas as we go so we're going to

start first with Excel and then the

difficulty so I'm going to select right

here and I can see which cell is

selected because it's sort of

highlighted here on this B and also two

but also right up here next to this

formula bar I just call that formula bar

we can see that we're calling out the

name of B2 so anytime we reference any

cells it first references the column

letter and then the row number so in

this case I'm selected in C7 so I'm

going to go ahead and give this a number

I'm going to say four for myself as you

notice I I just put it right in the Box

alternatively I can also select the cell

I want to go to and then come up here

into the formula bar press what I want

so I want five for Python and go from

there whenever I press enter it then

goes down to the next cell so

technically I could just go through and

enter this all in using my keyboard and

I don't have to click or move manipulate

at all except to select the cell that

wanted so those were all numerical

values when we move into the skill known

on whether we know it or not we want to

put in whether it's known or not we want

to put true or false this is known as a

Boolean value so typing in something

like true I can see when I press enter

it actually updates to be all caps for

this Tru so it recognizes the data type

of this as Boolean now if you're taking

this course you probably don't know

Excel so we're going to put in false

instead now say I want to update the

rest of these for false false I can yeah

go through and actually type it up or I

can select this lower right hand corner

of cell C2 and now I can drag these

values down and it will autofill it in

now autofills not just limited to

Boolean values let's say I had something

like Luke I could put that here and just

drag it down it's going to fill in Luke

all the way through here a cool feature

about Excel is say I have something like

one and then two I could select both of

these cells and then when I drag it down

it's going to actually fill in three or

four now autofill can also throw you off

especially for dates so let's say we're

filling in when we're starting Excel

which is we'll put in for the today's

date in my case it's August 27

20124 I'm going to go ahead press enter

to save that in it automatically updates

to this formatting here in America if in

Europe you may see the month in a

different location anyway if I select

this and actually drag down what you'll

see is is it will do that auto fill in

but it's not going to keep that same day

per it assumes we want to increment by

one day now specifically with dates if I

want to change the format I can actually

come up here and I'll expand out this

home ribbon again and right now it's Rec

recognizing that the number is of date

and for date I have a few different

options I can do short date which is

shown here or even something like long

date I can also go even further which

we'll explore as we get further into

this course into this more number

formats and date actually has a whole

bunch of other different options that we

can choose from but for right now we're

just going to keep it this simple date

format and I'm going to click okay now

assuming you haven't started any of

these I'm going to go ahead and actually

just select all the different cells that

I want and if you were to press delete

it's only going to delete that top cell

and that's sort of annoying because I

want to delete all these different cells

instead what I'm going to do if I'm on a

Windows machine I'm G to press delete or

in my case I'm using a Mac Windows VM

I'm press function delete and it's going

to delete all the different content

right I'm also going to go ahead while

I'm here delete all that different

content down there we don't need it now

we're going to move on to level type of

diet we're going to put into this is

text so in the case of excel you're

probably a beginner so I'll put in

beginner and then if I want to I can go

through and fill out different levels

for each of these so python Advanced RBI

Advanced and so on for all these now one

thing to notice real quick is for the

date it does specify in here under this

home ribbon that it is a date but all

these other one it just characterizes as

general which is perfectly fine now for

these other options down here let's go

ahead and say I wanted to put in

beginner for all the rest of these can't

necessarily drag and drop this but what

I can do is I can actually copy it

specifically I could right click the

cell and come up here and copy it but I

don't recommend that also over here on

the home menu they have an option as

well to copy or even cut something so I

can select something like copy as well

and it's going to put these marching

ants as they call it around the cell to

tell you that hey it's actually selected

and then if I wanted to paste it I go

ahead and select down here and I could

paste it down below that's not what we

want to do I don't like going through

and actually selecting all these

different buttons I want to minimize it

as much as possible and I want to use

shortcuts so in order to stop these

marching ants I can go ahead and press

escape and I'll select the cell that I

want to copy and from there I'll press

contrl C and that copies it and then I

can go ahead and paste it below by

selecting the cell that I want and

pressing control contr V now you'll be

noticing that when I'm going through

this I have these shortcuts peing right

here next to me on the screen so you'll

be able to follow along as well as I'm

using these shortcuts the other option

is I could cut this so I could press crl

X and then paste it in here crl V but

this is going to go ahead and take this

value out of here we don't want to

necessarily do that so I'll just copy

this again crl C and then paste it right

above here contrl V shortcuts are going

to be a big timesaver and we're going to

be using them a lot throughout this

course in order to save you time and

having you to go back to your mouse in

order to manipulate it and select the

different

cells all right so let's step this up a

notch and we're now going to get into

using formulas and formulas are denoted

by whenever we go into a cell like

difficulty here which we want it to be

on a 1 to 10 scale we denote formulas by

an equal sign and in this case we want

the difficulty to be on a 10-point scale

basically transition from that 5 point

scale so we need to multiply it times

two so we could do something like 4 * 2

and I press enter and it's going to give

me as I expect eight but I actually

don't recommend hardcoding values that

are already inside of excel here

specifically this four so instead of

this I'm going to remove this and I can

either type in the cell coordinates of

the cell so I could type in

B2 and as you notice it's highlighting

one the B2 is blue but then the cell B2

is highlighted in blue alternatively I

can have an equal sign here and just go

over and actually select it as well

whenever I press enter it's going to go

ahead and say a it's four now now that

I'm referencing that four I want to say

that this is 4 * 2 pressing enter we

have 8 once again we're going to use

that power of autofill so I can select

that cell of F2 and now drag it down and

what's going to be pretty interesting

about this is the two as denoted in the

formula bar and actually whenever I

click into it as well the two Remains

the Same but autofill automatically

knows to adjust the formula or the cell

coordinates for the next cell Down based

on how I did that autofill just to show

this as well I could say hey let's equal

this to B6 right below it and then if I

were to drag this over it's going going

to then put in C6 D6 E6 then F6 so

pretty cool I'm going go ahead and

delete this now the last column we're

going to be filling in is skill and

level we're also be using a formula for

this and we'll set this equal to this

skill thing and also this level so I'll

start by putting in an equal sign and

then it's not on the screen right now

but I know it's in b or sorry A2 and I

can see that selected by scrolling over

here now how am I going to get in that

F2 well I can do an Amper sand now and

from there I'll put in F2 and it has

this selected as well pressing enter

ended up in the wrong one sorry about

that should have been E2 and now I have

Excel beginner but there's no space in

between there this is sort of hard to

read so what I can do is actually

manipulate this to include another Amper

sand and then in between this I'm going

to put quotes and this is hey insert

this text character in between it

specifically I want to have a space then

a dash and then another space and then

press enter now if I tried to do this

without the quote if I just did this and

press enter I'm going to get a typo in

my formula you have to actually put

those quotes around to show that it's

text and it's trying to correct it for

some minus sign I don't really like how

it's doing it oh my gosh it's freaking

out now anyway I put the quotes back in

there pressing enter boom we have it and

like before I'm going to just do

autofill to fill all those

in so let's zoom out a little bit cuz

we're going to be now be working with

ranges which is a collection of cells

now if you notice whenever I select in

this case I'm selecting B2 it says B2 up

the top but if I go to select more of

this it will actually call out that five

r or five rows by two c or two columns

and then when I Let Go it just goes back

to B2 anyway ranges are a selection of

multiple of cells so if I come over here

to i1 put it in equal sign and then if I

want to say copy this entire range I can

go ahead and select this all so it's

saying it's A1 colon G6 so start the

upper left hand corner of A1 and the

bottom right hand corner of G6 now this

is pretty cool there's a new feature of

excel of dynamic rages it's going to go

ahead and fill this in there's only one

formula in here of that A1 through j6

but you see that has this Shadow border

around here that's showing that this

dynamic range is now filling in for all

these different things and if we look at

the formula bar it's sort of gray out

here too for it only at the very

beginning does it show that A1 and G6

and then you could manipulate it so if I

wanted to I could change it to G5 and it

would just go down a row now we're not

limited to just that we could in fact

select an entire column so in this case

I'll put an equal sign and let's say I

want to do the the full column of column

a right here I can select up here a it's

going to select all the way down and if

we go over to the formula bar itself we

can see that it's saying a colon a that

means all the contents of column A are

going to be included in this and from

there it's putting a copy putting all

these different things and then when

there's not a value in it because it's a

copy similar to over here for these

dates of zero we're going to see Zero in

all these different values all the way

down now similarly I can also do a copy

of a row so in this case if I wanted to

or multiple rows if I wanted to do rows

five and six I could press enter going

to get an erir with this though and that

has to do with this Q column right here

that we're copy and pasting here so I'm

going to go ahead and delete that real

quick get rid of it and now we have that

rows five and six duplicated below along

with that shadow around it and all there

now these ranges are going to save us a

lot of time later so I'm going to go

ahead and delete this right now I don't

want any of that as later on when we get

into actually using functions within

formulas I can use something like the

average function put in a range in here

so it selects all of it and then get the

average of it in this case now one last

thing to note on this before we wrap up

here on how to save this is you may have

noticed that this date started over here

is a number and that's because that's

how Excel stores dates with within this

spreadsheet right here so if I actually

click on it go back up to home right now

it's St storing it under the format of

General right now so if I were to make

this into an actual date we can see that

it is in fact 827 2024 now just some fun

little trivia if I were to put in number

one and transition it to a date so

coming up here and selecting date that

first date starts at January 1st 19900

and then they move on the numbers from

there all right last thing we need to do

is now save the work that you just

completed with this you can do this

multiple different ways we can come up

here to the top of your Excel workbook

right here and click save you can also

as shown you can use contrs

alternatively you can come over here to

the file menu and then come on down to

save or save as and then if you wanted

to you can specify the location where

you actually want to save your file and

save it there now you do have the option

which I highly recommend if you're

working with real world files you want

to actually save them to save this

autosave feature the one caveat to this

is that your files have to be stored on

one drive right now with the plan that I

have I can store about one terabyte of

files on there so if you'd like to do

that feel free to transition your files

there I'm not going to um and I won't

have Auto saave on for this but for very

important files definitely do have

autosave set up all right for those that

have purchased the practice problems and

notes you have some practice problems to

go through and get even more familiar

with manipulating cells inside of a

spreadsheet after that we're going to be

going into manipulating a workbook with

that see you in the next

one all right we're going to be

continuing on with this spreadsheets

intro focusing now on workbooks so

previously we were focusing on

worksheets which are a sheet inside of a

workbook now we're going to be focusing

on manipulating and moving data between

workbooks now for this I don't want you

immediately jumping into that 2or

workbooks Excel file this really just

has all the answers in it it doesn't

have really what we need for it instead

we're going to be starting with a new

notebook and instead importing in some

data so specifically if we go into this

folder of zore resource

into data sets we have this one Excel

file called Data job salary monthly now

this is similar to the data that we're

going to be using for the remainder of

the course we're actually going to use

another Excel sheet but this one here is

pretty neat because it's broken up by

months into different sheets so all the

job postings for January are in this

sheet called Jan and so on for February

and so on for March so what we're going

to be doing in this lesson is moving we

want to just evaluate the January data

move that into a new workbook so to get

a new workbook as easy as possible we're

going to come over here to the file menu

I'm just going go to new and click blank

workbook now here I have that new Bo

notebook right now it's titled book two

because it hasn't been saved anyway

going back to that file menu just to

show you I have different options I can

get a new notebook so we went into new

and just selected uh a blank workbook

also we could use this Home tab and

select a bank blank workbook based on

that also have a bunch of different

tutorials you can check out also we have

this open tab right here which allows

you on the left hand side to select a

location like this PC or even browse

different locations in your file system

but frankly I'm using more often than

not over here on the right hand side

this right here where this shows a past

history of Excel files I've worked with

so I can go through and actually select

an Excel file pretty easily we're going

to explore more about this file menu

more in a bit let's get moving some data

first now before we get into copying

this data into the new workbook itself I

want to actually just copy it within its

own workbook so if we noce some controls

down here at the bottom we have all the

different Sheets if we want to add

another sheet which I want to copy it to

I'm just going to add this in right here

and I'm going to call this Jan copy

press enter and that's new sheet and I

and I added that by just double clicking

in there and then allowing it to addit

addition I can rightclick it and I can

do things like rename it and that will

do the same thing now there's also some

controls around here you notice there's

some arrows on right here and what that

does is just Scrolls all the way over or

incrementally over so I can see all the

different sheets in this case there's

more sheets than I'd actually see in one

view then we have the scroll bar over on

the right hand side this is actually

just controlling the scroll area within

our new sheet of Jan copy so previously

we we saw how we can copy ranges using a

formula in this case I'm entering equal

to and then I'm just going to select

this range right here press enter and I

can get it inserted in and then actually

looking at the formula it's just equal

to

J1 uh colon p8 and this has its range

right there all right so I want to get

the contents into this sheet so I'm

going to start by putting an equal sign

and then I'm I go over to that Jan sheet

and when I go over here you're going to

notice that now next to that equal sign

I have Jan the name of the sheet and an

exclamation point this is identifying

the sheet and I want all this different

items so as I go to select it all you

can see that it's updating in the

formula bar right now I have A1 through

P2 selected but I actually want to

select everything in this sheet and

we're about at 3,000 rows and right now

I'm only about 500 of those this is

going to take forever so I don't

recommend necessarily doing this type of

method to try to select all your data so

I'm going to go ahead and Escape out of

this and go back to where we were at the

Gen copy instead once again I'm going to

press that equal sign go back to that

Jan sheet right up in the form bar once

again I can see that it has the Jan and

the exclamation point I'm going to

select A1 to start with and I'm going to

press the shortcut contrl shift and then

the right arrow key and now all the top

row is selected from here I'm going to

continue to hold control shift and press

control shift down and it's going to

select all the different arrows so as we

can see up here A1 to P 3103 scrolling

down we don't have any more data now all

I have to do is press enter and I did

this to basically show the nomenclature

now so now we're not only selecting a

range but we're also selecting a range

from a different sheet and this is how

Excel does the nclat or the formula

necessary to make this work and once

again this is a dynamic range appearing

inside of here but we really want to put

it inside of here into this new workbook

so what I'm going to do is I'm going to

actually delete this sheet right here

because we don't need this copy sheet in

here I don't want to actually manipulate

my data at all going to right click it

and select delete it's going to prompt

me any time that hey you're going to

permanently delete a sheet do you want

to continue yeah I want to continue now

once again I'm going to go back to that

original blank sheet that we have I want

to put it into here so I'm actually

going to name this one Jan and then

we'll call this one formula CU

technically it was a formula not a copy

I don't know why I did copy before

anyway back into A1 once again I'll

press that equal sign and then going

back to that other workbook I will

select it the first cell in there which

is actually A1 and now we can see we

have in the formul of the bar which is

actually the front of the bar which is

sort of strange in the other sheet that

our other workbook that we work with we

have inside of brackets the Excel file

name the sheet that we're in and then

the actual uh cell range of A1 we have

dollar signs around this this locks the

references of it which we're going to go

into more detail on but the main thing

to understand is this has A1 selector

right now but we want to select all this

data so that shortcut of control shift

right select all the different columns

and then control shift down okay it's

all selected I'm going to go ahead and

press enter and it's going to take me

back to my original workbook that I was

trying to work with this now that was

using formulas to copy this data we're

going to explore two more options the

second one is going to be somewhat

familiar using copy and paste so I'm

going to create this new sheet I'm going

to call it Jan copy and

paste from here I'm going to go back to

our original data that we have and since

we're at the bottom of the sheet I'm

just going to select the bottom right

hand corner press control shift left now

if you noticed it went and stopped

stopped at this Blank cell right here

which isn't a big deal I'll press it one

more time it'll go to the next cell over

that actually has a value in it and then

once again it's going to go all the way

to the end of a

3103 so basically if there's any blanks

while you're trying to do this it's

going to stop at those values there okay

and then from there I'm going to press

control shift up and as we're saying

it's going to stop at every different

Blank cell along the way this is going

to take forever unfortunately I don't

recommend you actually do that ever

again

instead start up at the top left and do

the control shift over to the right and

then all the way down in order to select

all the cells now like we did before we

want to copy it I could either use this

up at the top in the home ribbon right

here I could actually select copy or the

shortcut which I'm going to recommend of

contrl c and from there going back into

our new workbook selecting cell A1 and

then using contrl V and pasting all this

data in now moving on to the third

example which is is actually the one I

recommend you do anytime you need to

move sheets of data basically in both of

those previous approaches you could go

about missing getting data to move over

so I don't really recommend doing that

instead I would come down here to the

Jan sheet write click it and select move

or copy so we have this new window that

pops up and it has two book right now it

has this Excel sheet selected of data

job salary monthly we don't want to move

to that we want to move to book two we

also move to a new book but book two is

open that's what we've been working in

that's what we're going move to okay we

can see we have the different sheets

that we've already made in there and it

says in this dialogue this is where you

want to put this before this sheet and

we want at the end so we'll select move

to end now we don't want to take this

sheet Jan out of here we just want a

copy of it so we're going to select this

create a copy and then click okay now JN

has moved over here but I do want to

actually differentiate this so I'm going

to

put mover

copy now in the next lesson we're going

to be exploring more about the ribbon

but we're going to be exploring now more

about the file menu or also known as

backstage view we've gone through this

home new and open we also have this here

for share this is available for well if

you're sharing it via one drive this

makes it super easy to share with your

co-workers we're not going to go into a

lot of detail but this is a great option

if you're working in one drive and you

want to actually collaborate with other

co-workers you can work on Excel files

at the same time moving down to the list

here we also have get add-ins and we're

going to be actually looking at

different addins we can use in the

advanced chapters whenever we get to

that so we working with some addins with

that next up is info which has over here

on the right hand side some key metadata

about our Excel file itself then if we

want get into actually protecting our

workbook which we're going to cover in a

few chapters down the road you can get

into actually doing that the only other

thing that I find myself doing from time

to time in this section is on version

history once again this requires you to

be using one drive for it but you could

go back and revert back into a previous

version that you work with so it's great

for that now moving into save or even

save as since we haven't saved yes

they're both the same right here I'm

going to go ahead and save this but I

don't want to save this on one drive

personal I'm just going to shave this on

my desktop so I'll come and select

desktop and then I'll name this two

workbooks and save it now Beyond save as

we also have things like print which I

really don't find myself doing that too

often should be sending an electronic

version export if I wanted a pdf version

of something and then finally close as

well same thing as this x up here just a

x out of it and there's two more areas

down here that I want to call out and

that's a count and that allows you to

actually see behind the scenes of what

going on with your Microsoft account and

this is generic to all the different

Microsoft products that you have so not

just Microsoft Excel as you can see from

my information I'm actually inside the

Microsoft 365 Insider program so I get a

lot of access to Insider features get to

experiment with new stuff before any

other people do anyway this is where you

want to come anytime you want to make

sure that you have your Microsoft

products up to dat I have automatic

updates available so even I'm I check to

update now it's going to tell me hey I'm

up to date the other thing to note on

this is the different office themes that

you have on this I'm actually going to

change this right now to use system

settings which on my Mac I use dark

theme so it's going to go to that last

two options are hting down here behind

more I have feedback so if I wanted to

give feedback to this product i'

probably go to something like X or

Twitter instead and then finally options

we'll be getting to options later on in

this but this allows a very much more

advanced features that we can actually

go in and customize using this menu

especially whenever we get into add-ins

we're going to be doing that from here

all right so now you become an expert at

how to manipulate different spreadsheets

or sheets along with manipulating them

between different workbooks in the next

lesson we're going to be going into this

ribbon up here and actually exploring

everything a little bit further and

getting a sneak peek into each one of

these for those that purchase the

practice problems and course notes you

have some practice problems to go

through now and experiment working with

different workbooks with that see you in

the next one where we get into the

ribbon see you

there all right this final lesson of the

spreadsheets intro we're going to be

getting into the ribbon inside of Excel

and better understanding what are all

the different tabs and what are the

capabilities by doing some simple

exercises for this we're going to

continue to be analyzing that January

data set that we worked from the last

Lon and we're going to actually get into

actually performing some data analysis

with it so for this lesson you can open

and use that ribbon menu Excel file

which I have right here and all the data

that we're going to be working with are

that January data is in this data tab

along with all the examples and all the

different tabs but I don't need this I'm

not going to work with this so I'm going

to close this out instead I'm going to

be working off where we left from last

time in that two workbooks where we

actually moved over that January data

set now quick disclaimer for any of

these files that you're opening up if

you're noticing the security warning of

automatic updates of links have been

disabled can go ahead and just enable

the content and then click right here on

do not ask me again for network files

and select yes cuz I want to make it a

trusted document now if you're getting

any of these areas that the file has

been moved renamed or deleted cuz mainly

you have it in a different location of

what I had it here's actually the

address of the file that I'm using I

open it up anyway this is the actual

address of where the file is anyway you

can come down here and select these

three dots on the file in question and

just select change Source go into browse

and then from there inside the actual

file itself select where this is so in

this case it's looking for that data set

file with the data job salary monthly

I'm going to select it select okay and

then it's prompting me now that this

link workbook hasn't been refreshed want

to and go ahead and refresh it and it's

going to update it all right close out

of this now anyway that was all s silly

because I'm going to go ahead and delete

this formula one right here and also

this copy and paste tab right here we

only want to keep the Mover copy which

is the actual sheet that we moved over

that has all the data for this lesson

okay I'm just going to rename the sheet

data so let's dive into this Home tab

and this thing has a lot to do with

formatting the text and how things

appear within the spreadsheet for

example I can select all these top rows

right here so basically A1 all the way

to P1 I can change this font size to

something like 12 for the fill color or

the background color I can change it to

something like a light gray right now it

looks like it's already bold I could

turn it off or turn it back on

inspecting all these different columns I

can see that some of it is hidden

especially here this date column I can

see inside of here this is the actual

value but whenever we actually look look

at it from afar like it it has these

Amper sand signs so double clicking on

the edge of that H column right here it

actually expands out and moves it where

it needs to go you can actually do this

for all the column by just selecting all

of them and then double clicking that

last one and then that expands it all

the way we can see that that last column

is well super long so it has all the

different skills typically these titles

up the top I'd like to maintain centered

so that way I know that it's a title but

I could move it to either side also so I

can move it up or down if I wanted to

but we'll leave it right there in the

center as well getting into the number

formatting itself I can actually go and

select something like job post to date

it's going to select that whole column

if I wanted to I can turn this into a

date so in our case I want to do a short

date now other columns I would want to

format are these salary year average and

also salary hour average so besides just

clicking here I can also just select

that hey I want to use this as an

accounting number format and it's going

to automatically put these decimal

places at the end two decimal places

since we're in the 100 thousands I don't

really care about so I'm actually going

to remove them by saying decrease

decimal I'm going to do that twice now

for something like salary hour average

I'm going to also convert this to a

currency but for these these may have

two decimal places of values included in

it so I'm going to leave it now so for

the Styles and cells portion we're going

to be getting into this more especially

into conditional formatting in the

spreadsheets Advanced chapter and

chapter 4 so we'll save that for then

the next thing I want to do is get into

this editing and this is a pretty

powerful feature we can actually sort

and filter our data if we wanted to so

what I'm going to do is actually select

all these cells from P1 all the way to

A1 and then come in here inside of

editing select sord and filter and apply

this filter so let's actually get into

filtering this data specifically I'm

wanting to investigate

jobs or data analyst jobs in the United

States and specifically full-time jobs

we're going to be looking at the salary

data for this so I want to filter it

down for it so I'm going to select here

I'm going to unclick select all and

select data analyst and now it's going

to filter for all the different data

analyst roles there nothing else that's

not there additionally that job schedule

type I want to be looking at full-time

roles only I don't want to include any

other ones so I'll select fulltime I

want the country I don't want to be

skewed by any other countries I live in

the United States so I'm going to then

select United States and then finally I

only want to look at the salary or the

yearly salary data so I can actually

come over here to the salary rate and

select here I only want to look at the

year data okay so now this has

everything in it that I want we're going

to get to analyzing and visualizing this

in a second before that I want to talk

about two other features addins which we

talked about before on how you access to

the file menu you can get to addin via

this and finally analyze data which in

my opinion isn't that strong of a

feature this tab uses a little bit of

artificial intelligence behind the

scenes for you to investigate so it'll

actually provide you different

visualizations that you could actually

visualize out of your data and or even

you can go as far as asking a question

about maybe you want to see hey the

distribution of salary rate or something

like that all you have to do is come

down here and then insert in the chart

that you want to insert in I'm going to

close out of this now we can see that

we've made this salary distribution um

that we maybe want to visualize overall

though I find that this analyzed data is

pretty hit or miss so I'm not using it

very

often now the insert tab is where I

spend the second most of my time after

the Home tab they conveniently put in

the correct order there's three major

use cases that I'm using out of this in

chapter 4 on the advanced use of spread

sheets we're going to be going into

tables and then in chapter five we're

going to be going into pivot tables but

even closer to that in chapter 3 we're

going to be going all into depth on how

to use these charts but let's get a

sneak peek into this specifically

remember we filtered this table down to

data analyst jobs in the United States

and specifically full-time roles we want

to visualize this salary year average

column so with column M selected I come

up here to recommend charts and it's

going to give me a visualization of some

well recommended charts now there's only

four here I can also select this other

tab up here on all chart and actually

try to see hey what would this look like

maybe in a pie chart or a bar chart

anyway I want this in a histogram which

we're going to go into more detail on

how to read this later what all have to

do is just come in here double click it

it'll insert it in now notice how

whenever this was created we now have

new tabs appear inside of here

specifically with this selected we have

this chart design and format tab if I

select off of it those tabs disappear

and select it again they reappear this

tab allows me to dive in and actually

further customize these visualizations

to how I want them to appear I can even

move them to let's say a new sheet and I

can title this something like histogram

and then move it the charge Stone always

necessarily appear just like that let's

actually do a deeper analysis to see

what are the different job title short

columns available I want to clear all

these different filters on here so I'm

going to come back up here with this one

row selected come into editing sort in

filter and I'm going to say hey clear

all the different filters now selecting

column A going into insert and into

recommended charts it's recommended this

clustered bar chart which is actually

what I want to view so double clicking

on this this provides me a breakdown of

all the different counts of the

different job titles within our our data

set and we can see things like data

scientist engineer and analyst are some

of the highest amount of job postings in

this data set now unlike our histogram

example this actually provides this data

in a pivot table which we're going to be

going into in the pivot table chapter

which allows me to further manipulate

the data so say I want to actually sort

this I could rightclick the values right

here and clict hey sort smallest to

largest and then closing out this pivot

table tab right here I can actually see

what is the highest amount of job

compared to the lowest which is cloud

engineer now there's remaining tabs

we're going to be going and hopefully

rapid fire in order to cover these as I

find I'm using these less frequently

than these other tabs that we previously

talked about the draw tab allows you to

well draw on your spreadsheet so I can

just write on it if I wanted to but I

don't really find myself doing that

except for maybe being I'm building

dashboards besides that use case is

pretty rare if I want to end do this

drawing right here I can come up here

and click undo or I can select contrl Z

and it'll remove it page layout tab is

great if you're having to print out any

data for those co-workers that are

living in the past and don't know how to

accept things digitally you can do

everything from adjusting your page

layout to adjusting the scale that

you're actually viewing things now

personally I find myself more using

these sheet options right here so if I

go to this job count tab right here if I

wanted to I could turn off the grid

lines on here as you can can see it got

white on the background I really like

that now if I wanted to make sure they

had actual grid lines around my table I

come back to the Home tab and for here I

can select borders and from there I want

to put all borders on there so now I

look like I have this table right here

along with my graph super fancy next up

is formulas this is where you need to go

if you can't remember a function that

maybe you want to use if it's a text

function you come in here select

something like text you can scroll

through and actually see even a

description of of the different

functions that are available so in this

case replace it tells you hey replace

this part of a text string with a

different text string depending on what

version of excel you have and the newer

ones you'll have this insert python to

insert python functions and then finally

they have more advanced features with

maintaining and updating and formatting

your different formulas and functions

which we'll be diving to in the next

chapter now besides the home and insert

tab the data tab is the next tab that I

find myself using all the time in

chapter 7 we'll be diving into Power

query and we're going to be focusing

heavily on this getting transform data

and also queries and connections and

then in chapter 8 when we get to power

pivot we're going to be going into

managing our data model with power pivot

in chapter 4 we're going to be going

into this forecasting and we're also

going to be adding in some extra add-ins

that are going to appear in this data

tab now I sort of skipped over the data

types and sort and filter because we've

saw them on the Home tab they're just

conveniently located here in bigger

format for you use also all right this

tab on review is probably the least

likely for me to actually use I can

actually go through and check things

like spelling and add comments or even

protect my sheet besides that I'm not

finding I'm using that this often view

tab is similar to the review Tab and

that I'm using it a little bit more you

can change the format of how you

actually want to view things but mainly

I'm finding myself using this the most

of freeze pains let's say you see I'm

scrolling down here and I don't know

what the job or what the he headers are

right here so going over to this data

tab I can actually come in here to

freeze panes and select freeze top row

or even freeze First Column so in this

case that top row actually stays up

there and I really like it like that now

let's say I want to freeze both the top

row and that First Column there's not

really a selection for that so here's

what you can do you can come over here

to freeze panes and select unfreeze

paines and then select something like a

cell like B2 that means I want

everything above this and to the left of

it to freeze so now when I select freeze

panes this upper or top row is actually

Frozen and then the actual First Column

is Frozen as well all right final tab is

help and I'll be honest I think this is

pretty useless if I get stuck with

anything along the way I'm finding

myself navigating to something like chat

GPT and it's helping me a lot quicker

than trying to navigate through this

help box that it provides and I'm

already getting an error message with

even accessing it so you can see how

often I even use it

then now we've been doing a lot of

manual clicking with using the ribbon

and I think a good resource that goes

with this is shortcuts so if you come

inside of the resources folder we have a

Excel file here called Excel shortcuts

and what this has in it is a list of all

the different shortcuts that I find

myself using anytime I'm inside of excel

so it's worth having all of these I'm

not going to lie committed to memory it

looks like a long list but I'm telling

you by the end of this you're going to

have all of these basically committed to

memory they're going to be timesaver now

although I shed on people that print out

stuff this would be something that I do

recommend actually printing out and

having next to you so that way you can

reference really quickly while going

through this course all right now I know

we move fast through that but we're

really going to be diving into as I

called out during this lesson all of

these different tabs even more as we

advance through all the different

chapters that was more of a sneak peek

into what you're going to be exposed to

coming up in this course all right for

those that purchas the practice problems

you have some problems to go through and

actually experiment more with with the

tabs in the next chapter we're going to

be jumping into functions and also more

specifically formulas order to build

them out and form data analysis on that

data science job posting data set with

that I'll see you in the next

one all right welcome to this chapter on

formulas and functions in this lesson

we're going to be focusing specifically

on going a deep dive and understanding

formulas then in all the follow on

lessons this we're going to spend the

majority of our time working on

functions for that we'll be exploring

the entire function Library focusing on

the key functions within this library

that I find that I'm using time and time

again in data analytics so what are we

going to be doing in this lesson well

we're going to be focusing on a

fictitious data set we're going to keep

it small in order for us to get more

familiar with operating with formulas

and operating on this data set

specifically by the end of this we're

going to be able to input into into this

worksheet a number of years of

experience or total salary and be able

to see whether these jobs meet those

conditions specifically me that I meet

both of those conditions so for this you

can follow along by opening that

formulas intro workbook in this workbook

will be staying in this data sheet right

here all the different answers when we

get to the math operators comparison

operators or cell referencing are shown

via that sheet but we'll just be

sticking for data for

now first as math operators and as shown

by this table here you can use a variety

of different symbols for to conduct

different multiplication subtraction

division operations that you want to do

so let's dive into testing some of these

out we're going to be filling in each of

these columns that correlate with the

associated job title as we go through

this so the first one's going to be

experience pretty simple right we talked

about before in order to reference

another cell we would use an equal sign

and then from there we can either type

or select a cell I'm going to recommend

just typing it to make it go faster C3

it's highlighted blue because that's the

cell that's highlighted then we'll be

using the autofill feature of this to

fill in all the cells below and we

notice that it updates to here this

one's equal to C12 which correlates to

this one right to the left of it so

let's calculate our total salary and

this is going to be taking our annual

salary in column D and adding it to our

bonus Max in column e so we can do this

by specifying

D3 plus E3 and from there there pressing

enter once again to autofill it I select

that cell that I want and drag it on

down now if I want to calculate what is

the rate of bonus or the bonus rate that

is going to be the bonus divided by that

salary so in this case E3 / D3 once

again going to use autofill drag and

drop it all the way down now for all

these values I don't like what it's

formatted as right now I'm actually

going to change this to a percentage and

I want to see one decimal place so I'll

press this one to expand out one now

anytime I do any type of mathematical

operation in Excel I always want to try

to confirm it that it's correct I did

the operation correctly so in the case

of this bonus rate I can do this by

confirming what we got for total salary

previously so if we took that bonus rate

is which we want to confirm right so

we're going to take that and multiply it

times our annual salary right so that

should give us that bonus rate right

there then if we wanted to like we said

we want to confirm total salary right

here so I can just add in that we want

to also add in that annual salary itself

and we do have that total salary right

here to actually confirm what's going on

dragging it down and doing an autofill

all these values look like they

correlate to what it should be for total

salary so I feel we calculate a bonus

rate correctly now going back into the

formula itself you can see we have

multiple operations in here how do we

know whether multiplication addition

subtraction what comes first well really

if you know the order of operations it

really is the same here here the

different operators listed in their

order of Precedence exponentiation comes

first multiplication division or second

then addition and subtraction are third

it's Then followed by concatenation

which we did in one of the previous

lessons followed by the comparison

operators which we're about to get

to so with that segue here we are

comparison operators

for this you probably are familiar with

the first three the last three are

something that get a little bit more

complicated whenever you have a greater

than or equal to less than or equal to

or in this case a not equal to so

previously I just sort of did a cursor

check to make sure this confirmed t

total salary column equals this other

total salary column but imagine you have

hundreds of thousands of rows how can we

actually compare this and find these

values well what we can do is we can say

hey is G3

equal to I3 this looks a little bit

confusing right CU you have two equal

signs in there but everything to the

right of the equal sign it's basically a

comparison and from there it either ends

up as a true or a false and we can drag

and autofile this in and everything is

true similarly if we want to find

something like is the bonus Max greater

than the annual salary we can do hey is

bonus Max at E3 greater than that at D3

and the typical of any data a science

job none of these really exceed that at

all all right now that we're familiar

with math operators and also comparison

operators let's dive deeper into cell

referencing and we've been doing this

previously whenever we reference another

cell like A2 but we're going to add a

little twist to this I'm going to go

ahead and hide some of these columns

that way we clear up the Clutter going

to hide column F by right clicking it

and selecting hide then I'm also going

to select all the columns H through k

and also hide them want everything to

appear on the same sheet so we're going

to be referencing this table down here

for this portion of the exercise and

this is potentially goals that you may

have when you're trying to land a job

you may know how many years of

experience or you should have know how

many years of experience you have along

with a goal total salary that you want

to achieve and so we're going to be

building out formulas with this in order

to be able to find out which of these

jobs actually meet our conditions of the

expected years of experience and total

salary for so for this we'll go with

that I have five years of experience

then I'm looking at

$90,000 the first we want to calculate

in column L is whether it meets our

experience so for this we'll say hey is

C15 right here less than or equal to the

value right here in our experience and

as expected five is less than or equal

to basically equal to 5 it's true now

we're going to run a problem now when we

try to autofill this if I try to

autofill this down I'm getting this one

is false and then these all is true but

I would expect especially this AI

specialist at three it would be false

and so let's actually inspect this well

as we can see from this this is

referencing well c23 which is way down

here but it's still referencing the

correct C11 right here the problem is we

didn't really want this value up here

this C15 to actually change whenever we

went to do the autofill down below it so

what we can do here is provide a fixed

reference of that cell in order to do

this we're going to insert those dollar

signs that we saw

previously before the column and then

also the row so in this case I have C

locked and I have 15 locked now the

formula itself doesn't change at all but

now when I drag and drop this down all

of these are updating correctly as

expected AI specialist is going to be

false whenever I actually click on it to

inspect it it's still referencing that

C15 C11 next we're going to move on to

column M of seeing if it meets our

salary requirements so for this one

we'll be seeing hey is the salary or

total salary in G3 greater than or equal

to our total salary down here of 90,000

now we already know we need to lock c16

of this 990,000 because we're going to

be autofilling it down I can manually

type in the dollar signs but a shortcut

to this is just pressing F4 if you're on

a Mac you'll need to press function F4

anyway this locks this in so now

whenever I drag and drop this down as

expected the only other one that's less

than 990,000 is this data analyst rule

right here now I want to play with this

just a little bit more so we talked

about this right here putting a dollar

sign in front of the column and then a

dollar sign some of the row is a fixed

reference they also have what is called

a mixed reference so I'm going to go

ahead and put my cursor right there next

to G3 I'm going to press F4 and it's

going to do the absolute reference but

if I press it one more time it's going

to do a mix reference if you notice

there's only a dollar sign in front of

the three or if I press it again there's

only a dollar sign in front of the G now

technically this is going to work but

fine because we're going to now lock

this G column for this but it's going to

allow the three to update so I'm going

to show you this now by actually

dragging and dropping this down and from

there inspecting that last cell contents

we can see that that g is locked as

expected but it moved down now instead

of locking just the column we could also

lock the rows so I could also do change

up c16 now instead and lock the rows of

c16 cuz we're going to still stay in

that c column right there pressing enter

now autofill we don't have to just go

down we can also go up so inspecting it

locking it didn't really change by only

locking the row of 16 so let's wrap this

all up by actually def finding out which

of these actually meet both of our

conditions of 5 years and 990,000 well

it turns out that behind the scenes true

is equal to 1 and Z is equal to false so

if actually were to take this and add

this true to this true right here we

should get two autofilling it all the

way down we have two1 2 1 so basically

confirm that hey zero yeah false is zero

because 0 plus 0 is Zer now I recommend

instead we're going to be going through

and doing L3 * M3 so that way anytime

either one of these are true they will

return a one and now in order to get a

true or false back on whether it meets

both we can select that N3 and see hey

is it equal to one type over there equal

to one and it evaluates to true so now

I'm going to go ahead and just hide

these columns so we can actually see

this a little bit better but we can

find values in here that meet our

conditions of the 90,000 or 5 years and

let's say we're doing job searching and

it lasts over a year um we have to

change this to six this will

automatically update the formulas that

we've used here as shown here so that's

our intro to formulas and for me the

hardest thing to wrap my head around

when I was first tackling this was

around absolute and mixed references so

we have some practice problems for those

that purchased the course practice

problems in order to go through and test

this out and understanding what happens

whenever you lock the row or lock the

column all right and after that we'll

next be diving into an intro into

formulas which I'll be covering for the

remainder of this chapter with that see

you in the next

one for this lesson we're going to be

focusing on an intro into functions

specifically we're going to be going

over all the different functions that

we're going to be deep diving within

this chapter itself along with some

common problems you may run into and

errors and how to troubleshoot it to do

this we'll be continuing on from that

data set that we used in the last lesson

specifically we'll be calculating things

like averages and counts and how many

jobs actually meet our goals and we'll

be using functions for this so you can

continue working in that workbook that

you had from last time or open this

function intros workbook in this

function intros workbook I've gone ahead

and moved our job goals over here to

that column RNs and then added in this

bottom portion right here for the

averages and total counts really you can

do and manipulate as you

want so why use functions let's look at

a couple quick examples on the

importance of these things let's say we

wanted to get the average of each one of

these Columns of experience annual

salary and bonus Max previously we know

we can actually reference each one of

these cells to calculate the average we

wanted to do that we would have to

actually add up all the values so I have

to go through select C3 C4 all the way

down to

C12 and we would need to divide it by

that total number of 1 2 3 4 5 6 7 8 9

10 in that case we' get the average also

that me count that 10 wasn't necessarily

perfect so I don't really recommend

doing this but anyway nonetheless we can

actually do autofill to calculate the

averages as the is as well as it

automatically update the referencing

correctly to it but I don't recommend

doing that instead I recommend using

functions specifically we can use

something like the average function as

soon as I start typing a function a in

this case all the functions that have

the a name pop up if I wanted to well I

do know I want average right here I can

select it it provides a brief statement

of what it's actually going to do and

then I can doubleclick it to insert it

below here it actually specifies what's

going on with this function here and

specifically to provides me to hey

provide in these numbers now I could

select these number by number as we can

see that there's in Brackets here this

number two that means it's an optional

parameter but instead what we'll do is

we'll just provide a range providing it

from C3 all the way to C12 in that case

I got 5.3 similar to above and then

dragging this over we can get all the

other values as well as a quick example

also previously we had made this sort of

convoluted formula in order to calculate

calate whether we met both conditions of

mean our experience and also our salary

which we're specified over here well

there's actually a formula for that and

it's called the and formula and what it

takes for its arguments are logical

values so it can take a logical one for

the first parameter I can specify L3 and

then for the second parameter I can

specify M3 and notice how this second

parameter now highlights or becomes more

bold as I put it in so you can keep

track of where you are in the formula

any I'm going to close the parenthesis

press enter and it evaluates to True

dragging it all down these should match

these other ones and yeah this is

definitely something I'd use over these

formulas that I've used

before so let's dive into this formula

tab more and understand the capabilities

that we're going to be carrying out the

next lessons in this chapter the most

powerful of these especially for those

new to excel is this insert function

anytime you're looking for a function

and maybe can't can't recall the name

and you're not sure what even starts

with you can put something in here so

say I wanted maybe the average I can

type in average and then everything that

basically calculates a different average

off of it even if they're closely

related like this rank average will pop

up in here along with a description

below explaining it if you've used a

formula recently you can come in here

under recently used and I frequently

find myself just going back to this in

order to select something I may have

used recently now in the next seven

lessons we're going to be diving into

each one of these all the way through it

from logical and text to look up and

also math and trick now one note we

won't be going into detail on this

financial functions because I find

they're sort of nuanced but we will be

going into all the different ones that

I'm using on a daily basis as a data

analyst that aren't specific to

financial

applications so let's get into

understanding the basics about formulas

by calculating these different counts

and especially counts around whether any

of these jobs meet our goals for this I

know I want to use a count function so

I'm going to go to this insert function

I'm going to type in count now there's a

bunch of different ones that pop up

count itself just counts the number of

cells in a Range that contain numbers it

has to have numbers in it if I wanted to

do something more around text I would

say hey count the number of cells in

range that are not empty I could do even

do something conversely of counting the

number of blank cells for us we want to

actually do count so as we showed before

I'm just going to come in type count

it's going to prompt me that I need to

at least put at minimum a value and I

want to count all these cells here so

using autofill to fill it over um we can

see that all the different values are 10

nothing really spectacular here but now

let's get into a pretty unique use case

of count so in this scenario that I'm

count trying to calculate in cell c16

I'm trying to find out how many jobs

above here in these 10 right here how

many meet our goal of less than or equal

to 5 years and I want to count the

number of these so I know I want a type

of count I can go into insert function I

know it's here inside these different

statistical functions specifically I

have these different counts right here

and I'm going to scroll over this count

if right here and it's going to provide

me a description it says Hey counts the

number of cells Within range that meet

the given condition and that's what we

want to do we want to meet a condition

of a certain amount of experience now it

provides this box in order to help me

input in these values so for the range

here what I can do is specify hey I want

to count inside of here if they meet a

certain criteria and just going back to

that range right here we can see that it

already input all those different values

into an array likee object okay so the

criteria right now is NX want to put

something in here I can also press this

box and it'll make it disappear and I

want to compare it to this experience

but I want it to be less than or equal

to five so I can press enter to accept

it but the problem is it's going to

evaluate whether five is any of these

columns here and right now we see that

there are two I'm going go ahead and

close up so we can see this better right

now we can see that there's two fives in

here that's not what we we want we want

to see everything that is less than or

equal to 5 so instead what we need to

put in here is less than or equal to 5

now I'm going to press enter and we're

going to get an error this is pretty

common whenever you are manipulating

different formulas and you have in this

case I have this less than or equal to

right here so Excel is confused by this

what we need to do is actually put

parentheses around this which basically

sort of makes it into a string or text

if you will but now it knows hey I want

you to look for less than or equal to 5

I want you to evaluate this entire thing

pressing enter bam we have six values

here that are less than or equal to five

now similarly I can drag this over

because we want to also do this for

experience but I don't want to do less

than or equal to five I want to do

greater than or equal to

90,000 and in this case we have nine cuz

we only have have one that's less than

this but as you find out on this course

I don't like hardcoding values into my

formulas in this case I have five inside

of here but I'm already having five

right here what happens if I want to

change this maybe to say something like

three well it's not going to actually

update these values right here so I'm

going to go ahead and actually change

that back to five and we're going to

make another formula that actually fixed

this so I want to drag these down but we

actually didn't lock either one of the

these cells and it will cause errors if

we do so I'll just select right next to

it press F4 next to C3 I'll do the same

of f4 doing the same in this cell as

well all right now I'll take this and

I'll drag this down so now let's

actually fix this to be more Dynamic we

don't want it to be less than or equal

to this five right now what we can do is

that Amper sand operator and then from

there put in reference to S3 which

contains our five pressing enter bam we

got six same thing here I can delete

that 90,000 put in an Amper sand and

then from there we're going to be

basically putting it to mashing it

together with that 90,000 and it

evaluates now when we change this

experience to say something like two we

can see that it actually updates

appropriately to see that oh only one

job meets this requirement so pretty

cool I'm going change that back to

five now frequently you're going to run

into errors with your formulas let's say

I wanted to divide one by zero not a

good thing that we need to do anyway I'm

going to get this error right now you

can notice it because it has this green

check on the upper left hand corner but

also it starts with this hashtag and

it's saying hey you have a divide by

zero error I can even come down into

here and it tells me even more on this

provides help on this or if I wanted to

even ignore it now in this sheet of this

work workbook I have a bunch of

different errors in here that you may

run into from time to time again and

we're going to be running into these

errors as we go through the rest of this

chapter so if you get stuck along the

way while we're going through this I

feel like this is a good reference for

you to maybe save somewhere in order to

understand what is going on with the

different errors you may encounter now

the biggest time saer I've found with

any of these errors is using some sort

of chatbot specifically me I'm going to

go to something like chat GPT or even

claw they're going to be able to provide

really quick help in understanding what

an error is and what I need to do to fix

it all right so now it's your turn to

dive into and test Out These intro into

functions and play with them and

experience some of the errors of your

own after that we'll be diving into

logical functions a major type of

function that you need to be aware of

with that I'll see you in the next

one now that we have the basics down on

formulas and also functions we're going

to be moving into one of the most

important typ of functions to know

logical ones the most popular of these

are an if condition basically looking at

something and then providing a response

based on it so for this analysis we're

going to be jumping into our data

science job salary data set but we're

only going to focus on the first 20 rows

of it here and on the next few lessons

as well as I don't want to overwhelm you

with the all the data just yet now for

the final results we're going to be

doing two major things the first is

determining within this list of jobs

whether they meet our conditions of

finding the job we want of a data

analyst or business analyst and will

Market not desired or Ro desired

additionally we're going to do a common

practice and analytics of bucketing

basically taking those salaries and

depending on the amount value putting it

into a certain bucket for us we're going

to be looking at whether they have

salary data in this data set or more

specifically if they are greater than

our goal of 85,000

so why are these logical functions

needed well let's jump into that last

data set real quick and simplify how we

can actually use these as a quick

example previously in this P column we

were evaluating whether they met both of

our conditions of experience or salary

we can use an if statement in order to

clarify this so I can specifically call

out with an if statement saying if it

has The Logical test that we want to

actually evaluate so I'm going to put in

P3 in this case as it's going to return

true or false and then from there the

next value in there is value if true

which what do we want to return if it is

true well that our goal is met and then

if it's not met we want to have well not

met okay and then this whenever we drag

this down will provide not met or goal

met depending on if this is true or

false and so that's the power of these

if statements in helping us actually

provide this

value so that was just a quick example

of if let's actually jump into some more

examples so you get more familiar with

how to use this so here we are in this

data set and I don't need all the

columns of this data set so I'm just

going to select the columns that I don't

need I'm going select B through G and

then hide it additionally I'm not going

to need I or J so I'll hide these as

well so our first goal is to identify

whether these jobs meter conditions of

either a data analyst or a business

analyst we're going to start simple by

just finding out which one is a data

analyst first and then which one is a

business analyst and meets those

conditions so once again we'll start

with that if condition and for this

we're going to put in that logical test

remember pretty the example we need to

have a return either true or false so

we're wanting to check whether senior

data engineer in A2 is equal to data

analyst in K1 now we're going to be

autofilling this down so we need to make

sure that the A2 we're fine with it

actually adjusting as necessary K1 we

want it to lock at least lock on the row

value of one then if it's true we'll be

roll desired and if it's not it's not

desired as expected senior data engineer

is not desired let's drag this all the

way down and just double checking it we

see that the data analyst roles are R

desired okay so I can drag this over now

and just to double check it shifted over

to B2 but it's still but it's selecting

a right one of L1 so actually what I'm

going to do is I'm going to delete this

go back up here I'm sort of a

perfectionist I'm going to end up

locking that a value so it stays in that

a column none my values are going to

change here and then when I actually

drag it over I can check that okay A2 is

the correct one I once selected to

compare it to business analyst in

L1 okay then I'm going to autofill all

the way down looks like there's only two

business analyst roles here so now how

can we identify that it meets both of

those conditions both data analyst and a

business analyst well we're going to do

one approach first and it's called a

nested if statement and it's not really

the approach I'm going to recommend but

it's something that you should be aware

of so what I'm going to do is I'm going

to select cell K2 I'm going to go ahead

and copy this formula plugging it in

here we have it here and making sure

that it operates correctly yep it does

so how does this nested if statement

work well we're going to still evaluate

our first condition is the first role

evaluated as data analyst does it meet

that if it is we want to mark it as rule

desired now we get into what happens if

it's not a data analyst well now we want

to now check if it's a business analyst

so I'm going to close out this and what

we can do is I'm going to take this

business analyst formula right here

everything up to the if and I'm going to

go back in here and I'm going to drop it

in right here inside of the value if

false so it's an nested if statement an

if inside of another if so now if we

don't meet this first condition of the

value if isn't true it will go into the

nested if statement and start checking

this condition is now the software data

engineer equal to data analyst if it is

it's R desired if not it's not desired

so let's now drag and drop this all the

way down I'm going to expand this out a

little bit and now we can see if it's

data analyst we get rule desired along

now with if it's business analyst also R

desired but I'm not a fan of nessf as

they're hard to read instead I like

using the functions of and and or and

should be a little bit familiar because

we saw it from the intro lessons that we

did previously with and it evaluates

whether both conditions are true so in

this case I'll put in condition one of

B3 and then condition two of C E3 and

both conditions are true so it satisfies

as true dragging this all down in all

the following condition cases they're

not true for both conditions so

therefore it evaluates as false in or it

checks whether condition one or

condition two is true and then will

return true so inputting in the

conditions of B3 and C3 one of the

conditions here are true actually both

are dragging it down I expect yeah the

second and third rows are also true

where the final one both are false so

therefore it is false so let's run the

same Andor logic that we've run before

in order to determine which one we

actually use so in this one we're

checking whether both of these jobs of

data analyst and business analyst are

equal to this one here senior data

engineer as expected false and what

should we should expect for all of these

all of them are false because none of

these are going to be both data analyst

and business analyst so as you can

probably guess or it's probably going to

be the one that's going to work for us

we're evaluating whether either data

anal or business analyst are going to

match up to that value of senior data

engineer in this case we're getting

those tree values for data analyst and

True Values for business analyst so now

we're going to put that or function

inside of that if for The Logical test

and from there we can determine whether

it's rule desired not desired dragging

all down all of it's matching as

expected okay I'm going to go ahead and

hide these

rows so now what happens if we don't

want just a evaluate for a true or false

condition basically we want to evaluate

for multiple different conditions well

that's going to be something that comes

up if you need to ever bucket data which

we're going to be doing with salaries

now for this first one we're going to

just use a simple if statement we want

to determine whether a salary is greater

than 85,000 or if it's not we want to

just specify that the salary is low so

for this we're going to be evaluating if

H2 we're going to go ahead and lock that

H column is greater than that 85,000

which will lock that completely for the

85,000 then we want to say the salary is

greater than 85,000 conversely if it

doesn't meet this we want to say that

the salary is low I'm going to expand

this out a little bit and then we're

going to drag this down as expected we

have the values returning those are

85,000 and then this one at 35,000 it is

Mark is low now the problem we're

running into and why we need multiple

conditions is this is the salary is low

but there's actually no data there we

need specify in these conditions that

well there's no data so for this we can

use an ifs formula and what happens with

this is you provide a test and then a

value if true and that's just the first

one we can then provide another logical

test and the value of true so the first

thing I'm going to test is if there is

no value there I'm going to go ahead and

lock that H column as well and when I'm

looking for a blank I'm just going to

put in two quot Mark say signifying that

it's blank and the value of true is no

data okay put another comma we can see

we now we're on to logical test number

two the next thing we want to test is if

it's greater than 85,000 so we'll see H2

again locking that H and we want send it

if it's greater than or equal to that

85,000 which will lock if it is we want

to return back that salary is greater

than