Microsoft Excel Masterclass 2026 - From Beginner to Advanced

The free Excel course that you're about to watch is just a small part of Microsoft Office full course bundle. So if you want to also master Microsoft Word and Microsoft PowerPoint with my teaching style and also be able to gain a certificate of completion for the course you're watching. You can feel free to tap the first link in the description right now and join us over there in a fraction of cost. Also Mentionable that this course is brought up to you by the take of college, the smartest alternative currently available to the traditional education. With

all that being said, I'm very excited to get started. So, let's go. [Music] Hey everyone, we are getting started with one of the most requested and most wanted courses inside the takeoff college which is nothing but the Microsoft Excel course. So let's jump Right into it. Getting started with the introduction to Microsoft Excel and getting to know what it actually is. So, Microsoft Excel was first released in 1985 with the Microsoft Office family as I've talked about it and I've mentioned it before in the word and PowerPoint courses of the take of college as well.

Microsoft Excel is one of the core products, one of the three Core products to the Microsoft Office family. And if we consider this Office thing a family, Excel can be considered as one of its childs. A lot of people believe that it may be the smartest child or something like that. uh and yes that is that can be uh considered as a true thing. So Excel is more of a professional one in between these three options that we have. Word and PowerPoint are more commonly used tools while Excel is also Known as a professional like

accounting tool and things like that. and we are going to go through it completely throughout this course that we are in. So, Excel is a spreadsheet editor. What spreadsheet is is a computer application as you see for computation, organization and analysis and storage of data in endless forms of tables. And that is basically what we do with Microsoft Excel. So the primary usage of Excel is storing data and As well as storing that data, Excel provides us with plenty of amazing options when it comes to organizing and analysis inside that data. and we are going

to learn it completely inside this course. So in order to get Excel before anything you can always come to the official Microsoft website and as you see here we have Microsoft Excel. It has a sign up for free option and it also provides us with plans and pricing for Microsoft Excel which is free. As you See, we can use Microsoft 365 personal or family. It also has um special kind of pricings and plans for businesses that can be used and can be installed through. So after sign uping right here for free, it will provide you

with a file or something like that that you can use that file in order to install Microsoft Excel on your computer the right way. So this is actually the correct way that you can get it and you can have it through the Microsoft Official website and after downloading it and installing it which is the process that we skip today because it it is it is very easy you know and it has nothing to go through. Afterwards, you will have the icon of the software as you see something like this. And you can jump straight into

it by double clicking and opening it up. And this is what we are getting started with. So as you see over here, we have plenty of different sections. This is the First thing that you are probably going to face when you open the software up. So here we have plenty of options for creating a new project. As you see, it can either be a blank workbook or from the presets that Excel provides us with which are uh used in many cases and we are going to go through them in the next lessons which are more

kind of project based lessons but not in this lesson. In this lesson we are going through the essentials. So here we have Blank workbook. As you see here we have a an area for your uh previously and recent project which you have previously worked on or created or edited and you will be able to access them through here. Uh yours may be empty as you are just getting started with the Microsoft Excel. And over here we have this options. I mention it in all the office courses that we've had. So I've mentioned it in Word

and powerpoint courses as well. Um over here in general you can access office theme which is as you can see set to colorful. I just wanted to say that if it's set on use system setting or you know white something like this or black if you do not feel uh something like this you see the dark mode I love it actually by the way um if you do not feel comfortable with it and you want to have the exact color sets that we are having in these in this course and In These videos, you can

always access the options throughout this button over here and in general tab come to office theme and set it to colorful which is going to be the case that we are using inside this course. So in order to create your first ever Excel project uh your first ever spreadsheet all you need to do is that you need to come and click on this blank workbook button and afterwards as you see we are headed towards this complicated sounding And very professional sounding dashboard that Excel provides us with And this is the thing that you are going

to learn in this lesson. And after this lesson, you are going to master the essential parts of it. So over here as you see the main creator and the main element of our projects and our works here is these small squares that you see these rectangles sorry which are called cells and these cells are creating an endless table as you see I'm scrolling and it is not ending it is generating endless you know rows and columns for me. So if I go this way you'll see the same that it will provide us with as many

columns as we want and this endless table is built for us to store and analyze and organize our data over here. As you see this is a sheet itself which is as you just saw an unlimited kind of a table and this sheet can be uh plenty you know in one Microsoft Excel Project which is called a book we can have many of these sheets and they are creatable through this plus button. So if I press it as you see I'm having sheet two now and I can navigate between my sheets. So if in sheet

number one for example I say the take of college as you see if I go to sheet two I can't see it you know cuz this is a different setting for itself. If I go back to Sheet one, as you see, I will be able to see it. Uh, I can right click on these sheets and I can rename them. For example, my own sheet. I can move or copy them. I can delete them or I can insert new ones by clicking on this insert option as well. Um so I right click and delete this

sheet. So this is how it basically works. This is how sheets work. And all these sheets are going to create us one Book that we are working in right now. So just like any other Microsoft Office software, Microsoft Excel provides us with navigation options in these bottom bar that we have over here. So we have this zoom bar as you see which can be used in order to zoom in our spreadsheet. Um we can do the same also by clicking on these little buttons. this minus one for zooming out and this plus one for zooming

