Importing Excel Spreadsheets

Overview

Excel spreadsheets are one of the most common ways to store tabular data. MATLAB provides several functions and interactive tools that can read data from Excel files, interpret headers, and convert the contents into MATLAB arrays, tables, or timetables. In this chapter you will focus on importing Excel data using both command line functions and the graphical interface, with special attention to typical spreadsheet layouts such as header rows, mixed text and numbers, and multiple sheets.

Supported Excel File Types and Basic Concepts

MATLAB primarily works with Excel files that have the extensions .xls and .xlsx. In most cases you do not need Excel itself installed in order to read these files, because MATLAB can use its own readers. On some platforms, or for some special formats, MATLAB may also use Excel through the operating system if it is available, but that detail is handled automatically.

An Excel file is organized into one or more sheets. Each sheet is a grid of cells arranged in rows and columns. Columns are usually labeled with letters in Excel, such as A, B, C, and rows with numbers, such as 1, 2, 3. When you import into MATLAB you will often refer to data ranges such as A1:C10 or to entire sheets using their sheet names or numbers.

A typical workflow is to read the spreadsheet into MATLAB as a table. A table in MATLAB preserves column names and allows columns to have different data types, which matches how many spreadsheets are designed. You can also import numeric data as a numeric array, or import text as string or cell arrays, but tables are often more convenient for analysis.

Using readtable to Import Excel Spreadsheets

The function readtable is the main high level function used to import tabular data from Excel. In its simplest form, you only need to provide the file name. MATLAB then attempts to detect the format, the header row, data types for each column, and missing values.

For example, suppose you have a file data.xlsx that contains measurement data with column headers in the first row. You can import the entire first sheet with

T = readtable("data.xlsx");

The variable T is a table that contains one variable for each column in the sheet. The column headers from Excel become variable names in the table. MATLAB modifies names that are not valid variable names, for example it replaces spaces with underscores and may prepend letters if a name starts with a number.

You can specify a different sheet using the "Sheet" name–value argument. For example, to read a sheet named "Experiment2" you can run

T2 = readtable("data.xlsx", "Sheet", "Experiment2");

You can also refer to a sheet by its index, for example 1 for the first sheet, 2 for the second, and so on. The following reads the second sheet:

T2 = readtable("data.xlsx", "Sheet", 2);

If your Excel file contains multiple tables or areas, you may want to limit the import to a specific range of cells.

Selecting Ranges, Sheets, and Regions

Often, spreadsheets contain extra notes, titles, or summary rows that you do not want to import. The readtable function allows you to specify a range of cells to import using the "Range" parameter. Cell ranges use the same notation as in Excel, such as A2:D101.

For example, if your data starts in cell B3 and ends at F20 on a sheet called "Data", you can write

T = readtable("results.xlsx", ...
              "Sheet", "Data", ...
              "Range", "B3:F20");

If the first row of the range contains variable names, readtable will by default treat it as a header. If your range does not include a header row, you can instruct MATLAB not to treat any row as headers using the "ReadVariableNames" option:

T = readtable("results.xlsx", ...
              "Sheet", "Data", ...
              "Range", "B3:F20", ...
              "ReadVariableNames", false);

In that case, MATLAB assigns default variable names, such as Var1, Var2, and so on.

Ranges can also be entire columns or rows. For example, to read columns C through E from row 1 to row 500, you can specify "Range", "C1:E500". To read all rows for a specific set of columns but you do not know the last row, you can often omit the row number. For instance, some MATLAB versions allow notation such as "C:E" to indicate all rows from columns C through E. You can consult the function documentation if you need to use column-only ranges in your version.

Controlling Variable Names and Data Types

Excel files often have descriptive headers that may include spaces, special characters, or repeated names. MATLAB requires table variable names to be valid identifiers, so by default it modifies them. If you want to preserve the original text of the column labels, you can request them as descriptive names while still using valid variable names for indexing.

The "PreserveVariableNames" option controls whether readtable keeps the original column labels unmodified. For example,

T = readtable("survey.xlsx", ...
              "PreserveVariableNames", true);

In this case the variable names in T.Properties.VariableNames match the text in Excel as closely as possible. This can be useful when you only use dot indexing occasionally and rely more on functions that accept variable names as strings.

