Ask any accountant in any field which software they use on a daily basis, and the most common answer will be Microsoft Excel. The spreadsheet tool is perfect for anyone working in the finance and accounting fields due to its versatility and specific financial applications.
At this point, it’s expected that you be proficient in Microsoft Excel even in an entry-level position, which was part of the AICPA’s decision to include Excel as the spreadsheet tool in the CPA Exam. Whether you’re just starting out or have been in the field for some time, being able to efficiently navigate through a data set in an Excel file and use formulas to make sense of the data should be one of your top priorities. To help you become an Excel user, we’ve put together a listing of the top 10 Excel skills you need to know if you’re going to excel in your accounting career.
10 essential Excel skills to help you stand out
1. Copy and paste
This may seem like a rudimentary skill, but knowing how to copy and paste correctly can actually save you a fair amount of time. Excel has a variety of paste special options; you can paste formatting, paste the formula, paste the values, paste the validation, transpose, and various other options. If you don’t know how to use these correctly, you may spend time typing out individual formulas and values, or going through and changing the formatting of individual cells when you can simply utilize paste special.
2. Sort and filter
Knowing how to accurately sort and filter is the gateway into data analytics. The sort feature allows you to sort a specific set of data by a specific column. Filter allows you to only see data lines that adhere to your filter or filters. This is perfect for accountants who work with large data sets and need to narrow them down to extract the correct information.
3. Using the “Formulas” tab
When you first start using Excel, the number of formula options you available for data analysis can be overwhelming. Excel helps you to figure out what you need to use via the Formulas tab on the toolbar. This tab lets you search through every Excel function, as well as show you the most used formulas. It explains what a specific formula is used for and even walks you through how to set the formula up. It’s a great resource when you’re first learning the ins and outs of Excel formulas, and is even useful once you jump into advanced formulas.
4. Cell reference formatting
Cell referencing is referring to a specific cell in another cell or formula. Knowing how to reference cells correctly ensure your formulas and derived numbers make sense on your Excel spreadsheet. There are four types of cell referencing:
- A1 – Keep both row and column moveable
- A$1 – Keep row constant and column changeable. This helps when you want to drag a formula down a column.
- $A1 – Keep column constant and row changeable. This helps when you want to drag a formula across a row
- $A$1 – Keep both row and column constant. This helps when you want a formula to always reference a certain cell.
5. Using F4 to repeat last action
This is a nice timesaver that allows you to repeat the last action you took in new cell or group of cells. For example, if you highlighted a cell yellow, you can select another cell and select F4 to highlight that cell yellow. As an accountant, you’ll constantly be updating the format of cells, adding formulas, and generally working toward the most readable and understandable format within your Excel sheet. This shortcut will help you quickly change things in your sheet without having to use your mouse or toolbar.
6. Conditional formatting
This allows you to set specific formats for the value within a cell. For example, if you’re trying to see if you have any blank numbers in a sales tax column, you can add conditional formatting to the column to say “if cell is empty, make cell color red.” Anomalies will easily stand out and you’ll quickly identify if something is wrong in your sales tax column. Conditional formatting can be used for a variety of purposes and knowing how to use it will help you quickly identify specific values in a data set.
7. Graphs and charts
Visually analyzing data is very helpful for accountants, especially if they’re presented with large data sets. While a table can show a vast array of data, a graph and chart can show what the data actually means; trends may surface that weren’t easily identifiable in a table format. Accountants who are proficient in using graphs and charts in Excel can visually communicate data both internally within the organization and to clients in a more effective way.
8. VLOOKUP
When I started my first job in public accounting, it became increasingly clear that I needed to learn how to use a VLOOKUP. This formula helps you find data in a range of cells by row. For example, say you have a large data sheet with names and CPA Exam scores on it. You can use a VLOOKUP to find a specific score for a specific candidate on a specific exam. The basic formula looks at one field, but you can nest VLOOKUPs into SUMIF formulas or add field criteria to make them even more useful. This formula is especially helpful for analyzing large data sets, which is done in a variety of accounting positions, from data analysts to auditors.
9. Pivot tables
Often considered an advanced Excel skill (but used often by accountants), pivot tables take your data and organize it for you so you can organize it in a variety of data tables. You can sum specific data, put specific line items in rows or columns, and only include the data you need. Pivot tables allow you to take a huge sheet of data and turn it into a readable format that allows you to draw conclusions from the data.
10. Keyboard shortcuts
I was always jealous of colleagues who could use keyboard shortcuts; they could navigate between sheets, re-format cells, and add formulas without lifting a finger to put on the mouse. The best part about keyboard shortcuts is they increase your efficiency and reduce the time it takes you to do a task in Excel. As accountants, we work in Excel pretty consistently every day. Using shortcuts will help you cut down on the time it takes to complete tasks while also making you the envy of all of your friends.
If you haven’t jumped into mastering MS Excel skills, now is the perfect time to start. Surgent offers an Excel Certificate Course, which can be purchased as a standalone course if you just want to learn about Excel, or as part of Surgent CPA Exam Review’s Ultimate Pass if you’re studying for the CPA Exam. Either way, Excel is a powerful tool, and learning it will be invaluable to your career. Taking a course now will help you become an asset in any accounting profession as you continue or start your accounting career.