agenda 3 31 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Agenda 3 /31 PowerPoint Presentation
Download Presentation
Agenda 3 /31

Loading in 2 Seconds...

play fullscreen
1 / 45

Agenda 3 /31 - PowerPoint PPT Presentation

  • Uploaded on

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Agenda 3 /31' - drake

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
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
managing worksheets
Managing Worksheets
worksheet navigation
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
working with columns and rows
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
working with cells and ranges
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
understanding cell references
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
using relative references
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
using absolute references
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
using mixed references
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)
when to use relative absolute and mixed references
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
3d reference
3D Reference
  • When referencing a cell on a different sheet:
  • ='My Sheet'!A1
  • =Sheet1!A1
named range
Named Range
  • Name a range of cells in Excel to make it easier to reference in formulas and functions.
excel tables
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
working with formulas
Working with Formulas
  • Formula
    • An expression that returns a value
    • Written using operators that combine different values, resulting in a single displayed value
working with formulas1
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
working with formulas2
Working with Formulas
  • Order of precedence
    • Set of predefined rules used to determine sequence in which operators are applied in a calculation
introducing functions
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)
entering functions with autosum
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)
formulas versus functions
Formulas versus Functions
entering data and formulas with autofill
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
formatting cell text
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
options in the format cells dialog box
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
copying and pasting formats
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
copying and pasting formats1
Copying and Pasting Formats
  • Use Paste Special to control exactly how to paste the copied range
grouping worksheets
Grouping Worksheets
  • Use to apply same format or function to multiple sheets
  • All sheets should have the exact same layout!
highlighting cells with conditional formatting
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
highlighting rules
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
working with functions
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
working with functions1
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)
using the function library to insert a function
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
working with logical functions
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
working with logical functions1
Working with Logical Functions
  • Comparison operator
    • Symbol that indicates the relationship between two values
using the if function
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
working with date functions
Working with Date Functions
  • For scheduling or determining on what days of the week certain dates occur
  • 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”)
datedif examples
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.

  • 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.
  • 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.
chart basics
Chart Basics
communicating effectively with charts
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