In the realm of spreadsheet software, Microsoft Excel reigns supreme. Whether you’re in finance, analyse data, are studying, or running a small business, Excel is indispensable for organising, analysing, and visualising your data.
While Excel offers a plethora of features, functions, and formulas, mastering a select few can significantly enhance your workflow. In this blog, I unveil seven essential functions and formulas that are worth your time to learn.
I’ll first cover my top three core formulas for managing data, with an overview of their purpose and explain exactly how to use them: VLOOKUP, EOMONTH, and IF. Then I’ll more briefly cover four more ‘bonus’ functions, to prompt further ideas on your data management…
VLOOKUP (Vertical Lookup)
VLOOKUP is a powerhouse function for searching and retrieving data from a table based on a unique identifier. Whether you’re managing inventory, analysing sales data, or compiling employee information, VLOOKUP can save you hours of manual effort marrying up disparate datasets.
By simply specifying the lookup value, table array, column index number, and optional range_lookup parameter, Excel swiftly fetches the desired data. This function is invaluable for tasks like matching product IDs to their corresponding prices or retrieving customer details from a database.
It works by looking for a value you specify in the leftmost column of a multi-column table you’re interested in (e.g. a client database), then retrieves for you the datapoint in the column you want for that same row (e.g. the associated address of a client).
It’s called ‘vertical’ lookup because Excel is working it’s way from top to bottom vertically down a table to find the row you’re interested in. It then reads across that row for you to locate and retrieve the datapoint you want. Conversely, HLOOKUP works horizontally, from left to right in a table, to find the column you’re interested in, then reads down that column to find your datapoint. Most of the time, and if you’re using a normal data structure, you’ll want VLOOKUP.
As with all formulas, you can use the function wizard in Excel to take you through the steps, until you’re familiar enough to use just the formula bar. I might also do a quick how-to video on this useful function in due course. But for now, here’s each part of the formula explained…
- Lookup_value: This is the value you wanting to find in a table of data, to locate the correct row for retrieval of the datapoint you’re really interested in. This can be a cell reference (e.g. A1) or you can manually enter a value.
- Table_array: This is the range of data in which you want to find and retrieve something. Your ‘lookup value’ must be in the first column of your selected range. You can then specify the column you want to retrieve the ‘match’ from by using the…
- Col_index_num: Here you instruct Excel as to how many columns into your table it should look to the right, once it’s matched the row against your lookup value. It will then retrieve the datapoint of interest for the column you specify. E.g. if your of interest is in the fifth column, just type 5.
- Range_lookup: Generally, this should always be set to FALSE, as you’re probably looking for an exact match of your lookup value to your table’s first column.
Here’s some completed simple examples of how your VLOOKUP formula would look, depending on whether you’re looking up stuff in the same sheet, between the sheets (oo-err!), or across different workbooks:
I took that from Excel’s inbuilt ‘Help’ tab of the Ribbon, which I would advise using occasionally. It can be quite helpful if you know what you’re looking for!
EOMONTH Function (End of Month)
Managing dates in Excel is a common task, especially when dealing with financial data, project timelines, or scheduling. The EOMONTH function simplifies the process of calculating the end of a month relative to a given date. Whether you’re forecasting cash flows, scheduling project milestones, or analysing monthly sales trends, EOMONTH proves invaluable.
The syntax of the EOMONTH function is straightforward:
=EOMONTH(start_date, months)
Here, start_date is the initial date from which you want to calculate the end of the month, and months is the number of months in the future or past you want to consider.
For instance, suppose you have a start date in cell A1, and you want to find the end of the month for that date. You would use the following simple formula:
=EOMONTH(A1, 0)
This formula returns the end of the month for the date in A1. So whether your date in that cell is 01/02/2024 or 29/02/2024, the returned result is standardised to 29/02/2024 in both cases.
You can adjust the months argument to calculate the end of the month for future or past dates. But generally, I always use it as above (0), simply to categorise all my dates into single months. Categorising dates as months helps you analyse and summarise with pivot tables, allowing you to then present and analyse more easily into monthly trend tracking charts.
While EOMONTH is useful for categorising your date (or even date and time) into broader months, the IF formula is far more powerful for wider categorisation…
Creating Categories with the IF Formula
Categorising data is essential for organising and analysing information in Excel. The IF function is a powerful tool for creating categories based on specific criteria for text, numerical, or any other type of data. Whether you’re segmenting customers, classifying products, or evaluating survey responses, IF allows you to assign labels or group data according to predefined conditions.
The syntax of the IF function is as follows:
=IF(logical_test, value_if_true, value_if_false)
Here, logical_test is the condition you are evaluating, value_if_true is the value or action to take if the condition is met, and value_if_false is the value or action to take if the condition is not met.
For example, let’s say you have a list of volumes in column A, and you want to categorise them as “High,” “Medium,” or “Low” gradings based on your predefined thresholds. You can use the IF function like this:
=IF(A2 > 1000, “High”, IF(A1 > 500, “Medium”, “Low”))
This formula checks if the value in cell A2 is greater than 1000. If it is, it returns “High.” If not, it checks if the value is greater than 500. Then if that condition is met, it returns “Medium”. Otherwise, it returns “Low.” You can then colour-code your categories with conditional formatting to help draw attention to particular datapoints. Or indeed, use those categories when summarising your data into charts and graphs.
By utilising the EOMONTH function for date-related calculations and harnessing the IF function for creating custom categories, you can enhance your Excel skills and effectively manage and analyse your data with precision and efficiency. Now here’s a quick overview of some more functions you might not have heard of to broaden your skills further…
SUMIFS (Sum with Multiple Criteria)
You may be familiar with using SUMIF to create a sum of values where a certain criterion is met. For example, you want to total all sales of a particular product type, without having to run pivot tables and such. But when you need to sum values based on multiple criteria, SUMIFS comes to the rescue.
The versatile SUMIFS function allows you to specify one or more conditions to filter data before performing the summation. Whether you’re calculating total sales for a specific region within a certain time frame, or tallying expenses by category, SUMIFS provides the flexibility and precision you need.
With intuitive syntax and ability to handle complex criteria, SUMIFS empowers you to extract meaningful insights from your datasets with ease. Again, the formula wizard takes you through the steps, or try exploring the Help option on the Ribbon.
IFERROR (Error Handling)
Excel sheets often contain formulas that reference other cells or external data sources. When these formulas encounter errors, such as #DIV/0! or #VALUE!, it can disrupt your workflow and lead to inaccuracies. Enter IFERROR, a lifesaver for error handling. By wrapping your formulas with IFERROR, you can gracefully handle errors by specifying alternate values or messages to display in case of failure. This function not only improves the robustness of your spreadsheets but also enhances their readability and user-friendliness.
COUNTIF (Count with Condition)
Counting occurrences based on specific criteria is a common task in Excel. Whether you’re tracking the number of completed tasks, identifying outliers in a dataset, or analysing survey responses, COUNTIF simplifies the process. With COUNTIF, you can specify a range and a condition, and Excel will count the number of cells that meet the given criteria. This function is invaluable for generating reports, identifying trends, and gaining insights into your data with precision and efficiency.
PMT (Payment Calculation)
Financial planning and analysis often require calculating loan payments, determining mortgage affordability, or assessing investment returns. Enter PMT, the go-to function for such financial calculations.
This is a really handy function to finish with. By specifying parameters like interest rate, loan term, and initial amount, PMT computes the periodic payment for a loan or investment. Whether you’re planning your budget, evaluating investment options, or comparing financing alternatives, PMT empowers you to make informed decisions with confidence.
Mastering these functions and formulas can will help you tackle a wide range of tasks efficiently and effectively. Whether you’re a novice Excel user or a seasoned spreadsheet creator, incorporating these tools into your repertoire will undoubtedly enhance your productivity and streamline your workflow in data analysis and decision-making. So why not unleash your inner spreadsheet wizardry!
In my spare time, I’m passionate about compiling valuable content to share with others, aiming to offer insights, inspiration, and knowledge. If you’ve benefitted and/or appreciate it, and can afford to, I invite you to contribute a coffee. Your generous support helps fuel my knowledge-sharing efforts and keeps the inspiration for new content flowing.
Kind Regards, Adrian
I hope this guidance is supportive and saves some MS Office frustrations! Subscribe to my blog and EATO YouTube channel for more useful info. Liking the content? Please like and share with friends and colleagues who may also find it useful. If so inclined, supporting my work helps keep free content flowing. Want fantastic, time-saving templates or dashboards tailored to you and your business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below, including what tutorials or templates you’d like created.