Essential
Excel 2016
A Step-by-Step Guide
—
David Slager


Essential Excel 2016
A Step-by-Step Guide

David Slager


Essential Excel 2016: A Step-by-Step Guide
David Slager
Fort Wayne, Indiana
USA
ISBN-13 (pbk): 978-1-4842-2160-0
DOI 10.1007/978-1-4842-2161-7

ISBN-13 (electronic): 978-1-4842-2161-7

Library of Congress Control Number: 2016959787
Copyright © 2016 by David Slager
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is
concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction
on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic
adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted
from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied
specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser
of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright

Law of the Publisher's location, in its current version, and permission for use must always be obtained from Springer.
Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to
prosecution under the respective Copyright Law.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion
and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified
as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication, neither
the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may
be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.
Managing Director: Welmoed Spahr
Acquisitions Editor: Gwenan Spearing
Development Editor: Chris Nelson
Technical Reviewer: Ramona Gault and Fabio Claudio Ferracchiati
Editorial Board: Steve Anglin, Pramila Balen, Laura Berendson, Aaron Black, Louise Corrigan, James DeWolf,
Jonathan Gennick, Todd Green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James Markham,
Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing
Coordinating Editor: Nancy Chen
Copy Editor: Lori Jacobs
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global, cover image courtesy of Freepik.
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail ,
or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer
Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.
For information on translations, please e-mail , or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk

Sales–eBook Licensing web page at www.apress.com/bulk-sales.
Any source code or other supplementary materials referenced by the author in this text is available to readers
at www.apress.com. For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code/.
Printed on acid-free paper


I am dedicating this book to my parents, Andrew and Carrie (Sterk) Slager.
My parents sacrificed so much for their children. My mother was the world’s best listener.
I am also dedicating it to my wife, Annette Slager, who helped me with editing the book,
and my two wonderful children, Rhianna and Marten.


Contents at a Glance
About the Author ...................................................................................................xvii
About the Technical Reviewers ..............................................................................xix
Acknowledgments ..................................................................................................xxi
■Chapter 1: Becoming Acquainted with Excel......................................................... 1
■Chapter 2: Navigating and Working with Worksheets ......................................... 27
■Chapter 3: Best Ways to Enter and Edit Data ....................................................... 49
■Chapter 4: Formatting and Aligning Data ............................................................ 89
■Chapter 5: Different Ways of Viewing and Printing Your Workbook .................. 145
■Chapter 6: Understanding Backstage ................................................................ 173
■Chapter 7: Creating and Using Formulas ........................................................... 219
■Chapter 8: Excel’s Pre-existing Functions ......................................................... 265
■Chapter 9: Auditing, Validating, and Protecting Your Data ................................ 303
■ Chapter 10: Using Hyperlinks, Combining Text, and Working with the
Status Bar .......................................................................................................... 343
■Chapter 11: Transferring and Duplicating Data to Other Locations ................... 373
■Chapter 12: Working with Tables....................................................................... 411

■Chapter 13: Working with Charts ...................................................................... 445
■Chapter 14: Importing Data ............................................................................... 503

v


■ CONTENTS AT A GLANCE

■Chapter 15: Using PivotTables and PivotCharts ................................................ 541
■Chapter 16: Enhancing Workbooks with Multimedia ........................................ 591
■Chapter 17: Automating Task with Macros ....................................................... 643
Index ..................................................................................................................... 665

vi


Contents
About the Author ...................................................................................................xvii
About the Technical Reviewers ..............................................................................xix
Acknowledgments ..................................................................................................xxi
■Chapter 1: Becoming Acquainted with Excel......................................................... 1
What Is Excel? ................................................................................................................. 1
History of Spreadsheets ................................................................................................... 2
This Book.......................................................................................................................... 2
Excel Navigation Basics ................................................................................................... 2
Creating, Saving, and Opening Workbooks ...................................................................... 4
Getting to Know the Ribbon.............................................................................................. 9
Ribbon Contextual Tabs .......................................................................................................................... 9
Resizing the Ribbon .............................................................................................................................. 10
Using Dialog Box Launchers ................................................................................................................. 11

Minimizing and Hiding the Ribbon ........................................................................................................ 12
Using Ribbon Shortcuts ........................................................................................................................ 13

