Following our Excel Community launch competition, we are pleased to announce the results from our judging panel, chaired by Excel Community blogger Simon Hurst. We had over 30 entries submitted by Twitter, LinkedIn and through the post on IT Counts and we would like to thank everyone for their entries.
In first place and winner of the Kindle is an entry on IT Counts from Jonathan Mole. Congratulations and thanks to Jonathan for his entry.
# | by Jonathan Mole on 21 July 2011, 23:55
We cannot live without Excel, it lets you create masterpieces, but alas if you come back to old spreadsheets, you can easily be lost in the formulas. You can see what it is doing, but not the why.
Here is a wonderful trick that can save you hours of banging your head against the monitor in frustrating attempts to remember.
It is also invaluable when working in a team on the same spreadsheet, as it means that any team member can pick up the spreadsheet and see the logic of your calculations.
This can turn into real financial savings in an organisation.
The secret is just ‘N’ function. Normally you would use it to convert non-number values to numbers, but if the function cannot turn the value to a number, it returns zero.
This allows us to add comments without destroying our formula.
We selected 5 other entries from the list who will win a copy of John Tennent's recently updated book, 'Guide to Business Modelling'.
Entries from IT Counts
# | by Krishna Karki on 11 July 2011, 21:08
When you need to repeat some rows (at top) or some columns (at left) on the subsequent pages while printing, you need to go to page set up from page layout tab (and not from page set up when you are in print preview). On the page layout tab (Excel 2007), in the page set up group, click on print titles and this will give you the option to specify the rows and columns to repeat. It is tricky that the same page set up dialog box when you are in print preview wouldn’t let you select the rows and columns to repeat.
# | by Alex Millington-Hore on 21 July 2011, 20:36
There are so many hints and tips that improve the use of Excel and the flexibility of spreadsheets that providing one is so challenging. I know the competition is restricted to a single entry but I want to share some other tips before providing the one tip to enter the competition:
Explore the INDIRECT() function to help you create formulas that allow you to reference cells on other spreadsheets / tabs based on entering the name of that tab into a cell on the sheet you are on.
If you have Excel 2007 or later then use the 'Tables' feature to create the data set for the basis of charts and graphs. That way when you add data to the bottom it will automatically update the range and update the chart. Alternatively use the OFFSET() formula combined with MATCH() to create dynamic named ranges that allow you to update charts (for example adding the next month of data to a chart) without having to edit the series or axis in the chart properties. Example here http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
Create pivot tables based on dynamic named ranges (e.g., using the INDIRECT() or OFFSET() function) and use a formula to determine how many rows are in your data table so that every time you update the data table you simply click refresh on the pivot table to update and include the new rows. No need to update the pivot source or update a named range!
Clean up data before trying a VLOOKUP() or INDEX() and MATCH() by converting everything to either numbers (using /1) or text (eg., by using the formula =""&A1) and then if using text make sure you apply TRIM() first so that you remove the superfluous hidden spaces at the end of a text string that are put there by exporting data from 'green screen' business systems
All these are fantastic tips however the one thing that has changed the way that I use excel and my entry for the competition is learning how to integrate Visual Basic with Excel and most importantly how to create custom functions. A custom function is one that performs a task that you regularly want to do but for which there is no simple current Excel built-in formula to perform. Examples might be:
a function that displays the formula you have typed into a cell,
a function that returns the address of a cell either with or without the $ symbol that you can then use in an INDIRECT() formula or similar. etc...
The point is that once you have combined the infinite possibilities, flexibility and algorithms that you can write in Visual Basic with the power of Excel you realise that there are ways to produce information, reports and data that you never previously realised were possible in such a short period of time - certainly if they are reports that you replicate on a regular basis. Suddenly nothing that you want to do is impossible any longer.
For the Microsoft tutorial to help you get started visit http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx and unlock the hidden potential of one of the world most powerful data analysis tools!
# | by Andrew Hucknall on 27 July 2011, 11:12
The top tip that I would offer is to always get someone else to check your spreadsheet or model. As someone who used to build financial models, and now reviews other people's model (known as a 'model audit'), I know that all models contains errors, even those model built by well-known modellers, banks, advisers and accounting firms.
As a minimum, I would always suggest that you get someone else, such as a colleague, to look at your model, asking questions about its purpose and assumptions. If the model is reflecting a key decision, consider getting the model independently reviewed by a 'model auditor'.
Documenting your assumptions in an assumptions databook is a good practice to get into as it will provide an easier handover if someone else is to take on your model. If you have the necessary software, it is worth running a spreadsheet map that will show the consistency of formulae in a spreadsheet's design. An unexpected change in the formulaic structure somewhere in a row will indicate a potential error that can be investigated.
I would also recommend that you think beyond the formulae, and review the output of the model. Basic sense checks could include checking that a balance sheet balances. Analytical review techniques that you might consider using include charting key outputs to look for any unusual trends, manually reperforming key calculations to see if you get the same answer as the model is showing and checking all the information is flowing through the model, e.g. ensure the total depreciation charged agrees to the original capital expenditure, or that financing is fully repaid.
Although most tips focus on how to use the functions of Excel, from my experience, the best tip is to ensure that the spreadsheet or model reflects what you want it to achieve, and that you can rely upon the outputs.
Entries from Twitter:
David farman - http://twitter.com/#!/UkDavef
#icaewexcel tip#1 Spot overwritten formulas, press CTRL G click Special button and select Constants radio button - they will highlight them
Matt Emblem - http://twitter.com/#!/mattemblem
Colour alternate rows-highlight all, Format,Conditional Formatting, Formula Is "=mod(row(),2=1" Format, Patterns, choose colour #icaewexcel
Delete Blank Rows - highlight, Find@Select, GoTo, Special, Blanks. Delete in Home ribbon, Delete Sheet Rows #icaewexcel