# Excel Tutorial: Learn Excel in 30 Minutes – Just Right for your New Job Application

Hi! My name is Sali Kaceli and welcome to

Excel in 30 Minutes. This is a concise tutorial on learning Excel even for

anyone that has never used Excel before. To get you started and understand how

Excel worksheet how to use it effectively in business in just 30

minutes. This tutorial is designed to give you the tools necessary in your

technology toolkit for a new job application or for just understanding

how Excel works. This is based on Excel 2016 but the functionality is

gonna work exactly the same in previous versions. So let’s get started. So as soon as you open Excel and you started your spreadsheet you are presented with a

window very similar to this. So in Excel you have the workbooks or spreadsheets

and these are the files very similar to like Microsoft Word was designed to perform calculations

using formulas. The other thing is is that you have here

in the bottom these Tabs. Typically it starts with with only one time and you

keep on adding new ones by simply clicking on this plus near the bottom. Now to rename you can simply right click on them and choose Rename

and you can give its whatever name you want. If you want to delete a specific

Tab of course with caution you can just simply press Delete. Let’s get started on

some of the other concepts as well. Basically you have these columns and

rows these and then you have also these squares here in these are referenced as

the cells. Now this reference right here is referred to as B3 and you take into

consideration the column and where it meets all the row, that’s the reference. Now typically whenever you’re building those formulas for the

calculations you’d be using those references values within the references. That’s one of the key concepts in here. Additionally in each one of those cells

you can insert text or you can insert numbers. And those could be numbers formatted in a variety of ways. They could be general number they could be

currency they could be accounting, or a date or time and percentage and

things of that nature. And use format it from here or it could be a formula as

well for example here you could have D3 you could have 3 could have

five and now in here you want to get the total for those. Well in here you could put a

formula. And by the way all formulas in Excel start with the equal sign. That’s how their computer knows that it’s a formula. And then we put in the

function and then we get those has a total for those values and I will explain thoselater as well. This is an example of a formula within a cell and what does that

equal sign in front of it. And the other thing to remember in Excel is that

if I change one of those values and enter the total be updated automatically

and that’s where Excel the power of Excel comes in. As far as other functionality functionality the tabs here other functionality it’s very similar to

Microsoft Word or other applications from Microsoft Office. So now let’s move

on to another area for you to understand how Excel works and how to get started.

So I have created created these worksheets here in the bottom and now I’m going to

demonstrate how to use formulas to make those calculations here. So one thing to

remember as well as an adjusting these columns you can simply, notice how the

text doesn’t fit in there you can just drag it to the right and make it fit or

you can simply doubleclick and it will adjust accordingly to the widest point in

in your column there. So that’s how you adjust it. Now if I wanted to put a title in

here it could simply start typing and then hit enter the other thing I could

do here is that I could select these bunch cells in here and then I could

use what’s called here merge and center and for you the icon might be a little

bit larger than why there’s because my recording screen size here. Then

choose merge and cent er and now and notice all these cells have been merged

and text has been centered. Now of course you could make this font larger in adjust the properties manually or what you could do is you can go and use one of

those styles cell styles and format this using one of the themes. The next

thing that will learn here is to get you started right away with using Excel is

that we all want to format this cells and you select them and you want to

format them so that they are currency well you can format in currency by using

this drop down here and choosing currency or you could use this dollar

sign icon over here on the other number area in at this point that this has been

formatted as currency. Here on the right hand side we want to do potentially is that we want to

calculate those values and add them up basically get the sum or the total fees

and expenses for January February and March now to calculate it one of the

easiest ways of course would be to use this tool here the the autosum on the top

right and hit Autosum, and then just press Enter and that will give you the total. Notice that you have this toolbar here that

tells you what it’s calculating you need to make sure when you use the AutoSum make sure that it is doing the proper calculation here.

place doing the proper calculation here The other way to do this AutoSumor

to get the some of these numbers is to understand them and do this manually so the manual way is the equal sign. All formulas start of the equal sign and then

in Excel you put in the next thing is typically the function that you want to

perform in Excel. Excel has hundreds and hundreds of functions in here we are going to cover only few of them to just get you started so in this case as we type SUM and then

notice it says some it adds all the numbers in a range of cells so now it’s

it’s expecting you to insert the range The range in the cells here is

expressed by using parentheses and then you can simply either type those

references e.g. C5 we want to start on C5 and all the way to E5 the colon there it represents the range in between now and

the start of C five all the way through E5 or it could be E500 for that matter so it’s

going to calculate everything in between you could type those or you can select

