Lesson 8: Working with Large Worksheets - PowerPoint PPT Presentation

Loading...

PPT – Lesson 8: Working with Large Worksheets PowerPoint presentation | free to download - id: 1bbcb4-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Lesson 8: Working with Large Worksheets

Description:

Lesson 8: Working with Large Worksheets – PowerPoint PPT presentation

Number of Views:44
Avg rating:3.0/5.0
Slides: 121
Provided by: dougblo
Learn more at: http://www.utm.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Lesson 8: Working with Large Worksheets


1
Lesson 8 Working with Large Worksheets
2
Sorting by a Single Column
  • Sort Buttons
  • Sort by one column only

Ascending
Descending
If a sorting problem occurs, close without saving
and revert to presorted data.
3
Sorting Selected Rows
  • Select rows to sort if
  • No blank row separates the list from a total row
  • List contains should not be sorted
  • Select entire other rows that rows, not
    individual cells
  • Sort is based on column A

4
Sorting by Multiple Columns
  • Data?Sort Filter?Sort

Sort each column in ascending or descending order
Sort by any number of columns
Any same last names are then sorted by first name
5
Freezing Rows and Columns
Leaves column A visible when scrolling right
Leaves row 1 visible when scrolling down
Use Freeze Panes to lock the headings in
worksheets containing more rows or columns than
can fit on one screen.
6
Splitting the Worksheet Window
  • View different sections (window panes) of a
    worksheet at the same time

Separate scroll bars for vertical panes
Separate scroll bars for horizontal panes
7
Lesson 9 Protecting Workbooks
8
Protecting Workbooks and Worksheets
  • Three levels
  • Workbook level
  • Worksheet level
  • Cell level

9
Protecting Workbooks
  • Protect against
  • Moving a worksheet
  • Adding/deleting worksheets
  • Renaming a worksheet
  • Changing the window size and position

10
Protecting Worksheets
Choose exactly what users may change in each
worksheet
Assign a password to prevent users from turning
off protection.
11
Unlocking Cells before Protecting a Worksheet
  • All cells are locked by default
  • To allow editing in selected cells
  • Remove checkmark to unlock them
  • Protect the worksheet

12
Protecting Workbooks with Digital Signatures
  • Authenticates that the workbook
  • Originated from you
  • Came from a reliable source
  • Has not been altered since it was saved
  • Methods
  • Self-signature limited security
  • Via digital certificate recommended

Your network security administrator can give you
a digital certificate.
13
Creating a Self-Signature
  • Digital ID is valid only on your computer
  • Not necessary if a digital signature is installed

14
Creating a Digital Signature
  • Signing Methods
  • Invisible
  • Signature line in worksheet

Sign using an existing digital ID or certificate
15
Checking Signature Details
  • Workbook status bar display button
  • Signatures task pane
  • Menu

16
Lesson 10 Managing Multiple-Sheet Workbooks
17
Using Multiple Worksheets
  • Summarize data into a summary sheet
  • Create linking formulas between sheets

18
Modifying the Default Number of Slides
  • Excel default is three sheets
  • Change affects only new workbooks

19
Linking Cells Why Link?
  • Reflect management needs
  • Top-level managers want to see the big picture
  • Department-level managers are interested in
    details
  • Automatic updating
  • Results in linked cells update when detail cells
    change
  • Data entered only once

20
Creating Linking Formulas
  • Create the linking formula in the Summary
    worksheet

Source cell reference
Postage!B16
Begin with equals sign
Sheet name of source cell followed by exclamation
point
Use Point Mode to create a linking formula.
21
Copy and Move Worksheets
  • Create an exact duplicate of the original
    worksheet

Check to copy leave blank to move
22
Copy Formats between Worksheets
  • Copy formats consistently between worksheets

Format Painter
Select All button
23
Naming Cells and Ranges
  • Enter a name in the Name box for any cell or cell
    range
  • Use in formulas in place of cell references
  • Available throughout a workbook

Name box PostageTotal is the defined name of
cell B16
24
Naming Rules
  • Must begin with a letter
  • Cannot resemble a cell reference (A3)
  • No spaces, hyphens, or symbols
  • Underscores, periods, capital letters OK
  • Instructional_Materials
  • Instructional.Materials
  • InstructionalMaterials

