20 top Powerful 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