1 of 71

EXCEL�

BY ATUl BHATT

2 of 71

WHAT IS SPREADSHEET?�

  • A spreadsheet or worksheet is a file made of rows and columns that help sort data, arrange data easily, and calculate numerical data. What makes a spreadsheet software program unique is its ability to calculate values using mathematical formulas and the data in cells. An example of how a spreadsheet may be utilized is creating an overview of your bank's balance.

3 of 71

SPREADSHEET OVERVIEW�

4 of 71

FORMULAS

  • Formulas perform operations such as addition, multiplication, and comparison on worksheet values.
  • Formulas can refer to other cells on the same worksheet, cells on other worksheets in the same workbook, or even cells on worksheets in other workbooks.
  • Formulas may make use of built-in functions.

5 of 71

SUM FUNCTION

DEFINITION:

TIPS:

  • Probably the most popular function in any spreadsheet is the SUM function. The Sum function takes all of the values in each of the specified cells and totals their values.
  • The syntax is: =SUM(first value, second value, etc)

​

  • Blank cells will return a value of zero to be added to the total.
  • Text cells can not be added to a number and will produce an error.

​

6 of 71

SUM FUNCTION

7 of 71

AVERAGE FUNCTION

  • The average function finds the average of the specified data. (Simplifies adding all of the indicated cells together and dividing by the total number of cells.)

8 of 71

MAX & MIN FUNCTIONS

  • The Max function will return the largest (max) value in the selected range of cells. The Min function will display the smallest value in a selected set of cells.

9 of 71

COUNT FUNCTION

  • The Count function will return the number of entries (actually counts each cell that contains NUMBER DATA) in the selected range of cells.
  • Remember: cell that are blank or contain text will not be counted.

10 of 71

IF FUNCTION

DEFINITION:

TIPS:

  • The IF function will check the logical condition of a statement and return one value if true and a different value if false.
  • The syntax is: �=IF (condition, value-if-true, value-if-false)

​

  • Until you are used to writing them, test them out on multiple cells.
  • There are multiple ways to write an IF statement to get the same result

​

11 of 71

IF FUNCTION

  • IF Functions are like programing - they provide multiple answers based on certain conditions.

​

12 of 71

13 of 71

SUM IF�

=SUMIF (range, criteria, [sum_range])

Arguments :

range - The range of cells that you want to apply the criteria against.

criteria - The criteria used to determine which cells to add.

sum_range - [optional] The cells to add together. If sum_range is omitted, the cells in range are added together instead.

​

Purpose 

Sum numbers in a range that meet supplied criteria

Return value 

The sum of values supplied.

14 of 71

SUM IF

15 of 71

16 of 71

SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to sum cells based on dates, numbers, and text that match specific criteria.

17 of 71

18 of 71

COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. 

COUNTIF

19 of 71

20 of 71

21 of 71

22 of 71

23 of 71

CONCATENATE

  • Learn various ways to concatenate text strings, cells, ranges, columns and rows in Excel using the CONCATENATE function

​

24 of 71

25 of 71

Dates

26 of 71

27 of 71

Percentage

28 of 71

29 of 71

Summary 

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

Purpose 

Lookup a value in a table by matching on the first column

Return value 

The matched value from a table.

Syntax 

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

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.

Vlookup

30 of 71

31 of 71

Sheet 1

Sheet 2

Sheet 3

32 of 71

33 of 71

If Error

34 of 71

Double Vlookup

35 of 71

Dynamically Auto Update

36 of 71

Multiple Workbook

Sheet 1

Sheet 2

37 of 71

Multiple Lookup Values

38 of 71

39 of 71

Advanced vlookup

40 of 71

41 of 71

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

MATCH Function

Purpose 

Get the position of an item in an array

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments 

  • lookup_value - The value to match in lookup_array.
  • lookup_array - A range of cells or an array reference.
  • match_type - [optional] How to match, specified as -1, 0, or 1. Default is 1.

42 of 71

Match

43 of 71

44 of 71

Vlookup With Match Function

45 of 71

Lookup Value always in First column

Vlookup Never find value on left side.

Index Function is Solution

46 of 71

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

INDEX Function

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

Arguments 

  • array - A range of cells, or an array constant.
  • row_num - The row position in the reference or array.
  • col_num - [optional] The column position in the reference or array.
  • area_num - [optional] The range in reference that should be used.

47 of 71