Quick Access Toolbar...................................................................................................... 15
Switch Between Touch and Mouse Mode ............................................................................................. 16

Identifying the Current Cell............................................................................................. 19
Entering Data into a Worksheet ...................................................................................... 19
Getting Help.................................................................................................................... 21
Screen Tips ........................................................................................................................................... 22
Excel’s Tell Me What You Want to Do Feature ....................................................................................... 22

Summary ........................................................................................................................ 26
vii


■ CONTENTS

■Chapter 2: Navigating and Working with Worksheets ......................................... 27
Moving Between Cells Using the Keyboard .................................................................... 27
Selecting Cells ............................................................................................................... 30
Selecting Cells Using a Mouse ............................................................................................................. 31
Selecting Cells Using a Keyboard ......................................................................................................... 33
Select Cells by Using Their Cell References in the Name Box .............................................................. 35
Going Directly to Any Cell ..................................................................................................................... 37

Worksheets .................................................................................................................... 38
Naming Worksheets.............................................................................................................................. 39
Adding and Removing Worksheets ....................................................................................................... 39
Changing a Worksheet Tab Color .......................................................................................................... 41

Selecting Multiple Worksheets ............................................................................................................. 42
Hiding and Unhiding Worksheets .......................................................................................................... 42
Reordering and Copying Worksheets.................................................................................................... 43
Using Tab Buttons to Move Through the Worksheets ........................................................................... 44

Summary ........................................................................................................................ 48
■Chapter 3: Best Ways to Enter and Edit Data ....................................................... 49
Data Types ...................................................................................................................... 49
Inserting Special Characters .......................................................................................... 51
How to Change Column Widths ...................................................................................... 55
Automatically Resize Column Widths to Fit Number of Characters in the Cell ..................................... 58
Changing the Column Width for Multiple Columns ............................................................................... 58

How to Change Row Heights .......................................................................................... 61
Correcting Typing Mistakes ............................................................................................ 63
Changing Specific Characters .............................................................................................................. 63
Returning a Cell to Its Original Value .................................................................................................... 63
Clearing the Contents of a Cell That Has Already Been Accepted......................................................... 64

viii


■ CONTENTS

Shortcuts for Entering and Correcting Data ................................................................... 65
Using the AutoCorrect Feature.............................................................................................................. 65
Using AutoComplete to Enter Data........................................................................................................ 70
Pick from Drop-down List ..................................................................................................................... 71
AutoFill.................................................................................................................................................. 73


Creating, Viewing, Editing, Deleting, and Formatting Cell Comments............................. 82
How to Add a Comment ........................................................................................................................ 82
How to View a Comment....................................................................................................................... 83
How to View all the Comments at the Same Time ................................................................................ 83
How to Change the Default Name for Comments ................................................................................. 83
Editing and Deleting Comments ........................................................................................................... 84
Printing a Comment .............................................................................................................................. 84

Summary ........................................................................................................................ 88
■Chapter 4: Formatting and Aligning Data ............................................................ 89
Formatting Your Text Using the Font Group .................................................................... 89
Using Bold, Italics, Underline, and Double Underline ............................................................................ 90
Changing the Font and Its Size ............................................................................................................. 90
Using the Font Group’s Dialog Box Launcher ....................................................................................... 93
Formatting with Color ........................................................................................................................... 93
Check Which Formats Have Been Applied to the Current Cell .............................................................. 96
Cell Borders .......................................................................................................................................... 99

Formatting Numeric Data Using the Number Group ..................................................... 110
Using Default Formats ....................................................................................................................... 111
Formatting Monetary Values............................................................................................................... 112
Converting Values to Percent Style ..................................................................................................... 113
Converting Values to Comma Style ..................................................................................................... 114
Changing the Number of Decimal Places ........................................................................................... 114
Accessing the Format Cell Dialog Box ................................................................................................ 114

Aligning Data Using the Alignment Group .................................................................... 118
Fitting More Text into a Cell ................................................................................................................ 119
Aligning and Indenting Text in a Cell................................................................................................... 123
ix



■ CONTENTS

Align Text Vertically and Horizontally .................................................................................................. 123
Rotating Text ....................................................................................................................................... 124

