1 of 47

Advance Functions

1

7/12/2019

LEVEL -2

Duration:

Presentation: 2Hr

With demo: 4Hrs

With Practice: 16Hrs

2 of 47

2

7/12/2019

Contents

  • Cell Reference
  • Conditional formatting
  • Data Validation
  • Pivot Table/Chart
  • Review Data
  • shortcuts
  • Text to column
  • Column reference
  • Format cells/paste options
  • Functions
  • Lookup

3 of 47

CDSID: lvishwan

3

7/12/2019

When writing an Excel formula, $ in cell references confuses many users. But the explanation is very simple. The dollar sign in an Excel cell reference serves just one purpose - it tells Excel whether to change or not to change the reference when the formula is copied to other cells.

​

  • Relative reference (“A1”)
  • Absolute Reference (“$A$1”)

Cell Reference:

​

4 of 47

CDSID: lvishwan

4

7/12/2019

Cell Reference:

​

5 of 47

CDSID: lvishwan

5

7/12/2019

Conditional Formatting:

​

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

​

Conditional formatting quickly highlights important information in a spreadsheet. But sometimes the built-in formatting rules don’t go quite far enough. Adding your own formula to a conditional formatting rule gives it a power boost to help you do things the built-in rules can’t do.

​

  • Based on cells contain
  • Based on Another cell

6 of 47

CDSID: lvishwan

6

7/12/2019

Conditional Formatting:

​

7 of 47

CDSID: lvishwan

7

7/12/2019

Data Validation:

​

You can use data validation to restrict the type of data or the values that users enter into a cell.

One of the most common data validation uses is to create a drop-down list.

8 of 47

CDSID: lvishwan

8

7/12/2019

Dropdown List:

​

A drop-down list is an excellent way to give the user an option to select from a pre-defined list.

​

Ways to create dropdown list

    • From data cells
    • Manual entry
    • Formula
    • Dependent

​

Fixed

dynamic

9 of 47

CDSID: lvishwan

9

7/12/2019

Dropdown List:

​

    • From data cells:
      • Select cells where you want dropdown
      • Go to Data validation
      • Select list in dropdown
      • Select options range
      • Select ok

10 of 47

CDSID: lvishwan

10

7/12/2019

Dropdown List:

​

    • Manual entry:
      • Select cells where you want dropdown
      • Go to Data validation
      • Select list in dropdown
      • Enter options separated by ,
      • Select ok

11 of 47

CDSID: lvishwan

11

7/12/2019

Dropdown List:

​

    • Formula:
      • Select cells where you want dropdown
      • Go to Data validation
      • Select list in dropdown
      • Enter formula
      • Select ok

=OFFSET(reference, rows, cols, [height], [width])

​

=OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))

12 of 47

CDSID: lvishwan

12

7/12/2019

Dropdown List:

​

    • Dependent:
      • Select cells where you want dropdown
      • Go to Data validation
      • Select list in dropdown
      • Enter formula =indirect(dependent cell)
      • Select ok

To enable this dropdown first have to define the groups

13 of 47

CDSID: lvishwan

13

7/12/2019

Name Manager:

​

Name manager is use to define groups for dropdown and formulas.

We can create groups manually by creating New or else by selection

For tables, name and values will be created automatically

14 of 47

CDSID: lvishwan

14

7/12/2019

Allow invalid data:

​

Dropdown allows user to select specified values only. If we want to allow invalid data as exception, we can control it in data validation settings.

​

If we needs to check invalid dates, we can use circle invalid data

15 of 47

CDSID: lvishwan

15

7/12/2019

Data Validation:

​

16 of 47

CDSID: lvishwan

16

7/12/2019

Pivot Table/Chart:

​

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

​

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

17 of 47

CDSID: lvishwan

17

7/12/2019

Pivot Table/Chart:

​

Creating Pivot Table/Chart:

      • Go to pivot table
      • Select the data range
      • Choose where you want the pivot table report to be placed
      • Select ok

​

​

Select data range

Select report range

18 of 47

CDSID: lvishwan

18

7/12/2019

Pivot Table/Chart:

​

Value setting:

      • Choose the required field to add to report
      • Place the selected fields in the drag field area by simply drag and place

Select req field

Drag the field

19 of 47

CDSID: lvishwan

19

7/12/2019

Pivot Table/Chart:

​

Group setting:

      • Select the field in pivot table
      • Right click and then select group
      • Choose the criteria of grouping
      • Click ok

​

​

​

20 of 47

CDSID: lvishwan

20

7/12/2019

Pivot Table/Chart:

​

Multi level/Multi value:

      • Place the selected fields in the drag field area by simply drag and place
      • Drag the two different field in the same area then it creates multi level/multi value

​

​

21 of 47

CDSID: lvishwan

21

7/12/2019

Pivot Table/Chart:

​

Update Table:

​

  1. Refresh:

​

      • Select the any of the cell in pivot table
      • Right click and then select refresh

​

  • Change the range of table:
      • Select the any of the cell in pivot table
      • Go to Analyze tab then select change data source
      • Set the range and click ok

​

​

​

​

Before

After

Change data range

22 of 47

CDSID: lvishwan

22

7/12/2019

Pivot Table/Chart:

​

Calculated Field:

    • Click any cell inside the pivot table
    • Go to Analyze tab, in the Calculations group, click Fields, Items & Sets
    • Enter the field name and type formula
    • Click ok

