How to make Ireland's rugby ranking look good....

Well, pretty at least. When viewed on a spreadsheet. What am I on about, you may wonder. Bear with me...

Have you ever seen those photographs of celebrities where the poor girl has dared to leave the house without a bucket of makeup on? The horrors! The tabloids point out that she looks pretty normal without all the airbrushing and uplighting, and we all think, "We could walk right past her on the street and not notice".

The same goes for spreadsheets. They like a little lipstick and airbrushing. It makes them stand out. Don't believe me? Compare the two sets of exact same data below, showing the IRB top 8 rankings for the last 3 years, and the movements year on year:

Example A:

Example B:

Now, feel free to disagree with me, but I find example B easier to read (especially when its a 1000 lines of data over 30 columns you're analysing). You can immediately see who has moved up, who has moved down, and who has stayed consistently at the top (damn those Kiwis).

It took me all of about one and a half minutes to re-format the boring numbers  in Example A to the clear and concise numbers in Example B. Here is how:

1) Lose the gridlines: See my earlier blog on how to do this...

2) Choose your theme (this only applies to Excel 2007 and 2010): A theme basically sets a colour chart and font for your workbook. It influences the colours you can pick for cells, graphs, pivot tables etc., so choose wisely. If you haven't done this before, here is how:

Go to Page Layout - Themes. The drop down arrow will show you all the theme choices with their basic colour range. Select your particular fancy, and you're set. Note that next to the 'Themes' box, you also have options to change only the colour, font or effects. Play away to your heart's content until you're happy.

Note that if you decide halfway through that you would prefer a different colour chart, change your theme. Everything will follow suit, but note your columns and rows may change size too, so check your print preview before printing.

Note, also, that you can skip this step. Your worksheet will show the standard Office colours.

3) Highlight your headings: Select cell B2, and using your menu options on your Home menu tab, change your background colour, text colour and make the text bold.

4) Use your format painter: For anyone who hasn't discovered this little gem, be prepared for a time saver of note. Go to your home menu tab and find this:

Point to it, right click, and add it to your your Quick Access toolbar, because you should be using this tool all the time. What this tiny little brush does is copy all your formatting in your highlighted cell and will apply this formatting to all other cells you select afterwards.

So you can use this to format all your headings in Row 4 in one go, by simply clicking on Cell B2, clicking on the format painter, and then selecting Cells B4:G4.

5) Center your data:  by using the alignment buttons on your Home menu tab.

6) Center and merge:  your main heading by selecting Cells B2:G2, then clicking the Merge & Center button in the alignment segment on the Home menu tab.

7) Use Conditional Formatting: to show the positives and negatives in the YOY columns. Select cells D5:D12. Still on the Home menu tab, drop down on the arrow on Conditional Formatting and select Highlight Cells - Greater than

Type in 0 in the box provided, and select your colour choice. I went for Green for anything that had a movement greater than 0. Then repeat for Less Than.
With your cells still selected, use the format painter to apply the same conditional formatting to cells F5:F12.

8) Apply borders: Note I left this till last. You really want to be happy that you have finished adding to your data before you apply borders, otherwise if you add anything later, you can spend a lot of wasted time redoing them.

I have chosen to add blue borders, as opposed to the standard black ones. To do this, select the cells you want a border around, and then go to
Home - Borders - More Borders.... Select your border style and colour FIRST, and then select where you want your border applied.

And you're done. The neat formatting might even distract you from the fact that Ireland has dropped four places in one year. Or not.

These are VERY simple formatting techniques. It can get a whole lot more complicated, but it very much depends on your data and what you will use it for.

For some helpful tips on other formatting techniques I have found handy over the years, sign up to our newsletter.

Happy Spreadsheeting!

The most effective "three little words".......

Meet Sam. Sam has a dilemma. He has met the woman of his dreams - lets call her Suzie. They have had a delightful couple of years together, and Sam can picture himself spending the rest of his life with her. He wants to get down on one knee, declare his undying love, and beg for her hand in marriage (yes, soppy for an Excel geek, I know).


So what's the dilemma? Well, Suzie is already turning into a bridezilla. She has been anticipating this day, and presents him with a list of six rings, along with their basic characteristics. When Sam asks her, in some trepidation, what they cost, she airily replies, "about €5000". Sam is a bit of an Excel fan himself so does up this little spreadsheet, to help him budget:

