Top Excel Functions You Have to Know To Be a PRO.
A spreadsheet is a must owned piece of software for any business. Microsoft Office Excel is the superior brand for this type of software – as it offers some of the most useful functions that can automate much of your administration.
Excel is a means to not only store and maintain information – it is a successful way to analyse this data and action potential results. Here we offer you a list of the 20 top Excel functions that could revolutionise your business – whether small, medium or large in scale.
one: Charts
One of the most recognisable functions of a spreadsheet is to help you prepare charts – whether is bar, line – or the many variations offered by the software. Once information is put into a table, then it can be highlighted, and the chart wizard used. Be aware that there is likely a fair bit of formatting needed to make your chart work for your professional needs.
It is important to learn to use charts well because they can shorten the time it takes to analyse and interpret complex data sets. You can also use the charts to make more effective marketing material – especially for pitches and presentations.
Although this may seem one of the more basic functions of a spreadsheet – it also the most important and most useful – so cannot go without mention and should always come at the top of any list.
two: Conditional formatting
Often overlooked – conditional formatting is a great way to analyse and action data sets. You can change the colour of text, put it in italics or bold – depending on the set criteria you lay out. On a simple level, you can have accounts overdrawn shown in red – and those with positive credit rating displayed in green. However, this is just one basic application of the function – and it is worth spending some time thinking creatively about how your team looks at data and what they need to see at a glance.
three: VLOOKUP
It may be that you have information in one table that will directly relate to another. You may have a large data set and you want to create a more specialised data set based on set criteria. Using the VLOOKUP function – which can be designed using the wizard option – you can pull this data across in an instant.
four: Lookup_Value
This lookup option is useful for finding values in a larger table – as the name suggests really. It will pull out data from the cells with the stated value and include it in the new table.
five: Table Array
This is like the lookup_value – however – rather than a single value you can select a range from which information is drawn. Here you can define the extent of the data you want to include in your new table.
six: col_index_num
This lookup option simply connects one table to another – and information from a column of one spreadsheet is drawn into the new one.
seven: range_lookup
This helps you to specify how closely you want the data to match your criteria. TRUE in your function will present information closest to what you want to find when there is nothing that directly matches your lookup. The FALSE command will return a N/A response if no corresponding criteria are discovered.
eight: IF Function
The IF function is a great way of undertaking a forecasting exercise. The point is to ask for a True or False response to a set parameter. IF(Criteria, True, False, Value) – this is then applied to selected columns to checkout whether your assumptions are accurate, or not.
nine: SUMIF Function
This If Function does pretty much what it suggests. The spreadsheet will report on any cells that adds up the value that you set out in your criteria and in the range of cells you dictate. It will then report these in your new table.
ten: COUNTIF Function
This is like SUMIF except it shows you the data and does not just show the calculation that matches the value that you requested.
eleven: AND Function
Simply put – this allows you to offer more than one criterion when searching for variables. The value is then returned true or false – but this helps you to analyse a complex set of data using multiple criteria.
twelve: OR Function
This is the same as AND but instead it will report TRUE if either criterion is shown – there is not a requirement for both. If none of the criterion are present it will display FALSE.
thirteen: LEFT
This function pulls data from the left of the column you are writing the function within.
fourteen: RIGHT
This function pulls data from the right of the column you are writing the function within.
fifteen: CONCATENATION
This is both LEFT and RIGHT combined. A new column of data is prepared by setting the variable to pull information from a set range from both the left and the right of the column.
sixteen: NOW function
This automatically inserts a column where the date is shown for when the data was entered or edited.
seventeen: PV Function
If you have ever wanted a robot who acts as your personal financial advisor, then this PV Function is as close as you will get. It will help you with some pretty essential financial tasks such as calculating rates, periods for investment, future value and payment per period. It can also calculate other potential variables that you can design bespoke to your company.
eighteen: Time
There are a lot of time functions, including WEEKNUM() WORKDAY () EDATE() NETWEORKDAYS () YEARFRAC () and more. The point here is to provide automated updates to your spreadsheet – allowing for more accurate tracking.
nineteen: PROPER function
You can set criteria by which certain words in a sentence are capitalised under certain criteria. This is completely customisable to your needs for emphasis within a sentence.
twenty: Customised max and min function
The max and min function – along with sum and average – are all relatively basic functions not covered here. However, there is an advanced option with MAX and MIN – where you can select cells by order and degree. For instance, you can select the third highest piece of data in a data set.
