It would be quite difficult to find a business that didn’t use a spreadsheet for something. They are particularly versatile but, a lot of the time, their tricks are not widely known. Here are just a few useful Excel functions and shortcuts which you might like to add to your arsenal. This document is focused on Excel on a Windows PC but most, if not all, of these functions are also available in Excel for Mac, Numbers and Google Sheets, the syntax may just be slightly different.
Combine Data from Two Cells
If you have a spreadsheet of clients, you will probably have a cell for each of the two halves of their names. It could, however, also be useful to have the combined text in a single cell. There is an Excel function which can achieve this, the CONCAT function.
The basic syntax is =CONCAT(Text1,Text2) where Text1 is the cell with the First Name and Text2 is the cell with the Surname but, you would probably want to add a space between the two words =CONCAT(Text1,” “,Text2). Alternatively, you might want to put the surname first with a comma before the first name =CONCAT(Text2,”,”,Text1)
| First Name | Surname | Full Name |
| Alan | Jones | AlanJones |
| Fred | Smith | Fred Smith |
| Bob | Williams | Williams,Bob |
Also, the same result may be achieved by using the & operator –
=Text1&Text2
=Text1&” ”&Text2
=Text2&”,”&Text1
Add a Carriage-Return within a cell
If you are entering text into an Excel cell, sometimes it would look better if you could break up the words with a carriage return. However, just pressing Enter will not give the desired effect. Use these Excel functions or shortcuts instead.
Windows – ALT+Enter
Mac – Option+Enter
Within a formula such as CONCAT above – CHAR(10) e.g. =CONCAT(Text1,CHAR(10),Text2)
Raise a number to a power
Working out what 3 to the power of 7 equates to is easy on a scientific calculator. However, you’re probably not going to want to put 3*3*3*3*3*3*3 into an excel spreadsheet – use this shortcut instead:
=3^7
This will give the result of 2187.
Correct Capitalisation on text
This could be a very useful tool to enable you tidy up a spreadsheet quickly. Quite often when people sign up to services on websites, they don’t use capital letters at the beginning of their names. If you have extracted this information to use in a customer database or mailing list, you would likely want to be sure that the text looks right.
By using the formula =PROPER(Cell Reference) you can automatically make sure that the first letter of each word is uppercase and all subsequent letters are lower case.
| Original Text | Proper |
| paul jones | Paul Jones |
| fred smiTh | Fred Smith |
| portsmouth | Portsmouth |
| XYZ369 | Xyz369 |
Random Number Generator
This standard Excel function could be helpful if you’re running a competition for subscribers. Rather than actually putting all the names into a hat, you can easily generate a random number between two points.
Once you know how many numbers you have to choose from, you can simply use the smallest and largest numbers into the formula: =RANDBETWEEN(1,150)
The number will be regenerated each time the spreadsheet is recalculated or, after F9 is pressed.
Conversion to Roman Numerals
I don’t think that this is taught in schools anymore but I’m pretty sure that they do still use Roman Numerals on BBC TV programme credits! If you do ever have need to convert numbers to Roman Numerals, this can be done simply with the following formula:
=ROMAN(Number)
eg. =ROMAN(2019) will give you MMXIX
After the number, you can also add ,1 ,2 ,3 or ,4 to the formula to format the Roman Numeral increasingly concisely.
Trim unwanted spaces from cells
Another issue found with imported text can be the spurious addition of spaces into the text. This function allows you to quickly tidy up text in a list by removing unwanted spaces. Any spaces in the cell before or after the text will be removed completely and multiple spaces within the text will be reduced to a single space.
The formula used for this function is –
=TRIM(Cell Reference)
| Original Text | Trimmed Text |
| ABCD | ABCD |
| A. B. C. D | A. B. C. D |
| Alan Jones | Alan Jones |
| ABCD. | ABCD. |
If you want to find out more ways that you can streamline your spreadsheets, have a look at Microsoft’s list of Excel functions and shortcuts.
Alternatively, let someone else worry about getting the most out of your spreadsheets – a Virtual Assistant could be just the person to save you from your Excel woes! Visit my website to see how I could help your business grow.