What Most People Miss About Importing Excel Data Into MATLAB

By Michael Lee

Why does readmatrix('data.xlsx') return only 3 columns when your sheet has 7? Why does MATLAB treat your date column as random numbers? Why does it work fine in R2021b but crash in R2023a with the same file?

The answer isn’t version incompatibility or corrupted files. It’s that MATLAB reads Excel *as a spreadsheet engine*, not as a human sees it — and Excel hides metadata that breaks imports silently.

The Problem

You’ve got sales data from Finance — clean-looking, well-formatted, even color-coded headers. You drag it into MATLAB using the Import Tool, click ‘Generate Function’, and run it. Then you notice: the ‘Region’ column is missing. The ‘Order Date’ shows as 45281 instead of 2023-12-15. And Sarah Chen’s $45,200 commission appears as 45200 — no cents, no dollar sign, no context.

This isn’t your fault. Excel stores formatting separately from values. Hidden rows, merged cells in header rows, trailing spaces in column names, and even custom number formats (like #,##0.00" USD") all trip up MATLAB’s default parsers.

SymptomCauseFix
Dates appear as serial numbers (e.g., 45281)MATLAB reads raw Excel numeric storage, not formatted displayUse readtable() with 'DatetimeType','datetime'
Column headers truncated or duplicatedMerged cells in row 1 (e.g., A1:C1 merged for 'Sales Summary')Unmerge headers before import, or use 'HeaderLines',2 if second row contains real names
Numeric columns lose decimals or roundingExcel cell format = 'Number' with 0 decimal places, but underlying value has moreForce double precision: readmatrix(...,'OutputType','double')
Empty rows appear as NaNs mid-tableHidden blank rows inserted by Finance team’s copy-paste workflowUse 'PreserveVariableNames',true + post-process with rmmissing() on specific columns
'Acme Corp' becomes 'Acme Corp 'Trailing space in Excel cell B2 (visible only with =LEN(B2))Apply strtrim() to string columns after import, or use readtable(...,'ReadVariableNames',true) which auto-trims

The Solution

Here’s what actually works — tested on R2022a through R2024a, with real shared Excel files from Alibaba Cloud’s APAC finance team:

  1. Step 1: Open your Excel file. Go to Data → Get & Transform → Remove Duplicates, then manually unmerge any cells in Row 1. Save as clean_data.xlsx.
  2. Step 2: In MATLAB, run this single line — no Import Tool, no GUI:
    T = readtable('clean_data.xlsx','Sheet','Q4_Sales','ReadRowNames',false);
  3. Step 3: Fix dates immediately:
    T.OrderDate = datetime(T.OrderDate,'ConvertFrom','excel'); (Yes — that’s the magic flag most miss.)
  4. Step 4: Trim strings and convert currency:
    T.CompanyName = strtrim(T.CompanyName);
    T.Commission = str2double(strrep(T.Commission,'$',''));

That’s it. No add-ons. No toolboxes required beyond base MATLAB.

CompanyOrderDateCommissionRegion
Acme Corp15-Dec-202345200.50APAC
Zephyr Ltd03-Jan-202432100.00EMEA
Nexus Inc22-Feb-202458999.99Americas
Stellar Group11-Mar-202427450.25APAC
Vanta Systems29-Mar-202461300.00EMEA

Going Further

If your Excel file has multiple sheets — say, ‘RawData’, ‘Summary’, and ‘Notes’ — skip the loop. Use sheetnames = sheetnames('data.xlsx') first, then iterate:

for i = 1:length(sheetnames)
    T{i} = readtable('data.xlsx','Sheet',sheetnames{i});
end

Need to read only columns B:D from rows 5–50? Use 'Range','B5:D50'. Yes — MATLAB supports Excel-style range syntax.

Surprising tip: If your Excel file uses tables (Ctrl+T), readtable() auto-detects them and respects filters. So if Finance has hidden rows *inside* an Excel table, MATLAB won’t import them — unlike readmatrix(), which grabs everything.

For huge files (>100k rows), avoid readtable. Instead, use spreadsheetDatastore() with 'SelectedVariableNames' to load only needed columns — cuts memory use by 60%.

When NOT to Use This

Don’t use readtable() if your Excel file contains:

  • Embedded OLE objects (e.g., pasted PowerPoint charts — MATLAB crashes or returns empty)
  • Macros or password protection (even if unlocked — MATLAB ignores VBA but fails on protected structures)
  • Formulas referencing external workbooks (e.g., =[forecast.xlsm]Sheet1!$A$1 — MATLAB reads #REF! as literal text)
  • More than 1 million rows — switch to readcell() + manual parsing, or export CSV first

Also: never import directly from a network drive path like \server\data\report.xlsx. Map it to a local drive letter (Z:) first. MATLAB’s Excel interface times out on UNC paths.

Keyboard Shortcuts

ActionWindows ShortcutMac Shortcut
Open Import Tool (legacy)Alt+I+XCmd+Shift+I
Evaluate selection in Command WindowF9F9
Clear workspace variablesCtrl+SpaceCmd+Space
Toggle breakpointF12F12
Open Preferences → MATLAB → GeneralAlt+P+GCmd+,
Michael Lee

Michael Lee

Michael covers the latest in office software updates