in. We also have this number as you see which sorry this number which if I click on I will be able to use it and give it a percentage in order to set my view and its ratio with the reality and you know same stuff. We have plenty of viewing options inside Excel. As you see we are having normal right now. We can use page layout in order to see that if we want to print the data that we have how will the data Be looking like inside those papers that we are using and using

this page brick view preview. As you see um again this is for the purpose of printing and stuff and it will um you know show us the places that our data and our tables are being separated into multiple pages. And these are the different views that this software provides us with. So after all I zoom in a little bit and here Um as you see we are having cells and we can feel free to fill these cells with our data. So for example I say name um email you know and phone number. [Music] So I

say James Wilson uh James Wilson [email protected]. These are sample data. Let me even use other emails. For example, I say, you know, james at thetakeoffcol.com, you know, and a phone number plus one as you see. Okay. Whatever. Um, I'm adding other data here as well. So I'm saying for example we have William Smith, We have Brad Johnson and these are for example the three names that we have over here. um three or something like this. So these are as you see uh the data the sample data that I added over here and now we want

to go through the options that this um weird looking Software provides us with. So at first, just like what we had with Word and PowerPoint, we have the phone section over here inside the home tab. And through the phone section, we can change the phones family to uh our selected parts of our content. So let me tell you this beforehand. Let me mention this in Word. If you remember, if we wanted to make any changes or change anything inside our document before that, we had to make a selection of that part that we Wanted to

change. Uh we had this uh thing and this logic inside Microsoft PowerPoint as well. In Excel, we do have that as well. But here the difference is we can either be selecting a part for example I'm saying I'm just making changes on number you know on number only we can either be uh applying our you know formatting and stuff on a certain part of our text or we can be doing it with cells. So this is very common that we make a selection Of a couple cells and we apply our changes to them. So this

is how selection basically works in Excel. In Excel it's not like Word or PowerPoint. We have cell selection inside instead of text selection you know. So for example, I select this name and here as you see I am able to choose from different phone families that I have installed on my computer. For example, I say this, let me actually choose this entire row. So this first Row of mine I want to set a couple formattings to. In order to do that, so in order to make a selection of a row or a column, all you

need to do is that you can and you need to come on the name of it. So it can either be a column which is in letters or a row which are named by numbers and you can come on it and by clicking once as you see it will make a selection of the entire column to infinity as you see. So I do this with this row And over here I change the phone's family like that. I also can change the phone size over here using these numbers. I can type a number. So for example

I want it to be 120. It is possible. You know, there are no limits to the game. But I choose 16 for example. We also have these two buttons. One is for increasing the phone size and one is for decreasing the phone size. These two buttons can be used as Well when it comes to phone size and text formatting. The next options that we have over here just like what we had with for example Microsoft Word you know are text formatting options and they are available here as well. So I can bold a certain cell

of mine. So I bold Brad Johnson for example and as you see this is functioning. I can make it Italy over here. I can underline a certain text of mine as you see and we have border Settings as you see. So this will be applied to the cell or the cells that I'm making a selection of. So for example, I can select this part over here and inside borders I can say I want a thick outside border. And as you see I just did it. You know I can select this uh row of this table

for example and I say I want a left border here. You see, I can say I want a bottom border for this cell for example. And I am able to uh add borders to my cells and to my Cell selections just like that. And as easy as that, we also have more borders for more settings and options to those borders that we are creating and setting inside our project. Another thing we have over here is draw border. And using draw border, we will be able to use this pen in order to make draw drawings and

by clicking and dragging make those uh kind of borders that we need to have and we want to have Inside our project. Just like that. We also have draw border grid and using this we will be able to as you see by clicking and dragging draw grids uh not only borders. So it will make every single cell that we make a selection of having full of the four side borders. We have erase border and using that as you see we will be able to erase all the changes that we have created by clicking and dragging

and specifying a certain Area and using line color and line style. We will be able to make the and specify the styles of those borders that we are adding and using inside our project. The next thing that we have is the cell background color which can be specified over here. So I can say I want for example Brad Johnson to have a yellow background and I can come here and click on it. And as you see this is applied. I can choose between colors And I can use that this easy using this uh fill color

option. The next thing that we can change over here using the phone section is the phone's color and it is changeable using this phone's color option. So I say for example I want James Wilson to be um purple and as you see I can choose from these colors that I have as options and this will be applied to my selections and that's how we change the color to a text inside Microsoft Excel. So after all I'm headed over to the alignment section. So as you see we have cells over here and these cells have alignments

and those alignments as you can guess are due to those cells that they are positioned in. So in order to make alignments we can just select at first just like how we do it all the time. And over here inside the alignment section as you see we have plenty of options. So we can Align everything to center, align everything to left, align everything to right and we can as well vertically align them as you see to middle, top or bottom. Um, we have certain options of giving angles to our text. So as you see I

can make it like this like this or vertical text you know these are all these styles that I can give my text and my cells using this align section. Um another Thing that is setable and possible over here is the setting of our text direction. So it can be either left to right or right to left. And it can be either applied to one single cell or the entire of our project. And in order to do that, so in order to apply this to the entire of our project, first you need to select everything. And

if you don't know that in order to select everything inside Excel, you just need to come here and click on this Little thing that we have over here. And this will make a selection of the entire of our spreadsheet. So every single existing thing inside the software. The next thing that we have over here is the styles section. So at first I make a selection of this table that I created and opening this styles up. As you see I am able to choose between all those styles and options and presets that this um section is

