Excel Formula Archives

New Excel Functions in Excel 2021: 5 Awesome Excel formulas

Leave a Comment / Excel, Excel Formula, Function

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.

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

Leave a Comment / Excel, Excel Formula, Function

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.

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

How to refresh Pivot Table: 2 ways to refresh pivot tables in excel

Leave a Comment / Excel, Excel Formula, VBA

How to refresh Pivot Table

In our previous post we learnt about how to create pivot table in excel. Now in this post we will try to understand different ways to refresh pivot table when new data is being added in the data range of pivot table. There are different ways to refresh excel pivot table, but we can categories them in two different ways.

Manually and
by VBA code.

How to refresh pivot table manually

1) To refresh a pivot table manually first select any cell on excel pivot table then go to PivotTable Analyze tab then in Data column click on Refresh. Now we ill get 2 options Refersh and Refresh all.

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

Leave a Comment / Excel, Excel Formula, Function

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

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

1 Comment / Excel, Excel Formula, Function, Power Query and Power BI

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

Leave a Comment / Excel, Excel Formula, Function

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

1 Comment / Excel Formula, Excel, Function

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

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.

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

2 Comments / Excel, Excel Formula, Function

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

Leave a Comment / Excel, Excel Formula, Function

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.

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