Heat maps are a useful way to represent certain types of data; the data are colored by coloring according to the values in them, (e.g. red for high values, yellow for medium and green for low values), providing a powerful visual representation of a data set.
This allows you to quickly see results from DNA microarrays and all sort of other things. But, unfortunately, Excel’s abilities for handling a heat map like this are limited to say the least.
But the next item in my toolbox, ASAP Utilities, fixes all of that. ASAP utilities, available as a free downloadable trial, allows you to do many new tricks with your data that you couldn’t otherwise do using the standard set of Excel tools that come in the shrinkwrapped Excel package.
For example, after having color-coded a set of samples in your spreadsheet, you discover that you cannot search for or sort any of the samples based on your color designations.
Instead, you must somehow invent some other labeling scheme, such as numerical coding, and recode the rows by adding each number to an adjacent cell. Only then can you sort the rows by the numbers in the adjacent column. What a pain!
This is just one of those essential features that was annoyingly neglected in Excel. But not any more! Now, using the ASAP add-in, you can easily sort your data by cell color! Here’s how:
Simply make a selection of a range of cells to be sorted, click on the newly added “ASAP Utilities” drop-down menu incthe menu bar, select 5.Range > 2.Advanced sorting… and thencchoose to “sort by” Cell Color. That’s it! End of story.
Another useful feature of ASAP is that it allows you to import various file types directly into and export selected portions of your spreadsheet without having to do the “file, save as, rename, edit, save” shuffle.
In fact, you can even create a snapshot of your selected cells by exporting them directly as a *.gif or *.jpg image file as I’ve done here for my colored heatmap.
To do this, select your cells, click on “ASAP Utilities”, pick 17.Export > 1.Export selection or active sheet as new file. Click on the tab labeled “Export chart or range as image”. Click the radio button for your choice of format (Choosy mothers choose GIF), and Viola!
No more mouse-mouse-mouse to get to that buried directory either, since you can save to the same directory automatically.
And the final cool thing you can do with ASAP is launch the calculator or notepad from within Excel.
ASAP is just one entry in a list of some of the best MS Excel add-ins available complied by the editors of PC Magazine. The this list includes a set of 300+ drop-down menu items that were (intentionally) left out when the Excel developers finalized their requirements.
Well, Bon Appetit!… or whatever the equivalent is for munching this tidbit of fresh bites.
If you know of any other nice add-ins useful for biologists, please let us know. We’re always hungry!
Over the years, I have noticed that laboratory environments are just as fragile and sensitive as the experiments performed within. If permitted, the lab can deteriorate into a chaotic mess within only a few days. That is why it is crucial to establish an organizational system in the lab. Here are a few of my…
After ten years of postdoctoral research there is one important piece of advice I would give to anyone embarking on a research career: Spend as much time managing your data as you do generating it Take time at the beginning of each project to organize how you will record what you are doing day-to-day. The…
The completion of the Human Genome Project in 2003 ushered in a new era of rapid, affordable, and accurate genome analysis—called Next Generation Sequencing (NGS). NGS builds upon “first generation sequencing” technologies to yield accurate and cost-effective sequencing results. Fred Sanger sequenced the first whole DNA genome, the virus phage ?X174, in 1977. In that…
Even in the most basic applications, fluorescence microscopy can be a very powerful technique. Simply put, the ability to actually see the biology you are interested in cannot be matched in directness. Often, the aim of fluorescence microscopy is to observe the effect of an experimental manipulation. Ultimately, you would like to know that the…
Everyone loves a bargain! These days, more and more labs need to stretch every dollar as far as it will go. So how do you keep your lab well-supplied on a tight budget? To get a better deal on products you might choose to shop around, buy in bulk or haggle with vendors. However, if…
The importance of epigenetics in biology is increasingly acknowledged (if you’re not convinced yet, read my crash course). One commonly studied epigenetic mark is CpG methylation: cytosines that are directly followed by a guanine nucleotide (indicated by CpG), can be methylated, unlike non-CpG Cs. Since attachment of a methyl group to a cytosine can affect…
10 Things Every Molecular Biologist Should Know
The eBook with top tips from our Researcher community.