providing me with. So I can for example select this row of mine And come here and say I want to use this heading style for it. Um sorry these three are enough not the entire row. So I say this headings thing and for this part for this bottom part I say I want you all to look like this. And as you see these are all setable through this styles uh section over here. We also have format as table. So this is when I'm selecting as just as what I just did. So I'm selecting an entire

Table and I want to format it and these are the different options that uh this part provides us with. So in order to do that I need to make a selection of my table just like what I did and choose one of these presets that we have over here and it asks us that where is the data for your table. So this is how addressing works inside Excel. I am going to go through that completely in this lesson but let's skip that what is written here. It's it's Just an address to the entire table that

we have. And you can check or uncheck my table has headers. And by clicking okay, as you see, we will be headed forward to the table design. As now we have a table over here. As you see, whatever style that I'm choosing is being um mixed with what I had selected over here. So here I set it to normal. And now as you see all these table design styles are Appliable, all these presets and I can have my table just like that over here. The next thing that we have over here is the ability to

either insert or delete or format our cells. So we can be changing these sizes to our cells through this format inside the cells section. And in order to do that you need to at first again make a selection of what you want to change and come to format. And here we have row height and column width. And these are all setable over here. So I say 30 for example. And as you see I can change the height to my row. And now if I align this to middle as you see this is more touchable now

these vertical align options. So I can also change my column width. So these numbers I think are in pixels something like that you know and these are all setables using this cells format Option. We have delete. So this is about deleting rows, columns or the entire table, the table columns, the table rows. We can delete the entire sheet. And as you see, it says that this is the only sheet that you have and that's why you can't delete it. In order to delete it, you need to add another one. So this is basically these that

we just Talked about. These are sheets and they are controllable throughout these options as well. If I click delete cells for example, as you see this is what will be applied to my project. We also have insert. We can insert cells. We can insert uh rows. We can insert columns. As you see, we can delete columns, we can delete rows, and these are all happenenable through this sales section that we have over Here inside the home tab of Microsoft Excel. The next section that is very important as well and we want to go through right

now is the clipboard. So this is basically what we have had in the other softwares of Microsoft Office. So these are the commands that we had cut, copy, paste and format painter. Using cut and copy you will be able to either cut or copy the cells that you have. So here we have cells, not text, not anything like that. We have cells. And for example, I copy this uh specific cell or a set of cells. So I choose these two rows for example of my table and I copy them. And I come over here, I

click and then I will be able to paste. And we have we have plenty of pasting options. As you see we have paste we have formulas which is about when we are copying and pasting formulas. So what are formulas? You are going to know them in um this very lesson that we Are in. Just wait for them. But this is about pasting them. We have keep source formatting. We have no borders. keep source column widths as you see uh transpose. We have values. We have values and numbers formatting and as you see there are plenty

of options that it provides us with. As you see we also have we even have pasting options for pictures and illustrations Specifically. So this is paste. We also have format painter. So I want to write something over here. I say the take of college [Music] and I want this cell of mine look exactly like how this cell does this name cell for example. So all I need to do is that I need to first click this name. Select it and choose format Painter and then click and make a selection of my destination. And as you

see the takeoff college will look exactly like this name cell does. And this is format painter inside Microsoft Excel. Now let's continue our work by heading over to the insert tab. So these are important as well. Over here as you see we have plenty of insertables that we are going to go through one by one in this course. So the first thing That we can insert inside Microsoft Excel is pictures. And as you see I can come to insert tab and using pictures my picture can be either placed in a cell or placed over cells.

So I start with place in cell for example. As you see for all we have plenty of options. We can insert from this device stock images or online pictures. So I use this device and try to choose the logo to our college to be placed inside the cell. So This is as you see sorry again place in cell and this is as you see what we have here I can come to home and using this cells part and in inside format I can change the height to the entire row in order to see my logo

better as you see. So this is one option that we have for images. Another option as you saw is placing oversells Which will happen like this as you can see. So I'm having this image of mine placed over the cells that we have. And as you see as I imported and inserted this image of mine the picture format tab was opened. So here I have plenty of options available. We have remove background. So we can remove and remove the background of the image that we've imported and make it Transparent. For this case, it's not usable

because the logo that I've imported is already a transparent logo in the PNG format as you see. But this is usable over here. We also have corrections as you see for brightness, contrast, and those kinds of settings. And I can change them over here. We have plenty of color options. So we can apply different colors to this image that we have over here. We have Transparency which is about the opacity to this image. So if I make it transparent as you see I can watch everything behind it through it just like that. Um so back

to how it was. We have picture styles just like what we had with the other Microsoft Office family softwares and members. And we can set picture border over here. The color, the weight, the style and everything to it. I can set from picture effects From the plenty of preset picture effects that we have to my picture. And we also have picture layout which will allow me to convert my picture into smart objects. As you see, we also have alignment options. We have rotate. We have crop. you know they are all available over here as well

and this is the picture format tab inside Microsoft Excel. So the next insertable through this insert Tab, let me remove these images, is shapes. And as you see, Microsoft Excel provides us with plenty of shapes. And we can choose and use any of them that we choose just by clicking on it and then clicking and dragging. and I will be able to draw that shape inside my project. As you see the shape format tab is now opened and added. We have shape styles. Um we have presets faux our shape style. We can set different parts

