[PDF] Excel 2016 Level 3 - Dublin 08/02/2019 Simply open

View - Download Excel 2016 Level 3 - Dublin

Previous PDF

Next PDF

The Computer Workshop, Inc.

800-639-3535

www.tcworkshop.com training@tcworkshop.com

Excel 2016

Level 3

Lesson Notes

Excel 2016

Level 3

Course Number: 0200-300-16-W

Course Release Number: 3.0

Software Release Number: 2016

2/8/2019

Developed by:

Brian Ireson

Suzanne Hixon

Thelma Tippie

Edited by:

Published by:

RoundTown Publishing

5131 Post Road, Suite 102

Dublin, Ohio 43017

for

The Computer Workshop, Inc.

5131 Post Road, Suite 102

Dublin, Ohio 43017

(614) 798-9505 in any form or by any means, mechanical or electronic, including photoco pying, recording, storage in an

Disclaimer:

Round Town Publishing produced this manual with great care to make it of good quality and accurate, and

therefore, provides no warranties for this publication whatsoever, including, but not limited to, the implied

notice.

Trademark Notices:

The Computer Workshop, Inc. and The Computer Workshop logo are registere d trademarks of The

Computer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], and [Access] are registered

trademarks of Microsoft Corporation. [Photoshop] and [InDesign] are a r egistered trademark of Adobe. All of their respective companies. Using any of these trade names is for editorial purposes on ly and in no way is

Preface

Page iv

Excel 2016: Level 3, Rel. 3.0, 2/8/2019

Table of

Contents

Table of Contents

Using this Manual

To Download Data Files ...............................................viii

Conventions

...ix Conventions Used in this Manual ................................ix

Lesson 1: Macros and VBA

What is a Macro? .....................................................................3

Saving Files with Macros

3

Developer Ribbon

4

Adding the Developer Tab

4

Macro Naming

6 Macro Naming Rules ........................................................6 Understanding Macro Referencing ................................7

Macro Referencing

7 Recording a Macro ..................................................................8

Creating a Macro Using the View Tab

8

Creating a Macro Using the Developer Tab

9

Running a Macro

12 Run a Macro Using the Macro Dialog Box ..................12

Editing a Macro

14

Visual Basic for Applications

14

Recording a Macro in the Personal Workbook

18 Personal Macro Workbooks ...........................................18

Saving a Macro in the Personal Macro Workbook

18 Running a Personal Macro Workbook Macro ..................22 Using a Personal Macro..................................................22 Editing Personal Macros ......................................................23

Accessing the Personal Macro Workbook

...................23 Editing the Personal Macro ...........................................23 ...................................26 26
Recording a Relative Reference Macro ..............................29 Relative Reference Macros .............................................29 Removing A Personal Macro ...............................................32 Removing a Personal Macro Workbook Macro ..........32 34

Macro Security

37

Macro Security

37

Excel 2016: Level 3, Rel. 3.0, 2/8/2019 Page v

Preface

Table of

Contents,

continued

Lesson 2: Form Controls

Form Controls

41

What is a Form Control?

42
Adding Form Controls .........................................................44

Adding a Form Control to a Worksheet

44

Modifying Form Controls

46
Adding a Combo Box ...........................................................52 The Combo Box Control .................................................52 Adding A Group Box............................................................56

Adding the Group Box Control

56
ȱȱĴȱ ...................................57 57
58
ȱȱĴ ...................................................................62 ȱĴȱȱ ...............................................62 Assigning a Macro to a Form Control ..........................63 Assigning a Macro to a Form Control ................................63 Lesson 3: Hyperlinks and Connecting to the Internet

What is a Hyperlink

What is a Hyperlink?

71
Creating a Hyperlink ............................................................72

Inserting Hyperlinks

Link to Existing File or Web Page

73

Link to Place in this Document

74

Link to Create New Document

74

To Follow a Hyperlink in Excel

75

Modifying Hyperlinks

82

Modifying Hyperlinks

82
82
83
85
85
89
89
91

