Function Problems

How to Filter Excel Data by Custom Currency Formatting

Partner Editor Sep 27, 2026 871 views

Question details

The user needs to filter dataset rows based on specific currency codes that are applied via custom formatting, rather than as distinct text values.

How to Filter Excel Data by Custom Currency Formatting

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Trying to extract or filter rows containing specific currency codes that are applied visually via custom cell formatting.
Observed behavior: The standard FILTER function fails to distinguish currencies because it only evaluates the underlying numeric data, completely ignoring the visually displayed custom currency text.

Before you start

Understand that custom formatting changes the visual display of a cell without altering its underlying numeric value. If using macro functions to detect formats, ensure your file is saved as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a Helper Column to Extract Currency Details

Creating a helper column is the safest and most compatible way to extract formatting information for filtering without relying on legacy macros.

Insert a Helper Column

Right-click the column letter header next to your financial data and click 'Insert' to create a new blank column.

Populate the Currency Code

Enter the specific currency code (e.g., USD, CAD) manually for each block of data, or use an IF formula based on existing data logic to populate the text value.

Apply Standard Filter

Highlight your dataset, navigate to the Data tab on the ribbon, and click 'Filter'. Click the dropdown arrow on your new helper column to easily filter by the exact currency text.

Use a Helper Column to Extract Currency Details

Best Practice: Using a text-based helper column avoids macro security warnings and ensures your file remains compatible across all spreadsheet software.

Solution 2

Use GET.CELL Macro Function with LAMBDA

Extract the custom format text directly using a legacy Excel 4.0 macro function to read the visual output of the cell.

Powerful Spreadsheet Data Management

Manage and Filter Complex Data Easily in WPS Spreadsheet

WPS Spreadsheet provides robust tools for handling financial data, including advanced filtering, custom cell formatting, and full support for macro-enabled workbooks. You can easily extract and filter currency data using helper columns or complex formulas.

1. Open Your Data: Launch WPS Spreadsheet and open your financial dataset.
2. Apply Formatting: Highlight your data, right-click, select 'Format Cells', and apply your custom currency formats.
3. Use Data Tools: Navigate to the Data tab to enable filtering, or insert helper columns to manage your custom formats seamlessly.
4. Analyze Results: Use built-in dynamic arrays or standard filtering dropdowns to accurately parse your multi-currency data.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm).Seamlessly handles complex formulas and advanced filtering.Supports custom currency formatting and legacy macro functions.Lightweight, fast, and free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard FILTER function read my custom currency formatting?

The FILTER function processes the actual, underlying numeric values stored in the cells. Custom formatting only alters how the data visually appears on your screen, not the raw data itself.

Can I use the GET.CELL function in a standard workbook (.xlsx)?

No. Because GET.CELL is considered a legacy Excel 4.0 macro function, you must save your file as a Macro-Enabled Workbook (.xlsm) for the function to execute properly.

How do I fix the currency symbols in my filtered output?

When using dynamic array formulas like FILTER, the output cells often inherit formatting from their destination cells or original source incorrectly. You must manually highlight the filtered output range, right-click to access 'Format Cells', and apply your desired custom currency format.

Excel & Data Problems