Excel Basics E-Book

Jeffrey Hsu

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 337

Veröffentlichungsjahr: 2024

Beschreibung

This book is designed for first-time or beginning Excel users, providing a comprehensive yet accessible introduction to mastering Microsoft Excel 2019 and previous versions. Starting with the basics, it guides readers through the foundational concepts and features of Excel, ensuring a solid understanding of this essential software. Key topics include an overview of Excel, formulas, functions, formatting, conditional features, graphs, charts, pivot tables, and using a spreadsheet as a database.
The book is richly illustrated with screenshots, examples, applications, and exercises to reinforce learning. Suitable for professional reference, self-study, or instructor-led courses, it equips readers with the skills to enhance business and personal productivity. The content is structured to build from basic to advanced topics, making it a valuable resource for users at any stage of learning.
With additional resources like tutorial videos, PowerPoint slides, sample syllabi, tests, and quizzes, this book ensures a thorough and engaging learning experience. By the end of the course, users will be proficient in Excel, capable of leveraging its full potential to streamline tasks and improve efficiency.

Leseprobe

EXCEL® BASICS

Foundations • Formulas • Graphs

Gary Bronson, Ph.D.Silberman College of BusinessFairleigh Dickinson University

Jeffrey Hsu, Ph.D.Silberman College of BusinessFairleigh Dickinson University

Copyright ©2021 by MERCURY LEARNING AND INFORMATION LLC. All rights reserved.

This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David Pallai

MERCURY LEARNINGAND INFORMATION

22841 Quicksilver Drive

Dulles, VA 20166

[email protected]

www.merclearning.com

1-800-232-0223

J. Hsu and G. Bronson.Excel® Basics.

ISBN: 978-1-68392-772-3

Excel® is a registered trademark of Microsoft Corporation.

The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2021944921212223321     Printed on acid-free paper in the United States of America.

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc.For additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).

All of our titles are available in digital format at academiccourseware.com and other digital vendors. The sole obligation of MERCURY LEARNING AND INFORMATION to the purchaser is to replace the book, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.

To Lottie, who represents the futurefor those who love science and technology!

—Jeffrey Hsu

To Serafina, Emerson, and Isaiah

—Gary Bronson

CONTENTS

Preface

Acknowledgments

About the Authors

PART 1   BASIC SKILLS

Chapter 1   Getting Started

1.1   Introduction to Excel Spreadsheets

Cell Contents

Entering Data into a Cell

Exercises 1.1

1.2   Two Essential Skills – Highlighting and Copying

Selecting Cells by Highlighting

Copying Cell Contents

Exercises 1.2

1.3   Opening, Saving, Retrieving, and Printing Spreadsheets

Opening a New or Existing Spreadsheet

Saving a Spreadsheet

Printing a Spreadsheet

Exercises 1.3

1.4   Professional Spreadsheet Guidelines

Input and Results Sections

1.5   Common Excel Errors

Common Errors

1.6   Chapter Appendix: The Excel Development System

Using Excel’s Options

Using the Excel Ribbon Tabs Toolbar

Appendix Exercises

Chapter 2   Formulas and Formatting

2.1   Formulas

From Expressions to Formulas

Graphically Selecting Cells

Copying Formulas

Expression Construction Rules

Exercises 2.1

2.2   Introduction to Excel Functions

Highlighting Selected Arguments

Exercises 2.2

2.3   Formatting

Exercises 2.3

2.4   Cash-Flow and Cash-Balance Spreadsheets

A Cash-Balance Spreadsheet

Exercises 2.4

2.5   Absolute Addresses

Case 1: Magic Numbers as Input Values

Case 2: Running Sub-Totals

Exercises 2.5

2.6   Common Excel Errors

2.7   Chapter Appendix: Working with Rows and Columns

Adjusting Column and Row Widths

Inserting and Deleting Columns and Rows

Chapter 3   Functions and Conditional Formatting

3.1   Date, Time, and Text Functions

Text and Character Functions

Exercises 3.1

3.2   Statistical Functions

Standard Deviations

Exercises 3.2

3.3   Financial Functions

The PMT( ) function

Repayment Schedules: Amortization

Exercises 3.3

3.4   Information (IS) Functions

3.5   Conditional Formatting

Creating a Simple Conditional Formatting Rule

Creating a User-Entered Conditional Value

Exercises 3.5

3.6   Common Excel Errors

3.7   Chapter Appendix: Transferring Data Between Sheets

Chapter 4   Selection

4.1   Relational Expressions

Logical Functions

Exercises 4.1

4.2   The IF( ) Function

