The Top 5 Functions in Excel

I think somebody at Excel owes me a job because I have become an expert at it.  I have intimately spent the past 2 weeks (100+ hours) intensely building 3 different spreadsheets for mid-season reports (UWIC Archers, Team South Wales and Cardiff Celts).  Within that time, I have read both “Excel 2010 for Dummies” and “Excel Formulas and Functions for Dummies”.  Although I have previous spent over 150+ hours building volleyball spreadsheets (see previous blog post), I did learn Excel can do so much more.  In honour of this learning, I have decided to write down the best 5 functions of Excel (the last 2 are crackers!).  Drum roll please…

5.  Inserting shapes and text boxes into charts

This is an useful but simple thing for explaining pertinent points in the chart with a text box and arrow.  I was also able to insert lines that show the minimum level of performance.

4.  Relative and absolute references

The formula in cell “A3” is “=A1/A2”.  Copy and paste down and cell “B3” will automatically be “=B1/B2”.  This is called relative referencing.  However, sometimes you want a cell to refer to one cell only, e.g. the number of matches played for per game average calculations.  You can just do this by putting in an “$” in front of the reference cell number, e.g. “=A1/$A$1”.  It will mean cell “B3” has this formula “=B1/$A$1”.  This is also useful in other situations.

3.  Conditional Formatting

It’s a neat little thing to allows you to visually see the cell values within the cells themselves.  This can be done with data bars (my favourite), colour scales or with icon sets.  There can be other specific formatting but I haven’t got around to exploring that properly.

2.  Paste Special

This has seriously saved me hours of work!  I actually learnt this from a fellow analyst (Peter Clark and for the record, Josh Bryan did know it too).  Paste Formula allows you to copy and paste a formula without the cell formatting.  There will be situations when I had set up a table aesthetically with specific borders (left, right, top or bottom) and fill colour cells (I had strips of alternating white and blue).  Then I realised a formula was wrong.  Before I had to write in the right formula and copy and paste.  It meant the cell formatting was also copied so I had to change the cell formatting back to what it was.  Again, a brilliant function that has saved me hours of work (I will try and use iShowU application to demonstrate this).

Paste values is brilliant too if I wanted to use the data from one spreadsheet and copy it to another.  A normal copy and paste job will only copy that location of the copied cells.  Thus, if the original spreadsheet was deleted or moved, the data in the new spreadsheet would be lost.  Paste values copies the value itself.

1. “If” Formula

Seriously the best thing about excel!  The “if” formula allows you to perform two operations depending on a true or false assumption.  So a huge bane was getting a #DIV/0! error.  It means the division cannot be done if the the dividing number is 0.  So a hitting percentage cannot be calculated if no attack attempts have been made.  An “if” function allowed me to put “0.00” if there are no attack attempts or perform the calculation if there is 1 or more attack attempts.  It also allowed me to leave the cell blank if you wanted to.  For example, if it was a 3 set match, you don’t want zeros in your dashboards because it looks untidy.

It also allowed me to put more “if” formulas into an “if” formula.  It can become complex if other formulas are used.  The most complex one I have used is this:

=IF(BL40+BM40=0,” “,IF(OR(L40=0,M40=0),”0.00”,-L40/M40))