them like I selected them here and you can hit Enter and you could do the same thing if you wanna through this next one as well. SUM and then open parentheses you could even do that c6 colon e6 and close parenthesis and hit Enter or you could drag it like I did earlier. Another method here instead of me spending all day typing these references here are these formulas what you could do is it you can

drag this down from top to bottom top to bottom or left to right

this by the way is referred to as the Autofill feature in Excel. Now you

can use this for anything in sequence and I’m not gonna go into details of the

technicalities already you can check one of my other videos regarding that you

wanted to calculate also the totals for January here. So you do it the same way equals nine so open parentheses and then

drag this thing down hit enter and now we have the total here for this column for January. Now to move it to the right we can apply this also on the right hand

side by using the Autofill Feature. So just drag it here to the bottom when it changes to a plus sign and then let it go and it gives you the total. Now let’s

say we want to get the average of these these values from C4 to C12 so

let’s type here AVERAGE you can either start typing equal sign

and then just type AVERAGE and will show up here Equal AVERAGE or I will show you also a trick how to use those formulas and functions and basically what you do is you go under

formulas here you click on insert function and let’s assume you do not know where Average IS. You say give me a brief description as And tell me what you want to do. Notice it returns the arithmetic mean of arguments

and all that type of thing and then you also have this option here so for any of

the functions in Excel you can always go here Help on this function and it’ll

take it on Microsoft Help is all give your description of it as to how it

works. e.g. equal sign average A1 through A20. So instead of doing the SUM A1:A20, you could do average A1:A20 then it gives us

the syntax how it works and even some examples below that’s one way

of doing it so you could do it from this point here you choose average and then

you’re saying okay I want the average where it starts and then number one

where we want to start, selecting it and then I am doing on clicking OK and notice the average is 154. Notice I could have done that also this way. The Equal sign, Average, open parentheses and then select the range to get

the average for. And notice, I could use the Autofill Feature and get the

average for all the other columns. It works the same way for also using the lowest numbers. So let’s say I want the Minimum. I go here under equal MIN parentheses select

the range of start from top or bottom and then drag it to the right and there it is.

let’s say I want to get the maximum number and I’ll let you do that you

should be able to figure it out by now now let’s move on to something else is

more fun as well so now let’s say we wanted to do some calculations so let’s

say we wanted to get the SUM of a bunch of numbers and know that we have here a

bunch of employees and we want to get their pay figured out. Their total

deductions so this is the amount they pay their deduction 1 and deduction 2 and we want to calculate the total deductions. This is again adding a bunch of numbers

together so it will be equals and then the first number with the next one hit

enter and it gives the totals now this feature here Autofill automatically, that is a feature in 2016 if that doesn’t work for you in

previous versions don’t worry about it you can simply drag it and do it this

way now the next one is to be the net pay how do we calculate a net pay so

basically be gross pay minus deductions and that’ll give us a net

pay. So we are learning deductions doing subtraction so we do the equal sign and then you could

choose the gross pay which is C4 minus the total deductions which would

be f4 hit enter and there is the total for all

the employees at this point and there is the net pay for the first employee now

we can drag this down and of course we have the calculation for all employees

whenever you’re doing this make sure that you double check their

references and also note that we did not need to put the function for basic

arithmetic calculations now let’s learn about the annual net pay. So we want to get this multiplied by the monthly net pay multiplied by 12 and here we are

learning the multiplication aspect how to do it in Excel so we need to do it

here is=G4 that will be the cell over here times 12 hit enter and that gives us the

annual pay for the employees. Then let’s say we want to calculate weekly

pay this will be dividing the annual pay by 12 and again it will be equals sign, the

reference which would be h4 divided the division is represented by the / and then

divided by 52 which will be the number of weeks per

year enter and there is the calculation per pay per week so this briefly explain how

to use the arithmetic functions in Excel and these are very key even though this

may seem simple they’re very key in utilizing Excel effectively for your job

or whatever it is you’re doing. The next item here before we waste too much time

and we go beyond our 30 minute time limit here for this tutorial is how do

you sort your data and filter your data. In Excel that’s very

easy as well let’s say we want to sort say by date first so all you do is you

go to the column you want to sort by by and you click here on sort and filter

you choose oldest to newest to oldest however you want to sort it or another

region here and choose a through Z or alphabetically or however it’s as simple

as that Basically click on it and choose to sort it.

want to filter this data let’s say we want to see all the sales by a specific

sales person so we go here we want a filter only as the sales from

Franks well what you do is you click here on

sort and filter click on filter and notice it will put these drop down arrows now in here you can click on the drop down and choose which ever you want to have

them filtered by. So we choose Franks and now know all the Franks records show up the