Selecting a Text Value

Selecting a Numeric Value

Selecting a Calculation

Nested IF( ) Functions

Logical Conditions

Exercises 4.2

4.3   The VLOOKUP( ) Function

Exact Match

Give It a Try

Approximate (Closest) Match

Exercises 4.3

4.4   The HLOOKUP( ) Function

Give It a Try

Exercises 4.4

4.5   Common Excel Errors

4.6   Chapter Appendix: Testing and Debugging

PART 2   PRESENTATIONS

Chapter 5   Graphical Presentations

5.1   Introduction

Exercises 5.1

5.2   Creating Column and Bar Charts

Changing a Column Chart’s and Bar Chart’s Elements and Appearance

Changing Chart Types

Exercises 5.2

5.3   Creating Pie Charts

Changing a Pie Chart’s Elements and Appearance

Changing Chart Types

Exercises 5.3

5.4   Creating Line Graphs

Changing a Line Graph’s Chart Elements and Appearance

Changing Chart Types

Exercises 5.4

5.5   Trend Lines and Scatter Diagrams

Creating a Scatter Diagram

Changing a Scatter Diagram’s Elements and Appearance

Adding a Trend Line

R2 and Selecting an Appropriate Trend Line

Exercises 5.5

5.6   Common Excel Errors

5.7   Chapter Appendix: Creating Histograms

Procedure to Produce a Histogram in all Excel Versions

Chapter 6   PivotTables

6.1   Introduction

Basic PivotTable Elements

Exercises 6.1

6.2   Creating a Basic PivotTable

Additional Row and Summary Column Examples

Single Row Field and Single Value Field

Multiple Row Fields and Single Value Fields

Single Row Fields and Multiple Value Fields

Multiple Row Fields and Multiple Value Fields

Exercises 6.2

6.3   Adding Column Fields

Additional Column Field Examples

A Single Set of ROW and COLUMN Fields

Two Sets of Row Fields and a Single Set of Column Fields

Exercises 6.3

6.4   Common Excel Errors

6.5   Chapter Appendix: PivotTable Design Criteria

PART 3   INTERMEDIATE SKILLS

Chapter 7   Sorting and Searching

7.1   Records as Rows

Exercises 7.1

7.2   Sorting Spreadsheet Rows

Multi-Level Sorting

Exercises 7.2

7.3   Creating Group Subtotals

Nested Subtotals

Creating Nested Subtotals

Exercises 7.3

7.4   Searching (Filtering) for Selected Records

More Complex Searches

OR condition

Multiple ANDs on the same Criteria Range Row

Multiple AND Conditions on the same Criteria Range Row

Criteria employing both AND and OR conditions

Exercises 7.4

7.5   Database Functions

Exercises 7.5

7.6   Common Excel Errors

7.7   Chapter Appendix: Spreadsheets as Databases

Chapter 8   Intermediate and Advanced Techniques

8.1   Creating Drop-Down Lists

Exercises 8.1

8.2   Correcting Rounding Errors

8.3   Creating De-Duping Keys for Removing Duplicate Records

8.4   Array Functions - The SUMPRODUCT( ) Function

Exercises 8.4

8.5   User-Entered Conditional Formatting Formulas

Exercises 8.5

8.6   Chapter Appendix: Restricting Cell Access

Appendix A   Character Code Storage

Appendix B   Installing the Analysis ToolPak

Index

PREFACE

This book is designed to meet the needs of multiple audiences, including that of professionals and individuals who are interested in becoming well versed in the basics of using Excel spreadsheets, and also for courses of various levels where the primary focus is on learning and using Microsoft Excel.

Whether in the fields of business, science, statistics, mathematics, or data analytics, there are countless practical uses for Microsoft Excel, and this book will help to get you started in a friendly, non-technical, and approachable manner. It is suitable for those who prefer to learn Excel through self-study and learning, in order to further one’s skills in analytical problem solving and decision-making using Excel as an analysis tool.

This book is also designed to be used as a textbook or supplemental reference resource for various levels and audiences, including secondary, higher education, seminars, and specialized training courses. With the recent and increased interest in STEM education, this book is an ideal choice for a broad range of audiences and courses.

This book was written to provide a firm foundation in Excel for beginning learners and students. Its intent is to present, in a clear and accessible manner, the key capabilities needed for spreadsheet applications. These capabilities include knowing the basic data types and when they should be used, how to format cells, how to print spreadsheets in landscape or fit-to-page modes, how to create formulas, how to use absolute and relative addresses appropriately in copying formulas, and the basic Excel functions. This text presents topics in a clear, unambiguous, and accessible manner for beginning learners and students of Excel.

