teachr

The A to Z of Excel Functions

noreply@blogger.com (Matt) — Sat, 09 Oct 2010 13:08:00 +0000

Just a quick one for any returning visitors here. Over on my new site, 2toria, I've started an (almost) daily series on the A to Z of Excel functions, working alphabetically through the most useful. Go and have a look!

Go! Now!

(please?)

Worksheet Copy Tool on 2toria

noreply@blogger.com (Matt) — Sat, 12 Jun 2010 11:53:00 +0000

I've been developing a tool to copy worksheets to multiple existing workbooks - the add-in can be found here.

2toria!!

noreply@blogger.com (Matt) — Tue, 08 Jun 2010 17:28:00 +0000

If you've been expecting more updates from the site and wondered where I am it's because I've been working on my new project at 2toria.com. It's more of the same but with a more personal twist, plus I'm also offering consultancy work and downloadable content. I hope you can join me there for more quality posts.

Excel Challenge - Week Commencing 15/3/10

noreply@blogger.com (Matt) — Mon, 15 Mar 2010 17:05:00 +0000

I've only done one of these, I think, and never really followed up on it, so I thought I'd get back onto it with a pretty easy challenge in the form of a quick quiz on Microsoft Excel keyboard shortcuts. Answers will be posted on Friday!

1) What shortcut would you use to select all contiguous cells on a sheet?
2) Which shortcut lets you toggle between viewing values and formulas/functions?
3) What key do you press to edit the contents of a cell?
4) Which combination of keys do you press to move to the next worksheet in the workbook?
5) What would you press to check spelling and grammar?

Ok.. So they were easy, for now. If you want something more challenging send me your comments and I'll make sure next week isn't quite as easy...

Calculating Percentage Increase/Decrease

noreply@blogger.com (Matt) — Sun, 14 Mar 2010 08:45:00 +0000

I've recently received a request to explain how to calculate percentage increase or decrease over time in sales figures on a month to month basis, so thought I'd write a quick tutorial on how to do this.

Let's have a look at the figures:-

March - 14,000.32

April - 12,500.28

May - 15,323,99

The client want's to know the percentage increase/decrease in revenue from month to month. How do we do this?

It's actually quite simple. To find the difference between one value and the other we first need to divide the number we are comparing with by 100.

14,000.32/100 = 140.0032

That number is one 'per cent' of the original number, and as such is our starting point. We then need to find out how many times that number divides into the number you are comparing, like this:-

12,500.28/140.0032 = 89.29 (rounded up)

So, 12,500.28 is 89.29% of 14,000.32, or 10.71% less than 14,000.32 (100-89.29 is 10.71)

Does that make sense? Let's run through the comparison between April and May. We start again by dividing April's figure by 100 to find out the one 'per cent' value:-

12,500.28/100 = 125.0028

And then divide May's figure by the value:-

15,323.99/125.0028 = 122.5892

Which tells us that May's figure is 22.6% (rounded up) higher than April's.

It's fairly simple really, and a really useful method of comparing one figure with another in terms of percentage difference.

There is a much quicker way of doing this, however. I just wanted to break things down a little so that the maths behind it could make sense. The quick and easy way to do this is to take the first figure, divide it by the second, and then multiply by 100. Let's use the first example to show you that it works.

12500.28/14000.32 = 0.89

0.89 * 100 = 89

Much easier that way, isn't it?

Focus and Control: My new adventure

noreply@blogger.com (Matt) — Wed, 03 Mar 2010 12:56:00 +0000

Look out soon for a new blog by me on the subjects of focus and control in life, as well as a few other things. 2010 is the year for a change!

Job Interviews: Common Questions and How to Prepare

noreply@blogger.com (Matt) — Wed, 17 Feb 2010 12:45:00 +0000

In my second post on applying for jobs I've decided to look at the common interview questions (or at least the ones I ask regularly) and give you some pointers on the things to say, as well as the things not to.

This isn't a comprehensive list by any means, rather a list of the questions I frequently ask, so don't blame me if they don't show up in your interview. As with any job application the key is research and preparation.

Why have you applied for this role?

This is usually one of the first questions you're likely to be asked in any interview. If you really want this job then you probably already know, but in some cases you might just be looking for any job that pays the bills. In this question I'm expecting you to match your skills and previous experience to the role we're offering. You should have a good knowledge of the role itself and be fully conversant with the person/job specification. Make sure you do some research about the organisation you are working with and come across as having some idea of the role the organisation carries out.

An answer which impresses me here is one which shows you have done the above things. You need to tell me how you can fit in and how your skills match the role you're being interviewed for.

How do you manage your time?

