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.

Easily remove blank rows in Excel data

Another wonderfully simple tip from Chandoo at his Pointy Haired Dilbert blog:

Removing blank rows from other peoples' spreadsheets is something that has taken up my time on a number of occasions. I love this simple tip (that works in Excel 2003 as well as 2007) that reduces this to a few key strokes to remove the lot.

See the tip.




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.

The importance of personality in communications - A lesson learned from Freddie

I learned a useful lesson yesterday regarding the importance of putting personality into my professional communications.

Subscribers to this blog will know that yesterday, when sending out an email letting you know about Spreadsheets by Email's January 20% off deal, I managed to call you all "Freddie"! This was the result of a copying and pasting error, entirely of my own doing.

I spotted the error and immediately fired off a new email to all subscribers apologising for my stupidity. What happened next surprised me. This brief apology email solicited many more replies than probably anything I have ever sent out. I started to look at the reasons for this and came to the conclusion - it clearly came from a human being!

Training as a Chartered Accountant, I was brought up to write professional reports and letters (it was the 1990s!). Clearly professionalism has its place but things have changed - with the amount of information flying into everyone's Inbox these days, personality is the only thing that makes any communication stand out. I like to think that there is some personality in my emails to individuals but when sending an email (or a blog post) that is aimed at a group I tend to get a bit dry and professional.

Following my little "Freddie" incident however, I have hopefully learned a valuable lesson. In future I will try to ensure that there is more of "me" in my emails and blog posts. You will let me know when I slip up and revert to being boring and professional, won't you?


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.

What effect has the bad weather had on your business?

Here in the UK we have had one of the worst spells of weather for many years, with deep snow and freezing temperatures for weeks. The media seem to have gone overboard on the disruption caused but I would like to hear some real stories of how it has (or hasn't) affected your business.

In my business it has not stopped anyone getting to work, but I do get the impression that clients are taking longer to get moving after the Christmas break than would be normal. Whether this is due to staff absences as a result of the weather or simply the distraction of constantly talking about it, I don't know.

Please tell me your experience in the comments to this post.

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.

Give yourself the gift of time in 2010

I'm always telling my son that time is our most precious possession - once a minute has passed, we can never get it back again. As the old saying goes, "Time is money", but it is much more valuable than that if you think about it - you can swap your time for money, but money can't buy you any more time (to horribly misquote The Beatles).

We may not be able to buy more time but we can have a similar effect, by saving time on regular tasks, so that we can use it for something more productive or enjoyable.

Why not start the year by automating a regular job or task to save you time for the whole year.

I have done some quick calculations to show three simple ways to gift yourself a whole extra working day (8 hours) in 2010 (assuming you act now!):

Automate a monthly job that usually takes 40 minutes;

or

Automate a weekly job that usually takes 10 minutes;

or

Automate a daily job that usually takes 2 minutes;

How hard can that be? Think of all the jobs that you do that could be speeded up, or eliminated, with a little thought and maybe a simple spreadsheet. I bet you could gift yourself more than a day. Just think, saving a 10 minute job a day would save you a working week!

If you think you'll need a spreadsheet to automate the job, you don't even have to do it yourself - we can do it for you at Spreadsheets by Email.

The key is that you look at what you are spending these precious minutes of your life on.

Here's to a happy and productive year!

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.

January Poll - Which version of Excel do you mostly use?

With the impending launch of Office 2010 this year, I thought it would be interesting to know what versions of Excel readers were currently using.

Please use the poll at the top left of this blog to let me know.

Personally, I have both 2007 and 2003 installed but I still do most of my development work for clients in Microsoft Excel 2003, because of the backwards compatibility issues from Excel 2007. Excel 2007 is much more powerful, particularly with large datasets, but the potential problems if the client is using 2003 are too great to justify changing my default position. I also have many corporate clients who have not made the move up to 2007 for similar reasons.

I also have the Beta version of 2010 but have not had the time to properly play with it yet.

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.

Fun Christmas Quiz - in Excel

As everyone is winding down to Christmas, I thought something a bit light-hearted and seasonal was called for.

I have spent Thursday afternoon creating an Excel spreadsheet containing a seasonal (and entirely pointless) quiz - so you can have an equally unproductive Friday! Get all of the answers right and reveal a Christmas picture and message from Spreadsheets by Email.

Pass it round the office and try to do it without Google!

Get your Christmas Quiz (in Excel) at the link below:

Download your Seasonal Fun Christmas Quiz

...and Merry Christmas

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.