A major consideration was to give readers and students an understanding of the what and how that goes into creating spreadsheets properly, so that they avoid the mistakes commonly made by beginners. One example is in clearly and fully understanding the basic data types and when they should be used. For example, some novice users, without this understanding, would enter zip codes as numeric data, and then not understand why leading zeros were not printed. Additionally, knowing how to format a cell or how to copy a cell formula down through a column or across a row is essential. Without a clear and focused understanding, many would simply retype the same general formula, over and over, using new cell references. As an understanding of these basics is essential for creating a solid foundation for beginning learners, they form the core of this book.

Interestingly, we always encountered many novice learners who claimed to have a good knowledge and experience with Excel. Many of them could actually produce acceptable and sometimes even professional-looking spreadsheets, but under closer examination were found to have noticeable gaps in their understanding. For example, when asked to present the formulas behind their spreadsheet, it was frequently discovered that many cells included manual calculator-determined results. As such, the power and versatility of Excel is not being properly utilized. To forestall the development of such habits from even starting, a central element of this text is to give readers and students a real understanding of how spreadsheets are constructed so that they develop practices that use Excel with maximum effectiveness.

To facilitate this learning process, this together with a separate Excel 2019 Project Book (Mercury Learning: Bronson and Hsu, 2021) require the student to properly create actual, practical spreadsheets. Each spreadsheet application requires either using a newly introduced capability, or reinforces one or more previously learned capabilities. This is done in incrementally and increasingly more involved and demanding ways, so that the reader gets to really understand the skill being presented. Both texts can be used independently or together.

Part One (Chapters 1-4) form the central core of this text. It is here that the basic structure of Excel is explained and the elementary but necessary features of entering data into a cell, the three types of data permitted by Excel, and saving, retrieving, and printing a spreadsheet are presented.

Additionally, formulas, functions, and formatting are explained, together with conditional formatting, specific categories of functions (statistical, financial, information, date and time, text) and a basic professional template that separates a spreadsheet into the input and result areas is presented. Specialized formulas and functions including the =IF, vertical and horizontal table lookups, and conditional expressions are covered. The applications discussed include car and mortgage loans calculations, repayment schedules, and text and character manipulation techniques. Finally, each chapter in this section contains a list of commonly made implementation errors and a chapter appendix providing information on less used but important concepts that users typically encounter.

Part Two (Chapters 5-6) presents Excel’s chart and graph tools (column, pie, line graphs, etc.) and the concepts and process behind creating “summary table reports” using PivotTables. Included in the charts and graphs chapter is coverage of the benefits and steps involved in creating trend lines, using correlations, and developing histograms. Varying approaches, structures and applications are presented in the chapter on PivotTables.

Part Three (Chapters 7-8) contains more intermediate to advanced features that include sorting and filtering (searching) Excel lists, employing database functions, created subtotaled lists, setting up drop-down lists, using array functions, and developing user-created conditional-formatting formulas.

DISTINCTIVE FEATURES OF THIS BOOK

Writing Style. The goal of this book is to present the essentials and basics of an Excel spreadsheet, in a clear and easy to understand way. The discussion is supplemented by illustrations, examples, and helpful tips.

Application Testing. Every spreadsheet in this text has been successfully developed and tested using Excel on Windows-based computers, by novice learners and students who have used both these and Apple® Mac computers. This ensures that readers and students can both experiment and extend the existing spreadsheets and more easily modify them as required by a number of end-of-section exercises.

UNIQUE FEATURES

To facilitate the goal of making Excel accessible to beginners, the following unique features have been incorporated into the text:

Application Notes.   These shaded boxes in each chapter highlight important concepts, useful technical points, and developer tips and techniques used by professional spreadsheet developers.

End-of-Section Exercises.   Most sections of the book contain diverse skill builder and spreadsheet exercises.

Common Excel Errors.   Each chapter contains a section on common implementation errors typically encountered by beginning and novice users.

Chapter Appendices.   Each chapter ends with an appendix presenting more detail about specific topics, such as the Excel Development Screen, working with rows and columns, how to transfer data between sheets, how to locate and fix incorrectly copied formulas, and how to use a spreadsheet as a database.

Instructor Resources.   Instructor resources include PowerPoint slides for each chapter, video tutorials, sample syllabi, and sample tests and quizzes.

Jeffrey Hsu, Ph.D.Gary Bronson, Ph.D.September 2021

ACKNOWLEDGMENTS