Calendars, to-do lists, post-it notes...whatever you do (or don't), here I'm wanting to know how you ensure the work is done at the end of the day and what you will do if there's things left outstanding. I often probe a little deeper here to find out what you would do if you had a day fully planned out and the shit hit the fan first thing in (ie most staff off, a surprise audit by a regulatory body, etc). You might have to think on your feet a little, but I want to know that you can keep your head and keep on top of things. If the role has a line-management element remember you can delegate. If it doesn't and you're faced with such a situation then a good answer includes checking your current workloads to see if anything can wait until later. If it can, move it to a time where it can get done, if not then you need to speak to your customers (either internal or external) and ask them if their deadlines can be changed at all so you can fit in the time to fight fires. If this fails then you rely on your line-manager and ask them to take the lead.

The key to answering this question is to appear completely organised and ready for anything. If things go wrong you have a plan for how to deal with them and yes, you always make sure your daily to-do list is complete at the end of the day. Simple.

Can you talk to us about your IT skills?

Most administration roles require some use of IT - some more than others. Here I want to be impressed. I want to know you can use Word, Excel and Powerpoint. Microsoft Access is also good, if you've ever used it. If not, then don't panic (unless you're applying for an Access-heavy role, in which case I recommend getting some practise in!)

Think about any times when you might have used IT to improve services in your previous roles. Show good preparation by bringing along screenshots of any spreadsheets or databases/forms you have implemented in your roles so that I can see them. It's really quite impressive if you've done something like that in the past and you can show the evidence too.

If you have weak IT skills I suggest you do a bit of research. Fire up Word and Excel and play with them for a few hours. When the interview day comes and you get this question explain that you've used them in the past but you're a bit rusty. If you've had a little play around with them and (god forbid) you're given an IT assessment of any kind you won't be shellshocked when you open the software for the first time. Don't worry too much about it, though.

Can you tell us what you imagine a normal day for you in our organisation would be?

This question relies on you knowing the organisation, job description and person specification well. It's so disappointing to ask this question and the interviewee has obviously zero research on it. Look at the general tasks that are being asked of you and imagine what you would get up to each day. Chances are you're probably going to be slightly wrong, but if you mention the basics from the information you've been provided with you're going to be fine. This question, for me at least, is more to get a feel for how interested you are in the role. If you don't show that you know anything it makes me wonder if you're 100% into this. Do your research and use your imagination.

Can you tell us about a time when you've had to use your initiative to solve a problem in the workplace?

People tend to struggle with this question as they tend to find it hard to pull out a decent example of such a time. Give it some thought now. It's useful if you can think of an example that uses some of the skills required in the job you're being interviewed for, but if you can't, just think of something where you've had to think on your feet and the solution has been a positive one. Time management, delegation and ingenuity are useful things to include in your answer.

Can you tell us about [insert here] regulations?

The word to remember here is research, research, research!! Chances are you might already know which regulations govern the type of organisation you're working in (for example education or NHS) and be fine with this, but if not it certainly pays to have a bit of a read up, especially if certain regulations or rules are mentioned in the job description or person specification.

Nothing much else to say here, just research.. Even if you have never worked in that particular area before, it'll make you look good if you have a reasonable grasp of do's and dont's.

What are your strengths/weaknesses?

One of my favourites, this questions tests how self-aware you are. Strengths are usually quite easy to come up with. Its best to mention your exceptional organisational skills, ability to adapt to change, or maybe something else that is linked to the role you're being interviewed for.

Weaknesses, on the other hand are a different kettle of fish. Here we want to not only know what you're not too good at, but also how you can change things for the better. If you're no good at public speaking, then mention it, but also mention that you're eager to take part in any courses available that might improve this for you. The key is to highlight ways you can improve on those weaknesses.

Avoid telling me that your main weakness is that you're too organised or too much of a perfectionist. This is lazy - find something else.

Do you have any questions for us?

I really like this section when the interviewee has something intelligent to ask. The most common questions are usually about salary, holiday entitlement and opportunities for learning. These are absolutely fine, but I like it when I'm taken off guard somehow. Asking about the work environment, or what a typical day in the role would be like is a much better way to impress me, and it gets me thinking too. Bear in mind, especially if you're interviewed late in the day that myself and the rest of the panel are probably quite tired and will have heard the same thing many times throughout the day. Don't be that person - wake me up with something interesting to say and you'll be remembered much more than if you just ask the same questions as the rest of the people I've already seen today.

And that's about it for this post. There's a whole load of questions that you can be asked, obviously, and I can't cover them all here, but these are a bunch of the most frequently used by me. Basic point is, when you go for an interview make sure you've done your homework. Know the organisation, know the role, know the environment and know the rules and regulations that govern it. Show interest and you're almost there.

Good luck to you all. Bye for now.

Top tips when applying for jobs: Online application forms

noreply@blogger.com (Matt) — Thu, 11 Feb 2010 14:22:00 +0000

In the past two years working for my current organisation I've been involved in the recruitment and selection of no less than 30 staff. It's a time consuming process - adverts have to be posted, applications shortlisted and long days of interviewing can take their toll. Hopefully, by the end of this process we end up with that perfect staff member and everybody is happy.

In the first of a series of posts I want to provide some hints and tips to those of you who apply for jobs via online application forms. The bulk of the applications I see come to me via online applications and, whilst I've seen a lot of impressive examples I've also seen some real bloopers, which has led to this post.

My hints and tips here aren't supposed to be patronising in any way, more just a gentle nudge and a reminder of what you should be doing in these cases to ensure that you stick out and have a better chance of ending up on the 'IN' pile rather than 'OUT'

So, without further ado, let's go:-

Check your spelling

One of the first things to jump out of an application is one that contains spelling mistakes. Most online application forms do not contain inbuilt spellcheckers and this can be dangerous for you if you're sloppy about it. In most of my Job/Person Specifications, one of the 'Essential' criteria to meet is 'A high level of attention to detail' - one which I'm sure most of you will agree is required in most roles today.

Make sure you check your spelling across the whole application before pressing that 'Submit' button. If you need to, copy and paste your text into Microsoft Word and run a spell check on it before copying and pasting back into the application form just to be on the safe side (and you can't be too careful, trust me).

Alternatively, if you're using Firefox or Google Chrome you can get add-ins to check your spelling in forms and they'll automatically highlight anything that looks a bit dubious. It's really worth doing, because nothing puts me off more than a badly spelt application form.

Only use CAPITALS where CAPITALS are needed!!

In the online world, using CAPITALS ALL THE TIME IS CONSIDERED SHOUTING. The same applies to application forms. It won't necessarily go against you if you have caps lock stuck on, but think about how it looks to the reader. It's incredibly difficult to read, and doesn't really inspire confidence in me that any letters you send to our suppliers or customers won't look the same.

Please don't use txtspk.

This one is a no-brainer, and I'm sure the bulk of you reading this are saying "No, way! Why would I even think of doing such a thing?", but I've seen it...seriously. This is more or less an instant 'Out' pile crime and one I hope you can all avoid. A recent application I received actually said "I really want 2 work in this field", and it didn't look good at all.

Don't do it.

Make sure you're applying for the correct job

The online system we use saves your last set of application details so you don't have to re-type everything again. It's a really useful facility but has really ruined some people's chances when applying for other roles in the organisation and not checking quickly what has been written previously. On our application forms we have an 'Additional Details' section where we encourage applicants to tell us more about themselves and why they would be ideal for the role they are applying for. It's not a rare occurrence that I receive an application for a Secretarial Administrator which contains details about why the person applying would be ideal for a role as PA (the one they last applied for).

Once again, it doesn't inspire me with confidence and makes me wonder whether or not you've even read the job description.

Basic point is to check, then double-check your application from top to bottom so you're sure it'll catch my eye.

Match your application to the Job/Person Specification

Most job descriptions these days contain the JS/PS in which the employer tells you what the Essential and Desirable criteria are to be suited to this role. Make sure that, in your application you find examples of how you meet the essential, at the very least. A lot of organisations these days have a policy in place which means that all applications which meet the essential criteria have to be shortlisted for interview. If you can prove it, you've got more of a foot in the door.

Don't mention personal details in your application, unless you really have to

Most online job application forms are anonymised. Name, address, sex and age are very often stripped from the forms so that we, the employers can review your application without bias in any way. This is done for a reason, so don't feel the need to put your personal details anywhere in the application. It shouldn't make a difference to the person shortlisting, but it could, so don't do it. We don't need that information.

Don't mention personal hardships or past problems

At the shortlisting stage I'm interested in the person you are and how you might fit into one of my roles. Telling me about how you look after your critically ill mother isn't something I'm interested in (yet), and quite often I've seen that mentioned in a way that makes me want to shortlist because I feel sorry for you in some way. It's quite an uncomfortable thing to read and doesn't actually tell me anything about you (the most important person at this early stage). You might have issues around start/finish times because of your life or home situation, but you can mention that at interview, or even later if you are told you are successful for the job. Some people might find it offputting at the application stage, so leave it until later.

In an anonymised application form I received a couple of years ago the applicant mentioned a previous criminal conviction and how he/she had turned a new leaf and now felt they were ready to impress. I really didn't want to see that in an application form, and, whilst the the person wasn't successful due to not meeting the job specification, I wondered if it would put me off in some way. It shouldn't - most employers have to follow rules around Equality and Diversity so can't allow things like that to influence them. Some people, however may follow the rules less strictly and might instantly decide against shortlisting. Once again, if something like that needs to be mentioned, do it during the interview or later. If you're ever unsure you should speak to the human resources/personnel department first and let them advise you.

Don't be too brief

In one of my earlier points I mentioned the 'additional details' section of an application form. This is the bit where you get to convince me that you match the person spec'. Do it well and you're in my office a couple of weeks later for interview. Make sure you write enough, though. There's no need to provide a life story, but please write enough so that it looks like you want the job. Mention the role, why you're ideal for it and why you want to work for my organisation. Do a bit of research around the job and maybe slip some details in around how you would fit into it like a glove. Don't just give me a one-liner about how you think you're perfect for the role and then neglect to tell me why.

And finally...

Check your application again. Put yourself in my/their shoes and imagine reading it. Do you think you've got a good chance of being invited to interview? No? Read it again, figure out why and fix the problem.

I'm not guaranteeing that following these hints and tips will get you shortlisted. Employers are all different people, and not all of them will look for these things. I would like to think, however that these tips are pretty much common sense and if you follow them you're definitely increasing your chances to be a part of the 'In' crowd.

Look out for more posts in the future on Interviewing skills. I'll also be posting about these things from an employer perspective and offering an insight into tips which can help you to get the person you want.

Until then, thanks for reading.

Excel is rubbish at....

noreply@blogger.com (Matt) — Tue, 09 Feb 2010 12:52:00 +0000

....picking valentines day gifts, unless you have a huge list of possibles and use the RAND() function to pick from the list. If you don't have a huge list of possibles (I don't have a list at all!), you might as well not start Excel up because it isn't going to help.

What to do when Excel stops you using the plus symbol to start a formula/function

noreply@blogger.com (Matt) — Mon, 08 Feb 2010 13:18:00 +0000

Just recently I was sent a spreadsheet from one of my colleagues in finance to tinker with and add some VBA code. When she sent it through she mentioned to me that there was another tiny issue with it in that some of her functions no longer seemed to work correctly. Like me she wanted her numerical formulas to start with the plus '+' symbol, as Excel is quite clever and recognises it as a formula the same as it does when you start one with the equals symbol.

Only problem was, when she typed in a formula such as '+10/1.13', the program threw up an error about the formula typed containing an error, and so wouldn't actually work until equals was used. What a pain...

Anyhow, after a while I found the answer and discovered that typing the plus symbol is a leftover from the days when people used Lotus 1-2-3 (yep, Im one of those people too, hence my use of +!). If you're having the same problem in Excel, go to Tools>Options.. and select the 'Transition' tab.

If you make sure the 'Transition formula entry' checkbox is ticked and then click OK, the problem should be solved.

I didn't even know that tab existed until today!

My Key to Task Completion

noreply@blogger.com (Matt) — Thu, 14 Jan 2010 17:57:00 +0000

As its my first day of Operation Matt Ups His Game for 2010 I decided
to work on the non-procrastination aspect of my plan by starting to
clear my backlog of stuff. I decided not to go for the GTD method of
collecting everything together, but rather just start at the top and
get cracking with it.

I set out with the mindset that every task and email would be
processed through to completion or to a point where there was no more
I could personally do with it. I was lucky enough to have a free day
with no meetings as well, which helped me out a lot.

For each task/email I wrote down what the end looked like for that
task. Sometimes the end was the actual task I had to do, but in most
cases there were multiple steps, for example create a spreadsheet and
send on to my staff. Whilst most of the time this is obvious it does
often help give a task or project some clarity.

So, did it work for me? I have to say, I've been more productive today
than I have for ages. The key now is to turn this into habit/routine
so that I'm ALWAYS like this and not just getting into a fad. It feels
good to complete things, and this is my year for getting things done!!
Ill

My daily bus journal - 13th january 2010

noreply@blogger.com (Matt) — Wed, 13 Jan 2010 17:34:00 +0000

I've not had a bad day today really, but at the same time it's not
been the greatest. I've been extremely busy but I haven't managed to
get all the bits done that I would have liked.

In a fit of disappointment I started thinking about how I want to make
this year MY year in terms of getting on top of my game. Sometimes I
can be a serial procrastinator and that's probably self-evident in the
previous irregularity of my posts. Not this year..

This year I've decided that I'm going to start organising my life,
dealing with things head on and not ever (if possible) putting things
off. I'm good at starting projects and not finishing them, but that's
due to change.. I'm going to start by looking at all my open loops and
either deciding to close/compete them or to bin them if they are no
longer useful or relevant. Next I'm going to come up with some kind
of a plan as to where I want to be by the end of the year and try to
come up with a list of actions on how I can get there. I want to
become proactive now I'm really getting into my thirties and 2010
seems like a good year to get started.

In thinking about this stuff I've also managed to come up with a few
ideas for future posts, so expect to see a lot more from me in the
coming weeks and months.

Almost home now so ill sign off. Ill keep you all updated. Exciting
times await...

My daily bus journal - 12th January 2010

noreply@blogger.com (Matt) — Tue, 12 Jan 2010 16:25:00 +0000

It's whilst I sit on the bus I think of possible ideas for future
posts, some to do with Excel and others general life things. I do
however sometimes have dry spells where I can't really think of
anything. I'm in one of those spots now.

Can you guys think of anything you want a tutorial on? Post in the
comments and ill have a go. It doesn't necessarily have to be Excel
related - challenge me!

On another note I'd like today to discuss job interviews. In my role
I'm often involved in the recruitment and selection process. I
actually really enjoy it. It does, however occasionally really
irritate me. Well, actually, let me rephrase that, people irritate me.
With that in mind look out for a future post on tips about interviews
and job applications. I feel there are a lot of fundamental things a
lot of people should always do that they're just missing. Ill post it
this week at some point.

Apologies for the shorter and less interesting than normal post.

My daily bus journal - 11th January 2010

noreply@blogger.com (Matt) — Mon, 11 Jan 2010 17:59:00 +0000

Did you ever have an imaginary friend as a child?

As a boy my imaginary friend was named 'Giant', because that's what he was, a giant. A giant who lived under my bed but was absolutely huge. Its funny, because it doesn't make sense at all and yet I can still visit a place in my mind where I'm 5 years old and it makes perfect sense that a 20 foot giant, put on the earth to protect only me lives in the four inch high crawl space under my bed.

Thinking about it makes me wonder whether imagination in children is something that these days has been lost. In a world where technology and the media are king, do our children ever just use their minds and create whole worlds in their bedrooms as I did? I doubt it. Children do seem unable to occupy themselves like we did 'back in the day'.

As an example,my girlfriends son came into our bedroom last week during the bad weather which had meant he couldn't attend school and told us he was bored, and had nothing to do. Two weeks ago the boy had been bought a Nintendo Wii. He already has his own TV, DVD, PC and DS, and yet still managed to be bored. At the age of 8 I had toys, yes, but certainly nothing to even scratch the surface of what todays youth have and I was never, ever bored. I was shocked and, I guess, a little annoyed that he had made this incredible statement. It did get me thinking however about whether all this, this STUFF, has somehow stolen our children's imagination.

I don't know where Giant is now, I abandoned him years ago as my adult mind had more adult things to deal with and my childhood became a distant memory, but I still never get bored. I hope he lives on somewhere under somebody elses bed, I really do.

MBA Revision: SWOT Analysis

noreply@blogger.com (Matt) — Mon, 11 Jan 2010 15:26:00 +0000

When setting an objective for a business or organisation, it is prudent to scan the environment, both internal and external to identify whether or not the objective is feasible. There are various methods of doing this, one of which is that of SWOT analysis.

What is SWOT?

SWOT analysis is the process of looking at an organisation's Strengths, Weaknesses, Opportunities and Threats in terms of an organisational goal or objective, and then defining a strategy which will allow you to deal with each of the items which fall under the SWOT headings. If the goal or objective is not attainable following the analysis it should be abandoned/re-stated and the analysis carried out again.

Internal and external factors

As mentioned earlier, both internal and external factors need to be taken into account when creating a strategy. SWOT does this. For internal factors, it looks at the strengths and weaknesses of the organisation, and in turn uses the opportunities and threats to identify factors which might exist externally.

A quick whistle-stop tour of the acronym follows. In brackets I have also added the terms from the acronym ‘USED’ which aids as a description of what should be done with the findings of each area in the SWOT.

Strengths (Use)

These are things that the company does right, and does well. To continue as it is the organisation should continue to do these.

Weaknesses (Stop)

These are things the organisation isn’t doing too well at all. These items need to be worked on and improved.

Opportunities (Exploit)

Whether new untapped markets or a new and as yet, unexplored technology, opportunities are external factors that should be sought after and exploited. Opportunities that are successfully investigated and exploited should at some point become strengths.

Threats (Defend against)

Threats should be avoided as much as possible and are things which can negatively affect the organisation.

Criticism

SWOT analyses are subjective – it is unlikely that two people working independently on a SWOT analysis of the same thing will come up with the same results. To be effective, the analysis should be carried out by a group of people and significant time taken into analysing all factors within it.

Using SWOT carries the risk of missing other, maybe more obvious factors that fall outside of the radar. It should certainly not be used as the only tool in creating a company’s strategies.

Some see it as a “Significant Waste Of Time”!!

Excel is rubbish at....

noreply@blogger.com (Matt) — Sun, 10 Jan 2010 18:32:00 +0000

...stopping three kids from arguing about who gets to play on the Wii when there's only two controllers. No amount of complex calculations can assist. Any ideas?

MBA Revision: Acquisitions, mergers, joint ventures and more...

noreply@blogger.com (Matt) — Sun, 10 Jan 2010 18:08:00 +0000

As it's my strategy examination tomorrow night for the MBA I'm currently attending, today has been one of much revision, reading, re-reading and more revision. I'm quite tired, and not too confident about what I've learned, but to establish some of my knowledge and consolidate it in my mind I've decided to write a bit of a tutorial on one of the larger sections I've been reading up on, namely that involving acquisitions, mergers, joint ventures and strategic alliances. It's only a whistle-stop tour, but hopefully will be of some use, not only for me!

Acquisitions

Sometimes also known as takeovers (and in some circles mergers, although these can be defined in a different way depending on who you speak to), an acquisition is the purchase of one company by another, usually that of a smaller company by a larger one.

Acquisitions can either be friendly or hostile. In cases of friendly acquisition, a mutual agreement involving negotiation is made, whereas a hostile takeover is usually less 'friendly'. The company being acquired may not want to be, or in some cases the takeover is carried out without the board being aware of it.

Mergers

As discussed, some people put mergers and acquisitions under the same definition. However, I would argue that a merger is more of an agreement between two or more organisations to work together as one business.

Joint Ventures

A joint venture (or JV) is where two businesses decide to undertake business activity together. Both companies involved provide equity and they share in the revenues, expenses and control of the enterprise. A good example of such activity is that of Fuji Xerox.

Strategic Alliances

Similar to joint ventures, these require no equity stake by their participants and in total the arrangements are much less rigid than joint ventures. They usually involve a formal relationship between one or more parties, which results in them still working independently of each other, but getting their respective heads together on similar goals or business needs. Cooperation and synergy are two terms that describe these alliances quite well.

My daily bus journal - 8th January 2010

noreply@blogger.com (Matt) — Fri, 08 Jan 2010 17:33:00 +0000

In an effort to keep the blog more regularly up to date and to keep
myself occupied on the journey home I've decided I'm going to have my
own thought/rant of the day moment whilst on that journey using my
BlackBerry and gmail to post whilst on the move. Dontcha just love
technology??

I know this might seem a little off topic and self indulgent of me but
believe me, the everyday bus journey can sometimes suck! Fellow 'bus
wankers' (you'll understand this if you've ever watched The
Inbetweeners on channel 4) will, I am sure know exactly what I mean.

Things that irk me about bus travel:-

* Kids. Simple as. It doesn't matter how old or young, or whether
they're mine or not. Kids should not be allowed on buses. Ever.

* Mobile phones. This gripe covers a lot. It covers ring tones, loud
conversations and most annoying of all, the MP3s played at full volume
at the back of the bus by groups of the aforementioned, who shouldn't
be on them in the first place.

* Grumpy drivers. There's no need. At all. Yes, I know you've had a
bad day but you applied for and accepted this job after weeks of
training. Don't blame me for not having the exact change, or for not
ringing the bell to stop with more than 20 seconds notice. Cheer up!!

* Ill people. Picture the scene.. Its a hot day and the bus (already
hot inside and with room for 40 people upstairs) is full. Can you
blame me for feeling a little upset when all I can hear for the 30
minute journey is coughing, sneezing and sniffling? It makes me feel
ill by the time I'm done with the journey. I know people can't help
being ill, I just don't like it.

And that's just a small part of an ever expanding list. There are
hundreds of things I dislike about bus travel but I'm almost at my
stop so am going to wrap this up.

My likes? Other than the price, which is still cheaper than driving
(but I expect soon won't be) I can't think of anything.. Ill keep you
in the loop.

Microsoft Excel: 10 Good Habits to Adopt in 2010

noreply@blogger.com (Matt) — Fri, 08 Jan 2010 16:21:00 +0000

With any software product as vast and complex as Excel, there is often no completely correct way of using it, as long as you get the results you want. Some people like to add information up like this;-

=A1+B1+C1

and others like this:-

=SUM(A1:C1)

Whilst it's fairly obvious that, for large lists of information at least the second option is best, this is a perfect example of how two things can be done in two entirely different ways.

This article is a brief list of tricks and tips I've picked up over the years which make good habits to keep when creating Excel spreadsheets. These 'habits' can make your Excel spreadsheet work much more organised and stress-free.

So, without further ado:-

1) Save your file before you even start

This isn't as much an habit to adopt just for Excel, but for all software programs. The first thing you should do when you've opened Excel and decided what you're doing with it is to save it using a name that means something and in a location where it can easily be found. Excel's auto-recover function is great for bringing files back to life when there's been a power-cut, but it can be really annoying looking through a list of files named 'Book1.xls' etc for the one you were working on before the disaster. Get into the habit of naming your files before you start and you'll never have that problem again.

2) Try to keep similar things on similar sheets

To keep on top of my work I like to make sure that I group my spreadsheet work together by similar sheets. For example, I make sure that all my vlookups are on separate sheets where they can't be messed with, and my reports all together, again on separate sheets. It makes life a lot easier if things are separated into 'modules' where they can be tracked down and messed with in one place, rather than many.

3) Give your worksheets names which mean something

This is another thing that I find incredibly useful when using Excel. Each worksheet created in your workbook usually does something or has a purpose (see previous habit). How can you possibly know what purpose that is if you're sticking with the name 'Sheet1', 'Sheet2' etc? Give them a name related to their purpose and you'll know exactly where to look. On top of that, it'll make life much easier for those you send your work to.

As an addition to this I also find that colour coding the sheets makes life easier. Sheets which are for printing/displaying I usually colour green, whilst those which hold lookups and background calculations I colour blue. If you don't want others messing with your formulae/functions you can always hide these sheets, too - it's up to you (obviously!).

4) Comment your formulas & code

Adding comments to cells and any code you write in VBA is essential if you think things are likely to get complicated. Nothing is worse than having to re-visit a spreadsheet you worked on last year and trying to decipher exactly what everything does all over again. Using comments can help you along, especially when using huge calculations on multiple sheets - signpost the way and life will be much easier, especially for the new guy/gal when he/she starts using it!