Using Format Painter to Copy Formatting .................................................................... 129
Using the Mini-Toolbars and the Context Menu ........................................................... 132
Inserting, Deleting, Hiding, and Unhiding Rows and Columns ...................................... 134
Hiding and Unhiding Columns and Rows ............................................................................................ 134
Inserting Columns and Rows .............................................................................................................. 139
Deleting Columns and Rows ............................................................................................................... 139

Inserting and Deleting Cells ......................................................................................... 140
Summary ...................................................................................................................... 143
■Chapter 5: Different Ways of Viewing and Printing Your Workbook .................. 145
Views ............................................................................................................................ 145
Page Break Preview ........................................................................................................................... 146
Page Layout View ............................................................................................................................... 151

Printing ........................................................................................................................ 157
Creating a Print Area........................................................................................................................... 157
Adding Additional Cells to the Print Area ............................................................................................ 158
Removing the Print Area ..................................................................................................................... 158
Using Paste Special for Printing ......................................................................................................... 160

Dividing the Excel Window into Panes ......................................................................... 162
Freezing Rows and Columns ....................................................................................... 164
Synchronizing Scrolling................................................................................................ 165

Custom Views How to Create, Show, and Delete .......................................................... 168
Summary ...................................................................................................................... 172
■Chapter 6: Understanding Backstage ................................................................ 173
Backstage Overview..................................................................................................... 173
Info Group–Viewing, Adding, and Editing Information About the Workbook ................. 174
Properties Pane .................................................................................................................................. 175
Protect Workbook Options .................................................................................................................. 178
Check for Issues ................................................................................................................................. 180
x


■ CONTENTS

New Group—Creating a New Workbook ...................................................................... 185
Open Group—Open a Workbook .................................................................................. 188
Opening an Existing Workbook ........................................................................................................... 188
Options Affecting the Open Group ...................................................................................................... 191

Save and Save As groups—Saving a Workbook Using Save or Save As ...................... 192
Document Recovery ........................................................................................................................... 194
Saving Workbooks with Protections: Backups and Limiting Changes ................................................ 201

Print Group—Printing a Workbook ............................................................................... 205
Selecting a Printer .............................................................................................................................. 206
Printer Settings ................................................................................................................................... 207

Share Group—Sharing Workbooks .............................................................................. 213
Sharing Online with OneDrive............................................................................................................. 214
Sharing Files Using E-mail ................................................................................................................. 217


Account Group .............................................................................................................. 218
Summary ...................................................................................................................... 218
■Chapter 7: Creating and Using Formulas ........................................................... 219
Formulas ...................................................................................................................... 219
Introducing Formulas ......................................................................................................................... 219
Entering Formulas .............................................................................................................................. 221
Copying Formulas ............................................................................................................................... 226

AutoCalculate Tools ...................................................................................................... 229
AutoSum ............................................................................................................................................. 229
Average, Count Numbers, Max, Min .................................................................................................... 233

Viewing Formulas ......................................................................................................... 237
Creating Named Ranges and Constants ....................................................................... 238
Naming Ranges .................................................................................................................................. 238
Naming Noncontiguous Ranges ......................................................................................................... 239
Naming Constants .............................................................................................................................. 240
Name Manager ................................................................................................................................... 241
Using Column or Row Headings for Range Names ............................................................................ 242
Selecting Named Ranges Rather Than Typing Them into Formulas ................................................... 244
xi


■ CONTENTS

Absolute Cell References ............................................................................................. 254
Mixed Cell References.................................................................................................. 259
Order of Precedence ........................................................................................................................... 263

Summary ...................................................................................................................... 264

■Chapter 8: Excel’s Pre-existing Functions ......................................................... 265
Excel’s Built-in Functions ............................................................................................. 265
Function Construction .................................................................................................. 267
Functions That Sum Values .......................................................................................... 267
SUM Function ..................................................................................................................................... 268
Using the Insert Function Option ........................................................................................................ 270
SUMIF—Adds the Cells That Meet a Specified Criteria ...................................................................... 276
SUMIFS—Adds the Cells That Meet Multiple Criteria ......................................................................... 278

