Data Import & Export

How to Add the FTSE 100 as an Excel Stocks Data Type

Olivia Miller Oct 10, 2026 872 views

Question details

The user needs to correctly input and configure the FTSE 100 index so that Microsoft Excel recognizes it as a Stocks data type.

How to Add the FTSE 100 as an Excel Stocks Data Type

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a spreadsheet to track financial markets and looking to pull in data for the FTSE 100 index.
Observed behavior: Excel fails to recognize 'FTSE 100' as a valid Stocks data type, despite having data for the London Stock Exchange available.

Before you start

Ensure you have an active Microsoft 365 subscription and a stable internet connection, as the built-in Stocks data type requires online connectivity to fetch real-time financial market data.

Solution 1Recommended

Use the Specific UKX Ticker Symbol

Using the exact ticker symbol ensures Excel's data provider correctly maps the cell to the FTSE 100 index.

Often, typing the common name of an index is not enough for Excel's financial data provider to locate the correct asset. By explicitly providing the ticker symbol, you can bypass the recognition failure.

Enter the Ticker Symbol

Click on a blank cell in your spreadsheet and type 'UKX'.

Convert to Stocks Data Type

Select the cell containing the ticker, navigate to the Data tab on the Excel ribbon, and click on 'Stocks' in the Data Types group.

Extract Index Properties

Once converted, a small building icon will appear next to the ticker. Click the 'Insert Data' icon next to the cell to extract specific properties like Price, Change, or 52-week High.

Formatting Tip: If UKX alone is not recognized, try formatting it with the exchange prefix, such as 'INDEX:UKX' or 'LON:UKX'.

Solution 2

Use the STOCKS Formula

For specific Excel versions supporting dynamic arrays, the STOCKS formula can be used to pull index data directly.

Solution 3

Install a Financial Data Add-in

If Excel's native Stocks data type does not support the required index or you are not on Microsoft 365, a third-party add-in is the best alternative.

Free Microsoft Office alternative

Manage Your Financial Spreadsheets with WPS Office

While Microsoft Excel restricts features like the Stocks data type to paid Microsoft 365 subscriptions, WPS Office provides a free, highly compatible, and lightweight alternative for all your spreadsheet tasks. You can easily import financial data from web sources or CSV files into WPS Spreadsheet without expensive recurring fees.

1. Download WPS Office: Visit the official WPS Office website and download the free suite for your operating system.
2. Import Financial Data: Open WPS Spreadsheet, go to the Data tab, and use 'Import Data' to securely fetch stock quotes from CSV files or external web sources.
3. Analyze and Visualize: Utilize powerful built-in charts and pivot tables to track your index performance effortlessly.

100% compatible with Microsoft Excel formats (.xlsx)Easily import financial data via CSV or web queriesLightweight software that installs in secondsFamiliar tabbed interface requiring no learning curveBuilt-in PDF toolkit and free customizable templates

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't typing 'FTSE 100' trigger the Stocks data type in Excel?

Excel's backend financial data provider relies on specific ticker symbols or standardized naming conventions. Because 'FTSE 100' is a broad common name, Excel may fail to map it to the precise financial asset. Using the official ticker 'UKX' resolves this ambiguity.

Can I use the Stocks data type without a Microsoft 365 subscription?

No, the native Stocks data type is a premium feature exclusive to Microsoft 365 subscribers. If you use a standalone version of Excel (like Excel 2019 or 2021), you will need to rely on external data imports or third-party financial add-ins.

What should I do if the UKX ticker returns an error?

If you receive an error when entering UKX, try specifying the exchange prefix manually, such as 'INDEX:UKX'. Additionally, verify that you are signed into your Microsoft account in Excel and that your internet connection is active.

Excel & Data Problems