Transcript Microsoft Word 2013 - Warren Hills Regional School District

Microsoft Office
Microsoft
Office
Excel 2013 Core
Excel 2013 Core
Courseware # 3253
Lesson 8: Macros,
Importing and Exporting
Data
Microsoft Office
Excel 2013 Core
Lesson Objectives
• customize the Quick Access
Toolbar and Ribbon
• import and export data
between other programs
• find data in the worksheet
• change workbook document
properties
• replace data in the
worksheet with different
data
• save workbooks to SkyDrive
• create and use macros
• create, modify, and delete
hyperlinks
• save and open workbooks
containing macros
• create and delete
comments
• add macros to the Quick
Access Toolbar
© CCI Learning Solutions Inc.
2
Microsoft Office
Excel 2013 Core
Customizing the Quick Access Toolbar
• To customize:
– click File, click Options,
then click Quick Access
Toolbar, or
– click Customize Quick
Access Toolbar button
then select from list box
Excel Control
Icon
Undo
Save
Redo
Customize Quick
Access Toolbar
– right-click on Ribbon, then
click Customize Quick
Access Toolbar
© CCI Learning Solutions Inc.
3
Microsoft Office
Excel 2013 Core
Customizing the Ribbon
• To customize:
– click File, click Options,
then click Customize
Ribbon, or
– right-click on Ribbon,
then click Customize
the Ribbon
© CCI Learning Solutions Inc.
4
Microsoft Office
Excel 2013 Core
Finding and Replacing Data
Finding Data
• Can be used to find every occurrence of a value, function name,
or cell reference
• On Home tab, in Editing group, click Find & Select, then click
Find
© CCI Learning Solutions Inc.
5
Microsoft Office
Excel 2013 Core
Finding and Replacing Data
Replacing Data
• On the Home tab, in the Editing group, click Find & Select, then
click Replace
• Replace will only replace next occurrence, Replace All will replace
every occurrence at one time
© CCI Learning Solutions Inc.
6
Microsoft Office
Excel 2013 Core
Inserting Hyperlinks
• To insert hyperlink:
– On Insert tab, in Links group, click Hyperlink; or
– right-click on a cell and click Hyperlink, or
– press CTRL + K
• Insert hyperlink to:
– Go to another
workbook, file, or
web page
– Go to a cell or
named range in
workbook
– Create a new
workbook
– Create a new e-mail message
© CCI Learning Solutions Inc.
7
Microsoft Office
Excel 2013 Core
Modifying and Deleting Hyperlinks
• If referenced file moved or renamed, must change
hyperlink
– change text displayed for hyperlink, or add custom ScreenTip
• When no longer need hyperlink, remove it
– hyperlink text remains
• To modify hyperlink:
– Right-click hyperlink and click Edit Hyperlink; or
– move to cell containing link and on Insert tab, in Links group, click
Hyperlink
• To delete hyperlink:
– Right-click hyperlink and click Remove Hyperlink; or
– move to cell containing link and on Insert tab, in Links group, click
Hyperlink, click Remove Link.
© CCI Learning Solutions Inc.
8
Microsoft Office
Excel 2013 Core
Using Comments
• Use like “sticky” notes
• Different users can enter their
comments
• Works well with large
spreadsheets
– Comments are embedded
directly into cells
– Only displayed when you
want
• Buttons are in the Review tab
© CCI Learning Solutions Inc.
9
Microsoft Office
Excel 2013 Core
Importing Data Files
• Excel can import external data files by converting them
• On Data tab, in Get External Data group, select where
data file came from
• If not from MS Access, a website,
or a text file, click From Other
Sources
© CCI Learning Solutions Inc.
10
Microsoft Office
Excel 2013 Core
Importing Data Files
• Text format has two sub-formats:
– Delimited
• Text data separated by pre-defined character
• Separates line of text into separate values to insert into each
worksheet cell
– Fixed width
• Each value has specific start and end position in every line of text
• Position of the text separates data into each worksheet cell
• XML is rapidly becoming a standard format between
different systems
© CCI Learning Solutions Inc.
11
Microsoft Office
Excel 2013 Core
Open Non-Native Files Directly in Excel
• Excel is able to directly open many types of external
data files
© CCI Learning Solutions Inc.
12
Microsoft Office
Excel 2013 Core
Exporting Data from Excel
• Use Save as type list to select different formats for saving
worksheet data
© CCI Learning Solutions Inc.
13
Microsoft Office
Excel 2013 Core
Changing Workbook Properties
• Workbook properties contains
information about workbooks
– Useful when there are many
workbooks with similar names
• Good practice to remove
personal or company data
before sharing with others
© CCI Learning Solutions Inc.
14
Microsoft Office
Excel 2013 Core
Sharing Workbook Using SkyDrive
• Need a Microsoft account to access SkyDrive
• Save workbook to SkyDrive like a local hard drive, but is
actually on Internet:
– Access your files from
any computer
– Automatically add and
remove files using
SkyDrive app
– Share files with others
– Use Office Web App
© CCI Learning Solutions Inc.
15
Microsoft Office
Excel 2013 Core
Sharing Workbook Using SkyDrive
• Excel is integrated with SkyDrive
• Your account name and avatar is
displayed in upper right corner of Excel
• SkyDrive has two main folders:
– My Documents: use to store files that you do
not want to share with others
– Public: use to share files with others
© CCI Learning Solutions Inc.
16
Microsoft Office
Excel 2013 Core
Macros
• A set of actions that can be recorded and executed many
times with a single command
• Useful for tasks that have to be repeated in a workbook
• Uses Visual Basic for Applications (VBA)
• Tool provided to easily create a macro
• Macros executed using:
–
–
–
–
a shortcut key, or
Quick Access Toolbar, or
command button, or
when workbook is opened
© CCI Learning Solutions Inc.
17
Microsoft Office
Excel 2013 Core
Creating a Macro
• Macro Recorder used to record
every action until turned off
• Shortcut key is a single character
– Avoid certain characters used
by Excel or Windows
– Try to only use capital letters
• If you select a cell while recording a
macro, then macro will always go to this cell while
running a macro; avoid this using one of the following:
– Select your cell before beginning recording of macro
– Use the Relative Reference option button
© CCI Learning Solutions Inc.
18
Microsoft Office
Excel 2013 Core
Saving and Opening a Workbook Containing
Macros
• Workbooks containing macros must be saved as
macro-enabled workbooks (*.xlsm)
– If you forget, Excel displays an error message
© CCI Learning Solutions Inc.
19
Microsoft Office
Excel 2013 Core
Saving and Opening a Workbook Containing
Macros
• Trust Center macro security
settings controls treatment of
macro-enabled workbooks
• Default is display a security
warning bar – if nothing is
done macros stay disabled
• Trusted Locations can be designated to prevent this security
warning from appearing for every workbook
© CCI Learning Solutions Inc.
20
Microsoft Office
Excel 2013 Core
Adding Macros to the Quick Access Toolbar
• Convenient way to access a macro
• Can also select a special icon for it
© CCI Learning Solutions Inc.
21
Microsoft Office
Excel 2013 Core
Lesson Summary
• customize the Quick Access
Toolbar and Ribbon
• find data in the worksheet
• replace data in the
worksheet with different
data
• create, modify, and delete
hyperlinks
• create and delete
comments
© CCI Learning Solutions Inc.
• import and export data
between other programs
• change workbook
document properties
• save workbooks to SkyDrive
• create and use macros
• save and open workbooks
containing macros
• add macros to the Quick
Access Toolbar
22
Microsoft Office
Excel 2013 Core
Review Questions
1. You can add any Excel command to the Quick Access Toolbar
including those that are not currently in the Ribbon.
a)
b)
True
False
2. What items can Excel search for in a worksheet?
3. Give an example of when you would use Replace rather than
Replace All in a worksheet.
4. A hyperlink in a workbook can be used to (select all that apply):
a)
b)
c)
d)
e)
Create a new workbook.
Open a different workbook that is stored on your computer.
Launch an Internet web page.
Create an email addressed to one of your co-workers.
Jump to another cell in the same worksheet.
© CCI Learning Solutions Inc.
23
Microsoft Office
Excel 2013 Core
Review Questions
5. What can you use comments for in a worksheet?
6. Excel is capable of importing data stored in the following
formats (select all that apply):
a)
b)
c)
d)
e)
Extensible Markup Language (XML).
Plain text.
Comma Separated Values (CSV).
Microsoft SQL Server.
Open Document format.
7. Excel is capable of exporting data using the following formats
(select all that apply):
a)
b)
c)
d)
e)
Extensible Markup Language (XML).
Plain text.
Comma Separated Values (CSV).
Microsoft SQL Server.
Open Document format.
© CCI Learning Solutions Inc.
24
Microsoft Office
Excel 2013 Core
Review Questions
8. You can access files that are stored in the Public folder in
anyone’s SkyDrive – even those that belong to people that you
do not know.
a)
b)
True
False
9. A macro is:
a)
b)
c)
d)
Only available after you have enabled it using the Excel Add-In tool.
Somewhat intimidating because of the complex code you must enter accurately.
Used to automate a series of steps which is useful for repetitive tasks.
A type of fish.
10. A workbook containing a macro must be saved as a macroenabled workbook.
a)
b)
True
False
© CCI Learning Solutions Inc.
25