5) Use named ranges as often as possible

I don't think I would survive without using named ranges for almost anything that requires calculations - especially anything that will be re-used again, such as VLOOKUPs. Carefully chosen named ranges can act like constants in VB/VBA and, once again give your work much more meaning. Does this look better:-

=SUM(A1:A10)

or

=SUM(Salaries)

Where Salaries is the named range. Much better, isn't it?

These small tips are all pretty similar, and mainly involve using your common sense when creating new sheets. I'd say the main habit to get into is, where possible, to basically name everything that can be named. It'll not only make your life easier and your sheets easy to re-visit, but it'll make others' lives easier too when you're not around to explain things.

Hope this has been helpful!

What defines an entrepreneur?

noreply@blogger.com (Matt) — Fri, 08 Jan 2010 15:37:00 +0000

During my work on the MBA I've come across various terms and
definitions which have been of great use to me. This is the first of a
number of business and MBA-related posts which I hope will be of use
to some people.

So, what are entrepreneurs? What do you think they are?

I bet you're probably thinking of big names like Bill Gates, Richard
Branson and Steve Jobs, and thinking that would be right, but can you
explain what makes them entrepreneurs at all?

What words would you use to describe them? In my tutorial we came up
with words like 'visionary', 'inventive' and other similar terms. I'm
thinking you'll probably be on the same track. So with words like
these rattling around, what would a good definition be?