IF—Returns Different Values Depending upon If a Condition Is True or False ............. 282
AND - Returns TRUE if All of Its Arguments Are TRUE................................................... 286
OR—Returns TRUE If Any Argument Is TRUE ............................................................... 286
Nested Functions ......................................................................................................... 287
Date Functions ............................................................................................................. 292
TODAY Function—Returns the Current Date ...................................................................................... 293
NOW Function—Returns the Current Date and Time ......................................................................... 295
DATE Function—Returns the Serial Number of the DATE................................................................... 297
MONTH, DAY, and YEAR Functions ...................................................................................................... 298
DAYS—Returns the Number of Days Between Two Dates ................................................................. 300

Summary ...................................................................................................................... 302
■Chapter 9: Auditing, Validating, and Protecting Your Data ................................ 303
Validating Your Data and Preventing Errors.................................................................. 303
Data Validation .................................................................................................................................... 304

Evaluating Formulas ..................................................................................................... 319
Using IFERROR .................................................................................................................................... 319
Correcting Circular References........................................................................................................... 320

xii



■ CONTENTS

Formula Auditing .......................................................................................................... 322
Tracing Precedents and Dependents .................................................................................................. 322
Using the Watch Window .................................................................................................................... 325
Using the Evaluate Formula Feature to Evaluate a Nested Function One Step at a Time ................... 327

Proofreading Cell Values—Have Excel Read Back Your Entries ................................... 331
Spell Checking.............................................................................................................. 333
Thesaurus..................................................................................................................... 334
Protect Worksheets and Cells from Accidental or Intentional Changes........................ 336
Protect Your Data at the Worksheet Level........................................................................................... 336
Protect Your Data at the Cell Level ..................................................................................................... 340

Summary ...................................................................................................................... 341
■ Chapter 10: Using Hyperlinks, Combining Text, and Working with the
Status Bar .......................................................................................................... 343
Working with Hyperlinks .............................................................................................. 343
Concatenation and Flash Fill ........................................................................................ 356
Using the Status Bar..................................................................................................... 364
Cell Mode ............................................................................................................................................ 366
Flash Fill Blank Cells and Flash Fill Changed Cells............................................................................. 366
Caps Lock, Num Lock ......................................................................................................................... 366
Scroll Lock .......................................................................................................................................... 366
Fixed Decimal ..................................................................................................................................... 367
Overtype Mode ................................................................................................................................... 367
End Mode............................................................................................................................................ 367
Macro Recording ................................................................................................................................ 367

Selection Mode ................................................................................................................................... 367
Page Number ...................................................................................................................................... 367
Average, Count, Numerical Count, Minimum, Maximum, Sum............................................................ 367
View Shortcuts ................................................................................................................................... 367
Zoom and Zoom Slider........................................................................................................................ 368

Summary ...................................................................................................................... 371
xiii


■ CONTENTS

■Chapter 11: Transferring and Duplicating Data to Other Locations ................... 373
Moving and Copying Data............................................................................................. 373
Moving and Copying Cells Using the Drag-and-Drop Method ............................................................ 374
Moving and Copying Cells Using the Cut and Copy Buttons ............................................................... 376
Moving and Copying Cells Using the Keyboard .................................................................................. 377
Paste Button Gallery ........................................................................................................................... 379
Copy Data to Other Worksheets Using Fill Across Worksheets ........................................................... 385
Copy Data from One Workbook to Another ......................................................................................... 387

Paste Special................................................................................................................ 390
Using Paste Special ............................................................................................................................ 391
Using Paste Special to Transpose Rows and Columns ....................................................................... 393
Using Paste Special to Perform Calculations...................................................................................... 395

Inserting Copied or Moved Cells ................................................................................... 400
Insert Copied Cells .............................................................................................................................. 400
Insert Cut Cells ................................................................................................................................... 403


Using the Microsoft Office Clipboard............................................................................ 404
Entering Data into Multiple Worksheets at the Same Time .......................................... 405
Summary ...................................................................................................................... 409
■Chapter 12: Working with Tables....................................................................... 411
Creating and Formatting Tables ................................................................................... 411
Sort and Filter a Table .................................................................................................. 413
Adding to the Excel Table ............................................................................................. 414
Filtering Data with a Slicer ........................................................................................... 422
Using Themes ............................................................................................................... 424
Applying and Defining Cell Styles................................................................................. 429
Conditional Formatting ................................................................................................. 432
Summary ...................................................................................................................... 444

xiv


