Excel Advanced Tutorial | Learn MS Excel | Microsoft Excel 2020
Excel Advanced Tutorial | Learn MS Excel | Microsoft Excel 2020

Excel Macros

By using Macro we can record a task. It is very useful when you have a task that you need to perform repeatedly. It speeds up the procedure and assures accuracy.

How to open a macro:

Open View tab. On the right end of the Ribbon you will find the macro button.

Click on the arrow shown at the bottom of macro button. It will display three options; view macros, record macro and use relative references. See the image:

Macros in Excel 1

View Macro: Clicking on view macro option opens the macro dialog box where you can run, edit or delete a macro.

Record Macro: Click on this option to record macro. On clicking, it displays the record macro dialog box where you can fill the details of your macro.

Use Relative Reference: This option helps record macro in relative mode. Using relative mode you can run the recorded data anywhere on the worksheet.

Steps to record a macro:

Click on the Record Macro option. It will display the macro dialog box. Fill the details of macro then click ok. See the image given below.

Macros in Excel 2

Now perform the task that you want to record. When the task is done, click on the arrow below the macro button it will display three options, out of these options select stop recording. See the image given below:

Macros in Excel 3

Now the macro is created and is ready to use, to run the macro click on the Macro button. You can also click on View Macro option; it will display the dialog box with multiple options like Run, Edit and Delete. Click on the Run option, it will display the recorded task. See the image given below:

Macros in Excel 4

Excel Sumif Function

The SUMIF function is used to sum up the values of some specific cells of a column, the cells which meet certain criteria.

The syntax of SUMIF function is:

  1. =SUMIF(range,criteria, [sum_range])

So, the SUMIF function has three main parts:

Range: It refers to range of cells that you want to evaluate to shortlist the cells that meet the given criteria.

Criteria: It refers to conditions that tell which cells are to be added. It can be a number or a text.

Sum _range: It provides the actual cells that are to be added. It is an optional argument. If we omit this part of the function the SUMIF function treats “range” as “sum_range” thus adds the cells of the range argument.

See the following example:

The company wants to know the total salary of the marketing department. In this case, by applying the SUMIF function the company can add the salaries of all employees of marketing department. See the image given below:

sumif function in Excel 1

=sumif(C3:C12,”marketing”,D3:D12)

Range: C3:C12 is the range to be evaluated to shortlist the cells that belong to marketing department.

Criteria: “Marketing” is the criterion to evaluate the range or to find out the cells that are to be added.

Sum_range: D3:D12 is the sum_range that provides the actual values to be added based on the given criterion.

 

Excel Vlookup

Vlookup is an advanced Excel function that helps extract values from the database. Vlookup can filter large volume of data to provide the appropriate values based on the given conditions.

V lookup can be used in two ways, to find an exact match and to find the closest match. In this example, it is used to find an exact match.

Using Vlookup to find an Exact Match:

In this example, A company wants to know the incentive and remark for each employee. Vlookup is used to filter the database and extract correct incentive and remarks for the employees.

See the image given below, one table is showing the incentive and remarks for the number of items sold. Second table is created by the company to display the extracted figures; incentive and remarks for the employee.

Using vlookup, on entering the number of items sold by an employee in column C, the Excel automatically fills the corresponding incentive figure and remark for the employee.

vlookup in Excel 1

Before applying the vlookup, select the data table excluding the headings and name it in the name box, like we named it “salestable” in this example. See the image given below:

vlookup in Excel 2

Now we will use vlookup to retrieve the correct incentive and remark for Tom.

