# XL FORMULA FOCUS: SUM, AVERAGE, PRODUCT, POWER (an introduction to Excel math functions)

The picture above gives you a step by step encounter with 4 of Excel calculation functions. The numbers being used are in cells A1, B1 and C1. To make it easier to follow we’ve used the numbers 1, 2 and 3.

The blue cells contain the names of the functions we are using. Below each blue box is what you need to enter in the Formula Bar to make things happen. The next line shows the formula using the actual numbers instead of cell references. The line below that is the calculation that’s taking place, and last of all is the answer produced.

# XL FORMULA FOCUS: PROPER, UPPER, LOWER, LEN (your first four text functions)

The above picture gives a very quick lesson in four different functions. Here’s what they do:

# PROPER

Remember proper nouns from English class? Names of things, you know, stuff that deserves to start with a capital letter. This gives those words what they deserve. It’s particularly useful if you have a long list of names or address that have been entered in different ways, with all capitals, no capitals, or just plain shoddy capitalisation. This makes the first letter of each word a capital, and the rest lower case (of course it doesn’t work on words like XLCalibre (or McDonalds) where you need more than one capital).

# UPPER

This makes all letters in the specified text upper case. It’s that simple.

# LOWER

Guess what this does – it makes it all lower case.

# LEN

Just to throw you off guard, this tells you how many characters the text contains (LEN stands for length). XLCalibre is 9 characters long. Why is this useful? Well, the truth is on its not always that useful, but when combined with other functions it can be mighty handy. Trust me, if you’re going to learn more Excel functions you will want this in your repertoire.

So there you have it, an introduction to four text functions within the space of 5 minutes.

# Your First 10 Excel Keyboard Shortcuts (aka The Basics)

Never used a keyboard shortcut? Want to save time in Excel? Here’s where to start:

1. Ctrl+Z   Undo

2. Ctrl+Y   Redo

3. Ctrl+C   Copy

4. Ctrl+V   Paste

5. Ctrl+S   Save

6. Ctrl+N   New Workbook

7. Ctrl+O   Open

8. Ctrl+F   Find

9. Ctrl+P   Print

10. Ctrl+A   Select All

Have you got those down now? Good work, you’re on your way. If you’re ready to learn some more, we’ve got plenty more!

# XL FORMULA FOCUS: Find end of probation period using EDATE

So you’ve got a new employee, and you want to know when their probation period is over? The EDATE function is really straight forward.

=EDATE(Start_date,Months)

So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then close your brackets “)” and press enter.

Done.

RELATED: If you need to calculate length of service, you may want to look here.

# Learning to Learn Excel

Where do I start?

If you’ve decided to learn Excel that’s probably what you’re asking yourself. In some ways Excel is very simple – its just a big grid on a screen right? But the more you use it the more you realise it can do. So here are some tips on how I approached learning Excel that I think worked out and might be helpful:

Use Excel

This one might seem obvious, but if you’re an Excel beginner you probably don’t use it all that much at the moment. So make excuses to use it. Taking notes in a meeting on Word? Use Excel instead. Doing some simple calculations on a calculator? See if you can do it on Excel. Making a shopping list? … … You get the idea.

You need to get comfortable with the interface. I’m guessing that a lot of people are put off Excel because of preconceptions – it’s too complicated, I’m not a computer person, I’m not good at maths (don’t worry it kind of does the maths for you). But if you get used to the basics of moving around in Excel, you won’t find it so intimidating, and that’s when you can start to…

Explore!

See that tab on the menu that you’ve never used? Curious? Well click it and have a look! If you see something and think “I don’t know how to do that”, well this is your chance to learn. See the button next to the formula bar at the top of the screen, it’s got “fx” on it? Click it and explore the functions that Excel offers. Pick one that you like and try to learn it. The application guides you through it. And if you don’t find what you need there…

Look online

Your favourite search engine will probably be able to find you the answer to your question. Excel has been around for a long time, and it has a really big following. If you’re patient and use online searches, you can do much more ambitious stuff than you might expect.

Keyboard Shortcuts

It’s not until you learn a few keyboard shortcuts that you learn how much more efficient they make you. To remember them it helps to focus on one or two at a time and use the frequently, then adding one here or there as you go. We keep a regular stream of tips on our Twitter and Facebook feeds, which can drip feed you new things to try out. If you don’t use it you’ll lose. But if you’d never use it what was the point in learning it in the first place? Which is why you should…

Be Selective

If you’re not an analyst that’s does statistic-type things, don’t learn the statistical functions! Be curious by all means but start by spending your time on the stuff that you will probably use. Autofilters, freeze panes, print areas – everyday things that will be useful. Whatever those things are for you, focus on them. Be patient, once you have the basics you’ll start to pick more difficult, obscure things much easier if you want to.

Network

Are there other people in your office that use Excel? They might be able to teach you some tricks or help out with a problem. Most Excel-type-people I’ve met are quite keen to share (I reckon this is because Excel’s versatility and power can excite people and they may not have anyone to share this with in person). Or you can network online. Our LinkedIn group is full or HR, reward and payroll professionals that use Microsoft Excel. If you have a question or want to network with like-minded people it’s a good place to be (of course I’m biased). And there are hundreds of other groups, forums and networks out there to choose from.

Persistence

There are two sides to this. One is a bit like Use Excel above – use it every day if you can, and try to repeat newly learned skills often until they’re second nature. The second side is to do with patience. That long formula you’ve built yourself up to might look impossible to finish, but its not. If you keep going with it and Look online and Network then you will come out of the other side a better Excel user.