■ CONTENTS

■Chapter 13: Working with Charts ...................................................................... 445
Chart Types ................................................................................................................... 445
Creating and Modifying Charts ..................................................................................... 448
Pie Charts ..................................................................................................................... 465
The Standard Pie Chart ....................................................................................................................... 465
Pie of Pie Subtype............................................................................................................................... 469

Combination Chart........................................................................................................ 477
Hierarchical Charts ....................................................................................................... 481
Treemap Chart .................................................................................................................................... 481
Sunburst Chart.................................................................................................................................... 487


Sparklines .................................................................................................................... 495
Summary ...................................................................................................................... 501
■Chapter 14: Importing Data ............................................................................... 503
Importing Data into Excel ............................................................................................. 503
Importing Text Files ...................................................................................................... 504
Delimited Text Files............................................................................................................................. 504
Fixed-Width Text Files......................................................................................................................... 518

Importing Data from an Access Database .................................................................... 521
Importing Data from a Web Site ................................................................................... 524
Importing Data Using a Query ...................................................................................... 527
Summary ..................................................................................................................... 539
■Chapter 15: Using PivotTables and PivotCharts ................................................ 541
Working with PivotTables ............................................................................................. 541
Creating a PivotChart ................................................................................................... 569
Creating PivotTable on a Relational Database .............................................................. 579
Summary ...................................................................................................................... 589

xv


■ CONTENTS

■Chapter 16: Enhancing Workbooks with Multimedia ........................................ 591
Adding Pictures to the Worksheet ................................................................................ 591
Using Screenshot ......................................................................................................... 599
Working with WordArt .................................................................................................. 603
Adding and Modifying Shapes...................................................................................... 609
Using SmartArt ............................................................................................................. 625
Inserting Sound into a Worksheet ................................................................................ 631

Inserting Video into a Worksheet. ................................................................................. 633
Using Bing Maps .......................................................................................................... 634
Summary ..................................................................................................................... 641
■Chapter 17: Automating Task with Macros ....................................................... 643
Creating (Recording) a Macro....................................................................................... 643
The Problem with Absolute Cell References ................................................................ 646
Saving a Macro-Enabled Workbook ............................................................................. 648
Creating a Macro Using Relative Cell References ........................................................ 650
Adding Macros to the Quick Access Toolbar and Other Objects ................................... 652
Sharing the Personal Workbook with Others................................................................ 658
Looking at VBA Code .................................................................................................... 658
Creating Macros from Code.......................................................................................... 661
Summary ...................................................................................................................... 664
Index ..................................................................................................................... 665

xvi


About the Author
David Slager’s career has involved working with technology, creating
documentation, authoring, planning, programming, and training. He
enjoys working with analytics and solving problems. He has taught
students of all ages: elementary, middle school, high school, and college
students; adult education; and corporate training.
As a consultant, he developed major e-learning training projects for
agriculture and steel businesses. He designed a simulation program that
trained feed market managers to use analytics to improve their position in
the market. Steel mill executives, managers, staff, and floor workers used
his training program to learn the Order Fulfillment System.
He consulted with and trained school officials and teachers from

dozens of private and public schools on using technology in the classroom.
David has a Master of Science in Education, specializing in Instructional Media Development, a
bachelor’s degree in Organizational Management, and an associate’s degree in Accounting. He is A+ and
Network+ certified. He is a CDP (Certified Data Processor) and a CCP (Certified Computer Professional).
He is a Master Certified Microsoft Office User Specialist certified in all Microsoft Office Products.
He was a computer programmer and involved in computer operations for many years. He was also the
computer department head of a college for many years.
David created the web site www.professoroffice.com.

xvii


About the Technical Reviewers
Fabio Claudio Ferracchiati is a Senior Consultant and a Senior Analyst/Developer using Microsoft
technologies. He works at BluArancio S.p.A (www.bluarancio.com) as Senior Analyst/Developer and
Microsoft Dynamics CRM Specialist. He is a Microsoft Certified Solution Developer for .NET, a Microsoft
Certified Application Developer for .NET, a Microsoft Certified Professional, and a prolific author and
technical reviewer. Over the past ten years, he has written articles for Italian and international magazines
and co-authored more than ten books on a variety of computer topics.
Ramona Gault is a freelance writer and editor who worked for over a decade as a technical editor in the
Microsoft Office group in Redmond, Washington, where she became a fierce advocate for the end user.