25
Creating Names from Row or Column Titles
Select titles and values (columns A and B)
Formulas?Defined Names ?Create from Selection
Specify which cells to use for names
Defined names
26
Using Names to Navigate
Choose a name from the list
Highlight displays defined range chosen
27
Using Names in Formulas
  • SUM(Salaries)
  • Sales Expenses
  • TotalPostage
  • Linking formula

28
Modifying and Deleting Defined Names
  • Name Manager

Delete the selected name . . .
. . . or change its cell reference with the
Collapse button
28
29
Types of Hyperlinks
  • Internal
  • To cells in a workbook
  • External
  • To another workbook or non-Excel file
  • To a web page
  • To an email address in Outlook

30
Creating Hyperlinks
Create a ScreenTip to display in the worksheet
Location type
Cell reference and worksheet
Inserted hyperlink
30
31
Printing Multiple-Sheet Workbooks
  • Select multiple worksheets
  • (Shift) Select adjacent sheets
  • (Ctrl) Select nonadjacent sheets
  • Apply page setup options to multiple worksheets
  • Print selected sheets
  • Print all sheets in workbook

31
32
Lesson 11 Creating Tables and Outlines
33
Why Use a Table?
  • Automatic table expansion when rows or columns
    are added
  • Calculated columns copy a formula automatically
  • Table style library
  • Filtering automatically available
  • Function drop-down list for cells in total row

34
Table Parts
Calculated column
Column (field) holds one category of data
Filter button
Row (record) holds data for one person, event, or
transaction
Labels outside the table
Optional total row
35
Converting a Range of Cells to a Table
Choose a table style (may be changed later)
Accept defaults
Select the worksheet range
Rename the table to a descriptive name for use in
formulas.
Result of table conversion
36
Formatting a Table
First Column and Last ColumnHighlight all
entries in the column
Banded RowsApply a fill to alternating rows to
enhance readability
Total RowDisplays total in last column if
contains numbers
37
Summary Formulas in the Total Row
  • A summary function may be chosen from a list for
    any number column

Result of choosing AVERAGE
Result of choosing COUNT
Result of choosing SUM
38
Selecting Table Rows and Columns
Click inside the first cell of a table row to
select the row
Click the top of the table column heading to
select the table column
39
Creating Calculated Columns in a Table
  • Select any cell in the table column
  • Create a formula as usual with Point Mode
  • H5I5
  • Excel converts cell references to structured
    reference in the formula

The formula is applied automatically to all
cells in the calculated column
40
Sorting a Table Column
Drop down the column heading list
Up arrow indicates the list is sorted from A to Z
or lowest to highest
In this example of sorting by color, some text in
the table is red
41
Filtering a Table Column
Drop down the column heading list
Filter by color or for text, such as Begins with
or Does not contain
Indicates that not all records currently display
All records except those with IL are hidden
temporarily
Limit the display to one or more selected entries
42
Custom Filters
Filter will display only records with a Review
Date between April 15 and June 15
Choose Clear Filter from column name in the
column heading list to remove any type of filter.
43
Working with Outlines and Grouping
  • How outlines work
  • Group data by rows and columns

Outline levels are displayed on the top and left
Expand and collapse grouped data
44
Working with Outlines and Grouping
  • Auto outline
  • For smooth Auto Outline, arrange detail data to
    right or left of summary formulas
  • Create groups manually
  • Select detail data and group, even if Auto
    Outline has been performed

45
Displaying Subtotals in an Outline
  • First sort the list by the column on which
    subtotals will be based

46
Lesson 12 Creating PivotTables and Macros
47
Working with PivotTables Example 1
Raw data in worksheet
Summarized by products sold by each city in a
PivotTable
Product totals
City totals
48
Working with PivotTables Example 2
City totals
Summarized by city then by product
49
How PivotTables Work
50
Manipulating Fields on a PivotTable
Three fields are selected
Region field dragged to Column Labels area
Pivoting is the process of dragging a field from
a row to a column, or vice versa.
51
Changing Field Data on a PivotTable
  • Add or remove fields
  • Filter for specific items within rows
  • Suppress display of an item within columns

52
Filtering a PivotTable Report
Choose (All) from the drop-down list to display
all items in the field.
53
Editing PivotTable Calculations
  • Change the function
  • Create a calculated field (custom formula)
  • Update the active PivotTable or all PivotTables
    in workbook

