Agenda 3 /31

1 / 45

# Agenda 3 /31 - PowerPoint PPT Presentation

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.

## PowerPoint Slideshow about 'Agenda 3 /31' - drake

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
• 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
• 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
• http://www.lynda.com/course20/Excel-tutorials/Managing-worksheets/61219/68239-4.html
• 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
• 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
• Cell reference identifies column/row location
• Range reference indicates location and size of a cell range
• 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
• 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
• 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
• 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
• 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
• 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
• When referencing a cell on a different sheet:
• ='My Sheet'!A1
• =Sheet1!A1
Named Range
• Name a range of cells in Excel to make it easier to reference in formulas and functions.
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
• Formula
• An expression that returns a value
• Written using operators that combine different values, resulting in a single displayed value
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 Formulas
• Order of precedence
• Set of predefined rules used to determine sequence in which operators are applied in a calculation
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
• 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
• http://www.lynda.com/course20/Excel-tutorials/Understanding-formulas-functions/61219/68204-4.html
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
• Process of changing workbook’s appearance by defining fonts, styles, colors, and graphical effects
• Only the appearance of data changes, not data itself
• 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
• 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 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 Formats
• Use Paste Special to control exactly how to paste the copied range
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
• 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
• 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
• 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 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
• When you select a function, the Function Arguments dialog box opens, listing all arguments associated with that function
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 Functions
• Comparison operator
• Symbol that indicates the relationship between two values
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
• For scheduling or determining on what days of the week certain dates occur
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”)
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.

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.
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.
Chart Basics
• http://www.lynda.com/course20/Excel-tutorials/Choosing-chart-types/61219/68268-4.html
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