To control data types, you can use the "TextType" option for text columns and you can specify import options for more advanced control. For simple cases, setting "TextType" to "string" can be convenient:

T = readtable("survey.xlsx", ...
              "TextType", "string");

This instructs MATLAB to import text columns as string arrays instead of cell arrays of character vectors.

When you need more detailed control, for example to force certain columns to numeric types, treat specific text as missing, or skip some columns, you can use detectImportOptions with an Excel file. The workflow looks like this:

opts = detectImportOptions("measurements.xlsx");

The variable opts contains information about the file structure, such as variable names, types, and data ranges. You can modify properties of opts. For instance, to set the second column to type double, you can write

opts.VariableTypes{2} = "double";

Then import the table using

T = readtable("measurements.xlsx", opts);

This approach is helpful when the automatic type detection is not accurate enough, for example when numeric columns contain some text markers or special codes.

Handling Missing and Mixed Data in Excel

Excel files frequently include missing entries, such as empty cells, cells with the text "NA", "N/A", or custom codes like "-999". MATLAB tries to interpret these as missing values where possible, but you may need to specify them explicitly.

If you use detectImportOptions, you can set the MissingRule and TreatAsMissing properties. For example, to treat the strings "NA" and "N/A" as missing, you can do:

opts = detectImportOptions("survey.xlsx");
opts = setvaropts(opts, ...
                  opts.VariableNames, ...
                  "TreatAsMissing", ["NA", "N/A"]);
T = readtable("survey.xlsx", opts);

For numeric variables, "TreatAsMissing" can instruct MATLAB to convert these codes to NaN. Text variables can use the missing string value.

Spreadsheets also commonly mix numbers and text within the same column. For example, a column may contain numbers in most rows but a text label such as "error" in some cells. By default, MATLAB often treats the entire column as text in order to accommodate all values. If you want numeric data, you can convert the column after import or use custom import options that treat specific text as missing and keep the rest numeric.

For instance, if column "Value" sometimes contains the text "error", you can adjust its import options like this:

opts = detectImportOptions("results.xlsx");
opts = setvaropts(opts, "Value", "TreatAsMissing", "error");
T = readtable("results.xlsx", opts);

Then the "Value" variable is usually imported as numeric with NaN where "error" occurred.

Empty rows and columns can also appear in Excel sheets. readtable skips completely empty trailing rows and columns automatically. However, blank rows within the main data range may appear as rows with missing values. You can remove them later using logical indexing or functions that handle missing data.

Importing Numeric Arrays with xlsread and readmatrix

If you do not need a table and only want numeric arrays, you can use readmatrix for Excel files. This function attempts to return a numeric matrix when possible and is often simpler for numeric data that does not require variable names.

For example, to read numeric data from the default sheet of numbers.xlsx, you can use

A = readmatrix("numbers.xlsx");

If the sheet contains a header row with text, readmatrix normally ignores it and returns only the numeric values. You can specify a particular range to control which cells are read:

A = readmatrix("numbers.xlsx", "Range", "B2:F101");

The readmatrix function can also handle some mixed data, but when a column contains nonnumeric entries, it may produce NaN for those cells.

Historically, the function xlsread has been used to read Excel files into MATLAB. It can return numeric data, text, and a combined cell array. However, xlsread relies more directly on Excel on some platforms and has been superseded by newer functions like readtable and readmatrix. In new code, readtable and readmatrix are generally recommended, especially for beginners.

Importing Text and Raw Content with readcell

Sometimes you want to preserve the exact contents of each cell, including numbers, text, logical values, and dates, without MATLAB deciding types in advance. In that case, readcell is useful. It reads data from Excel into a cell array, where each cell can hold data of a different type.

For example, you can run

C = readcell("mixedData.xlsx");

The variable C now holds each spreadsheet cell as an element. A number in Excel becomes a numeric value in C, text becomes a string or character vector, and empty cells become empty arrays or missing values. This approach is helpful for irregular or poorly structured spreadsheets where automatic type detection is not reliable.

As with readtable and readmatrix, you can specify sheet and range:

C = readcell("mixedData.xlsx", ...
             "Sheet", "Sheet1", ...
             "Range", "A1:H50");

After importing, you can process the data, convert appropriate parts to numeric arrays or tables, and discard unwanted cells.

