Daily Dose of Excel

OK, time for some non Excel 2007 stuff…

Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.

What the poster wanted is to go from:

To:

Read on here.

Regards,

Jan Karel Pieterse

JKP Application Development Services

Founding member of:
Professional Office Developers Association

Sometimes I need to analyse data (row-by-row) in a worksheet with a lot of columns. Sometimes I then want to see information from a cell in a column way off to the far right together with a couple of columns on the left side of the sheet. Of course you can split the window, freeze the panes, but this isn’t always sufficient.

I devised a tiny utility that shows a modeless window (only works in XL2000 and up) which displays the content of a cell in a set column on the same row:

The form can be resized to get it out of the way and accomodate for the amount of information you want to see.

Find it here.

Otherwise know as Freeze Panes. This feature, under the Windows menu, allows you to lock certain columns, rows, or both in place so that they never scroll out of view.

Start by locating the split bars. I don’t their technical name, but it was either split bars or doohickies. I went with the former. The horizontal split bar is just above the arrow on the vertical scroll bar. When you hover over it, the cursor changes to a two-headed arrow.

The vertical split bar is just to right of the horizontal split bar and has the same function. These bars can be moved to split the window into two or four panes.

You can scroll inside any of these panes, which can be useful, but is more often just confusing. To lock those panes into place so that their contents can’t be scrolled, use the Freeze Panes option.

The thick split pane bars now become thin freeze pane bars. You can click inside those panes, you just can’t scroll around in there and get lost. To remove, go back to the Windows menu and choose Unfreeze panes.

Custom Views under the View menu is a quick way to hide/show unwanted columns (among other things). There are two situation in which I find Custom Views useful: First, if there are certain columns that I want shown, but not printed; and second, if there are columns/rows that will be hidden in the final product, but I want to be able to quickly hide/unhide them during development.

Here’s a simple example: Create a custom view that shows everything and call it Develop.

Next, hide some columns and define another custom view, call it Final

You can use View > Custom Views to quickly toggle between the views. You can also add the Custom View Toolbar dropdown to one of your toolbars.

Bernie Deitrick showed a cool way to insert rows into a spreadsheet in answer to a newsgroup post this week.

First, create a new column A.

Next, fill numbers down for each row. Then copy those numbers to the blank rows below.

Sort on Column A

Then you can delete Column A and your done. Obviously it would be quicker to just insert rows when you only have 10 rows. But if there were more rows, this method would be quicker. I would have probably written a macro to do it, but I like these clever user-interface methods. Particularly if you just need to do it one time.