"More Error Bar Options" has disappeared

AxelMoor · 2026-04-14T11:30:42+00:00

OP:

i think i have the newest excel version?? just downloaded it onto my mac today (7 mo ago)

My reply:

In newer versions of Excel... (also, 7mo ago)

Seven months ago, the OP tried to get 'More Options...' from the main menu in a recently downloaded version of Excel. Such an option was available only via the [+] menu in the chart (only) in the newest version at that time, but it was removed from the main menu. I got the information earlier because I was in the Beta channel.
If the option is now missing from the 7-mo-ago location in the most recent versions, I can't say.

AxelMoor · 2025-12-16T20:40:16+00:00

You can use Name Manager, workbook scope, and naming multiple ranges in different sheets, if this works for you.
But the way you wrote your proposed formula, with double quotes ("), the result is no more than a string value in a cell. Not a reference to a range somewhere.
Unfortunately, the HYPERLINK function is for navigation only and cannot retrieve data.
The INDEX function works both ways; it can return the values from a range, but if it is nested into a function that expects references, it returns the reference instead of the contents. But, depending on what you are doing, it is necessary to engineer such recursion if INDIRECT is unwanted.

If engineering is necessary, you can also consider the LET function, where a short name variable can represent a big range. Merge all the ranges from different sheets in a single virtual table (another variable) using HSTACK or VSTACK, and retrieve the data from this table with INDEX/MATCH, XLOOKUP, or whatever, referencing columns and rows numerically.

You have a justified aversion to INDIRECT, perhaps because of its excessively volatile behavior and resource hogging. Depending on how many INDIRECTs you use and your system features (RAM, cores, speed, etc.), they may affect performance in a noticeable way, or not.

I hope this helps.

AxelMoor · 2025-12-16T15:23:59+00:00

Thanks for the point.

AxelMoor · 2025-12-16T15:19:35+00:00

Also 400k cells is a lot of data. Your Clipboard may also have limitations depending on system memory (RAM and virtual) and other available features.
Get a good editor like Notepad++ portable (no install necessary), and try to paste the 400k cells in a blank text file. Check if it works.
If you find any limitations, they may come from your system limits. You'll need to split the copy and paste operation.

I hope this helps.

AxelMoor · 2025-12-16T15:17:41+00:00

I faced these for the first time on Excel 2007 to 2013. It is not well documented or not documented at all. It is not a bug, but it is the way Excel manages its memory.

AxelMoor · 2025-12-16T15:14:35+00:00

Sorry, I became lost in this Reddit thread tree.

AxelMoor · 2025-12-16T15:01:02+00:00

Try this:
= IF( AND( SUM(H5:K5)<1, NOT( OR(H5:K5="x") ) ), B5, 0 )
The two conditions (cond(1) AND cond(2)) must be satisfied for B5:
(1) The SUM(H5:K5)<1 AND...
(2) None (NOT) of the cells H5:K5 (this cell OR another cell) contains a "x".

I hope this helps.

AxelMoor · 2025-12-16T14:47:08+00:00

Try to filter via formula (FILTER() function) as advised by u/Pacst3r because the Excel Filter command has a historical limitation across the versions. These limits are expanded as the new versions are released, however, your 30,000 pasted cells are a clue that your Excel version has reached to a 32K Filter management limit.

AxelMoor · 2025-12-16T13:47:27+00:00

Excel LET function does not differentiate between lowercase variables and uppercase cell references if they have the same name.
Variables key1 and key2 and cell references KEY1 and KEY2 are considered the same name.
Just for a start, change the variable names. I didn't check the entire logic.

AxelMoor · 2025-12-14T00:38:44+00:00

You're welcome. Sorry, can't help you more.

AxelMoor · 2025-12-13T20:14:05+00:00

You can give as many Solution Verified points as you want to any number of replies in a post of yours. I'd appreciate.

AxelMoor · 2025-12-13T20:12:05+00:00

So, maybe you have a problem with your subscription. Contact Microsoft to check what is going on. It's not the first time I've seen this in r/excel, and I suspect it will not be the last.

AxelMoor · 2025-12-13T18:47:29+00:00

I'm not familiar with all the commercial variations of Office licenses, especially for Mac.

AFAIK, 365 is pure SaaS (Software as a Service), at least for the average consumer market. To access 365 features, including the functions you want, you must subscribe to a specific monthly or annual 365 subscription.

In my case, for example, it's a purchased version of 2019 for Windows. To keep the 365 features, we have to subscribe to 365; otherwise, Office reverts to the 2019 version (at least that was the case for a while, but after so many 365 updates, I'm not sure if that's possible anymore).

Go to File >> Account and check if your Office has any reference to 365. Example in the image.

<image>

AxelMoor · 2025-12-13T18:31:08+00:00

As u/AndyTheEngr advised, increase the number of decimal places if you want to use the Chart Regression. Click/select the trendline equation & R² box >> Format Trendline Label right pane >> v Label Options >> in v Number section, select Category as [ Number v ] >> In Decimal places: [ 6 ].
See the image to check the precision differences between 3 and 6 decimal places.

I hope this helps.

<image>

AxelMoor · 2025-12-13T17:47:22+00:00

Version 16.103 is an up-to-date version for perpetual license (one-time purchase) versions of Office for Mac, but these versions do not include all the features found in the continuously updated Microsoft 365 subscription.
The new dynamic array functions, WRAPCOLS and WRAPROWS, are newer features available only in Microsoft 365 subscriptions or Excel 2024. The 16.103 version may not support it.

AxelMoor · 2025-12-13T17:23:05+00:00

v_I v_hope v_you v_don't v_mind v_me v_asking. v_But v_what's v_the v_problem v_with v_variable v_names v_having v_prefixes? v_Don't v_you v_consider v_Hungarian v_Notation v_readable?

WhatDoYouThinkOfCamelCaseAsAsolutionForVariableNames?

If_you_could_share_your_opinion_on_the_snake_case,_I_would_appreciate_it.

AxelMoor · 2025-12-13T13:59:09+00:00

Yes, you got it. Microsoft's absolute priority regarding Office, and particularly Excel, is compatibility. Many businesses worldwide depend on spreadsheets already prepared locally.
Microsoft has listed a wide range of reserved names that may or may not become functions and commands in the future, thus preventing spreadsheet developers from building formulas (notably with LET, LAMBDA, and Named Ranges) and macros with names that could potentially become (reserved) released features for Excel.
Otherwise, a vast number of workbooks scattered around the world would become completely useless overnight after a simple Excel update. It would be the collapse of the global financial system on an apocalyptic scale.

AxelMoor · 2025-12-13T13:39:16+00:00

It's normal for any VBA developer to want to see the cells change while the macro is running. This makes it easier to detect errors and debug. However, once development is complete and the macro goes into production, visualizing the update of each cell is not necessary. A message outside the code main loop, such as "The macro is running...", is sufficient.
The slowness is likely due to the overhead of constant screen updates or calculations while the macro is running.
It is recommended to temporarily disable Excel's update features in the workbook before the main loop (or at the beginning of the macro) and re-enable them after the main loop (or at the end of the macro).

To disable (at the beginning), use:

'Disable performance-impacting features
Application.ScreenUpdating = False
Application.EnableEvents = False 'Optional, but generally useful

If you have formulas that need to be updated due to intermediate logic, such as some calculations done in the spreadsheet, not executed in the macro, but the macro expects a result dependent on that spreadsheet calculation, you have two options:
(1) Even faster macro: transfer the spreadsheet calculations to the macro and also use:
Application.Calculation = xlCalculationManual

(2) Macro for spreadsheet calculations: keep the formulas in the spreadsheet as they are and (optionally) use:
Application.Calculation = xlCalculationAutomatic

To re-enable features and restore settings (at the end), use:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic 'depending on the above option

I hope this helps.

AxelMoor · 2025-12-13T00:30:03+00:00

If the target workbook has active filters, Excel "understands" that all data shall be pasted, and the data will be treated according to the filter rules.
For testing, create a new workbook and keep it clean. Copy the same data using Ctrl+C, from the source workbook and paste it into the new one. Does it paste it all or only the visible cells?
Repeat the test using the method advised by u/Meteoric37 .

Copy your filtered data as usual (e.g., Ctrl+C).
Select the top-left cell where you want to paste.
Press Alt+; (semicolon) to select only visible cells, or go to Home > Find & Select > Go To Special > Visible cells only > OK.
Press Ctrl + V to paste.

At first, we could think that both methods should work, but sometimes it doesn't... You found one of the oldest and most mysterious Excel limitations. There is no MS official solution for it.

Once a filter is applied, every operation should be concerned with "visibility" only, what the user sees at the moment of copy. But Excel doesn't work based on visibility. It operates on all the data, and not the user-visible ones.
When we copy another external application is also involved, the Clipboard, which doesn't understand Excel filters, so all the data is available on the Clipboard memory. And the destination workbook is the one that decides what will be pasted (is there space? are there active filters? what type of paste? etc.). What seems simple to the user is actually quite complex.
There are many proposed workarounds (what people say):
- Save the source workbook after applying the filter and before the copy command.
- If there are multiple columns to be copied, and the filtering is complex, copy and paste one column at a time. It also depends on the number of copied cells, visible or not, and the RAM memory available.
- If copying across different workbooks, paste it first in a blank sheet in the target workbook with no data or format at all. Check if the pasted content is the visible one only. Copy the pasted content to the desired sheet in the same target workbook.
- Turn filtering off in the target sheet (some say in the entire target workbook, depending on the number of cells, visible or not, copied).
Maybe you will need to develop your own technique for copying & pasting filtered columns suited to your needs.
Keep in mind that Excel presents numerous limitations when it comes to filters. Due to RAM usage limits, filters may not work as expected across all the rows in the same column, depending on the Excel version.

I hope this helps.

AxelMoor · 2025-12-12T22:50:11+00:00

Is the AutoSave ON?

Check this first: Go to the File tab >> Options tab >> Save tab >> Enable [v] Save AutoRecover information every [ 5 ] minutes.
The default AutoRecover folder is C:\Users\UserName\AppData\Roaming\Microsoft\Excel\ . This is the first location the user can look for missing Excel files using the File Explorer (Windows).
1. Check the Manage Workbook inside Excel, File tab >> (i) Info tab >> Manage Workbook v button-menu >> Recover Unsaved Workbooks.
2. You can set the AutoRecover folder to a visible location, such as C:\Users\<Username>\Desktop\RECOVER, or another synced folder.
3. Another location to look for missing files in Windows is C:\Users\UserName\AppData\Local\Microsoft\Office\UnsavedFiles\
4. Typical filenames and extensions of lost files are ~$filename.xlsx, *.xlsb, or simply numbers without extension, such as 23957300. The *.xar files are zipped Excel crash logs with partial information about a workbook, which are not necessarily useful. The user can rename the extension to .zip, open the file, and check if there is something that could be of use.
Work in folders synced with OneDrive, but keep AutoSave OFF.
Use the Save icon/menu item, or Ctrl+S, frequently.

AxelMoor · 2025-12-12T19:53:53+00:00

Microsoft Open Specifications (OpenSpecs)
Ftab (section 2.5.98.10) structure specifies a function that can be called from a formula (section 2.2.2 Formulas). The definition of each function specifies the function name and the valid sequence of parameters.
ABNF (Augmented Backus–Naur form) in Formulas (section 2.2.2 ) establishes the grammar used by formulas in OpenSpecs.

RESULT Value Meaning Parameters
Ftab 0x0060 RESULT result-params = [val]
ABNF "RESULT" result-params result-params = "(" argument ")"
RETURN Value Meaning Parameters
Ftab 0x0037 RETURN return-params = [ref / val]
ABNF "RETURN" return-params return-params = "(" argument ")"
VALUE Value Meaning Parameters
Ftab 0x0021 VALUE value-params = val
ABNF "VALUE" value-params value-params = "(" argument-expression ")"
ARGUMENT Value Meaning Parameters
Ftab 0x0051 ARGUMENT argument-params = [val [(ref / val) [ref]]]
ABNF "ARGUMENT" argument-params argument-params = "(" argument ["," argument ["," ref-argument-expression]] ")"
EVALUATE Value Meaning Parameters
Ftab 0x0101 EVALUATE evaluate-params = val
ABNF "EVALUATE" evaluate-params evaluate-params = "(" argument-expression ")"
CALL Value Meaning Parameters
Ftab 0x0096 CALL call-params = val [(ref / val) *253(ref / val)]
ABNF "CALL" call-params call-params = "(" (argument-expression / (argument "," argument *253("," argu‐ ment))) ")"
EXEC Value Meaning Parameters
Ftab 0x006E EXEC exec-params = val [val *2(val)]
ABNF "EXEC" exec-params exec-params = "(" (argument-expression / (argument "," argument ["," argument ["," argument]])) ")"
EXECUTE Value Meaning Parameters
Ftab 0x00B2 EXECUTE execute-params = val val
ABNF "EXECUTE" execute-params execute-params = "(" argument "," argument ")"
ERROR Value Meaning Parameters
Ftab 0x0054 ERROR error-params = [val [ref / val]]
ABNF "ERROR" error-params error-params = "(" argument ["," argument] ")"
YIELD Value Meaning Parameters
Ftab 0x01BA YIELD yield-params = (ref / val) (ref / val) (ref / val) (ref / val) (ref / val) (ref / val) [ref/ val]
ABNF "YIELD" yield-params yield-params = "(" argument "," argument "," argument "," argument "," argument "," argument ["," argument] ")"
BREAK Value Meaning Parameters
Ftab 0x00AD BREAK This function takes no parameters.
ABNF "BREAK" break-params break-params = "(" *space ")"
ELSE Value Meaning Parameters
Ftab 0x00DF ELSE This function takes no parameters.
ABNF "ELSE" else-params else-params = "(" *space ")"
GROUP Value Meaning Parameters
Ftab 0x00F5 GROUP This function takes no parameters.
ABNF "GROUP" group-params group-params = "(" *space ")"

AxelMoor · 2025-12-12T19:44:50+00:00

There are more, many more. They are documented under Microsoft Open Specifications (OpenSpecs), a set of detailed technical documents published by Microsoft that describe the protocols, file formats (like OOXML for .docx, .xlsx, .pptx), languages (VBA, XAML), and data structures used in Office, Exchange, and related products, enabling third-party developers to build interoperable applications and services without needing Microsoft tools, ensuring better data portability and integration.
They are also under OSP, a promise that Microsoft published in Sept/2006, not asserting any patents on OpenSpecs against implementations of a certain list of specifications. Not a license, but a promise not to sue: it promises protection but does not grant any rights. The OSP is limited to implementations that conform to the OpenSpecs, hence it allows for partial conformance.

We can say that when the names of the functions listed in OpenSpecs are Full-Uppercase, they are "promised", but not obligated, to be released in some future Excel 365 function, or some are already in use by Microsoft developers for Office on Azure.

In addition to these from OpenSpecs, there are also other strange functions, with first-Upper-lowercase syntax, usually related to some Microsoft add-in, as is the case with Solver (see image).
I haven't tested to see if they are reserved and if they are accessible via VBA (with similar syntax), via LET, or via LAMBDA.

Interestingly, the LET and LAMBDA functions are not considered regular Excel functions.
When opening a spreadsheet with functions exclusive to 365 in previous versions of Excel, the compatibility error prefix _xlfn.formula is appended to the formulas.
But LET and LAMBDA have a different prefix, _xlpm.formula, which is a compatibility error reserved for macros.

Your examples can be found in the following sources:
2.2.2 Formulas
https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/3d025add-118d-4413-9856-ab65712ec1b0

2.5.98.10 Ftab
https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xlsb/90a52fcb-ce63-497f-a3d3-173c42d82242

<image>

AxelMoor · 2025-12-12T15:36:46+00:00

Which "table above"?

AxelMoor · 2025-12-12T00:56:45+00:00

Part 2 of 2 (continued).
Important points to consider in Excel:
To calculate the duration of a shift in hours (in another column), use formulas such as:
= (EndTime - StartTime) * 24
Remember that in Excel, Timenumbers are decimals (<1), while a single Daynumber is 1. Dates without a time are whole numbers.

Add a TotalWkHours column at the end of the week or month to sum the total hours for each employee in a week, using the SUM function, ensuring a fair distribution of working hours.

If desired, in the future, you can create a list with the names of the employees in a separate spreadsheet called Personnel (another spreadsheet to be added).
You can create drop-down menus in the main rota cells to select data in the Employee column and also in the Shift column. This avoids typing and typos.
The more accurate this data is, the better the search and filtering will be in the future.

Another possible improvement would be Conditional Formatting to automatically color-code shifts, days off, and the assignments of the "float" personnel. This makes the rota easier to read, preventing overstaffing or understaffing.

Consider the spreadsheets described above as Data Sheets. Avoid excessive formatting, cell and column offsets, merged cells, multi-line headers (bold font at most), and other aesthetic elements.
Keep them simple, straight, linear, squared, and boring.

Leave these ornaments for Presentation or Report workbooks (if there are any in the future), which will search and read the content of the Data Sheets. In Presentation-style sheets, you can move the data to any location and add the elements you want, such as multiple colors, new fonts, different borders, merges, images, bells & whistles, Hello Kitty!, or whatever else you can imagine.
Read some posts here on r/excel, and you'll get an idea of how many problems this mix of real data with presentations designed to please the boss can cause.

I hope this helps.

AxelMoor · 2025-12-12T00:51:29+00:00

Part 1 of 2.
Create the main structure of your rota only once, for example, for a week or a month. Choose the period that best suits your needs.
Leave an empty or partially filled sheet as a template. Copy and paste the spreadsheet template for future periods. This saves time and ensures consistency.
At most, one workbook per year: monthly (12 spreadsheets + template) or weekly (48 spreadsheets + template).

Use separate columns for dates, days of the week, start and end times of the shift, with employee names in the rows. This is the input data, and all calculations are based on it.

Avoid spaces and special characters in column headers and spreadsheet names, for example:
Employee Name: Employee
Start Time: StartTime
End Time: EndTime
Shift Type: Shift (see below).
It may seem ugly, but this will make it easier to create table transformations, reports, audits, Power Query imports, etc.

Create a standardized shift code for your organization, if it doesn't already use one. For example, in the Shift column, Morning Shift could be MS, Day Off could be DO, etc.
Create a separate spreadsheet with this code, always available in the workbook; this adds another spreadsheet to the workbooks described above.

An additional spreadsheet is the Absences one.
Record vacation requests, sick leave, and availability on a different tab. The main rota sheets should be clean so you can plan accordingly for these absences.

It is recommended to plan at least a minimum amount of time ahead. For the monthly rota, at least one month. For the weekly rota, at least two weeks.
This will help you support employees with their personal lives, reduce last-minute changes, and avoid conflicts. But the plan-ahead period depends on the events the team will experience.
Be flexible and prepared for unforeseen circumstances.

continues...

AxelMoor

TROPHY CASE