ChemFinder for Excel: making spreadsheets chemically smart
By Jordan ShatsoffCambridgeSoft Corp.
Table of Contents
Using ChemFinder for Excel
Importing data
Displaying structures
Drawing structures
Convert name to molecule
Molecular formula
Percent composition
Filtering a list of structures
Now what?
As chemists we often want to maintain information about groups of compounds we're working on. Most of us are comfortable using Microsoft Excel spreadsheets to organize information, but Excel has one drawback for chemists: it doesn't know chemistry and it isn't easy to show structures along with your data. CambridgeSoft's new ChemFinder for Excel adds chemical intelligence to spreadsheets, so chemists can use the spreadsheet skills they have developed, while still having the ability to view and manipulate chemical structures and properties.
ChemFinder for Excel has a nice set of chemistry related functions that include percent composition, chemical formula, molecular weight, number of particular atoms in a compound, SMILES, and a host of ChemProp estimations including Boiling Point and LogP. When using ChemFinder for Excel you will quickly understand that it is more than just a couple of functions related to chemistry. The Excel Add-In has the ability to display structures, convert a name to a structure, filter tables of structures, and use Excel's standard search and sort features. Additionally, you have the ability to import SDFiles (the industry standard file type for exchanging chemical information), import ChemFinder hit lists from both ChemInfo and personal databases, and import hit lists from web sites such as ChemACX.Com.
Using ChemFinder for Excel
After installing ChemFinder for Excel, you'll see a new menu added to Excel between the Data and Window menus. A toolbar is also added. These are your two main interfaces when manipulating information in the program. In the following tutorial I generally refer to items in the menu, because it is easier than describing the icon you need for the same tasks.
Importing data
The two main methods of getting data into ChemFinder for Excel are entering the data by hand (this includes cutting and pasting columns of names from another document) or importing either an SDFile or ChemFinder database. Whichever method you choose, you first need to create a new ChemFinder worksheet. Go to the ChemFinder menu and choose New ChemFinder Worksheet. To import data, go to the ChemFinder menu and Import Table. You then select the file type to be either a ChemFinder Database (*.cfw) or an SDFile (*.sdf). Choose your file and hit Open.
Displaying structures
Depending on where your data came from, you will obviously have a different number of structures. If you imported your data, there will be a list of structures simply labeled Structure1, Structure2, etc. Select this first column. In the ChemFinder menu, choose Show Picture. This will take a variable amount of time depending on how long your list is. You can also select just one cell and only show that picture if you don't need to see all of the structures.
Drawing structures
When using ChemFinder for Excel, you may want to add molecules to your list or create a list by drawing structures. This is easy: double click on an empty cell in the first column of your ChemFinder worksheet. When asked if you want to add a molecule to the worksheet, click Yes. This will launch ChemDraw. Draw your molecule and close ChemDraw. Then select Show Picture for that cell.
Convert name to molecule
While you can add a structure to your ChemFinder worksheet by drawing it, it may be easier to simply type the name and convert it to a structure. In the first column of a ChemFinder worksheet, type in your chemical name. Then select that cell and in the ChemFinder menu choose Convert Name to Molecule. Then all you need to do is select Show Picture. This results in a structure field that can be used for calculations the same as the structures you import.
Molecular formula
If you imported a ChemFinder database, you may have imported a molecular formula field. But if you don't have a molecular formula field in your database or SDFile, it's easy to use ChemFinder for Excel functions, just like using any other Excel function.
In the cell next to the first structure you will insert a function. ChemFinder calculation functions work just like any other Excel function. Go to the Insert menu and choose Function. In the Function category you will see ChemFinder Functions. Click on that category and you will see the ChemFinder for Excel functions available to you. Choose Chem.Formula. The only required parameter is the structure you are interested in. Type in the cell position, or click on the cell where the structure resides. This will produce the molecular formula for that structure. You can drag it down just like any other Excel formula, and you will have the molecular formula for all of your structures.
Percent composition
In most ChemFinder for Excel functions only the cell containing structures is required. The two main exceptions to this are percent composition and the counting of a particular atom. Both of these require you to also name the atom you are interested in. When using the Chem.Composition function you will need to enter the location of the structure and also the element type. For example, you can see the percent composition of Carbon in your structure by typing C for the element type.
Filtering a list of structures
People use ChemFinder is to work easily with structures and to be able to search through these structures. With ChemFinder for Excel, you can filter a table by structure or substructure. In the ChemFinder menu go to Filter Table. A search query form appears. To draw your structure, simply double click in the Search Query field and ChemDraw will be launched. Draw your structure and close ChemDraw. Then choose whether you want to do an exact structure search or a substructure search. You can also choose whether you want to see the matches (Include Hits) or hide the matches (Exclude Hits).
After you have filtered based on a structure, you can then further filter by properties you may have calculated. For example, you could select only those structures with a minimum percent composition of Oxygen or some other element.
Now what?
While I have discussed only some of the features and functions of ChemFinder for Excel, the types of manipulations and calculations you can perform are limited only by your imagination. You may decide to leave your structures in Excel and update your spreadsheet with new chemicals you work with. You can also export your spreadsheet to an SDFile. This will allow you to import your data into a ChemFinder database with all of your additional columns of data and calculations.
ChemFinder for Excel is a familiar working environment where you can create and customize your worksheets to perform numerous chemical calculations, while having structures readily available and maintaining the ability to search and filter through your results.
For sales or product information about CambridgeSoft products, including pricing, call 800-315-7300, or 617-588-9300 outside the United States.