Microsoft Office 2016 In Practice Chapter 4 2202021

Student Learning Outcomes Creating and Formatting an Excel Table Microsoft Office 2016: In Practice

Microsoft Office 2016: In Practice Case Study Paradise Lakes Resort (PLR) § Vacation company

Microsoft Office 2016: In Creating and Formatting an Excel Table SLO 4. 1 ©

Table Terminology Microsoft Office 2016: In Practice Table • Header Row • Record •

Microsoft Office 2016: In Practice Table Guidelines § Type descriptive labels in the first

Tables Microsoft Office 2016: In Practice § Create a table § Apply table styles

Microsoft Office 2016: In Applying Conditional Formatting SLO 4. 2 © 2017 by Mc.

Microsoft Office 2016: In Practice Conditional Formatting § Applies specified formats to cells only

Microsoft Office 2016: In Practice Conditional Formatting Rules Applies formats according to specified criteria

Microsoft Office 2016: In Practice Rules Manage Rules Clear Rules © 2017 by Mc.

Microsoft Office 2016: In Sorting Data SLO 4. 3 © 2017 by Mc. Graw-Hill

Sort Options Microsoft Office 2016: In Practice One Column Multiple Columns Cell Attribute Excel

Microsoft Office 2016: In Filtering Data SLO 4. 4 © 2017 by Mc. Graw-Hill

Microsoft Office 2016: In Practice Types of Filters Auto. Filte r Custom Filter Advance

Microsoft Office 2016: In Using Subtotals, Groups, and Outlines SLO 4. 5 © 2017

Microsoft Office 2016: In Practice Subtotal Command with Sum § Inserts summary rows for

Microsoft Office 2016: In Practice Outlines Use Outline Buttons Create an Auto Outline Define

Microsoft Office 2016: In Importing Data SLO 4. 6 © 2017 by Mc. Graw-Hill

Microsoft Office 2016: In Practice Get External Data § Process of getting data from

Data Location Options Microsoft Office 2016: In Practice Text Files Word Documents Access Database

Import Features Microsoft Office 2016: In Practice § Workbook Connections § Connects data that

Microsoft Office 2016: In Exporting Data SLO 4. 7 © 2017 by Mc. Graw-Hill

Export Options Microsoft Office 2016: In Practice Text file Clipboard Share. Point List ©

Microsoft Office 2016: In Building and Formatting Pivot. Tables SLO 4. 8 © 2017

Microsoft Office 2016: In Practice Pivot. Tables Cross tabulation report based on list-type data

Pivot. Table Options Microsoft Office 2016: In Practice Create a Pivot. Table Create a

Chapter Summary SLO Summary Microsoft Office 2016: In Practice 4. 1 4. 2 4.

Slides: 28

Download presentation

Microsoft Office 2016: In Practice Chapter 4 2/20/2021 Formatting, Organizing, and Getting Data © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 1

Student Learning Outcomes Creating and Formatting an Excel Table Microsoft Office 2016: In Practice Applying Conditional Formatting Sorting Data Filtering Data Using Subtotals, Groups, and Outlines Importing Data Exporting Data Building and Formatting Pivot. Tables © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Case Study Paradise Lakes Resort (PLR) § Vacation company § Four resort chains § In northern Minnesota Import and sort data from external sources! Format lists as tables and Pivot. Tables and subtotal data! © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Creating and Formatting an Excel Table SLO 4. 1 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 4

Table Terminology Microsoft Office 2016: In Practice Table • Header Row • Record • Field Name © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Table Guidelines § Type descriptive labels in the first row and begin each label with a letter, not a number. § Assign each header a unique label; do not repeat any of the descriptive labels. § Keep the same type of data within each column. § Do not leave blank rows within the data. § Keep the table separate from other data on the worksheet. © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Tables Microsoft Office 2016: In Practice § Create a table § Apply table styles § Table style options § Use the Table Tools group § View structured references and table name © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Applying Conditional Formatting SLO 4. 2 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 8

Microsoft Office 2016: In Practice Conditional Formatting § Applies specified formats to cells only when the cells meet specified criteria § Use rules to highlight, select fill colors, change font styles, add color scales, or even data bars or icons © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Conditional Formatting Rules Applies formats according to specified criteria (rules) § Highlight § Top/Bottom § Formula § Data Bars § Color Scales § Icon Sets © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Rules Manage Rules Clear Rules © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Sorting Data SLO 4. 3 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 12

Sort Options Microsoft Office 2016: In Practice One Column Multiple Columns Cell Attribute Excel Table © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Filtering Data SLO 4. 4 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 14

Microsoft Office 2016: In Practice Types of Filters Auto. Filte r Custom Filter Advance d Filter © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Using Subtotals, Groups, and Outlines SLO 4. 5 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 16

Microsoft Office 2016: In Practice Subtotal Command with Sum § Inserts summary rows for a sorted list § Available for a normal range of cells, not in an Excel table § Lists data as an outline FIRST: Sort the rows by the main field to be totaled. © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Outlines Use Outline Buttons Create an Auto Outline Define Groups © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Importing Data SLO 4. 6 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 19

Microsoft Office 2016: In Practice Get External Data § Process of getting data from another software program, file format, or Internet location § External data § Source © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Data Location Options Microsoft Office 2016: In Practice Text Files Word Documents Access Database Files Web Site Data © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Import Features Microsoft Office 2016: In Practice § Workbook Connections § Connects data that originated outside the workbook § Flash Fill § Recognizes a pattern in the first cell and suggests data for the remaining rows in a column © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Exporting Data SLO 4. 7 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 23

Export Options Microsoft Office 2016: In Practice Text file Clipboard Share. Point List © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Building and Formatting Pivot. Tables SLO 4. 8 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 25

Microsoft Office 2016: In Practice Pivot. Tables Cross tabulation report based on list-type data § Used for analysis of data § To pivot is to rearrange © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Pivot. Table Options Microsoft Office 2016: In Practice Create a Pivot. Table Create a Pivot. Chart Refresh a Pivot. Table Adjust Field Settings Format a Pivot. Table © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Chapter Summary SLO Summary Microsoft Office 2016: In Practice 4. 1 4. 2 4. 3 4. 4 4. 5 4. 6 4. 7 4. 8 Create and format a list as an Excel table Apply Conditional Formatting rules as well as Color Scales, Icon Sets, and Data Bars Sort data by one or more columns or by attribute Filter data by using Auto. Filters and by creating an Advanced Filter Use subtotals, groups, and outlines for tabular data in a worksheet Import data into an Excel worksheet from a text file, a database file, and other sources Export Excel data as a text file and into a Word document Build and format a Pivot. Table © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.