In my years of hiring new employees it’s very common to see people coming from college or university with accounting or finance degrees without the Microsoft Excel skills they need to do their job.
I think these 20 somethings should be coming in like Bruce Lee’s of Excel! Ninjas flying around with no mouse and blowing past everyone!! But alas, it’s not that case.
It’s a bit crazy to me that these schools still haven’t found the right way to connect the students to let them know what skills are actually needed in the workplace!
This is an area I take personally! I want to help all accounting and finance professionals get to a serviceable level of Excel (this is why we offer our 5.0 CPE credit Excel course 100% for Free) . But truthfully, I want everyone to get to Ninja status!
In this article we will go through:
-
Who is Excel training beneficial for
-
Why Excel Basics Are Important For Accounting Professionals
-
The key skills that are the true building blocks of excel
-
How to implement this training
We will also share with you our 100% free online course, Microsoft Excel Bootcamp for Accountants, in which you will also earn 5.0 CPE credits if you are a CPA!
Who is Excel Training Beneficial For?
I’ve actually been told by several companies that our free Excel boot camp is a required training for all new people who join their team!
While flattering to me, it reinforces my point that many people enter the workforce and just don’t have the basics that they need.
New employees, especially recent graduates, should all go through the Excel boot camp. even if you have some skills in Microsoft excel, then this can serve as a bit of a refresher.
Another somewhat surprising area where I have seen this basic Excel training useful is actually for non accountants! However, this still helps us within the accounting and finance functions …
I’m talking about training the outsiders! I have used this training with sales teams, procurement teams, operations teams, and more. there are many times where we get data or information from other parties and as much as we would like to keep them out of spreadsheets, sometimes it is inevitable.
Giving the basics to those OUTSIDERS can make our lives easier and it also will improve your company overall.
Why Excel Basics Are Important
The list of reasons why Microsoft Excel training is important can get pretty long. I’m going to focus on four key reasons.
Building Blocks
I think of the basic Excel functionality as a building block on the path to becoming in Excel Ninja and maybe even moving into more advanced Analytics and other tools/programming/languages (SQL, Macros, Alteryx, Power BI, etc).
Time Saver
Simply put, the better you are as an accountant at Microsoft Excel, the more productive you will be at your job.
There are studies that show Advanced Microsoft Excel users that don’t have to use their Mouse can save as much as 20 minutes a day versus someone who is a slower user. if you do the math and you work 5 days a week 50 weeks a year… It comes Out to 80 hours a year of time saved! That is 2 weeks of work versus a less skilled user!
All that time could go into learning more advanced skills, self-development, or just better work life balance!
Fewer Mistakes
When you know the correct way to structure a file, you avoid hard coding formulas within cells, you know how to double check your work with edit checks that are calculated… you will have cleaner files and fewer mistakes.
When you are more accurate that builds Trust with your boss and your peers and the people you support. Ultimately, this will help you advance your career and be more successful!
Better Presentation
All of these basic Excel skills include knowing how to present data, knowing how to format it, and knowing how to save and send it so that it can be easily digested. This helps everyone in the organization.
For the next part of this, we are going to dive into some basic excel skills for accountants…
Basic Excel Formatting for Accounting and Finance Professionals
Font Coloring
We use different font coloring in our spreadsheet to guide users and tell them how to interact with our spreadsheet. In general, we use the following 3 basic font coloring:
-
BLUE: Indicates that this cell is an input/assumption cell and that the value can be changed.
-
BLACK: Indicates that a cell that should not be modified or overwritten with a hard-coded value either because it contains a formula or it is a value that will not change (for example, the historical sales figures).
-
RED (optional): Some people like to color cells red that link to other workbooks. However, this is optional, whereas the blue and black coloring are not.
Number Formatting
Make sure you format all of your numbers properly! When you leave a number unformatted, it makes it very hard to read which can lead to errors. Additionally, only add decimal places when it is absolutely necessary.
A Public Service Announcement on Hard Coding
Don’t ever, ever, ever, ever hard-code in a value into a formula. For instance, this formula is a big no-no:
=A1*(B1+C1) + 100
Notice that the formula initially references other cells which is perfect. But then, at the end of the formula, it adds in an arbitrary 100. What does that mean? Why did the person add it there?
When we hard-code in values in a formula, it can cause 3 major problems:
-
It makes the spreadsheet very undynamic. Our entire spreadsheet should be driven by a few numbers and assumptions. When we hard-code in a number, we then have to manually go through ever cell that contains that value and change it.
-
It can cause major errors. Many people forget they hard-code in values. This can lead to major errors down the road as people copy and paste formulas or carrying them into the future. They are hidden bombs in your spreadsheet
-
It makes it very hard to understand your assumptions. When you hard-code in a value, you hide from other people your assumptions. This makes it hard to review and audit your spreadsheet.
Keyboard Shortcuts
Keyboard shortcuts can save you immense amounts of time. You may not realize it, but every time you take your hand off the keyboard to use your mouse, you are wasting precious seconds. And over the course of a day or week, those seconds turn to minutes which turn to hours.
Because of that, you NEED to learn keyboard shortcuts. It is more than just a “good to know.” If you want to master Excel, you need to know keyboard shortcuts.
We have 2 cautions when learning shortcuts:
-
When you start learning keyboard shortcuts, it WILL be slower than using your mouse. That’s right. But, you must not quit! Keep at it for a couple weeks and soon the shortcuts will be muscle memory and automatic. Resist the temptation to go back to your mouse.
-
If you try to learn every keyboard shortcut, you’ll go nuts! You should learn the shortcuts to the actions you perform most frequently.
The keyboard shortcuts everyone needs to learn are:
• Copy = Ctrl + C
• Paste = Ctrl + V
• Cut = Ctrl + X
• Save = Ctrl + S
• Undo = Ctrl + Z
• Redo = Ctrl + Y
Now let’s get into some basic functions and excel formulas everyone should know….
Absolute Cell References
There may be times when you do not want a cell reference to change when copying cells down a column or across a row. By default, all cells are Relative References. This means that as you copy a formula, the cell references will change based on where you paste the formula. Absolute References on the other hand, do not change when copied. Therefore, you use an absolute reference to keep a cell, range, or row/column constant.
When a cell includes an absolute reference, there are $ to the left of the column or row referenced. For instance, B5 is a normal cell reference whereas $B$5 is an absolute cell reference.
To get an absolute cell reference, hit the F4 key on your keyboard. If you keep hitting F4, it will cycle through the different variations of locking the formula.
$A$2 = The column and the row do not change when copied, the entire range is fixed.
A$2 = The row does not change when copied.
$A2 = The column does not change when copied.
Conditional and Lookup Formulas
SUMIFS
The SUMIFS function adds all numbers in a range of cells that meet one or more criteria. The syntax for the SUMIFS function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])
The sum_range argument is required and is the range of cells you want to sum.
The criteria_range1 argument is required and is the range of cells that you want to apply criteria1 against.
The criteria1 argument is required and is the criteria used to determine which cells to add. You can add additional criteria and criteria ranges in the subsequent arguments.
In the example below, you can add the total number of cars (criteria 1) that Adam (criteria 2) sold using the formula:
=SUMIFS(A2:A6, B2:B6, “Car”, C2:C6, “Adam”) which returns the value 6
COUNTIFS
The COUNTIFS function counts the number of cells in a range that meets one or more criteria. The syntax for the COUNTIFS function is as follows:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2], …)
The criteria_range1 argument is required and is the range of cells that you want to test criteria1 against.
The criteria1 argument is the criteria used to determine which cells to count. You can add additional criteria and criteria ranges in the subsequent arguments.
In the example below, you can count the total number of times Adam (criteria 1) received a passing grade (criteria 2) using the formula:
=COUNTIFS(A2:A6, “Adam”, C2:C6, “Y”) which returns the value 2
VLOOKUP Formula
The VLOOKUP function searches vertically (top to bottom) the leftmost column of a lookup table until it locates a value that matches or exceeds the one you are looking up. Think of the VLOOKUP as a phone book. You want to look up a person’s phone number so you open the phone book, look up their last name, and then go over and find their number. The VLOOKUP does the exact same thing.
The VLOOKUP function uses the following syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The lookup_value argument is the value that you want to look up in the first column of the lookup table (for example you want to lookup “Gimli”).
The table_array is the lookup table’s cell range. This is the entire table (i.e. phone book).
In the example below, cell B7 has the lookup_value (Gimli) and cells A2:C5 is the table_array (the phone book where we want to look up Gimli).
The col_index_num argument is the column number in the table from which the matching value must be returned. The first column is 1. In the above example, Name is column 1, Age is column 2, and Gender is column 3.
The not-so-optional range_lookup argument is either TRUE or FALSE. This tells Excel whether you want Excel to find an exact (FALSE) or approximate (TRUE) match. When you specify TRUE or omit the range_lookup argument, Excel finds an approximate match. When you specify FALSE as the range_lookup argument, Excel finds only exact matches. Unless it is a special situation, you should put FALSE 99% of the time.
The reason is, if your lookup values (Name in our example), is not in alphabetical or numerical order and you do not choose the FALSE option, the VLOOKUP will not return accurate results.
Putting this together, the formula =VLOOKUP(A1,C2:E5, 2, FALSE) will return Gimli’s age which is 140.
Our Excel Bootcamp gets into more detail and will go through If statements, working with dates, removing leadings spaces, customizing your ribbon, using text-to-columns, conditional formatting, data validation, Pivot Tables, and more!
Get the FREE 5.0 CPE Credit course HERE!
How to Implement Excel Training
This might be the easiest part. Make it a part of your on-boarding / new-hire training for everyone that starts!
Beyond that, you can regularly assess if there are others in the organization that could benefit from it and just send them the sign-up link!
Once you have mastered the basics, head here to continue on your path to full blown Accounting Excel Ninja where we learn more complex functions, data validation tools, complex calculations, and gain a deeper understanding of what it takes to differentiate yourself as an accounting professional!
