✔️ Excel : Microsoft Excel 2016 essentials
4.4 (8 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
989 students enrolled
Wishlisted Wishlist

Please confirm that you want to add ✔️ Excel : Microsoft Excel 2016 essentials to your Wishlist.

Add to Wishlist

✔️ Excel : Microsoft Excel 2016 essentials

A comprehensive Excel Training course for those who want to improve their Microsoft Excel skills & shine at the office!
4.4 (8 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
989 students enrolled
Created by Prem Chand
Last updated 5/2017
English
Price: $25
30-Day Money-Back Guarantee
Includes:
  • 3 hours on-demand video
  • 13 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Work with ​Microsoft Excel​​ like a professional
  • Easily perform simple and advanced mathematical calculations inside ​Microsoft Excel​​ .
  • Create and work with Pivot Tables inside ​Microsoft Excel​​ .
  • Sort , filter and arrange data inside ​Microsoft Excel​​ .
  • Perform financial calculations inside ​Microsoft Excel​​ .
  • Perform Data Analysis inside ​Microsoft Excel​​ .
  • Link a ​Microsoft Excel​​ spreadsheet with a Word Document , and generate reports this way .
  • Work with Comma Separated Value files inside ​Microsoft Excel​​ .
  • Encrypt and Password Protected a ​Microsoft Excel​​ document .
  • Record and use Macros inside ​Microsoft Excel​​ .
  • Gather data from external sources for ​Microsoft Excel​​ documents .
  • Use the VLOOKUP and HLOOKUP functions for exact matches and approximate matches inside ​Microsoft Excel​​ .
  • Calculate PMT , PPMT and IPMT inside ​Microsoft Excel​​.
  • So much more to come. I will make this the best Microsoft Excel course on the Internet.
View Curriculum
Requirements
  • The course was created with Microsoft Excel 2016. You can use Excel 2007,2010, 2013 or 2016. Most of the tools will work even with the 2007 version
  • A good grasp of basic computing tools will help, in addition to having a ​Microsoft Excel​​ installation
  • A Microsoft Excel installation.
  • This course is suitable for anyone who wants to learn Microsoft Excel regardless of their purpose.
  • Even if you are a school student and have no apparent use of Microsoft Excel, simply enroll. It will help you a lot in the future.
Description

!! 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. 

  1. Microsoft Excel worksheets, menus, and formulas. 
  2. The different ribbon options inside Microsoft Excel .
  3. The different formatting options inside Microsoft Excel , including conditional formatting and more.
  4. Basic and advanced mathematical operations inside Microsoft Excel .
  5. Data analysis and Financial Calculations inside Microsoft Excel
  6. Logical Functions and Looking up Data inside Microsoft Excel , using VLOOKUP and conditional statements.
  7. Pivot Tables inside Microsoft Excel . Gathering data and freeze panes inside Microsoft Excel .
  8. Using Macros inside Microsoft Excel .
  9. Data Validation and Linking inside Microsoft Excel
  10. Printing a document and exporting documents to different formats inside Microsoft Excel

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 !


Who is the target audience?
  • Anyone who wants to learn Microsoft Excel. The tools are explained in great detail.
  • People who already know Microsoft Excel, but want to improve their skills and master Microsoft Excel
  • This Microsoft Excel course will take you from a Beginner level to an Advanced Level. We will cover everything in between. It is suitable for everyone.
  • If you work in an office, in accounting, finance or any kind of business operation, you should take this course.
Compare to Other Excel Courses
Curriculum For This Course
44 Lectures
03:08:21
+
Microsoft Excel 2016 : All the Basic Stuff
5 Lectures 24:03

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.

Preview 05:27

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.

Preview 04:41

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.

Microsoft Excel 2016 : Ribbon Display Options inside Microsoft Excel
04:07

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.

Preview 04:53

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 2016 : Maximum, Minimum, Average and Count functions in Excel
04:55
+
Microsoft Excel 2016 : Formatting options inside Microsoft Excel
11 Lectures 40:01

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.

Preview 01:49

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.

Microsoft Excel 2016 : Conditional Formatting of cells inside Microsoft Excel
02:16

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.

Microsoft Excel 2016 : Font styles and Sizes in Microsoft Excel
02:43

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.

Microsoft Excel 2016 : Adjusting Column width and Row height inside Excel
02:42

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.

Microsoft Excel 2016 : Aligning and wrapping text inside Microsoft Excel
04:27

Microsoft Excel 2016 : Drawing Borders for Cells inside Microsoft Excel
02:12

Microsoft Excel 2016 : Formatting numbers and Currency inside Microsoft Excel
04:42

Microsoft Excel 2016 : Formatting Date and Time inside Microsoft Excel
04:57

Microsoft Excel 2016 : Conditional Formatting in detail inside Microsoft Excel
05:14

Microsoft Excel 2016 : Creating and working with tables inside Microsoft Excel
06:09

Microsoft Excel 2016 : Using Slicers in a table inside Microsoft Excel
02:50

Don't skip. This is the most fun part. Trust me. Some questions to test your knowledge from what you've learned so far.

"Never Skip the Fun" Quiz : Microsoft Excel Section 1 and 2 Overview
3 questions
+
Microsoft Excel 2016 : Important basic operations in Microsoft Excel
6 Lectures 25:03

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.

Preview 07:23

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.

Microsoft Excel 2016 : Mixed Referencing inside Microsoft Excel 2016
03:50

In this Microsoft Excel lecture, we will look at how charts are used inside Microsoft Excel.

Microsoft Excel 2016 : Using Charts inside Microsoft Excel
04:27

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.

Preview 02:27

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.

Microsoft Excel 2016 : Cross sheet and Named References inside Microsoft Excel
03:25

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.

Microsoft Excel 2016 : Calculating Percentages inside Microsoft Excel
03:31
+
Microsoft Excel 2016 : Data analysis and financial calculations in Excel 2016
4 Lectures 23:02

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.

Microsoft Excel 2016 : Calculating Payment per period PMT inside Microsoft Excel
07:57

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 2016 : PPMT and IPMT Calculation inside Microsoft Excel
06:08

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.

Microsoft Excel 2016 : Goal seeking inside Microsoft Excel 2016
03:15

Microsoft Excel 2016 : Using the Solver for Data Analysis in Microsoft Excel
05:42

Always do the quiz. That's what keeps you entertained.

"Never Skip the Fun" Quiz 2
3 questions
+
Microsoft Excel 2016 : Logical Functions & look up inside Microsoft Excel 2016
4 Lectures 18:57

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.

Preview 05:45

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.

Microsoft Excel 2016 : Using VLOOKUP for approximate matches in Microsoft Excel
05:44

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.

Microsoft Excel 2016 : Using VLOOKUP for exact matches in Microsoft Excel 2016
04:02

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.

Microsoft Excel 2016 : Countif, Sumif and Averageif functions in Microsoft Excel
03:26
+
Microsoft Excel 2016 : Pivot Tables inside Microsoft Excel
3 Lectures 14:15

'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.

Microsoft Excel 2016 : Creating and modifying Pivot Tables in Microsoft Excel
07:46

Microsoft Excel 2016 : Gathering data from external sources in Microsoft Excel
04:11

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.

Preview 02:18
+
Microsoft Excel 2016 : Macros inside Microsoft Excel 2016
4 Lectures 16:52

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.

Microsoft Excel 2016 : Introduction to Macros inside Microsoft Excel 2016
02:19

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. 

Microsoft Excel 2016 : Creating a Macro inside Microsoft Excel 2016
05:29

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.


Microsoft Excel 2016 : More about Macros inside Microsoft Excel 2016
06:37

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 2016 : Assigning a macro to a button inside Microsoft Excel 2016
02:27
+
Microsoft Excel 2016 : Printing inside Microsoft Excel 2016
1 Lecture 03:43

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.

Microsoft Excel 2016 : Setting and Adding to Print Area in Microsoft Excel 2016
03:43
+
Microsoft Excel 2016 : Security Features inside Microsoft Excel 2016
1 Lecture 04:01

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.

Preview 04:01
+
Microsoft Excel 2016 : Data Validation and Linking Inside Microsoft Excel 2016
4 Lectures 16:28

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. 

Microsoft Excel 2016 : Data Validation Inside Microsoft Excel 2016
03:19

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.

Microsoft Excel 2016 : Data Linking an Excel Spreadsheet with a Word Document
04:42

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.

Preview 05:07

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.

Microsoft Excel 2016 : Exporting a spreadsheet as PDF inside Microsoft Excel
03:20
1 More Section
About the Instructor
Prem Chand
4.1 Average rating
126 Reviews
4,239 Students
5 Courses
Engineer, Film Maker and Web Designer. A Multipotentialite.

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 :)