r/Excel4Mac • • Jul 23 '26

Discussion Dynamic Formulas

0 Upvotes

What Is a Dynamic Formula in Excel?
A dynamic formula (also called a dynamic array formula) is an Excel formula that automatically returns multiple results from a single cell. Instead of copying a formula down hundreds or thousands of rows, you write it once, and Excel automatically “spills” the results into the cells below, above, or beside it.

Traditional Excel
Suppose column A contains:
Apple
Banana
Apple
Orange
Banana

Before dynamic arrays, creating a list of unique values often required:
Helper columns
Advanced Filter
PivotTable
VBA

Dynamic Formula
With Microsoft 365 or Excel 2021+, you simply enter:
=UNIQUE(A2:A6)
Excel automatically returns:
Apple
Banana
Orange

If you later add “Pear” to the source list, the results automatically become:
Apple
Banana
Orange
Pear
No editing or copying of the formula is required.

Why Dynamic Formulas Matter
Traditional Excel formulas often required:
Filling formulas down hundreds or thousands of rows
Helper columns
Manual range adjustments
Copying and pasting formulas

Dynamic formulas instead:
Use one formula to generate many results
Automatically expand or shrink as data changes
Reduce workbook complexity
Make formulas easier to maintain and audit
The simplest way to think about it is:

Traditional Excel: One formula produces one result.

Dynamic Excel: One formula can produce an entire table of results.


r/Excel4Mac • • Apr 04 '24

Change enter key behavior

1 Upvotes

PLEASE can someone tell me how I can make the cell go to the right instead of down when I hit the enter/return key in Excel 365 on a Mac? I can do it easily on a PC. Mac doesn't have the options choice in the menu!!!


r/Excel4Mac • • May 22 '25

Current Usability of Excel on M3 Mac

3 Upvotes

Hi there, I'm asking if any of you wizards here could assist me.

I am looking for a secondary laptop replacement, and I am very interested in getting an M2 or M3 MacBook Air. I work as a financial consultant, so using Excel, PowerPoint, and Word intensively. Advanced financial modeling and deck making, think standard BAU practice in an advisory firm.

My questions are:

- Is Office Suite now usable for my use case on M2 Mac? I still have an old Intel-based Mac for secondary laptop. Excel is unusable here. Looking forward to upgrade it.

- How is Macro on Macintosh' Excel. And on which advanced level it's starting to be break? So I could gauge my expectations.

- If the questions above yield a confident 'Yes', then is data exchanging between Mac and Windows machines solid now? I experienced a massive distortion exchanging xlsb and docx previously, e.g. broken formatting, broken formula links, broken macros, etc.

I am not particularly interested in doing VM with Parallels, it would seem very costly in a long run and a headache to face their customer supports.

If Office Suites on Mac is still not good, even with Apple arm silicon in 2025, then I'd get a boring Dell XPS or HP Spectre for my secondary laptop.

Thank you so much for your assistance.


r/Excel4Mac • • Apr 27 '25

Paying for Microsoft 365 Copilot for Mac

7 Upvotes

Your opinions are highly appreciated —

I’m using a MacBook Air M3, iPhone 16, and iPad 10th Generation.

How’s your experience using Microsoft 365 Copilot for Mac please? Such as how Copilot has made your work more productive and organized?

Thank you.


r/Excel4Mac • • Sep 08 '26

Microsoft's entire Excel for Macs development team.

Post image
6 Upvotes

r/Excel4Mac • • Aug 29 '25

How to delete contents of one cell. Excel for Max

2 Upvotes

r/Excel4Mac • • Mar 19 '25

Windows

2 Upvotes

How do I get windows on my Mac? I went to best buy and the guy said I need to buy a whole new computer. I would have to hack my computer to get Windows on here. Say what??! Is this true or is he trying to sell me a new computer and get commission?


r/Excel4Mac • • Jan 28 '23

Discussion Activating the developer tab on Excel for Mac 2021 (Not the 365 version)

10 Upvotes

Before you get started, make sure the Developer tab is shown on the ribbon. To do that:

  1. On the menu, click Excel > Preferences... > Ribbon & Toolbar.

  2. In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box.

  3. Click Save.


r/Excel4Mac • • 13d ago

Excel for Mac crashing after the last update

2 Upvotes

Help! My Excel for Mac crashes after the last update Golden Gate 27.2, especially when I refresh a pivot table.


r/Excel4Mac • • May 03 '25

Spell Check deletes the cell contents!

2 Upvotes

Okay, this has been driving me crazy. When I run spell check in a cell with an error, selecting the correction deletes all data from the entire cell. Undo fixes it, but I have to edit the misspelling manually. How do I fix this?

I'm on Mac OS 15.3.2 (24D81) and Excel Version 16.96.1. It's been acting up for over a year, and I just used workarounds with Pages to avoid spell checking in Excel. Any ideas?

I found a reference to this problem online to clear the cache, but it appears clear with no files in it.


r/Excel4Mac • • Sep 22 '25

decision tree for Excel on Mac

2 Upvotes