Type the vlookup function in the cell where you want to display the incentive. D12 is the cell in this example, where we want to display the incentive for Tom.

  • In the cell D12 type: =vlookup(
  • Now type what you want to find or look for in the data table. In this example, it is the value of cell C12, so type: C12,
  • Type the name of the data table containing the information and a comma: salestable,
  • Type the column number, the column of data table from where you want to retrieve the information then type a comma. In this example, it is second column of data table so type: 2,
  • Now type “false” which tells excel to retrieve the information for exactly the same value that is in cell C12, not anything else close to it. In this example, it will retrieve information for 9 items sold from the second column of data table. So, if figure 9 is not available in data table it will display an error.
  • Close the bracket and press the Enter key. You will get the incentive for Tom.

vlookup in Excel 3

Now to retrieve the remark for Tom, type the same function in cell E12, but select the third column of data table instead of second as remarks for employees are given in the third column of the data table. After getting the remark for Tom using fill handle you can get the incentive and remarks for other employees. See the images given below:

vlookup in Excel 4 vlookup in Excel 5

Using Vlookup to Find Closest Match

In the previous example, vlookup is used to find an exact match. But what if Tom sells 6 items then how we retrieve information from the data table. In this case, we will use vlookup to find the closest match for our lookup value.

For that we will type “true” instead of “false” in the formula. It tells Excel to find the closest match for the vlookup value if it doesn’t find an exact match in the data table.

Vlookup always looks for smallest closest match available in the data table so in this case for 6 items sold by Tom it will find the incentive for 5 items sold as it is the lowest closest match for 6 items sold. So it will retrieve incentive 100 from the second column of the data table.

See the example:

Items sold by the employees are not matching with the data table. So “true” is used in the formula to find the closest match for the number of items sold by the employees.

vlookup in Excel 6

Again to find the remarks for the employees “true” is used instead of “false” in the formula. See the image given below.

vlookup in Excel 7

After getting the details for Tom use fill handle and get the details of other employees.

vlookup in Excel 8

Using Vlookup to Match Lists

Vlookup can be used to match lists, to see if the lists are matching or there are some missing values. In this example, we use vlookup to retrieve the name of employees to list 2 from list 1 data. See the image given below:

vlookup in Excel 9

The vlookup function is: =vlookup(E4,List1,2,false)

  • In this function we have named the list1 data table as “List1”.
  • E4 is the cell which we want to look for in the “List1”.
  • “2” is the second column of list1 from where we want to retrieve the information.
  • “False” is used as we are looking for an exact match.

Vlookup displays “#N/A error code” for the employee codes of list 2 that are not available in list 1 data.

What is a Pivot Table?

A pivot table is a data summarization device that is utilized in the context of data handling. Pivot tables are utilize to summarize, sort, rearrange, group, check, aggregate, or standard record stored in a database. It permits its clients to change columns into rows and rows into segments. It permits grouping by any information field. Pivot tables are the ideal arrangement when we have to summarize and analyze a huge number of data.

Starting with a blank Pivot Table

1. Open the document GL Pivot Table Data.xlsx. The worksheet has a query, including FY11 costs from the Economic Data Warehouse.

What is Pivot Table

2. Spot our cursor on any cell in the information.

Note: Ensure your information is in a tabular format, and there are no clear rows or columns. Additionally, every column must have a different heading that is one row high.

3. Go to the ribbon and open theWhat is Pivot Tabletab.

What is Pivot Table

  • Click on theWhat is Pivot Tabledown arrow to get a list of choices.

Creating Your First Pivot Table

To make a pivot table:

1. Click on a cell in our table of information

2. Pick “PivotTable” from the Insert label on the ribbon

What is Pivot Table

3. Click on the select table/range button as appeared in the picture above

4. The default is New Worksheet under where to put up the PivotTable

5. Acknowledge the defaults by tapping on theWhat is Pivot Tablebutton.

6. We will get the following small window.

What is Pivot Table

7. Press in cell address A1

8. Press Ctrl + A on the keyboard to choose all the record cells

9. Your small window demonstrated now occur as follows

What is Pivot Table

10. Press on Close button to return to the options window

11. Press on the OK button.

12. A clear PivotTable and Field List will occur on a new worksheet.

What is Pivot Table

13. When you make a PivotTable, you’ll have to choose which fields to include. Each field is just a column section from the source information. In the PivotTable Field List, check the container for each field you wish to include.

What is Pivot Table

14. The selected fields will be included in one of the four regions below the Field List. In this example, the Order ID field has been added to the Rows zone, while the Amount has been added to the Values zone. On the other hand, you can click, hold, and drag a field to the desired region.

What is Pivot Table

15. The PivotTable will evaluate and outline the selected fields. In this example, the PivotTable displays the amount sold by each customer.

What is Pivot Table

Refreshing the Data

If we transform any of the information in our source worksheet, the PivotTable will not update automatically. To physically update it, choose the PivotTable and afterward Analyze → Refresh.

What is Pivot Table

Refresh Data when opening a file

There is an alternative that can be turned on that will refresh the information when opening the document. To set an option to upgrade PivotTable data when we open our workbook automatically, do the following:

1. From the PivotTable Tools press Options Tab then within Pivot Table Group, Options command.

What is Pivot Table

2. Press on the Data Tab and tick Refresh the information when opening the record.

What is Pivot Table

3. Press on OK.

Pivoting Data

Probably, the best thing about PivotTables is that they can rapidly pivot or rearrange information, permitting us to look at our worksheet data in a various manner.

To change the row

1. Press, hold and drag any current fields out of the rows zone. The field will vanish.

2. Drag the another field from the Field List into the rows region. In this example, we’ll utilize the First Name field.

What is Pivot Table

3. The PivotTable will change, or pivot, to display the new information.

To add columns

PivotTable has only displayed one column of information at a time. To display various columns, we will have to add a field to the Columns region.

1. Drag the field from the Field List into the Columns region. In this example, we’ll utilize the Region field.

2. The PivotTable will contain multiple columns.

Filters

Filters can be utilize to precise down the information in our PivotTable, permitting us to see only the data we need.

To add a filter

1. Drag the field from the Field record to the Filter region. In this example, we’ll utilize the First Name field.

What is Pivot Table

2. The filter will occur raised the PivotTable. Press the drop-down arrow, and then check the container next to Select Multiple Elements.

3. Uncheck the container for any elements you don’t want to contain in the PivotTable. In this example, we’ll uncheck the container for a few different First Names, then Press OK.

What is Pivot Table

4. The PivotTable will conform to mirror the changes.

Grouping Data

In the row and column label region of a PivotTable document, you can group the elements in a field in a custom method. Grouping the information can assist you in isolating a subset of data that fulfils your particular needs, and that cannot be easily grouped in different manners, such as sorting and filtering. You may wish to group utilizing one of the following:

  • Group numbers in numeric fields
  • Group dates or times
  • Group selected elements

To group fields

1. Choose the information in the PivotTable.

2. Right-click on selected information, and press Group.

What is Pivot Table

3. Press OK.

To group by date

1. Press any unit inside the Date segment.

2. Right-click and click on Group.

3. Select Months and press, OK.

What is Pivot Table

4. The PivotTable will conform to mirror the changes.

 

Excel ISNA Function

ISNA function helps to get rid of #N/A code error

Using ISNA function we can tell Excel to leave the cell blank or to display any desired text like “not found” instead of displaying #N/A code error. See how the function is used in the example:

The function is:

  1. =if(isna(vlookup(E4,list1,2,false)),“”,vlookup(E4,list1,2,false))

The above function has three parts:

First part: =if(isna(vlookup(E4,list1,2,false)), It means if the function don’t find the value of cell E4 in List1 then display second part.

Second part: “”, It means leave the cell blank. You can write text between quotation marks that you want to display instead of a blank cell.

Third part: vlookup(E4,list1,2,false)) It means if the function finds the value then perform vlookup as usual. See the images given below:

isna in Excel isna in Excel

 

How to Find and Remove Duplicates in Microsoft Excel?

Finding Duplicates

1. For finding the duplicates, we will be utilizing the conditional formatting function. In this example, we need to discover if any name has been frequently. So first, we will choose the Name column.

How to Find and Remove Duplicates in Microsoft Excel

2. Press on the conditional formatting option accessible at the right-hand side of the home icon.

How to Find and Remove Duplicates in Microsoft Excel

3. When we press on the dropdown of conditional formatting, we will take different options. Select the first option ‘Highlight Cells Rules’ and the sub-option as ‘Duplicate Values.’

How to Find and Remove Duplicates in Microsoft Excel

4. We will view a duplicate values dialog box, whereas we can choose our formatting option. In this case, we choose ‘Light Red Fill with Dark Red Text.’

How to Find and Remove Duplicates in Microsoft Excel

5. Press on OK, and we will view that the duplicate names have been highlighted in red color.

How to Find and Remove Duplicates in Microsoft Excel

Remove Duplicates

1. We will use a similar example as above for information the removal of duplicates. To remove the duplicates, choose a random cell and select the option of Remove Duplicates from the Data tab in the ribbon.

How to Find and Remove Duplicates in Microsoft Excel

2. The following dialog box shows up.

3. Leave all checkboxes checked and press OK.

How to Find and Remove Duplicates in Microsoft Excel

4. After Clicking OK, all the duplicate values are removed, a pop up will occur, informing us about the duplicates removed and the unique values that remain. As noticed above Rahul remains since the Gender mentioned is different.

How to Find and Remove Duplicates in Microsoft Excel

5. If we want to delete all values which have a similar name and gender change. Click 2nd step just unmark the Column B checkbox.

How to Find and Remove Duplicates in Microsoft Excel

6. After Pressing on OK, we will see that even the row with Rahul as the name has been deleted.

How to Find and Remove Duplicates in Microsoft Excel

 

How to create a drop-down list in Excel?

A drop-down list is an outstanding way to give the client an option to choose from a pre-defined list.

It very well may be utilized while getting a client to fill a structure or while making an intuitive excel dashboard.

Drop-down lists are very regular on social sites and apps and are very natural for the client.

Create a Drop-down List

To create a drop-down list in Excel, perform the following steps.

  1. Using Data from Cells.
  2. Entering Data Manually.
  3. Using the OFFSET formula.

Using Data from Cells.

1. We have a list of elements, as demonstrated below:

How to create a drop-down list in Excel

2. Select a cell where we need to make the drop-down list.

3. Click on Data and select Data Tools → Data Validation.

How to create a drop-down list in Excel

4. The ‘Data Validation’ dialog box shows up.

5. In the Settings icon, choose List as the Validation criteria

6. When we choose List, the source field shows up.

How to create a drop-down list in Excel

7. In the source field, enter= $L$18+'[Book2.xlsx] Sheet 2′!$A$3:$A$8 or basically press in the Source field and choose the cells utilizing the mouse and press OK. This will embed a drop-down list in cell C2.

8. Ensure that the In-cell dropdown alternative is checked (which is checked by default). If this option is unchecked, the cell does not display a drop-down, we can manually enter the values in the list.

How to create a drop-down list in Excel

By Entering Data Manually

In the example, cell references are utilized in the Source field. We can also add elements precisely by entering it manually in the source field.

For example, suppose we need to display two options, yes and No, in the drop-down in a cell. Here is how we can precisely enter in the data validation source field:

Select a cell where we need to generate the drop-down list (cell C2 in this example).

Click on Data and select Data Tools -> Data Validation.

How to create a drop-down list in Excel

In the Data Validation dialogue box, inside the Settings icon, select List as the Validation criteria.

When we choose List, the source field shows up.

How to create a drop-down list in Excel

In the source field, enter Yes, No.

How to create a drop-down list in Excel

Ensure that the In-cell dropdown option is tested.

Press OK.

How to create a drop-down list in Excel

This will make a drop-down list in the selected cell. All the elements recorded in the source field, isolated by a comma, are recorded in various lines in the drop-down menu.

All the things entered in the source field, isolated by a comma, are shown in various lines in the drop-down list.

Dynamic Drop-down List

We can utilize a formula that refreshes our drop-down list naturally when we include an element to the end of the list.

1. In the first sheet, choose cell B1.

How to create a drop-down list in Excel

2. In the Data icon, in the Data Tools group, press Data Validation.

How to create a drop-down list in Excel

The ‘Data Validation’ dialog box shows up.

3. In the Allow box, press List.

4. Press on the Source box and enter the formula: =OFFSET (Sheet2! $A$1, 0, 0, COUNTA (Sheet2! $A: $A), 1)

How to create a drop-down list in Excel

5. Press OK.

6. On the second sheet, add a new element to the end of the list.

How to create a drop-down list in Excel

Remove a Drop-down List

To remove a drop-down list in Excel, perform the following steps.

1. Select the cell with the drop-down menu.

How to create a drop-down list in Excel

2. In the Data icon, in the Data Tools group, press Data Validation.

How to create a drop-down list in Excel

The ‘Data Validation’ dialog box shows up.

3. Press Clear All.

How to create a drop-down list in Excel

4. Press OK.

Dependent Drop-down Lists

Sometimes, we may have more than one drop-down list, and we need the elements shown in the second drop-down to be dependent on what the client chooses in the first drop-down.

These are known as dependent or conditional drop-down lists.

Here are the steps to make a dependent and conditional drop-down record in Excel:

  • Select the cell where we need the first (primary) drop-down list.
  • On the Data tab and Selects Data Validation. This will open the data validation dialog box.

How to create a drop-down list in Excel

On the data validation dialog box, inside the settings icon, choose List.

How to create a drop-down list in Excel

In the Source field, indicate the range that includes the elements that are to be displayed in the first drop down list.

How to create a drop-down list in Excel

Press OK. This will make the Drop-Down 1.

How to create a drop-down list in Excel

Choose the entire data set (A1: C8 in this example).

How to create a drop-down list in Excel

Click on Formulas and select Defined Names, then Create from Selection (or we can utilize the keyboard shortcut Control + Shift + F3).

How to create a drop-down list in Excel

On the ‘Create Named from Selection’ dialog box, check the Top row alternative and uncheck all the others. Doing this makes two names field (‘Employees Name’ and ‘Gender’). Employees named field defines all the employee names in the file, and the Gender named field represents the genders in the file.

How to create a drop-down list in Excel

Press OK.

Choose the cell where we need the Dependent and Conditional Drop Down list (E3 in this example).

Click on Data and select Data Validation.

How to create a drop-down list in Excel

On the Data Validation dialog box, inside the setting icon, make sure the List is selected.

How to create a drop-down list in Excel

In the Source data, enter the formula =INDIRECT (D3). Here, D3 is the cell that includes the main drop-down.

How to create a drop-down list in Excel

Press OK.

 

How to Merge cells in Microsoft Excel?

There are different methods we can merge cells in Excel.

One of the most used methods is utilizing the Merge & Center option in the Home tab.

How to Merge cells in Microsoft Excel

The issue with using Merge & Center is that it can combine the cells, but not the text within these cells. Let us say we have a data set, as shown below:

How to Merge cells in Microsoft Excel

If we select cell A1 and B1 and use the Merge & Center option, it will keep the text from the leftmost cell (A1 in this case) and delete the text from all other cells.

Excel is not utterly ruthless, though it warns you before this happens. If you try and merge cells that have text in it, it shows a warning pop-up letting you know of this (as shown below).

How to Merge cells in Microsoft Excel

If we go ahead and click OK, it will combine the two cells and keep the text from the leftmost cell only. In the above example, it will combine A1 and B1 and will show the Employee Name only.

MS Excel Tutorial
Excel Formula
Excel Worksheet
Excel IF Functions
Excel Fill Handle
Shortcut Keys
Excel Calculation
Excel Advanced Functions
Interview Questions

LEAVE A REPLY

Please enter your comment!
Please enter your name here

nineteen − 12 =