Exploring Microsoft Office 2016 Series Editor Mary Anne

Exploring Microsoft® Office 2016 Series Editor Mary Anne Poatsy Mulbery|Krebs|Hogan|Cameron |Davidson|Lau|Lawson|Williams Series Created by Dr. Robert T. Grauer Copyright © 2017 Pearson Education, Inc.

Exploring Excel 2016 Chapter 4 Datasets and Tables Managing Large Volumes of Data Copyright © 2017 Pearson Education, Inc.

Objectives (1 of 2) • Freeze Rows and Columns • Print Large Datasets • Understand the Benefits of Data Tables • Sort Data • Filter Data • Apply Conditional Formatting • Create a New Rule Copyright © 2017 Pearson Education, Inc.

Objective 1: Freeze Rows and Columns (1 of 3) Skills: • Freeze Rows and Columns Copyright © 2017 Pearson Education, Inc.

Freeze Rows and Columns (2 of 3) • Freezing—keeps certain rows and/or columns visible at all times • Freeze options: • Freeze Top • Freeze First Copyright © 2017 Pearson Education, Inc.

Freeze Rows and Columns (3 of 3) Rows 1 -5 are frozen and rows 6 -20 are no longer visible Copyright © 2017 Pearson Education, Inc.

Objective 2: Print Large Datasets (1 of 4) Skills: • Display and Change Page Breaks • Set and Clear a Print Area • Print Titles • Control Print Page Order Copyright © 2017 Pearson Education, Inc.

Print Large Datasets (2 of 4) Automatic page break Manual page break Copyright © 2017 Pearson Education, Inc.

Print Large Datasets (3 of 4) Print area Copyright © 2017 Pearson Education, Inc.

Print Large Datasets (4 of 4) Select rows and columns to repeat Select page order Copyright © 2017 Pearson Education, Inc.

Objective 3: Understand the Benefits of Data Tables (1 of 1) • Data structure—organization method to manage multiple data points • Table advantages: • Column headings remain onscreen • Sorting and filtering is more efficient • Table styles easily format tables • Calculated columns enable you to create and edit formulas • Calculated total row implements summary functions • Structured references used in formulas • Table data can be exported Copyright © 2017 Pearson Education, Inc.

Objective 4: Sort Data (1 of 4) Skills: • Sort One Field • Sort Multiple Fields • Create a Custom Sort Copyright © 2017 Pearson Education, Inc.

Sort Data (2 of 4) Copyright © 2017 Pearson Education, Inc.

Sort Data (3 of 4) Copyright © 2017 Pearson Education, Inc.

Sort Data (4 of 4) Type new entries Copyright © 2017 Pearson Education, Inc.

Objective 5: Filter Data (1 of 6) Skills: • Apply Text Filters • Apply Number Filters • Apply Date Filters • Apply a Custom Filter Copyright © 2017 Pearson Education, Inc.

Filter Data (2 of 6) • Filtering—only displays records that meet specified conditions • Filter types: • Text • Number • Date • Custom Copyright © 2017 Pearson Education, Inc.

Filter Data (3 of 6) Filter arrow Deselect Select All Select to display records Copyright © 2017 Pearson Education, Inc.

Filter Data (4 of 6) Number Filters Menu of range options Copyright © 2017 Pearson Education, Inc.

Filter Data (5 of 6) Date Filters Menu of date ranges Copyright © 2017 Pearson Education, Inc.

Filter Data (6 of 6) Set to Between Add additional conditions Copyright © 2017 Pearson Education, Inc.

Objective 6: Applying Conditional Formatting (1 of 4) Skills: • Apply Conditional Formatting with the Quick Analysis Tool • Apply Highlight Cells Rules • Specify Top/Bottom Rules • Display Data Bars, Color Scales, and Icon Sets Copyright © 2017 Pearson Education, Inc.

Applying Conditional Formatting (2 of 4) Red highlighting with red text Text-based formatting Quick Analysis Copyright © 2017 Pearson Education, Inc.

Applying Conditional Formatting (3 of 4) Conditional Formatting Select formatting option Select condition Dark red border Copyright © 2017 Pearson Education, Inc.

Applying Conditional Formatting (4 of 4) Data bars Color scales Icon set gallery Icon sets Copyright © 2017 Pearson Education, Inc.

Objective 7: Create a New Rule (1 of 4) Skills: • Use Formulas in Conditional Formatting • Manage Rules Copyright © 2017 Pearson Education, Inc.

Create a New Rule (2 of 4) Format style Minimum and Maximum values Rule type Fill colors Copyright © 2017 Pearson Education, Inc.

Create a New Rule (3 of 4) Select Use a formula to determine which cells to format Formula using cell references Copyright © 2017 Pearson Education, Inc.

Create a New Rule (4 of 4) Click Edit Rule or Delete Rule Select rule Copyright © 2017 Pearson Education, Inc.

Summary Skills: • In data stored as a range, you can freeze rows and columns to always display heading information • Converting a range to a table has several advantages: • Sorting and filtering • Adding aggregate statistical functions • Applying conditional formatting 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.