Stephen L. Nelson - Excel Data Analysis For Dummies

Excel Data Analysis For Dummies, 2nd Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright 2014 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

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, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions .

Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit www.wiley.com/techsupport .

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com . For more information about Wiley products, visit www.wiley.com .

Library of Congress Control Number: 2013957980

ISBN 978-1-118-89809-3 (pbk); ISBN 978-1-118-89808-6 (ebk); ISBN 978-1-118-89810-9 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

Chapter 1

In This Chapter

Figuring out tables

Building tables

Analyzing tables with simple statistics

Sorting tables

Discovering the difference between using AutoFilter and filtering

First things first. I need to start my discussion of using Excel for data analysis by introducing Excel tables, or what Excel used to call lists. Why? Because, except in the simplest of situations, when you want to analyze data with Excel, you want that data stored in a table. In this chapter, I discuss what defines an Excel table; how to build, analyze, and sort a table; and why using filters to create a subtable is useful.

A table is, well, a list. This definition sounds simplistic, I guess. But take a look at the simple table shown in Figure . This table shows the items that you might shop for at a grocery store on the way home from work.

As I mention in the Introduction of this book, many of the Excel workbooks that you see in the figures of this book are available for download from this book's companion website. For more on how to access the companion website, see the Introduction.

Commonly, tables include more information than Figure . In column A, for example, the table names the store where you might purchase the item. In column C, this expanded table gives the quantity of some item that you need. In column D, this table provides a rough estimate of the price.

Figure 1-1: A table: Start out with the basics.

Figure 1-2: A grocery list for the more serious shopper like me.

An Excel table usually looks more like the list shown in Figure .

Let me make a handful of observations about the table shown in Figure . First, each column shows a particular sort of information. In the parlance of database design, each column represents a field. Each field stores the same sort of information. Column A, for example, shows the store where some item can be purchased. (You might also say that this is the Store field.) Each piece of information shown in column A the Store field names a store: Sams Grocery, Hughes Dairy, and Butchermans.

The first row in the Excel worksheet provides field names. For example, in Figure , row 1 names the four fields that make up the list: Store, Item, Quantity, and Price. You always use the first row, called the header row, of an Excel list to name, or identify, the fields in the list.

Starting in row 2, each row represents a record, or item, in the table. A record is a collection of related fields. For example, the record in row 2 in Figure shows that at Sams Grocery, you plan to buy two loaves of bread for a price of $1 each. (Bear with me if these sample prices are wildly off; I usually don't do the shopping in my household.)

Row 3 shows or describes another item, coffee, also at Sams Grocery, for $8. In the same way, the other rows of the super-sized grocery list show items that you will buy. For each item, the table identifies the store, the item, the quantity, and the price.

---

Something to understand about Excel tables

An Excel table is a flat-file database . That flat-file-ish-ness means that theres only one table in the database. And the flat-file-ish-ness also means that each record stores every bit of information about an item.