VLOOKUP and MATCH combo function in excel: How to create a dynamic VLOOKUP in excel ||DataWitzz

VLOOKUP and MATCH combo function in excel

We already know about the VLOOKUP function in excel and its functionality. VLOOKUP basically search value based on the given criteria. There are few limitations in VLOOKUP like it picks only the first value available in the reference table, its columns are static. In this article, we will be discussing how to make a dynamic column range in VLOOKUP in excel.

The problem with static column number in VLOOKUP

Let us understand this by the below example.

VLOOKUP and MATCH combo function in excel
VLOOKUP and MATCH combo function in excel

In the above image, we can see that we are trying to get the Quantity value of Product ID 1108. The Quantity of product id is in the Quantity … Continue Reading

How to create group in excel pivot table without using any formula || DataWitzz

How to create group in excel pivot table

In our previous article, we learned about what is a Pivot Table and how to create it. Now we will try to explore more about the pivot table. In this quest, we will try to understand how to make a group in an excel pivot table without using any formula. Group is making categories of our data to make it more readable.

How to create group in excel pivot table

One of the best features of excel pivot is to group data without using any complicated formula. In the below image we can see our data set Call Duration in the Rows area is a set continuous number.

How to create group in excel pivot table
How to create group
Continue Reading

Excel Pivot Table: How to use it and its basic understanding

Excel Pivot Table

Pivot table is the most powerful tool of excel by which we can create a report or summarize our data in a very meaningful way. It is used usually with big datasets. In this article, we will be discussing the basics of Excel Pivot Table and step by step method to create Excel Pivot Table.

What is an Excel Pivot Table

We can think about Pivot Table as a report but it differs from the traditional reports which are static in nature. Pivot tables are interactive in nature and it is dynamic as well. We can group data into categories, filter data to include or to exclude some categories, can break date data into months or years … Continue Reading

What is the AGGREGATE function in Excel|| 19 functions in a single formula|| How to use the AGGREGATE function in excel

What is the AGGREGATE function in Excel

What is the AGGREGATE function in Excel – The AGGREGATE function in excel is a conglomerate of functions. We can use 19 functions from this single function. This is just a “Function of Functions” which incorporates multiple functions. It was introduced in the 2010 version. So if you are using an older version then it would reflect #NAME? errors. The excel functions that it incorporates are given below.

What is the AGGREGATE function in Excel
What is the AGGREGATE function in Excel

Let us understand the syntax for the AGGREGATE function. There are 2 different syntaxes available as shown in the below image.

Syntax of AGGREGATE function
Syntax of the AGGREGATE function
  • First Syntax:
    • function_num: Required. A number between 1 to 19
Continue Reading

How To Sum a set of Cells Ignoring #N/A Errors In Excel || 3 tricks for the problem|| DataWitzz

How To Sum a set of Cells Ignoring #N/A Errors In Excel

We often encounter an issue where we wanted to sum set cells which already have #N/A in one or more cells. Due to these N/A values sum also reflects N/A instead of a number. How to sum a set of cells ignoring #N/A errors in Excel?

How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?
How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?

We can see in the above example that there were two entries of #N/A in the dataset due to which #N/A was reflected as a sum value. To encounter these types of problems we would be discussing a few ways forward in this tutorial.

SUMIF function in excel can … Continue Reading

5 Excel functions for average: 5 Useful functions in excel to calculate average || DataWitzz

5 Excel functions for average

We often require to find the average score of data sets in our regular excel use. Even sometimes we need to find the average basis of some criteria. Excel provides multiple functions to calculate the average. We will be discussing the following average function in this article.

  • AVEDEV
  • AVERAGE
  • AVERAGEA
  • AVERAGEIF
  • AVERAGEIFS

Let’s understand all of them one by one

AVEDEV is used to get the return of the average of the absolute deviations of data points from their mean. AVEDEV is basically a measure of the variability in a data set. Syntax for AVEDEV will be as given below.

5 Excel functions for average
5 Excel functions for average

LLets understand this function from the below example as illustrated … Continue Reading

Difference between absolute and relative reference : 3 Types of cell references

Difference between absolute and relative reference

In normal excel use, we are facing issues with cell values when we are copying a cell from one place to another place. It happens due to references used in the cell. Usually, there are two cell references in excel 1) absolute reference and 2) relative reference. We also use mixed references. Let’s understand it in detail.

Relative References

BY default every cell reference is the relative reference. When copied across cells the values also change relatively.

Difference between absolute and relative reference in Excel
Relative Reference in excel

In the above example, we can see that we first refer to value in CELL B3 and when we copied it to cell E3 the cell reference got changed from cell … Continue Reading

How to import text file to Excel and Automate it without VBA: How to use Get and Transform option in excel to import data|| DataWitzz

How to import text file to Excel and Automate it

Importing a text or CSV file in excel is always been a tidy task for data analysts. With the help of Get and Transform data this task has become very easy. This option not only helps in importing data but also helps in transforming the data as per our requirement, like removing a particular column or using some filter before importing or changing the data type of the column becomes very easy. Let’s understand this by a real-time example.

To import excel we will go to Data tab then Get and Transform option. After clicking Get and Transform we will get the different options for importing the data. We will … Continue Reading

How to calculate EMI in excel with Principle and the Interest components | DataWitzz

How to calculate EMI in excel

How to calculate EMI is most annoying thing when we take loan or planning to take loan. It also very difficult when we wanted to check interest and loan amount components spread over EMI tenure. Though there multiple online stuffs are available to calculate EMI but the spread over EMI tenure is very early. I just tried to make it excel which will surely help you.

Let us first learn How to calculate EMI in excel :

In excel to calculate EMI we would use PMT function of excel. Syntax for PMT function is as below:

How to calculate EMI in excel
How to calculate EMI in excel

PMT function requires below input to calculate EMI:

  • rate: interest rate
Continue Reading

Common Excel errors and Tips to handle them: 5 most common excel errors

Common Excel errors

While working with excel we usually encounter some errors as an output. These common excel errors can impact calculations of the workbook. We will be discussing these Common Excel errors and Tips to handle them in the below article. Hope this will help you understand these error and solutions to handle these.

  1. Value Error (#VALUE!) : These errors reflect when we put a wrong variable value in the formula. Like when adding five values out of which four are numeric and one is aplhabet, then in that case this error will reflect. Let us see below example to understand this.
Common Excel errors
Common Excel errors

In the above example, we can see that while adding numeric values … Continue Reading