Excel’s Most Underrated Function

Key insights

• SUBTOTAL: A flexible Excel function that performs SUM, AVERAGE, COUNT and other operations while adapting to your visible data.
  Use it instead of basic SUM or AVERAGE when you work with filters or dynamic tables.
• Syntax and examples: =SUBTOTAL(function_num, ref1, [ref2], ...).
  Example: =SUBTOTAL(101, E2:E25) returns the average of visible cells; =SUBTOTAL(109, E2:E25) returns the sum of visible cells.
• How it handles hidden rows: SUBTOTAL ignores rows hidden by filters so results reflect only visible data.
  Use the 101–111 function numbers to also ignore manually hidden rows when you need truly visible-only calculations.
• 2025 improvements: Excel now shows a dropdown menu at the bottom of tables to switch operations quickly, and Formula Completion suggests SUBTOTAL as you type in Excel for the web.
  These updates make the function easier to discover and use.
• Best use cases: Filtered reports, end-of-table summaries, dashboards, and any workbook where visibility matters.
  Place SUBTOTAL at table bottoms or summary rows for fast, accurate totals that update with filters.
• Best practices & common mistakes: Pick the correct function_num (use 101/109 to ignore manual hides), limit ranges to the relevant table area, and avoid summing full columns when possible.
  These steps prevent double counting and keep results accurate when filters change.

Mynda Treacy (MyOnlineTrainingHub) [MVP] recently released a YouTube video that newsroom readers should note for practical Excel workflow improvements. The video focuses on making spreadsheets easier to navigate and to analyze, and it pairs hands-on demonstrations with a downloadable example workbook to follow along. Consequently, the content is useful for both everyday users and advanced analysts looking to save time and reduce errors.

What the video demonstrates

The presentation opens by showing how the HYPERLINK function can transform messy URLs into neat, clickable anchor text, which makes navigation cleaner and more professional. Moreover, Treacy demonstrates building intra-workbook shortcuts that take you to specific sheets and even to precise cells, so users can reach the right place with a single click. She also covers how to open external files directly from a workbook and how to create dynamic links that update with formulas, which helps when source files or folder structures change.

Understanding the SUBTOTAL function

In addition to navigation techniques, the material highlights the capabilities of the SUBTOTAL function and argues that it is underrated despite its power. The function lets you perform sums, averages, counts and more while excluding hidden rows, which makes it more reliable for filtered lists than standard functions like SUM. For example, using specific function numbers such as 101 or 109 targets visible cells only, and that behavior becomes essential when working with filtered tables or interactive dashboards.

2025 enhancements that improve discoverability

Significantly, recent updates in 2025 make these functions easier to find and use: Excel now surfaces options such as SUBTOTAL more prominently through interface tweaks and suggestions. A new dropdown when a subtotal appears at the bottom of a table enables users to switch between sum, average, count and other operations without rewriting formulas. Likewise, the Formula Completion feature in the web version suggests advanced functions while you type, which helps users learn by doing and reduces the friction of discovering powerful tools.

Trade-offs and practical challenges

However, adopting these techniques involves trade-offs that editors and users should consider before changing workflows. While the HYPERLINK approach simplifies navigation, it can create maintenance work when files move or when shared workbooks contain absolute file paths; thus, users must weigh convenience against long-term manageability. Similarly, SUBTOTAL improves accuracy with filtered data but can confuse collaborators who expect global totals, so clear labels and documentation are essential to avoid misinterpretation.

Performance, compatibility, and security considerations

Performance is another factor: large numbers of dynamic hyperlinks and formula-driven links can slow very large workbooks, and complex SUBTOTAL setups across many ranges may add calculation overhead in heavy models. Additionally, compatibility matters because some features behave slightly differently across Excel desktop, web, and older versions, which requires testing when sharing files across teams. Finally, opening external files via hyperlinks raises security questions, so teams should balance convenience with policies about linking to external content or network locations.

Practical tips and recommended best practices

To balance benefits and risks, Treacy’s examples suggest several practical tactics that reporters and spreadsheet users can implement straight away. First, use tables and named ranges to make links and SUBTOTAL ranges resilient; second, prefer relative or workbook-based links instead of absolute file paths whenever possible; and third, add clear labels next to subtotal cells so readers understand whether a total reflects filtered or unfiltered data. Testing links and documenting assumptions in a short notes sheet will also reduce errors and speed onboarding for colleagues.

Takeaways for newsroom workflows

In short, the video from Mynda Treacy frames two complementary ways to work smarter in Excel: cleaner navigation through the HYPERLINK function and more accurate filtered calculations with SUBTOTAL. While each approach brings clear time savings, the trade-offs around maintenance, performance, and collaboration mean organizations should adopt them thoughtfully and with simple governance. Ultimately, using these tools can cut repetitive tasks and reduce mistakes, but editors should encourage testing, documentation, and shared standards before rolling changes out across teams.

Keywords

underrated Excel functions, hidden Excel functions, Excel functions most users ignore, lesser-known Excel formulas, best Excel functions for productivity, powerful Excel functions to learn, Excel formula tips and tricks, Excel functions beginners overlook