[PDF] [PDF] Excel Data Analysis For Dummies 2nd Edition

View - Download [PDF] Excel Data Analysis For Dummies 2nd Edition

Previous PDF

Next PDF

Stephen L. Nelson is an author and CPA who provides accounting, business advisory, tax planning, and tax preparation services to small businesses. He is the author of more than 100 books, including

QuickBooks For Dummies

and

Quicken For Dummies.

Cover Image:

©iStockphoto.com/Henrik5000

Visit the companion website at

www.dummies.com/ extras/exceldataanalysis to find sample spreadsheets from the examples used throughout the book. Go to

Dummies.co

m for videos, step-by-step examples, how-to articles, or to shop!

Open the book and find:

How to make the most of Excel

to analyze data

Insight into the info you're

already working with

No-sweat descriptions of how

to get things done

Guidance on creating

PivotTables and PivotCharts

Easy explanations of

Excel add-ons

Useful data analysis tips

and facts

A handy glossary of terms

Fancier tools for those who

have mastered the basics $26.99 USA / $31.99 CAN / £17.99 UK

9 781118 89809352699

ISBN:978-1-118-89809-3

Computers/Desktop Applications/Spreadsheets

Want to analyze data?

Let Excel do the heavy lifting!

If you're like most people, you probably don't take full advantage of Excel's data analysis tools. This friendly guide walks you through the features of Excel to help you discover the insights in your rough data. From input, to analysis, to visualization, this book shows you how to use Excel to uncover what's hidden within the numbers.

Nelson

Nelson

2nd Edition

Stephen L. Nelson

Author of

QuickBooks For Dummies®

E. C. Nelson

Na vigate and analyze data

W ork with external databases,

PivotTables, and PivotCharts

U se Excel for statistical and financial functions M ake the most of the latest features of Excel 2013

Start with FREE Cheat Sheets

Cheat Sheets include

$IFDLMJTUT t$IBSUT t$PNNPO*OTUSVDUJPOT t"OE0UIFS(PPE4UVGG

Get Smart at Dummies.com

Dummies.com makes your life easier with 1,000s

of answers on everything from removing wallpaper to using the latest version of Windows.

Check out our

7JEFPT

t*MMVTUSBUFE"SUJDMFT t4UFQCZ4UFQ*OTUSVDUJPOT Plus, each month you can win valuable prizes by entering our Dummies.com sweepstakes. * Want a weekly dose of Dummies? Sign up for Newsletters on %JHJUBM1IPUPHSBQIZ t.JDSPTPGU8JOEPXT0GGJDF t1FSTPOBM'JOBODF*OWFTUJOH t)FBMUI8FMMOFTT t$PNQVUJOH

J1PET$FMM1IPOFT

tF#BZ t*OUFSOFU t'PPE )PNF(BSEFO

Find out "HOW" at Dummies.com

*Sweepstakes not currently available in all countries; visit Dummies.com for official rules.

Get More and Do More at Dummies.com

To access the Cheat Sheet created specifically for this book, go to Excel

Data Analysis

2nd Edition

by Stephen L. Nelson, MBA, CPA and E. C. Nelson 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 sys tem 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, witho ut 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) 74

8-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 Everythin g Easier, and related trade dress are trademarks or registered trademarks of John Wile y & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Micr osoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Son s, 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 COMPLETENE SS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, I

NCLUDING

WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WA

RRANTY

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 RENDERI

NG LEGAL,

ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHE R THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. T

HE 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 PROVI DE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHE

N THIS

WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please conta ct our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fa x 317-572-4002. For technical support, please visit www.wiley.com/techsupport Wiley publishes in a variety of print and electronic formats and by prin t-on-demand. Some material included with standard print versions of this book may not be included i n e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in th e version you purchased, you may download this material at http://booksupport.wiley.com . For more information about Wiley prod ucts, 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

Contents at a Glance

Introduction ................................................................1

Part I

Where's the Beef?

Chapter 1

Introducing Excel Tables ........................................................................ .......9

Chapter 2

Grabbing Data from External Sources

Chapter 3

Scrub-a-Dub-Dub: Cleaning Data

