Managing and Customizing workbooks

Question 1

New Template

Answer 1

File → New → Template

Question 2

Save a Personalized Template

Answer 2

File → Save → Save as “Excel Template”

Question 3

How to open a personalized template?

Answer 3

To open: File → New → “Personal”

Question 4

How to get the developer tab?

Answer 4

Right click ribbon → “customize the ribbon” → turn on

Question 5

How to run Macros?

Answer 5

Alt + F8

- Must save as macro-enabled workbook

Question 6

How to save as macro-enabled workbook?

Answer 6

File → options → trust center → trust center settings → macro settings

Question 7

To copy macro

Answer 7

1. Open workbook with macro
2. Open new workbook (Ctrl + N) save as macro enabled workbook
3. Go to developer tab
4. Click Visual Basic
5. Open Modules Folder in VBAProject
6. Drag Module to the new workbook

Question 8

Macros Form Controls

Answer 8

Insert –> Check box (reminder to select design mode)

Linked Cell (true/false does not go in quotes)

Right click - Format controls

Question 9

Ref # in another workbook

Answer 9

in book 1 –> Click on another workbook +# –> enter

Question 10

How to break/edit link?

Answer 10

1. Data tab on ribbon
2. Edit Links
3. Source (identifies workbooks referenced)

• If file name is changed or moved, then the link would be broken; link needs to be reestablished

Question 11

Hiding and Displaying Ribbon tabs

Answer 11

Right click any ribbon, customize ribbon

Question 12

How to create formulas between workbooks?

Answer 12

Create a formula by selecting references in another open workbook

Question 13

Referencing data from another workbook

Answer 13

1. Open new workbook
2. Use sign “=” to reference cells or columns
3. Open workbook with data
4. Select column/row or cell

Question 14

Why is it important to manage and share workbooks?

Answer 14

This has to do with the idea that you will be collaborating with others and that several people may be working on the same file.

Question 15

Restricting editing

also use workbook password

Answer 15

Unlocking cells that people can edit and then turning on worksheet protection.

Format –> cells
Ctrl + 1 = format cells –> protection –> Locked

Workbook password: File –> info –> protect workbook

Question 16

Allow Users to Edit Ranges

Answer 16

Allows you to identify multiple ranges to unlock using multiple passwords if you’d like. Worksheet protection can be turned on within this dialog box. Explore it.

Question 17

Structured table reference

Answer 17

Uses naming of the table - selecting whole column (ex.)

SUM( select column/cell)

Question 18

Flash fill

Answer 18

Ctrl + E

*not dynamic which means if there are changes it will not account for it

Question 19

Customized fill series

Answer 19

With time: Space between # and am/pm

Put first #, select “Fill” on home ribbon –> column

Custom list:

1. File
2. Options
3. Advanced
4. General
5. Edit custom list

Question 20

Hidden cell value

Answer 20

Ctrl + 1 –> Protection –> Hidden

Question 21

Access to limited people

Answer 21

1. Under review tab
2. Allow users to edit ranges
3. New Range
4. Select range
5. Protect sheet

Control + 1 = unlock

Question 22

Recover unsaved workbooks

Answer 22

File –> info –> manage workbook –> recover workbook

Question 23

Control how frequent workbooks are recovered

Answer 23

1. Options
2. Save
3. Save AutoRecover information every __ minutes
4. Location

Question 24

Make calculations automatically

Answer 24

1. Formulas
2. Calculations Options
3. Automatic/Manual

Question 25

Tools button for saving

Answer 25

When performing a Save As, you can use the Tools button to password protect the file