I had a huge response to my last column about Excel Tricks, so today I have a couple of additional tricks for you to try.
Pretend that you create a spreadsheet for your boss. It contains your company’s weekly sales figures. The dates are located across the top of the sheet (top row), the sold items down the left side (first column) and the totals at the bottom and right side where needed.
The boss reviews it and tells you that the info is accurate and looks great…with one exception. He/she wants the sold items to be in the top row and the dates in the first column, i.e., the exact opposites of the current layout of these two components.
I am pretty sure that many of you Excel users have been here before. You realize that you will basically need to recreate the data from scratch and it will take an hour or more to do so. However, once you know how to use “Transposition” in Excel you will be done in about eight seconds or less.
Transposing basically flip-flops a table in Excel.
To make Transpose work its magic, first select (highlight) the cells you want to transpose…all of them including all cells, rows, columns, formulas, labels, etc. Next, copy the cells you want to transpose. You can copy them in the way you usually copy data. I figure you are an advanced user so I won’t elaborate on multiple ways to copy the data.
Next, left click the cell at the location you want the top left cell to be located in the new flipped table. Make sure that no other data is in that area including any of the original cells you are transposing. If this isn’t exactly where you want its final location to be, don’t worry you can move it later.
From the new location, go to the Home tab, Clipboard group, Paste button, Paste Special and choose Transpose. You may also right click in the new empty location and choose Paste Special and then choose Transpose. That is it!
One note; all of your previous formatting (if any) will be copied into the new table. You may have to fix some of the column widths, label colors, font sizes, etc. I suggest you use the “Clear Format” feature in Excel, which you may not know about…yet.
To clear the formatting, first select the cells you want to clear. In Excel 2007 or above, from the Home tab look in the Editing group look for an eraser-like button that may be labeled “Clear”. Click the button and choose “Clear Formats”. Then you may reformat the new table in the style that looks best to you. In 2003 or before, use the Edit menu, point to Clear, and then repeat the choices above.
The other choices available under the eraser are: “Clear All” which totally empties the selected cell(s), “Clear Contents” which deletes the contents of the cells but leaves the formatting and “Clear Comments” which we will take a look at next week.