of it ourselves. So we can change the shape feel as you see the shape outlines and the settings to them. The shape effects that it provides us with. We also have word art styles which is about the times that we type inside our shape. So how we do that, how we add texts inside our shapes inside Microsoft Excel is that we right click on the shape and click edit text and as you see we will be able to Add texts over here the take of college and afterwards this text is controllable through this word art

styles. We can choose between these presets or set the fill, the outline and the effects of it ourselves. As you see, we also have rotation options here. And that's basically all that we have. And as you see, shapes are flexible inside Microsoft Excel and movable and Placeable anywhere that we want them to be. So the next insertable over here is icons. As you see Microsoft Excel provides us with a wide range of stock images which are icons basically and we can choose between them. Click insert and that very specific icon and graphic that we've chosen

is now imported to Microsoft Excel and we will be able to use all these icons inside our project in order to make it look better or look more Professional whenever that it is needed. The next insertable that we have over here is 3D models which are available in all parts of the office family as you probably know if you've been through word course and powerpoint course you know what I mean and over here as you see we can choose between these 3D models that it provides us with and we can click on insert and Afterwards,

as you see, we will have them inside our project. We have 3D Model tab opened over here as well, which we can use to choose between different views that it provides us with. Or we can use this button over here as you see for our 3D rotations in order to make the 3D model the exact thing that we are looking for. The next thing that we can insert is smart arts. So in order to do that, first we click on this smart art button over here inside the insert tab and we go through All these

presets that this provides us with. We need to choose the one that we are seeking to use and click okay. And as you see smart art will be added to our project. So we can change the content to it using this part. So I say step one for example, [Music] step two, step three. And as you see this is how we can have Smart arts inside Microsoft Excel. When we add them this uh tab will be opened over here. So we have smart art design which is about the uh design and the color setup of

this smart art that we've added over here. We can also convert it to another one. So we keep the data and convert this smart art to uh another one one that we are seeking to use. We also have this formatting tab. this Format tab over here as you see which we can use in order to style different shapes that are used inside our smart art or different texts that are used and placed inside our smart art just like that. The last thing that we can add inside illustrations section of the insert tab is screenshots which

is basically about clipping a part of our screen and it will provide us with the image that we've clipped inside our project Uh in case that we need it. And as you see we have a picture format for this screenshot opened over here as well. And that shows us that screenshots that Excel provides us have the exact same settings that a an ordinary image can have. So that's basically it. After all we have charts. So in order to explain that first I need to come to file click open and open one document that I have

over here. I have prepared which is and contains a sample data that I am going to use to create my chart over here. So here I zoom in a little bit. As you see we have all this data which are about regions, products, months and the sales of those products in those specific months in those specific regions. So I want to convert this data into a chart. In order to do that first I need to select. So I make a selection of this entire table that I have over here. I specify the Areas that I

want to use the data from in order to make my chart. And then I come to insert and as you see I can't edit anything over here. Why? I assume because yes this has a protected view. I have protected it. We'll talk about these protection stuff uh later on. But so I make a copy of it and bring it to my actual project. I create a new spreadsheet and I rename it to charts And then I paste the data over here by pressing Ctrl +V and I make a selection and come to insert and here

inside the charts section we can add our charts over here. So we have recommended charts. What this will do is basically it's actually very very cool. So what this will do for us is that it will go through the data that we have selected and it will detect what kind of a data that we have and detect that what kind Of charts we may be seeking because as you know there are plenty of different types to charts. And using this recommended charts button, it will provide us with the recommended kinds of graphs and charts that

it will provide us for this specific data with. So we can choose between them and by clicking okay as you see our chart will be created. We can also be choosing the very specific kind of chart that we are Seeking ourselves throughout these options that we have. So these drop-downs are all about different parts and kinds of charts that we may have over here. And as you can see, we can choose any of them for any kind of specific specific showcasing purposes. And uh as easy as that we can add charts to our project. So

after adding them as you see here we have a chart title that I Can click multiple times on and change it and say the sales report for example. And afterwards I can select the chart itself and uh as you see this chart design tab will be opened. So here I have different chart styles that I can select from. We have different sets of colors as you see this is very cool. And we also have the format tab for each specific part of our chart. If we want to make very Personalized changes into different parts of

it, we can use this format tab of charts that we are provided with. So that's basically it. That's all that we have with charts inside Microsoft Excel. Let's imagine that after all I want to make a print of the entire of this data. So I want to print this table as well as this chart. And in order to do that we need to set uh the settings and the setup of our printing activity. And in order to do that we have this Page layout tab over here. As you see, we can use page layout in

order to set different themes and preset phones and colors as you see inside our project. And changing our theme will lead us to an overall change in the colors and in the phones, phone sizes and phone families that we have over here. We can also uh change a very specific part to our theme. So we can just change the color for example or we can just change the thirds you know or we can just uh Use those effects and if we choose an effect over here this will be applied to our entire project. Um the

other things that we have over here are the page setup section elements and items. So the first one is margins which is about the distance and this the white space that is going to be placed between the border of the pages that we are printing on and where our content starts. So in order to understand these better, I'd rather switching to page Layout view and then we will be able to see it better. At first as you see when I switch I will have a preview of how my project will look like. So for example,

I want to change I want to move this chart and place it entirely in that one paper in that single paper that I have just like that. And then if I for example come here and change the margin and make it wide, you will be able to see that we have changes inside these spaces that we have Between the border and the edge of the paper. So these are margins. We also have orientation to our project. It can be either landscape or portrait. Why aren't they being applied? I don't understand but whatever. So we have

