Excel accounting formulas and functions
Excel’s formulas and functions are the fuel behind the spreadsheets used to calculate report, forecast, and budget. The terms formulas and functions are often used interchangeably, while some choose to differentiate between the two. Technically, a formula is an equation created by the Excel user. When you enter =B1+B4, that’s a formula.
Functions, on the other hand, are predefined formulas that are programmed into Excel. Functions can be as simple as SUM, which adds up the value of the selected data in a row or column, or they can be as complex as IF, which provides information based on conditions you set. Without pre-programmed functions, it would take a long time to set up these formulas manually.
Common functions for accountants
The sheer number of functions offered by Excel is too vast to detail here. Functions for the categories of finance, logic, text, engineering and many others all exist in Excel. Every Excel user has their own essential functions – and every accountant has their favorites. Here are a few of the more complex and most commonly used functions for accountants and how to use them.
HLOOKUP and VLOOKUP
Do you want to know the rent for a particular lease? Or what leases are assigned to a particular field manager? As long as that data is captured in a spreadsheet with appropriately labeled columns and data, the LOOKUP functions can uncover that information for you.
INDEX MATCH
If you need a function with a little bit more power than VLOOKUP, INDEX MATCH actually combines two different functions, INDEX and MATCH. Unlike VLOOKUP, INDEX MATCH allows you to select the column that you want to search, and it even “remembers” that column when you insert another column into your spreadsheet. That’s just one of the key differences that’s driving many Excel users to favor INDEX MATCH over VLOOKUP, regardless of their task.
Go To Special
So you’ve got a spreadsheet full of values, formulas, and other important data. If you had to manually search for every blank cell or formula, you’d lose hours of time. Go To Special does the job for you. You can access it three different ways:
- F5 > Special.
- Ctrl+G > Special.
- From the Home tab, click Find & Select > Go To Special (on the far right of the ribbon).
From here you can select the type of data you want to find:
IRR
Determining the internal rate of return is important for making capital budgeting decisions because it allows you to calculate the profitability of your investments. The IRR function is a key step in determining the discount rate of your lease obligations, among other measurements.
IF, AND, and OR
IF, AND, and OR allow you to “ask” Excel to retrieve data based on certain logical conditions. Using these functions makes it easy for you to retrieve data without having to create a report for every condition. So, if you have a spreadsheet that includes all of your sales for the quarter, you can use IF to isolate sales above or below a certain amount. You can use OR to show all sales that came directly from your website or from a third-party partner. Or you can use AND to identify sales generated in a certain region in a particular week.
Excel accounting practices
Now that we’ve gone over some common formulas and functions let’s look into some practices that’ll make Excel easier to use.
Our favorite Excel shortcuts
We interviewed a few of our in-house accountants to get an idea of how they use Excel. These tips can help you move faster in the program and handle your reporting with ease. Most of the tips below use hotkeys, which are keyboard shortcuts that help you do the things you need in Excel quicker.
Formatting shortcuts
- Alt + H + B will bring up the ‘borders’ feature.
- Alt + H + B + N clears all borders from a cell.
- Alt + H + B + T will produce the thick black underline in the selected cell.
- Alt + H + B + U this creates the summation line (a single line at the top of the cell and double line at the bottom of the cell).
- Alt H + F + P uses the format painter, which will take whatever format you have on that cell (colors, text size, font, etc.) and copies it so you can apply it to other cells.
- Alt + H + H + N removes the background color of a cell.
- Alt + H + O + A autofits column height.
- Alt + H + O + I autofits column width.
General shortcuts
- Alt + A + T will apply a filter.
- Alt + CTRL + V allows you to paste only copied values, not formulas.
- Alt + E + S pastes a special menu.
- Alt + I + C lets you add a new column by selecting a column to the right of where you want to add the new column.
- Alt + I + R lets you add a new row by selecting the row below where you want to add the new row.
- Alt + W + F + F allows you to freeze all cells above the cursor.
- Ctrl shift and then an arrow will highlight either the row or the column.
- Ctrl + space highlights column.
- Double-click the little black box in the corner of a cell with formulas to automatically copy them down the column.
- Shift + Space highlights a row.
Shortcuts that help with multitasking
- Ctrl + Page Up/Page Down allows you to move from sheet to sheet within the Excel file.
- Alt + Tab toggles between your spreadsheets and the internet.
- Ctrl + G allows you to go to specific cells.
- F4 toggles between locked cells.
- Holding Alt and + then enter will sum the numbers in the column above.
- Alt + H brings up the ‘Home’ tab keys.
Understand the Excel accounting number format
Excel offers several preset formats to display numbers, which makes it easy to create consistency. The Accounting and Currency formats are most popular. The Accounting format comes with a currency symbol, a thousand separator and two decimal places.
The dollar signs are aligned at the left side of the cell, and there’s a little room between the number and the right edge of the cell. If the value of a cell is zero, it’s represented with a dash. If it’s a negative number, that goes in parentheses.
The raw numbers look like this:
And the Accounting format displays them this way:
You can change the number of decimal points and the currency symbol by clicking “More Number Formats” from the drop-down menu.
Under the Currency format, the numbers appear like this:
As you can see, the zero value appears as a zero with decimal points. And the dollar sign is aligned on the left side of the number, not the cell. The space on the right side of the number is also gone. The negative number appears with a minus symbol instead of parentheses, which you can change in the “More Number Formats” menu. You can also change the number of decimal points in this section too.
Free Excel accounting templates
Many businesses use Excel for financial statements or for collating data. And if you’ve ever tried to format your own spreadsheets, you know that it can be time-consuming. While we don’t recommend Excel for important tasks like lease accounting, it’s still a valuable tool for reporting and smaller tasks. Regardless of your project, there’s probably an Excel accounting spreadsheet out there to help you with it. Here are some of our favorite free templates:
Excel and accounting software
There are many types of accounting software to perform the tasks accountants often use Excel for, like QuickBooks, NetSuite, and LeaseQuery. These products weren’t just designed to make accountants’ lives easier, though. Excel is an outstanding tool for day-to-day tasks, but performing complex accounting in Excel and using these calculations for financial reporting presents serious risks. As we’ve discussed previously, accounting errors can have serious consequences, such as financial restatement. Data security is another major concern with Excel.
However, many businesses and accounting departments are under constraints, budgetary or otherwise, that prevent them from purchasing these types of systems. For companies that have fewer than 10 leases, we’ve developed LeaseGuru, a free lease accounting software that’s perfect for small businesses.
Bringing it together
Companies across industries rely on Excel to collate their data and perform basic tasks. We know that when it comes to tracking your leases, Excel may not be the most robust system to use. However, as you compare lease accounting systems, you’ll find that most allow for integration with Excel where you can export your amortization schedules, reporting, and more.
These tips should help you manage your Excel spreadsheets and keep them looking streamlined and professional. For more tips and help, get a free demo of our lease accounting software or check out more of our free tools for lease accounting and lease management.