Ah Sam, bless him. He hasn't done his research. He doesn't know about the 4Cs of diamond buying (and for ease in this example, we'll only concentrate on one). He doesn't realise that bigger rocks mean more money, and that platinum is quite a pricey commodity.

So how can we help him? We use the three little words that really mean something...IF, AND and OR.

Now, a couple of caveats before we go further....
a) I am not a jeweller and am sadly not in the habit of buying diamond rings all that often, so these prices are completely made up, and are not particularly logical. Its all for the sake of example....
b) There are other ways of working this problem out - vlookups, modelling tools etc. I am going to do the following examples purely to illustrate how to use IF, AND and OR in formulas.

Lets assume that the base price of €5k applies to a 0.5 carat diamond on a gold band. Sam gets told by his friendly jeweller that all platinum rings incur a 15% markup to cover the cost of the metal. Easy way for Sam to update his sheet? An IF statement.

An IF statement basically says "Is this statement true? If yes - do A, if not - do B". So our formula checks to see whether the metal in column C is platinum, and if it is, it takes the base price and adds the mark up of 15%. If it isn't platinum, the formula just picks up the base price as is.


So Sam thinks he is sorted, but alas no. Our friendly jeweller tells him that a 1 carat diamond carries a hefty markup of 60%, but for this price, they will exclude the additional cost of a platinum band. What now? Well, we use 2 IF statements in one...like so:

This formula uses the first IF statement to check whether the diamond is 1 carat. If it is, it adds on 60%. If the diamond is not 1 carat, then instead of going to the base price, it then checks if the metal is platinum, and adds 15%. This is the 2nd IF statement.

Note: - It is ESSENTIAL to get the logic right in these formulas. If you had checked the metal type first, and you had a platinum ring with a 1 carat diamond, your formula would only show a 15% markup.

You can nest an IF statement quite a few times in one formula, but beware. It gets very complicated and if you find you have 10 or 15 IF statements in one formula, you might want to step back and see if there is an easier way.

Anyway, lets get back to Sam. He heads to another jeweller who has a different pricing system. This jeweller tells Sam that any diamond equal or greater to 0.7 carats, on a band of platinum, has a markup of 30%.

Here we have just nested an AND statement into an IF statement. So instead of one statement needing to be true, BOTH statements need to be true - the diamond must be greater or equal to 0.7, AND the band must be platinum - for us to do step A, which is add on 30%. If BOTH statements are not true, then the formula will revert to the base price.

Now, clearly this pricing is not quite logical. So lets say that if EITHER the band is platinum, or the diamond is greater than or equal 0.7 carats, it gets a 30% markup. We can now use a nested OR statement:

This is the same as the nested AND statement, except in an OR statement, only ONE of the criteria needs to be true for the formula to implement step A.

So there you have it, my favourite 3 little words. Well,when it comes to Excel anyway. Obviously these formulas can get much more complicated, as you can nest away to your hearts content and make things as complicated as you would like. I just wanted to try give you a basic idea of the logic behind these simple, but oh so useful, statements.

For some more ideas on how to use these statements, why not sign up to my newsletter? Just click on this link and fill in the details...

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Happy Spreadsheeting!

A lifeboat for Excel's stormy seas....

"Its not the boat, its the skipper". This harsh advice was often directed at me by my father when I came off the water. I'd be blaming my poor performance on the sail, mast, rudder, wind, anything but me. And if I'm honest, it was all me. Sailing was truly not my forte. I was born to be a landlubber.

I read a blog about Excel today that reminded me of those happy times. It was posing the very valid question of why Excel always gets the blame when reported results turn out to be incorrect. I read this and thought of my dear father. I would willingly bet my house that almost all of the errors that are blamed on Excel, are due to the Excel user.

It is way too easy to make an error in Excel, due to being rushed (who hasn't been under deadline pressure?), not sense checking the results, or - the scariest - not fully understanding what Excel does, and therefore not knowing that the formula you think is perfect, is actually picking up the wrong data.

In an attempt to throw out a lifebelt to all those in difficulty, I have listed below a couple of the most common (and easy) errors to make, and how to prevent them. (And yes, I know these well, because I have fallen into their evil little traps before...)

1) Circular references

You may be thinking, hang on....Excel tells you when you have a circular reference. You're right, it does.When you create a formula that contains a circular reference, an error message pops up that helpfully lets you press ok and allow the circular reference. You then save and close the spreadsheet. When you next open it, it doesn't bother telling you again that you have a circular reference.

Therefore, you open a spreadsheet that maybe someone has sent you, you assume all is ok, and you happily use the results given. You would be wrong. And you may just end up blaming Excel. Before you do, check the bottom left hand corner of your spreadsheet. If there is a circular reference, it will tell you exactly what cell contains the wrong formula. If the error is in a different sheet within your workbook, it simply states 'Circular References'. Go through each sheet until you find the pesky blighter. Ignore it at your peril.