​

​

​

23 of 47

CDSID: lvishwan

23

7/12/2019

Pivot Table/Chart:

​

Get Pivot Data:

    • Write a formula to accessing a pivot table data in the required cell

24 of 47

CDSID: lvishwan

24

7/12/2019

Pivot Table/Chart:

​

Change Chart type:

    • Right click the chart area and select change chart type
    • Select any one of the style
    • Click ok

25 of 47

CDSID: lvishwan

25

7/12/2019

Pivot Table/Chart:

​

Add data Labels:

    • Go to chart elements available at top right of chart and enable the data label

26 of 47

CDSID: lvishwan

26

7/12/2019

Pivot Table/Chart:

​

27 of 47

CDSID: lvishwan

27

7/12/2019

​

​

​

​

Types of Pivot Chart

28 of 47

CDSID: lvishwan

28

7/12/2019

​

​

​

​

Formating of Pivot Chart

29 of 47

CDSID: lvishwan

29

7/12/2019

Share workbook:

​

By sharing an Excel file, you are giving other users access to the same document and allow them to make edits simultaneously, which saves you the trouble of keeping track of multiple versions.

30 of 47

CDSID: lvishwan

30

7/12/2019

Share workbook:

​

31 of 47

CDSID: lvishwan

31

7/12/2019

Sheet Protect:

​

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.

Note: Right-click anywhere in the sheet and select Format Cells and then go to the Protection tab and clear Locked.

32 of 47

CDSID: lvishwan

32

7/12/2019

Workbook Protect:

​

To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password.

33 of 47

CDSID: lvishwan

33

7/12/2019

Excel File Protect:

​

To protect workbook from view and modify, while saving the workbook we can use General option by password.

34 of 47

CDSID: lvishwan

34

7/12/2019

Shortcuts:

​

  • Ctrl+D : copy top cell value
  • Ctrl+enter: fill values in all selected cells
  • shift+space: select row
  • Ctrl+space: select column
  • Ctrl+`: show formulas
  • Alt+enter: carriage return (next line in cell)
  • Ctrl+shif+end: select used range

35 of 47

CDSID: lvishwan

35

7/12/2019

PRE DEFINED FUNCTIONS

36 of 47

CDSID: lvishwan

36

7/12/2019

Functions:

Functions are Predefined operation with simple name.

By default excel contains many functions to perform various operations required for day to day work.

  • These options can be found under the formulas tab in the Menu bar.
  • All the operations can be found inside the Insert function menu also
  • Further all the operations are grouped based on the business need like

financials, logical, date & time, Maths & Trignometry, etc.

  • In addition ,the user can directly insert the functions in the worksheet by
  • Typing =(equal to symbol) in the cell in which the operation is required.

37 of 47

CDSID: lvishwan

37

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

​

38 of 47

CDSID: lvishwan

38

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

​

39 of 47

CDSID: lvishwan

39

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

​

FUNCTION

EXAMPLE

RESULT

Join

“hello” & “ world“

“hello world”

Case change

Upper(“hello”)

“HELLO”

count

Len(“hello”)

5

Find/Search

Find(“l”,”hello”)

3

replace

SUBSTITUTE(“helo”,“l",“ll")

“hello”

split

LEFT(“hello",3)

“hel”

40 of 47

CDSID: lvishwan

40

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

41 of 47

CDSID: lvishwan

41

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2.Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4.Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

​

42 of 47

CDSID: lvishwan

42

7/12/2019

Before going on to the live examples, we need to know the basic classification of the functions.

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2.Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4.Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

​

8/9/2019

=WEEKNUM(B12,1)

32

8/9/2019

=WEEKDAY(B13,1)

6

8/9/2019

=TEXT(B3,"dddd")

Tuesday

8/9/2019

=MONTH(B15)

8

8/9/2019

=TEXT(B3,"mmmm")

January

8/9/2019

=YEAR(B17)

2019

43 of 47

CDSID: lvishwan

43

7/12/2019

Functions: Example

​

44 of 47

CDSID: lvishwan

44

7/12/2019

String Functions:

​

45 of 47

CDSID: lvishwan

45

7/12/2019

LOOKUP

​

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range.

LOOKUP's default behavior makes it useful for solving certain problems in Excel.

​

=LOOKUP (lookup_value, lookup_vector, [result_vector])

​

Other options:

​

VLOOKUP

HLOOKUP

MATCH&INDEX

key

value

key1

value1

key2

value2

key3

value3

key4

value4

key5

value5

key

value

key4

​

​

​

​

​

​

​

​

​

46 of 47

CDSID: lvishwan

46

7/12/2019

Lookup:

​

47 of 47

CDSID: lvishwan

47

7/12/2019

Function

Lookup

Vlookup

Hlookup

Match&index

syntex

=LOOKUP (lookup_value, lookup_vector, [result_vector])

=VLOOKUP (value, table, col_index, [range_lookup])

=HLOOKUP (value, table, row_index, [range_lookup])

=INDEX(range, row_or_column)

=MATCH(lookup_value, lookup_range, match_type)

Col/row

both

Column only

Row only

both

Match type

Approximate only

both

both

both

Reference value

Any col/row

First column

First column

Any col/row

Rating

​

​

​

​

Comparision:

​

VBA