Agenda 3 /31

1. Agenda 3/31 • Review Exam • Discuss key points from Tutorials 1-4 and beyond • Cell references • Formulas • Functions • Logical and IF • Dates • Formatting • Conditional formatting • Charts • Discuss Spreadsheet design and Excel HW #1

2. Introducing Microsoft Excel 2010 • Computer program used to enter, store, analyze, and present quantitative data • Creates electronic versions of spreadsheets • Collection of text and numbers laid out in a grid • Workbook vs. Worksheet • Displays values calculated from data • Allows what-if analysis • Ability to change values in a spreadsheet and assess the effect they have on calculated values

3. Managing Worksheets • http://www.lynda.com/course20/Excel-tutorials/Managing-worksheets/61219/68239-4.html

4. Worksheet Navigation • A workbook can have two kinds of sheets: • Worksheet contains a grid of rows and columns into which user enters data • Chart sheet provides visual representation of data • For detailed charts that need more space to be seen clearly or to show a chart without any worksheet text or data • Do not contain worksheet cells for calculating numeric values

5. Worksheet Navigation Keys

6. Working with Columns and Rows • To make data easier to read: • Modify size of columns and rows in a worksheet • To modify size of columns or rows: • Drag border to resize • Double-click border to autofit • Format the Cells group to specify • Deleting and clearing a row or column • Deleting removes both the data and the cells • Clearing removes the data, leaving blank cells where data had been

7. Working with Cells and Ranges • Cell reference identifies column/row location • Range reference indicates location and size of a cell range • Adjacent (A1:G5) • Nonadjacent (A1:A5;F1:G5) – use the control key to select • Selecting a range • Work with all cells in the range as a group • Moving and copying a range • Drag and drop • Cut and paste

8. Understanding Cell References • To record and analyze data • Enter data in cells in a worksheet • Reference the cells with data in formulas that perform calculations on that data • Types of cell references • Relative • Absolute • Mixed

9. Using Relative References • Cell reference as it appears in worksheet (B2) • Always interpreted in relation (relative) to the location of the cell containing the formula • Changes when the formula is copied to another group of cells • Allows quick generation of row/column totals without revising formulas

10. Using Absolute References • Cell reference that remains fixed when the formula is copied to a new location • Have a $ before each column and row designation ($B$2) • Enter values in their own cells; reference the appropriate cells in formulas in the worksheet • Reduces amount of data entry • When a data valued is changed, all formulas based on that cell are updated to reflect the new value

11. Using Mixed References • Contain both relative and absolute references • “Lock” one part of the cell reference while the other part can change • Have a $ before either the row or column reference ($B2 or B$2)

12. Using a Mixed Reference

13. When to Use Relative, Absolute, and Mixed References • Relative references • Repeat same formula with cells in different locations • Absolute references • Different formulas to refer to the same cell • Mixed references • Seldom used other than when creating tables of calculated values • Use F4 key to cycle through different types of references

14. 3D Reference • When referencing a cell on a different sheet: • ='My Sheet'!A1 • =Sheet1!A1

15. Named Range • Name a range of cells in Excel to make it easier to reference in formulas and functions.

16. Excel Tables • To make managing and analyzing a group of related data easier, you can turn a range of cells into a Microsoft Office Excel table. • A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. • By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet. • Practice in Tutorial 5

17. Working with Formulas • Formula • An expression that returns a value • Written using operators that combine different values, resulting in a single displayed value

18. Working with Formulas • Entering a formula • Click cell where you want formula results to appear • Type = and an expression that calculates a value using cell references and arithmetic operators • Cell references allow you to change values used in the calculation without having to modify the formula itself • Press Enter or Tab to complete the formula

19. Working with Formulas • Order of precedence • Set of predefined rules used to determine sequence in which operators are applied in a calculation

20. Introducing Functions • Function • Named operation that returns a value • Simplifies a formula, reducing a long formula into a compact statement; for example, to add values in the range A1:A10: • Enter the long formula:=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 - or - • Use the SUM function to accomplish the same thing: =SUM(A1:A10)