2) Is Excel set to Manual Calculation?

Again, you may be thinking 'Doh'. But really, it happens. You're working with huge sets of data, way too many formulas hanging off them to be practical, and everytime you press enter, your spreadsheet churns through all those figures and formulas to recalculate. So ten minutes and a cup of coffee later, you decide to make your life easier and you switch on 'Manual Calculation'.

(In case you're wondering how, go to Formulas - Calculation Options, and the box below will pop up...Make your choice wisely....)

You enter all your data with ease, you print your dashboard that picks up totals from each of your spreadsheets, and wa'hey....your results are all wrong. Why? Because Manual Calculation is still on.

To prevent this catastrophe while using Manual Calculation, do both of these:

• Press F9 on a regular basis. This recalculates all your formulas immediately.
• Check that handy little bottom left hand corner on your spreadsheet before you print or send anything. If it says, "Calculate", heed the warning and press F9.

And remember to switch back to Automatic when you're done.

3) Are your sums right?

Not such a stupid question. Here are 3 scenarios where your autosums might not be adding up:

a) You have a filter on. This can sometimes throw out your sum function, depending on how your spreadsheet is set up and what you are trying to sum. How do you know whether you have a filter on? Simple - if the row numbers on the left are blue, you have a filter applied. To quickly remove a filter that is applied, and show all your data, press ALT D,F,S.

b) You think you have dragged down to include new rows in your sum, but instead of dragging, you've just moved the range. A picture will explain this better:

If you wanted to include row 7 in your sum, you can point your mouse at the bottom right hand corner of cell B6 and a little double headed arrow will show up. Drag down to row 7 and voila, your sum now includes everything.

The error comes in when you drag down when the four headed arrow is showing, not the double headed arrow. This results in you just dragging down the range. In this case, your sum would be SUM (B4:B7). Easy mistake to make, not so easy to spot when you have thousands of rows to sum.

So how to spot it? Excel tries to help again (see, it is NOT the enemy!). A little green triangle appears in the top right hand corner of the cell with your formula. Click on it, and it will tell you that your "Formula Omits Adjacent Cells". Go find those cell numbers and include them.

c) You simply have left a cell out. This is again, easy enough to do, especially when you are trying to add only certain cells, so you add each one individually. The easiest way to check that you have included every cell you should have, is to click on the cell with your formula, and then click once in the formula bar. All the cells you have chosen will be highlighted by different colour borders, which makes it easy to identify what cells, if any, you have forgotten. See below for an example.

If we were just trying to add sales for the dinghies only, by using this check, we would immediately see that we have left out the sales for Dinghy B (cell B6).

As an aside, this trick is also very useful when trying to make sense of long, complicated formulas. The colours help track what you have done, and Excel even provides different coloured brackets for embedded formulas to make your life even easier.

So these are all fairly basic, but oh so easy, errors to make. And while I recognise that Excel is not perfect, when there are errors in reports or results, it is normally the fault of the skipper!

For some more handy hints on errors, why not sign up for our weekly newsletter at http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Or check out our training program to avoid your own rocky seas at www.excelangels.ie

Hope your spreadsheet seas are calm ones this week!

Cooking and data capture - what do they have in common?

Quite a bit actually. Unless cooking is your vocation and livelihood, it is quite likely that when you cook everyday, it becomes a bit of a chore. Boring. Sure, you may try spice it up every now and again with new recipes, new ingredients, but basically, you do it because you need to prevent unrest and strikes in your home. You would probably prefer to spend your cooking time doing something you enjoy, like exercising, playing outside with the kids, or drinking a bottle of wine with a good book.

Data capture is also not something too many people enjoy, especially when it becomes tedious, boring and, lets be frank, mind numbing. You would probably prefer to be doing something more productive with your time, like actually analysing the data you have input. Or reporting on your data. Or drinking a bottle of wine with a good book...

But there is hope. Just like a "Meals in 30 minutes" recipe book can revolutionise the way you prepare the family grub, a couple of changes in the way you use Excel for data capture can revolutionise your working day.

Here are a couple of closely guarded family recipes for data capture success. Use them, enjoy them, the bottle of wine is optional:

1) Use a formula wherever possible.

This may seem like a ridiculously simple suggestion, but bear with me. Imagine you have a sales price list of products, which is simply the cost price of the product, plus a 30% markup.

