Excel 2019 Data Analysis

Excel 2019 Data Analysis

The Computer Workshop, Inc.

800-639-3535

training@

Lesson Notes

Excel 2019

Data Analysis

Course Number: 0200-405-19-W Course Release Number: 1

Software Release Number: 2019

5/6/2020

Developed by: Brian Ireson

Suzanne Hixon Thelma Tippie

Edited by: Jeffery DeRamus

Cheri Howard

Published by: RoundTown Publishing 5131 Post Road, Suite 102

Dublin, Ohio 43017

for

The Computer Workshop, Inc. 5200 Upper Metro Place, Suite 140

Dublin, Ohio 43017 (614) 798-9505

Copyright ? 2020 by RoundTown Publishing. No reproduction or transmittal of any part of this publication, in any form or by any means, mechanical or electronic, including photocopying, recording, storage in an information retrieval system, or otherwise, is permitted without the prior consent of RoundTown Publishing.

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 warranties of merchantability or fitness for specific uses. Changes may be made to this document without notice.

Trademark Notices: The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of The Computer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], and [Access] are registered trademarks of Microsoft Corporation. [Photoshop] and [InDesign] are a registered trademark of Adobe. All other product names and services identified throughout this book are trademarks or registered trademarks of their respective companies. All NASA information was obtained from public resources. Using any of these trade names is for editorial purposes only and in no way is intended to convey endorsement or other affiliation with this manual.

Preface

Table of Contents

Page iv

Lesson 1: Tables

Tables.........................................................................................3 Creating a Table........................................................................4

Using Home Tab.................................................................4 Using the Insert Tab...........................................................5 Using the Quick Analysis Tool........................................6 Table Components.................................................................10 Records and Fields.................................................................14 Resizing A Table.....................................................................15 Using the Resize Handle.................................................15 Resizing a Table Using the Design Ribbon...................15 Adding Fields.........................................................................17 Using the Right Click Insert Menu................................17 By Selecting an Adjacent Column.................................18 Using the Ribbon..............................................................18 Adding Records......................................................................19 Adding Records Inside the Table...................................19 Adding Records at the End of the Table.......................19 Deleting Records or Fields....................................................20 The Total Row.........................................................................25 Adding a Total Row........................................................25 Hiding the Total Row......................................................26 Using the QAT to Create a Total Row...........................26 Data Forms..............................................................................28 Adding the Form Tool to the QAT................................28 Using a Form to Enter Records......................................29 Slicers.......................................................................................32 Adding Slicers to a Table................................................32 Formatting the Slicer........................................................33 Using the Slicer.................................................................33 Clearing a Slicer Filter.....................................................34 Closing or Deleting a Slicer............................................34

Lesson 2: Importing Data

Importing Data from other sources.....................................39 Using Excel and Access.........................................................40 Creating a Table from an Access Object..............................41

Using the Data Ribbon....................................................41 Setting the Refresh Properties........................................42 Importing Data Using the Microsoft Query Connection.46 Setting up a Querry Connection....................................46 Importing Data from a Text File..........................................55 Opening A Text File in Excel..........................................55 Importing a Text file into a Workbook..........................58

Excel 2019: Data Analysis, Rel. 1, 5/6/2020

Table of Contents,

continued

Preface

Lesson 3: Data Management

Understanding Structured Data....................................65 Guidelines for Data Structure........................................65 Cleaning Up Raw Data....................................................65 Removing Blank Rows..........................................................66 Removing Blank Rows....................................................66 Removing Duplicates............................................................69 Remove Duplicates..........................................................69 Conditional Formatting...................................................72 Using Conditional Formatting To Find Duplicates....72 Comparing Two Lists With Conditional Formatting..73 Sorting Data............................................................................77 Applying a Simple Sort...................................................77 Basic Sorting in a Table....................................................78 Sorting on Multiple Fields..............................................81 Flash Fill..................................................................................85 Flash Fill to Combine or Separate Data........................85 AutoFilters...............................................................................89 Basic Filtering...................................................................89 Custom AutoFilters................................................................92 Creating a Custom AutoFilter........................................92 Using the Search Feature................................................94 Using Wildcards...............................................................94 Clearing Filters.................................................................95 Advanced Filter......................................................................97 Using an Advanced Filter...............................................97 Clearing the Filter............................................................98 Copying Filtered Records.....................................................99 Copying Filtered Records to a New Location .............99

Lesson 4: Database Functions

Database Functions..............................................................105 Basic Syntax of D-Functions...............................................107 Creating a D-Function Formula.........................................108

Entering the Function Manually..................................109 Expanding D-Functions .....................................................114 Adding Drop-down Menu's...............................................116

Data Validation Lists.....................................................116

Excel 2019: Data Analysis, Rel. 1, 5/6/2020

Page v

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download