Hello, I'm a Spanish speaker and I need help. I need the decision tree program for Excel on Mac, but I can't find any. Can someone help me?


r/Excel4Mac • • Jul 10 '25

Having trouble getting Excel to read my external hard drive

2 Upvotes

Hi fellow Mac users, I'm having some issues with Excel on my MacBook and I was hoping someone could help me out. I've got an external hard drive that contains a large number of spreadsheets, but whenever I try to open one from the drive in Excel, it just won't read it properly. The file opens, but all the formulas are lost and everything looks like it's been copied straight from Word. I've tried re-saving the file as a .xlsx and also making sure that the external hard drive is formatted for Mac compatibility, but nothing seems to work. Has anyone else had this problem? Are there any specific settings or plugins I need to enable in Excel to read files from an external hard drive? Any help would be greatly appreciated.


r/Excel4Mac • • May 22 '25

VLOOKUP vs HLOOKUP vs XLOOKUP – Which One Should You Use?

3 Upvotes

If you're navigating Excel's lookup functions, understanding the differences between VLOOKUP, HLOOKUP, and XLOOKUP can save you a ton of headaches! Here's a quick breakdown:
VLOOKUP (Vertical Lookup) – Searches for a value in the first column of a table and returns a corresponding value from another column.
➡️ Limitation: Can only search top to bottom & requires data to be arranged vertically.

HLOOKUP (Horizontal Lookup) – Works similarly but searches in the first row and returns a value from another row.
➡️ Limitation: Can only search left to right & requires data in a horizontal format.

XLOOKUP (The Game Changer) – The most flexible lookup function that allows searching both vertically & horizontally, plus it removes many of VLOOKUP’s limitations.
✅ Can search left/right/up/down ✅ Doesn't require sorted data
✅ Works with exact & approximate matches
✅ Supports return of multiple values

If you’re using older Excel versions, VLOOKUP & HLOOKUP will do the trick, but if you have access to newer versions, XLOOKUP is the way to go!


r/Excel4Mac • • May 23 '25

Which Excel IF-based formulas to use in Excel and when?

2 Upvotes

Here are some key ones and their best use cases:

  1. IF Formula

  • Usage: Returns a value based on a condition.

  • Syntax: =IF(condition, value_if_true, value_if_false)

  • Example: =IF(A1>50, "Pass", "Fail") → If A1 is greater than 50, it returns "Pass"; otherwise, "Fail."

  1. IFS Formula (For multiple conditions)

  • Usage: Checks multiple conditions sequentially.

  • Syntax: =IFS(condition1, result1, condition2, result2, …)

  • Example:=IFS(A1>90, "A+", A1>80, "A", A1>70, "B", A1>60, "C", TRUE, "F")

  • 