We would like to express our thanks to the hard-working and dedicated staff at Mercury Learning and Information. To David Pallai, Publisher, for expressing confidence in and publishing this book, and for tirelessly working with us closely on all aspects of the publication process, from beginning to end. It is rare that authors can receive the “personal touch” from such a dedicated and experienced publishing professional, who guided us through the various intricacies and challenges of the process. To Jennifer Blaney, for her professionalism, production expertise, and in doing a great job keeping the book on schedule.

In particular, we would like to recognize the commitment to quality that has been shown by these individuals, in terms of the care shown during copyediting, resolving issues, requesting multiple rounds of page proofs, and providing us the opportunity to provide input into just about every aspect of our book and its production, marketing, and promotions. For this, we are grateful.

Jeffrey Hsu would like to express thanks to my friend, colleague, and co-author, Gary Bronson for the wonderful experience in working together. It was always stimulating and insightful, and I find that I always learn something new working with Gary. We often say to each other that “we make a good team” and indeed it is true!

Jeffrey Hsu would like to express thanks to my friend and colleague Mel Stern, who encouraged me to explore further and fully appreciate all that Excel has to offer.

Jeffrey Hsu would also like to express thanks to my niece, Lottie, who helped me select the cover design, and also to whom this book is dedicated.

Finally, to both our families, for being understanding with us during the many hours we spent working on the book, and for being ever more patient when they hear from us over and over that “we still have more work to do on the book.”

ABOUT THE AUTHORS

Jeffrey Hsu, Ph.D., is a professor of information systems at the Silberman College of Business, Fairleigh Dickinson University. He is the author of numerous papers, chapters, and books, and has previous business experience in the software, telecommunications, and financial industries. His research interests include human-computer interaction, e-commerce, IS education, and mobile/ubiquitous computing. He is the editor in chief of the International Journal of e-Business Research (IJEBR). Dr. Hsu also serves as the managing editor of the International Journal of Data Analysis and Information Systems (IJDAIS), and is on the editorial board of several other journals. Dr. Hsu received his Ph.D. in information systems from Rutgers University, his M.S. in computer science from the New Jersey Institute of Technology, and an M.B.A. from the Rutgers Graduate School of Management.

Gary Bronson, Ph.D., is a professor of information systems at the Silberman College of Business, Fairleigh Dickinson University, where he was twice voted Teacher of the Year of the college and received the Distinguished Faculty Award for Research and Scholarship, of the university. He has worked as a senior engineer at Lockheed Electronics, an invited lecturer and consultant to Bell Laboratories, and a software consultant to a number of Wall Street financial firms. He is the author of the highly acclaimed A First Book of C and has authored several other successful programming textbooks on C++, Java, and Visual Basic. He is a co-author of the Excel 2019 Project Book with Jeffrey Hsu and the co-author of Mathematics for Business with Richard Bronson, and Maureen Kieff. Additionally, he is the author of a number of journal articles in the fixed-income financial and programming areas. Dr. Bronson received his Ph.D. from Stevens Institute of Technology.

Part1

BASIC SKILLS

Chapter 1     Getting Started

Chapter 2     Formulas and Formatting

Chapter 3     Functions and Conditional Formatting

Chapter 4     Selection

C H A P T E R1

GETTING STARTED

This Chapter Contains

1.1   Introduction to Spreadsheets

1.2   Two Essential Skills – Highlighting and Copying

1.3   Opening, Saving, Retrieving, and Printing Spreadsheets

1.4   Professional Spreadsheet Formats and Guidelines

1.5   Common Excel Errors

1.6   Chapter Appendix: The Excel Development System

1.1 INTRODUCTION TO EXCEL SPREADSHEETS

At its most basic level, an Excel spreadsheet is simply a file containing sheets of individual storage locations, as shown in Figure 1.1. In general, a spreadsheet file is called a workbook, while the individual sheets are known as worksheets. By convention, the terms worksheet and spreadsheet are used synonymously, and both of these terms are used interchangeably throughout this text. Initially, and for many of your early applications, you will only need one spreadsheet. However, for more advanced applications, it is common to employ multiple worksheets within the same workbook.

Figure 1.1

An example of an empty spreadsheet

Each individual location within a spreadsheet is referred to as a cell. To clearly locate a cell in a spreadsheet, each cell has a column and row designation. Columns are marked with a letter, starting with the letter A, and each row is marked with a number, starting with the number 1, as shown in Figure 1.2. There are many cells available on an Excel worksheet. Excel 2019 supports over a million rows, over 16,000 columns, for a total of more than 17 billion cells on a single worksheet.

Application Note

Relative, Absolute, and Mixed Cell Addresses