xix


Acknowledgments
I would like to extend a special thank you to Chris Nelson, the development editor, for helping me prepare
this book.

xxi



CHAPTER 1

Becoming Acquainted with Excel
Excel is a powerful and versatile spreadsheet program that can be used for both business and personal
needs. It has amazing capabilities that you can use to make any type of data you record more streamlined
and productive. In the first chapter, you’ll learn the basics of creating worksheets, and how to use the
Ribbon, a feature which drives the user-friendly resources in Excel.
After reading and working through this chapter you should be able to
•

Know what Excel is and know some of its capabilities

•

Create, save, and open a workbook

•

Identify the current cell

•

Use the Ribbon

•

Use and customize the Quick Access Toolbar


•

Enter data in a worksheet

•

Get help by using Screen Tips and the Tell me what you want to do features

What Is Excel?
Excel is an electronic spreadsheet program. A spreadsheet is a grid of cells organized into rows and columns
in which you enter and store your data. Excel can meet both your personal and professional needs.
Using Excel, you can do all of the following:
•

Create, edit, sort, analyze, summarize, and format data as well as graph it.

•

Keep budgets and handle payroll

•

Track investments, loans, sales, inventory, etc.

•

Perform What-If Analysis to determine such things as “if the price of gas went up
20 cents per gallon” by how much would that decrease my profit, or “if I extend my
loan payments from 15 years to 20 years” by how much will that affect my monthly
payments, total payments, and total interest.


One can improve the appearance of a spreadsheet or better convey what you want a spreadsheet to say
by adding pictures, clip art, shapes, smart art, video, and audio.

Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-2161-7_1)
contains supplementary material, which is available to authorized users.
© David Slager 2016
D. Slager, Essential Excel 2016, DOI 10.1007/978-1-4842-2161-7_1

1


CHAPTER 1 ■ BECOMING ACQUAINTED WITH EXCEL

Microsoft Office is Microsoft’s most profitable product. Microsoft devoted most of its effort in Microsoft
Office 2016 to updating Excel. Microsoft made few changes to its other products in Office.

History of Spreadsheets
VisiCalc (short for Visible Calculator) was the first computerized spreadsheet available to the public. It was
created by Dan Bricklin and Bob Frankston in 1979 for the Apple IIe and then released in 1981 for the newly
created IBM PC. Up to this point, sales of personal computers had been slow because there wasn’t a lot you
could do with them. Early PCs were very expensive and there weren’t any prewritten applications. They
were mostly purchased by computer programmers who thought they were fascinating and it gave them a
chance to practice programming at home. At this point, programmers worked on large-scale computers
called mainframes. At that time, you couldn’t go to a store and buy software like you can today. Back then,
company programmers wrote all the programs that the company needed themselves. Each company
wrote its own payroll program, its own inventory program, etc. Companies didn’t share the software with
each other. With VisiCalc, businesses now had a product that could be of great benefit. Sales of personal
computers took off. VisiCalc became the world’s first Super App. VisiCalc also started a revolution in
businesses being started for the sole purpose of creating software to be sold to the public.

The Lotus 1-2-3 Spreadsheet program was released in 1983. It was made specifically for the IBM PC. It
was faster and had better graphics than VisiCalc and soon replaced it. Lotus 1-2-3 greatly increased the sales
of the IBM PC.
Microsoft Excel has dominated the spreadsheet market since the 1990s.

This Book
An Excel book that taught you every possible option would be too large for you to carry. As you go through
the material in the book, explore the different options and try different things, think about how you could use
what is being taught in different environments. Don’t just click here and enter that, because that is what the
book is telling you to do without thinking about what it is you are doing. Excel is so powerful and is capable of
doing so many things. Be a free thinker and think about how you could use Excel to solve various problems.
Throughout this book you will be reading about an Excel topic followed by a practice. You can learn
by reading, but to fully comprehend the different topics you should do the exercises. Many illustrations are
included to make it easier to follow along and comprehend.

■ Note Your Excel program might not match perfectly with this workbook. Microsoft is constantly making
changes to the program through the Internet.