The answer is there are quite a few really, depending on what source
you get the definition from. In general, however the overriding idea
is that an entrepreneur is a driven, passionate person who can
identify opportunities and exploit them, especially opportunities that
may not have been discovered previously, such as niches in the market.
An entrepreneur will take responsibility for his or her actions,
taking risks as necessary to reach their desired successful outcome.

Sounds like Messrs Gates, Jobs and Branson, doesn't it?

I'm a businesswoman, get me out of here...

noreply@blogger.com (Matt) — Thu, 19 Nov 2009 13:42:00 +0000

Jordan aka Katie Price is back on our screens again, exactly where she was all those years ago before her marriage to Peter Andre and the beginning of Posh and Becks v2.0 (or so we thought). It would seem that, given the recent end to that particular celebrity ‘fairytale’ (and it certainly seemed to be that way, for a while at least) both of them need to do something to endear themselves into the hearts of the British public once more.

Whilst Peter seems to generally be keeping himself to himself and remaining the better liked of the pair, Katie hasn’t really done herself any favours over the past couple of months and what better opportunity than to appear on the TV show that started the whole ball rolling?

My first thoughts? What a stupid whore... I won’t mince my words, and neither did my girlfriend at the start of last night’s episode. She leaves the kids at home and sends herself on a mission of (in her own words) ‘closure’ by coming back.

