Flip-flop that worksheet data without any fuss

Here's a practical Excel skill that could save your students hours of tedious labor and frustration.

Sooner or later, most Excel users will set up a worksheet and enter tons of data, only to realize that the structure needs to be reversed: Rows should be columns and columns should be rows. For users who don’t know about Excel’s built-in feature for transposing data, this is a catastrophic realization — but you can teach your Excel students the simple, painless way to restructure their data, and they’ll remember you fondly for the rest of their professional lives. Here’s a simple example you can use to demonstrate how the feature works.

The scenario
Let’s suppose you’ve set up a range of sales data with column headings for the months of July through December and row headings identifying four sales regions (North, South, East, and West).

We’ll use this range of data to demo Excel’s feature for reversing rows and columns.

  1. Start by selecting all the cells that contain headings and data, then click the Copy button on the Standard toolbar.
  2. Click in a cell that appears in the upper-left corner of the area where you want your transposed data to appear. Be sure to pick a blank area of your worksheet. Excel won’t let you paste the copied data in an area that already contains entries.
  3. Choose Paste Special from the Edit menu, select the Transpose check box in the bottom-right corner of the Paste Special dialog box, and click OK. Excel will paste the copied data and transpose it for you. That is, the former column headings (months) will become row headings and row headings (regions) will become column headings.

Using the Transpose option, we’ve reversed the structure of the data.

  1. To complete the process, you can remove the original data from the worksheet so that you’re left with just the correctly arranged columns and rows. To do this, simply highlight the cells from your original range and choose Delete from the Edit menu. When Excel presents the Delete dialog box, select the option that’s appropriate for your data (Shift Cells Up, Entire Row, and so on). Click OK, and Excel will remove the cells containing your original data.

What about Word data?
If you’re teaching an Office or Word class, you can show your students how to put this feature to work if their data appears in a Word table. Since Word offers no tools for transposing rows and columns, the simplest way to restructure a table is to pop it into Excel, use the Transpose option, then bring the data back to the Word document. We’ll illustrate this technique using the same sales data, only this time in a Word table.

Transposing columns and rows in Word is easy if you let Excel handle the job.

  1. Select the entire table in the Word document ([Alt]-double-click in any cell), and click the Cut button on the Standard toolbar.
  2. Switch to Excel, click in a blank area of the worksheet, and click the Paste button.
  3. With the pasted data still selected, click the Copy button.
  4. Click in a blank area of the worksheet, choose Paste Special from the Edit menu, select Transpose, and click OK.
  5. After you’ve transposed the data, make sure the column widths are wide enough to display the contents of each cell. Then select all the data and click the Cut button.
  6. Switch back to Word and click Paste.

This trick — in addition to correcting the table — will get your students thinking about interoperability. Even if they’re exclusively Word users, they’ve now seen that sometimes the way to handle a document problem is to use another application.
Students are understandably enthusiastic when trainers show them efficient ways to solve problems they’ve experienced firsthand — yet a lot of standard courseware focuses only on general features instead of getting down to the nitty-gritty. If you have some real-world techniques and solutions you’ve taught successfully, drop us an e-mail message and let us know.


Jody Gilbert has been writing and editing technical articles for the past 25 years. She was part of the team that launched TechRepublic and is now senior editor for Tech Pro Research.


Editor's Picks