copyright 2007, paradigm publishing inc. excel 2007 chapter 7 backnextend 7-1 links to objectives...

Copyright 2007, Paradigm Publishing Inc.

EXCEL 2007Chapter 7

BACK NEXT END

7-1

LINKS TO OBJECTIVES• Record & run a macr

o• Save as a macro-ena

bled workbook• Change macro secur

ity • Edit, delete macros• Personal Macro

Workbook• Custom Lists• Pin & unpin

frequently used files• Customize Quick

Access Toolbar• Hide the Ribbon bar• Create a custom

workbook theme• Create & use a

template• Features Summary

Microsoft Excel 2007 – Level 2

AUTOMATING REPETITIVE TASKS AND

CUSTOMIZING EXCEL


EXCEL 2007Chapter 7

BACK NEXT END

7-2










Performance ObjectivesPerformance Objectives• Record and run a macro• Save a workbook as a macro-enabled workbook• Create a macro that is run with shortcut keys• Change the macro security settings• Edit and delete a macro• Save a macro to the Personal Macro Workbook

for use in all workbooks• Create and delete a custom fill series• Pin and unpin a frequently-used file to the Recent

Documents list• Add and remove buttons for frequently-used

commands to the Quick Access toolbar• Hide the ribbon to increase space in the work area• Customize the work area to hide gridlines and

column and row headers• Create a custom workbook theme• Create and use a template


EXCEL 2007Chapter 7

BACK NEXT END

7-3










Creating a MacroCreating a Macro

A macro is a named series of instructions that can be recalled by

– selecting a menu item– pressing a particular key combination

Each macro must have a unique name–must begin with a letter–can be a combination of letters, numbers, and underscore characters with no spaces–may not be the same as a cell referenceMacro instructions are stored as Visual Basic (VBA) instructions either with the current workbook or available to all other workbooks


EXCEL 2007Chapter 7

BACK NEXT END

7-4










The Macro RecorderThe Macro Recorder

• Turn ON the Macro Recorder to record the series of instructions

• The Macro Recorder works much like a tape recorder to record every keystroke and mouse click

Click

Top button views existing macros

Bottom button

opens this list

OR click the Macro Recorder button in the left side of the Status bar


EXCEL 2007Chapter 7

BACK NEXT END

7-5










Recording a MacroRecording a Macro

View tab, Macros button, Record Macro

Enter a descriptive name

Enter the letter to presswith the Ctrl key

to execute the macro-Case sensitive

Enter a description

Click

Select where the macro

is to be stored

Record Macro dialog box


EXCEL 2007Chapter 7

BACK NEXT END

7-6










Recording a Macro…/2Recording a Macro…/2

Click toStop Recording

HINTSSave before running a macro.

Before starting the macro, consider if the location of the active cell is a factor.

With the Macro Recorder ON,

Type information, press the Enter key, click menu items, use shortcut keys –all keystrokes and most

mouse clicks will be recorded


EXCEL 2007Chapter 7

BACK NEXT END

7-7










Saving the MacroSaving the Macro

Must save the workbook as ‘macro-enabled’

Saving as type ‘Excel Macro-

Enabled Workbook’

creates a .xlsm workbook which

includes Visual Basic

(VBA) code for the macro

ClickOR

In Save As dialog

box


EXCEL 2007Chapter 7

BACK NEXT END

7-8










Running (Playing) a Macro

Double-click the macro

Select the macro, then

click Run

OR

View tab, Macros button(top button)


EXCEL 2007Chapter 7

BACK NEXT END

7-9










Running a Macro…/2Running a Macro…/2

Click to edit theshortcut key andthe description

If a keyboard shortcut has been assigned to the macro, the macro can be run by simply

pressing its shortcut key combination


EXCEL 2007Chapter 7

BACK NEXT END

7-10










Enable Macros in a WorkbookEnable Macros in a Workbook

Excel can place a Security Warning above the worksheet when opening a workbook

that contains macros

Click

Read carefully,

click ‘Enable this

content’, and click

OK

Only enable the content when the

workbook is from a trusted source


EXCEL 2007Chapter 7

BACK NEXT END

