Natural Blogarithms Ramblings of a Christian Mathematician and Bioinformaticist

Helpful Tips in Excel

Both Microsoft Access and Microsoft Excel became heavily used tools in my bioinformatics research.  Scripting came in handy on occasion to automate tasks but my lask of "skills" in that arena left me relying heavily these applications.  Within the last two months, I couldn't count how many tables and queries I've created (point-and-click style) in Access, how many times I used VLOOKUP and MATCH in Excel.  I've just recently discovered the many, many joys of Pivot Tables (amazing!).  Oh and by the way, I'm flat in love with Excel 2007.

Here were a couple of new tips I stumbled across today:

Convert one column into two
Suppose you have a list of full names in a column, and you'd like to separate the first names from the last names. In Excel 2003, select the column and click Data*Text to Columns. In Excel 2007, click the Data tab, and select the Text to Columns button. In both versions, choose Delimited (unless all the entries are the same length, in which case you can select Fixed width), click Next, and check Space (or whichever option applies; see the screen below). You can leave "Treat consecutive delimiters as one" checked. Click Next again to view data-formatting options, and then Finish.

Select Space (or whatever separates the items in the column you wish to split) to create two columns where there once was one.

At this point, you may want to change the order of the columns. To do so, simply select the column header, right-click the selection, and choose Cut. Now click the header of the blank column you want to place the cut cells in, right-click, and select Insert Cut Cells.

Paste formatting with one keystroke
If you'd like several disconnected cells to share a format, such as bold text and a background color, it can be a hassle to select each cell one at a time, open its cell-format dialog box, and make the changes you want. Instead, reformat one of the cells, and then select all of the others by pressing Ctrl, and clicking them one by one. Once they're all highlighted, press F4 to apply the formatting to all of them at once.

http://www.cnet.com/8301-13880_1-9836865-68.html?part=rss