orientation as well as you see we have size which is about the size to those papers that we are printing on. It can either be A4, A3, A5. All those international sizes over here are available. We have background Uh which can be a picture as you see. So we can browse a file and insert a picture from our computer and that will be set as the background to our project and our pages. And after all those settings, all you need to do is that you need to come to file and go to print. And then

you will be able to make a print of your spreadsheet. As easy as that. And by clicking on print, it will print you the data that you have selected. a very interesting way of Working with data and viewing our data and going through it in order to make conclusions out of it that Excel provides us with. So basically a very interesting view of the data that Excel provides us with is these pivot tables that we have inside Excel. But in order to go through them at first let me again open up another document that I

have over here a sample data for pivot table and I Click open as you see it's not editable protected view so I click enable editing and they and then I will be able to make my changes over here. So here basically as you see I have a table providing me with information about my customer, my customers, the product category that they purchased, the date, the quantity, the revenue and everything. and I select this data and going through The insert tab I click recommended pivot tables. So what this button does is basically the same as what

recommended charts used to do. As I said, recommended charts goes through the data that we have and somehow detects our um kind of how can I say desired, you know, uh styles of charts that we may want for this very specific content that we are selecting and recommended pivot tables will do basically the same. So, we have plenty Of presets that it provides us with which are pretty cool for this specific content that we have over here. Um, and by choosing each of them and clicking okay, you will be familiar with the concept of pivot

tables. So, this is what we have basically for pivot tables. Um over here as you see we have row labels sum of quantity. I can open these up. So it has for example this very specific kind of table has categorized the orders of mine with The names. So it has listed the names. I can collapse the names and under the names I can say the dates and the quantities of each order and as you see here we have sum of quantity. So the data that is not at all even mentioned here are the money that

they have spent. So the revenue as you see is unchecked the region the product category and order ID. So this is basically a pivot table. So let me delete it. And create mine by selecting this data coming to insert and clicking this pivot table from table / wrench. So first it says that what is your range? If you select your table it will automatically provide it with the wrench. Uh but if you want to get familiar with the addressing system that we have over here as you see first we have the name of the sheet

then we have this uh specific mark. We have dollar sign a Which is about the column. So we have column a dollar sign one. So one is the number of the row. So a1 a1 is this order id. This is about addressing. So I'll mention it again uh a little bit further. But then we have this um little thing over here. Then we have again dollar sign G the column G and the row 21. So G21 and this is the range. This is the area that we've selected. And by clicking okay, our pivot table as

you see will be Created. So I zoom in a little bit and as you see here we have filters, columns, rows and values. So let me add for example let me add dates to rows. So as you see we have the dates that orders have been happened inside those dates and then I add customer under them. So as you see now my content is actually and my data is actually sorted and categorized by the date that each purchase has happened. And as you see we Have no two purchases in happening in a common date. So

this is probably not that much of a smart kind of categorizing but this is what you can see you know. So I remove it. I remove this field. I remove this field. Then for example I want to add category to my rows. So we have apparel box electronics and home. And then I add customers inside that apparel. So then I will be able to see that sorry to that product categories not appper and then I Will be able to uh see that for each category which customers are interested. So this is very very cool. I

actually love pivot tables because as you can see they are amazing you know they are basically everything that we need in order to analyze the data that we have collected. So this is as you see the num the name of people who are interested in those specific categories and then I can add fil something to filters. So I say quantity And then if I open this up and I say quantity two okay only the customers who have had purchases with the quantity of two will be visible you know what I'm saying so these are filters

I remove it we have columns as well as rows so I say the revenue for example you And these are added as columns over here. So for example, one thing that I can do is That I can use product category as a column. And as you see now we have for example those customers and then we have categories and then we can create a relationship over here. So we can say that I want the order ids to be listed here. You know uh we have values over here as well. So if I change and I

move this order id to this values as you see it will create a sum of all order ids of Alice in this category For example you know. So I move it out I remove it. One thing I can do is that I can bring this revenue inside value and it will be very cool. So now it is using the info that we give it we gave it. So the info comes from this table if you remember right this is the table. So this is actually going through all the customers that we have had and is

saying that inside each category how much money has this customer spent overly. So this is sum of revenue. This is the summary Of the money that they have spent or sum of quantity for example. So I can bring it over here just like you see. So for example, as you see Alice has only bought one uh apparel kind of a thing but has bought 11 books, you know, as you see. So pivot tables are basically it. You can play with these in order to get familiar with them. This is more of a um functionality thing

and more of an analysis tool rather than a tool of creation or an Editing tool. And I love it. As you see, this is a lovely tool inside Microsoft Excel. And just like what you learned, you can go through different kind of sample datas and play with these in order to get familiar with it and in order to learn how to use it appropriately inside your projects. After all the next thing that I want to go through which is again a very interesting one is the Functions and the formulas that we have inside Microsoft Excel.

So beforehand let's talk a little bit about the concept of addressing inside Excel. So every single data, every single cell that we have inside Excel has a name and that name is visible over here. Whenever I am selecting a cell, I can come here and see the name of it. And how these names work is basically at First we have the letter of that column that this cell is located in and after the letter stick to the letter we have the number of the row of that column. So this is placed in the column H