Closure? Really? Is it nothing to do with the reported obscene amounts of cash she gets for doing it (one paper quoted £300k)?

I highly doubt this is anything to do with closure. The decree nisi should have provided that, surely. Another thing that amuses me is that part of her ‘job description’ on the show is that of ‘businesswoman’. Surely not...

So, last night we were watching and in a high state of gigglesomeness when, surprise surprise, Katie was voted once again to take part in the night’s live Bush Tucker trial, which in this case consisted of various school-related activities with an evil twist.

All eyes and cameras on Katie, she seemed highly pensive about the whole thing. She was visibly shaking and was putting in an Oscar-winning performance of being scared to death, and yet battled her way through the 12-stars-to-collect challenge, only managing to fail on collecting three of them.

It was during this initially painfully embarrassing television treat that I started to feel something happen to me. As Katie suffered through the first couple of challenges, and then failed the one where she was drinking some insect milkshake concoction (only to very cleverly state that she would only quit ‘if her team-mates said it was ok’), I started to get behind her on the challenges, and both my girlfriend and I were as good as cheering her on to complete the final challenges at the end.

Once the show finished we realised exactly what was happening to us. We were starting to actually like this girl…

I’m a Celebrity, Get Me Out of Here is, for this year only, The Katie Price (aka Jordan) show, and I don’t like it. I can’t help but feel, however, that she’ll have a lot more fans come the end of the season due to her fabulous acting/marketing skills. Maybe she is more of a businesswoman than I thought.

