Function

New Excel Functions in Excel 2021: 5 Awesome Excel formulas

New Excel Functions in Excel 2021

Every time when Microsoft excel launches a new version new excel functions are also been added there. Excel 2021 also has new functions like  FILTER, SORT, SORT BY, UNIQUE, SEQUENCE. These all excel functions help us reducing our processing time. Let us see in brief what these functions are and how they work. Let us go through these New Excel Functions in Excel 2021.

FILTER function in excel

Excel FILTER function allows us to filter a range of data from a dataset based on some criteria. It is one of the New Excel Functions in Excel 2021. Let us understand first the syntax of Filter formula excel.

New Excel Functions in Excel 2021
New Excel Functions in Excel 2021

What … Continue Reading

Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples]

Flash fill in excel

What is Flash fill in excel?

Flash Fill is a data tool in Microsoft Excel that allows us to combine, extract or transform data based on a few examples. We only need to provide a couple of examples of the results we want, excel will guess the pattern and fill in the rest of the data for us.

What is Flash fill in excel?
What is Flash fill in excel?

In the above gif file, we can see that we only provided one row of input, and after using Flash fill it automatically separates values.

How to Enable Flash Fill in Excel?

To enable flash fill in excel go to File then Options. After that select Advanced and … Continue Reading

Table in Excel: How to create excel table step by step by 2 methods

How to create excel table

Table in excel is a powerful feature that helps us in organizing our data by grouping data together. It is very quick to create a table in excel by just clicking a few keys or using a shortcut key and converting your flat data into a table with a lot of benefits.

The benefit of using excel table is as below but it is not limited to these only:

  • Clean Formulas : When we are using excel table feature then it become very easier to read forumals even it is very complicated.
  • Referencing a range : It is very easy to reference a range as every table has its own name.
  • Auto expand : It
Continue Reading

How to transpose excel rows to columns:3 Ways to Transpose Data in Excel

Excel rows to columns transpose

What is excel transpose function

Excel transpose function is a very useful functions in excel. It is used to switch rows to columns or excel columns to rows. In our regular excel use, we usually come across situations where we need to change excel columns to rows or rows to columns to make calculations easier or to visualize data in excel. Let’s learn how to transpose excel rows to columns.

Method 1: Transpose Data with Paste Special Command

In this method, we will first copy the data that we wanted to transpose which is in rows. After copying the data we will select any cell where we wanted to transpose the data. Then we will … Continue Reading

How to return a value if the VLOOKUP value is in a range || Excel VLOOKUP ||DataWitzz

How to return a value if the VLOOKUP value is in a range

Till now we have learnt about VLOOKUP and how to make VLOOKUP range dynamic with the help of excel MATCH function. Now let us learn how to return a value if the VLOOKUP value is in a range. Recently I got a data set where in i was asked to lookup values but the lookup reference table has data in range only.

Let us see the below image to understand this scenario then we will discuss how to selve this issue.

How to return a value if the VLOOKUP value is in a range
lookup value from range

In the above image we can see there are two tables. The one which is on left side Marks column and … Continue Reading

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

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