A cell address that consists of a column and row designation, such as A1, B24, or M256, without any other notations or symbols included, is known as a relative cell address (or relative cell reference). The vast majority of cell addresses you would encounter are of this type, and they are the type you typically would use most often, especially when you first start working with Excel.

There are times, however, when a different type of address, known as an absolute cell address (or absolute cell reference), such as ($A$1 or $B$24) or a mixed cell address (or mixed cell reference) , such as ($A1 or B$24) is needed. The reason for these other kinds of cell addresses is explained in Section 2.5. In general, relative cell addresses allow both the column and row to change when copied, absolute cell addresses allow neither to change, and mixed allow either the row or column to change (but not both). For now, simply be aware that the cell addresses you are currently using are one of three types of addresses available in Excel, and that each one serves a different purpose when copying cells.

Figure 1.2

A spreadsheet with the rows and columns labeled

Every Excel spreadsheet has these same column and row markings. This permits a cell in a spreadsheet to be uniquely located and identified by giving its column and row designation, which together constitute the cell’s address. For example, the address A1 refers to a spreadsheet’s leftmost and topmost cell, while the address C4 refers to the cell located in column C and row 4. For convenience, these two cells have been highlighted in Figure 1.3.

Figure 1.3

The cell addresses A1 and C4 are highlighted

In addition to providing the basic worksheet shown in Figure 1.3, what makes Excel so useful and more than just a sheet of storage locations are its following capabilities:

The ability to enter, change, and delete data from individual cells and print parts of complete spreadsheets

The ability to perform basic mathematical operations (such as addition or multiplication) on numerical data within two or more cells

The availability of functions that can be used to determine square roots, payments on loans, averages, standard deviations, and many other statistical operations

The ability to change the appearance of numbers and text (style, font, size, and color), and format numbers to appear in different ways (such as the number of decimal values to be displayed, dollar signs, and date)

The ability to create and print graphs and summary tables

The ability to change the width of individual columns and the height of individual rows

The ability to use one or more worksheets that can be linked together (that is, data in one worksheet can be accessed by another worksheet)

In fact, most of this text is focused on showing you how to use these capabilities. Your expertise in using Excel will depend on your skill in learning and applying these capabilities to your own individual projects.

Cell Contents

It is particularly important to understand that each and every spreadsheet cell can contain only one main data entry (which may consist of various data sub-elements) at a time. The vast majority of data elements you will enter, and the only ones that are directly supported in Excel, must be one of the following:

Numbers

Text (sometimes referred to as labels)

Formulas

Excel identifies and stores each of these data types in its own internal manner as code by recognizing the following characteristics of the data:

A number is data that includes only the following characters:

Numeric digits (0 through 9)

Special symbols (such as -, $, and .)

Thus, a number, at a minimum, must contain one or more of the numeric digits 0 through 9. Additionally, a decimal point, commas, a leading dollar sign, and a leading + or – sign can, but need not, be included. By default, the content of a cell is interpreted as a number whenever these, and only these, symbols are present, and the cell has not been designated as holding a different type of data, which is accomplished using formatting (described in Section 2.4). By default, a number is displayed toward the right side of a cell, which is referred to as right-alignment. Each individual number is stored using a numerical code format.

