Advanced Spreadsheet Skills PDF

LESSON 4: ADVANCED SPREADSHEET SKILLS OBJECTIVES: familiarize the MS Excel common functions; explain the different formulas, text, values; and perform how to enter data, complex formulas, and computations  What is MS Excel? Microsoft Excel provides an automated wa...

LESSON 4: ADVANCED SPREADSHEET SKILLS OBJECTIVES: familiarize the MS Excel common functions; explain the different formulas, text, values; and perform how to enter data, complex formulas, and computations  What is MS Excel? Microsoft Excel provides an automated way of displaying any statistical data. It can be used to automatically compute for several factors that are not easy to notice especially when faced by large amount of data. Microsoft Excel includes several arithmetic and basic functions that help you compute faster. Microsoft excel Advanced Spreadsheet Skills ▪ It allows users to organize data in rows and columns and perform calculations on the data ▪ These rows and columns collectively are called worksheet. Key Terms in MS Excel: ▪ Column – vertical ▪ Column Heading – the box at the top of each line of entries in a column containing a table letter Key Terms in MS Excel: ▪ Row – horizontal line of entries in a table ▪ Row Heading – the row number Key Terms in MS Excel: The maximum number of rows and columns on a worksheet in Excel is 1,048,576 rows by 16,384 columns or XFD. Key Terms in MS Excel: ▪ Cell – the place where information is held in a spreadsheet ▪ Active Cell – the selected cell Key Terms in MS Excel: ▪ Cell Reference – the cell address of the cell usually combine letter and number (ex. A1, B4, C2) Key Terms in MS Excel: ▪ Merge – combining or joining two or more cells Key Terms in MS Excel: ▪ Functions – are predefined formulas and are already available in Excel ▪ Formula Bar – the bar that displays the contents of a cell  BASIC MATH OPERATIONS: =C1+C2 returns the sum of C1 and C2  BASIC MATH OPERATIONS: =SUM(C1,C2) returns the sum of C1 and C2  BASIC MATH OPERATIONS: =SUM(range) returns the sum of all the numbers within the range  BASIC MATH OPERATIONS: =C1-C2 returns the difference of C1 subtracted by C2  BASIC MATH OPERATIONS: =C1*C2 returns the product of C1 and C2  BASIC MATH OPERATIONS: =PRODUCT(C1,C 2) returns the product of C1 and C2  BASIC MATH OPERATIONS: =C1/C2 returns the quotient of C1 divided by C2  BASIC MATH OPERATIONS: =QUOTIENT(C1,C 2) returns the quotient of C1 divided by C2  OTHER FUNCTIONS: ▪ =ABS(x) – returns the absolute value of x ▪ =AVERAGE(x,y) – returns the average of x and y ▪ =CONCATENATE(x,y) – joins x and y ▪ =IF(Condition, x, y) – returns x if the condition is true, else it returns y ▪ =ISEVEN(x) – returns true if x is an even number ▪ =ISODD(x) – returns true if x is an odd number ▪ =COUNT(range) – counts the number of cell containing a number within a range  OTHER FUNCTIONS: ▪ =COUNTIF(range, criteria) – count the number of cell that fits with the criteria within the range ▪ =ISNUMBER(x) – returns true if x is a number ▪ =ISTEXT(x) – returns true if x is a text ▪ =LEN(x) – returns the length of characters in x ▪ =PROPER(x) – returns the proper casing of x ▪ =LEFT(x,y) – returns the characters of x specified by y (from the left) ▪ =RIGHT(x,y) – returns the characters of x specified by y (from the right)  OTHER FUNCTIONS: ▪ =PI() – returns the value of pi ▪ =MIN(x,y) – returns the smallest number between x and y ▪ =MAX(x,y) – returns the largest number between x and y ▪ =MIN(range) – returns the smallest number within the range ▪ =MAX(range) – returns the largest number within the range ▪ =POWER(x,y) – returns the value of x raised to the power of y ▪ =ROUND(x,y) – rounds x to a specified number of digits (y) ▪ =COLUMN(x) – returns the column number of x ▪ =ROW(x) – returns the row number of x ▪ =SQRT(x) – returns the square root of x  OTHER FUNCTIONS: ▪ =UPPER(x) – returns x in all capital form ▪ =LOWER(x) – returns x in non-capital form ▪ =TODAY() – returns the current date ▪ =NOW() – returns the current date and time THANK YOU!