7-11










Changing Macro Security SettingsChanging Macro Security Settings

Click

Choosedesired

settings


EXCEL 2007Chapter 7

BACK NEXT END

7-12










Changing Macro Security Settings/2Changing Macro Security Settings/2

Macro Setting Description

Disable all macros without notification

All macros are disabled; security alerts will not appear

Disable all macros with notification

All macros are disabled; security alert appears with the option to enable content if you trust the source of the file. (Default setting)

Disable all macros except digitally signed macros

All macros not digitally signed are disabled; security alerts do not appear.If digitally signed by a publisher in the Trusted Sources list, the macro is allowed to run. If digitally signed, but not in the list, a security warning will appear.

Enable all macros(not recommended)

All macros are allowed; security alerts do not appear.

Macro Security when opening workbooks …


EXCEL 2007Chapter 7

BACK NEXT END

7-13










Editing a MacroEditing a Macro

Click to edit the macro using the

Visual Basic Editor

Select the

macro

View tab, Macros button

Often easier to delete the macro and re-record it


EXCEL 2007Chapter 7

BACK NEXT END

7-14










Editing a Macro…/2Editing a Macro…/2

Beginning of macroComment lines

Statements are

executable lines of code

End of macro


EXCEL 2007Chapter 7

BACK NEXT END

7-15










Saving a Macro to the Personal Macro Workbook

Macros are stored by default in the current workbook, and are available to all other open workbooks as long as the current workbook

is open.Macros stored in the Personal Macro

Workbook are available whenever Excel is open on the current computer.

When creating the macro, click the down arrow and choose Personal Macro Workbook


EXCEL 2007Chapter 7

BACK NEXT END

7-16










Deleting a Macro

Selectthe macro

Click to delete

the selected macro

View tab, Macros button(top button)


EXCEL 2007Chapter 7

BACK NEXT END

7-17










Creating a Custom List SeriesUse to fill a series of cells with the Fill

handleor to create a list with an unusual sort

order

Click


EXCEL 2007Chapter 7

BACK NEXT END

7-18










Creating a Custom List Series /2

Import the list

1. Type entries into a worksheet2. Select the range containing the entries

3. Open Excel Options, Edit Custom Lists

4. Click Import

5. Click OK


EXCEL 2007Chapter 7

BACK NEXT END

7-19










Creating a Custom List Series /3

Add a list by typing it in

1. Open Excel Options, Edit Custom Lists

2. Select NEW LIST

5. Click OK

4. Click Add3. Type

the list here, one entry per line


EXCEL 2007Chapter 7

BACK NEXT END

7-20










Deleting a Custom List

1. Open Excel Options, Edit Custom Lists2. Select the list

3. Click Delete

4. Click OK

A warning message

is displayed


EXCEL 2007Chapter 7

BACK NEXT END

7-21










Pinning Workbooks to the Recent Documents List

Click the Office button to see the Recent Documents list

Click the pin of the frequently-used workbook

Click again to unpin it


EXCEL 2007Chapter 7

BACK NEXT END

7-22










Customizing the Quick Access Toolbar

Quick Access Toolbar

Click

Toggle on or off

Click to re-order,

add, or delete

commands


EXCEL 2007Chapter 7

BACK NEXT END

7-23










Minimizing the Ribbon

Use to provide more Excel work area space

To toggle the ribbon on or offDouble-click a tab

Click the ‘Customize Quick Access Toolbar’ button and click ‘Minimize the Ribbon’

OR

OR

Press Ctrl + F1


EXCEL 2007Chapter 7

BACK NEXT END

7-24










Creating Custom Themes

A Theme is

… a combination of theme colors, theme fonts, and theme effects

… stored in the Themes drop-down gallery

Page Layout tab


EXCEL 2007Chapter 7

BACK NEXT END

7-25










Modifying Theme Colors

Page Layout tab, Theme Colors

Click to create your own

Change Colors, Name,

and Save


EXCEL 2007Chapter 7

BACK NEXT END

7-26










Modifying Theme Fonts

Page Layout tab, Fonts

Click to create

custom font

Choose Heading font and Body font

