As someone who has cut his or her teeth in the world of accounting, you probably see yourself as something of an Excel master. Many accountants grew up using Excel, and continue to use it every day for calculations, graphing, pivots and more. But even experienced Excel users can discover new tips and tricks to save hours each day.
New Guide from FloQast Looks to Help Accountants Do More with Excel
In a recent whitepaper from Controllers Council sponsor FloQast titled An Accountant’s Guide to Excel – Tips and Tricks to Help Finance & Accounting Professionals Save Time and Become More Effective, this leading provider of financial close software explored some advanced tips to help you get even more out of your spreadsheets.
From using the text functions without editing to cleaning up your spreadsheets to becoming a VLOOKUP expert, this guide is written to help controllers to cut minutes or hours off their day. Available for download here, the Accountant’s Guide to Excel provides an in-depth look at how you can use Excel to accomplish the following:
Part 1: Using the Text Functions without Editing
Say you have a spreadsheet with a bunch of names. Maybe it’s a list of vendors, clients, or employees written in “last name, first name” format. What if you needed to break those up or display them in “first name, last name” basis? What was your first thought?
Text to Columns? Sure. But what if that’s linked to another spreadsheet? Probably won’t work.
If you’re looking to view this the way you want—without potentially breaking any links to other spreadsheets—there’s an easier way. By using the Left Function, the Find Function, and Data Fill, you can reduce workload and do so without breaking any connection.
Part 2: Cleaning up Your Spreadsheets: Combining and Trimming
Spreadsheets have a habit of getting messy. Data gets moved, added, and modified, leaving everything looking ugly or unsearchable. Part 2 of the FloQast guide was written to help you combine and trim all the new columns created in part 1.
In this step, FloQast shows you how to combine the two new columns into “First Name Last Name” and then trim out the spaces for a cleaner spreadsheet.
Part 3: Putting Data to Good Use with VLOOKUP
After making the changes to employees in parts one and 2, part 3 is written to put this to good use for something everyone loves—calculating bonuses.
One of the most highly anticipated tasks for many accounting teams is calculating bonuses. While there are many ways to do that, one popular method is to base them on the company’s sales goals. So let’s say your company’s bonus plan says:
- If employees attain from 0% to 100% percent of their budgeted sales, they get no bonus.
- If employees get between 100% to 115% of the budgeted sales, they get a bonus of one percent of revenue.
- If employees attain 115% to 120%, they get 1.10%.
- If employees get 120% or more of the budgeted sales, they get 1.25% going all the way back to dollar one.
Of course, you could go over to the budget tab, find Store No. 1001, copy the budget amount of $125,000 from cell B2, return to this spreadsheet, and paste the result in a new budget column. But doing that for every month and for all three stores would take a lot of time. There’s a better way.
Part 3 of the Accountant’s Guide to Excel shares with readers the necessary components of using VLOOKUP to combine and calculate bonuses without running into hassles or illegitimate data.
Learn and Discuss: Join the Controllers Council
There’s always something new to learn. If you’re looking to step up your Excel game or discuss things more pressing and challenging, why not join the fastest growing community of controllers? The Controllers Council features networking, discussion forums, and a variety of learning opportunities for controllers new and old. Learn more about the benefits of membership here.
Additional Tips for Controllers
Tips for Controllers Looking to Build an Effective Relationship with the Audit Committee