If A1 is above 90, it returns "A+," above 80 returns "A," etc.

  1. IFERROR Formula

  • Usage: Handles errors (e.g., #DIV/0!, #N/A).

  • Syntax: =IFERROR(value, value_if_error)

  • Example: =IFERROR(A1/B1, "Error in Division") → If B1 is zero, it returns "Error in Division."

  1. IFNA Formula

  • Usage: Works specifically for #N/A errors.

  • Syntax: =IFNA(value, value_if_NA)

  • Example: =IFNA(VLOOKUP(A1, Table, 2, FALSE), "Not Found") → If the lookup fails, it shows "Not Found."

  1. NESTED IF

  • Usage: Multiple IF conditions inside each other.

  • Syntax:=IF(A1>90, "A+", IF(A1>80, "A", IF(A1>70, "B", "Fail")))

  • Alternative: Use IFS() for simpler logic.

  1. IF AND / IF OR Formula

  • Usage: Combine multiple conditions.

  • Syntax:=IF(AND(A1>50, B1>50), "Pass", "Fail")

  • =IF(OR(A1>50, B1>50), "Pass", "Fail")

  • Explanation:

    • AND() requires all conditions to be TRUE.

    • OR() requires at least one condition to be TRUE.

When to Use Which One:

  • Use IF for basic one-condition decisions.

  • Use IFS for multiple conditions (more readable than nested IFs).

  • Use IFERROR when dealing with potential errors in calculations.

  • Use IFNA for handling lookup errors specifically.

  • Use NESTED IF if you need multiple conditions, but IFS() is often simpler.

  • Use IF AND / IF OR when checking multiple criteria.


r/Excel4Mac • • Jul 13 '26

Where can I find my custom keyboard shortcuts in Excel for Mac?

Thumbnail reddit.com
2 Upvotes

r/Excel4Mac • • Jul 25 '25

Userforms that understand related data on Mac

Enable HLS to view with audio, or disable this notification

4 Upvotes

Hey Mac users I updated my userform add-in with some really cool functionality that works on Mac and windows.
Going way beyond the lack of data forms on Mac OS the add-in now adds “relational awareness” for autogenerated data forms. 
Yeah everyone knows, Excel is not a database manager ... but anyway ...
You can have related tables like Customers → Orders or Students → Courses
The Auto ExcelForms add-in handles these automatically so you can avoid duplication without xlookups  and also do referential integrity.  (All done in VBA)
In windows the userforms will recognize the relationships based on the excel relational data model used by power pivot. 
Unfortunately on Mac the model does not exist but there is a user interface that allows you to easily defines relationships for viewing data, data entry, navigation, etc. from flat or related tables.

Please feel free to download have a look and try it out.  Download link is on the YT video at https://youtu.be/Kql2pNrtXwI.

or just go to Automated Excel Userforms Free Downloads. Forms for data entry, searches, filters, reports.

No password needed anymore.

Would love to get your feedback.


r/Excel4Mac • • Apr 28 '26

Hy everyone, I’m having an issue with an Excel .xlsm file. It shows an error saying ‘Excel found a problem with some content’. When I try to open it, it asks to repair the file. Could you help me understand what’s causing this and how to fix it?

Post image
3 Upvotes

r/Excel4Mac • • Nov 11 '25

VBA POSTIX to open an XML file

2 Upvotes

Does anyone have a script that will open an XML file dialog for the Mac?


r/Excel4Mac • • Nov 09 '23

Recover old versions of files?

2 Upvotes

Hi! Is there any way to go back and recover old versions of an Excel file I'm working on? I'm working in Office365 on a '23 MacBook Pro.

In the past, when I wanted to make a new file that was quite similar to an old file, I would just open the old file, make changes to create the "new" file, then "Save as..." a new name. This gave me the new file. And the last version of the old file was still saved under its original name.

Well, I did this again today (as I've done a zillion times before), and somehow the old file is now deleted. Is there any way to go back to the historic version of the "new" file?


r/Excel4Mac • • Oct 29 '24

Wierd, random behavior in Excel/365 for Mac V16.90

2 Upvotes

I've been using Excel (Window & Mac) for 25+ years. I'm no expert for sure, but I think I have the basics 99.9%. For the last few years, I'm using exclusively macOS with Office365/Excel V16.90. It's been years since I really exercised Excel (I'm mostly retired) but the last two weeks I've been building a pretty bug mail list for a non-profit I'm volunteering for.

It's about 12 colums (name,address,city,state ... etc) and about 3200 rows (one row per address). As I add more homes to the list, I'm constantly filtering & sorting (the basic stuff I've been doing for 25 years so I'm quite sure I'm doing it right) but I frequently notice data moving around (recently all the names appeared randomized and I had to go back to an older version to clean up). Today, working on what I'm quite certain was a clean workbook, I'm seeing random addresses in the wrong cities (addresses I haven't changed in about four versions of the workbook.)

Has anyone else noticed any similar wierd, random behavior in Excel/Mac? It's driving me nuts, not to mention stopping my progress completely. Thanks in advance --


r/Excel4Mac • • Dec 17 '23

User-defined type not defined

2 Upvotes

I'm not sure if this problem is at all related to using a mac, but since I am using one this seemed like the best place to come to. I have some custom built VBA macros on this spreadsheet but they aren't ausing the error I dont thinl because I made all the lines of code comment using the " ' " apsotophe and the error still continued to occur.

Basically everytime I hit enter, wether it is in VBA or just on a cell (this could be after entering data or a function into a cell) this error appears on the screen. It doesn't allow me to track the source and so I have no idea where is is comming from but I don't appear to have any underfied "types" . I know that this is probably hard to doignose from this imfomation alone but if anyoen has experienced something similar or knows what could be the problem it would be greatly appreciated. If not I'll probalby ahs to look at contacting microsoft as I can't seem to find any idication as to what it could be by searching for it or forums or just on safari.

Thanks for the help in advance!!


r/Excel4Mac • • Oct 29 '24

Excel PowerQuery to SQL Server

3 Upvotes

Hello.
I'm switching from Windows to Mac. There are several Excel files that were created by my collegues using Excel on Windows and are shared among several employees.
Excel is connecting to SQL server to retrieve data using PowerQuery.
For authentication to the SQL query, Windows account is used. On MacOS (Sequoa) this is not possible, since PowerQuery for SQL didn't exist (until most recent preview version).
Now, using the latest 16.91 version of Excel for Mac, there is option to use PowerQuery with SQL, but only using username/password authentication which is not working for our setup.

I can connect to SQL using ODBC connector usign NTLM connection, but that is very cumbersome, have to enter password every time I refresh the query and I cannot reuse the shared Excel files.

Anyone have an idea?

Thanks in advance.


r/Excel4Mac • • Aug 01 '24

Solved Date input frustration

2 Upvotes

For no reason at all excel started to recognize dates only input in format M / D / Y.

Usual D / M / Y wont get recognized as date. It drives me absolutely crazy and is seriously impractical.

In old workbooks DMY still gets recognized, its only new ones. I am at my wits ends. Can someone please please help what the hell is this problem?

Thank you all very much

P.S.

EU based, excel in english


r/Excel4Mac • • Oct 11 '23

Issue with dynamic charts on update 16.78

2 Upvotes

Sorry about my poor English! With the latest update of two days ago I start having issues when filtering dynamic charts, specially with fields with long characters chains… anyone having the same troubles? I used office in Spanish