It is used to create instant summaries,
reports and data analysis from your raw data
Definition
=CONCATENATE(1stCell,"delimiter",
2ndCell)
1 CONCATENATE Combine the values of several cells into one
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,
COL_INDEX_NUM, [RANGE_LOOKUP])
The formula allows you to look up data that
2 VLOOKUP is arranged in vertical columns
=LEN(SELECT CELL)
To get the number of characters in a given
3 LEN cell
=SUMIF(RANGE,CRITERIA,[sum_range])
It adds up the values in cells which meet a
4 SUMIF selected number
=DAYS(SELECT CELL, SELECT CELL)
To determine the number of days between
two calendar dates
=NETWORKDAYS(SELECT CELL,
SELECT CELL,[
5 DAYS/NETWORKDAYS numberofholidays])
With Exception - Except Holiday
Formula =SUBSTITUTE(A1,"p","s")
Pivot Table &
Formula Replacing cells in bulks - Ex: Bulk Change in
6 SUBSTITUTE URL
=MIN(IF(RANGE1,CRITERIA1,RANGE2))
Minimum of a set of values, and match on
criteria
=MAX(IF(RANGE1,CRITERIA1,RANGE2))
7 MINIF/MAXIF
Maximum of a set of values, and match on
criteria
=COUNTIFS(RANGE,CRITERIA)
Counts the numbers how many times a value
8 COUNTIFS appears based on one criteria
=LEFT(SELECT CELL,NUMBER)
will return the “x” number of characters from
Left the beginning of the cell
=RIGHT(SELECT CELL,NUMBER)
9 LEFT/RIGHT
will return the “x” number of characters from
Right the end of the cell
1 Creating Pivot Table
2 Format Pivot Table Values
3 Sorting Values in Pivot Table
Report Filters
4 Filters
Slicer
Excersice Design
5 Pivot Table Layout
Color & Style
6 Pivot Charts
7 Percentage
Home > Conditional Formatting > Data Bars>
8 Conditional Formatting - Bar in Percentage Icon in plot > choose last
