Exploring Data
with Excel 2019
Larry Rockoff
Exploring Data with Excel 2019
Deep Dive Press
Copyright 2020 by Larry Rockoff
All rights reserved. No part of this work may be reproduced, stored in a retrieval system or transmitted by any means without written permission from the publisher.
ISBN: 978-0-578-78956-9
Microsoft, Excel, and Access are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners.
Editor: Nicole Roth
Contents at a Glance
Introduction
An Overiew of Excel
The Excel User Interface
Getting External Data
Tables
Pivot Table Basics
Pivot Table Calculations
Charts and Pivot Charts
Data Commands
Formulas
Text Functions
Numeric and Data Functions
Aggregate and Statistical Functions
Analysis Tools
Index
Table of Contents
Introduction
Topics and Features
Plan of the Book
An Overview of Excel
Excel Components
Cells
Tables
Pivot Tables
Charts and Pivot Charts
External Data
Add-Ins
Data Analysis
Looking Ahead
The Excel User Interface
Worksheets and Cells
The Ribbon
Home Commands
View Commands
Page Layout Commands
Review Commands
The Backstage View
Looking Ahead
Getting External Data
Data from Text Files
Data from Microsoft Access
Data from SQL Server
Power Query
Looking Ahead
Tables
Table Basics
Table Tools
Sorting
Filters
Looking Ahead
Pivot Table Basics
The Field List
Field Arrangement and Layout
Expanding and Collapsing Fields
Showing Details
Filters
Slicers
Sorting
Looking Ahead
Pivot Table Calculations
Subtotals and Totals
Grouping
Calculated Items and Fields
Percentages and Ranks
Summarization
Looking Ahead
Charts and Pivot Charts
Pivot Chart Basics
Layout Options
Column and Bar Charts
Pie Charts
Line and Radar Charts
Standard Charts
Sparklines
Looking Ahead
Data Commands
Sorting and Filtering
Subtotals and Grouping
Text to Columns and Flash Fill
Data Validation
Looking Ahead
Formulas
Formula Basics
Relative and Absolute Cell References
The Name Manager
Formula Auditing
Functions
Nested Functions
Looking Ahead
Text Functions
Case Conversion
Joining Text
Text Substrings
Finding and Replacing Text
Splitting Text
Converting to and from Text
Looking Ahead
Numeric and Date Functions
Arithmetic
Rounding
Unit Conversion
Financial Analysis
Dates and Time
Looking Ahead
Aggregate and Statistical Functions
Sums
Counts
Averages
Percentiles and Ranks
Frequencies and Variability
Correlation and Regression
Array Functions and the Sum of Products
Looking Ahead
Logical and Lookup Functions
Logical Functions
IS Functions
Lookup Functions
Looking Ahead
Analysis Tools
Data Analysis
Data Tables
Scenario Manager
Goal Seek
Solver
Final Thoughts
Index
About the Author
Larry Rockoff has been involved with reporting and business intelligence development for many years. His main area of interest is in using reporting tools to explore and analyze data in complex databases. He holds an MBA from the University of Chicago, with a specialization in Management Science.
Besides writing about Excel, he has also published books on Access and SQL. A second edition of his bestselling book, The Language of is available worldwide and has been translated into three languages.
He also maintains a website that features book reviews on technology topics, focusing on analytics and business intelligence as well as broader social issues at:
larryrockoff.com
Please feel free to visit that site to contact the author with any comments or questions.
For more information on this and other publications of Deep Dive Press, please visit:
deepdivepress.com
You are also encouraged to connect with or follow the author on LinkedIn at:
linkedin.com/in/larryrockoff
Introduction
Used by millions around the world, Microsoft Excel has become synonymous with the word spreadsheet and both defines and dominates that software category. One would be hardpressed to find any business or data analyst who doesnt use or interact with Excel in some manner. One reason for Excels popularity is that it is a very easy tool with which to get started. Its intuitive user interface simply requires the user to enter some data in some cells and perhaps add a few formulas. The results are immediate and understandable.
The purpose of this book is to help the beginning Excel user move beyond the basics and become more comfortable with some of Excels more complex features. The focus is on using Excel as a data analysis tool. The full realm of Excel features is considered. Besides the commonly used tactic of entering numbers and formulas in cells, special emphasis is given to the use of Excel tables, charts, pivot tables, and pivot charts. This book also emphasizes procedures that allow you to interact with external data, whether that is with text files or an entire corporate database.
Topics and Features
Excel 2019 is available in both Office 2019 and Microsoft versions. With Microsofts move to subscription services, we anticipate that most users will be using Excel as part of their Microsoft subscription. As such, all screenshots in this book are taken from the Microsoft version of Excel. There may be some subtle differences in the user interface for those using Office 2019.
The topics in this book are many and varied, but in essence, well focus on one main objective:
How to use Excel to explore and analyze data
Our definition of data analysis includes the ability to summarize and manipulate data, but excludes the use of advanced quantitative analysis.
With this objective in mind, well emphasize the following aspects of Excel:
Performing calculations with formulas and functions
Creating pivot tables to summarize and interact with data
Representing data via charts and graphs
Interacting with external data
We assume no prior knowledge of Microsoft Excel. In other words, this is an introductory book on Excel, but one that focuses on exploring data and other topics that will be useful for the data analyst.
A number of features make this book unique among Excel books:
The emphasis is on data analysis
Microsoft Excel can be used to store data, analyze data, and then present data to other individuals. Our emphasis is on using Excels various features to explore and analyze data. We spend relatively little time on storing data, since that is best left to desktop databases such as Microsoft Access.
