[go: up one dir, main page]
Scribd
Upload
12 views1 page

Pivot Table & Formula

Uploaded by

shoab

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt

Pivot Table & Formula

Uploaded by

shoab
12 views1 page

Copyright

© © All Rights Reserved

Available Formats

PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
12 views1 page

Pivot Table & Formula

Uploaded by

shoab

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
You are on page 1/ 1

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

You might also like