You may have seen the meme. It goes something like “They always talk about how bad social media is for mental health, but what about Excel spreadsheets?” However, Excel for accounting professionals is as necessary as a stethoscope is for medical professionals.
Few tools are as simple, powerful, and maddening all at the same time, and yet for accounting and financial professionals, Excel is both an unavoidable beast and your potential best friend. (Our CRO, Justin, has been out of his Big Four accounting days since 2018, and still has dreams where he is buried in an Excel spreadsheet 😀).
At Strongbox, we know a thing or two about spreadsheets. Our software produces a ready-to-use workbook using financial data right from a business’s accounting system. We’ve spent years perfecting the art of Excel via automation. And now, we’re passing on some advanced Excel tips to you.
Working with the Data: Excel for Accounting
The reason Excel is so crucial is that it can do so many things with raw data. What took days to do on paper can now be done in a couple lines of Excel formulas in seconds. When dealing with financial data analysis, here are the must-know Excel formulas for accounting:
Pivot Tables
VLOOKUP
If formulas
LAMBDA
Pivot Tables
Pivot tables are one of the most important features for analyzing data in Excel. Pivot tables allow you to create instant summaries of a large set of data and can be helpful for creating financial reports or account summaries.
The trickiest part when creating a pivot table is making sure that you have no blanks in your data set. All columns and rows need to be filled in. (Helpful Hint: the Strongbox output creates a clean, flat data source with all blanks already removed for you.)
Once your data is clean, it’s just a few clicks to have a drag-and-drop table editor that you can use to create custom views, summaries, and reports. This can be especially helpful for uncovering anomalies and sharing information with clients in a consumable way.
Tip: Don’t forget about layout options! The default layout is in Compact Form, but you can use a Tabular form to create a table that looks like your original dataset, just summarized.
VLOOKUP
No conversation about Excel for accounting would be complete with VLOOKUP. When managing different data sets, VLOOKUP is a necessity. VLOOKUP starts with one piece of data that you know and then uses that to find additional information about that piece of data.
For example, you may know the product number from your inventory sheet, but that sheet doesn’t include the product price that you need for a calculation. Using the VLOOKUP function, you can search the sheet that has the prices and the product number to pull all the pricing information quickly and accurately into your inventory sheet.
VLOOKUP gets even more powerful when combined with other Excel functions. You can use VLOOKUP with Match, Wildcards, Power Query, and more. VLOOKUP makes cleaning and managing your client’s data much simpler and reduces the amount of human error in manually hunting and entering information.
Tip: if your VLOOKUP returns errors when you know the lookup value is in the dataset, try using the TRIM function as part of the lookup value. The TRIM function removes any unnecessary spaces from the lookup value that might be tripping up the formula.
If Functions
Excel’s IF functions allow you to ask a question in the data and receive a true/false response based on the results. These functions make it very easy to calculate only the data that meets the conditions you need to be included without having to manually cull out that information for standard formulas. There are multiple types of IF formulas available, including SUMIFS, AVERAGEIFS, DATEDIF, and COUNTIFS.
If you have conditions you need data to meet in order to be included in a calculation, then IF formulas will be your best friend. For example, if you need to find out how much your client spent with a particular vendor, you can use the SUMIFS formula on a general ledger, only adding the sums that meet the criteria you set (e.g., the name or vendor number for that vendor).
Tip: If your IF function doesn’t return what you’d expect, trying adding the quotes “” around the logical test value as this helps Excel more accurately identify the criteria.
LAMBDA
A relatively new feature for Excel, LAMBDA gives you the ability to create and reuse formulas that you can label with custom names for future use in that same workbook. It’s a powerful feature that lets you set parameters (even arrays), define your calculation, and then use that formula as if it were any other Excel function.
For accounting professionals, this means you can create and name formulas for things like net change, cost per item, and more.
Unfortunately, you can’t reuse your saved LAMBDA formulas in other workbooks, but creating one only takes a few seconds. When starting a new project, create all the LAMBDA formulas at the beginning, and then you’re set.
How to Brand an Excel Sheet
When dealing with data that will be presented to a client, the analysis is only one piece of the puzzle. The presentation of that data also matters. To be effective, the data needs to be easy to read and pleasing to look at. You may also have requirements from your firm to match colors, fonts, or logos.
If you’re using Strongbox, we can apply custom branding to your workbooks so that your output is client-ready from the start.
However, there are also functions within Excel that can help produce a visually appealing, custom presentation of the data. Functions that help financial data look customer-ready in Excel include:
Conditional formatting
Paste special
Color customization
Conditional Formatting
Like IF functions does to data analysis, conditional formatting does to the visualization of your data. Conditional formatting adjusts the formatting of cells or rows or columns based on certain criteria being met.
A common example of conditional formatting is having negative numbers turn red and positive numbers turn green, but conditional formatting can be much more involved than that if you’re looking for a custom experience. Excel comes in with some built-in conditional formatting options, but using your IF functions, you can create your own conditional formatting rules in the Rules Manager.
Paste Special
Copying and pasting data is a routine part of using Excel in accounting. However, all too often, you’ll copy your beautiful data from one sheet only to find it destroyed once you paste it into a new workbook.
Enter Paste Special.
Paste Special gives you the power to do a lot more than a standard paste. Paste Special will give you the option to copy formulas, number formats, existing themes, and more.
Color Customization
Color is a great way to make data more digestible. Color can give the eye a shortcut for determining what data means and also create brand affinity for your firm.
The easiest way to add color to a spreadsheet is with the font color and/or the cell fill color. However, you can also customize the colors of your tabs or even the background for your sheets. Your firm’s marketing team will be able to provide you with the HEX or RGB color codes for your firm so you can ensure that your “blue” is the right “blue.”
Additionally, Excel comes with preset color scales to help make sure the colors you are using visually work together. When combined with conditional formatting, your colors can be a powerful storyteller for your analysis.
Try It For Yourself
Mastering Excel is necessary for anyone in the accounting profession. We hope these advanced Excel tips help in that process. Fortunately, many of the elements we included here are applied automatically when using Strongbox. Contact us today to see the Strongbox output for yourself and to sign up for a risk-free pilot program to try for yourself