Setting Up a Workbook (Part Two)

Excel techniques used:

Insert or delete a cell

• Home/ cells/ insert/ insert cells
• Home/ cells/ delete/ delete cells

Move a group of cells to a new location

Zoom in or out on a worksheet

Zoom in or out to fill the program window

• View/ zoom/ zoom to selection

Change to another open workbook

• View/ window/ switch windows

Arrange all open workbooks in the program window

• View/ window/ arrange all

Ad, move, or remove a button to the Quick Access Toolbar

• Customize quick access toolbar/ more commands
  

(Zoom in by clicking the pictures)

Setting Up a Workbook (Part One)

Excel techniques used:

Open, create, and save a workbook

• Microsoft office button
• Quick access toolbar

Set file properties

• Microsoft office button/ prepare/ properties

Define custom properties

• Microsoft office button/ prepare/ properties/ property views and options down arrow/ advanced properties/ custom

Display, create, and rename a worksheet

Copy a worksheet to another workbook

• Move or copy dialog box

Change the order of worksheets in a workbook

Hide, unhide, and delete a worksheet

Change a row's height or column's width

Insert, delete, hide, or unhide a column or row








(Zoom in by clicking the pictures)

Working with Multiple Worksheets and Workbooks: Tracking Cash Flows (Part Two)

Excel techniques used:

Use data from multiple workbooks

• Create a summary workbook
• View the list of linked workbooks

Using lookup tables

• Setup a lookup table and insert the VLOOKUP function

Create and use an Excel workspace






(Zoom in by clicking the pictures)

Working with Multiple Worksheets and Workbooks: Tracking Cash Flow (Part One)

Excel techniques used:

Create a worksheet group

• Enter formulas in the worksheet group
• Apply the same formatting to all sheets in the worksheet group
• Ungroup the worksheet group

Create cell references to other worksheets

• Enter a formula that references another worksheet

Consolidate data from multiple worksheets using a 3-D reference

• Copy information across worksheets
• Enter the formula that includes the 3-D cell reference

Create a workbook based on a template

• Create a workbook based on a built-in template
• Create and save custom workbook template

Use data from multiple workbook

• Create a link to data in another workbook
• Create a workbook reference using the point-and-click method

(Zoom in by clicking the pictures)

Working with Excel Lists: Summarizing Faculty Data (Part Two)

Excel techniques used:

• Insert subtotals into a list
• Create a PivotTable and PivotChart
• Retrieve detailed data in a PivotTable

(Zoom in by clicking the pictures)

Working with Excel Lists: Summarizing Factulty Data (Part One)

Excel techniques used:

• Apply IF function
• Freeze rows and columns
• Find and replace values in a worksheet
• Create a list range
• Sort data using the custom lists
• Apply conditional formatting

(Zoom in by clicking the pictures)

Working with Excel Lists: Tracking Vehicle Data (Part Two)

Excel techniques used:

• Insert subtotals into a list
• Use the subtotals outline view
• Create a PivotTable and PivotChart
• Format numbers in a PivotTable
• Add a filed to a PivotTable
• Add a page view of a PivotTable

(Zoom in by clicking the pictures)

Working with Excel Lists: Tracking Vehicle Data (Part One)

Excel techniques used:

• Freeze rows and columns
• Find and replace values in a worksheet
• Create a list range
• Sort data in a list using sort button, AutoFilters, and sort command
• Add data using insert row and data form
• Filter data in a list using custom AutoFilters
• Apply conditional formatting to a range
• Use the total row to calculate totals in a list

(Zoom in by clicking the pictures)

Working with Charts and Graphics:Charting Sales Data

Excel techniques used:

• Create column and pie chart using the chart wizard
• Separate a slice from a pie chart
  
• Edit the data source of a chart
• Insert unattached text into a chart
• Create fill effects
• Add and modify drawing objects using the drawing toolbar
• Insert an AutoShape
• Insert text into an AutoShape
• Print a chart
  

(Zoom in by clicking the pictures)

Formatting Worksheet Data: Formatting a Sales Report

Excel techniques used:

• Format data using the comma, currency, and percent style
  
• Use the format cells dialog box
• Copy and paste formats using the format painter
• Indent and wrapping text
• Add background image to the worksheet
• Find and replace a format
• Create and apply a style
• Apply an AutoFormat
• Add headers and footers to printouts
  
• Work with the print area and page breaks

(Zoom in by clicking the pictures)

Working with Formulas and Functions: Creating a Loan Payment Schedule

Excel techniques used:

• Copy and paste formulas
• Use Auto Fill to insert formulas and complete a series
• Chang the zoom magnification of the workbook window
• Use relative and absolute references
• Use the Sum funciton
  
• Use the PMT function
• Use the PPMT function
• Use the IPMT function

(Zoom in by clicking the pictures)

Working with Formulas and Functions: Creatin a Payroll Worksheet

Excel techniques used:

• Copy and paste formula
• Copy formulas using Auto Fill option
• Change the zoom magnification of the workbook window
• Use relative and absolute references
• Apply the IF function

(Zoom in by clicking the pictures)

Working with Formulas and Functions: Developing a Family Budget

Excel techniques used:

• Copy and paste formulas
• Copy formulas using Auto Fill option
• Change the zoom magnification of the workbook window
• Use relative and absolute references
• Use the Sum function
• Use the Average function
• Use the Date function
• Use the PMT function
• Apply what-if analysis
• Use the IF function

(Zoom in by clicking the pictures)