Excel for Windows (Desktop Application)
Welcome to the Excel for Windows (Desktop Application) feedback forum! This is the place for users to send us suggestions and ideas on how to improve. If you think you have found a bug, please send us feedback in-app instead! To do so, please click “File”, then “Feedback”, then “Send a Frown” in Excel. This way, we will get detailed information that will help us better diagnose the problem.
To help us build the best version of Excel ever, we have partnered with UserVoice, a third-party service, to create this site to hear your suggestions and ideas for the next version of Excel. Your use of the portal and your submission is subject to the UserVoice Terms of Service & Privacy Policy, including the license terms. Please do not send any novel or patentable ideas, copyrighted materials, samples or demos for which you do not want to grant a license to Microsoft.
-
named range fix - excel 2016
Named Range fix required due to re-occurrence of previous issues in Excel 2016
This relates to named ranges being lost when duplicate names used at scope workbook and worksheet used between linked workbooks.
http://eileenslounge.com/viewtopic.php?f=27&t=3604&hilit=disappear%E2%80%A6
I have examples of files where the order that workbooks are opened affects whether named ranges appear/disappear or not and hence whether formulas work or return errors.
3 votes -
Anchor period during formula entry.
Upon upgrading to Excel version l 365 16.0.11328, I lost the ability to anchor formula ranges with the period key instead of the colon. Prior to this version, hitting the period would insert a colon so that I could complete a cell range. Could this functionality be added back to the "Transition Navigation Keys" option?
Having started with Lotus 123 back in the 1980's, I still use "Transition Navigation Keys." Using the period during formula entry has become second nature and is embedded in my muscle memory. Please kindly consider this for your more "seasoned" Excel users.
6 votes -
Be able to use user-defined Power Query functions in Excel formulas
Power Query has a few advantages over VBA for creating user-defined functions for use in Excel formulas:
• Power Query produces shorter code that is faster and easier to write. This is largely thanks to Power Query native supporting tables, lists, and records; having many built-in functions to work with these data types; and supporting first-class functions and higher-order functions, which eliminates repetitive code and grants the programmer more expressive power than VBA.
• The Power Query GUI produces code that is closer to ideal than the VBA code that the Macro Recorder produces. Power Query also shows your recorded…
4 votes -
Toggle Relative/Absolute for Structured References
When referencing a table column, it's cumbersome to add the additional square brackets and repeat the column name to make the reference absolute.
It'd be great if the F4 shortcut could also work in this, so toggling
tablename[ColName] --> tablename[[ColName]:[ColName]] --> tablename[ColName], or, when referencing the same row from within the table,
@[ColName] --> [@[ColName]:[ColName]] --> @[ColName]Many thanks,
6 votes -
Directions from 0 to 2pi 1)atan(x,y) 2)atan(E,N) and from -pi to +pi 3)atan2(x,y)
1)atan(x,y)=pi-pi/2(1+sgn(x))(1-sgn(y^2))-pi/4(2+sgn(x))sgn(y)
-sign(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y))) when x=x2-x1 ,y=y2-y1
2)atan(E,N)=pi-pi/2(1+sgn(N))(1-sgn(E^2))-pi/4(2+sgn(N))sgn(E)
-sgn(N*E)*atan((abs(N)-abs(E))/(abs(N)+abs(E))) when N=N2-N1 ,E=E2-E1
3)atan2(x,y)=pi/2(1-sgn(x))(1-sgn(y^2))+pi/4(2-sgn(x))sgn(y)
-sgn(x*y)*atan((abs(x)-abs(y))/(abs(x)+abs(y))) when x=x2-x1 ,y=y2-y1
The above fuctions give the directions in the 4 quadrans for all the pairs of dx and dy.
For dx=dy=0 the result is indefinite .
87 votes -
Excel is wrong in calculating -x^2
If you type in for example =-6^2, Excel gives you 36. This is incorrect. Excel is assuming you mean =(-6)^2. But -6^2 is different.
You can see this when you factorise. -6^2 = (-6)*(+6) = -36.
Whereas (-6)^2 = (-6)*(-6)=36.
Now if you type in = 0-6^2, only then does it give you the correct answer of -36.
This error goes against millennia of algebraic convention.
Plot the graph y=-x^2-7x-12 and then plot y=-7x-x^2-12. These two expressions are identical, yet Excel gives very different traces (the latter of the two gives the correct graph).
Another way to correct -x^2 would…
6 votes -
TRIM within IFS throws #VALUE error on strings longer than 255 chars
The use of the TRIM function within an IFS function throws a #VALUE error, if the string within TRIM is longer than 255 characters:
e.g.:
=IFS(TRUE;REPT("x";256))
if you use 255 instead of 256, the function works fine. If you use REPT alone, there's no problem.
Tested on German Excel version 1910 Build 12130.20390, monthly channel.1 vote -
COLUMNA() returns the letter(s) where as COLUMN() returns a number
column() is great, as usually we want the column NUMBER
BUT when you want the column LETTER(s), something like
COLUMNA()
could return it.
I know you can use
=SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),"1","")
but it's pretty long-winded...to clarify
=COLUMN(AB1) returns 28
=COLUMNA(AB1) returns AB3 votes -
A more efficient way to sum column
Hi there guys,
Let's say I have a simple column with numerical values (check figure A attached).
If I look at the right bottom corner of the Excel window, I'll get a quick summary of the data (average, count, sum).
Oftentimes, I'll need to use this sum elsewhere (sometimes beyond Excel). Thus, I've always wondered why there is no shortcut to simply copy the sum to clipboard.
I understand that I can use something like Autosum to calculate it quickly but I feel this would be an even faster and more efficient way. And that's sort of the point of…
2 votes -
add an easy way of counting unique values, DISTINCT
there is no function currently to Count distinct , there are work around and compounded formulas to write. but that is what they are, work around. add a simple DISTINCT as an option to the COUNT function.
3 votes -
Flexible string to date /time conversion functions
It would be nice to have a function that can accept a string value that represents a date and/or time and an Excel date/time format expression to return the date/time value i.e.
todate(stringvalue, format expression)
totime(stringvalue, format expression)For example, consider the following string values for dates stored in cell A1
20190913 : todate(A1, "yyyymmdd")
19-09-13 : todate(A1, "yy-mm-dd")
19-Sep-13 : to_date(A1, "yy-mmm-dd")for time
091500 : totime(A1, "hhmmss")
9:15 : totime(A1, "h:mm")for timestamp
20190913091500 : to_date(A1, "yyyymmddhhmmss")The existing datevalue() fucntion only recognize "standard" date formats and can make mistake…
3 votes -
Negative coefficient for square : erreur in the excel formula
Hi,
There is a problem in one excel formula. When you use a formula for a second order equation with a negative coefficient for the square term, excel applies the square to the cell value and the negative coefficient. The consequence is that the result is always positive and so result is wrong.
Ex.
=-A1^2, if A1=1,2,3 the result is 1,4,9 and not -1, -4, -9. Using parenthesis does not correct the bug =-(A1)^2 = 1, 4, 9. it is not the case if you do = 0-A1^2 (results, -1, -4,-9). Normally if you want include the negative coefficient in…1 vote -
easy nested formula creation
when you create nested formulas, you need to think very hard how to write the formula correctly with parenthesis but also with the data logic.
Getting both at first shot is impossible!
What if you put calculation step in one cell horizontally. When this is done, you have your calculation right and no syntax error.
Select all cells with simple calculation and use my new nested formula creation. Syntax error free 100% guarranteed! Waiting for MS excel to contact me for further discussion.2 votes -
Remove older Compatibility functions from IntelliSense
If all supported versions of Excel list a function under the Compatibility category, then the function should be removed from IntelliSense. This should help discourage people from developing spreadsheets for unsupported versions of Excel, and it declutters the IntelliSense list.
1 vote -
convert decimals into hours and minutes
Convert tracked hours and minute (Not referring to time of day but cumulative hours of work) into decimals.
1 vote -
Match Function Reverse Order
Add ability for Match function to base match on a reverse order lookup.
e.g. Match(A1,A1:A100,0,REVERSEORDER)
which should start the match from row 100 to row 1.2 votes -
Create WORKTIME as a new function
I would like to have a formula that returns the date and time that is the indicated number of working hours before or after a date and time (the starting date and time). This would be similar to the WORKDAY function, but would also account for business hours.
2 votes -
Named spilled arrays
When defining named ranges using Ctrl+Shift+F3, add a "Include spilled array" checkbox to the label selection dialog box.
This would simply reference the first cell next to the label with the spill operand.
2 votes -
Introduce #N/F as a new ERROR.TYPE besides #/A
With the arrival of XLOOKUP() there is a real need for a new error.type that can distinguish a criteria not matched or not found from a non available value #N/A.
I suggest that a new #N/F i.e. a not found error be added and ERROR.TYPE(#N/F) would give ... 9, i.e. just after error.type 8 (#GETTING_DATA).
Besides ISNA() we could have also ISNF()
One must also think of what would return ISERR() and ISERROR() and what IFERROR() would do.
2 votes -
Evaluate Formula Readability
There needs to be a huge update regarding the Evaluate Formula.
I'm sure those using this feature are not trying to see simple calculations done.For me, I might create a report that has a cell calculate multiple IFs within IFs that have embedded functions.
with this in mind, it would be a huge advantage to the user if the Evaluate Formula has the following upgrades:
able to expand the window.
the window currently is too small and only allows to view simple calculations.color code what is being calculated.
with everything being the same color it is a bit…
4 votes
- Don't see your idea?