Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 12 Templates, Styles, and Macros Standardizing Workbooks Copyright © 2017 Pearson Education, Inc.

Objectives • Select a Template • Apply Themes and Backgrounds • Apply Cell Styles • Create and Use a Template • Protect a Cell, a Worksheet, and a Workbook Copyright © 2017 Pearson Education, Inc.

Objectives • Create a Macro • Create Macro Buttons • Set Macro Security • Create a Sub Procedure • Create a Custom Function Copyright © 2017 Pearson Education, Inc.

Objective 1: Select a Template • Skills: Select a Template Copyright © 2017 Pearson Education, Inc.

Select a Template Search for online templates Templates on computer Copyright © 2017 Pearson Education, Inc.

Objective 2: Apply Themes and Backgrounds Skills: Customize a Theme Apply a Background Copyright © 2017 Pearson Education, Inc.

Apply Themes and Backgrounds Click Themes Page Layout tab Themes gallery Copyright © 2017 Pearson Education, Inc.

Apply Themes and Backgrounds Colors Fonts Effects Copyright © 2017 Pearson Education, Inc.

Apply Themes and Backgrounds Copyright © 2017 Pearson Education, Inc.

Objective 3: Apply Cell Styles Skills: Create Custom Cell Styles Modify and Remove Cell Styles Copyright © 2017 Pearson Education, Inc.

Apply Cell Styles • Cell style—collection of format settings based on the currently selected theme • Cell styles control: § Font attributes § Borders and fill styles and colors § Vertical and horizontal cell alignment § Number formatting § Cell-protection settings Copyright © 2017 Pearson Education, Inc.

Apply Cell Styles Cell categories Copyright © 2017 Pearson Education, Inc.

Apply Cell Styles Name style Select options Copyright © 2017 Pearson Education, Inc.

Objective 4: Create and Use a Template Skills: Create and Use a Template Copyright © 2017 Pearson Education, Inc.

Create and Use a Template • Template creation guidelines: • Use formatted, descriptive labels, empty cells, and formulas • Avoid values when possible in formulas • Use appropriate error trapping functions • Include data-validation settings • Include template instructions • Turn off worksheet gridlines • Apply appropriate template formatting • Give worksheets meaningful names Copyright © 2017 Pearson Education, Inc.

Create and Use a Template Select Template Copyright © 2017 Pearson Education, Inc.

Objective 5: Protect a Cell, a Worksheet, and a Workbook Skills: Lock and Unlock Cells Protect a Worksheet Protect a Workbook Copyright © 2017 Pearson Education, Inc.

Protect a Cell, a Worksheet, and a Workbook Click Format Selected cell Lock cell Copyright © 2017 Pearson Education, Inc.

Protect a Cell, a Worksheet, and a Workbook Enter password Confirm password Select actions Copyright © 2017 Pearson Education, Inc.

Protect a Cell, a Worksheet, and a Workbook Click Protect Workbook Select action Enter password Copyright © 2017 Pearson Education, Inc.

Objective 6: Create a Macro Skills: Use the Macro Recorder Record a Macro Use Relative References Run a Macro Copyright © 2017 Pearson Education, Inc.

Create a Macro Developer tab Copyright © 2017 Pearson Education, Inc.

Create a Macro Name the macro Select shortcut key Specify storage location Copyright © 2017 Pearson Education, Inc.

Create a Macro Select macro Run the macro Copyright © 2017 Pearson Education, Inc.

Objective 7: Create Macro Buttons Skills: Create Macro Buttons Copyright © 2017 Pearson Education, Inc.

Create Macro Buttons Button Name macro Copyright © 2017 Pearson Education, Inc.

Create Macro Buttons Clear Form button Copyright © 2017 Pearson Education, Inc.

Objective 8: Set Macro Security Skills: Set Macro Security Copyright © 2017 Pearson Education, Inc.

Set Macro Security warning Enable Content Macro button Copyright © 2017 Pearson Education, Inc.

Set Macro Security Select a security setting Copyright © 2017 Pearson Education, Inc.

Objective 9: Create a Sub Procedure Skills: Create a Sub Procedure Use VBA with a Protected Worksheet Edit a Macro in the Visual Basic Editor Copyright © 2017 Pearson Education, Inc.

Create a Sub Procedure Project Explorer pane Comments Visual Basic Editor Code Copyright © 2017 Pearson Education, Inc.

Create a Sub Procedure Copyright © 2017 Pearson Education, Inc.

Create a Sub Procedure Copyright © 2017 Pearson Education, Inc.

Create a Sub Procedure Copyright © 2017 Pearson Education, Inc.

Objective 10: Create a Custom Function Skills: Create a Custom Function Copyright © 2017 Pearson Education, Inc.

Create a Custom Function Copyright © 2017 Pearson Education, Inc.

Summary • Achieving a unified look: §Selecting a template §Customizing a theme • Protect a spreadsheet: §Cell level §Worksheet level §Workbook level • Create, edit, and run macros: §Macro Recorder §Visual Basic Editor §Sub procedure §Function Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.