Using the Import Tool for Excel Files

For beginners, the Import Tool provides an interactive way to explore and import Excel spreadsheets without writing code initially. You can open the Import Tool from the MATLAB toolbar or by using the Current Folder browser. When you double click an .xlsx file in the Current Folder, MATLAB often opens it in the Import Tool automatically.

Inside the Import Tool you see a preview of the spreadsheet. You can select ranges by clicking and dragging, choose whether to import as a table, numeric matrix, cell array, or column vectors, and annotate whether the first row contains column headers. The tool detects data types and displays its guesses, which you can adjust. You can also exclude specific columns or rows from import.

A key feature of the Import Tool is that it can generate MATLAB code for the import settings you choose. After configuring the import, you can click an option that shows the corresponding readtable or similar code, then save or copy that code into a script. This is a convenient way to learn the syntax for importing Excel files, especially when you are not yet comfortable with all the import options.

Once you are familiar with the options you need most often, you can skip the graphical tool and write the import commands directly, which makes scripts faster to run and easier to share.

Reading Excel Sheets with Datetime and Categorical Data

Many Excel spreadsheets store dates and times. Excel generally saves dates as numeric values that represent days since a given origin, while displaying them as formatted dates. When you import such columns with readtable, MATLAB usually converts them automatically to datetime values, provided it recognizes the column as dates. This is convenient, because datetime supports calendar operations and plotting.

If a date column is not recognized automatically, it might be imported as numbers or text. In that case you can use detectImportOptions and adjust the type of the variable to datetime, and specify an input format if needed. For example, if a column "Date" uses a specific text format, you may set:

opts = detectImportOptions("log.xlsx");
opts = setvaropts(opts, "Date", ...
                  "InputFormat", "dd/MM/yyyy");
T = readtable("log.xlsx", opts);

Categorical data such as coded labels or categories in Excel columns can also be turned into MATLAB categorical variables. By default, readtable may import some columns as categorical if it detects repeated text labels, but not always. You can convert a text variable to categorical later with categorical, or adjust the import options to force a specific column to have type categorical, for example by modifying opts.VariableTypes.

Correctly importing datetime and categorical data at the beginning often simplifies later analysis, especially when grouping, summarizing, or plotting data.

Dealing with Excel Formulas and Calculated Cells

Excel cells can contain formulas, such as =A1+B1, or references to other sheets. When MATLAB reads an Excel file, it usually obtains the values that Excel would display, not the formulas themselves. If you import a sheet that contains formulas, MATLAB reads the resulting values as if they were plain numbers or text.

If you need to preserve formulas rather than their results, the typical approach is to work directly in Excel. MATLAB focuses on reading and writing data values. For most workflows, using the evaluated results of formulas in MATLAB is sufficient, especially when you treat the spreadsheet as a data source for further analysis.

If the spreadsheet contains formulas that are not evaluated due to external links or other issues, MATLAB may import unexpected values such as zeros or missing entries. In such cases, it can help to open the file in Excel, ensure that formulas are calculated correctly, and save the file before importing it into MATLAB again.

Writing Scripts That Import Excel Data Reproducibly

When you import Excel data only once, interactive methods like the Import Tool can be enough. However, for repeated analysis or automated workflows, it is better to place your import commands in a script or function. This makes your process reproducible and less sensitive to manual errors.

A typical script that imports an Excel spreadsheet might first create import options, adjust them, and then call readtable. For example:

% Create import options from Excel file
opts = detectImportOptions("salesData.xlsx");
% Adjust sheet and range
opts.Sheet = "Quarter1";
opts.DataRange = "A1:G200";
% Treat special codes as missing in specified column
opts = setvaropts(opts, "Region", "TreatAsMissing", "Unknown");
% Read the table
SalesQ1 = readtable("salesData.xlsx", opts);

By storing opts adjustment commands explicitly, you avoid relying solely on automatic detection that might change if the file structure changes. If the Excel file changes in size but keeps the same layout, you may update just the "Range" or rely on dynamic ranges when appropriate.

You also need to be careful about relative and absolute file paths. When you run your script from different folders, the path to the Excel file may not be the current folder. Using functions that build file paths, or putting data files in known subfolders, helps make your import scripts robust.