SME Blog

My favourite Excel function

I'm a self confessed Excel junkie.  I've tried other spreadsheets, but to me they just don't have the flavour of the real thing.



The Excel vlookup function is rather useful.  If you look above you can see it in action. 

In this example, we have a table of sales budget figures for each month (in green).  By changing the month number in cell D2 (in red) it gives the corresponding sales figure for the month in cell D6 (in blue).

To do this all that is required is the vlookup formula as follows:

=VLOOKUP(D2,F3:G14,2,false)

So what does that actually do?  The function looks up the value in D2 and looks for it in the first column in cells F3:G14.  It then returns the corresponding value in the second column.  Our our example the second column is the sales budget column.  Adding "false" just ensures that Excel looks for exact matches.

Our example is quite simple, but it does give a taster for what is possible with the vlookup command

Excel guru

Simon Hurst of the ICAEW's IT Faculty is undoubtedly an Excel guru.  He has some excellent Excel tips that he shares online at his new blog, the Beancounters Guide to Technology.

The benefit of the online approach is that Simon has, rather neatly, included animation as well as screenshots and text.  Well worth a look.

Technorati Tags: small business medium sized business sme smb blogging accountants  sme-blog  excel

Excel tips

I should confess now. It's a problem I've had for some years now. I'm an Excel junkie.  It's hard to kick the habit as it's everywhere I go.  I've tried other spreadsheet brands, but they just don't have the depth and finesse of Excel.  To me they just seem like Excel Lite.  No wonder we're all using it.

I came across an Excel article in PC World called Who Knew Excel Could Do That? Ten Top Tips.  It's good, very good in fact.  If you are an Excel junkie or just an occasional user it is well worth checking out. If you're time poor then at least try tip 5.

N.B. Just in case you miss it (I did initially) tips 6-10 are on a second page clickable at the end of the first. 

Technorati Tags: small business medium sized business sme smb blogging accountants  sme-blog

Philip Woodgate

Sorting & AutoFilter

Continuing with the Excel Tips series this video is on sorting and autofilter.  Using these tips can make working with data much easier.  In this case I've taken a slice of my ITunes music collection and used it to show how you can sort it and filter it as you wish.  You may not like the music, but you should find the video is helpful.

Click here to download the Windows Microsoft Media version

This works with Microsoft Media player.

Click here to download the Real player version

You can download a free Real player here (right-hand side download screen).

Click here to download the Quicktime version.

You can download a free Quicktime player here.

Click here to download the Excel spreadsheet used in the video

Philip Woodgate

Excel is a registered trademark of the Microsoft® Corporation.

Technorati Tags: small business  medium sized business  sme  smb  blogging accountants  excel

Look me up

There is only one numerical software package I see practically all SME's use. It's everywhere. What's it called? Click here to find out and come back and let me know if you agree. If you do then you may   find the below video useful. It's on an amazingly useful function called Vlookup. The Vlookup function has saved me a lot of work over the years

In the Excel help screen it explains it as "Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table."  That may make perfect sense to you, but watch the video to understand how it actually works in practice.

Click here to download Microsoft Media version (1mb) 

This works with Windows Media player version 10.

Click here to download Real Player version (2mb)

You can download a free Real player here (right-hand side download screen).

Click here to download Quicktime version (20mb) 

You can download a free Quicktime player here.

Click here to download the Excel Vlookup example spreadsheet

Philip Woodgate

Excel is a registered trademark of the Microsoft® Corporation.

Technorati Tags: small business  medium sized business  sme  smb  blogging accountants

Press play to view the small screen version as below: