EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
As soon as I start talking about analysing data with clients, I see their eyes roll and something switches off in their brain.
They’re listening, but they’re not taking it in… I’ve often wondered why this happens and I think it’s because, for a lot of people, data analysis sounds daunting and scientific. But it doesn’t have to be.
In fact, analysing data (and the ease with which it can be done) is one of my absolute favourite things about Excel. You don’t need a lab coat and a PhD to delve deep into your business.
Excel’s functions are simple yet incredibly powerful and effective tools that will help you get a better understanding of your business and its performance.
There’s no need to grab your calculator and spend hours manually crunching numbers or searching large data sets for a needle in the haystack (trust me, I wouldn’t be in this business if there was!)
Functions are pre-built formulas that perform calculations, manipulate data, and provide valuable results. They allow you to automate repetitive tasks, saving you time and ensuring accuracy in your analyses.
Think of functions as your very own Excel data analysis assistants.
Functions are a handy tool to have up your sleeve when working with Excel. They’re shortcuts to performing calculations within your spreadsheet that will help you get to the root of your data faster.
The great thing about functions is that they can be used to perform both simple and complex calculations.
In this blog, I’ll show you some of my favourite functions that will help you analyse your data in a flash. From basic calculations to advanced analytics, we’ll explore a treasure trove of essential Excel functions designed to unravel the insights hidden within your data.
Before we take a look at the essential Excel functions themselves, let’s take a second to understand a bit more about their makeup.
If you’re already familiar with functions and how they work, that’s ACE! Feel free to skip ahead to the next section where I’ll be diving into my list of essential functions for analysing your business data.
For those of you that are new to understanding the language of Excel, here are two things you need to know about the makeup of functions and how they work:
All Excel functions follow a specific syntax, consisting of the function name, arguments (inputs), and optional parameters. Understanding the syntax helps you effectively wield Excel’s functions.
Excel functions operate on cell references. By referring to specific cells or ranges, functions dynamically calculate values based on changing data, providing flexibility and adaptability.
Below is a list of the basic functions you should know when using Excel. Bookmark this page so that you have it to refer back to help you speed up your data analysis in future.
The SUM function is a workhorse for adding numerical values within a range.
Whether you need to calculate total sales, expenses, or revenue, SUM makes it a breeze.
Simply provide the range of cells containing the values you want to calculate, and watch as Excel adds them up for you.
For example, =SUM(A1:A20) adds the values of the numbers in cells A1 through to A20 (this will also ignore any text it finds in the range too, which is pretty cool!).
The AVERAGE function calculates the mean of a range of values.
Use this function to determine information like your average sales per month, average customer satisfaction ratings, or average time spent on a task.
AVERAGE enables you to grasp the central tendency of your data with ease.
For example, =AVERAGE (A1:A20) will return the average of those numbers.
Counting is a fundamental task in data analysis, and the COUNT function is your go-to tool.
It counts the number of cells within a range that contain numerical values.
Use COUNT to get to the number of entries in a number field that is in a range or an array of numbers.
For example, if you were to use the function =COUNT(A1:A20) and only five of the cells in this selected range contained a number, the function would return the result, 5.
TIP: COUNTA would be used to count the number of cells in a range that was NOT blank (i.e. if they contained numbers, text, characters or dates).
Conditional analysis is a powerful technique, and the IF function is your secret weapon.
IF allows you to set conditions and perform different calculations based on whether those conditions are met. It allows you to make a logical comparison between a value and what you expect.
With IF, you can categorise data, calculate commissions based on sales targets, or flag anomalies in your data.
An IF statement can have two results, TRUE and FALSE.
For example, =IF(C2=”Yes”,1,2) is asking, IF(C2 = Yes, then return a 1, otherwise return a 2).
TIP: Use a Nested IF formula to include multiple conditions and outcomes… watch out for this in a future article.
VLOOKUP is an amazing function that allows you to search for specific data points in a table and retrieve related information from another column.
It’s perfect for matching data across multiple tables or finding specific data within a dataset.
VLOOKUP can be your trusted ally in customer analysis, product pricing, or inventory management (to name just a couple of its practical applications – there are LOADS more).
You might use a VLOOKUP to find the name of a product by its ID number, or the contact information for a customer by looking up their name.
Data analysis often involves combining text from different cells or columns. Enter CONCAT, the function that seamlessly merges text strings together.
Use it to create full names, concatenate addresses, create unique references or generate custom messages.
With CONCAT, you can conquer data manipulation challenges effortlessly.
CONCAT combines the text from multiple ranges and/or strings (but it doesn’t provide delimiter or IgnoreEmpty arguments, they must be adjusted for manually). There is a brand new formula: TEXTJOIN, which does negate some of the issues from CONCAT.
For users with older versions of Excel (pre-2019), you can use the CONCATENATE function to achieve the same results.
TIP: Use “ “ to create Concat formula i.e. =A2&B2
It’s quicker, simpler and easier to read than the full CONCAT or CONCATENATE formula (always use the longer version if you are trying to impress someone though!)
Now that you have the basic functions, let’s get a little more advanced so that you can dive even deeper into your data and get a more tailored analysis.
Do you ever find yourself needing to sum values based on specific conditions? SUMIF and SUMIFS are here to save the day.
These functions allow you to sum values that meet specific criteria.
The SUMIF function will sum the values in a range that meets a single criteria that you specify. For example, if you want to sum only the values in a column that are larger than 10.
SUMIFS on the other hand add arguments that meet multiple criteria you specify. For example, if you choose to sum the number of clients who reside in a particular city, and who spend over a certain value of money.
Building upon the COUNT function, COUNTIF and COUNTIFS enable you to count cells that meet specific criteria.
COUNTIF enables you to count the number of cells that meet a single criterion. For example, counting the number of times the name of a city appears in a customer list.
COUNTIFS on the other hand applies multiple criteria to cells to count the number of times all criteria are met.
Whether you’re using them to count customer orders above a certain value, track the number of overdue tasks, or count sales transactions within a given timeframe. COUNTIF and COUNTIFS bring precision to your data analysis endeavours.
Average with precision using AVERAGEIF and AVERAGEIFS. These functions allow you to calculate average values based on specific conditions.
AVERAGEIF returns the mean average of all the cells in a range that meet a given criteria. For example, you might ask the function to calculate the average of all transactions in a selected range that total more than £100.
The returned average will therefore only be based upon values within the range of £100 or more.
AVERAGEIFS will return the mean average of cells that meet multiple criteria.
Congratulations! You’ve uncovered the power of essential Excel functions to unlock the insights hidden within your business data.
By harnessing these functions, you can perform calculations, manipulate data, and gain valuable insights with ease (which is what we like!)
Embrace the power of these essential Excel functions, unleash them in your business analysis endeavours, and achieve real data-driven success. Get ready to unravel the potential of your business data, one function at a time!
If you’re still not quite sure where to start, then get in touch.
Book a FREE 30 consultation with me and let’s take a look at your business and where you can implement Excel to help make your operations more efficient. Click here to book.
EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