Index Function

48 of 71

Index With Matching

49 of 71

HLOOKUP Function

HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row.

HLOOKUP( value, table, index_number, [approximate_match] )

Parameters or Arguments

value

The value to search for in the first row of the table.

table

Two or more rows of data that is sorted in ascending order.

index_number

The row number in table from which the matching value must be returned. The first row is 1.

approximate_match

Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

​

50 of 71

Hlookup

Vlookup

51 of 71

Hlookup With Match

52 of 71

PMT function returns a payment amount, so you can use it to:

  • Calculate the monthly payment due on a personal loan
  • Calculate the payment due for a loan, with interest compounded bi-annually

The PMT function has the following syntax:

  • PMT(rate, nper, pv)
    • Rate is the interest rate for the loan.
    • Nper is the total number of payments for the loan.
    • Pv is the present value; also known as the principal.

PMT Function

53 of 71

54 of 71

Search Product Name

Behalf Also

55 of 71

56 of 71

The AVERAGEIFS Function is a Statistical function that calculates the average of all numbers in a given range of cells based on multiple criteria.

Formula

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

57 of 71

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.

SUMPRODUCT

Purpose 

Multiply, then sum arrays

Return value 

The result of multipled and summed arrays

Syntax 

=SUMPRODUCT (array1, [array2], ...)

Arguments 

  • array1 - The first array or range to multiply, then add.
  • array2 - [optional] The second array or range to multiply, then add.

58 of 71

59 of 71

60 of 71

Summary 

The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

Purpose 

Get a subtotal in a list or database

Return value 

A number representing a specific kind of subtotal

Syntax 

=SUBTOTAL (function_num, ref1, [ref2], ...)

Arguments 

  • function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
  • ref1 - A named range or reference to subtotal.
  • ref2 - [optional] A named range or reference to subtotal.

SUBTOTAL

61 of 71

SubTotal

62 of 71

63 of 71

Left

Length

MID

64 of 71

Right

65 of 71

Trim

66 of 71

Data Validation

67 of 71

68 of 71

Insert a new worksheet. SHIFT+F11

Move to the next sheet in the workbook. CTRL+PAGE DOWN

Move to the previous sheet in the workbook. CTRL+PAGE UP

Enter current date. CTRL+; (semicolon)

​

Enter current time. CTRL+SHIFT+: (colon)

Merge Cells ALT+H+M

69 of 71

  • Ctrl+N: Create a new workbook
  • Ctrl+O: Open an existing workbook
  • Ctrl+S: Save a workbook
  • F12: Open the Save As dialog box
  • Ctrl+W: Close a workbook
  • Ctrl+F4: Close Excel
  • F4: Repeat the last command or action. For example, if the last thing you typed in a cell is “hello,” or if you change the font color, clicking another cell and pressing F4 repeats that action in the new cell.
  • Shift+F11: Insert a new worksheet
  • Ctrl+Z: Undo an action
  • Ctrl+Y: Redo an action
  • Ctrl+F2: Switch to Print Preview

70 of 71

  • F1: Open the Help pane
  • Alt+Q: Go to the “Tell me what you want to do” box
  • F7: Check spelling
  • F9: Calculate all worksheets in all open workbooks
  • Shift+F9: Calculate active worksheets
  • Alt or F10: Turn key tips on or off
  • Ctrl+F1: Show or hide the ribbon
  • Ctrl+Shift+U: Expand or collapse the formula bar
  • Ctrl+F9: Minimize the workbook window
  • F11: Create a bar chart based on selected data (on a separate sheet)
  • Alt+F1: Create an embedded bar chart based on select data (same sheet)
  • Ctrl+F: Search in a spreadsheet, or use Find and Replace
  • Alt+F: Open the File tab menu

71 of 71

  • Alt+N: Open the Insert tab
  • Alt+P: Go to the Page Layout tab
  • Alt+M: Go to the Formulas tab
  • Alt+A: Go to the Data tab
  • Alt+R: Go to the Review tab
  • Alt+W: Go to the View tab
  • Alt+X: Go to the Add-ins tab
  • Alt+Y: Go to the Help tab
  • Ctrl+Tab: Switch between open workbooks
  • Shift+F3: Insert a function
  • Alt+F8: Create, run, edit, or delete a macro
  • Alt+F11: Open the Microsoft Visual Basic For Applications Editor
  • Alt+H: Go to the Home tab

​