John C. Flackett

A weird thing happened today with what should have been a simple exercise in data manipulation in Excel. Like most people I’m still trying to get to grips with where everything is in Office 2007 (I’ve lost count of the times I’ve heard:
“ARGH! the bloody ribbon! Where the hell is the <insert tool name>?”

Well, today I knew where the thing I wanted was…it just didn’t work! How can something as simple as transposing data (swapping columns for rows and vice versa) be so difficult?

The Problem

Transposing data in Excel is easily performed (all versions) by selecting all the cells containing the data you want to transpose, copying the cells (usually using ‘Ctrl+C‘), selecting the start cell where you want to copy the data to, and selecting the ‘transpose‘ checkbox from the ‘Edit?Paste Special...‘ dialog box (see Figure 1).

Figure 1: Excel 'Paste Special...' window

There are a couple of caveats you should be aware of:

1. You have to ‘copy‘ rather than ‘cut‘ the data you wish to transpose and
2. The cells you are copying the transposed data to cannot overlap the cells with the original data in.

However, when I tried this on a set of data, Excel 2007 responded with the error:
"The Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste
- Select a rectangle that's the same size and shape, and then paste."
Seeing that I only had a single cell selected, the advice was pretty poor!

The Solution

It seems that Excel 2007 is not so happy performing certain tasks when the worksheet is in “compatibility mode”. In other words, Excel would not transpose the data because the workbook (file) I was using had been originally created in a previous version of Excel (Excel 2003 to be precise – I had created it on a secure server and was manipulating it on my local machine).

The solution then was to save the workbook in 2007 format (as an .xlsx file), and then perform the transpose on the data. Why Excel didn’t provide such information in the error message is beyond me, especially as such compatibility issues cause a serious amount of angst.