## Exchange Bioinformatics Data Between Excel and MATLAB

### Using Excel and MATLAB Together

If you have bioinformatics data in an Excel^{®} (2007 or newer) spreadsheet, use Spreadsheet Link™ to:

Connect Excel with the MATLAB

^{®}Workspace to exchange dataUse MATLAB and Bioinformatics Toolbox™ computational and visualization functions

### About the Example

**Note**

The following example assumes you have Spreadsheet Link software installed on your system.

The Excel file used in the following example contains data from DeRisi, J.L.,
Iyer, V.R., and Brown, P.O. (Oct. 24, 1997). Exploring the metabolic and genetic
control of gene expression on a genomic scale. Science
*278(5338)*, 680–686. PMID: 9381177. The data was filtered
using the steps described in Gene Expression Profile Analysis.

### Before Running the Example

If not already done, modify your system path to include the MATLAB root folder as described in the Spreadsheet Link documentation.

If not already done, enable the Spreadsheet Link Add-In as described in Add-In Setup (Spreadsheet Link).

Close MATLAB and Excel if they are open.

Start Excel. MATLAB and Spreadsheet Link software automatically start.

From Excel, open the following file provided with the Bioinformatics Toolbox software:

*matlabroot*\toolbox\bioinfo\biodemos\Filtered_Yeastdata.xlsm**Note**is the MATLAB root folder, which is where MATLAB software is installed on your system.`matlabroot`

In the Excel software, enable macros. Click the

**Developer**tab, and then select**Macro Security**from the Code group. If the**Developer**tab is not displayed on the Excel ribbon, consult Excel Help to display it. If you encounter the "Can't find project or library" error, you might need to update the references in the Visual Basic software. Open Visual Basic by clicking the**Developer**tab and selecting**Visual Basic**. Then select**Tools**>**References**>**SpreadsheetLink**. If the**MISSING: exclink2007.xlam**check box is selected, clear it.

### Running the Example for the Entire Data Set

In the provided Excel file, note that columns A through H contain data from DeRisi et al. Also note that cells J5, J6, J7, and J12 contain formulas using Spreadsheet Link functions

`MLPutMatrix`

and`MLEvalString`

.**Tip**To view a cell's formula, select the cell, and then view the formula in the formula bar at the top of the Excel window.

Execute the formulas in cells J5, J6, J7, and J12, by selecting the cell, pressing

**F2**, and then pressing**Enter**.Each of the first three cells contains a formula using the Spreadsheet Link function

`MLPutMatrix`

, which creates a MATLAB variable from the data in the spreadsheet. Cell J12 contains a formula using the Spreadsheet Link function`MLEvalString`

, which runs the Bioinformatics Toolbox`clustergram`

function using the three variables as input. For more information on adding formulas using Spreadsheet Link functions, see Create Diagonal Matrix Using Worksheet Cells (Spreadsheet Link).Note that cell J17 contains a formula using a macro function

`Clustergram`

, which was created in the Visual Basic^{®}Editor. Running this macro does the same as the formulas in cells J5, J6, J7, and J12. Optionally, view the`Clustergram`

macro function by clicking the**Developer**tab, and then clicking the Visual Basic button . (If the**Developer**tab is not on the Excel ribbon, consult Excel Help to display it.)For more information on creating macros using Visual Basic Editor, see Create Diagonal Matrix Using VBA Macro (Spreadsheet Link).

Execute the formula in cell J17 to analyze and visualize the data:

Select cell

**J17**.Press

**F2**.Press

**Enter**.

The macro function

`Clustergram`

runs creating three MATLAB variables (`data`

,`Genes`

, and`TimeSteps`

) and displaying a Clustergram window containing dendrograms and a heat map of the data.

### Editing Formulas to Run the Example on a Subset of the Data

Edit the formulas in cells J5 and J6 to analyze a subset of the data. Do this by editing the formulas’ cell ranges to include data for only the first 30 genes:

Select cell

**J5**, and then press**F2**to display the formula for editing. Change**H617**to**H33**, and then press**Enter**.Select cell

**J6**, then press**F2**to display the formula for editing. Change**A617**to**A33**, and then press**Enter**.

Run the formulas in cells J5, J6, J7, and J12 to analyze and visualize a subset of the data:

Select cell

**J5**, press**F2**, and then press**Enter**.Select cell

**J6**, press**F2**, and then press**Enter**.Select cell

**J7**, press**F2**, and then press**Enter**.Select cell

**J12**, press**F2**, and then press**Enter**.

### Using the Spreadsheet Link product to Interact With the Data in MATLAB

Use the MATLAB group on the right side of the **Home** tab to
interact with the data:

For example, create a variable in MATLAB containing a 3-by-7 matrix of the data, plot the data in a Figure window, and then add the plot to your spreadsheet:

Click-drag to select cells

**B5**through**H7**.From the MATLAB group, select

**Send data to MATLAB**.Type

**YAGenes**for the variable name, and then click**OK**.The variable

**YAGenes**is added to the MATLAB Workspace as a 3-by-7 matrix.From the MATLAB group, select

**Run MATLAB command**.Type

**plot(YAGenes**for the command, and then click`'`

)**OK**.A Figure window displays a plot of the data.

**Note**Make sure you use the

`'`

(transpose) symbol when plotting the data in this step. You need to transpose the data in`YAGenes`

so that it plots as three genes over seven time intervals.Select cell

**J20**, and then click from the MATLAB group, select**Get MATLAB figure**.The figure is added to the spreadsheet.