rest are not being displayed at this time you can filter by more than one

criteria here so you could say I want to filter by the region specific region

that say the eastern region and then Franks and then I see only those from

Franks and the Eastern area now to clear the filters what you have to do is

unfortunately this doesn’t fit on my screen here but there’s an option right

above the recording screen here it says clear filter from region or you can

just uncheck this so clear region and it will clear out. And next unfilter, and it will clear it out. The next one you just clear it at the top notice as well that one other feature

here is that you can also create filters that match a specific criteria that are

greater than smaller than top ten and all that type of stuff now that’s data

filtering in just less a minute here or two. next thing that I will show you here is is how to create charts in

Excel so that’s another very useful feature so let’s start with the basic

and easiest one first. So it’s a you have the year and the sale and you

want to apply a specific chart for this. well one of the cool things is that

if you have office 2013 or 2016 is that you have this option here on the bottom and will give you some additional things that you can teak and and customize here

for your liking and tinkering will so here it and go on the charts and you can

create a line chart that’s how it would look like or a clustered chart or any of those other types of charts here that’s one way to create a chart the other way is

that this is the traditional way is by going under insert and then you choose

to insert a chart. Now you know that you have all these different types of charge

but one of them features here is the recommended charts

and this was new in 2013 and 2016 so click here and you choose one of those

charts click OK now know this is inserted the chart

automatically here and the next thing you can do is you can customize and use

any of those tools and options here on the top to tinker with it. The other thing you can do is you can

change colors if you want to tinker with colors more the layout to make it a different

layout to bring more data or less data on the screen here and also notice that

you have a bunch of options here on the right hand side and we have two tabs and

these contectual tools on the charts in Excel so you can tinker with

those as well with the charts now you might have references like this where

you have a bunch of stuff a sales for example for this year have multiple sets

of data now if you want only one set of data, you can just select the labels in the data area and then create a chart very similar to how you did earlier if you want more than

one so like two or three of them so let’s say three of them will go then

under insert and then choose recommended charts, pick one of those charts and

then click OK. Now notice they are grouped together and you can tweak it as we discussed it earlier. You can tweak it further. Before we

move any further here as well I’d like to demonstrate how to get data from one

worksheet to another so let’s say I have here and I’m creating an annual report

and then I want to put in there the various employee expenses per month or per year

or whatever and I had to come in here let’s say these are the names of my

employees and just copying and pasting them at this point but now I want to

post in another one another area for a report well what you can do is basically you

can post the value of another cell in another worksheet wherever you want to

another part of the worksheet you can do this in a couple ways by using what’s

called named references but here I am going to explain the quick and easy way is

by hitting the equal sign so there are three steps remember three steps here equal sign on the cell if you want to

post the value you go wherever the data was. For now I want to post for example

the pay for hubert here so I just click on it and then hit enter and it posts

it. Now if I change it somehow over here his pay let’s say it increased or decreased or whatever happens then I can go here and it will be updated now notice

whenever you see these number signs here that means that the column here

it’s not wide enough you just need to drag it to the right and it will be

posted this could also be a calculated his value as well so equal it could be

wherever there was a formula for example for this person here and hit enter and

it’s now posted for this person here. Now in my madness here to show Excel in 30 minutes, I am going to move to another quick area here as to how to

change the print layout for this document and let’s go here to the beginning of this document, let’s say I want to format this and tweak tinker with it more so we can go and

highlight a certain area in Excel and you can apply to format that as a table

and you just pick any of these designs and you could say my table has headers and format accordingly the other things you can utilize is you can

under the Home tab you can use conditional formatting where you can format the data by using these bars here based on the value that exists in that cell. You can set the margin you can basically click on custom margins and you don’t specify how

many of the margin should look like the top or on the bottom and other things

you can put a header and footer so if you want a custom header it’ll say just

type in here and and then that will be posted on all

the pages there then you can also click here on print preview and it will give

you a preview of this document let’s say that I did not want to print this other

stuff I just want to print this area this table right here to set the print area

here what we do is select the area that I want to see others my print area I go on

under page layout and then I can go another print area and choose set print

area. If I go to File Print and get a preview of it this is what’s going to

print out only that specific section is going to print out I’d like to also

demonstrate how to do mailing and sharing with other users as well. So what

we do is if we want to share this spreadsheet with somebody else you can go here under

the File menu and then you can click on Share under share and share it via

email or other people in the cloud or also you can share it as an attachment

and this will be the most common one the other option is to send it as a PDF you

can actually choose to export this you choose to export it as a PDF format and

that’s all you have to do you give it a name. I’ll demonstrate this point very

