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

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.

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

• First Syntax:
• function_num: Required. A number between 1 to

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?

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.

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.

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:

PMT function requires below input to calculate EMI:

• rate: interest rate

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

Standard Error in Excel

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 this standard error in excel and Tips to handle errors in excel in the below article. Hope this will help you understand these errors and solutions to handle these.

#value error in excel 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 value error in excel.

In the above … Continue Reading

How to use logical functions in excel: AND, OR, NOT, and XOR || DataWitzz

How to use logical functions in excel

There are basically 5 types of logical functions available in excel which are mostly used in day-to-day life. These functions are as mentioned below:

• IF(S)
• AND
• OR
• NOT
• XOR

You can read about the IF function from the link Here. I will be explaining the use of the rest four functions.

AND function is used to evaluate a condition where we wanted to check if all conditions are true or not. It will throw true or false based on all conditions matched or not. Let us see the syntax of AND function in excel

The syntax is very simple just use conditions in logical values. Let us understand … Continue Reading