Microsoft Office Tutorials and References
In Depth Information
Exhibit 4.4 Data entry with data validation tool
3. We have data located in an MS Word document, either as a table or in the form
of structured text, that we would like to import and duplicate in a worksheet.
There are many other situations that could require manipulation of data, but
these cover some of the most commonly encountered. Conversion of data is a very
common activity in data preparation.
Let’s begin with data that is not physically oriented as we would like; that is, the
inversion of records and ﬁelds. Among the hundreds of cell functions in Excel is the
Transpose function. It is used to transpose rows and columns of a table. The use
of this cell formula is relatively simple, but does require one small difﬁculty—entry
of the transposed data as an Array . Arrays are used by Excel to return multiple
calculations. It is a convenient way to automate many calculations with a single
formula and arrays are used in many situations. We will ﬁnd other important uses
of arrays in future chapters. The difference in an array formula and standard cell
formulas is that the entry of the formula in a cell requires the keystrokes Ctrl-Shift-
Enter (simultaneous key strokes), as opposed to simply keying of the Enter key.
The steps in the transposition process are quite simple and are shown in