Refreshing the Data

91

Preface

Page vi

Excel 2016: Level 3, Rel. 3.0, 2/8/2019

Table of

Contents,

continued

Lesson 4: Outlining, Subtotals, and Consolidation

Outlining ........................................................................ ........97 Automatic Outlining .............................................................98

Using an Automatic Outline

98

Exploring Outlined Data

Showing or Hiding Outlined Data

99

Removing an Automatic Outline

100

Grouping Data Manually

To Manually Outline Data

103

To Remove Manual Groupings

103
Creating Subtotals ...............................................................106 Using the Subtotal Feature ..........................................106

Removing Subtotals

107

Consolidating Workbooks

111
To Consolidate Workbooks .........................................112

Lesson 5: Analytical Tools

Goal Seek

119

To use Goal Seek

119

Data Tables

123

Creating a One-Input Data Table:

123

Two-Input Data Table

127

Scenarios

130
Adding Scenario's .........................................................130

Showing Scenarios

132

Editing a Scenario

135
Removing Scenarios......................................................135

Solver

137

Installing the Solver Add-in

Using

Solver

139

Forecasting

147

Create a Simple Forecast

147

Customizing the Forecast

148

Lesson 6: Sharing and Linking Workbooks

Shared Workbooks

155

Sharing a Workbook

156
Sharing Limitations.......................................................156

How to Share a Workbook

157

Opening and Editing a Shared Workbook

159

Tracking Changes

161

Tracking Changes

161

User Credentials for Tracking Changes

161
Highlighting Changes ..................................................161

The History worksheet

163

Excel 2016: Level 3, Rel. 3.0, 2/8/2019 Page vii

Preface

Table of

Contents,

continued

Adding the History Worksheet

163
166

Accepting and Rejecting Changes

168

Accepting and Rejecting Changes

168

Protecting Shared Workbooks

171

Protecting a Shared Workbook

171

Appendix A: The Default Workbook

Lesson Overview

The Default Workbook

................A1

Saving Cell Styles to the Default Workbook

.............A1

Appendix B: Querying a Website with Excel

Standard

..............B5

Refreshing the Data

Preface

Page viii

Excel 2016: Level 3, Rel. 3.0, 2/8/2019

Welcome to the

Excel 2016 Level 3

course. This manual and time from

The Computer Workshop

website: website in the Download section.

To Download Data Files

Once on

The Computer Workshops

ȱȱȱȱęȱȱȱDownload. Clicking this link opens the Download page where you can choose either Data Files or

Handouts.

1. Data Files opens a list of general application types. 2.

Click once on the ȱĜȱ link.

3.

Click once on the software related to the course.

4.

Click once on the version related to the course.

5. If there are multiple folders, click on the folder. 6. your computer. The handouts are in PDF format and also available to you without login or password. Simply open the PDF and either print or save to your computer.

Using this

Manual

Preface

Page ix

Excel 2016: Level 3, Rel. 3.0, 2/8/2019

Conventions Used in this Manual

format. The left column (“Instructions") gives numbered instructions, such as what to type, keys to press, commands to choose from menus, etc. The right column (“Results/ Comments"), contains comments describing results of, reasons for, quick keys, etc. for the instructions listed on the left.

Key names and Functions are bold and enclosed in

square brackets: Keys you press simultaneously are separated by a plus (+) sign, typed in bold and enclosed in square brackets. Keys you press in sequence are separated by a space, bold and enclosed in square brackets. Ribbon tab names are in bold and italic: Example: Home

Group names are in bold: Example:

Dialog box names are in italic: Example: Save As

ĴȱȱȱȱȱȱȱȱDZȱȱExample: ǽǾ Information you are to type will be in bold. Example:

Information that you need to supply will be indicated with pointed brackets. Example: Type: <ȱ>.

Conventions

Tips and Notes

Lesson Overview

You will cover the following concepts in this

chapter:

Excel 2016: Level 3

Rel. 3.0, 2/8/2019

Lesson 1: Macros and

