Function Problems

How to Extract Top and Bottom 5 Expenses by Date in Excel

WPS Editor Sep 27, 2026 869 views

Question details

The user wants to add online and cash debit columns and extract the 5 largest and 5 smallest nonzero expenses within a specific date range.

How to Extract Top and Bottom 5 Expenses by Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing financial analysis to identify the highest and lowest spending transactions within a given period, excluding empty or zero-value entries.
Observed behavior: The user needs a structured method to conditionally sum multiple expense columns, filter out zeros, and rank the top and bottom five results based on a selected date range.

Before you start

Ensure your transaction data is formatted as an Excel Table (Ctrl+T) with clear headers (e.g., Date, Name, Online Debit, Cash Debit) and verify that your debit columns contain numerical values rather than text or blank errors.

Solution 1Recommended

Use Pivot Tables with a Top 10 Value Filter

This is the most reliable method, as it avoids complex array formulas and allows you to easily filter dates, ignore zeros, and display top/bottom values dynamically.

Before creating the Pivot Table, you must combine your online and cash debit columns into a single total expense value for each row. Pivot Tables come with built-in value filters that make extracting top and bottom ranks incredibly simple.

Create a Total Expense Column

Add a new column next to your data named 'Total Expense'. Enter the formula '=C2+D2' (assuming C is Online Debit and D is Cash Debit) and drag it down to sum the expenses for every row.

Insert a Pivot Table

Select your entire data table, go to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a New Worksheet.

Set Up the Pivot Table Fields

Drag the 'Date' field to the 'Filters' area. Drag the 'Name' or 'Category' field to the 'Rows' area. Finally, drag the new 'Total Expense' field to the 'Values' area.

Filter the Date Range

Click the filter dropdown at the top of the Pivot Table (next to Date). Select 'Select Multiple Items' and check only the dates that fall within your desired date range.

Apply Top and Bottom 5 Filters

Click the dropdown arrow on the 'Row Labels' cell. Go to 'Value Filters' > 'Top 10'. In the dialog box, change '10' to '5' and select 'Top' to get the highest expenses. Repeat the process on a copied Pivot Table, selecting 'Bottom' instead of 'Top', to get the smallest nonzero expenses.

Pro Tip: To ensure zeros are ignored in the 'Bottom 5' filter, you can add a 'Label Filter' or 'Value Filter' strictly set to 'Greater than 0' before applying the Top/Bottom filter.

Solution 2

Use Dynamic Array Formulas (FILTER and SORT)

Ideal for users on modern spreadsheet versions who want the top and bottom values to update instantly without refreshing a Pivot Table.

Smart Data Analysis

Analyze Expenses Quickly with WPS Spreadsheet

WPS Office provides powerful data analysis tools, including dynamic array formulas and intuitive Pivot Tables, allowing you to easily extract top and bottom expenses without complex formatting hurdles.

1. Open your data: Launch WPS Spreadsheet and open your financial ledger.
2. Add a totals column: Create a 'Total Expense' column summing your online and cash debits.
3. Insert PivotTable: Select your dataset, navigate to the 'Insert' tab, and click 'PivotTable'.
4. Filter Top/Bottom 5: Drag your categories to Rows and Totals to Values. Click the Row filter, select 'Value Filters', and choose 'Top 10' to adjust to your top 5 and bottom 5.

Fully compatible with Microsoft Excel formats (.xlsx and .xls)Supports advanced dynamic array functions like FILTER and SORTIntuitive Pivot Table interface for quick financial analysisFree and lightweight alternative to heavy spreadsheet apps

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SMALL function returning zeros instead of the actual lowest expenses?

The SMALL function evaluates all numbers in a range, including zeros. If you have transactions with zero debit, they will be ranked as the smallest. You must use the FILTER function to exclude zeros (e.g., Expense > 0) before evaluating for the bottom values.

Can I combine the online and cash columns directly inside the array formula?

Yes. Within a dynamic array formula like FILTER, you can add them directly as part of the condition or the output array. However, creating a helper column first makes your data much easier to read and troubleshoot.

Why do I get a #CALC! or #VALUE! error when using the FILTER function?

A #CALC! error typically means the FILTER function found no data matching your criteria (e.g., no expenses within the specified date range). A #VALUE! error usually occurs if your date criteria or debit columns contain text strings instead of actual numbers or dates.

Excel & Data Problems