Excel Formulas And Functions

• Learning the difference between formulas and functions

• Understanding more than one way to do a LOOKUP

• Using functions to manipulate text

• Using the IF function to automate data entry

• Using functions to manipulate date and time entries

• Creating complex functions and breaking them apart

Table of Contents

1. Introduction

2. Formula Overview - Basic Formulas 00:02:53

3. Absolute 00:15:32

4. Date Functions 00:24:22

5. Age Calculation 00:41:17

6. Basic Functions - Sum And Average 00:48:30

7. Text Function 00:55:04

8. UPPER, LOWER, And PROPER 01:01:08

9. Text Functions - Text to Columns Wizard 01:05:10

10. MID Function 01:08:09

11. LOOKUP Functions 01:16:07

12. VLOOKUP 01:16:23

13. XLOOKUP 01:29:14

14. The Difference Between True And False 01:31:44

15. IF Function 01:35:40

16. Presenter’s Closing And Email Address 01:39:30

17. Presentation Closing 01:40:05

Index

• Absolute Reference 00:15:32, 00:45:00

• AVERAGE 00:48:45, 00:52:11

• Cell 00:06:53, 00:12:30, 00:30:31, 00:42:05, 01:10:24

• Column 00:27:29, 00:30:09, 01:06:46, 01:16:33, 01:22:01

• Formula 00:01:22, 00:05:56, 00:08:46, 00:21:59, 00:28:30, 00:36:36, 00:45:19, 00:53:59

• Function 00:01:22, 00:25:24 00:29:01, 00:34:40, 00:42:15

• IF Function 01:35:40

• LEFT Function 00:56:56

• LOOKUP 01:16:07, 01:19:19, 01:24:37

• LOWER 01:01:12

• MID Function 01:08:15

• PROPER 01:01:14

• RIGHT Function 00:59:40

• SUM 00:48:46, 00:50:25

• Table 01:18:46, 01:20:03

• Table Array 01:20:45

• Text to Columns Wizard 01:05:56

• UPPER 01:01:11

• VLOOKUP 01:16:23, 01:19:54, 01:25:01

• Worksheet01:24:25

• XLOOKUP 01:29:14

Key Terms

AVERAGE : Returns the average (arithmetic mean) of the arguments.

Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Formula: A formula is an expression which calculates the value of a cell.

Function: Functions are predefined formulas and are already available in Excel.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

LEFT Function: The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

LOWER : =LOWER The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The LOWER function is a built-in function in Excel that is categorized as a String/Text Function.

MID Function: The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". Extract text from inside a string. The characters extracted. =MID (text, start_num, num_chars)

PROPER: =PROPER The Microsoft Excel PROPER function sets the first character in each word to uppercase and the rest to lowercase. The PROPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.

RIGHT Function : The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet

Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.

Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.

Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.

UPPER: =UPPER The Microsoft Excel UPPER function allows you to convert text to all uppercase. The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.