Microsoft Office Tutorials and References
In Depth Information
Now, let us reacquaint ourselves with data we ﬁrst presented in Chap. 2. Table 4.1
contains this quantitative and qualitative data and it will serve as a basis for some of
our examples and explanations. We will concentrate our efforts on the three quali-
tative records in the data table: Account (account type), Date Rcvd. (date received),
and Comment . Recall that the data is structured as 39 records with 7 data ﬁelds.
One of the 7 data ﬁelds is an identifying number ( Item ) associated with each record
that can also be considered categorical data, since it merely identiﬁes that record’s
chronological position in the data table. Note that there is also a date, Date Rcvd .,
that provides similar information, but in different form. We will see later that both
these ﬁelds serve useful purposes.
Next, we will consider how we can reorganize data into formats that enhance
understanding and facilitate preparation for analysis. The creators of Excel have
provided a number of extremely practical tools: Sort, Filter, Form, Validation , and
PivotTable/Chart . (We will discuss PivotTable/Chart in Chap. 5). Besides these
tools, we will also use cell functions to prepare data for graphical presentation.
In the forthcoming section, we concentrate of data entry and manipulation. Later
sections will demonstrate the sorting and ﬁltering capabilities of Excel, which are
some of the most powerful utilities in Excel’s suite of tools.
4.3 Data Entry and Manipulation
Just as was demonstrated with quantitative data, it is wise to begin your analytical
journey with a thorough visual examination of qualitative data before you begin the
process of formal analysis. It also may be necessary to manipulate the data to per-
mit clearer understanding. This section describes how clarity can be achieved with
Excel’s data manipulation tools. Additionally, we examine a number of techniques
for secure and reliable data entry and acquisition. After all, data that is incorrectly
recorded will most likely lead to analytical results that are incorrect; or to repeat an
old saw—garbage in, garbage out!
4.3.1 Tools for Data Entry and Accuracy
We begin with the process of acquiring data; that is, the process of taking data from
some outside source and transferring it to an Excel worksheet. Excel has two very
useful tools, Fo rm and Validation , that help the user enter data accurately. Data entry
is often tedious and uninteresting, and as such, it can lead to entry errors. If we are
going to enter a relatively large amount of data, then these tools can be of great
beneﬁt. An alternative to data entry is to import data that may have been stored
in software other than Excel—a database, a text ﬁle, etc. Of course, this does not
eliminate the need to thoroughly examine the data for errors, speciﬁcally, someone
else’s recording errors. Let us begin by examining the Fo rm tool. This tool permits
a highly structured and error proof method of data entry. The Fo rm tool is one that
is not shown in a ribbon and therefore must be added to the Quick Access toolbar