Startseite » Excel EN » Excel FILTER Function – How to Use It on Windows, macOS & Ubuntu

Excel FILTER Function – How to Use It on Windows, macOS & Ubuntu

The Excel FILTER function is one of the most powerful tools for extracting specific data from a range based on defined conditions. It allows users to dynamically filter rows in real time without using the traditional drop-down filter menu. Whether you’re on Windows, macOS, or Ubuntu/Linux, understanding how to use the FILTER function can greatly enhance your data analysis and productivity in Excel.

✅ What is the FILTER Function in Excel?

The FILTER function returns an array of values that meet a given condition. Unlike basic filtering, this function updates automatically when your data changes.

Syntax:

=FILTER(array, include, [if_empty])
  • array: The range or array to filter
  • include: The condition to apply
  • if_empty (optional): Value to return if no match is found

📘 Example Use Case

Task: Filter sales above $1000

=FILTER(A2:C100, C2:C100 > 1000, "No Results")

This returns all rows where column C (e.g., “Amount”) is greater than 1000.

✔ Fully supported in Excel 365 and Excel 2021
✔ LibreOffice Calc offers a similar formula: FILTERXML() or use Standard Filters

🖥️ How to Use the FILTER Function on Different Platforms

🪟 On Windows

  • Type your FILTER formula directly into a cell
  • Press Enter to get dynamic results
  • Use Ctrl + Arrow keys to quickly navigate your array
  • Excel auto-expands the array to fill filtered results

🍎 On macOS

  • Works the same as on Windows
  • Use Cmd + Return to confirm array formula
  • Use Cmd + Shift + Arrow keys to select arrays

🐧 On Ubuntu (LibreOffice)

LibreOffice Calc does not support Excel’s FILTER() natively. Instead:

  • Use Data > More Filters > Standard Filter
  • Or use Advanced Filter to mimic similar behavior

LibreOffice is working toward full dynamic array support.

⌨️ Keyboard Shortcuts for Filtering

ActionWindows ExcelmacOS ExcelUbuntu (LibreOffice Calc)
Open Filter MenuCtrl + Shift + LCmd + Shift + FCtrl + Shift + L
Select range for filteringCtrl + ACmd + ACtrl + A
Confirm array formulaEnterCmd + ReturnEnter
Auto Filter via RibbonAlt + A + TMenu navigationData > AutoFilter

🧠 Pro Tips for Using FILTER

  • Combine with SORT: =SORT(FILTER(A2:C100, C2:C100 > 1000))
  • Use with ISNUMBER + SEARCH for partial matches: =FILTER(A2:B100, ISNUMBER(SEARCH("John", A2:A100)))
  • Nest inside IF or XLOOKUP for advanced logic

❓ Frequently Asked Questions

Is the FILTER function available in Excel 2016 or earlier?
No. It’s only available in Excel 365 and Excel 2021. Use AutoFilter or helper columns for older versions.

Can I use FILTER with multiple conditions?
Yes. Use * (AND) or + (OR):

=FILTER(A2:C100, (B2:B100="USA")*(C2:C100>1000))

What if the filter returns no results?
Add a fallback:

=FILTER(A2:A100, B2:B100="Invalid", "No Match Found")

✅ Conclusion

The Excel FILTER function is a must-know for anyone working with dynamic datasets. With just a few keystrokes, you can extract exactly the rows you need—no drop-down filters required. Whether you’re on Windows, macOS, or Ubuntu, mastering this function will take your data skills to the next level.

Complete List of Windows Keyboard Shortcuts

If you need help for Windows, you can find a whole list of all keyboard shortcuts here.

https://keyboard-shortcuts.org/

Advertisment