Transposing Rows and
Columns
Excel is not a database management system. It is not, for example, designed to manage the relationship between parent and child records. To extend the example in the previous section, a PO is a parent record and invoices are child records because each invoice -the child record -belongs to a PO -the parent record.
Still, Excel has some rudimentary database features, among them the AutoFilter and the Advanced Filter. These two filters in particular depend on a particular orientation of your data, called
lists
. In Excel, a list has these characteristics:
-
Each record (each person, each product, each invoice) occupies a different row.
-
Each variable (for example,
name
and address, or model and price, or invoice date and amount) occupies a different column.
-
Each column begins with the name of the variable that's located in that column.
Figure 1.1 is an example of a list. It has different records -invoices -in different rows, different
variables
-dates, amounts, and so on -in different columns, and each column is headed by the name of that column's variable.
Changing the Orientation with Paste Special
Many of Excel's tools work best, and some work
only
, when the data you point them at are arranged in lists. Tools such as pivot tables and data filters will not work properly with any other arrangement.
From time to time, a
user
might decide to enter data using a different layout. When this happens, it's often a 90-degree rotation from the normal list arrangement; that is, he puts different records in different columns and different variables in different rows. Figure 1.6 shows an example.
This arrangement is unwise for several reasons, but the strongest is that the user will run out of columns long before he runs out of rows. An Excel worksheet has 256 columns only, and there's no way to add more. But it has 65,536 rows. (No, you can't add more rows either, but 65,536 is pretty roomy. 65,536 is 2 to the 16th power, by the way.)
There are many situations in which you would have more than 256 records. For example, a company of any appreciable
size
has more than 256 invoices to deal with. But it is rare to have as many as 256 variables that describe the records. So, the dimensions of the worksheet itself argue for using the list structure.
Nevertheless, you frequently encounter worksheets laid out as in Figure 1.6. If only a few formulas are based on the data as it's shown in the figure, there are a couple of easy fixes:
-
Select the entire data range.
-
Choose
Copy
from the
Edit
menu.
-
Select a
cell
that has empty columns to its right.
-
Choose
Paste Special
from the
Edit
menu, fill the
Transpose
check box, and click OK.
-
Repair the range addresses used in the formulas.
CAUTION
Be careful when transposing data using Paste Special. In some cases, you can transpose formulas so that they depend on
cells
that don't exist. Chapter 2 discusses this problem in detail.
Changing the Orientation with the TRANSPOSE Function
Sometimes the user has structured the worksheet as shown in Figure 1.6 because it's easier to enter the data that way -perhaps the data comes in a hardcopy format with records in columns and variables in rows. Then it's much easier on the person entering the data to follow the arrangement of the hard copy.
To preserve the data entry format and yet reconfigure the data so that it forms a list, you could take these steps:
-
Count the number of rows and columns in the existing data range.
-
Select an entire range of blank cells. This new range should have as many columns as the original range has rows, and as many rows as the original range has columns.
-
In the Formula Bar, type
=TRANSPOSE(
followed by the address of the original range (for example, A1:Z5), and a closing parenthesis.
-
Array-enter the formula using the key combination Ctrl+Shift+Enter.
Now you have two ranges: One is laid out as in Figure 1.6, where more data can be entered as it becomes available, and the other appears as in Figure 1.7.
To accommodate more records as they're entered into the original range, just select more rows in the new range before you enter the TRANSPOSE formula. That way, as more records are entered in columns in the original range, they'll appear in new rows in the transposed range.
|