Excel

Excel related topic to be tagged

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

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 (#VALUE!) :

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

Common Excel errors
Common Excel errors

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

How to use logical functions in excel
Syntax of AND function

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

How to use the IF function in excel
Syntax of IF function

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

descriptive statistics

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.

What is Descriptive Statistics
What is Descriptive Statistics

This tool box contains different options like Descriptive … Continue Reading

Index fucntion excel

INDEX Function – Formula, Examples, How to Use Index in 2 different ways in Excel

Index Function

Excel Index function is an excel function that comes under lookup and reference functions. Index function helps in getting a value from a reference or array. It also works like VLOOKUP but it edges higher as it lookup value from any side and even from a different array. It can be used along with other excel functions to get summary data.

There are two different ways by which we can use excel index function. Let us understand the syntax of index function

Index Function
Index Function syntax

The first syntax array represents an array or range of data, row_num means the number of row from which data is supposed to pick if we miss this value or put 0 all … Continue Reading

offset excel function

Offset excel function: Formula, Examples, and Free video

Offset excel function

Excel offset function is a very interesting function that helps in getting data based on four parameters. These parameters are height, width, rows, and columns. Where VLOOKUP and HLOOKUP can get value from one side only, OFFSET function in excel can get value from any side. There is a lot of flexibility with OFFSET in excel as compared to other lookup functions in excel.

OFFSET function syntax

Let us understand how OFFSET excel function works. The OFFSET function syntax is given below.

Offset excel function
Offset excel function

In the above image we can see the inputs, reference means the reference from which the base is to be offset. It can be a cell or a range of adjacent cells. … Continue Reading

data validation excel

Data Validation in Excel: How to use it,8 awesome uses of excel data validation

Data Validation in Excel

Data validation in excel is a very useful tool. Its main functionality is to create constraints while data entry in excel. For example, we could put a constraint that no duplicate value should enter in a cell or column or the value entered in a cell should be between 5 to 25. It also reflects a message about the condition required to enter the data in that cell, when we put a wrong entry.

How to add data validation in excel sheet

To apply data validation in excel first we will go to Data >> Data Validation >> Data Validation. After that we will click on Data Validation then we will get an input box … Continue Reading

dependent data validation list excel

How to create multiple level dropdown list in excel

How to create multiple level dropdown list in excel

Data validation in excel is one of the useful methods to put validation in cells of excel. In this article, we will learn how to create multiple level dropdown list. Multiple level dropdown list also known as dynamic data validation or multiple level data validations. It helps in putting validation in the second cell based on the selection of the first cell. By using this if we select a particular value in the first cell then the second cell will reflect values related to the first cell.

Let us start with an example of how to create multiple dependent drop down list in excel. We have a data set with two … Continue Reading