We launched the new Excel Community on 29 June and to celebrate this launch we’ve decided to hold a competition on making better use of Excel.

**In order to be in with a chance to win, share with us a comment below your favourite tip that you’ve found in Excel. What is the one thing you’ve learnt that has changed the way you use Excel? **

**How to enter**

You can enter in two ways:

- On this blog – you must login or register to comment below and share your Excel tip with us. Any anonymous posts will not be eligible to win.
- On Twitter - use the hashtag
**#icaewexcel**to tweet and share your Excel tip, any entry tweets without the hashtag**#icaewexcel**is not eligible to win.

The ICAEW IT Faculty will then pick the winners. You will be contacted via your community or Twitter details, therefore any entries made anonymously will not be eligible to win.

For more information please read the terms and conditions for this competition prior to entering.

**Timeframe**

The competition will commence on 10.00 GMT, 4 July 2011 and end on 17.00 GMT, 31 July 2011.

**Prizes:**

- Grand prize -1x Kindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display
- Runner up prizes - 5 x copies of the latest third edition of ‘Guide to Business Modelling (Economist)’ from John Tennent who is one of the Excel Community partners.

**Questions**

You can get more information and how to join the Excel Community at www.icaew.com/excel. Any questions please email excel@icaew.com.

Good luck and look forward to reading your hints and tips on Excel.

## Comments

If you need to enter data into a block of cells in Excel, select the whole block before you start then, when you press return or enter at the end of each entry, the selection will move to the next cell down or right depending on whether you selected a column or a row. This is irrespective of your normal Excel editing options.

Wonderful as this tip is, for some deeply unfair reason, apparently I'm not eligible to enter the competition, so it's still worth having a go! I too look forward to reading your best Excel tips.

Good luck!

My top tip is using the INDEX function in combination with MATCH as an alternative to V(orH)LOOKUP.

It is much more flexible....e.g. with VLOOKUP you can only look at data to the right of the lookup value. With INDEX & MATCH you can look left or right.

Use Conditional formatting to highlight values particularly when performing some kind of what if / sensitivity analysis or with large blocks of figures, to find the largest, smallest, positive, negative etc.

And a little warning I'd like to add. When using the latest version of Excel, be careful if using a trackwheel mouse - when you hover over the newly designed menu bar, it scrolls through the options.

If your accounts production software is capable of comma separated file (.csv) import then create a list of postings in Excel and import to the accounts software – this allows you to make use of the functions of Excel which are not available in the accounts production software.

For instance by entering postings into Excel

data can be summarised by use of a pivot table for report purposes

autofilters can be applied to review postings made to a particular account

creating 'useful' narrative is much easier in Excel (by the use of formulas)

We cannot live without Excel, it lets you create masterpieces, but alas if you come back to old spreadsheets, you can easily be lost in the formulas. You can see what it is doing, but not the why.

Here is a wonderful trick that can save you hours of banging your head against the monitor in frustrating attempts to remember.

It is also invaluable when working in a team on the same spreadsheet, as it means that any team member can pick up the spreadsheet and see the logic of your calculations.

This can turn into real financial savings in an organisation.

The secret is just ‘N’ function. Normally you would use it to convert non-number values to numbers, but if the function cannot turn the value to a number, it returns zero.

This allows us to add comments without destroying our formula.

e.g.

=SUM(A2:A4)+B5+

N("A2-A4=web income, B5 monthly income. Formula calculates total income. J.M")I recommend if you are working in a team to initial your formula so, the team knows who to speak with if needed.

Hope this serves you as well as it has served me over the years.

J. Mole

My few would be-

1 Custom views-save different views and print area set ups and easily flick between the two

2 Pivot tables linked to the backend of accounting systems and then used to create dynamic formatted reports or having pre-formated other sheets with VLOOKUPS/HLOOKUPS in

3 With reference to 2 above-having a macro on the tab when selected to automatically update the dynamic report when you click into it (right click on tab and view code-paste a simple update pivot report macro in here)

4 Inserting formula into calculated fields of pivot tables also very useful

5 SUMIF AND COUNTIF also very good

One of my favourite formulas is =CONCATENATE (TEXT1,TEXT2) because it has such a ridiculously long name and the same can be acheived with =TEXT1&TEXT2

Is there another use for this?!

Sometimes I think if I could train my nine year old in excel he could do my job without finishing school,going to university and getting a degree and MBA

Andrew Miller

PS If I have won please send me my kindle before 8th August as I am going on holiday then!

The most useful tip for reviewing spreadsheets is to be able to double click a cell and it takes you to the source of that cell - effectively working backwards until you see how it all fits together. you could use the auditing tools but this is a quick method that I use all the time, hundreds of times a day.

Hope everyone find this useful!

Graham

Very useful tips noted in the thread thus far.

As a previous auditor working on Electronic Audit Files I found the Hide and Un-Hide commands very useful, especially when utilising client supporting schedules which contain a lot of detailed information (general ledger accounts, among others).