VBA

What is a Macro?

Saving Files with Macros

Developer Ribbon

Macro Naming

Macro Referencing

Recording a Macro

Running a Macro

Editing a Macro

Recording a Macro in the Personal Workbook Running a Personal Macro Workbook Macro

Editing Personal Macros

Recording a Relative Reference Macro

Removing A Personal Macro

Macro Security

Lesson Notes

Excel 2016: Level 3, Rel. 3.0, Recording a Macro in the Personal Workbook Page 3

Lesson 1: Macros and VBA

What is a Macro?

Saving Files with

Macros

A Macro can be described as a tiny program that uses Visual Basic code to automate a sequence of actions or instructions. A Macro can be simple and consist of only a few tasks or commands, or be quite complex, involving lots of data

ȱȱǯȱȱȱȱMacros, it is

worthwhile to invest time in learning Visual Basic programming.

But for simple

Macros

, you don"t have to do any programming at all.

Simple

Macros

are great for any long sequence of keystrokes basic Excel actions that would be convenient to automate for a particular workbook.

Macros

can be saved with the workbook in which they were created, or they can be saved in a separate personal Macro workbook where they are accessible all of the time. As mentioned previously, Excel will record the actions you take when creating a Macro and convert them into Visual Basic code.

ȱȱȱȱȱȱȱȱExcel needs to

execute your Macro Once you plan on creating Macros in a workbook, it must be saved as a macro-enabled workbook. When performing a Save As choose the second option from the drop-down list within the Save As TypeȱęǯȱȱȱExcel workbook will have an extension of .xlsm.

Lesson 1: Macros and VBA

Page 4

Excel 2016: Level 3, Rel. 3.0, Recording a Macro in the Personal Workbook

Developer

Ribbon

Adding the Developer Tab

Macro in Excel make sure that the

Developer

TabȱȱǯȱȱDeveloper

Tab is your gateway to creating and editing Macros in Excel 2016
. If the

Developer Tab

is not showing on the Ribbon, do the following:

Select the File Tab

Click the Options in the index on the left.

Click the Customize Ribbon category on the left of the

Excel Options dialog box.

On the right in the Customize the Ribbon: list, click the checkbox before

Developer

. A checkmark should be added to the checkbox. Click [OK] to close the Excel Optionsȱȱǯȱȱ

Developer Tab

now appears at the top of the Excel screen.

Click the Developer Tab to activate it.

Excel 2016: Level 3, Rel. 3.0, Recording a Macro in the Personal Workbook

Lesson 1: Macros and VBA, Page 5

Instructions:

Results/ Comments:

Action 1.1 - Adding the Developer Tab

1.

Start Excel. Notice there is no Developer

Tab. 2.

Click the File Tab and choose Options

from the categories listed on the left side of the Backstage view. 3.

Select Customize Ribbon from the

categories on the left. 4. checkbox next to

Developer

and click the [OK]ȱĴǯ 5.

Activate the Developer Tab on the ribbon.

6.

Locate the Code Group.

7.

Locate the Controls Group.

8.

Click the View Tab.

9.

Locate the Macros Group.

10.

Close the workbook without saving

A blank spreadsheet is opened, if not then click the blank workbook option from the list of templates.

Excel Options dialog box opens.

Options related to customizing the ribbon

are displayed.

Developer Tab

will now be added to the ribbon.quotesdbs_dbs44.pdfusesText_44

[PDF] de quoi est composé le royaume uni

[PDF] de quoi est composé the united kingdom

[PDF] maitriser excel 2013 pdf

[PDF] relief de londres

[PDF] culture du royaume uni

[PDF] relief royaume uni

[PDF] excel pour les nuls pdf gratuit

[PDF] climat du royaume uni

[PDF] carte relief angleterre

[PDF] faune royaume uni

[PDF] différence enrichissement sans cause et répétition de l'indu

[PDF] régime général des obligations cours

[PDF] remboursement indu caf

[PDF] régime général des obligations schéma

[PDF] les indus définition