What functions is Excel missing ?

Posted on June 9th, 2015 in Huis , Posts by Hui , Quizzes - 215 comments

Today a simple question, What functions do you believe Excel is missing?

In the comments below list what functions you believe Excel is missing?

They may be Mathematical, Text, Date, Statistical, Chemical, Biological in fact what ever you think Excel would be better off to have as an inbuilt function.

Please provide as much info as you think we need to understand the requirements, but don’t write a manual.

 

 

 

Written by Hui...

Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

215 Responses to “What functions is Excel missing ?”

  1. Amey says:

    There should be a color function in Excel which will make easy to analyse.. Something like
    If(color(C4)=color(d4) etc..
    This may provide more controls.

    A good function addition

  2. indesignkat says:

    not really a function, but I'd like to be able to put exclusions in a filter. Now you can filter to include only entries that contain a string, but I'd like to be able to filter to *exclude* entries that contain said string.

    • Deb says:

      That already exists, at least in 2013. Under Text Filter you can select "does not contain" and type in the string.

  3. Ben says:

    I perform a lot of reconciliation work in Excel and need to figure out which figures in a sample set tie back to a single target number.

    I'd like Excel to look at the whole sample of numbers and, in a goal seek style, try adding different combinations of numbers multiple times until it finds the subset of numbers that, when added, match my one target number. Does this already exist as a function? If not, I think that would be a valuable function.

    Ben.

  4. bobhc says:

    Function (no VBA) to concatenate different cells.

  5. Damon Edmondson says:

    DateDiff... its in access, but something similar with ability to group by buckets would be great

  6. Cetico says:

    =MEDIANIF() or =MEDIANIFS()

    Like the function =AVERAGEIF() or =AVERAGEIFS() but to calculate de MEDIAN

  7. SG Kenny says:

    Maxif and Minif, I know you can work around with array formulae but these seem like fairly obvious formulae to build in as standards. Also, some kind of IFERROR/OR combo...so at the moment I use:
    =IS(IFERROR(condition))=TRUE,"Option1","Option2". IFERROR has it's uses but it can't support an either/or scenario

  8. Justin says:

    I'd like the charting functions to include boxplots. Now it is a tedious process to construct boxplots.

  9. hossat says:

    Hi,

    Do not make DATEDIFF as a hidden function.

    The possibility to use the vba function VAL (which returns numbers into a string) in as a worksheet function.

    Hoss

    • Deepak says:

      First VBA VAL converts numeric strings in to numeric like as "1" to 1.

      Xl already have a function Value() for the same.

      • hossat says:

        Thank you Deepak.

        The VBA function VAL() accepts string as input and returns the numbers found in that string. Some examples:
        Val("10 Main Street"). Result: 10

        Val("34 10 Main Street"). Result: 3410

        Val(" 34 10 Main Street"). Result: 3410

        Val(" 34 - 10 Main Street"). Result: 34

        Val("075"). Result: 75

  10. Bryan says:

    A function that provide a Vector array. Something like =Vector(10) would give a Vector array 10 rows long.

    • Desk Lamp says:

      I'd like to see a version of the FIND() and SEARCH() formulae that can find the nth instance of a string rather than the first. The work arounds for this are horrendous!

      For example I want to find the position of the last (or 4th) "\" in "C:\Users\DeskLamp\Documents\Excel_Files"

  11. Jim Amorin says:

    Median calculations in Pivot Tables

  12. Mehmet Gunal OLCER says:

    "WORDING NUMBER" function.
    Converts numbers to texts.
    Such as; 66,513.81 $ to
    sixtysix thousand fivehunderedthirteen and 81% $.

    I already prepared a template for this issue.

  13. Heather says:

    A way to have a volatile function like =TODAY(), that would only calculate when the file is opened or saved, and not every time the workbook recalculates anything.

    My company uses an Excel Add-in that actually addresses the volatile functions (they only calculate when you update THAT particular cell), but it would be nice for Excel to have something of that type as native.

    Perhaps something like: =STABLE(TODAY()) or =NV(TODAY())

    • FormulaDesk has a UDF called FD.Utility.RemoveVolatility(volatileCell, targetAddress)

      It only updates the value of targetAddress when the value of volatileCell actually changes.

      Does that work as you'd expect? Of course, a native version would always be better 😉

      http://www.formuladesk.com

      • Heather says:

        Yes Gareth, that is similar to what the function in our Reports add-in does. But, as you said, native is always preferable.

        Thank you for pointing me to the formula desk site however, I see that it might come in handy for other things. 🙂

    • Chirayu says:

      I guess a workaround to this is adding a macro in the Workbook_Open or Workbook_Close functions.

      Something like: Range("A1").Value = Format(Date, "DD/MM/YYYY")

  14. John Heinzel says:

    I know how to do this through GoTo Special (blanks), entering the formula, and Ctrl+Enter, but Excel should have a function to fill in all the cells below a populated cell with the value from that cell.
    By selecting the column or columns you needed to “fix”, then clicking the button, it would fill in the blanks with the value populated in the non-blank cell above.

  15. azeez says:

    Twrr gain calc function

  16. Dan says:

    For data visualization purpose, it would be good to have a tool in excel to auto create the dashboard based on the selected rows and columns. In the current version (even the latest 2013) there are not much sophisticated tools available, unless we buy 3rd party add-ons.

  17. anne says:

    To have the option to not only put absolute references on a cell but on a whole worksheet

  18. PM Kiran says:

    Dictating data into cells. Making data entry by voice

  19. Josh Williams says:

    I would really like to see a "do nothing" formula for use in long IF statements. I also have co-workers that are irritated that Excel does not have a mode function.

  20. Charles says:

    Which cell is the cursor in/which cell has been selected.

    • There is a 'Crosshairs' button on the FormulaDesk ribbon tab. It draws arrows pointing to the current cell. Is that what you'd expect?

      • Charles says:

        I would like something more dynamic. As an example if a user selects a cell in a table then I want to use conditional formatting to highlight the entire row of the table.

        • Chirayu says:

          Private Sub Worksheet_SelectionChange(ByVal Target As Range)

          Dim CurrRow As String
          Dim CurrCell As String

          CurrRow = ActiveCell.Row
          CurrCell = ActiveCell.Address

          With Cells.Interior
          .Pattern = xlNone
          .TintAndShade = 0
          .PatternTintAndShade = 0
          End With

          Rows(CurrRow).Interior.Color = RGB(150, 150, 150)

          End Sub

    • Gunal Olcer says:

      This is possible by using CELL function in combination with Conditional Formatting. mgolcer@yahoo.com

  21. Nancy Eagen says:

    I would like to see a "Contains" function in Excel, to be able to use in Formulas. As far as I can tell, there is none. I have used the ISNUMBER (SEARCH) in IF Statements though and that seems to work. But a "Contains" seems easier to me. I know you can use Contains in Filters, but I am talking about in Formulas (IF Statements, etc.). Thank you.

  22. Gary says:

    AVERAGEIFS
    MAXIFS
    MINIFS

  23. Sayali says:

    I would like to see a Million () function which would simply convert the current number and divide it by 1000,000. At the moment I have written a simple custom function but I have to copy it in to every file.
    I would also like to see a CountbyColor function. Both these functions will be very useful for dashboards

  24. Heather says:

    John Heinzel's comment reminded me of something else - more of a QAT fix than a function

    For features like Go To Special - which have dozens of options but you may need only one repeatedly - it should be easy to add just that ONE feature (like Go To Special - Formulas - Errors) to the QAT.

    So when I'm working on something and I only need to highlight the error cells repeatedly, I can do that with a single button click.

  25. Sayali says:

    I would like to see a Million () function which would simply convert the current number and divide it by 1000,000. At the moment I have written a simple custom function but I have to copy it in to every file.

    • Jcooper says:

      If you really want to convert it, put 1,000,000 into a cell. Copy it. Select the cells you want to convert. Paste Special. Divide.

      I tend to keep my numbers unaltered but use a custom format [ #,,;-#,,] to display the numbers in millions.

  26. Luke M says:

    1. A concatenate function that can handle arrays/ranges
    2. Some way to reverse contents of cell, equivalent to VB RevStr function

    • Chirayu says:

      Hi Luke 🙂

      I was wondering for point 2. You could always have a sub macro that runs whenever you make changes to current file. It'll copy the existing sheet to a different file so when you try to roll back it'll just copy the data from the different file. Like having multiple version that overwrite each other.

  27. Jay says:

    This is admittedly pretty minor, but it impacts me often: I'd like to see a simple function that converts military time into "standard" time.

    • Chirayu says:

      Do you mean 24Hr to AM/PM?

      You could always directly change the number format or use a formula like
      =TEXT(A1,"hh:mm:ss AM/PM")

  28. Jomili says:

    Disclaimer: I'm on 2010, so don't know if 2013 has it:

    Filter for all colors. I can autofilter for any one color in my range, or for no color, but I can't filter (without a macro) for all colored cells.

    Convert a filtered range to values.

    Pastespecial Transpose Link. I can either transpose, or paste a link. I can't do both at once.

    Protect by cell type, for instance Protect Formulas, or Protect Constants.

  29. Suzie says:

    im very visual and take advantage of the conditional formatting to point out things by color often. One thing I hate is, that although there is a option to filter by color, it only allows you to choose 1 color. There should be an option like in the pivot filter function to "choose multiple" where it turns into check boxes and you can view multiple colors.

  30. indesignkat says:

    This may exist, but I'd like to be able to set conditional formatting to a column and not have it get screwed up by other people using copy/paste in the worksheet. Whenever someone else opens a spreadsheet I've made, I always end up getting called over to explain why my formatting doesn't work anymore. Invariably it's because they've been copy/pasting instead of filtering/sorting and the conditional formatting rules have multiplied like rabbits.

    • Chirayu says:

      You'll need to tell them to paste as values rather than direct copy paste.

      CTRL + C for copy
      ALT + E + S + V for paste values

  31. Chad says:

    I sometimes use date math in conditional formatting functions to help track due dates and whatnot visually, like:

    =DATE(YEAR(A18)+5,MONTH(A18),DAY(A18)-30)>TODAY() which would allow me to see when it's within 30 days of 5 yrs expiration date, in this example. Also, this saves from hard-coding something like "+365". This gets confusing to teach people.

    Perhaps a function like: =DATETRACK(relative_date,+/-yrs,+/-months,+/-days) ... =DATETRACK(A18,5,0,-30) would yield TRUE once the actual date was greater, triggering the same desired effect

  32. Alejandro says:

    CAGR function! I know: http://chandoo.org/wp/2014/04/29/calculate-cagr-using-excel/, but it would be nice to have it in a formula.

  33. ahmed says:

    =CountUnique(range or named range)

    And

    Advanced Data validation
    1. -> List -> Unique entries from range
    2. nested lists: aka list B depends on list A selected entry

    Not a function, but a much needed feature IMO.

    We all know all of the above is possible with VBA or array functions, or some PIvot table /VB combos, yet the idea here is missing direct fucntions

    I suspect Chandoo is planning to create an Addon and collecting ideas/needs 🙂 which is great IMO

  34. Jorge says:

    A ReverseRange function: given an array or vector it will reverse the numbers; no sorting involved.

    Example {1,12,5,3,2} ReverseRange (range) = {2, 3, 5, 12, 1}

  35. David says:

    As opposed to using a bunch of nested If statments, I would love to see something similar to this:

    =SELECTCASE(criteria,value1,result1,...,valueN,resultN)

  36. Doggage says:

    The way Excel handles complex numbers is truly pitiful. It is a hacker's afterthought. It doesn't have to be MathCad, but it could be made an awful lot smoother.

  37. Herbert says:

    MAXIFS(), MINIFS(), MEDIANIFS(); then no need for array functions.
    SHEETNAME(); I know there is a workaround for this using a combination of functions, e.g. =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""), but it would make life easier.
    USERNAME(); for this there are also workarounds, but can we do it KISS (Keep It Super Simple)?

    However, functions are not my biggest challenge in Excel. There are usually workarounds.

    Instead of expanding on the list of functions I would prefer Microsoft to expand on the options for protection of an Excel sheet.

    For instance, I would like to partially protect a worksheet containing an Excel table that is used for data entry. Currently if you protect a worksheet containing a table, the user cannot add new rows in the table. However, if you do not protect the table columns containing formula, the user can mess-up or delete these formulas.

    I also would like to have the option leave a cell unprotected for data entry but protected for the conditional formats for this cell, especially if they contain formulas. Currently a user can remove a cell format via copy/paste.

    Cheers.

  38. Leah says:

    I would like the ability to get chapter style numbers to go in order.

    I want 1.1, 1.2, 1.3 . . ..= 1.9, 1.10, 1.11 NOT 1.1, 1.10, 1.11, 1.2, 1.3 . . . 1.9. This is really infuriating when you are trying to create nested numbering that groups like things together. As an add-on, subtotaling by each place value would also be fabulous.

  39. K-Li-Ch says:

    In my Excel Courses I've found too many people that aren't clear about how to calculate "a percent of" (like in Pivot Tables).

    I think that a function as:

    PERCENTOF(value, One of: Range of all values that add to 100% Or cell with represents 100% of Values)

    will be a great addition to Excel' Functions.

    Carlos M.

  40. Stephen Powell says:

    The functionality that I miss comes from the old LOTUS 123. I still remember the keystroke sequence "/RNT" which stood for "RANGE - NAME - TABLE". This would create a two column table in the spreadsheet starting from the location of your cursor at the moment you entered "/RNT". The table listed all "range names" in column one and in the second column it listed the cell addresses covered by each range (citing the top left cell and the bottom right cell).

  41. Somak says:

    0. BOX PLOT!!!! Rest are nice to haves...
    1. Square root / any fractional power does not operate on Complex Numbers
    2. Eigenvalue and Eigenvector
    3. instead of specific SUMIF, AVERAGEIF and xIFS, a CALCULATE function like we have in ppvt would be awesome
    4. BLANK() and IFBLANK() would be more elegant than
    =IF(x="",t_arg,f_arg)

  42. Paul S. says:

    I would like to see something like an IFTRUE function (similar syntax to the IFERROR function). Currently, if there is a long formula in an IF function that is evaluated to be true, and if we want to return that same value, we have to repeat the long formula. If we need to have long formulas in nested if functions, this makes for an extremely long and confusing formula. An IFTRUE function would greatly reduce the size of long formulas in this case and would be easier to follow.

    Currently, a complicated nested formula looks similar to this:
    =IF(evaluate_a_super_long_formula_if_it_is_true,the_true_value_is_the_value_of_the_super_long_formula_we_just_evaluated, if(evaluate_a_SECOND_super_long_formula_if_it_is_true , the_SECOND_true_value_is_the_value_of_the_SECOND_super_long_formula_we_just_evaluated ,value_if_not_true))

    It would be nice if the formula would be more like this:
    Basic formula: =IFTRUE(logical_test,[value_if_false]) - -- where if the logical test is true, it returns that value without rewriting the formula out again.

    So, in the end, the formula of my nested formula shown above would really be:
    =IFTRUE(evaluate_a_super_long_formula_if_it_is_true,IFTRUE(evaluate_a_SECOND_super_long_formula_if_it_is_true , ,value_if_not_true))

  43. Andrew H says:

    Native support for regular expressions. This would include functions and regexp Find/Replace.

  44. Paul S. says:

    I love the simplicity of the “Iferror” function. I would like to see a simple short formula for if a cell is blank. Something like

    =IFBLANK(value,value_if_blank)

    Where it could plug in a specific value if the cell is currently blank.

  45. Scott says:

    I would love it if I could set a default number format for pivot tables! Every time I create a new pivot table, I always have to change the formatting to make it readable.

    • PeterG says:

      You can use Debra Dalglieshes (Contextures) very useful Pivot Power add-in (very cheap and very worthwhile if you do a lot of work in pivot tables) as I do.

  46. Nolan says:

    Ability to use RegEx functionality for validation, cleaning, and formatting.

  47. Suzie says:

    The comment about conditional formatting mutiplying like rabbits reminds me of another annoyance. So yes, a new rule is created when lines are copied. So what happens is, a clean up is necessary after many accumulate under the rule manager. But instead of being able to select multiple duplicate rules at one time (by holding down the 'control' key one would assume) or an option to 'select all', you sit there and click each one and then click delete for about 10 min.

    • Chirayu says:

      Don't worry lol. I wrote down that you can easily teach people how to value paste instead of direct paste

  48. Rich Halecki says:

    a VLOOKUP to retrieve nth occurrence:
    =vlookup(a1,sheet2!B:D,3,TRUE,2)

    In this instance the last parameter would say find the second value in sheet2 column B that matches the value in A1. If there isn't a second match, it would return NA just like the current VLOOKUP function does.

    I fill in calendars with items using SUMPRODUCT to get multiple tasks for a certain date. It works, but confuses most people when I give it to them. A modified VLOOKUP would be intuitive and easily understood.

  49. Marcy says:

    ISNULL

  50. Eric Lind~ says:

    I need more statistical analysis tools.

    I can perform some basic stat tests in Excel, but advanced functions are not possible without significant programming.

    All of the stat tests in Excel assume the data being analyzed are normally distributed. If this assumption isn't actually valid, then the results are technically skewed. That said, the first thing I'd love is a normality test such as: Anderson-Darling, Kolmogorov-Smirnov and/or others.

    If the data is not normal, I'm forced to use non-parametric tests. Most social science research follows non-normal distributions.

    To give some examples:

    Parametric => Non Parametric

    Pearson Correlation => Spearman's Rho Correlation
    T-Test => Mann-Whitney U
    ANOVA => Kruskal Wallis

    I do know I can convert a Pearson correlation to Spearman by ranking my data and running a Pearson test on the ranks.

    It would also be nice to investigate multiple arrays of data to see if they form clusters or factors. (Exploratory Factor Analysis with Eigenvalues)

    It would be equally nice to be able to test the reliability of factors to produce repeatable results in follow up data. (Chronbach's Alpha)

    Would also be wonderful to have a package which helps perform various Six Sigma statistical tests such as:

    * Capability Analysis (Cpk/Ppk)
    * Control Charts
    * Experimental/Factorial Designs

    Anyway, this last bit is kind of specialty stuff on the Six Sigma end, but the other stats are crucial to all social and/or applied researchers across many disciplines from psychology to business to healthcare to fill in the blank.

    In the mean time, I'll keep using Excel to clean up my data, and Minitab to analyze it.

  51. Michael (Micky) Avidan says:

    TWO FUNCTIONS ARE MISSING:
    JOIN - To return a string created by joining a number of sub-strings contained in an array (incl. all Arguments).
    SPLIT - To return a zero-based, 1-dimensional array containing a specified number of substrings (incl. all Arguments).
    *** Both are well known & used in VBA coding but for the average user (Newbie in VBA) these two functions should be embedded as Worksheets Functions.
    *** If I'm not mistaken both functions work fine in "Google Docs" spreadsheets.
    ------------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2015)
    ISRAEL

    • FormulaDesk has this UDF:
      FD.Text.Concatenate(arg, separator)
      arg: Array of text values.
      separator: The separator (text) to join them with.

      Is this how you envisage the JOIN function working?

  52. Rickemil says:

    Structured function view, this is not a function, but is a related feature, that I'd like to have. Specialy for complex "mega formulas".

    • FormulaDek has a few features to help you with this: the 'Formula Explorer' will display mega formula in a very easy to understand way, with rolled-up sub-results as well.

      Clicking 'Show Formula Steps' will create a new sheet and break the formula into steps.

      Also, in the Formula Explorer you can right-click any reference in a formula to 'import' the formula in the referenced cell into the current formula - just in the display - you can then copy and paste if you desire. This makes it easy to create formula across many cells while developing, but then combine them into a single mega-formula later.

  53. Dave says:

    I'd like to be able to easily format numbers to include 'st', 'nd', 'rd', 'th' example 1st, 2nd, 3rd so on ... I know you can use choose etc but easy format would be sweet!

  54. Hola says:

    Look at other sites:
    * MVP sites, like dmcritchie, etc
    * Google sheets

    =SORT(Array, No, SortOrder, Distinct, Column/Row)
    No=Column or Row number (can be an array)
    with SortOrder -1,0=Not sorted,1 (opt.)
    with Distinct 1,0 (opt.)
    ...

    =CONCATENATEX(Array, delimiters, move)
    with move=0,1,2,3,4 0=no movement, 1=left,2=right,3=down,4=up

    =IMAGE(Array, move)
    Array=one picture/binary image or many
    Google function: =IMAGE(url, [mode], [height], [width])

    =ITERATE(From, To, Step)

    =MERGE(Array1, Array2, ...)

    =FILTER()

    =DISTINCT()

    =FILENAME(), inkl. sheet name

    * DateDiff make it official

    ....

  55. Rob Miller says:

    A way to create a truly blank cell, not "".

  56. Rob Miller says:

    A way to create a truly blank cell, not “”.

  57. John H says:

    The ones I regularly use as UDF which I think should be standard functions:

    IFZERO like IFERROR

    COLUMN ALPHA - return "D" when =column(D1)

    CELL("filename") - should have an optional switch to just show the xxx.xlsx name not the full path

  58. Ali says:

    -A much more simpler way to create waterfall charts

    -The ability to break the Y axis in excel

  59. Ankit says:

    Calculate median in pivot table.
    Count distinct VALUES in a column.

  60. MF says:

    A function for calculating TAX or Commission.

  61. Rob says:

    stdevifs. yeah there are formulas, but a nice clear ifs command for standard deviation and standard error would be nice.

  62. Dave says:

    a weighted average function

    • Kim says:

      I use SUMPRODUCT() for this, but it would be great to have it all wrapped up in one function!

      For instance, =SUMPRODUCT((Weighting_Values_Selection)/Sum_Of_Weight,Values_To_Be_Averaged)

  63. Asheesh says:

    I think a concatenate along with a delimiter which can be used over a range...something like SPECIALCONCAT(Range,Delimiter)

    • Does the FD.Text.Concatenate function in FormulaDesk do what you want? http://www.formuladesk.com

      • Asheesh says:

        Hi Gareth - I have the formuladesk installed in my personal system..though never had the need to use this functionality at home...I am talking more in terms of having this as an inbuilt functionality in excel.. reason, though I have the formuladesk in my personal system but due to compliance reasons I can not have this installed in official system...

        • Hi Asheesh. Thanks for pointing that out. Of course it would be great if all there capabilities were built into Excel! But, in the meantime, what do you think it will take for your company to allow employees to install FormulaDesk? Is there anything I can do to make assure them that it's ok to install? Anyone I should contact?

  64. Karthik says:

    Dear sir,

    I think kindly develop the logistics and customs related function in excel.

    Regards,
    Karthik

  65. Khalid NGO says:

    I think there should be built-in excel function (like UDF SpellCurr) for converting amount in words.

    the following also
    MAXIF
    MINIF
    so we can avoid using combination CSE with MAX(IF(.... MIN(IF...

    and:
    ConcateIF
    ISDATE

  66. nishad says:

    =Countif(Colour)
    =Countif(Red)
    =sumif(colour)
    =averageif(colour)

    we need a formula to count or take the total based on the format if the cell.

  67. Yash says:

    Excel does not contain calculating Root of a number,
    Which makes it difficult, altough we can apply it indirectly

    Measures to be taken to find nth Root of a number directly, so it is easy for finance, Mathematicians and physicians too

  68. Dau says:

    1. Unique Count in Pivot Table
    2. A quicker way to convert crosstable into Flat tables (Currently I use Index - would prefer a quicker one button function)

  69. Darin says:

    Unique function or distinct function without using advanced filter
    Auto filter by columns
    Easier way to make a dynamic range vs. using offset
    More built in statistical functions similar to what the popular excel add ins provide
    Counted items next to the text in slicers. Ex: South (5), North (3)

  70. Mehmet Gunal OLCER says:

    MAXIF, MINIF

  71. ANAND says:

    1)It is possible to consolidate data appearing in different sheets in non unique form. After having done any changes in any of the sheets as to values or form, currently there is limitation as the consolidation is not done. It will be better if we refresh the data then data consolidation should be done automatically.

    2) Countif ( By Color )
    Countifs ( By Color )
    Minif ( By Color )
    Maxif ( By Color )
    Maxifs ( By Color )
    Averageif ( By Color )
    Averageifs ( By Color )
    Sumif ( By Color )
    Sumifs ( By Color )

    3) Financial
    Simple Payback Period
    Discounted Payback Period

  72. Alan Murray says:

    I have loads of functions Microsoft could add.

    1. Count unique values
    2. An IfColour()
    3. Count the No of Words in a Cell
    4. A Sports category with functions for crickets stats inc Duckworth - Lewis, calculate head to heads, unique league table rankings.

  73. Syed Mohammad Ali Quadri says:

    Figure cannot convert into text (English)

  74. Ramesh says:

    I think "CONCATENATEIFS" is missing

    • Ramesh says:

      To concatenate based on a condition, I'm using a VBA function, however it'd be great to have this as a default function within Excel

  75. Bruce says:

    =isformula(A1)

  76. Ilyas says:

    I think the SUMIF(), COUNTIF() functions in 3D format so that they can be used across sheets will be very useful.

  77. mma173 says:

    isdate()
    to be able to check if a cell contains a date.

    • Chirayu says:

      Assuming Column A has dates/string & column B has formula

      =IF(ISERROR(DATEVALUE(TEXT(A1,"dd/mm/yyyy"))),"False","True")

  78. The Imposter says:

    I might be missing something, but I find extracting strings from texts difficult. If they could have a function which conditionally retrieves a string from a string, that would make things easier. In other words something that means that I don't have to nest several find()'s within a left(), mid() or right(), would be nice.

    Otherwise the ability to have a multidimensional lookup ( or at least 3d) stacked between sheets.

  79. Dias says:

    A formula that returns a letter of the column instead of number (AA, not 27).

  80. jraju says:

    Hi,
    If suppose one has to type the hours minutes seconds , Even though, the cell is formatted to accept time format, as chosen, you have to type in the format to accept the same. For example, if you choose, 01:30pm format from excel to a cell, then you have to type in the same format to accept the data in correct time format.
    I want a funtion, where if you just type 130 it should be made to the above 01:30 pm, without typing the : inbetween the 1 and 30. Could you do something?

    • Rudra says:

      Hey,
      There is a workaround for this...Make use of excel's 'Automatically insert decimal' and replace "." by ":" using find and replace.

      But this works only for hh:mm format.

      Regards
      Rudra

    • Chirayu says:

      Assuming A1 has Time in 24Hr format e.g. 130 = 1:30AM / 2300 = 11:00PM. And B1 will have formula
      Also assumes you only uses hours & minutes. Not seconds.

      =IF(LEN(A1)=3,(0&LEFT(A1,1)&":"&RIGHT(A1,2)&" AM")*1,IF(LEN(A1)=4,TEXT((LEFT(A1,2)&":"&RIGHT(A1,2))*1,"hh:mm AM/PM"),"NA"))

      • jraju says:

        Hi,
        I mean a function or button for doing this. Do not you know, how much this takes if one has to manually insert : in hour format in each and every cell.
        If decimal could be had why not try a function for this. I am not tech savvy to do

  81. Doug Gabbard says:

    I would love to be able to put the power of Solver in a function. Instead of running the Solver add-in manually whenever I make a change in my model, I would like a function that automatically recalculates an optimal solution.

  82. What about a formula that magically makes a beer appear on your desk?

  83. The Stig says:

    I'd love to see a function that builds on Excel's ability to average data. I'd like to see a function that calculates an average monthly value based on known starting and ending points.

    For example, if I want to calculate the average monthly print volume of a printer in my office. I know how to build the formula manually. I'd like to see a built in function to complete this task.

    Analysis Start Date: 3/8/15
    Analysis End Date: 5/13/15
    Starting Page Count: 10,000
    Ending Page Count: 15,000

    Current Formula:
    (Ending Page Count - Starting Page Count)/(12*(Analysis End Date-Analysis Start Date)/365)

    Answer: 2,304

  84. Mike says:

    MINIFS (similar to AVERAGEIFS). And perhaps MAXIFS, but I simply had a need for MINIFS. I now solved it through an array formula (which I don't like to use....)

  85. Sudhir Gawade says:

    We have formula for networkday similarly excel should have networking hours formula.

    • Sudhir Gawade says:

      syntex should be like this
      Networkhrs( start datetime,end datetime,login time , logout time ,holidays)

  86. Brian says:

    LastNumberInRow
    LastValueInRow
    FirstNumberInRow
    FirstValueInRow

    same for columns

  87. Michael says:

    Not sure if this was mentioned yet or not (saw the MedianIf) ... but PercentileIf(s) and QuartileIf(s)

  88. Laurent Bosc says:

    - add ability to use range name in pivot table calculated fields.
    - add ability to use in pivot tables, calculated fields with a custom calculation (ex %of column) in other calculated fields
    - add our own icons set in conditional formats
    - vba : when driving pivotfields/pivot items or slicer items, have a way to quickly unselect all elements (Currently we are obliged to loop on all elements and unselect each element, which could be long in case of big list. There are workarounds, but it is tricky)
    - slicers : filter a slicer! (Like with a standard filtered field). For example i want to only see slicer lines like FR*. In qlikview this function is very helpful
    - ability to count unique values (formula)
    - new built in event mouseover cell

    • Jeff Weir says:

      @Laurent: Check out my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

      It turn out that you can very quickly hide all but one PivotItem programatically if you make a temp copy of the Pivot, make the field of interest in the temp into a Page field with .EnableMultiplePageItems set to False, and then hook it up via a slicer to your original Pivot. This forces the original PivotField to have the same filter setting – just one item visible. But it doesn’t make that original Pivot have the same layout. So the original pivot can still be say a Row field where you can then merrily make additional items visible.

  89. Somak says:

    SWITCH-CASE (or select-case, whatever)

    For that matter - why not a custom function builder without VBA? Like we can store our custom charts - should be able to store our custom functions as well!

  90. Kamesh63 says:

    "Search as you type" in slicers. If there is a long list in a slicer, this would add great functionality to reach the desired value.
    Similar functionality in Validation Data Lists is also needed.

  91. Andy says:

    Not a function as such, more functionality. I would like to be able to click on one chart and format paint all the styles to another. I am still using Excal 2007, so this may be available by now. I know about saving a chart as a template!

  92. Rudra says:

    Along with excel I also work in google spreadsheet and am fond of google's function like filter, sort, split etc. Would be nice if excel incorporate these function in next version.

    Not function but I would also like to have:
    1. Filter by multiple colors

    Regards
    Rudra

  93. Rudra says:

    Sorry I forgot to add,
    Extract Number only and text only from alphanumeric string would be great.

  94. Nick says:

    I think the filtering functionality could be made more user friendly.
    If there was an option to invert the selections in the filter list it would be faster.
    Also, when there is a huge list of data being filtered, and you have to scroll all the way to the bottom to get "Blanks". It would help if it was at the top.

  95. Jake Collins says:

    3D scatter plots
    negative time

  96. Gnana Prakash says:

    Count unique Function

  97. Laurent Bosc says:

    I forgot one:
    Bubble charts : be able to change color of bubbles with negative values

  98. Tayyab Hussain says:

    Hi-

    One function that I'd like in excel would be to convert numbers to words. This function is used in many places and like invoicing, Salary letters sent to banks for salary transfer, etc,etc.

  99. Ray says:

    CONCATENATEIF(S)

  100. ira haron says:

    a "put" function.
    places a value (from a dashboard, perhaps) into any designated cell row, column, any page, any other file.

  101. ira haron says:

    a "put" function

  102. Not new functions as such - just the ability to use all existing functions in 3D (like basic functions SUM, AVERAGE etc can) to calculate through sheets. SUMIF and SUMIFS won't work in 3D.

  103. Hi Team,

    there should be something like

    =sumproductif()
    =reverse()
    =NumToText()
    =running() ( for i.e. 1st,2nd,3rd)

    - Ability to create search box (like google) in particular cell
    -complex formulas can be used in Pivot Table

    Thanks
    Istiyak

  104. Dave Dudus says:

    It would be of great value to chart fanatics like myself to have Excel provide the functionality of specifying a column of points used to color the data points. The colors would be representative of either discreet values or a user-specified binned range of values. As an example, suppose you want to plot a person's age (column A) versus income column (b) versus the year of car that they drive (column C).
    The points would be colored by either the discreet values in (C) or a user specified bin of years, say, for a 5 year binning, 1960-1965, 1965-1970, etc. I frequently do this using scientific data (e.g. rock porosity vs. permeability discriminated by radioactivity) with my own developed tools, but it sure would be nice if Excel offered this internally.

  105. Rob says:

    Better scenario manager. Would like automated outputs for several scenarios across several input variables. Want to fully define format of output to corporate format. Scenario manager, data tables or offset work around doesn't cut it!

  106. Sam says:

    Conditional formatting improvement to easily apply format across a row (without special tricks).

  107. Hola says:

    =IFS(A1="Apple";123;A1="Banana";456;789)

    Same as
    IF A1="Apple" THEN 123
    ELSEIF A1="Banana" THEN 456
    ELSE 789

    • James Brown says:

      This is already present with nested If functions
      =If(A1="Apple",123,IF(A1="Banana",456,789))

      If you want something more complex then use Index/Match to lookup a value in a range from a key in another range.

  108. Mcan says:

    People will allways copy/paste ALL instead of paste values.
    So the formating and the conditional formating will be gone evantually.

    That is why I always hard code the conditional formating into the macro's worksheet activate event.

    What I need is a worksheet protect option which will lock the paste all, and which will only allow to paste values. Voila half of my issue has been solved.

  109. PeterG says:

    After copying a complete row range or a whole sheet sheet, I would love to have Row Widths as a Paste Special Option to work just like Column widths or a paste special fit to size.

  110. Samuel Cruz says:

    more missing options that I will like to see are:
    - To be able to insert picture within the cells.
    - Under number format, to be able to see more than 20 digits, not only 15, even if our number start with a 0, be visible.
    Example: 08456891070060510302
    If you enter it in a cell you will see: 8456891070060510000
    - a command button to be able to insert multiple checkboxes in the column range that we specify.

  111. GFC says:

    Issamecolor(A1;B1)
    Issamefontsize(A1;B1)
    Issameshading(A1;B1)
    Issameborder (A1;B1)
    Issameconditionnalformattingrule(A1;B1)

  112. Gary Lundblad says:

    Better indirect conditional formatting that adjusts for added columns. I've tried removing the "$"s and they just come back. Invariably someone will add a column to one of my worksheets and viola, there goes my CF- now it's behaving very strangely. Also the ability to do indirect by row, not just be column. There may be a way to do this latter part, but I'm not sure.

    Conditionally color and name worksheets

    Easier way to work with times.

    Add a picture conditionally, perhaps with a VLookup. I have seen ways to do it, but they are very complicated.

    A simpler shortcut for Format Paint.

    A function that would build smaller lists (subsets) of a larger list, based on specified criteria. For example, if there was a long list of car owners with make, model, owner's name, etc... I want smaller lists created based on make for example, a list of all Toyotas, another of all Nissans, etc...

    Merge two columns of data with a specified separator, and without having to concatenate first and then paste special over the top.

    The ability to have multiple filters on a page. I'm talking about how you can filter a list, but then if there is another list further down on the same worksheet, and I filter that list, the upper list is no longer filtered.

    Add Rounding to the formulas of a lot of cells at once, "Round(###,2)"

    Conditional Print Features that would change the orientation based on defined parameters, and also change to "Fit Sheet on One Page," etc...

    Protect and unprotect multiple sheets at once.

    Advanced character remove or replace.

    Some of these options are available via add-ons, but I would prefer they were native to Excel.

    Conditionally hide or unhide worksheets

    Conditional print areas, as well as conditionally print to a variety of printers.

    Conditionally group or ungroup, as well as expand and collapse.

    A wizard for creating formulas to pull data out of pivot tables. These formulas can get very long, based on the number of criteria, so a wizard to step through creating them would be great.

    Replace one color, or formatting, for another in a selected or specified range.

    Conditionally format part of a formula cell, i.e. a cell that pulls different text based on other conditions, and some of the words in those text strings could be bold, a different color, or underlined.

    A calendar that could be inserted in a cell that would allow for selecting a date rather than typing one. I know there are ways to do this in VBA, but I'm not sure why there isn't a simple one in Excel.

    More advanced protect features, like allowing hide/unhide, or other things, in a protected sheet, or protecting/unprotecting based on user name, or other criteria.

    I could probably come up with more, but these are some off the top of my head, and it's probably enough.

    • Jeff Weir says:

      Gary: That's a great list. I've got some questions regarding some things on it:
      Invariably someone will add a column to one of my worksheets and viola, there goes my CF- now it’s behaving very strangely.. Yeah, I've had that happen soon. I have a plan to create a better CF dialog that will let you save and restore CF formatting. The current dialog is crap, as per my recent post at http://dailydoseofexcel.com/archives/2015/06/08/easy-way-to-back-up-cf-formats/

      A function that would build smaller lists (subsets) of a larger list, based on specified criteria. For example, if there was a long list of car owners with make, model, owner’s name, etc… I want smaller lists created based on make for example, a list of all Toyotas, another of all Nissans, etc… . Would a combination of PivotTables and Dynamic PivotTable Names do what you need here? I'm working on an add-in for creating bulletproof Dynamic PivotTable Names, as per a proof-of-concept I posted sometime back here at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/ . Maybe that would also handle your requirement for A wizard for creating formulas to pull data out of pivot tables. . I'm not entirely sure what you mean by These formulas can get very long, based on the number of criteria, so a wizard to step through creating them would be great.

      Re The ability to have multiple filters on a page. I’m talking about how you can filter a list, but then if there is another list further down on the same worksheet, and I filter that list, the upper list is no longer filtered.. Excel Tables (introduced 2007) let you do that, unless I'm misinterpreting what you want.

      • Gary Lundblad says:

        Thank you for the follow-up questions Jeff. I took a look at your proof of concept on structured references, but the workbook I downloaded didn't seem to act like yours in the example. It was set up slightly different, with different states showing, and the number at the top was "1," and stayed "1" even if I added more cities.

        I consider myself fairly competent with Excel, but alas pivot tables are one of my weakest points, so I am not sure if structured references would solve my problem or not. I can elaborate more one what I'm trying to do, but perhaps that should be outside of the forum unless you think another very long post is okay.

        What I mean by "these formulas can get very long" is this

        "=IF(ISERR(GETPIVOTDATA("Discount Amt",'License Data'!$A$6,"Category",$Q$1,"Country",$B$2,"Day",DAY($B18),"Month",MONTH($B18),"Year",YEAR($B18),"Currency","US Dollar","Is New",$E$5)),0,GETPIVOTDATA("Discount Amt",'License Data'!$A$6,"Category",$Q$1,"Country",$B$2,"Day",DAY($B18),"Month",MONTH($B18),"Year",YEAR($B18),"Currency","US Dollar","Is New",$E$5))"

        That is one of the formulas in my workbook, and there are literally thousands of these in the entire workbook.

        Thank you for the information about Excel Tables and filtering. I don't use tables that often, but I suppose I could probably use them whenever I want to filter two different sections in the same workbook.

        Gary

        • Jeff Weir says:

          Gary: Use the new IFERROR funciton to cut the size of those formulas in half. And by 'new' i mean Excel 2007 or later.

          You should use Tables any time you have a block of structured data sitting somewhere. Then you never have to repoint your formulas or charts again, because Tables have built in dynamic ranges that are handy and bulletproof.

          Re the structured pivottable reference download, I see that the formula in A2 was screwed up. Sholud have been =COUNTA(Sheet1.Jeffs_Pivot.Sum_of_Kilos)

          Put that in, and you should see it works fine.

  113. Gary Lundblad says:

    Okay, I have another one.

    It would be really cool if you could insert windows within a worksheet to other places in the workbook, or even other open workbooks, that would allow you to view and edit those areas right on screen. And it would be really helpful if they were floating so they could be drug around. I know you can view other areas of a workbook via the camera, but I would also like to be able to edit them.

    • Hui... says:

      @Gary
      Open your file
      Goto the View Tab
      New Window
      Now resize the new window to be smaller than the original window

      As you type in the new window it is immediately updated in the main window

  114. AA-Ron says:

    =Multivlookup() should be built in.

  115. Tom Smith says:

    1 - Add recognition of color option to the counting type functions (like: Countif/s Averageif/s Sumif/s)
    so we could have:

    countif(a1:a200,"Blue")

    2 - word manipulation functions, where a "word" is defined as delimited by a space or character specified in the function
    something like:

    wordcount(range,delimiter)
    word(range,word #,# of words)
    findword(find_word,within_text,start_num)
    searchword(find_word,within_text,start_num)

    3 - unique processes including a count and a listing function
    something like:

    uniquecount(range)
    uniquelist(range)

    4 - A Select function rather than using series if nested IFs
    something like:

    Select(logical_test_1,value_if_true,logical_test_2,value_if_true,logical_test_3,value_if_true,...,value_if_false)

    5 - native calendar drop down added to the data validation options

    6 - Add Pivot Table names/fields so they can be used similarly to named ranges, and additionally be able to grab the subtotal information in a similar way

  116. KIRAN S N says:

    Dear Sir,

    Please find herein below formula. I need help for the below.
    When I enter 13.5% it has to show 1.75% But it shows only 1.00%.

    Please help me. I am using Excel 2007.

    =IF(ISBLANK(C10),"",IF(C100.12,C100.125,C10=0.131,C10<=0.136),(((C10-0.13)-(0.005*(ROUNDDOWN((C10-0.13)/0.005,0))))*1.5)+0.01,"Drying")))))

    (If Moisture is upto 12 % - No deduction
    If Moisture is between 12 % to 13% - straight deduction
    If Moisture is between 13.1 % to 13.5% - x 1.5% deduction
    If Moisture is above 13.6 - Qty after drying).

  117. Neelkanth says:

    Hi all

    Currently, it's next to impossible to dynamically freeze panes or row or columns i.e., to create a stack flow horizontally or vertically.

    Yes, it is claimed it could be done through VBA coding, but having an option under Freeze Panes would make life a lot easier.

    Check out this link if you do not follow the function I'm talking about: http://stackoverflow.com/questions/11266012/dynamically-freezing-panes-in-excel

  118. Jorge says:

    Count unique

  119. […] Read the full article here: What functions is Excel missing? […]

  120. Ruslan says:

    Hi, everyone!

    I think that good function could grouping sheets into sections. Sometimes when I analyse couple of companies I need couple sheets of (revenue, costs, SG&A and others), so if i could group my sheets into sections (company 1, 2, 3 .... X), include into sections necessary sheets it probably could give me better navigation.

    Ruslan

  121. Falak Niaz says:

    Excel is missing Number2Text function as a builtin.
    for example

    Rs. 5,342.00
    =Number2text(5,342.00)
    Result = Five Thousand Three Hundred Forty Two

  122. Michael S says:

    A few things that I know there are workarounds, but would be good if they could just be part of the vlookup formula

    Vlookup that works both right to left as well as left to right, perhaps with a negative number in the formula to tell it which way to go.
    In Vlookups if the result is an empty cell choosing if it gives a blank cell or a 0.

    A more radical idea would be to have independent page-lits or something similar in dashboards. If you have a number of data reports in a dashboard, they often don’t have the same number of cells, cell widths etc. Ideally you could design the pagelit you want and then putting this where it fits in the dashboard, without warring about how this lines up with cells above or below it. Currently this is usually done with merging cells, but that often is a pain when you try to change things etc.

  123. steve says:

    RSS root sum square
    RMS root mean square

Leave a Reply