How to Display Only Stock Symbols in Excel Stocks Data
Question details
The user wants to display only the stock ticker symbol in cells formatted with the Excel Stocks data type, rather than the default display of the full company name and exchange information.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting financial portfolios and spreadsheets using the built-in Stocks rich data type.
- Observed behavior
- Excel defaults to displaying the lengthy company name and exchange details for the Stocks data type, and standard custom formatting cannot override this behavior to show just the symbol.
Ensure you have an active internet connection to retrieve financial data, and verify that your version of Excel supports dynamic array formulas like XLOOKUP.
Use XLOOKUP on a Separate Data Sheet
Since the Stocks data type natively forces the company name to display, the best workaround is to isolate the rich data on a separate sheet and use a formula to extract only the ticker symbol to your main presentation sheet.
By separating your raw data from your presentation dashboard, you maintain full access to the rich data type's properties (like Price, Change, and Volume) while strictly controlling how the stock is identified visually.
Create a new worksheet in your workbook. Input your stock symbols here and convert them into the Stocks data type using the Data tab.
In the column immediately next to your Stocks data, type a formula to extract the symbol. For example, if your stock is in cell A2, type `=A2.TickerSymbol` in cell B2.
On your primary dashboard sheet, use the `=XLOOKUP()` function to retrieve the extracted ticker symbol from your background data sheet, alongside any other financial metrics you need.
Limit Name Length using Fill Alignment
If you prefer to keep the Stocks data type on your main sheet without using secondary lookup tables, you can use cell alignment to visually restrict the lengthy company name from spilling over.
Manage Complex Financial Spreadsheets with WPS Office
While Excel's rich data types have strict formatting limitations, you can effectively manage large financial datasets, build robust lookup tables, and analyze your portfolios efficiently with WPS Office. It provides powerful spreadsheet tools completely free of charge.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Financial Workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx portfolio files.
- 3. Analyze Your Data: Utilize built-in advanced formulas and formatting tools to track your financial data exactly how you want.
Frequently Asked Questions
Can I use standard custom number formatting to hide the company name?
No. Excel's Stocks data type is a 'Rich Data Type', which inherently overrides standard text or custom number formatting rules. You must use formulas to extract specific properties like the ticker symbol.
How do I extract just the price or ticker from a Stocks data cell?
You can extract specific data points by referencing the cell followed by a period and the property name. For example, `=A1.TickerSymbol` returns the symbol, and `=A1.Price` returns the current stock price.
Why does Excel add the exchange abbreviation to the company name?
The exchange abbreviation is included by default to ensure precise identification, as many companies trade under similar ticker symbols across different global financial exchanges.
