##### 20 top Powerful Excel Tricks for Data Analysis

Table of Contents

Toggle## Excel tricks for Data Analysis

## Vlookup

Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a marks of students by roll number .

In its simplest form, the VLOOKUP function says:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Arguments

value – The value to look for in the first column of a table.

table – The table from which to retrieve a value.

col_index – The column in the table from which to retrieve a value.

range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

In the below example, we have used Percentage and sum formula to find the percentage of students by their roll number

=VLOOKUP(E3,A6:K21,11,1)

ROLL NUMBER TYPE ROLL NUMBER

IF we want to update table and change it into dynamic them we habe to to table

go to table and press ctrl + T

a screen appear

tick my table has a header

now it you update table you need not to change the range in vlookup

here we have update new roll number Ao16 and its updated in the vlookup

now go to table design and select the table format or color as per your wish and update the table

## CONCATENATE

CONCATENATE

=CONCATENATE is one of the easiest to learn but most powerful formulas when conducting data analysis. Combine text, numbers, dates and more from multiple cells into one. This is an excellent function for creating API endpoints, product SKUs, and Java queries.

Formula:

=CONCATENATE(SELECT CELLS YOU WANT TO COMBINE)

Creating email address of employees

## RANK

=RANK is an ancient excel function, but that doesn’t downplay its effectiveness for data analysis. =RANK allows you to quickly denote how values rank in a dataset in ascending or descending order. In the example, RANK is being used to determine which clients order the most product.

Formula:

=RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])

note: [order] is optional

## SUMPRODUCT

=SUMPRODUCT is an excellent function to calculate average returns, price points, and margins. SUMPRODUCT multiples one range of values by its corresponding row counterparts. It’s data analysis gold. In the example below, we calculate the average selling price of all our products by using sumproduct to times Price by Quantity and then divide by the total volume sold.

Formula:

=SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL

SUMPRODUCT(B2:B9,C2:C9)/C10

We hope you found that useful. If you’re interested in Data Analysis in Excel, take a look at the Excel course that has helped hundreds of thousands of people master Excel.

If you’re interested in learning more about Excel, we teach the basics for the price of a cup of coffee.

## How to use data table in Excel What-if” Analysis

## How to use data table in Excel

To use data table in Excel we have taken to find loan amount in Excel in the example below

- Loan Amount : 10,00000
- Interest 8% yearly
- Time : 5 years

First we use PMT function as shown below in the picture

=PMT(rate, nper, pv, [fv], [type])

=PMT(rate/12, nper*12, pv, [fv], [type])

Note: here we divide rate by 12 and nper we divide 12 so we can get monthly installment of money . Fv blank type 0 or leave blank.

After finding the PMT.

Next is to create a table of rate like 6% 7% 8% etc.

- Select PMT installment such as =B7.
- Select the table area.
- Go to date table from table
- Now in column select B7
- Installment will appear.

## SUMIFS

=SUMIFS is one of the “must-know” formulas for a data analyst. The common formula used is =SUM, but what if you need to sum values based on multiple criteria? SUMIFS is it. In the example below, SUMIFS is used to determine how much each product is contributing to top-line revenue.

Formula:

=SUMIF(RANGE,CRITERIA,[sum_range])

note: [sum_range] is optional

Use the formula

=SUMIF($A$4:$A$17,D3,$C$4:$C$17)

Use F4 Key for dollar sign.

and drag the formula till E8

## SUMIFS Function in Excel

SUMIF function allows us to sum the data given based on associated criteria within the same data. However, the SUMIFs Function in Excel allows applying multiple criteria.

Formula used for the SUMIFS Function in Excel

“SUMIFS ( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n] )”

Type the formula and give it to SUMIFS($C$3:$C$16,$A$3:$A$16,D3,$B$3:$B$16,E3)

not drag the formula

## Sumproduct

In Excel, the SUMPRODUCT function is used to multiply corresponding elements in multiple arrays or ranges and then sum the products. It is often used for calculations involving multiple criteria or weighted averages. The general syntax of the SUMPRODUCT function is as follows:

SUMPRODUCT(array1, [array2], [array3], …)

## MEDIAN

**The median is a statistical measure that represents the middle value in a set of numbers. It is used to describe the central tendency or the “typical” value of a dataset. In other words, the median splits the data into two equal halves, where half of the values are above the median and half are below it.****=MEDIAN(A1:A5)**

## MODE

**In statistics, the mode refers to the value or values that appear most frequently in a dataset. It is the value that occurs with the highest frequency, meaning it has the greatest number of occurrences compared to any other value in the dataset. =MODE.SNGL(A2:A11)**

## Standard Deviation:

**Standard deviation is a statistical measure that quantifies the amount of variation or dispersion in a dataset. It provides information about how spread out the values are from the mean (average) of the dataset.**

## VAR

Estimates the variance based on a sample. Variance measures how much the values in a dataset vary from the mean (average).

=VAR(A1:A5)

The function will calculate the variance based on the values in the range A1 to A5 and return the result. In this case, the variance will be approximately 12.3.

## LEFT

The LEFT function returns a specified number of characters from the beginning (leftmost side) of a text string

## RIGHT

he RIGHT function returns a specified number of characters from the end (rightmost side) of a text string. The syntax of the RIGHT function is as follows

## MID

The LEFT function returns a specified number of characters from the beginning (leftmost side) of a text string