and in the row three. So this is called H3. As you see this for example is placed in the column H still but in the row 13. So this is called H13 as you see. So this is as easy as that. This is how we can address our cells inside Microsoft Excel. We Also have ranges. And how we address ranges is basically for example we have this range over here. How we address this specific range is that we say H6. So the beginning of the range and then we say the end of it. So age2

and then we write it like this. So I say H six this little thing and H 13. And this is how we specify a range inside Microsoft Excel. So now that you are familiar with the addressings that we have inside Excel and you know how to address a cell or a range, now we are free to head over to the functions that we have inside Excel. So before let me again import the sample data. I will provide you with the sample data inside the course page and the these will be provided to you and you

will be Able to use them easily in order to practice all these things that you are learning. So this is as you see what we have. So what functions and formulers are. So let me introduce what they are at first. Functions and formulas are about making relationships and interactions between these cells that we have and these data and making cells that are related to other cells and that that is when we use functions and formulas that we have Inside Microsoft Excel. The first thing that I want to start with is that how can we copy

the content of a specific cell into another cell. So first for example I want to copy this finance or this cell this specific cell whatever it is I want to copy the content of it into this cell. So first I need to uh understand the address to it. So as you see this is in C and six. So this finance is C6 and then I go to the different cell And inside that cell I start with equals. So this is how all formulas and functions are going to start with the equal and I say equals

C6 and I press enter. And as you see here we have finance. So if I change it I say financial for example and I press enter as you see the content to this one will change. So this is how we clone another sales content into another cell you know. So I change It back to finance. The next thing that I want to talk about is specifically for numbers. So let's say that I want to um add the salary of John sorry the target sales or whatever you know or let's do it by salary right so

I want to add the salary of John to the salary of David for example so I want to add D2 as you see over here to D6 And I want to show it over here. So what I can do is that again I need to start with equals and then I say D2. As you see it will make a selection of that very specific cell in order to in order for us in order to make sure that we are doing the right thing. Then I can use plus D6 sorry D 6 and using that plus

we'll add these two To each other. So we'll summarize these two. If I press enter as you can see it says 10,700. So it has added 4,500 to 6,200 just like that. As cool as that. We also can uh add multiple of these two together. So I want for example I want the total salary over here. So I can come here and say equals D2 plus D3 + D4 plus D5 plus D6 and press enter. And as you see this is the total salary of this month that has to be paid. And this is the

summary the summarization of all these uh items and values over here. So this is adding as well as adding we also have subtraction. So how we do that is basically the same. So we use equal We address the first cell for example I say E or F um 2. So I want to make uh let's say we have a target sales and we have actual sales. I want to specify the difference between them. So I can say here I can say equals G G2 minus F2. So this was this will show me the distance of

the actual sales with the Target sales. So John has had a bad performance cuz it has had minus 1,500. So it this is the distance between the actual sales that he has made and the target sales that he had. But if I do the same here for example for G 2 G3 sorry I forgot to use equal. So G3 equals G3 minus F3. As you see Sarah unlike John has had a good performance. So, she has had $1,000 sale more than her target sales. So, That's basically it. That's what we have with subtractions inside Excel.

We have multiplication as well. So I can for example multiply this 10% to the actual sales in order to specify that how much this John is going to take from this 8,500. So he has 10% bonus from the sales that he makes. So I say this cell for example equals to G2 8,500 Multiple. So I use this star to use to do multiplication and I multiply it with E2. And as you see now it has calculated me 10% of 8,500 which is 850 you know as easy as that. So these are very cool I love

these formulas and uh functions as they help us a lot actually a lot. So this is it. This is what we have with multiplication. We also have division inside Microsoft Excel. And let me add my own values over here. So for example, I say 12 and three. Then I want to divide 12 by three. So here I say equals a14 which is this 12 divided two. So I use slash in order to divide divided to or divided by a15 and then as you see it says four and this is how we have divisions inside Microsoft

Excel as well as I'm using the Term formulas and as you saw we can use mathematical uh formulas and all mathematical commands over here and we and merge them as well. So here for example I can do multiple of calculations at the same time. So for example I can say I want this cell to be equal to for example I want to calculate the total the summary actually the summary of salary and the 10% bonus over the actual sales that John has made. So in Order to do that I want to first have D2. So

D2 is the salary of John. And then I want to add this D2 to here I use parenthesis in order to uh specify the mathematical priorities. So parentheses are allowed as well inside formulas. So I say I want D2 so the salary of John to be combined to be added on the on top of the actual sales that John has made. So, G2, which is the actual sales multiplied to the bonus, so E2, which is 10%. So if I press enter as you see uh John had made 4,500 as his main salary and on top

of that he had made 850 as bonus and 4500 plus 850 will equal to 5,350. As easy as that, we can add formulas, mathematical formulas over here. So I do the same for Sarah. If you didn't understand, uh, watch out. So I'll explain again a little bit. For Sara, I Say equals. So first I want the salary of Sarah, which is 5,500. So I say D3 which is the salary of SRA plus parenthesis inside those parentheses I say the actual sales that SRA has made. So G3 multiplied to the bonus percentage of SRA which is

15% which is E3. So again as you see for example this is going to be how much SRA is going to take home. So these are basically what We have as formulas inside Microsoft Excel. The next thing that I want to go through over here is the functions. So functions are a little bit different with the formulas. So let me first choose all of these cells over here and by pressing delete I clean them all and then we are headed to functions. So we have plenty of these functions inside Excel. The first one is sum.

