10 Excel Functions to Help Your Business
I have heard a few people say that using Excel is risky as there are always errors and it is difficult to use. Well, when there are mistakes in Excel, 99 out of 100 are human errors, and the other one is probably not entirely Excel’s fault either. As for being difficult, maybe, but that is why there are people like us who will create spreadsheets for you! Have a look at our website to see what else we do, and what we can do for you. There are also some free downloads and brochures. If you are an Excel user, and you would like some tips, take a look at the useful functions below, and then sign up to receive our monthly newsletter, where there will be more tips.
Here are 10 Excel functions and tools that will benefit your business:
- VLOOKUP – This function is very handy. If you have a table of information and you wish to extract some data relating to a category of sorts, this is the one to use. In other words if you have a list of clients, all with an address alongside, you will be able to select your list of clients from a drop down list (Data Validation) and have their address appear alongside. There is actually a better way of doing this, if you use a combination of INDEX and MATCH, it works much better.
- IFERROR – This is a handy function to eliminate all of the horrible error signs. If it is an error you can choose what to show in that cell, however if the cell has a value, it will show that value.
- IF – This function is useful for doing checks and selecting appropriate data. If the figure is less than one, have the cell show the word ‘loss’ etc. This is like the IFERROR function, but it does not refer only to errors, you get to choose what not to show!
- Graphs – Graphs are always easier to use to get an all round picture. Excel have had graphs for ages, but the older versions are a bit more tricky to use. Excel 2013 have automatic graphs, so you highlight your data and it creates the graph. If you want something specific it will require some tweaking, but you can get the basics easily.
- INDEX – This is similar to VLOOKUP, but it indexes data based on a word that it is looking for. This is actually very handy with VLOOKUP if there is more than one column to choose from. If you have the client list and address, but you also have the secondary address, VLOOKUP and INDEX will be able to find the address and check whether you want the initial or secondary address. It then replies with the selected data!
- Custom Formatting – I have come across a few clients who have spreadsheets where they colour in the cell with a specific colour based on the information. Custom formatting does this automatically. Sometimes a bit confusing, but once you have set it up, it won’t let you down.
- TODAY – This function inserts today’s date. This is handy when doing project management spreadsheets as it will allow you to do countdowns to dates (with some handy equations), or just show you today’s date.
- Data Validation – This allows you to limit what is entered into a cell, or to have a drop down list selection, and what data to have in the drop down list. If you locked the sheet and have a cell that you don’t want to lock but don’t always want to be available, this is what you use.
- Protect Sheet – This means that people can’t go and delete your hard worked equations. With the use of the ‘hidden’ function, it also hides your equations so that the person using the document can’t see how you achieved what you did. This means that the people will only be able to enter information into the cells that you allow them to, and they can’t see any of the workings, leaving your equations a secret!
- Share Workbook – This means that various people can use the workbook simultaneously. It does sometimes limit the possibilities, but it is handy to do. This means that as you use and save the spreadsheet, it will show the updates on all users PCs. If two people save at the same time, it will ask the administrator which option to take.
If you are an Excel genius, you’ll probably know about these, but if you are a new user, these may seem a little advanced. Don’t fear if this does seem a little beyond what you can do, but if you would like to take full advantage of these functions and more, please contact us and we’ll be happy to create a bespoke/custom spreadsheet for your business!