Administrative Sparkle

Have you ever gone to great lengths of creating a worksheet with all the data and links and then wondered if the layout would have been better suited with the information in the columns having been placed in the rows?

How do you determine what information you want displayed in the columns and what information you want in the rows when first starting out? Many times the spreadsheet starts out pretty simple. You have the basic idea of the information that you want, but are now expanding on the information you want to include. So the spreadsheet becomes much more complex in the information you want to include, view, print, and understand.

The easiest way to design a spreadsheet for viewability when working within the spreadsheet is to have the bulk of your information running down the rows. To build your worksheet shallow and wide would work for a landscape orientation, but you will find yourself navigating away from your titles in the leftmost column (unless you freeze your panes). Nevertheless, you will find navigating your spreadsheet much easier scrolling up and down, rather than left to right. You will also find printing information for page breaks easier when the data runs deep instead of wide.

You don’t have to redo the whole spreadsheet just to see if the spreadsheet works better had the information you wanted listing down the rows had worked better going across in columns. What assistant hasn’t lost her mind because her manager wants the entire worksheet formatted completely opposite? Or, how about the option to present the spreadsheet to your manager with the alternative option. Afterall, it’s difficult to see how the information is best presented until you have the finished product.

Excel 2007 has included a tool that transposes your data without losing the integrity of the spreadsheet links and formulas.

Instructions on How To Transpose Excel Data

1. Select all the titles and data that you want included.
2. With the information selected, on the Home bar, in the Clipboard group, click on Copy (or Ctrl-C).
3. On the worksheet, select the first cell of the destination rows and columns where you want to rearrange the copied data (I would suggest a new worksheet in the tabs).
4. On the Home bar, click the arrow below Paste to view the multiple options you can do with Paste, and click on Transpose.

That is all there is to it! You might need to adjust cell width and minor editing to the format, but the bulk of the work is done. Also, you can view a tutorial on the Microsoft website that shows you step-by-step how it is done right here.

If you find this helpful, please share. If you have stories or tips and tricks, leave a comment for others to learn. If you like the information you get here, subscribe. If you would like to make a suggestion on something you would like to see, feel free to drop me an email.