6 Game-Changing Excel Functions for Accountants
When you're deep in numbers and reports, Excel is your best friend. But are you using it to its full potential? Let’s talk about six Excel functions that are total game-changers for anyone in accounting. These are the kinds of tricks that can make your work a lot smoother and faster.
1. =SUMIF(S): Your Quick Math Buddy
Ever need to quickly add up certain things, like how much was spent on office supplies last month? =SUMIF(S) is perfect for that. Instead of going through each entry by hand, this function lets you sum up all the numbers that match your specific criteria in a snap. It’s a real-time-saver and keeps things accurate.
For example: If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
Learn more on how to use the formulate by watching the full YouTube vi deo here.
2. XLOOKUP: Find Anything Fast
If you've ever wished for a "find" feature in Excel for numbers, XLOOKUP is it. Say you need the amount for a particular invoice number from a huge list, find the cost of a car part using its part number, or discover an employee's name by searching their employee ID. Just use XLOOKUP to track it down. It’s like having a search engine right in your spreadsheet, helping you quickly find exactly what you need.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example: Finding the dial code for Brazil in a table.
Step 1. Identify the lookup_value: The value to search for - include this in a separate cell. i.e. Brazil in F2
Step 2. Identify the lookup_array: The range to search for the lookup value B2:B11
Step 3. Identify the return_array: The range where to find the requested value D2:D11
Learn how to do it: watch the full YouTube video here.
3. =COUNTIF(S): The Number Spotter
Need to compare lists or see how many times something shows up? =COUNTIF(S) is the detective you hire. It’s great for checking things like whether all transactions have been recorded or if there are any duplicates. This function is your go-to for making sure everything lines up just right.
Example: How many times the company has incurred travel expenses in a quarter? Or how many salespeople in the East region have 50, or more orders?
The formula: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…). Basically, you need to have at least one range specified from which the selection is made and a criterion to select from the range.
criteria_range 1 (required): Include the range of information that is looked at. I.e. B2:B11.
criteria 1 (required): The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".
Additional ranges and their associated criteria are optional.
criteria_range 2, criteria 3, ... (optional): This can get extremely complex with up to 127 range/criteria pairs allowed.
Learn how to do it: watch the full YouTube video here.
4. =IF: Your If-This-Then-That
The =IF function is all about choices. It lets you set rules in your spreadsheet that say, "If this thing happens, then do that." It’s really handy for categorising things automatically, like marking expenses as "paid" or "unpaid." It’s like automating part of your thinking process in Excel.
An IF statement has two results. The first result is when the criteria is True, the second if it is False. Both needs to be defined, for example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
The formula: =IF(the condition to test,”if true”,”if false”)
Example: Is the fee over or under budget? Learn how to do it: watch the full YouTube video here.
5. =EOMONTH: The Date Whisperer
You can use =EOMONTH to calculate maturity dates for accounts payable or accounts receivable or due dates that fall on the last day of the month.
The formula: EOMONTH(start_date, months)
Start_date (required): A date that represents the starting date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Note that problems can occur if dates are entered as text.
Months (required): The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
Examples:
Starting date: 1 January 2024
Date of the last day of the month, one month after the date in A2.
Formula: =EOMONTH(A2,1)
Result: 2/28/2024
2. Date of the last day of the month, three month before the date in A2.
Formula: =EOMONTH(A2,-3)
Result: 10/31/2023
Learn more about the =EOMONTH function here.
6. =UNIQUE: The Duplicate Zapper
Tired of seeing the same thing twice or more in your lists? =UNIQUE cleans up your data by removing duplicates, leaving you with only one of each entry. This is perfect for when you’re putting together lists of clients or transactions and only want to see each one once. It keeps everything neat and tidy.
Follow these steps remove duplicate information:
Select the range of cells that contain duplicates in your worksheet.
On the Data tab, click Remove Duplicates (in the Data Tools group).
Under Columns, select one or more columns. To quickly select all columns, click Select All, to quickly clear all columns, click Unselect All.
Click OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain. Click OK to dismiss this message.
Undo the change by click Undo (or pressing Ctrl+Z on the keyboard).
Learn how to do it: watch the full YouTube video here.
With this function, only the first occurrence of the duplicated information is kept, with all the duplicated information being removed permanently. Making a copy of the original range of cells or table to another worksheet or workbook before removing duplicate values is recommended.
Make Excel your number 1 tool
Using these six functions can really step up your Excel game, making you faster and more accurate in your accounting work. They turn Excel into not just a spreadsheet tool but a powerful assistant for your day-to-day tasks.
Sharpen your Excel skills with CIBA’s Excel for Bookkeepers CPD
What you will learn:Practical Creation of Financial Sheets:The attendee will know how to design an Excel sheet that effectively converts bank statements into a trial balance, streamlining their bookkeeping process.Mastery of Essential Functions:Attendees will become proficient in key Excel functions that every bookkeeper should be familiar with, enhancing their efficiency and accuracy in financial tasks.Understanding of 3D Formulas:Participants will grasp the concept and application of 3D formulas in Excel, allowing them to work across multiple sheets and workbooks with ease.Efficient Use of Shortcuts: Attendees will learn and internalize valuable Excel shortcuts, significantly reducing the time spent on routine tasks and boosting their productivity.Access to Additional Resources:By the end of the webinar, participants will be equipped with extra resources and materials to further refine their Excel skills and stay updated in the realm of bookkeeping.
