What would be in your Excel Survival Kit?

This weekend, I went on a bushcraft training weekend with my fifteen year-old son, building our own camp in the woods, etc.

This was an amazing experience and is still fresh in my mind as I write this blog post. So, it got me thinking, "In the Excel jungle, what would I have in my survival kit?"

I thought I'd ask myself the following question (and I'd love to hear your answers too, in the comments) - If I could only have three functions/features in Excel, what would they be? I am going to assume that basic mathematical functions like adding and subtracting and SUM are there, but what are the top three additional features that I wouldn't want to be without?

So, here are my top three (in no particular order):

1. VLOOKUP - Having Excel look up information from tables elsewhere in the spreadsheet is an incredibly useful facility. I did, however, think for a while about this as I would have preferred INDEX and MATCH, but that would have taken up two of my choices!
2. PivotTables - I use them all of the time and they remove the need for formulae in many cases.
3. The IF statement - being able to get Excel to essentially make decisions based upon the information it has available is too useful a feature to give up!

Please let me know yours in the comments.

By the way, although you've missed the discount, you can still get Mynda Treacy's Excel Dashboards course until it is taken down on Thursday night.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Quickly name and access ranges

A quick reminder before we start, that the 20% off deal on Mynda Treacy's Excel Dashboards course expires on Thursday. So, if you want to take advantage, hop on over there now while you've still got the chance.

This week, I've got a simple tip to quickly name and access ranges in Excel.

You can use the Name Manager on the Formulas ribbon to define range names, however I will show you a much quicker way to name fixed ranges, as well as go to them and highlight them once they exist.

This tip uses the Name Box at the top left of your screen, just below the ribbon to the left of the formula bar.

This box normally shows the cell reference of the active cell (the cell that is currently selected). If you click in cell B6, for example, you will see the name box shows "B6".

While you are in cell B6, click in the Name Box and type the name "Fred". Click in another cell and the name box will change to the cell reference of the cell you have clicked in. Now click back on cell B6 and notice what happens. The Name Box says "Fred"! You have successfully renamed B6, Fred!

You can do the same thing with a range of cells. If you highlight the range C5:E10, for example, and type "Bill" in the Name Box, click in another cell and then re-highlight the range C5:E10, the Name Box will show "Bill" again.

Note that these names follow the same rules for named ranges named in any other way - most notably, they should not include any spaces.

What's more, if you click the dropdown arrow next to the Name Box, you will see Bill and Fred are both listed there. If you select one of them from the list, you will go to that range and highlight it. This works from any of the other worksheets in the workbook too.

You might not find too many reasons to name cells Bill or Fred, but if B6 was renamed TaxRate, then that might be useful. You could use the name TaxRate in any formula (instead of B6) and if you need to check what the TaxRate is, or change it, you can go to that cell by selecting it from the dropdown in the Name Box.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Find a code in among other text

I've got a little bit of news before we get into this week's post.

Regular readers of the blog will know that I am a big fan of Mynda Treacy's Excel Dashboards course. Well, Mynda is making it available once again, for a limited period. You can get it here - and if you sign up by the 24th July, you will not only get a 20% discount, but I will give you a copy of my Introduction to Excel Pivot Tables video course, absolutely free.

So, if you've missed out before, click over there now, before you miss out again.

OK. On with this week's post.

Have you ever had a problem where account codes, or suchlike, are embedded in larger sections of text, when you need them in a field of their own (for lookups or pivot tables for example)?

Well, here's a little trick that will work in certain circumstances.

I had an issue yesterday with a client who needed to analyse transactions by project code, however the project code did not have it's own field in the system and was entered, along with a number of other pieces of information in the description field. What's more, the code could appear anywhere within the description field.

The key to making this work, is being able to identify something within the code that will not appear elsewhere in the text. In my client's example, all project codes started with PROJ.

We can use the FIND function for this:

=FIND(find_text,within_text,[start_num])

