Importing and Exploring Bioinformatic Data from Microsoft Excel

On this page…
Using Excel and MATLAB Together About the Example Before Running the Example Running the Example for the Entire Data Set Editing Formulas to Run the Example on a Subset of the Data Using the Spreadsheet Link EX Interface to Interact With the Data in MATLAB

Using Excel and MATLAB Together

If you have bioinformatic data in an Excel (2007 or 2010) spreadsheet, use Spreadsheet Link EX to:

• Connect Excel with the MATLAB Workspace to exchange data

• Use MATLAB and Bioinformatics Toolbox computational and visualization functions

Back to Top

About the Example

Note   The following example assumes you have Spreadsheet Link EX 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 the Gene Expression Profile Analysis demo.

Back to Top

Before Running the Example

1. If not already done, modify your system path to include the MATLAB root folder as described in Modifying Your System Path in the Spreadsheet Link EX documentation.

2. If not already done, enable the Spreadsheet Link EX Add-In as described in Customization in the Spreadsheet Link EX documentation.

3. Close MATLAB and Excel if they are open.

4. Start Excel 2007 or 2010 software. MATLAB and Spreadsheet Link EX software automatically start.

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

   matlabroot\toolbox\bioinfo\biodemos\Filtered_Yeastdata.xlsm

   Note   matlabroot is the MATLAB root folder, which is where MATLAB software is installed on your system.

6. 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.)

Back to Top

Running the Example for the Entire Data Set

1. 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 EX 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.

2. 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 EX function MLPutMatrix, which creates a MATLAB variable from the data in the spreadsheet. Cell J12 contains a formula using the Spreadsheet Link EX function MLEvalString, which runs the Bioinformatics Toolbox clustergram function using the three variables as input. For more information on adding formulas using Spreadsheet Link EX functions, see Entering Functions into Worksheet Cells in the Spreadsheet Link EX documentation.

   

3. 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 Examples: Using Spreadsheet Link EX Functions in Macros in the Spreadsheet Link EX documentation.

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

   1. Select cell J17.

   2. Press F2.

   3. 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.

   

Back to Top

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

1. 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:

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

      

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

      

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

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

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

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

   4. Select cell J12, press F2, and then press Enter.

   

Back to Top

Using the Spreadsheet Link EX Interface 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:

1. Click-drag to select cells B5 through H7.

   

2. From the MATLAB group, select Send data to MATLAB.

3. 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.

4. From the MATLAB group, select Run MATLAB command.

5. 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.

6. Select cell J20, and then click from the MATLAB group, select Get MATLAB figure.

   The figure is added to the spreadsheet.

   

Back to Top

Features and Functions

Creating get Functions

© 1984-2012- The MathWorks, Inc.    -   Site Help   -   Patents   -   Trademarks   -   Privacy Policy   -   Preventing Piracy   -   RSS