Page 1 :
Microsoft Office Excel 2007, Data Processing in Spreadsheets, , 1/28/2009, , Microsoft Excel, , 1
Page 2 :
Use Excel’s functions!, • A function is a predefined (built-in) formula for, commonly used calculations., • Each Excel function has a name and syntax., – The syntax specifies the order in which you must, enter the different parts of the function and the, location in which you must insert commas,, parentheses, and other punctuation, – Arguments are numbers, text, or cell references, used by the function to calculate a value, – Some arguments are optional, 1/28/2009, , Microsoft Excel, , 2
Page 3 :
Functions, • Excel supplies more than 350 functions, organized into 10 categories:, – Database, Date and Time, Engineering, Financial,, Information, Logical, Lookup, Math, Text and Data,, and Statistical functions, , • You can use the Insert Function button on the, Formula bar to select from a list of functions., • A series of dialog boxes will assist you in filling in, the arguments of the function and this process, also enforces the use of proper syntax., 1/28/2009, , Microsoft Excel, , 3
Page 4 :
fx marks the spot!, • Office Excel 2007 offers two methods to insert, predefined functions into your spreadsheet …., – The first method is the use of the Function Button, next to the Formula Bar:, , Function Button, , 1/28/2009, , Formula Bar, , Microsoft Excel, , 4
Page 5 :
fx marks the spot! (cont), – The second method is the use of the Formulas tab, and the Function Library:, , Insert “Function Window”, (like the Function Button), Specific Group Types of Functions, 1/28/2009, , Microsoft Excel, , 5
Page 6 :
Inserting a Function, Categories, Available, Functions, within, Category, , Available, Function, Categories, , The Insert Function Window is identical to that of previous, versions of Excel. If you aren’t familiar with this window, the, functions are grouped into categories and listed accordingly., 1/28/2009, , Microsoft Excel, , 6
Page 7 :
Tips on Functions, • The Search for a Function (from the Insert Function, Window) will attempt to locate the function based upon a, description of what you would like to do., • You may also select a category from the drop-down box, or, choose All (lists every function in Excel alphabetically)., • Things to remember about functions:, – The arguments shown in boldfaced type are required, – When the cursor is in an argument's text box, you can, either enter a value, a cell reference, or click on a cell, (cell pointing), , 1/28/2009, , Microsoft Excel, , 7
Page 8 :
Functions from the Formulas tab, , The ribbon list (Functions Library) from within the, Formulas tab are categorized the same as the groupings, from the Insert Function Window (previous example)., The key to using the categories is to DERIVE what the, commonality of the groupings is., , Red Hot Tip -> practice using the functions as if you were, practicing with a musical instrument! That way your, thought process is automatic and not “search and pray.”, 1/28/2009, , Microsoft Excel, , 8
Page 9 :
Functions YOU should know, Statistical Functions, , • MAX - find the greatest value in a range, • MIN – find the lowest value in a range, • AVERAGE – determine the average within a, range (sum divided by number of values), • COUNT – determines the number of cells in a, range containing a numeric value (counts them), • COUNTA – counts the cells with text as well as, numeric values (non blank cells), 1/28/2009, , Microsoft Excel, , 9
Page 10 :
Functions YOU should know (cont), Arithmetic Functions, , • SUM – adds (sums) all values in a range, {the Sigma button, AutoSum, is also this function}, , • ROUND – rounds a decimal number to a, specified set of digits, , 1/28/2009, , Microsoft Excel, , 10
Page 11 :
Functions YOU should know (cont), Logical Functions, , • IF – a decision making function used to, determine the truth value for a condition, • AND – determines the truth value for a group of, arguments as a whole (all must be true to be, true), • OR – determines the truth value for a group of, arguments separately (only one must be true), 1/28/2009, , Microsoft Excel, , 11
Page 12 :
Functions YOU should know (cont), Financial Functions, , • PMT – calculates the payment for a loan based, on constant payments and constant interest rate, • FV – determines the future value of an, investment based on periodic, constant, payments and a constant interest rate, , 1/28/2009, , Microsoft Excel, , 12
Page 13 :
Functions YOU should know (cont), Lookup Function, , • VLOOKUP – searches for a value in the first, column of a table array (cell range) and returns, a value in the same row from another column in, the table array., • The V in VLOOKUP stands for vertical; meaning, that the value to “lookup” is searched for in a, vertical manner!, 1/28/2009, , Microsoft Excel, , 13
Page 14 :
Commonly used functions syntax, , 1/28/2009, , Microsoft Excel, , 14
Page 15 :
In-class Example – common functions, , Sample spreadsheet with common functions, (Stocks.xlsx)., , (you will find this file on my lecture notes page), , 1/28/2009, , Microsoft Excel, , 15
Page 16 :
In-class Example – VLOOKUP function, , Sample spreadsheet with VLOOKUP function, (Baseball_Shutouts.xlsx)., , (you will find this file on my lecture notes page), , 1/28/2009, , Microsoft Excel, , 16
Page 17 :
Sorting data in a worksheet, • Why Sort?, – Easier to read a data list that has been sorted, – Can aid in proof reading for data entry errors, – Things like “people” lists are easier to maintain, , • Excel makes it easy to sort a list in ascending or, descending order based on any field(s) in the list., • The field(s) selected to sort on are called the sort, fields or the sort keys., , 1/28/2009, , Microsoft Excel, , 17
Page 18 :
Sorting a worksheet, Do the Two Step:, • Step 1 – select data in, worksheet to be sorted, • Step 2 – From the Data, tab, select either:, ascending, or descending, , 1/28/2009, , Microsoft Excel, , 18
Page 19 :
Sorting using a single sort key, , Data sorted alphabetically by Symbol., , 1/28/2009, , Microsoft Excel, , 19
Page 20 :
Multiple key sorting, Pressing this button …., will give you this dialog window for, setting-up multiple levels of sorting., , 1/28/2009, , Microsoft Excel, , 20
Page 21 :
A word of caution …., • When sorting a data table in a worksheet …., be sure to select all data in the worksheet, before sorting., , Do you know why?, , 1/28/2009, , Microsoft Excel, , 21
Page 22 :
In-class Example, , Sample sorting worksheet (Stocks_to_Sort.xlsx), (you will find this file on my lecture notes page), , 1/28/2009, , Microsoft Excel, , 22
Page 23 :
AutoFilter feature, • Filtering is a quick and easy, way to find and work with a, subset of data in a range., • A filtered range displays, only the rows that meet the, criteria you specify for a, column., • Unlike sorting, filtering does, not rearrange a range filtering temporarily hides, rows you do not want, displayed., , 1/28/2009, , • Select the Filter option,, from the Sort & Filter, ribbon in the Data tab., , Microsoft Excel, , 23
Page 24 :
Autofiltering, , Clicking on the “drop-down arrow”, for a column …., will display the filtering options window., , 1/28/2009, , Microsoft Excel, , 24
Page 25 :
Autofiltering (cont), • By using Text Filters,, you can specify a, condition [to filter data, by] that would normally, take a great deal of time, when you use the, simple sort/remove, method!, , 1/28/2009, , Microsoft Excel, , 25
Page 26 :
Using Auto Fill, • To use the Auto Fill feature, select the cell, [range] that contains the values and/or formulas, you want to copy., • Click and drag the fill handle in the direction you, want to copy and then release the mouse, button., • If needed, click the Auto Fill Options button, and, then select the Auto Fill option you want to, apply to the selected range., 1/28/2009, , Microsoft Excel, , 26
Page 27 :
An example of Auto Fill, , Fill Handle Pointer, Auto Fill Options Button, 1/28/2009, , Microsoft Excel, , Options!, 27
Page 28 :
Auto Fill Options button menu options, • Copy Cells will copy all values and formulas into, the selected range as well as the formats used, to display those values and formulas., • Fill Series does just that! What are the rules?, • Fill Formatting Only copies only the formats, used to display values or formulas without, copying the values and formulas themselves., • Fill Without Formatting copies only the values, and formulas without any of the formats used in, the source range., , 1/28/2009, , Microsoft Excel, , 28
Page 29 :
Questions?, , 1/28/2009, , Microsoft Excel, , 29
