!! THE MOST CONCISE YET DETAILED MICROSOFT EXCEL COURSE, ANYWHERE ON THE INTERNET !!
!! TRY IT BEFORE YOU BUY IT . SEE THE SAMPLE LECTURES !!
!! MASTER MICROSOFT EXCEL IN JUST OVER 3.5 HOURS !!
!! LIFE TIME ACCESS AND A CERTIFICATE UPON COMPLETION !!
Microsoft Excel is probably one of the most widely used softwares in the world. And rightly so. Microsoft Excel is so versatile that it can be used in practically any area. From simply counting numbers to calculating astronomical data, to put satellites in the orbit. I'm not kidding. I worked as a structural engineer and we used Microsoft Excel in many calculations including analysis of framed structures.
This Microsoft Excel is a comprehensive course that teaches you all the general aspects of Microsoft Excel and some areas in particular. Here is a list of areas that we will cover in this Microsoft Excel course.
These are some of the areas that we will look at inside this Microsoft Excel course. I will keep on adding more lectures and quizzes to this course, every week. You will have lifetime access to the course, and won't have to pay extra to access the updated Microsoft Excel lectures. I have provided several sample lectures, so that you can Try it Before you Buy it !
In this Microsoft Excel lecture, we will look at worksheets, menus and tour the Microsoft Excel interface in detail. Microsoft Excel is an excellent program that is used by students, offices and businesses in general and will be a valuable tool to be a master of. This lecture is an introduction to Microsoft Excel 2016.
Calculations and data manipulation inside Microsoft Excel works with the use of different formulas. Unlike a mathematical formula or a chemical equation, the formulas inside Microsoft Excel begin with an equal to sign. We will look at how formulas are used inside Microsoft Excel, and we will actually practically use some formulas to calculate certain functions inside Microsoft Excel. Take a look at this lecture.
Latest versions of Microsoft Excel have implemented the ribbon display, which conglomerates all the tools that are required to work with Microsoft Excel in one place. There are different tabs inside of the ribbon, and each tab will consist of certain types of tools. You can use different tools inside Microsoft Excel, simply by pointing and clicking them on the Ribbon display. We will look at how this can be done in this Microsoft Excel lecture.
Autofill is another useful feature inside Microsoft Excel. It is by the use of autofill that we are able to get so much done so quickly inside Microsoft Excel. We can just start typing some content, and Microsoft Excel will automatically detect patterns related to it. If we had typed up something similar earlier, it will prompt us to use the autofill feature. It is a tremendous tool that helps us become more efficient inside Microsoft Excel.
In this Microsoft Excel lecture, we will look at a group of cells that contain certain numbers. We will then try to find the maximum, minimum, average and the count of numbers from this data. We have ordered this data inside Microsoft Excel and will make it easier to find these special values, right here inside Microsoft Excel.
Microsoft Excel offers different ways to format data inside it. There are different ways to format cells and the data in those cells, here inside Microsoft Excel. There are options that help us modify the cell width, cell height and highlighting. We can actually change the type of data that the cell can hold inside Microsoft Excel . We will look at all that, and more in this Microsoft Excel lecture.
In this lecture, we will look at the concept of Conditional formatting inside Microsoft Excel. Conditional formatting easily spots trends and patterns in data and represents it in visual forms. There are many conditional formatting tools inside Microsoft Excel , such as icons, bars, Color Scales and so on. We will look ath the conditional formatting tools here inside Microsoft Excel, in this Microsoft Excel lecture.
There are different types of fonts available inside Microsoft Office. Microsoft Excel makes use of all these fonts. You can also download other fonts from the web. Different fonts have different purposes, and therefore, are designed in different ways. We will look at font styles and font sizes inside Microsoft Excel, and how they influence our spreadsheet design, right here in Microsoft Excel.
Inside Microsoft Excel 2016, you can adjust the columns and rows the way you want it. You can adjust the width or height of a column or row respectively, and also adjust all the cells to fit perfectly by using the best fit option, here inside Microsoft Excel. Try it out.
Text wrapping is a feature inside Microsoft Excel that helps us to contain longer texts inside small cells without actually increasing the width of the cell. It is useful when you have to confine the cells to a certain area to print them later using Microsoft Excel.
Don't skip. This is the most fun part. Trust me. Some questions to test your knowledge from what you've learned so far.
In this Microsoft Excel lecture, we will look at how you can perform basic mathematical operations inside Microsoft Excel. We will cover addition, subtraction, multiplication and division.
In this Microsoft Excel lecture, we will look at the concept of mixed referencing. It is a way of using absolute and relative referencing together to achieve unique results inside Microsoft Excel.
In this Microsoft Excel lecture, we will look at how charts are used inside Microsoft Excel.
Flash filling is a unique feature inside Microsoft Excel. It detects patterns in the data that we have entered, and helps us fill an entire column or row, without typing it all over again. Take a look at this Microsoft Excel lecture to see how it is done.
In this Microsoft Excel lecture, we will look at named references. It will show you how you can give a particular name to a cell and reference it later to be used in calculations inside Microsoft Excel.
In this Microsoft Excel lecture, we will look at how you can calculate percentage inside Microsoft Excel. It is a very valuable operation inside Microsoft Excel.
PMT or Payment per period, is an important financial calculation. It is relevant, if you have borrowed money from a bank or another financial institution. Microsoft Excel can help you do basic accounting as well. In this Microsoft Excel lecture, we will look at how PMT can be calculated inside Microsoft Excel. Take a look and see how it's done.
PPMT and IPMT are the subsections of PMT. PPMT is the principal paid and IPMT is the interest paid. Microsoft Excel lets you calculate both these values along with the PMT. We will look at how all that is done, right here inside Microsoft Excel in this video.
Microsoft Excel is so versatile. In this microsoft excel lecture, we will look at a tool called the goal seeker. It is used when we have to get to particular value on a cell that can have variable values. Take a look.
Always do the quiz. That's what keeps you entertained.
Conditional statements are extremely important in any programming language or software. Microsoft Excel offers this feature in a versatile way. We can choose to fill the cells according to whether a logical criterion has been satisfied or not. We will look at how this is done inside Microsoft Excel, in this Microsoft Excel tutorial.
Hello and welcome back. In this Microsoft Excel lecture, we will look at a powerful function inside Microsoft Excel. It is called the VLOOKUP or Vertical Lookup. There is another one called Horizontal Lookup or HLOOKUP as well. But we usually use the VLOOKUP function. If you want to use the HLOOKUP table, you will need a table that’s wider in the horizontal direction.
Essentially, vlookup is used to lookup and compare data between tables and then take a specific action, depending on the values that are returned. There are two different ways to implement this. You can use vlookup to look up exact matches, or you can use to look up approximate matches. Let us take a look at this particular worksheet here. I got this worksheet from a google search for vlookup example Microsoft Excel worksheets. I have uploaded this to the resources section so that you can use it as well.
If you take a look at this Microsoft Excel worksheet, you can probably understand what we are going to do. We have to fill in the reduction rate in this column, by comparing the subtotal values to those in the reduction rate table over here. This table will be called the Lookup table from now on. We can also use a table from another Microsoft Excel worksheet. That’s fine too.
So this is how it will work. The vlookup function will compare the subtotal value with the values here in the left column of this table. If the threshold hasn’t reached, the value above it will apply. For example , if the value is 999, the reduction rate will be 2 percent, and so on.
Now, if you want to use Hlookup as I had mentioned earlier, you need a horizontal table. You can actually create such a table by copying the data from this table and pasting its transpose.
For this just drag to select the data, right click and copy it, and let’s paste it over here. Right click and instead of simply pasting, let’s go through these paste options, and we have a paste transpose option here. Click that and we will have pasted a transpose of the lookup table.
So I will now use the vlookup function on this cell here. I am going to type in the vlookup function in its right format. Microsoft Excel will give you a hint on how to do it when you actually start typing it.
Start typing, equal to vlookup, open bracket, the value that you want to lookup, which is this one over here, comma, the entire array of the lookup table, which is this selection that I am making. Also note that this selection will be used to fill in the values throughout this column here. So we have to make this absolute. Otherwise it will just keep on going. So press F4 to do that. Now , comma, the column of the lookup table that has the answers, which in this case is 2 , because it is the second column of the look up table. Note that it is not called I. It’s called 2 because again, it is the second column of the lookup table. Now close the bracket and press enter. Now if you’ve done this correctly, you will see the value 3 percent, or you will see 0.03 in which case , just select that entire column and format it as a percentage column.
So now in the adjacent total column, we will get the reduced values, so 3 percent is reduced from the subtotal. Therefore, this value will be 97 percent of this value. There is also a small caveat to this procedure. The left column of the lookup table must be sorted in ascending order. If not, whatever comes first will be applied, and the data that you get will be erroneous.
Hello and welcome back. SO, in this Microsoft Excel lecture, we will use the vlookup function to lookup exact matches. We had looked at approximate matching in the earlier lecture. Let’s take a look at this worksheet here. We have different products, attributes and a department column that’s blank. So, obviously that’s the Microsoft Excel column that we have to fill in. And then to the right, we have the lookup table that we will use. It has the same products, their departments filled in, and category. So we are going to use this department info to fill in this column here, after comparing the products on this table to this one, to find an exact match. Now when I say exact match, I mean, exact match. Even the spaces have to match. So if just type an extra space after this product right here, it will not match with the ones here when we use the lookup function later. So that’s something you have to keep in mind. Don’t type in anything that you don’t need. Now let’s just zoom out a little bit before we proceed. We can also hide some of these columns that we don’t need so that we have some working room. You can later unhide everything by clicking here, right clicking and choosing unhide. For now I will just drag these Microsoft Excel columns here, right click and choose hide.
Okay, so now let’s get down to work. Click this Microsoft Excel cell and start typing, equal to , vlookup, open bracket, the cell that we have to lookup, which is this one over here, comma, and the range of data that we have to search, which is actually a long list and it will be tedious to drag and select the whole thing, so instead of doing that, we will just drag these two columns to select the entire column. And since we have selected two entire columns, we don’t have to make them absolute selections because, hey, we have selected the whole thing anyway. Also, the function is looking for an EXACT match this time. It wouldn’t match with something approximate anyway. OK, so now comma, the column which has the answer, which is this one, so type 2. You know why it is two. It’s because that’s the second column. Then for the exact match version of Microsoft Excel vlookup, we have one more argument, which is a zero or “false” to denote that it is an exact match. I know it doesn’t sound right. Why is it zero or false if it is a match! Yea But it is !” So type zero, close the bracket and press enter. There you have it. Drag down to fill up the whole table this way. You can also fill in the category column here with this technique. The difference will be that you will have to drag 3 columns here instead of two as the range, and the answer will be in Microsoft Excel column number 3 instead of column number 2.
Hello and welcome back! We’re gonna be looking at Countif and Sumif functions in this Microsoft Excel video.
Countif is used to count the number of items that conform to a certain criteria, and sumif is used to sum up the values that conform to a certain criteria. Take a look at this Microsoft Excel worksheet here.
We have a table of products, with category and departments , and on the right we have a Microsoft Excel table that we will use to count the number of items belonging to each category. We have the different departments here, including kitchen, dining and bedroom. We also have a column to list the count of items here. We are going to find the count of the items by looking at the items here in this Microsoft Excel column. For this I am going to type the countif function here. Equal to, countif, open bracket, This entire column, because we are gonna be searching this entire column, and we are also going to be looking for exact matches to this word here. SO, again comma, we are looking for this word, so click that cell. Close the bracket and press enter.
Now you can see the number of items, and then drag it down to fill in the rest of it. So that’s how countif works inside Microsoft Excel .
To the right of this Microsoft Excel column, we can see a revenue column, where we can add up the revenue of those specific departments. For this we use a sister function of countif, which is called sumif. This is how it works.
Equal to sumif, open bracket, we will be looking in this column, comma, we will be looking for the word kitchen, comma, and then the revenue column. Close bracket and press enter. So that’s the total revenue for kitchen stuff. Now drag this down and you will get the rest of it. Now there is another one called averageif which gives you the average cost. It works the same way.
Equalto averageif, comma, This column where are gonna be looking, comma, the kitchen department, comma, the price column this time, because we have to get the average of the prices. Close bracket and press enter. Drag it down and you will see the rest of it.
Okay these are three important mathematical functions that you will use frequently when you work with Microsoft Excel . Try them out and be back for the next video. Thanks for watching.
'Pivot tables' are an important part of Microsoft Excel. It helps you filter data that is significant, from a larger group of data in a Microsoft Excel worksheet. You can then represent the data, the way you want to. In this Microsoft Excel tutorial, we will look at how we can create a one dimensional and a two dimensional pivot table, right here inside Microsoft Excel.
When you work with a large worksheet inside Microsoft Excel, you will want the header and the lef-most column to stay in place as you scroll through the Microsoft Excel worksheet. This is possible through the use of freeze panes inside Microsoft Excel. We will look at how it is done in this Microsoft Excel tutorial.
This whole section will be dedicated to one of the most powerful features inside Microsoft Excel. Macros. So in this lecture, I will explain the concept of macros. So what exactly is a macro ? A macro is simply a set of operations inside Microsoft Excel, saved as a single repeatable step. It is a way to automate your work.
For example, let us look at this worksheet here inside Microsoft Excel . We have a bunch of data here, of different data types. Let’s say I want to modify this cell right here. So I will click this cell. Then I am going to apply certain operations on this cell. I am going to give it a border. I am also going to give it a yellow highlight, make the text bold and italic, and then underline the text. I am also going to change its font color to red. Okay, so that’s about six operations that I have applied on this cell. And then later, maybe I want to apply it to this cell, and this cell and so on. I want to apply this same effect to random cells all over this Microsoft Excel worksheet.
If that’s the case, I will have to spend too much time doing these same operations over and over again. Macros to the rescue. I can save this sequence of steps as a macro, and then just apply it later with just a single click. And it can include elaborate operations like applying a mathematical formula or something like that. It is not confined to just changing the appearance.
Hello and welcome back! As promised, we will record a very simple macro in this lecture. We will move on to complicated stuff later. For now, let’s just record what we did earlier, as a macro. As you can see, I have already recorded a macro simply to practice and I have applied it to some of these cells. This is what we will be doing here.
There are different ways to get to recording a macro inside Microsoft Excel . The simplest way is this button right here, on the bottom left of the screen. It shows a picture of the worksheet with a small record button on top. That is a record macro button inside Microsoft Excel. We also have the search bar here, where we can just type macro, and a record macro option just shows up. We also have a dedicated macro group right over here, in the view tab. Click on that downwards arrow and you can see the options; View Macros and Record Macros.
So, we can choose any of these options to start recording a macro. I’m gonna choose this button right here. Click on it and a new box opens up. We have to enter a macro name, first of all. I’m going to call it Yellow cell. And then we have to assign a shortcut key. And the shortcut must be a letter plus the control key. I will assign the letter M. Now, we have different options to save this macro. We can store this macro in this workbook, or a personal macro workbook, or a new workbook. Then, we also have a description option so that we can understand what this macro was all about, when we take a look at it later. So, let’s just type in a description : This macro will highlight the cells with a yellow color. It will make the text bold and also give it a border. Now, when we press okay, the macro will start recording, and anything we do after this point, will be saved as a step in this macro, so that we can reuse it later by pressing Control+M. So, now that I already have this cell selected, I am going to apply the operations now. I will just go over here and give it a border, then highlight the cell with a yellow color. We will also turn the text bold. And we will change the font color to red. Okay, so that’s all I want to do with this macro. Now that I’m done, I can just go over to this button here, it’s a grey square stop button… and STOP. We have recorded the macro with a keyboard shortcut control + L. Now let’s go over to this cell and press control L, and again and again and again. See how that works! Now think of how much time it will save you when you work with Microsoft excel in the future.
Now, if you have to view the macros that have been created already, just go over here to the view tab under the macros group. Click on it and you can see that there is a View Macros option. There is also a keyboard shortcut ALT+F8. Now you can see the different macros that you have created. You can run the macros, edit or delete the macros right here. This is useful when they are no longer useful, or if you run out of letters to assign to the macros. And that’s another point. You cannot assign the same letter to different macros obviously. It will give you an error. Now, if you want to edit the description or the shortcut key of the macro, you must press the options button right here and not the edit button. If you press the edit button, this is what happens. It will open up a Microsoft visual basic window where you can edit the function of the macro. That’s advanced stuff, we are not going to cover that in this lecture.
In this lecture, we will study macros in detail inside Microsoft Excel. We had already created a macro in the earlier lecture. One thing that I forgot to mention in that lecture, is the fact that the macros that you run on a worksheet inside Microsoft Excel , cannot be undone. You cannot press ctrrl+z and make it go away. So, that’s something that you have to keep in mind when you apply a macro. But in that case, we can actually correct it by manually taking away the formatting. But when you work with a complicated macro inside Microsoft Excel , like the one we will create in this lecture, it will be quite difficult. The only option you will have then, will be to close the file and reopen it without saving the file. Therefore, it is a good idea to save the file right before you start creating the macro inside Microsoft Excel . It is also advisable to write down the steps that you want to perform while recording the macro, because, literally every action that you perform will be recorded in the macro. Another thing that I wanted to show you is that you can save the macro outside of the workbook that you are in. The option is right here when you try to record a macro. Instead of storing the macron in this workbook, just choose to store the macro in the personal macro workbook. Now, its called the personal macro workbook because it will only available on your local machine. It will not sync with any other device that you may be using.
Okay, so having said all that , let’s move on to the meat of this Microsoft Excel lecture. We have the same worksheet that we had opened up earlier. We will record another macro, so that you can cement the concepts that you learned in the earlier lecture. What we are going to do in this macro, is to find and select the numbers in this worksheet that were not created by any kind of formulas. This can be done easily, and is quite useful, especially when you face a new worksheet that you have never seen before. When someone else presents a worksheet that they created, you don’t have much of an idea as to which of those cells were manually input and which ones were created using formulas and calculations. For this, all you have to do is go over here to the home tab, and to the right end, we have this option here that says Find and Select. Click on it and you can see the different options including formulas and comments. We are going to go into this option here that says “GO to special “. Go in there, and we have the option to look for constants and then if we uncheck everything under formulas except for numbers, and press ok, it will highlight all the constants that are not made up of formulas. Then, we can highlight all such numbers by pressing this button here.
So, this is the macro that I want to create inside Microsoft Excel . I am going to do so now by pressing a random cell on the worksheet. Then I will press the record macro button. I will name it “find numbers”. The keyboard shortcut this time will be control shift m. So, I will hold down the shift key as I press m. And then in the description just type something. See, the description is very useful if you are creating a macro that’s gonna run for several minutes with so many operations involved. Otherwise, it need only be a couple of words. Now press ok, and I am recording the macro. Go to find and select, Go to special, tick constants and uncheck everything except numbers. Press ok. Go and highlight the cells. And then stop recording the macro. We have saved that macro in the personal macro workbook, so it can be used on any workbook.
Now let’s look at how we can add a shortcut icon to access this macro, if we were not inclined to using the keyboard shortcut all the time. For this, go over here to the status bar where we have this small downwards icon.click the icon and select “More commands”. This takes us into an elaborate options menu, where we can choose commands from anywhere to be added to that bar. We will click right here and choose macros. We can see that it has listed the macro that we just created. We can even save it with a specific icon, by clicking modify here. Choose the icon that you want to use. Change the display name to something relevant if you want. Then press ok. You can see that we now have an icon for that macro, right over here. You can later remove it from the same menu, if you want to.
As I had mentioned in the earlier lecture, we will now look at how you can create a button and then run a macro using that button. It is fairly simple, and is also quite useful when you want a macro to be available anytime you want. It is also useful to make the Microsoft excel worksheet experience very intuitive. For example, if you want to take the user from one Microsoft excel worksheet to another, you can just record a macro to do this and then assign this macro to a button. The user can then click that button to go to the next Microsoft excel sheet or any Microsoft excel sheet for that matter.
Now, we already have a macro recorded, that looks for numbers that are not filled in using formulas. We have to create a button to assign this macro to.
For this, go to the insert tab right over here. We have a shapes menu in this tab, which is over here. Click that menu. We have a bunch of different shapes to choose from. Choose the one that you like and drag it into position. You can then resize it and fill it in with some text by rightclicking and choosing edit text, or simply doubleclicking on it. Once it is position, Just right click and choose assign macro. You might have to click a couple times. Sometimes it shows up a different Microsoft excel menu. Now, you have this window, where you can choose which macro to assign to the button. Choose the macro that we just created. Press OK. Now press outside of the rectangle and that macro has been assigned to this button. Now if I just press this button, it works the way we want it to. That’s it. That’s how you assign a macro to a button.
Microsoft Excel offers versatile printing features. In this Microsoft Excel lecture, we will look at what those print features are and how they can be leveraged to represent our data properly, in a printed format, here inside Microsoft Excel.
In this Microsoft Excel lecture, we will learn how to encrypt and password protect a Microsoft Excel document. This will prevent other users from accessing it freely. You will have to give them the password for their being able to access the Microsoft Excel document. Watch this Microsoft Excel lecture to learn how.
When you are at the computer all day, and you feel tired, you are bound to make a couple of mistakes filling up the worksheets. Microsoft Excel is here to help. You can predefine a set of data, which you can use to fill a larger Microsoft Excel with. This is what is called Data Validation inside Microsoft Excel. Try it out.
Very often, you will have to export a Microsoft Excel worksheet outside of Microsoft Excel. Usually, this happens when you have to present the Microsoft Excel in a word document. This can be done by linking the data in the Microsoft Excel worksheet with the Microsoft Word document. See the lecture to understand how it can be done, here inside Microsoft Excel.
CSV stands for Comma Separated Values. This is a file type that large businesses and corporations use to transfer data between different types of systems. Microsoft Excel offers a way to work with these files inside Microsoft Excel. See the tutorial to understand how to work with CSV text files inside Microsoft Excel.
Microsoft Excel offers you different options to share your work. One of the most popular options is to create a PDF document from the Microsoft Excel spreadsheet. This is done by setting a print area on the Microsoft Excel document, and then exporting the Microsoft Excel spreadsheet as a PDF. See the lecture to understand how it's done.
MADE OVER $50,000 WORKING ONLINE PART TIME!
I'm Prem and i am a multipotentialite. If you don't know what that means and want to know more , just google it. It is a fancy word for someone who is interested in vastly different areas and industries. We have been told from a young age,either to find our passion and stick to it, or to get a job. I believe that is bad advice in this exponentially advancing digital world, because there is so much out there that you will be passionate about and want to work for. For example, you could be a programmer who also likes to play guitar professionally. I believe that you can do both, if you carefully manage your time.
About Me :
I am interested in many areas and i strive to learn as much as i can about each of them, and so should you if you think you are a multipotentialite too. If not, that is fine too! We need all kinds of people :)
Here is what i have done so far in life:
>> Born in the 90's. Grew up in a middle class family, in the tropical paradise, called Kerala. Google it.
>> Was put into the regular school system, just like most kids. We did not know any other route, and neither did our parents.
>> Went to University. Studied Civil Engineering, and then took my Masters in Computer Applications and another masters in Structural Engineering and Construction Management.
>> Worked at a big construction company in India, as a Structural Engineer. I liked the work, but i wanted to do a lot more. I am a high-energy person. I worked for about 8 hours a day, and was bored for the rest of the day because i did not have much else to do. Therefore, I started learning about whatever i was interested in.
>> Interests include : Software, Programming and Web Development, Business, Marketing, Graphic Design. Cinema, Video and Audio Production, VFX, 3D Modelling and Animation, Advertising, Music and Fine arts.
>> So, with all these interests, i could not confine myself to just a day job. I went and worked in the Kerala Film Industry. Started out as a set assistant, worked later as a Focus Puller, Assistant Cameraman, Worked in post production as a VFX artist, and then became an assistant director and right when i was about to plan my first directorial project, my family immigrated to Canada, and later, I came along.
>> Restarting everything here in the west. What i've learned; Time is the most valuable asset. When you indulge in any activity, ask yourself this; " Could i be doing something better?"
Over the course of the next 5 years, i intend to create atleast 30 courses, spanning every area that i have studied and worked in.
Take a look at what i have created, enroll and leave a feedback :) Thanks for reading :)