Excel Navigation Basics
Before we can do anything with Excel, let’s get to know the main parts of the program. Figure 1-1 shows an
Excel workbook. The arrows have been added to highlight the purpose of the different areas of the workbook.

2


CHAPTER 1 ■ BECOMING ACQUAINTED WITH EXCEL

Figure 1-1. An Excel workbook
Figure 1-1 shows essential components of the workbook and worksheet. I’ll work clockwise around the
sheet starting with the Quick Access Toolbar (QAT).

•

The QAT is a shortcut tool for storing the commands you use most often and want
quick access to.

•

The formula bar shows the formulas for the current selected cell. Excel displays the
result of the formulas, not the formula itself, in each cell. This bar lets you see the
formula that is producing the cell results.

•

The vertical scroll bar and horizontal scroll bar allow you to move through the
worksheet page.

•

The Zoom control, Page Break Preview, Page Layout View and Normal View are
buttons that allow you to control how you are viewing the worksheet.
•

The Zoom control lets you increase or decrease the size (Zoom percentage) of
the worksheet on your screen.

•

Page Break Preview allows you to control where one page ends and another
begins. This helps make the worksheet more user friendly by allowing pages to
be organized in a way that makes sense to the user.


•

Page Layout View shows how the page will look when it is printed. Use this
function to ensure the printed workbook will be neat and easy to read.

•

Normal View is the default view. It shows how the workbook looks while you are
working on it. Sheet Tabs let you select the worksheet that you want to work on
or view. Many workbooks in Excel will have multiple sheets.

3


CHAPTER 1 ■ BECOMING ACQUAINTED WITH EXCEL

The row number tells you what row you are on in the workbook. Excel has a potential of 1,048,576
rows. Columns are identified by letters. There are 16,384 columns in an Excel spreadsheet. This means that
a single worksheet contains more than 17 billion cells. Each cell can hold 32,767 characters. How many
worksheets you can have in a workbook depends upon your computer’s available memory. Each cell is
identified by an address which consists of the column letter and the row number. The Name box displays
the address of the cell where you are at the moment. The Name box in Figure 1-1 displays A1 which is the
address of the current active cell. The Ribbon provides access to all of Excel’s capabilities. The Ribbon will be
discussed in much greater detail later in this chapter and in subsequent chapters.

Creating, Saving, and Opening Workbooks
The first step is to create a workbook. Next, you must make sure to save your work as you go. You should
consider what you want in the workbook and what it should be named before you create and save it. This
will make it easy to open and use it again.

We’ll start our Excel journey by creating a new workbook and then examine the different parts of
the workbook. How you start Excel depends upon your operating system. Excel starts just like any other
application you use.

EXERCISE 1-1: CREATING AND SAVING A WORKBOOK
In this exercise, we’ll create a simple blank workbook and save it.
1.

4

Start your Excel program. If the Excel start button is on your status bar you can click
on it, otherwise start Excel the way you normally start a program. Figure 1-2 shows
the opening window.


CHAPTER 1 ■ BECOMING ACQUAINTED WITH EXCEL

Figure 1-2. Excel starting window

5


CHAPTER 1 ■ BECOMING ACQUAINTED WITH EXCEL

■ Note The file names on the left side of your window will not be the same as those shown in Figure 1-2
because they are the names of the files that I have opened.
2.

When you start a new workbook, you have two choices:
•


You can start with a blank workbook by clicking the Blank workbook button, or

•

You can click one of the many template buttons to create a new workbook based
on the templates you selected.

Click Blank workbook for this exercise.
3.

Click any cell, type any value you want, and then press the Enter key.

4.

Click the Save button
located on the QAT at the top left of your window (see
Figure 1-1). The first time you save the workbook Excel will display the File tab with
Save As highlighted. See Figure 1-3.

Figure 1-3. Places to save your workbook

■ Note You will learn a lot more about saving workbooks from the File tab (known as the Backstage) in
Chapter 6.
You can save your file to many different locations. If you are using this book in a school, you
should ask your instructor where to save your files. The folders on the right are places where
you have recently saved files. Selecting one of these locations or clicking on the Browse
button will bring up the Save As window.
5.


Click Browse.

Your Save As window may look slightly different than the one in Figure 1-4 depending upon
your version of Windows. If you have used File Explorer before, this window works the same
6