## Tuesday, 9 February 2010

### Top 5 Tips to make Excel Spreadsheets work for you

I thought I would share with you an article I was kindly asked to write for Malcolm Gallagher's Achiever Newsletter. It's a nice summary of what I think are the top 5 most useful things to learn to get the most out of Excel. I'd also love to hear any you feel I have missed (please use the comments below).

Here is the article:

Top 5 Tips to make Excel Spreadsheets work for you

You probably already use Excel in your business to some extent, but very few businesses even scratch the surface of what it can do for them. I build spreadsheets for businesses throughout the world and below I will show you the top 5 things you should know to really start making Excel work for your business:

1.      Learn Pivot Tables - The number one spot has to go to learning how to create a Pivot Table. A pivot table allows you to analyse any data you have, whether you have keyed it in or pulled it from a database such as your accounting software, in seconds. Master these and you will amaze yourself with how you can master your data. There is not enough space here to show you how to do this, but here is a FREE VIDEO that will take you through it step-by-step.
2.      Find the Insert Function (fx) button - This little tip pushed pivot tables close for the number one spot because it allows you to use any function Excel has, without knowing it first. All you need to do is click the little fx button to the left of the formula bar and you can browse all of Excel’s formulae. It will tell you what the formula does, how to use it and then help you to fill in the arguments step-by-step. To see how it does this, just click on it, or see this blog post for more detail.
3.      Use dropdowns for data entry – The information you get from a spreadsheet is only as good as the data you (or your employees) put in. It is very easy to use Excel’s Data Validation to create a drop-down list that only allows you to enter data from that list. I have created a blog post that covers how to do this in detail.
4.      Learn the VLOOKUP formula – You can access this via the Insert Function (fx) facility discussed in point 2. VLOOKUP allows you to look up data from a list in a spreadsheet. For example, you could enter a product code and automatically show the description and price from a separate price list. Use the fx button for more detail or watch the FREE VIDEO on my blog.
5.      Learn how to record a macro – a macro is a short program attached to a spreadsheet that carries out a particular task. The good news is that you do not need to be a programmer to create one – when you have a repetitive task you can simply ask Excel to record you carrying out the task and create a macro to do it automatically next time. You do this by selecting Tools > Macro > Record New Macro… Again, there is a short FREE VIDEO on my blog.
Hopefully these tips help you to tap into how Excel can help your business, just question whenever you are manually manipulating data whether it can be done in Excel – it almost certainly can.
For those of you who don’t have the time, or the inclination, to build the spreadsheet you need, why not let my team and I do it for you at Spreadsheets by Email?

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

1. I think for the slightly more advanced, Excel becomes MASSIVELY more powerful and dynamic (which helps when building financial models) if you start using the OFFSET, INDEX and MATCH functions. Also, NAMING RANGES can add real benefit as well.

2. Stephen

You're right. OFFSET in particular is great for looking up budget figures for example.

In fact I think I should do a post on OFFSET.

Thanks

3. I like the example you use for Vlookup. So many instances Vlookup is used (incorrectly) for record management when it is just a simple table lookup as you have indicated.

4. tgroom57, thanks for the compliments. VLOOKUP is incredibly powerful when you understand what it is for.

5. I do agree that these tips are great I personnaly use them all the time. Also one thing that help me a lot when when working with formula is the Formula Evaluate (Fx), this tool is found in the Auditing Tool Bar which I have always open as part of my custom tool bar.

In addition, the use of list for common entries not just reduce error but increase productivity as data can be enter faster.

6. This is so well written, commendations for this piece.

lose weight fast

7. Nice comment but I would like to add as one of the basic
skills : sort & filter (on different columns and levels) apart form copy paste
These make huge chunks of data manageable.

8. Great things about Knowledge. Some great benefits of knowledge usually are not simply restricted to prada replica, yet also includes those that always broaden their particular knowledge in recent times. David Moschoyiannis, far better identified inside the design market since Sean Moschoyiannis, can be a movie director and also shareholder of just one regarding Australia’s major design organizations, D. You. Simon Building contractors. Yet this individual didn’t arrive at in which he could be today basically away from pre-graduate knowledge, yet decades regarding continuing knowledge and also studying. Through his / her job gucci replica provides taken care of jobs of each level and also coming from each market, which usually directed your pet being advertised coming from getting venture director to be able to design director in mere 7 decades and also employed movie director in mere 6. His / her extended and also successful job will be data adequate in which knowledge is essential not merely inside obtaining people initial design careers yet rendering it to be able to well-respected opportunities inside market. Getting Knowledge on the chanel replica. In most of the coming into the particular design market one of the most challenging portion will be obtaining options to get knowledge on the extremely commence. Typically, interviewers won’t acquire graduates by using an chanel replica schedule as a result of bare minimum income louis vuitton replica, and a lot other programs can’t manage incorporating paid out location pupils on the folks these have chosen.