Part II

PivotTables and PivotCharts ............................79

Chapter 4

Working with PivotTables ........................................................................ ...81

Chapter 5

Building PivotTable Formulas

Chapter 6

Working with PivotCharts

.127

Chapter 7

Customizing PivotCharts

...141

Part III

Advanced Tools ...........................................155

Chapter 8

Using the Database Functions ...................................................................157

Chapter 9

Using the Statistics Functions

Chapter 10

Descriptive Statistics........................................................................ ........225

Chapter 11

Inferential Statistics

..........245

Chapter 12

Optimization Modeling with Solver

Part IV

The Part of Tens ..........................................287

Chapter 13

Ten Things You Ought to Know about Statistics .................................289

Chapter 14

Almost Ten Tips for Presenting Table

Results and Analyzing Data

................301

Chapter 15

Ten Tips for Visually Analyzing and Presenting Data ..........................307

Appendix

Glossary of Data Analysis

andExcel Terms Index

Table of Contents

Introduction .................................................................1 About This Book ........................................................................ ......................1

What You Can Safely Ignore

...1 What You Shouldn"t Ignore (Unless You"re a Masochist) ..........................2

Foolish Assumptions

...............3

How This Book Is Organized

..3

Part I: Where"s theBeef?

Part II: PivotTables and PivotCharts

Part III: Advanced Tools

3

Part IV: The Part ofTens

Icons Used inThis Book

.........4

Beyond theBook

.....................5

Where toGo fromHere

...........5

Part I

Where's the Beef? ..............................................7

Chapter 1

Introducing Excel Tables .............................9 What Is a Table and Why Do I Care? .............................................................9

Building Tables

......................12

Exporting froma database

Building a table thehard way.............................................................12

Building a table thesemi-hard way

Analyzing Table Information

16

Simple statistics

...........16

Sorting table records

...18

Using AutoFilter ona table

Undoing a filter

.............23

Turning offfilter

...........23

Using thecustom AutoFilter

Filtering a filtered table

Using advanced filtering

Chapter 2

Grabbing Data from External Sources .................31 Getting Data theExport-Import Way ...........................................................31

Exporting: The first step

Importing: The second step (if necessary)

Querying External Databases and Web Page Tables

................................44

Running a web query

...45

Importing a database table

Querying anexternal database

It"s Sometimes a Raw Deal

....55

Excel Data Analysis For Dummies, 2nd Edition

vi

Chapter 3

Scrub-a-Dub-Dub: Cleaning Data .....................57 Editing Your Imported Workbook ...............................................................57

Delete unnecessary columns

Delete unnecessary rows

Resize columns........................................................................ .............58

Resize rows

...................60

Erase unneeded cell contents

Format numeric values

Copying worksheet data

Moving worksheet data

Replacing data infields

Cleaning Data withText Functions

What"s thebig deal, Steve?

The answer tosome ofyour problems

The CLEAN function

....65

The CONCATENATE function

The EXACT function

....66

The FIND function

........67

The FIXED function

......67 The LEFT function........................................................................ ........68

The LEN function

.........68

The LOWER function

...68

The MID function

.........69

The PROPER function

..69

The REPLACE function

70

The REPT function

.......70

The RIGHT function

.....70

The SEARCH function

..71

The SUBSTITUTE function

The T function

..............72

The TEXT function

.......72

The TRIM function

.......73

The UPPER function

....73

The VALUE function

quotesdbs_dbs19.pdfusesText_25

[PDF] excel exercise 1 student gpa calculator

[PDF] excel final exam answers

[PDF] excel for dummies 2013 pdf

[PDF] excel formula download

[PDF] excel formulas cheat sheet pdf

[PDF] excel formulas for data analysis

[PDF] excel formulas in hindi pdf

[PDF] excel formulas pdf free download

[PDF] excel formulas with examples xls

[PDF] excel intermediate tutorial pdf

[PDF] excel interview questions for data analyst

[PDF] excel is fun power query

[PDF] excel macro for pdf

[PDF] excel macro interview questions

[PDF] excel macro tutorial pdf download