These commands are very powerful if built into a Macro which can then be inserted into a 'Shape'. For manager/partner review the detail can be hidden and the working paper schedule simply notes the objective, audit work done and conclusions with Two separate shapes titled Hide and Un-Hide.

Should reviewers require the detail they can simply click the Un-Hide Shape and vice versa.

Thus, to implement:

a) Select

View - Macros - Record Macrob) Whilst recording Select the rows/columns you wish to Hide and

right click - hidec) Select stop recording

d) Reverse for the Unhide Macro

e) Insert a

Shapefrom theInsertmenuf) once inserted

right click the shapeandassign macroAs a minimum, I would always suggest that you get someone else, such as a colleague, to look at your model, asking questions about its purpose and assumptions. If the model is reflecting a key decision, consider getting the model independently reviewed by a 'model auditor'.

Documenting your assumptions in an assumptions databook is a good practice to get into as it will provide an easier handover if someone else is to take on your model. If you have the necessary software, it is worth running a spreadsheet map that will show the consistency of formulae in a spreadsheet's design. An unexpected change in the formulaic structure somewhere in a row will indicate a potential error that can be investigated.

I would also recommend that you think beyond the formulae, and review the output of the model. Basic sense checks could include checking that a balance sheet balances. Analytical review techniques that you might consider using include charting key outputs to look for any unusual trends, manually reperforming key calculations to see if you get the same answer as the model is showing and checking all the information is flowing through the model, e.g. ensure the total depreciation charged agrees to the original capital expenditure, or that financing is fully repaid.

Although most tips focus on how to use the functions of Excel, from my experience, the best tip is to ensure that the spreadsheet or model reflects what you want it to achieve, and that you can rely upon the outputs.

Andy Hucknall

You want a tip ? How about an

undocumented feature?I stumbled across this back in the 1990s. It may have been as early as Excel 4 … but I’m pretty sure that it has been replicated in all subsequent versions … and I’ve never seen it documented.

If, like me, you have a relaxed working approach which enjoys “one-handed” mouse-spreadsheeting whilst nursing a cup of coffee (or perhaps something stronger !) in your spare hand, then you may get annoyed when you have to move between mouse and keyboard and back.

Context sensitive menus, “drag-and-drop” and “drag-fill” are all very well, but it takes a keyboard press to get to the bottom/top (or left/right) of a column (or block) of data … doesn’t it ?

Not any more, try this :

The cursor has five components; the drag-fill handle and four sides. Now we all know what the drag-fill handle does … and of course the four sides are anchors for “dragging and dropping”. However, you may be unaware that if you

double-clickany of the four sides, the cursor will “jump” to the end of the current block of data.This is the equivalent of the keyboard command “Control-Arrow” (where “arrow” is one of the up/down/left/right keys). So if you are in a block of data, it will jump to the end of the block in the direction chosen. If you are in an empty cell, it will jump in the chosen direction to the first piece of data (or not move, if there isn’t any data at all in that direction).

Now where did I put my coffee down ….

My favourite formula (at the moment) has to be SUBTOTAL. If you haven't tries it then I suggest you give it a go.

It has two main advantages over SUM (or a number of other formulae that it encompasses):

1. If you SUBTOTAL a range of cells that include another SUBTOTAL(1 - to avoid confusion) then the the formula ignores SUBTOTAL(1), so you don't end up doubling the total, but at the same time it is more robust because if a new line is added then it is more likely to be picked up.

2. It has a variant that will ignore hidden cells (add 100 to the function number), so your total will always add up even if some one (obviously not you!) has hidden some of your data.

If covers the following functions:

Really really simple, but used a lot for allowing others to check your formula on a printed sheet or without having to go into each cell:

Press and hold

Ctrl and then press the ` key(key towards the top left of the keyboard, next of the number 1 on a qwerty keyboard) displays the formula in the cell formula used to create the value.Save and or print as you wish and repeat the Ctrl+ ` key process to return the display to values.

Hope this helps!

Erica

I like the ability to custom formatting, align positive and negative numbers, have negative numbers with brackets and zeros appearing as a dash. This avoids red/black colour blindness issues, saves on mistakes when photocopied red negative numbers are misread and removes cells full of zeros.

In 2003 and before select format, custom, then pick one from list and in the “type” box edit to #,##0 ;(#,##0); -

The space after the positive number aligns with negative numbers. Format is positive no.;negative no.;zero no.. To add decimal places #,##0.00. This should be made consistent with the rounding function in cells as well otherwise the casting may be at odds with the numbers displayed

Rounding is a very simple function. allowing the summation of numbers without the embarrassing miscasting errors that give non accountants scope to diminish the credibility of reports.

=round(the number or formula to be rounded, the number of decimal places)

Morris

Thank you everybody for your contribution and your useful Excel tips. The Excel Communitiy Launch Competition is now closed.

We will review all entries and the winners will be contacted and then announced on this blog!

Remember that by posting a comment you are agreeing to the website Terms of Use. Please note that guest comments are not moderated.

Community members please firstLoginE-mail address will not be published

Please type in the digits from the image