Type a Name and click

Save


EXCEL 2007Chapter 7

BACK NEXT END

7-27










Applying Custom Theme Colors & Fonts

Page Layout tab, Colors

Custom Color set appears at top of Colors list

Custom Font set appears at top of Fonts list


EXCEL 2007Chapter 7

BACK NEXT END

7-28










Applying Theme Effects

Page Layout tab, Effects

Theme effects are sets of lines and fill effects

Although you cannot create your own set of theme effects, you can choose the one that you want to use in your own document theme


EXCEL 2007Chapter 7

BACK NEXT END

7-29










Saving a Workbook Theme

Page Layout tab, Themes

After setting thecolors, fonts,and effects,click Save

Current Theme…

Enter Name and click Save


EXCEL 2007Chapter 7

BACK NEXT END

7-30










Resetting to Template Theme


To reset the theme for

the current document,

click


EXCEL 2007Chapter 7

BACK NEXT END

7-31










Deleting Custom Themes


To delete a custom theme,

right-click the theme and click


EXCEL 2007Chapter 7

BACK NEXT END

7-32










Saving a Workbook as a Template

A template contains standard text, formulas, and formatting for non-

changing cells and formatting for cells that will change

Examples of templates: invoice, purchase order, expense form, time card

1. Create a workbook that contains all the standard data, formulas, and formatting 2. Clear the data from cells that will have variable information, but leave formatting

3. Save As the workbook, changing the Save as type to Excel Template (.xlst)


EXCEL 2007Chapter 7

BACK NEXT END

7-33










Using a Custom Template

With the prepared workbook open,click Not this

button

thenclick

Double-click

desired template


EXCEL 2007Chapter 7

BACK NEXT END

7-34










Deleting a Custom Template

Click

Not this button

thenclick

Right-click desired

templateand click Delete


EXCEL 2007Chapter 7

BACK NEXT END

7-35










How do you record a macro?

Record Macro button

in the Status barOR

Features Summary

Click


EXCEL 2007Chapter 7

BACK NEXT END

7-36










How do you run a macro?

Features Summary

View tab, Macros button Double-click

the macro

Select the macro, then

click Run

OR

Press themacro’s

shortcut keys

OR


EXCEL 2007Chapter 7

BACK NEXT END

7-37










How do you delete a macro?

Features Summary

Selectthe macro

Click to delete

the selected macro

View tab, Macros button


EXCEL 2007Chapter 7

BACK NEXT END

7-38










How do you view or change the Macro Security options?

Click the Office button, Excel OptionsClick Trust Center in the sidebar,Click ‘Trust Center Settings’ –Click Macro Settings in the sidebar

Features Summary


EXCEL 2007Chapter 7

BACK NEXT END

7-39










How can you open the Custom Lists dialog box?

Features Summary

Open Excel Options and click Edit Custom Lists


EXCEL 2007Chapter 7

BACK NEXT END

7-40










After opening the Custom Lists dialog box, how do you create a custom list?

Click NEW LIST, click Add and type in

the entries

OR

Features Summary

Import the list from a range in a worksheet


EXCEL 2007Chapter 7

BACK NEXT END

7-41










What must be changed to create a custom theme set?

Theme colors, Theme fonts, and Theme effects

Features Summary


EXCEL 2007Chapter 7

BACK NEXT END

7-42










How can you save your custom theme set?Page Layout tab, Themes

After setting thecolors, fonts,and effects,click Save

Current Theme…

Type name and click Save

Features Summary


EXCEL 2007Chapter 7

BACK NEXT END

7-43










How do you change a custom theme after it has been saved?

Page Layout tab, Colors

Right-clickcustom color or font set

and choose Edit…

Page Layout tab, Fonts

OR

Click

Features Summary


EXCEL 2007Chapter 7

BACK NEXT END

7-44










How do you create a template?

Create a workbook with preset text, formulas, and formatting, format and clear

cells to be filled in, and Save AS the workbook as a template

Features Summary


EXCEL 2007Chapter 7

BACK NEXT END

7-45










Coming Next

IMPORTING, EXPORTING, AND DISTRIBUTING

DATA