Excel is rubbish at...

noreply@blogger.com (Matt) — Tue, 19 May 2009 19:57:00 +0000

...tidying up the living room. No use whatsoever.

Simple charting without creating charts!

noreply@blogger.com (Matt) — Sun, 17 May 2009 12:00:00 +0000

In Excel there's a not often used function, REPT, which allows you to create simple chart effects without having to create a graph yourself. I'll show you how in this tutorial.

In the table below there's a list of scores taken in a test. The scores run from 0 to 10. What we want to do is create a simple chart which shows the distribution of the scores graphically by the side of the data table. Here's where the REPT function steps in..

REPT works by repeating a specified string of text a number of times, and works like this:-

=REPT(text to repeat, number of times to repeat)

So let's get cracking. Type the following into cell C2:-

and press Enter..

As you can see, the function has shown three star symbols as its output. All we now need to do is copy the function down the rest of the scores, which gives us the result below:-

And it's that easy. We now have a simple visual indication of the scores received. Handy eh?

A useful tip here is to change the font to one of the more unusual, pictorial style fonts such as Wingdings, and that way you can really improve how your graph might look. Check out the examples below for ideas:-

Both of these were done using the 'Webdings' font (characters used were '=' and '4', which give the results shown here).

To further spruce up your data you could also apply conditional formatting to your little charts to display them in different colours based on values falling within certain ranges. Give it a try yourself.