PivotTables do not automatically update after the
source data is changed.
54
Working with PivotCharts
  • Create from a PivotTable or worksheet data
  • Add or move fields in PivotTable Field List task
    pane as usual
  • Filter fields as usual

Format a PivotChart just as you would a regular
chart.
55
Setting the Macro Security Level
  • Choose Office?Excel Options
  • Disabling with notification displays message when
    workbook is opened

56
Macros
  • Set of instructions that can be played back at
    any time
  • Useful for automating routine tasks

57
Recording a Macro
  • Works like a video recorder
  • Turn on recorder
  • Name the macro
  • Perform sequence of keystrokes and mouse clicks
  • Stop recording

Where macro is stored
All keystrokes and mouse clicks are recorded,
including mistakes and corrections. A macro may
be re-recorded, if necessary.
58
Using a Personal Macro Workbook
  • Make macros available in all workbooks on your
    computer system

Macros can be used with any workbook
The Personal Macro Workbook is a hidden workbook.
59
Assigning Macros
  • Macros may be run using
  • Run command in the Macro dialog box
  • Shortcut keys
  • Custom buttons

60
Using Shortcut Keys
  • Run a macro with a few keystrokes

Assigning a shortcut key to run a macro from
within a worksheet
A shortcut key must use either (Ctrl) or
(Ctrl)(Shift) plus a letter.
61
Using Custom Buttons in Worksheets
  • Insert a shape
  • Assign a macro
  • Click the button to run the macro

62
Saving a Workbook as Macro Enabled
  • Use Save As
  • Change the Save As Type
  • Filename is saved with the extension .xlsm

63
Lesson 13 Using Financial Functions and Data
Analysis
64
Using Financial Functions
  • Perform financial calculations
  • Type the function or use the Insert Function
    dialog box

Many categories to choose from
Get help choosing the right category/function
Many functions to choose from
65
Introducing the PMT (Payment) Function
  • Calculate a periodic payment amount

Annual rate divided by 12
of payments (divide by 12 if years)
Loan amount (negative number)
Monthly payment that results
66
Introducing the FV (Future Value) Function
  • Calculate the future value of an annuity

Annual rate divided by 12
Deposit amount (negative number)
of monthly deposits
Type the function in the Formula Bar or use the
Function Wizard
67
Introducing Data Analysis Tools Using Goal Seek
  • Let Excel adjust your variable to achieve a
    desired outcome

Specify desired outcome
Use Goal Seek on any cell with a formula
Choose a cell as a variable for adjustment
68
Using Solver
  • Let Excel adjust multiple variables to achieve a
    desired outcome

To avoid errors, use the point method when
entering cell references.
Adjust multiple variables
Set constraints on any affected cell
69
Using Analysis Tools Scenario Manager
  • Scenario
  • Named combination of values assigned to variables
    in a what-if model
  • Manage scenarios
  • Save and compare multiple scenarios
  • Add scenarios
  • Input values for additional what-if models

70
Using the Data Analysis ToolPak
  • Choose from 19 analysis tools
  • Each tool performs its own function

