What functions is Excel missing ?

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.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

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.

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.

The usual preference is to be able to pass a range of cells to the function. Current CONCATENATE function makes you input each cell individually. Would like something like:
=BetterFunction(Delimiter,SomeArray)

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

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.

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.

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.

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.

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

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.

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 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.

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.

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.

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

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.

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.

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).

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)

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.

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.

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.

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.

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.

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.

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

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.

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?

Excel 2013 addresses #1
Load range into the data model and then you will have the option for Distinct Count when choosing Value Field Settings > Summarize Values By > Distinct Count (at the very bottom below Varp)

Thanks for clarifying Justin. I am currently using Excel 2010 and havent had a chance so far to work with Excel 2013. Its really good to see this option now in Pivot table. Maybe one of these days I'll see if I can get to work on 2013. Thanks again.

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)

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.

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.

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.

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?

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

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.

- 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

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.

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!

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.

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.

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!

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.

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.

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.

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.

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: 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)

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.

@Neelkanth I've added the ability to freeze columns and rows to the FormulaDesk add-in. You can freeze and columns and they will be frozen on the right-hand-side of the worksheet. Similarly, rows will be frozen at the bottom of the worksheet. http://www.formuladesk.com

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.

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.

At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 1,000+ articles and tutorials on using excel, making better charts and workbooks. Read more.