- By
**drake** - Follow User

- 148 Views
- Uploaded on

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

- 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

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

- 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
- 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

- 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
- 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

- 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!
- http://www.lynda.com/course20/Excel-tutorials/Applying-global-changes-worksheets/75924/78631-4.html

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 FunctionWorking 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

Download Presentation

Connecting to Server..