Thanks for participating in the 30XL30D challenge, that ended yesterday.
My favourite Excel function in the series was INDEX — I learned a few useful tricks while researching that one! How about you?
30 Excel Functions Ranking
As I promised, here is a list of the 30 functions, with their rank in your voting.
01 – VLOOKUP — looks for a value in first column of table, returns another value from same row
02 – ADDRESS — creates address as text, from row and column numbers
03 – INDIRECT — returns the reference specified by a text string
04 – INDEX — returns value or reference, based on row and column number
05 – OFFSET — returns reference, of specified size, offset from starting reference
06 – MATCH — searches for value in array, and returns its position, if found
07 – CHOOSE— get a value from a list, based on an index number
08 – AREAS— returns the number of areas in a reference
09 – LOOKUP — returns a value from a one-row or one-column range or from an array
10 – CELL — gives info about cell formatting, contents and location
11 – FIND — finds text within another string, and is case sensitive
12 – HLOOKUP — looks for a value in first row of table, returns another value from same column
13 – TRANSPOSE — changes vertical ranges to horizontal ones, or vice versa
14 – ERROR.TYPE — returns a number based on error type
15 – EXACT — check for an exact match between text strings, including upper and lower case.
16 – SUBSTITUTE — replaces old text with new text, in a text string, and is case sensitive
17 – CLEAN — removes non-printing characters from text
18 – REPLACE — replaces a specified number of characters in a text string, with new text
19 – SEARCH — looks for a text string, within another text string, and returns its position
20 – CODE — returns a numeric code for the first character in a text string
21 – T — returns the text from a value, or an empty string for no text
22 – INFO — get information about the operating environment
23 – N — Returns number based on a cell’s value
24 – COLUMNS — returns the number of columns in an array or reference
25 – HYPERLINK — creates hyperlink in a cell, based on link location and friendly name
26 – TYPE — identifies the type of value in a cell, by returning a number
27 – TRIM — removes extra spaces from text string
28 – CHAR — returns a specified character, for the number entered
29 – FIXED — round a number and return the result as text
30 – REPT — repeats a text string a specified number of times
More Excel Functions
You can find more videos and examples for Excel Functions, on my Contextures website.
1 — How to Sum Cells – Start with the SUM function, then try SUMIFS and more!
2 — Count All or Specific Cells – Do a simple count, or count based on criteria
3 — How to Do a VLOOKUP – Find a lookup item in a table, such price for specific product
4 — Lookup With Criteria – Use formulas to get values from a lookup table, based on multiple criteria
4 — Combine Text & Numbers – Use formulas to combine values text and numbers from different cells
For me INDEX is undoubtedly the most versatile function available in Excel and for that reason alone should really be #1 !
Thanks Luke, and thanks for all your contributions during the series.
Thanks for doing this Debra. It’s quite an undertaking!
I’d like to second Luke’s comment. As I was looking at the rankings I was thinking that Index is my #1. My appreciation of it has grown hugely in the last few months, especially in the area of replacing VLookups with Index/Match.
Thanks Doug! It was a big task, and rewarding to learn all the new formula tips and tricks.
Thanks a lot Debra.
The whole series of 30 detailed Functions was marvellous documented. Eventhough there are over 3xx functions in total, ther were in my humble opinion the most commonly used.
Carry on with these ideas
Regards
Francisco Romero