Access Hacks

Hack #60. Use Excel to Reorient Access Data

Use Excel's Paste Special Transpose feature to turn data on its ear.

Here's an easy way to change columns to rows (or rows to columns; I guess it all depends on how you look at it). Figure 7-6 shows a table filled with some data. The table contains 8 fields and 100 rows of data.

Figure 7-6. Eight columns of data in a table

Perhaps your user wants to view the data so that each person's record is displayed vertically, which isn't an uncommon request with Excel users. This hack shows how to do just that: put this data in Excel, but turn it sideways.

First, select all the data in the Access table; then, pop over to an open Excel workbook. Find an empty worksheet, and paste the data. Note that for this example, I have purposely pasted the data in row 12. You will see why in a moment. Figure 7-7 shows how the data landed in Excel.

Figure 7-7. Access data pasted in Excel

Upon being pasted, the data is in a selected state. That's great! Just leave it as is, but if you lose the selection, just select it again. The next step is to copy the data, by either selecting Edit → Copy or pressing Ctrl-C. Copying the data is a necessary step. It might seem that the data is already on the clipboard. It is, but not in the way we need; therefore, the extra copy ...