RAG (Red, Amber and Green) reporting - simple conditional formatting tutorial

noreply@blogger.com (Matt) — Sat, 16 May 2009 11:10:00 +0000

A question which I often seem to be asked is how to create a sheet where there is a visible RAG status which is displayed with the data to enable highlighting information you may need to follow up on or deal with straight away without scanning down a list of data.

If you don't already know, RAG stands for 'Red, Amber, Green' and is often used in reporting in this way. For example, in the image below we have some data on sales figures of five employees.

The boss figures that any profits over 3,500 is good and needs no monitoring at all. Values between 1,501 and 3500 are ok, but he wants them highlighting so he can monitor them in the future. He isn't particularly worried about them, but they are a little lower than he'd like so wants to make sure he has a reference for the future and any annual appraisals. Anything lower than this worries him greatly and he wants them highlighted red so he can pull them into the office and have a chat with them about their performance.

Simple, yet effective, and you can set this up automatically using conditional formatting. Here's how:-

Starting with your initial data, highlight the values in the list.

Then, in the menu bar at the top of the screen, choose Format>Conditional Formatting...

In the dialog box which appears you'll see there are various options, but don't let them worry you too much as they're fairly self-explanatory.

All you need do at this stage is keep the 'Cell Value is' and 'between' drop-down lists as they are. Pop '0' and '1500' into the two boxes and then click the Format... button, where you can select how you want any cells whose values fall between this range will be displayed. If you regularly use formatting in your sheets you'll recognise the next dialog as a bit of a stripped-down version of the format dialog. Click on the last tab to select 'patterns' and choose a red background, as displayed below.

Now, click the ok button and we move onto the next condition. Click the Add>> button to expand the dialog so we can do just that. Again keeping the first couple of boxes clear set this condition to be between 1501 and 3500.

Follow the same instructions as above and set the background for cells meeting these criteria to amber (yellow).

When you've done this click the Add>> button once more and create a condition which will highlight any cells over 3500 with a green background, as shown below:-

And that's pretty much it. If you now click the 'Ok' button the conditional formatting will be applied to your selected data and you should end up with the data originally shown at the beginning of this post.

So..now you know the very basic elements of conditional formatting, try to use them yourself to create your own RAG reports. There are a hundred and one things you can do with this kind of formatting however, not just RAG reporting. With a bit of thought these can also be used to create some extremely impressive reports and dashboard-style worksheets.

Your Questions Answered!!

noreply@blogger.com (Matt) — Tue, 24 Mar 2009 18:20:00 +0000

This blog has now been up and running since 2005 and seems to have been successful enough amongst those of you who've visited.

After a couple of weeks of writing posts (more new ones soon, promise) to fill the pages of the blog I decided I'd bring it back to you guys, the people who the blog is for. Do you have a tutorial you're yet to find, or a problem with Excel (or anything for that matter) that you want a solution to? Please post your problem, question, whatever into the comments and I'll add it to my list of potential future articles.

I obviously probably won't be able to do something with all of them, but please do post away and I'll do the best I can. As I said before this isn't my blog as such, it's yours. You are the people who read it and if I can make it better for you, and keep you as regular visitors then I'll do all I can to meet that goal.

So...got a question? Excel problem need sorting? Get commenting and I'll see what I can do...

Hope to hear from you soon guys.
Mat