excel tutorial 2010

8/13/2019 Excel Tutorial 2010

http://slidepdf.com/reader/full/excel-tutorial-2010 1/19

Excel 2010



Formula in Excel

• Formulas are equations that perform

calculations on values in a worksheet.

• A formula always starts with an equal sign (=).

• Microsoft Office Excel follows the standard

order of mathematical operations.

•

Example: =5+2 --- results 7.



Formula with Functions

• A formula can also be created by using a

function which is a prewritten formula that

takes a value, performs an operation and

returns a value .

• Example: =SUM(D1, D2)



Formula

• A formula can contains:

– Function

– Constants

– Reference

– Operators

ConstantsFunction Operator

Reference



Handling Text

• Text Functions:

– PROPER

– LOWER

– UPPER

– MID

– TRIM

– LEN



• PROPER:

– Capitalizes the first letter in a text. Converts all

other letters to lowercase letters.

– SYNTAX: PROPER(text)

– Example: PROPER(“this is a TiTle”)

Output: “This Is A Title”



• LOWER:

– Converts all uppercase letters in a text string to

lowercase.

– SYNTAX: LOWER(text)

– Example: LOWER(“This is A TITLE”)

Output: ”this is a title”



• UPPER:

– Converts all letters in a text string to uppercase.

– SYNTAX: UPPER(text)

– Example: UPPER(“This is A TITLE”)

Output: ”THIS IS A TITLE”



• MID:

– Returns specified numbers of characters starting

from a start index from a text string.

– SYNTAX: MID(text, start_index, num_chars)

– Example: MID(“This is A TITLE”, 3, 5)

Output: ”is is”



• TRIM:

– Returns text with removed leading and trailing

spaces from a text string.

– SYNTAX: TRIM(text)

– Example: TRIM(“ This is A TITLE ”)

Output: ”This is A TITLE”



• LEN:

– Returns numbers of characters in a text string.

– SYNTAX: LEN(text)

– Example: LEN (“This is A TITLE”)

Output: 15



Handling Numbers

• Numbers can be handled using the operators

(+, -, *, /) or using functions.

– Ex: “=A2 + A3 –A4B2 / B3”

• Math Functions:

– SUM

– PRODUCT

– AVERAGE

– POWER



• SUM:

– Adds the numbers specified in arguments

– SYNTAX: SUM(number1, *number2+,….)

– Example: SUM(3,4) 7

SUM(“3”, 4, TRUE) 8

SUM(A2:A6)



• PRODUCT:

– Multiply the numbers specified in arguments

– SYNTAX: PRODUCT(number1, *number2+,….)

– Example: PRODUCT(3,4) 12

PRODUCT(“3”, 4, TRUE) 12

PRODUCT(A2:A6)



• AVERAGE:

– Calculates the average of the numbers specified in

arguments

– SYNTAX: AVERAGE(number1, *number2+,….)

– Example: AVERAGE(6,4) 12

AVERAGE(“4”, 4, TRUE) 3

AVERAGE(A2:A6)



• POWER:

– Returns the result of a number raised to a power.

– SYNTAX: POWER(number, power)

– Example: POWER(3,2) 9

POWER(“3”,TRUE) 3



VLOOKUP Function

•Searches the first column of a range of cells for avalue, and then return a value from any cell onthe same row of the range.

• Syntax: VLOOKUP(lookup_value, table_array,

col_index_num, [range_lookup]) – lookup_value: value to search in first column

– table_array: range of cells of data

– col_index_num: col no. in the range from whichmatching value must be returned

– range_lookup:

• TRUE – approximate match.

• FALSE – exact match



• Example: VLOOKUP(3, A1:B7,2, FALSE)

TUESDAY



PivotTable

• A PivotTable report is an interactive way toquickly summarize large amounts of data.

• Useful to summarize, analyse, explore, and

present summary data.• Designed for:

– Subtotalling and aggregating numeric data,summarizing data by categories and subcategories

– Expanding and collapsing levels of data.

– Moving rows to columns or columns to rows

– Etc.

excel tutorial 2010

Documents