You could easily (but slowly) take each product, line by line, and calculate the sales price. An even worse approach would be to hardcode each price. OR, you could do something like this (I've shown the formula for the first line):

Note that I 'locked' the cell with the markup (G3) into the formula. When you do this, you can autofill your list, and the formula will continue to look up cell G3. To do this, highlight the cell coordinate in your formula and press F4 on your keyboard. The little $ signs will appear next to the column and row coordinates. To lock just the row or just the column, continue to push F4 until the right coordinate is locked.

For more on autofill, please read my prior blog: http://excelangel.blogspot.com/2012/04/autofill-your-lifewell-as-least-your.html



The joy of this is that if you change your mark up in the future, it is literally a 10 second job to update your pricelist, not a hour's worth of work.

You can make this step even easier by 'naming' your markup cell...but more on this in our subscribed newsletter. Sign up here.

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

2) Break your data down into its simplest form

Taking our data example above, imagine your boss now tells you that all the blue and green products need to have 35% markup instead. Easy enough on 8 lines. Not so easy on 100. But if you just break your data up into its simplest form, it is easy to filter or sort and then adjust the formula. Like so:

3) Use Autocomplete if you need to type repeated words in a list

Autocomplete is a massive time saver. If you need to type in the full list of products from scratch, the first time you type in 'Gadgets', Excel logs the spelling and remembers it. If you then start typing in 'Gadgets' again a few cells down in your list, Excel will offer you 'Gadgets' after you type in a couple of letters. You can then hit enter to select it, or type over it if you want a different word.

This not only saves you time, but also ensures that all your 'Gadgets' are exactly the same, making sorting, vlookups, counting etc much more effective. One thing to note though, you can't have gaps in your list for this to work. Any gaps, and Excel loses it's memory.

If you find your Autocomplete is not working, ensure it is selected by going to File - Options - Advanced, and make sure the box next to "Enable Autocomplete for Cell Values" is ticked.

So these are 3 little time savers. Hope they help. For more info, or for one to one training, check out our website at www.excelangels.ie

Happy Spreadsheeting. I'm off to find that bottle of wine...

A picture tells 1000 words, but a graph can give more insight

3 guys walk into a bar. Its a Tuesday. They have a couple of pints. They leave. It's now Thursday. The same 3 guys walk into a bar. They have a couple of pints. They leave. (If you're waiting for the punchline...sorry...read on and I might just share a joke at the end...) Anyway, so what? We have 3 guys. They like pints. Not so unusual.
But, for arguments sake, lets map their intake on a graph. Lets say the data looks like this:

If we showed their intake levels on a couple of different graphs, it would look like this:

A basic 3D clustered column graph:

The graph very clearly shows the trends, and lets you make some assumptions, like: Jim is looking forward to the weekend, maybe Bob has a Friday meeting, and Joe, well, maybe Joe is having a bad week.

A stacked 3D column graph:

This graph adds everything together for the week. It very quickly shows you the total for each guy, without you having to do mental arithmetic. (Admittedly, not so tough with the given data, but if you were looking at sales data for 10 different types of widget for a week, you might appreciate this graph a bit more).

What if you wanted to see the % split of their intake during the week? Easy....




















The 100% stacked graph takes the total data for the week per man and shows you the % intake per day. Again, this would be useful if you were analysing sales across the week per widget, and wanted to assess whether any particular day was good for a particular widget, in order to up production or distribution, or increase promotions on the 'not so good' days.

Now....lets think about the pub. What are their strong sales day, and who is their favourite customer to be given extra loyalty points?
Take the 3D stacked graph, and using the same data, simply swap the axes:

Going on this graph, Joe is definitely the man to be getting the free beer mats or bottle openers. And maybe the pub should plan a pub quiz on Tuesdays to up the sales? 



So what is all the point of this pub talk, if not to remind you that its the weekend? I just wanted to illustrate how useful and effective graphs can be, if used correctly. I have used the most basic type of graphs here, but they show the trends really well. Graphs can get alot more complicated than this, and they all have their place in data analysis. The trick is to use the right graph, for the right data and for the right audience.

All good graphs should do the following 2 things:

1) Give you an instant picture of the current trends pertaining to the data used; and

2) Lead you to ask more questions about the data, forcing you to analyse your data in more depth.

By using graphs this way, you can very simply and quickly find out trends, deviations from trends, and anomalies. And they look prettier than rows upon rows of data.

Ok, so as promised...a joke. Sticking to the bar trend...2 atoms walk into a bar. The 1st atom says, "Wow, I partied so hard last night I think I lost an electron." The 2nd atom replies, "You positive?"

And with that, we end this week's lesson. If you would like a couple of handy tips on graphs, why not sign up to our newsletter at:

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

 Also, don't forget we offer personalised training and template creation...see our website for more details.

www.excelangels.ie

Have a great weekend, and happy spreadsheeting!