If Data Analysis is not on the Ribbon, you must
install it (Office?Excel Options?Add-Ins.
71
Using the Data Analysis ToolPak
Performs calculations on the scores in column D
and displays results in columns IL
Original data
Results duplicate and sort scores, which are
ranked from highest to lowest
The rows containing original data in columns AD
are not sorted.
72
Lesson 14 Auditing and Additional Functions
73
Using 3-D Cell References in Formulas
  • Create a formula using data in the same cell on a
    range of worksheets

Deleting a worksheet or moving a worksheet tab to
outside the range in the 3-D reference affects
the formula.
74
Introducing Lookup Functions Using VLOOKUP
  • VLOOKUP (Vertical Lookup)
  • Finds values in tax, commission rate, and other
    lookup tables
  • Syntax consists of three components (arguments)

2nd argumentLookup table
1st argumentCell value to be looked up
3rd argumentColumn number in lookup table
75
How PivotTables Work
Function searches for 14,000 (cell C6) in left
column of lookup table
Formula result
Search takes place in the Comm_Rate lookup
table Search stops at 10,000 because the lookup
value is at least 10,000 but not 20,000
Corresponding contents of 2 columns are returned
to the formula cell
The first column of the lookup table must be
sorted from lowest to highest.
76
Creating a Formula with the IF Function
  • Take an action if the logical test is true
  • Take a different action if false

2nd argumentAction if true
3rd argumentAction if false
1st argumentLogical test to be evaluated
77
Using an IF Function to Display Text
  • Display Yes if true
  • Display blank if false

78
Using Criteria IF Functions
  • Use AVERAGEIF, COUNTIF, and SUMIF to calculate
    using one criterion

2nd argumentCriterion
1st argumentCells to be evaluated
3rd argumentSum only the cells in this range
that meet the criterion
79
Using Criteria IF Functions
  • Use AVERAGEIFS, COUNTIFS, and SUMIFS to calculate
    using up to 127 criteria sets
  • Only cells that meet their respective criteria
    are calculated

3rd argument2nd range to be evaluated
1st argument1st range to be evaluated
4th argumentCriterion for 2nd range
2nd argumentCriterion for 1st range
80
Tracing Formulas Auditing Tools
  • Analyze and debug worksheets
  • Locate formulas dependent on a cell value
  • Locate errors in formulas

81
Tracing Precedents
  • Trace precedents
  • Precedents cells referenced by a formula
  • Trace Precedents command displays arrows to
    precedent cells

82
How Tracing Precedents Works
  • Example
  • Trace precedent cells included in the formula
    C6D6
  • Cells from the lookup table are included they
    are precedents in the D6 formula

Blue tracer arrows indicate precedent cells
83
Tracing Dependents
  • Trace dependents
  • Dependents Cells containing formulas that
    reference the selected cell
  • Trace Dependents command displays arrows to the
    dependent cells

84
How Tracing Dependents Works
  • Example
  • Trace the formulas that depend on the value in
    cell C6

Use the Remove All Arrows command to remove the
tracer arrows.
85
Checking Errors
  • Excel displays a triangle icon
  • Error Checking menu on a cell helps identify the
    cause of a formula error
  • Error Checking command navigates to all cells
    with errors

86
Using Evaluate Formula
  • View the sequence of calculations of each part of
    a formula

87
Using the Watch Window
  • Displays formula results when the formula is out
    of view

88
Lesson 15 Using Advanced Formatting and Analysis
Tools
89
Working with Grouped Worksheets
  • Work simultaneously on all worksheets in the
    group
  • Enter data in cells
  • Format selected cells
  • Copy and paste

The grouped sheet tabs turn white
90
Ungrouping Worksheets
  • Remember to ungroup when ready to enter variable
    data

Ungroup using context menu
Or click a sheet tab not in the group
91
Consolidating Worksheet Data
  • Combine values from source worksheets into a
    destination sheet

Destination worksheet
Use Point Mode to select reference ranges in each
worksheet to be consolidated
Reference list
The summary worksheet does not automatically
update when source data is changed.
92
Consolidation Functions
  • SUM
  • AVERAGE
  • MIN
  • MAX

The SUM function is used most often.
93
Creating Links to Source Data in a Consolidation
  • Displays an outline with source data rows above
    summary row
  • Changes to source data do update in the summary
    worksheet

94
Types of Consolidation
  • By Position
  • References same range in multiple worksheet
  • Works well with identical layouts
  • By Category
  • References cells by row or column
  • Works well with different layouts

95
Working with Data Validation
  • Restrict data entry in cells
  • Types of values
  • Minimum and maximum values

Create input messages and error alert messages to
customize the validation
96
Locating All Invalid Data
  • Red circles indicate a potential problem
  • Ignore or correct problem
  • Red circles are temporary and do not print

97
Working with Conditional Formatting
  • Formatting applied only if cell contents meet set
    conditions
  • Visual alert that a value is outside parameters
  • Choices on the Ribbon

Data bars, color scale, icon sets
Custom rules you create
Presets Frequently used formats
98
Creating a Custom Conditional Formatting Rule
Conditional formatting is used as a visual alert
to indicate that a value is outside the
parameters.
99
Data Tables One Variable
  • Helpful in what-if analyses
  • Values from data table substituted for a cell
    reference in a formula
  • Formula FV(B3/12, B4,B5) is the basis for the
    data table calculations shown

Each payment value in the data table is
substituted for cell B5 in the formula
Value of 0 is returned in the cell with the
formula
Formula results for each payment value
Column of payment values that you input
100
Data Tables Two Variables
  • The same formula FV(B3/12, B4,B5) with
    variables substituted for two cell references
  • A result displays for every combination of rate
    and payment listed in the table

Row of interest rates you input
The input cells are blank because multiple values
are substituted from the row and column.
Formula results for each combination of rate and
payment
Column of payment values you input
101
Creating Trendlines on Charts
  • Linear trendline
  • Best-fit straight line that shows data trend

102
Creating Trendlines on Charts
  • Polygonal trendline
  • Smooth out fluctuations by averaging adjacent
    data points

Increase the Order option value to make a
smoother trendline.
103
Lesson 16 Integrating Excel with Other Programs
104
About File Formats
  • File format
  • Structure for storing data in a computer file
  • Native file format
  • Application program normally used to save files
  • Identifying a files format

By filename extension
By icon
Choose to display filename extensions in Windows
Explorer.
105
Compatibility with Previous Excel Versions
  • Excel 2007 file formats
  • Open XML, based on Extensible Markup Language
  • Prior versions
  • Different file formats
  • Title bar identifies these files

106
Compatibility with Previous Excel Versions
  • Save a workbook in a different Excel file format

Compatible file format
107
Handling Compatibility Issues
  • Compatibility Checker
  • File owner scans workbook
  • Any changes to correct incompatibility remove
    features in all Excel versions
  • Compatibility Pack
  • Users of versions 2000, 2002 (XP), and 2003
    install a file converter
  • Incompatible features hidden
  • Features visible when file is reopened in Excel
    2007

108
Handling Compatibility Issues
  • Scan non-XML files with Compatibility Checker

Correct any areas marked
Locate these cells in worksheet
Copy compatibility report to a new, printable
worksheet
109
Converting Workbooks to Other File Formats
  • Converter program allows an application program
    to open or save files in nonnative formats
  • Text
  • PDF
  • XPS
  • Several converters are installed in Excel

Excel warns you that formatting or features may
be lost with the new file format. You may first
save in Excel Workbook format to preserve a copy
of the workbook.
110
Types of Text Files
  • Tab delimited
  • Arrows do not print
  • Comma delimited
  • Characteristics
  • Saves worksheet data in a text-only format
  • Removes all formatting
  • May be only method for transferring data between
    incompatible programs

111
Saving Excel Data in Text Formats
  • Save a worksheet in tab delimited file format
  • Compare icons for files saved in different
    formats
  • Open and view text files in Notepad

112
Additional File Format Types
  • PDF (Portable Document Format)
  • XPS (XML Paper Specification
  • Characteristics
  • Users may use a free reader to view/print
    workbooks with all formatting intact dont need
    Excel at all
  • Prevents users from making any changes or
    accessing hidden information

The PDF and XPS command appears on the Save As
submenu after you download and install a
converter.
113
Saving Excel Data in PDF Format
  • Save a worksheet in PDF format
  • Open and view workbook in Adobe Acrobat Reader

114
Using Excel Tables with Word Mail Merge
Main document containing merge field codes in Word
Selected records in Excel
Personalized document copies in Word
115
Sharing Excel Data with Access
  • Use the filter, query, and report capabilities in
    Access

Choosing options in the Import Spreadsheet Wizard
(Access)
When the link option is selected, any changes to
the original worksheet will update in the Access
table.
Resulting Access table
116
Link Excel Charts on PowerPoint Slides
  • Copy chart in Excel
  • Paste options
  • Link to chart data (default)
  • Embed the entire workbook along with the chart
  • Paste the chart as a picture only

Slide containing the chart pasted into a content
placeholder
Embedding a chart gives the user access to all
data in the workbook, which may not be desirable.
117
Importing Data from Other Applications
  • Drag and drop

Dragging and dropping cuts the text from the
source document. Restore it with Undo or by
closing without saving.
118
Importing Data from Other Applications
  • Copy and paste
  • Adjust formatting after pasting

Copy
Paste
119
Importing External Data
  • Import data from text files
  • Adjust formatting after importing text

The Text Import Wizard guides you through
importing text file data.
Preview shows tab codes as small boxes, not arrows
120
Importing External Data
  • Import data from a web page
  • Adjust formatting after importing web data

Enter the desired URL in the Internet Explorer
window
Indicate tables that may be selected from the web
page once selected, arrows turn green
About PowerShow.com