21. Entering Functions with AutoSum • Fast, convenient way to enter commonly used functions • Includes buttons to quickly insert/generate: • Sum of values in column or row (SUM) • Average value in column or row (AVERAGE) • Total count of numeric values in column or row (COUNT) • Minimum value in column or row (MIN) • Maximum value in column or row (MAX)

22. Formulas versus Functions • http://www.lynda.com/course20/Excel-tutorials/Understanding-formulas-functions/61219/68204-4.html

23. Entering Data and Formulas with AutoFill • Use the fill handle to copy a formula and conditional formatting • More efficient than two-step process of copying and pasting • By default, AutoFill copies both content and formatting of original range to selected range

24. Formatting Cell Text • Formatting • Process of changing workbook’s appearance by defining fonts, styles, colors, and graphical effects • Only the appearance of data changes, not data itself • Enhances readability and appeal • Live Preview shows the effects of formatting options before you apply them • Themes • Named collections of formatting effects

25. Options in the Format Cells Dialog Box • Presents formats available from Home tab in a different way and provides more choices • Six tabs, each focusing on different options: • Number • Alignment • Font • Border • Fill • Protection

26. Copying and Pasting Formats • Copying formats with Format Painter • Fast and efficient way of maintaining a consistent look and feel throughout a workbook • Copies formatting without duplicating data

27. Copying and Pasting Formats • Use Paste Special to control exactly how to paste the copied range

28. Grouping Worksheets • Use to apply same format or function to multiple sheets • All sheets should have the exact same layout! • http://www.lynda.com/course20/Excel-tutorials/Applying-global-changes-worksheets/75924/78631-4.html

29. Highlighting Cells with Conditional Formatting • Goal of highlighting: Provide strong visual clue of important data or results • Format applied to a cell depends upon value or content of the cell • Dynamic: If cell’s value changes, cell’s format also changes as needed • Excel has four conditional formats: data bars, highlighting, color scales, and icon sets

30. Highlighting Rules • Each conditional format has a set of rules that define how formatting should be applied and under what conditions format will be changed

31. Working with Functions • Quick way to calculate summary data • Every function follows a set of rules (syntax) that specifies how the function should be written • General syntax of all Excel functions • Square brackets indicate optional arguments

32. Working with Functions • Advantage of using cell references: • Values used in the function are visible to users and can be easily edited as needed • Functions can also be placed inside another function, or nested (must include all parentheses)

33. Using the Function Library to Insert a Function • When you select a function, the Function Arguments dialog box opens, listing all arguments associated with that function

34. Working with Logical Functions • Logical functions • Build decision-making capability into a formula • Work with statements that are either true or false • Excel supports many different logical functions, including the IF function

35. Working with Logical Functions • Comparison operator • Symbol that indicates the relationship between two values

36. Returns one value if a statement is true and returns a different value if that statement is false IF (logical_test, [value_if_true,] [value_if_false]) Note bold in function builder denotes required argument Using the IF Function

37. Working with Date Functions • For scheduling or determining on what days of the week certain dates occur

38. DATEDIF • The DATEDIF function in Microsoft Excel calculates the difference, or interval,  between two dates. This difference can be expressed in a variety of ways. The function takes the form • =DATEDIF(Date1, Date2, Interval) • where Date1 and Date2 are the two dates and Interval defines how the date difference should be returned. Interval must be enclosed in quotes like this: • =DATEDIF(Date1, Date2, “d”)

39. DATEDIF Intervals

40. DATEDIF Examples Note that when typing the date directly into the function you may get strange results, typing the date(s) into a cell and referencing that cell in the function is the preferred method.

41. Lookups • Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. • The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

42. VLOOKUP • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) • Lookup_value    The value to search in the first column of the table array. Lookup_value can be a value or a reference. • Table_array    Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. • Col_index_num    The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. • Range_lookup    A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: • If TRUE or omitted (this is the default), an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. • The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. • If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used.

43. Chart Basics • http://www.lynda.com/course20/Excel-tutorials/Choosing-chart-types/61219/68268-4.html

44. Communicating Effectively with Charts • Keep it simple • Focus on the message • Limit the number of data series • Use gridlines in moderation • Choose colors carefully • Limit chart to a few text styles

45. Choosing the Right Chart