1 / 45

Agenda 3 /31

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. Introducing Microsoft Excel 2010.

drake
Download Presentation

Agenda 3 /31

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related