briefly how you can link of this were reports are supposedly every month

you’re making a report but you’ll want to data from your Excel to be

automatically linked to your report in in Word. So there’s a way that you can

actually link a portion or part of your data in Excel with a word document

for your reports and then every month is gonna be pulled are automatically to your

report. So suppose this is my document in Word here and say this is my report copy and paste

it. so for example if I choose here Control C to copy it so or copy over

here if i go and paste it into Word now this that’s the first thing is going to

happen here is it’s going to paste it that doesn’t look too bad however it’s

not really linked with Excel so what we want to do is we want to make sure that

this is actually linked with our excel file and then you go here to excel I

select the data I copy it just like a copy this area control C now I going

to word here instead of pressing Ctrl V to paste it i click here on under the Paste

section click on paste special place there’s a link to an Excel object and then

click OK and then it has been pasted. doesn’t look too different from the

previous one if I were to close this and save it so we’ll save it I’ll call this

is my report now if I go here to and now I let’s say months passed by and

whatever happens there I go and change my expenses here have changed so let’s say I spent more and notice now my numbers have changed here such and my expenses have

gone up and instead of me going and copy and paste in my report and so on

what they can do as I can minimize my Excel or close it doesn’t have to be

opened by the way , double click on the report notice that the first thing that will pop up

as its gonna say this is linked to an Excel or other file out there do you

want to update it now yes and the magic happens automatically here but this was

after I made the changes and it updated this data automatically from Excel as

I change something in Excel Word report. Another feature that like to show

you and the ability to do mail merges using a data file from Excel this is our

data file and we can maintain this anyway you want as you go and you

basically have the first row has the field names then you maintained a

different columns here and the addresses and other information as well. Now you

can utilize this and update it as you go and then every so often create a mail

merge to create the mail merge what you do is you go under mailings here in word

and you click on mail merge and I usually recommend that you utilize the

step-by-step wizard then you click on Next here and then you choose Letters. You could choose envelopes, email messages as well and that’ll be a powerful feature

free to utilize its the same principle overall for all those functions and have

other video tutorials for those as well then what you

do is you click here and create letters and that you click on Next and then it’s

going to ask you if you want to use the current document that’s what we are used

in this case we’re going to create one from scratch then right click on select

recipients and we go and locate that file the excel file that we have the

data and and this is my file and I’m gonna pick the one that says Sali

Kaceli customers typically it will be the first one but in this case and I use

the second one because that’s how I renamed it as you noticed the moment ago. then I click OK here and notice it has all my customers. I could sort it, filter it find duplicates and all that type of thing

from here and then click OK I just told the system will use this document and

I’m going to use this data file for the mail merge. The next one is to write the

letters it says go ahead and write the letters I say Dear and then ago here

on there in certainly merge field and I say their first name and then you could

write the letter and then also include in their various fields such you have

from your Excel file so for example if I’d say you could basically just fill in

the fields here you get the idea and then you can also include in here the

comments field that we have from before You could also say say and you can format this however you want of course there are multiple uses to incorporate various

fields and customize this for other functions as well but just for

simplicity at this point and then here you can click on preview letters by the

way at this stage you can also save this file you can save it so that it’s time

you open it up in the future it will actually linked to your Excel file and

you can simply run another mail merge automatically without going through all

the steps you can preview your letters and know this is kinda put here Dear Owen then I can go to the next customer next one next one of those how

it’s changing them and then finally you can scroll to the bottom here next to

complete the merge and then you can either and it’s those individuals others

or you can print them directly to the printer. Notice you also have this

finish and merge button here in the top so personally I choose to edit them. It will create a new document and at this point all the files have been compiled and I

should have thirty eight letters created on him because they had 38 customers in

my excel file. These merged results documents do not need to save unless you want it for documentation purposes and the form file

that you have from earlier you can save that and utilize it for future emerges

as well so now if I if I don’t open it again here from emerged know this it’ll

prompt me the link to the excel file you say yes and at this point they can go on

their mailings and then I can to another merge or customize this by adding

something more finish and merge and i edit individual letters all of them and

then there is just a couple of clicks if you wanted to do an email merge to send

email messages you can click here on send email messages for the subject in

here and then the to field that is the field that corresponds with the Excel data file you press email here you click all records and engage using Microsoft

Outlook to send this out your customers and that’s how do I mail merge in

conjunction with Microsoft Excel and Microsoft Word. So that is it this

tutorial excel in 30 minutes I hope this was helpful and useful and if you want

to learn more about Excel just check the full tutorials have put together an

Excel and thank you and feel free to post comments and contact me if you have questions or comments