Frequently Used Funtions and Methods

[ExcelChampion]

I took it to mean worksheet functions only...

But in that case...

I use the FIND function quite often...nearly every few minutes when working with a spreadsheet.

Pivot Tables are invaluable.

Autofilter saves tons of time...so does Sort...

How about Freeze Panes? That's a good one that I find many users don't know...

 

[brian.wethington]

I took it to mean worksheet functions only...

But in that case...

I use the FIND function quite often...nearly every few minutes when working with a spreadsheet.

Pivot Tables are invaluable.

Autofilter saves tons of time...so does Sort...

How about Freeze Panes? That's a good one that I find many users don't know...

Great, someone push his button again... we may never get a word in edgewise. Just kidding of course....

Really though, I use sumproduct daily but for every time I use sumproduct I find myself using pivot tables 2 or 3 times. I can't forget about text to columns though. Can't forget about Conditional Formatting..... There really is no an end to this list for me, is this unfortunate or fortunate?

 

[Lewiy]

How about Freeze Panes? That's a good one that I find many users don't know...

Now freeze panes is one of my favourites, good call

 

[SydneyGeek]

Another feature I use heaps is Names.

Sheet-level for workbooks that consolidate multiple worksheets with the same structure but different numbers of rows

Dynamic for anything that will expand or contract, either for use in formulas or with charts and pivot tables.

And just plain vanilla names too.

Denis

 

[Fazza]

I guess my work these days is handling database-style lists. I am forever using pivot tables. They are fantastic for the work I do.

General functions are mainly the routine ones, SUM would be used the most. Or on a filtered list, looking at the sum in the status bar.

Array type formulas such as long winded SUMPRODUCT I virtually never use. There was a time some years ago that I used them briefly, but they are not good for the tasks I do.

I do use query tables a bit and it makes doing some things so simple. A little bit of SQL and you can simply do some fancy selection, filtering, tables joins (instead of VLOOKUP), etc, etc.

Also SQL for consolidating from multiple workbooks (even closed ones) or worksheets.

 

[Patience]

I have never. Ever. Used SUMPRODUCT. But I am determined. I believe it can help me, but I have never found a practical use for me. Maybe I should make it my task for this week. By Friday I will probably wonder what the blue rinsed hel* I did before!

My most frequently (over)used funtions are (in no particular order):

CONCATENATE
VLOOKUP - (sometimes using HLOOKUP in the column number variable slot)
IF
ISERROR - (often these are together)
ISBLANK

Other ones I use reasonably frequently - TRIM, LEFT, RIGHT, MID, PROPER, COUNTIF, MONTH, DATE.

This was an interesting thread. Thanks.

 

[Domski]

I have never. Ever. Used SUMPRODUCT. But I am determined...

Have a look at this, it helped me to get my head around how they worked:

http://www.mrexcel.com/wwwboard/messages/8961.html

I have to analyze very large lists quite a lot and find 10,000 sumproduct formula take forever to calculate on my poor local government PC.

One way round this I've found is a quick Pivot Table followed by Advanced Filtering the original list to produce a summary and then a cheeky GETPIVOTDATA to extract the info from the pivot table into a nice list again. Might seem a bit weird but works for me.

And although I'm still learning the basics of VBA you can't beat a few macros for automating repetitive tasks that would have taken me hours a couple of years ago.

Dom

 

[Jon von der Heyden]

On my part I more commonly use:

• 1: SUMPRODUCT - used typically within established financial models where a Pivot Table just doesn't cut it.
  2: SUMIF - which I prefer over single criteria SUMPRODUCT
  3: COUNTIF - as above
  4: VLOOKUP - where range lookup = 1 to eliminate unnecessary use of multiple IF's (hence also CHOOSE and others)

Though I like array formula, I try to avoid where ever possible because they seem to slow my models up.

Agree with SydneyGeek, names make things so much neater and easier to audit. Dynamic ranges are also particularly helpful.

BUT....

As I do alot of workbook sharing amongst colleagues, many choose to use alot of fill colour :x so SUMCOLOR / COUNTCOLOR etc. becomes especially necessary. But I have opted to use XLM4.0 macros over the UDF's posted on all the Excel sites as it makes it easier to share with other users and easier for so many others to understand.

Ever so grateful to Schollar for introducing these to me.

 

[Jon von der Heyden]

Oh and...

From my favourites:

Schollars suggestion to avoid an unnecessary loop: http://www.mrexcel.com/board2/viewtopic.php?t=192430

Clever use of Excels big number: http://www.mrexcel.com/board2/viewtopic.php?t=207653

Clever use of MOD to perform calculation on every other row / column: http://www.mrexcel.com/board2/viewtopic.php?t=241353

Working with TAX year: http://www.mrexcel.com/board2/viewtopic.php?t=210382

And I also like smart use of COUNTIF to help identify unique items from those that have been duplicated, though there are many other techniques this is one I commonly use. E.g. =COUNTIF($A$1:A1,A1)>1

I also typically install alot of validation in my models to make avoid user errors and to help them understand (I like the input feature).

Not to forget use of conditional formatting for further diagnostic checks.

As a 'typical' user (i.e. not one of these mathematicians and incredible coders), I like to use as much of the standard features intuitively to help make my models more user-friendly