# Excel

Excel related topic to be tagged

## 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 19

## 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?

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

## 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

#### 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.

In the above example, we can see that while adding numeric values … 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

## How to use the IF function in excel ||Learn it with 2 examples||DataWitzz

#### How to use the IF function in excel

IF function in excel is used to evaluate a given condition if it is TRUE or FALSE and returns a value whether a condition is TRUE or FALSE. It is a conditional function in excel and it helps in testing a value based on the condition. IF function in excel works with other logical functions like AND,OR etc. It is also being use as nested function to incorporate multiple condition.

Let’s understand the syntax of IF function from the below image

In the above image logical_test means the condition that we wanted to evaluate, [value_if_true] it is the value that comes when condition is True and [value_if_false] is … Continue Reading

## What is Descriptive Statistics: How to calculate it in excel using the Data Analysis Tool Pack

#### What is descriptive statistics

Descriptive statistics are used to describe, show or summaries the basic features of a data set in a meaningful way. It provides basic summaries of the data like mean, median etc. It is very useful as if we only present data it wouldn’t make any meaning but while using descriptive analysis the same data can be presented in a very meaningful way.

How to use Descriptive Statistics in Excel

To use Descriptive Statistics in Excel we will use Data Analysis tool pack. First we will go in Data tab Data Analysis. After clicking Data Analysis a new box will come like below image.