5 SIMPLE EXCEL TIPS

Below you’ll find five simple yet powerful formula / functions within Excel which you might not know about. Hopefully they’ll help save you some time!

(1) =PROPER

This formula will take text and, as the formula name suggests, present it properly. For example you have a column full of names with all small letters or a mix of small and caps and you want to tidy it up, =proper will give the words a capital letter. Examples below.

(2) =TRIM

This formula will allow you to cleanse a data set, removing any spaces before or after text. This is especially useful when you are trying to analyse data or present it in pivot tables and you need everything to be consistently named.

(3) =RIGHT or =LEFT or =MID

Let’s say you have a consistent coding system for a piece of data and you want to extract an element of it without too much effort. For example 3579-LON-PERM might be a data set for an employee number, location and employment type. If you have this for 10,000 records and want to separately pull out any of those pieces of information you can use =right, =left or =mid.

=RIGHT – This will pull the text from the right hand side of a text field for a given number of characters. In the case of my example if we want to extract the employment type we would use =RIGHT for 4 characters.

=LEFT – The same as =RIGHT but it works from the left hand side, so this time if we want just the employee number we would use =LEFT for 4 characters.

=MID – This enables you to pull text from the middle of a field. We would use it if we want to extract the location. When you use =MID, it asks you how many characters into the text it should start the extract and then for how many characters. So in my example if we want to extract LON, we would say =MID, starting at character 6, for 3 characters.

(4) Stitching text together

Right, Left and Mid extracts text into its own field. There are also ways you can stitch text from multiple cells together into a single cell. This is known as =CONCATENATE but there’s a simple way to perform this action.

Let’s say you have peoples first names in one column and their second names in another column. You want to have their full name in a single cell. Very simply, in your empty column you would type =, then click on the first name, then type &” “&, then click on the second name. The & sign is the instruction to link together the text, the “ “ is included to instruct Excel to include a space between the names.

You could even combine two of the formula's from this article and make the names proper whilst combining them together.

(5) Dragging for Increments (or not)

If you sometimes find that Excel wants to decide what to do with your input when you drag it down or across cells, there’s a simple trick to changing its mind!

For example if I enter the number 5 into a cell, and when I drag down I want it to populate 6, 7, 8, 9, 10 – Excel actually decides it would rather just enter the number 5 again into those cells. Holding down control when you start the drag until you let go will switch it to increments (or vice versa) – you will notice a little ‘+’ sign appear near your cursor and the number will change from a 5 in the little box to the number it will become.

There we have it - any questions leave a comment or send me a message.

Cheers

Adam