The FIND function returns the position of find_text within the larger text, within_text. The optional argument, start_num, allows you to specify at what position to start looking (if this is argument is not entered, the FIND function will start at the beginning of within_text).

Say the cell A1 contains the following text:

"Expenses for PROJ13245 but that is not the only text here"

the function

=FIND("PROJ",A1)

will return 14, the position in the text of the first letter of PROJ.

If the project code is always 9 digits, then we can use the MID function with FIND to pull it out.

The MID function is structured thus:

=MID(text,start_num,num_chars)

This returns the num_chars of text from text, starting at start_num, so:

=MID(A1,14,9)

would return our project code, if we already knew that it started at position 14, however we can replace the 14 with our FIND function, to calculate the start of the Project Code, so:

=MID(A1,FIND("PROJ",A1),9)

will pull out the 9 digit project code from the text.

But what if the length of the code was variable, well, if we can assume that it will always be followed by a space, for example, we can use find again to identify the end, and this calculate num_chars.

We can find the position of the space at the end of the project code, by using the following FIND function:

=FIND(" ",A1,FIND("PROJ",A1))

All we have done is used our earlier calculation of the start of the project code, FIND("PROJ",A1), as the start_num value in a new FIND looking for the space. This will return the position of the first space following the letters PROJ.

To calculate the num_chars we need for our MID function, we just need to deduct the start position, i.e. FIND("PROJ",A1). So,

=FIND(" ",A1,FIND("PROJ",A1))-FIND("PROJ",A1)

will return the length of the project code to replace the 9 in our MID function:

=MID(A1,FIND("PROJ",A1),FIND(" ",A1,FIND("PROJ",A1))-FIND("PROJ",A1))

which will return the project code, PROJ13245.

And that's it. It obviously won't work in every situation, but there are plenty where it, or a version of it, will.

Good luck!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

ICAEW Twenty Principles for Good Spreadsheet Practice Launch Highlights (Video)

For those of you who couldn't make it to the launch of the ICAEW's Twenty Principles for Good Spreadsheet Practice, I thought I'd share with you a highlights video that the ICAEW have made of the event.

It will be like you were there. You can even download the Principles document itself, and I would highly recommend that you do, as it is a great starting point for checking (and influencing) the quality and integrity of spreadsheets within your business.

Here's the video:

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Twenty Principles for Good Spreadsheet Practice - get your copy

In earlier posts you've heard me talking about the ICAEW's Twenty Principles for Good Spreadsheet Practice.

This is a set of principles that, if you apply them, should significantly reduce the problem of spreadsheet risk, as well as make your spreadsheets far more robust and efficient.

The principles are now officially launched (to an audience of around 200 people at an event at Chartered Accountants' Hall in London on June 17th) and free to download (I'll give you the link at the end of this post).

The principles themselves are the work of the ICAEW IT Faculty Excel Community Advisory Committee, of which I am privileged to be a member.

The principles in brief are:

1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
2. Adopt a standard for your organisation and stick to it.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of know¬ledge and competence.
4. Work collaboratively, share ownership, peer review.
5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
8. Design for longevity.
9. Focus on the required outputs.
10. Separate and clearly identify inputs, workings and outputs.
11. Be consistent in structure.
12. Be consistent in the use of formulae.
13. Keep formulae short and simple.
14. Never embed in a formula anything that might change or need to be changed.
15. Perform a calculation once and then refer back to that calculation.
16. Avoid using advanced features where simpler features could achieve the same result.
17. Have a system of backup and version control, which should be applied consistently within an organisation.
18. Rigorously test the workbook.
19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
20. Protect parts of the workbook that are not supposed to be changed by users.

If you click here, you can read more about the committee and the background to the principles but, more importantly, you can download the Principles document itself, including examples and advice on application.

Just follow the link, and click on the link "Twenty principles for good spreadsheet practice" in the first sentence.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".