So what a sum does is basically Summarizing um specific cells that we give it. So it will summarize the content of those sales of those cells sorry. So I start for functions actually start just like how uh formulas used to start they start with equals as well. So first I say equals and I say sum. So this is the sum function and then it will require us the values that we have. So these are how sum functions uh basically are. We have an equals we have the name of the function. we have parenthesis and inside

those parentheses we give values to the um function. So I want to summarize G2 for example. So the sales of John with G3 the sales of Sarah with G4 with sales of Mike with G5 sales of Anna and G6 sales of David. So as you see we can add endless values here and by pressing enter The summary of all of them will be visible over here and that's basically how sum works and what sum does for us. The next function that we have is the minimum function. So I say equals min and inside min inside

those parentheses of min again we give the address to those cells that we have. So for example again I use these actual sales G2 G3 G4 G5 and G6. So what this mean does is that it goes Through all the sales all the sales that you give it and it will show you the minimum value. So the lowest value that they have. So here the minimum value is 8,500 as you see which is for John. And if I press enter, as you see, it will show us the exact same thing that I just set because

this is the minimum to all these values that we have selected. So, let me copy this uh selection that we've made to just paste it next times. Sorry. Uh the next thing the next function that we have inside Microsoft Excel is max. So I say equals max and max is basically maximum. So just like how we had in minimum but max will uh show us the highest value among those selections that we've made. So the highest value over here is going to be the 13,000 which is for Sarra. If I press enter, as you see

this is showing the exact same thing over here. The next option that we have is the next function actually that we have is count. So inside count again we can give address to specific cells. So it will count all the valid cells. So all the cells with numbers for us. So if I again if I here for example include G7 as you see G7 is empty but this will show this will show me five. I selected six cells but this will only show me and count me the number of those cells which have numeric data

and Numbers inside them. So this can happen also with a range. So I can for example say A1 to uh G6 and as you see this is how we make selections of ranges and if I press enter as you see this will say that we have 20 over here. So let's see. We have four over here and we have five over here. And these are the only ones that it counted. You know these uh texts and These dates were not counted because count only counts numeric data. So we also have count a which is a

little bit different with count. So count a will also include the text values and we'll count them as well. So again over here if I say a1 2 g6 and press enter. As you see now we have 42 because count a counts the texts as well as the numbers but count only Counts the number of valid numeric data that we have inside our selection. The next function that we have over here inside Microsoft Excel is the average function. So how it basic basically works is that it gives a range of numbers and it will provide

us with the average to those numbers. So for example I come here and say equals average and inside the parenthesis I say G2. So this cell 2 G6 and then if I press enter it will provide us with the average to all these numbers. So average is basically let's calculate it to see if it has done it right. You know average is the summary of all those numbers divided to the count of them. So if I say 8,500 + 13,000 + 10,800 + 970 9700 plus 12500 and then I divide them all to five. As

you see 10,900 just like that. So this is what we have with the average function. We also have median which is a uh statistic related uh concept. So I am not here to try to uh and and I'm I am not at all trying to teach you what median is and what the concept of median Is. But as I said it is a statistical uh concept which is when we have plenty of data the data in the middle uh so it will sort them from the highest to the lowest and the one in the middle

will be known as the median. So if I give it a range and press enter as you see 10,800 is the median. So it has first sorted these and then due to that sort that it has done it is giving us the middle one which is going to be known as the median u and we have it as well inside our Functions. The next thing that we have is concat. So for example, let's say that I want to uh add this John to this department that he is working on in order to know him with

that department as well. So I'm going to for example have a cell over here and I want to say HR John, you know, instead of John or sales Sarah or it Mike. So in order to do that I can use the command concat as you see. So it is About concatenate concatenating sorry as you see and u concat has parenthesis as well and inside those parenthesis we need to provide text. So I say A2 and C2 and I want to concat these two. If I press press enter as you see it will say John HR.

So that's how we add texts uh to each other and we join two strings and use them inside our project. The next concept that we have is if. So if basically works in a more logical form. So now we are into really serious functions. So if is one of them before let me remove all of these and clear here a little bit. So here uh I say equals if first we have the logic that we want to test and we want to see if is uh this requirement is me met or not you know so

in the parenthesis in the first part as I said it's about logic so for example I Want to say if uh G2 so the actual sales of John is bigger than the F2. So the actual sales of John is bigger than the target sales of John. Then I want to say excellent and comma then this is else. So this is if this requirement is not met. So I say if not say try harder and close the parenthesis. If I press enter, uh, value if true, value if false. What can the problem B? Uhhuh. We needed

to specify these with quotations. So these will be provided as strings. So as you see this says, try harder. I copy the very function that I wrote. And over here I paste it. But instead of G2 I say um G3 and instead of F2 I say F3. So this is about SRA. As you see to SRA it says excellent. And I can do the same over and over. G4 F4 try harder, you know. Uh G5 F5 try harder. Let me see. Now a mistake is ma made here. So G5. Yes. Excellent. And the last one

G6. Sorry. G6 and F6. So this is how if uh and conditions work inside Microsoft Excel. So the other function that we have over here and we want to talk about is count if. So count if will basically go Through a range and see where and how many times is the logic and the requirement met and it will show us the count of those times that these requirements have been met. So let me uh show you something. For example, I choose this range. I say equals count if and inside of it first it wants a

wrench so I can select it and as you see it will be applied over here and then I say um Over 10,000 and close it. Sorry. um it's only about equals. So I say 9,500 and I press enter. And as you see it says that we have one single value of this 9,500 in the range that you provided me with. So let me show you with a better example. For example, we have these numbers over here And these are all twos and ones and threes. And then I choose this range and I over here I

say equals count if inside the range the cell is one. As you see it says that we have three ones. Again I say count if inside the wrench when we have two. As you can see it says that we have six twos and I say count if inside the wrench we have three And it says three just like that. So this is how count if basically works. So we also have value lookups. So how they basically work is that we say equals vookup which is a very very commonly used and very uh cool but a

little bit complex uh function. So inside the vookup as you see at first we need to specify the value that we are looking for. So for example, let's say that I am looking for the Salary of John or the salary of Anna, right? So I say I am looking for Anna. This is what I'm looking for. Then it says the table, the table array. So I choose the entire table as you see. Then it says the column index number. So here we have Anna and I need to start counting from here. So this is the

first column, the second, the third and salary is placed in the fourth column. So I want to find the number which is set for Anna in the fourth column and then It says approach approximate match or exact match. So this will basically work. How how this will work is that if I say true, it will go and look for Anna. And if for example I have written NA with a capital A, but we have NA with small A inside our text, it will not find us that Anna. But if I say exact match uh sorry

for with approximate match it will count small uh with uh capital N both as one single Thing you know it's all about cases it's all about those letter cases and stuff so I say true and then I close it and press enter what is the problem over here value not available. So let me see um I may be making a mistake over here but this is basically how V lookup works and these are the values that it gets in order to Give you that thing that you are looking for. So the next thing that we

have over here, the next function that we have is sum if. So sum if is called as well. So let me create another sheet over here. Let me open another sheet that we had for pivot tables. That's very cool actually for this purpose. So as you see here we have plenty of Purchases that have happened by plenty of customers and for example Diana has done many of purchases over here. So here for example in order to use sum if I will be able to say equals sum if. So let me show you how it works.

Basically first it gets a range. So I give it the entire table. Then it will get the uh value that you are looking for. So for example I am looking for the revenue that Diana has Generated us. So the total money that Diana has spent. So in the criteria part I say Diana and then in the sum range it is about the range that it wants to look for. So I choose this revenue. And the last function that I wanted to talk about is equals now parenthesis. It doesn't get any kind of value inside it.

You just need to say equals now parenthesis and by pressing enter as you see it will provide us with the very Date and hour that we are currently in right now. So this is all that we had with the Microsoft Excel essentials. Now you are pretty familiar with the software actually very familiar with the software and what needs to happen right now is that you need to do multiple projects real world projects in order to increase your experiences with Microsoft Excel but now you do have the essential talents and Skills that are required in order

to use Microsoft Excel. So the last thing that I wanted to talk about in this essentials lesson is about protecting and protecting data and saving the project that you have created. So the first one is about protection. In order to protect some kind of a data and make it uneditable, all you need to do is that you need to come right click on the sheet and click protect sheet. And as you see, you will even be able to make a password over here. But I don't I don't make a password. I just click okay. And

as you see, this will now be uneditable. I cannot add anything over here unless I enable editing inside my project. So afterwards after making the change that we have made uh I want to show you how to save it save the project. So let me get back to this First project of us that we had and I want to save it for example. So I told you how we can protect one part of our work. We can just right click on our sheet and click protect sheet and we will be able to make uh protections.

Then by coming to file and save and clicking browse we will be able to save and export our work. So we have XLS S XLSX as you see. So XLSX is for Excel workbook. So this is going to u save our project just like it Is. And we will be able to access it whenever we want, however we want and wherever we want. And this is going to save it to an Excel project. So if I do that in desktop I say my first sheet and press save. As you see now my project is saved.

And if I close Excel for example tomorrow I can come and doubleclick and open my project and continue my work here. The Other options that we have inside save as one of them is for example CSV. So CSV is as well a very used and common format of these sheets and of data saving and data protection which can be used in order to save your content too. And the point about about CSV is that it is not only for Excel unlike XLSX. XLSX was only for Excel but CSV can be opened for example in Google

Sheets as well. You know what I'm saying? The next option of saving that we have Over here is txt as you see. So if I click save, you will see that we are able to save our data into a text file. As easy as that. So we have our table over here as you see. This is cool as well. Um, we also can save our project in PDF and by clicking save you will see that we have this PDF opened up over here. We can view our Data. uh we can go through it and we

can at the end even print it if you want that specific PDF. For example, I can come to charts and make a PDF out of charts. So this PDF exporting option will only be about the sheet that you are currently in. So this will only export the sheet that you are inside and as you see we will be able to convert our Excel project into PDFs as well. That's basically it. That's all that we have inside Microsoft Excel. And it was such a pleasure to be with you in this course. It was such a pleasure

to be able to teach you all these essentials to the software and I hope that this lesson has helped you. Don't forget to practice all the concepts that we went through with all the practicing Files and sources that I provide you with and as well um keep updated uh from the upcoming lessons and parts of this course which are coming and all are going to be only about projects and we are not going to go through any of the essentials like this session. anymore. Um, that's it. Thanks for watching. Take care. [Music]