Text is any data containing a letter or character, except data recognized as a formula or a function. Typically, text is used to create headings and labels. Thus, any cell that contains at least one letter or non-numeric symbol (!, #, &), begins with a double-quote ("), or the cell itself has been designated as a text cell (which is accomplished using formatting), is considered text and stored by Excel in a character-by-character code. Attempting to perform a mathematical operation using a cell containing text results in an error, or no result, since text does not have any numeric value.

Aformula always begins with an equal (=) sign. Thus, a cell’s contents that begin with an equal sign are considered to be a formula, as well as if the cell contains both operands and operators. One exception is when formatting has been used to change the formula into text. In this latter case, the formula will not compute anything but will display the formula as a text string.

Formulas can be user-created, such as =3"A1, which multiplies the value in A1 by 3, or =B1+B2+B3+B4, which adds the contents of cells B1, B2, B3, and B4. Typically, however, an Excel supplied function, such as SUM( ), is used within a formula.

A formula can contain a function, such as =SUM( ). A function is a self-contained “program” that can be used to do computations and perform operations. The ability to use formulas and functions to create new information from existing data and keep a spreadsheet current whenever data is changed forms the essence of what makes spreadsheets valuable.

For example, the functions =SUM(B1:B4) and =AVERAGE(B1:B4) calculate the sum and average, respectively, of the values in cells B1 through B4. Notice that the notation B1:B4 used in each of these functions indicates that a range of cells, from B1 through B4, inclusive, are used in the calculation of the sum and average, respectively. Instead of explicitly specifying operators and operands (as in an Excel formula), it is like a “black box” to which arguments (inputs) are fed, and the resulting output displayed. The result of each calculation appears in the cell containing the formula.

Example 1

Figure 1.4 shows a section of a spreadsheet that was created using all three data types: (numbers, text, formulas), and functions.

Application Note

The Importance of Formulas

Formulas form the backbone of all spreadshe et applications and are what distinguish an Excel spreadsheet from a table of values prepared using a word processing program. There are two main reasons for this.

First, when you create a spreadsheet application that has more than two or three columns and/or rows, which is the norm for most commercial applications, typically only a single formula needs be entered in the first cell of each column or row. This one formula is then simply copied, as needed, to the remaining cells in the column or row. (Because of relative cell addresses, Excel automatically adjusts cell references in the copied formula, described further in Section 2.2).

Second, when you use a spreadsheet and change any of the values used in a formula, the formula will automatically recalculate and display the correct value for the new data. This is extremely important in keeping a spreadsheet up-to-date and accurate for all displayed values.

Figure 1.4

A spreadsheet that uses all three data types: (numbers, text, formulas) and functions

Figure 1.5 shows what was actually typed into each cell.

Figure 1.5

The input for Figure 1.4

As shown in Figure 1.5, cells B2 through B4 contain numbers, while cells B5 through B8 contain formulas and functions. In cell B5, the sum is found with a formula that uses the addition operator (+), while cell B7 uses Excel’s SUM( ) function, both of which are explained in more detail in Chapter 2. Similarly, the average of the grades is determined in cell B6 using the division operator (/), while cell B8 uses Excel’s AVERAGE( ) function. In all four cases, however, Excel knows these formulas and functions are to be calculated because they begin with an equal (=) sign. Although not shown, a function can be included within other functions and mathematical expressions as in =10 " SUM(B2:B4)/3.

Notice that in Figure 1.5, cells B1 and A5 through A8 all contain text. The text in cell B1 is referred to as a heading, as it is located at the top, or head, of a column and identifies what the column contains. The text in cells A5 through A8 is referred to as labels. Labels, like headings, are always text.

For the data entered in Figure 1.5, fill in the data types that you think Excel assigns to each entered item using Figure 1.6 below.

NOTE

Remember that there are three data types (number, text, formula) and functions.

Figure 1.6

Fill in the spreadsheet with the correct data types for the data in Figure 1.5

Dates and Boolean Data

In addition to the three basic data types (numbers, text, and formulas) and functions, there are two other data items that can be entered into a cell: dates and Boolean data. Dates on or after 1/1/1900 are stored as consecutive integers, with the number 1 representing January 1, 1900 (i.e., 1/1/1900), the number January 2, 1900, and so on.1 Dates prior to 1/1/1900 are stored as text, and can be changed to numbers. In all cases, dates prior to and after 1/1/1900 can always be displayed and stored as text.

Boolean data consist of the two values, TRUE and FALSE. These are described in detail in Section 3.3. Boolean data form the basis for comparison operations, such as “is the value in cell A5 greater than the value in cell B5.” Internally, the Boolean TRUE and FALSE values are stored as the numbers 1 and 0, respectively.

1 The integer numbers used to store dates on and after 1/1/1900 are referred to as Serial Numbers in Excel.

Figure 1.7 shows the appearances of dates in three of formats provided by Excel. How these formats are selected is presented in Section 2.3.

Figure 1.7

Different date appearances

Entering Data into a Cell

To enter data into a cell, first use either the mouse or the cursor arrow keys to go to the desired location. If you use the mouse, click when you are at the designated cell. When you do so, the cell border will appear as shown in Figure 1.8, indicating, in this case, that cell D4 is the currently selected (active) cell. Note that the currently selected cell location also appears in the name box (this provides another way of selecting a cell: simply type the cell location in the name box, which is also useful when creating named ranges for more advanced needs).

When the desired cell has been selected, type the number, text, formula, or function that you want stored in the cell. As you type, the data will appear in both the cell and in the Formula Bar. When either the enter key or one of the cursor arrow keys is pressed, the data is stored in the cell and becomes the cell’s contents. (The shortcut key F5 can also be used on both the PC and the Mac.)

Figure 1.8

Entering data into a cell

EXERCISES 1.1

Answer the following questions.

How is the location of an individual cell indicated?

What is the term used for the cell addresses in the form A1, G5, and M26?

List the three basic types of data that can be entered into a cell and how Excel determines the data type of each item that is entered. What is a function?

How is a range of contiguous cells indicated in a formula?

Determine the data type of each of the following:

121.135

=A1"B1

=M5/N5

ABOVE AVERAGE

$89.47

=AVERAGE(A1:A10).

For the data and formulas shown in the spreadsheet below, determine and fill in the values that will be calculated and displayed in cells A5, B5, and C1 through C4.

For the data and formulas shown in the spreadsheet below, determine and fill in the values that will be calculated and displayed, using the spreadsheet to the right.

Follow the directions for the spreadsheets.

Enter the text and numbers shown in the following spreadsheet:

Enter the appropriate formulas in cells D2 through D5 and other modifcations so that your completed spreadsheet looks as follows:

1.2 TWO ESSENTIAL SKILLS – HIGHLIGHTING AND COPYING

Once data is entered into a cell, the processing of the data can begin. In fact, most of this text is concerned with the Excel skills needed for this processing, such as finding the sum and/or average of the data within a range of cells, preparing the data so that it appears correctly when displayed or printed, or calculating profit and loss statements. These two skills are (1) highlighting a range of cells, such as a column of data whose sum is needed, and (2) copying one or more cells. How these are accomplished is described in this section.

Selecting Cells by Highlighting

A large portion of spreadsheet processing typically requires using a group of related cells. For example, the sum of a column of data may be needed, or perhaps the average of grades in a row, where each row corresponding to a student’s test grades may be required to determine a final grade. A commonly used technique for specifying a range of cells is to highlight the cells within the range.2 While the cells are highlighted, they form a unit that can then be processed by a formula or configured so that all individual cell values in the group have the same format when displayed.

Note that we are not referring to highlighting a cell’s contents by changing the color of the cell’s data. What is being highlighted is a set of one or more adjacent (that is, touching) cells that designates them as a single unit while they are highlighted. In Excel, a set of adjacent cells is referred to as a range of cells, or cell range, for short.

2 A second method is to list the top left-most cell address in the range, followed by a colon, followed by the lowest right-most cell address.

Figure 1.9 shows a number of highlighted cell ranges. The underlying criterion for each range is that every cell in the range is adjacent to, that is, touches, at least one other cell in the range. Also shown under each range is how the range is designated using cell addresses. You will note that cell ranges can be horizontal, vertical, or square/rectangular in shape.

Figure 1.9

Examples of cell ranges

To highlight a range of cells, you must first select a single cell at the top or bottom or corner cell in the desired range. For example, in Figure 1.10, the cursor has been moved to a selected starting cell, which in this case is B4. Notice the shape of the cursor in the cell.

Figure 1.10

Selecting the first cell in a range of cells

Once you have selected the first cell in the desired range, you can now define the desired group of cells by doing one of the following:

Press and hold the left mouse button down, starting from the first cell, and move the mouse so that the cursor points to the desired last cell in the range. Then release the left mouse button.

Click and then release the left mouse button on the first cell in the desired range. Then hold the Shift key down and click and release the left mouse button on the last cell in the desired range.

Click and release the left mouse button on the first cell in the desired range. Then hold the Shift key down and use the keyboard arrow keys (← ↑ ↓ →) to go to the last cell in the desired range, and then release the Shift key.

Figure 1.11 shows how the selected cells look using the first method, where the left mouse button has been pressed and held down as the mouse cursor is moved from its initial position in cell B4 to cell B11, and then the left mouse button is released. The cells, as shown in Figure 1.11, become highlighted as they are included in the range. This same highlighting would occur if any of the other two methods are used.

Figure 1.11

Completing the range selection

Copying Cell Contents

The contents of a single cell are copied by first moving the cursor to the lower right-hand corner of the selected cell, as shown in Figure 1.12.

Figure 1.12

Locating the copy rectangle

When the cursor appears as a cross-hair (+), as shown in Figure 1.13, the selected cell is ready to be copied. This is known as the fill handle.

Figure 1.13

Placing the cursor on the fill handle (the cursor changes to a cross-hair)

Once the cursor has changed to a cross-hair (fill handle), press and hold the left mouse button while you drag the cursor over the cells where you want the copy to occur. As you do so, each cell where the copy will take place becomes highlighted, as shown in Figure 1.14. Here, the contents of cell B3 are being copied to cells C3 through E3. The copy takes place when the left mouse button is released.

Note that the copy could just as easily been made up or down column B, rather than across the third row (row 3). The only restriction on this method is that the copy is always made to adjacent (touching cells), which is usually what is required. If a copy to non-adjacent cells is needed, standard cut-and-paste techniques should be used.

Figure 1.14

Copying cell B3 to cells C3, D3, and E3

Adaptive Pattern Matching

When copying dates, text, and numbers (and more importantly formulas, as described in Section 2.1), Excel tries to determine if a pattern exists. Based on any detected pattern, Excel places the next value in the pattern into the copied cell. This is sometimes referred to as completing a series or sequence.

Typically, for formulas and text, (and other commonly used sequences) such as days-of-the-week and months, Excel only requires one cell to determine the pattern; for dates and numbers (and less frequently used sequences), two adjacent cells are typically needed. For example, consider the spreadsheet segment shown in Figure 1.15a.

Figure 1.15a

The original cell contents

Now, if cell A1 is copied to cells B1 through cell F1, the spreadsheet segment will appear as shown in Figure 1.15b. Note, as the contents of cell A1 were formatted to be centered in the cell, the copied cells retain this format. The reason is that the Copy feature normally brings over not just the data, but also the cell format from the source to the destination.

Figure 1.15b

The cell contents after cell A1 is copied

Give It a Try

In Figure 1.16, write in the contents that you think Excel will display when cell A1 is copied from cell B1 through F1. If you can, check your answer by using an actual Excel spreadsheet.

Figure 1.16

Write the contents that will be displayed when cell A1 is copied from cell B1 through F1

To detect a pattern in numbers and dates, Excel requires that two adjacent cells be highlighted and then copied. For example, consider Figure 1.17a, where a number pattern appears in cells A2 and A3. If these two cells are highlighted and then copied, this spreadsheet segment will appear as shown in Figure 1.17b.

Figure 1.17a

Original data

Figure 1.17b

Copied cell results

EXERCISES 1.2

For each of the following spreadsheets, determine and fill in the text that will displayed if the text in cell A1 is copied to the right.

For each of the following spreadsheets, determine and fill in the text that will displayed if the text in cells A1 and A2 are first highlighted and then copied down.

In Figure 1.18, write in the contents that will be displayed when cells A1 through B1 are highlighted and then copied to cells C1 through H1.

Figure 1.18

Write your answer

In Figure 1.19, write in the contents that will be displayed when cells A1 through B1 are highlighted and then copied to cells C1 through H1.

Figure 1.19

Write your answer

1.3 OPENING, SAVING, RETRIEVING, AND PRINTING SPREADSHEETS

Opening, saving, and printing your spreadsheets is accomplished using the Quick Access Toolbar, which is located at the top of the Excel screen as shown in Figure 1.20, and provided in detail on Figure 1.21. These tasks can also be done using the commands located in the Excel ribbon under the File tab.

Figure 1.20

Locations of the Home tab, Tabs toolbar, and Quick Access Toolbar

The options on the Quick Access Toolbar, shown in Figure 1.21, are used to open either a new or existing spreadsheet, to save the spreadsheet you are currently working on, and to print parts or all of the current spreadsheet. Additionally, it provides an option for customizing the Quick Access Toolbar.

Figure 1.21

The Quick Access Toolbar

Opening a New or Existing Spreadsheet

As shown on Figure 1.22, to open a new, blank spreadsheet, select the New option from the File tab’s submenu. This causes the window pane to the right of the menu to appear. Within this pane, you can select a new blank workbook to be displayed, select one of the pre-formatted spreadsheets, or search for an online spreadsheet template. It should be pointed out that while the main focus of this book is on creating your own spreadsheets, Excel does offer the means to help some users get started quickly by providing pre-formatted, partially completed templates for spreadsheets.

Figure 1.22

Opening a new spreadsheet

To open a previously saved spreadsheet, click on the File tab’s Open option, as shown in Figure 1.23. Doing this automatically brings up the window pane shown to the right of the menu. Here, a list of recently saved spreadsheets is provided. You can either select one of the displayed spreadsheets or click on the Browse option at the bottom of the pane and enter the location and filename of the desired saved spreadsheet.

Figure 1.23

Choosing the Open option opens up another list of options

Saving a Spreadsheet

It is important to save your work and remember the location where it is saved. Frequently, Excel will provide you with a list of your most recently saved files; however, this is not always the case if you are using a public computer. Always make a note of the directory and name of your saved spreadsheets when you save them.

There are a number of methods to save a spreadsheet. The easiest method to resave a previously saved spreadsheet is to click on the Quick Access Toolbar’s Save option, which is shown on Figure 1.21.

The first time you save a spreadsheet, however, you should use the File tab’s Save As option shown in Figure 1.24 or the spreadsheet will be saved using a default name provided by Excel.3