1. Use data validation to control entries in cells – the dialog box is found on the data ribbon. The ‘list’ option is particularly useful
2. Using Vlookup() is a good way to retrieve data from a long list –
3. Good structure is essential for anything other than very simple spreadsheets – keep your inputs, calculations and outputs on separate sheets
4. The magic camera tool is a simple way of combining different tables and charts into a fantastic looking report
5. Use conditional formatting to find duplicate data
6. Use the match() or countif() formulas to find out if something exists in a long list
7. When you learn something new on Excel e.g. a formula or some VBA– save it as an example. This will act as a reference library.
8.Dynamic range names will automatically vary in size as the range they refer to varies – e.g. as you add names to a list then the range name increases in size
9. You don’t need to learn every formula in excel –there are hundereds of formulas but you can solve most situations with about 50 different formulas
10. Formulas are often far more powerful in combination than on their own e.g. Index() on its own its fairly limited but becomes much more powerful when used in combination with Indirect() and Match()
Excel Monkey 