Additional Information
Book Details
Abstract
Microsoft Excel is a powerful tool that can transform the way you use data. This book explains in comprehensive and user-friendly detail how to manage, make sense of, explore and share data, giving scientists at all levels the skills they need to maximize the usefulness of their data.
Readers will learn how to use Excel to:
* Build a dataset – how to handle variables and notes, rearrangements and edits to data.
* Check datasets – dealing with typographic errors, data validation and numerical
errors.
* Make sense of data – including datasets for regression and correlation; summarizing data with averages and variability; and visualizing data with graphs, pivot charts and sparklines.
* Explore regression data – finding, highlighting and visualizing correlations.
* Explore time-related data – using pivot tables, sparklines and line plots.
* Explore association data – creating and visualizing contingency tables.
* Explore differences – pivot tables and data visualizations including box-whisker plots.
* Share data – methods for exporting and sharing your datasets, summaries and
graphs.
Alongside the text, Have a Go exercises, Tips and Notes give readers practical experience and highlight important points, and helpful self-assessment exercises and summary tables can be found at the end of each chapter. Supplementary material can also be downloaded on the companion website.
Managing Data Using Excel is an essential book for all scientists and students who use data and are seeking to manage data more effectively. It is aimed at scientists at all levels but it is especially useful for university-level research, from undergraduates to postdoctoral researchers.
Mark Gardener (www.gardenersown.co.uk) is an ecologist, lecturer, and writer working in the UK. His primary area of research was in pollination ecology and he has worked in the UK and around the word (principally Australia and the United States). Since his doctorate he has worked in many areas of ecology, often as a teacher and supervisor. He believes that ecological data, especially community data, is the most complicated and ill-behaved and is consequently the most fun to work with. He was introduced to R by a like-minded pedant whilst working in Australia during his doctorate. Learning R was not only fun but opened up a new avenue, making the study of community ecology a whole lot easier. He is currently self-employed and runs courses in ecology, data analysis, and R for a variety of organizations. Mark lives in rural Devon with his wife Christine, a biochemist who consequently has little need of statistics.
Table of Contents
| Section Title | Page | Action | Price |
|---|---|---|---|
| CONTENTS | vi | ||
| ABOUT THE AUTHOR | viii | ||
| ACKNOWLEDGEMENTS | ix | ||
| INTRODUCTION | x | ||
| WHO THIS BOOK IS FOR | x | ||
| WHAT YOU WILL LEARN IN THIS BOOK | xi | ||
| HOW THIS BOOK IS ARRANGED | xi | ||
| HOW TO READ THIS BOOK | xii | ||
| SOFTWARE USED | xii | ||
| SUPPORT FILES | xiii | ||
| PART 1 Arranging and managing your data | 1 | ||
| 1 ARRANGING YOUR DATA | 3 | ||
| 1.1 SYSTEMS FOR DATA LAYOUT | 4 | ||
| 1.1.1 Common ways to lay out data | 5 | ||
| 1.1.2 A standard layout for data | 10 | ||
| 1.2 RECORDING FORMAT | 12 | ||
| 1.3 TURNING YOUR DATA INTO A DATABASE | 14 | ||
| 1.4 PLANNING YOUR DATA LAYOUT | 19 | ||
| 1.4.1 Index variables | 19 | ||
| 1.4.2 Using dates | 20 | ||
| 1.5 USING RECORDING SHEETS AND NOTEBOOKS | 20 | ||
| 1.5.1 Data from equipment | 20 | ||
| 1.5.2 Data in notebooks | 21 | ||
| 1.5.3 Data in recording sheets | 22 | ||
| 1.6 SAVING YOUR DATA | 23 | ||
| 1.7 EXERCISES | 25 | ||
| 1.8 SUMMARY | 25 | ||
| 2 MANAGING YOUR DATA: BUILDING YOUR DATASET | 27 | ||
| 2.1 ADDING TO YOUR DATASET | 27 | ||
| 2.1.1 Adding new variables | 28 | ||
| 2.1.2 Adding notes to your data | 58 | ||
| 2.2 EDITING YOUR DATA | 62 | ||
| 2.2.1 Find and replace | 62 | ||
| 2.2.2 Sorting data | 65 | ||
| 2.2.3 Filtering data | 69 | ||
| 2.3 REARRANGING YOUR DATA | 80 | ||
| 2.3.1 Rearranging data by row | 81 | ||
| 2.3.2 Rearranging data by column | 81 | ||
| 2.4 EXERCISES | 85 | ||
| 2.5 SUMMARY | 86 | ||
| 3 MANAGING YOUR DATA: CHECKING YOUR DATASET | 88 | ||
| 3.1 TYPOGRAPHICAL ERRORS | 88 | ||
| 3.1.1 Sort tools in error checking | 89 | ||
| 3.1.2 Pivot tables in error checking | 89 | ||
| 3.1.3 Filter tools in error checking | 92 | ||
| 3.2 VALIDATING ENTRIES | 92 | ||
| 3.2.1 Validation during entry | 93 | ||
| 3.2.2 Validation after data entry | 99 | ||
| 3.3 NUMERICAL ERRORS | 101 | ||
| 3.3.1 Errors in sample data | 102 | ||
| 3.3.2 Errors in correlation data | 119 | ||
| 3.4 EXERCISES | 133 | ||
| 3.5 SUMMARY | 134 | ||
| PART 2 Using your dataset – summarizing, visualizing andsharing your data | 137 | ||
| 4 MAKING SENSE OF YOUR DATA | 139 | ||
| 4.1 TYPES OF DATASET | 139 | ||
| 4.1.1 Correlation and regression datasets | 140 | ||
| 4.1.2 Association datasets | 140 | ||
| 4.1.3 Differences datasets | 140 | ||
| 4.2 WAYS TO SUMMARIZE YOUR DATA | 141 | ||
| 4.2.1 Averages | 141 | ||
| 4.2.2 Variability | 143 | ||
| 4.2.3 Replication | 144 | ||
| 4.2.4 Data distribution | 145 | ||
| 4.2.5 Using the Analysis ToolPak | 149 | ||
| 4.3 WAYS TO VISUALIZE YOUR DATA | 151 | ||
| 4.3.1 Types of graph | 151 | ||
| 4.3.2 Pivot charts | 157 | ||
| 4.3.3 Sparklines | 158 | ||
| 4.4 EXERCISES | 160 | ||
| 4.5 SUMMARY | 160 | ||
| 5 EXPLORING REGRESSION DATA | 163 | ||
| 5.1 FINDING CORRELATIONS IN YOUR DATASET | 163 | ||
| 5.1.1 Correlation matrices using all-numeric data | 164 | ||
| 5.1.2 Correlation matrices using grouping variables | 168 | ||
| 5.1.3 Highlighting correlations | 171 | ||
| 5.2 VISUALIZING THE CORRELATIONS | 176 | ||
| 5.2.1 Sparklines | 177 | ||
| 5.2.2 Scatter plots | 178 | ||
| 5.2.3 Checking assumptions of normality | 184 | ||
| 5.3 EXERCISES | 188 | ||
| 5.4 SUMMARY | 188 | ||
| 6 EXPLORING TIME-RELATED DATA | 190 | ||
| 6.1 SUMMARIZING TIME-RELATED DATA | 190 | ||
| 6.1.1 Using pivot tables | 190 | ||
| 6.2 VISUALIZING TIME-RELATED DATA | 203 | ||
| 6.2.1 Sparklines | 203 | ||
| 6.2.2 Line plots | 210 | ||
| 6.3 EXERCISES | 222 | ||
| 6.4 SUMMARY | 223 | ||
| 7 EXPLORING ASSOCIATION DATA | 224 | ||
| 7.1 CONTINGENCY TABLES FROM YOUR DATA | 225 | ||
| 7.2 VISUALIZING YOUR CONTINGENCY TABLES | 227 | ||
| 7.2.1 Column and bar charts | 228 | ||
| 7.2.2 Pie charts | 238 | ||
| 7.3 EXERCISES | 241 | ||
| 7.4 SUMMARY | 241 | ||
| 8 EXPLORING DIFFERENCES DATA | 243 | ||
| 8.1 SUMMARIZING DIFFERENCES DATA | 243 | ||
| 8.1.1 Using pivot tables | 244 | ||
| 8.1.2 Using the Analysis ToolPak | 252 | ||
| 8.2 VISUALIZING DIFFERENCES DATA | 254 | ||
| 8.2.1 Sparklines | 255 | ||
| 8.2.2 Histograms | 256 | ||
| 8.2.3 Bar charts | 257 | ||
| 8.2.4 Box-and-whisker plots | 267 | ||
| 8.3 EXERCISES | 276 | ||
| 8.4 SUMMARY | 277 | ||
| 9 SHARING YOUR DATA | 279 | ||
| 9.1 EXPORTING YOUR DATA | 280 | ||
| 9.1.1 Sharing your entire dataset | 280 | ||
| 9.1.2 Sharing portions of your dataset | 284 | ||
| 9.2 SHARING YOUR SUMMARIES | 285 | ||
| 9.2.1 Sharing pivot tables | 285 | ||
| 9.2.2 Sharing summary results | 287 | ||
| 9.3 SHARING YOUR GRAPHS | 287 | ||
| 9.3.1 Sharing sparklines | 288 | ||
| 9.3.2 Sharing charts | 291 | ||
| 9.4 EXERCISES | 295 | ||
| 9.5 SUMMARY | 296 | ||
| APPENDIX 1 ANSWERS TO EXERCISES | 299 | ||
| Chapter 1 | 299 | ||
| Chapter 2 | 299 | ||
| Chapter 3 | 300 | ||
| Chapter 4 | 300 | ||
| Chapter 5 | 301 | ||
| Chapter 6 | 302 | ||
| Chapter 7 | 302 | ||
| Chapter 8 | 303 | ||
| Chapter 9 | 304 | ||
| APPENDIX 2 DIFFERENCES BETWEEN VERSIONS OF EXCEL | 305 | ||
| GENERAL DIFFERENCES | 305 | ||
| Pivot tables | 305 | ||
| Charts | 306 | ||
| SUMMARY | 307 | ||
| INDEX | 309 |