9 Best Excel Skills for Accountants in 2024

The resources that are available to accountants continue to expand, but Microsoft Excel remains one of the profession’s most widely used tools. As Microsoft 365 (formerly known as Office 365) accelerates its expansion in the cloud, Excel will continue to serve as an industry and professional standard that accountants will need to respect.

The Robert Half report Benchmarking the Accounting and Finance Function found that:

63% of American companies consider Excel a vital accounting tool.

When looking at smaller accounting companies, this percentage goes even higher. The seminal spreadsheet tool gained even more traction when the American Institute of CPAs (AICPA) replaced its proprietary spreadsheet with Excel for its 2018 and subsequent CPA exams.

---

Want to level up your Excel skills? Try GoSkills' Excel for Accounting course today.

Level up your Excel skills

Start learning Excel for Accounting today!

Start free trial

---

Learning Excel has never been more important, and the platform continues to add to its list of features. The following is a list of the most important Excel skills for accountants, but it is by no means a full set—professionals are encouraged to consistently update their knowledge of the spreadsheet program, especially as the product updates itself from time to time.

1. Filling and formatting

No less than Warren Buffett has explained the importance of business efficiency in every profession. Whether you subscribe to the 80/20 rule of productivity or Buffett’s 5/25 of goal setting, shortening the time you spend in rote data entry and formatting helps to increase your efficacy as an accountant.

Excel has the ability to auto-fill cells and add special formatting to cell batches more easily than ever. Learn the shortcut keys and pay attention to Excel’s new automation features to accelerate your work rate in the platform immensely. For instance, holding Ctrl + Shift with the numerals below allows you to quickly apply common formats to large amounts of data:

• + 1 - Format numbers with two decimal places
• + 2 - Format cells as time
• + 3 - Format cells as date
• + 4 - Format cells as currency
• + 5 - Format cells as a percentage
• Ctrl + Shift + 6 - Format cells in exponential or scientific form

2. Sparkline charts

A chart line is a feature that allows you to embed charts inside cells, greatly improving your ability to consolidate large amounts of information into a single spreadsheet. The chart can be in a number of formats, including win/loss, bar or line format. Creating these charts is a relatively simple procedure:

1. Select your range of numbers
2. Click on the Insert menu
3. Choose one of the built-in chart options
4. Select a location range that is in the same worksheet as your data range

3. Making charts visually appealing

Excel Charts has many features for not only creating reports but also making them look attractive to readers. Here are a few tricks that you can use to quickly increase the visual appeal of your charts.

• Time series charts – Bar and line charts are usually the best option for plotting data over a specific period of time. You can use the following shortcut keys to create a line chart (Alt+N+L+N) or a bar chart (Alt+N+C).

• Breaking down data into components – If you are trying to show data with multiple components, the pie chart is usually the best option. You can enter information as percentages or absolute values. The shortcut key for this is Alt+N+Q.
• Scatter plotting – This is a great tool for exploring the trends in data points. The shortcut for plotting points this way is Alt+N+D.
• Trend line– Your line and bar charts have the ability to add trendlines and scatterplots to more easily interpret the data and understand the relationships there.

Check out this tutorial on how to create a bar chart in Excel:

---

---

The art vs. the science of Excel

Accounting certifications are a great way to quantify Excel skills for accountants, but they are by no means foolproof. Even the switch of the AICPA to Excel as its testing standard can only help to determine if an accountant knows the basics, not whether they can get creative with Excel’s vast array of options.

One of the core concepts of developing a business regardless of industry is the ability to move beyond the stated function of your tools. The modern accountant is expected to provide a unique value to their client base – after all, everyone has access to Excel in the cloud.

The skills mentioned below are more artistic in nature, and there are very few limits to the problems that a motivated accountant can’t solve after mastering these techniques. It can be difficult to determine how to prioritize these job skills for a resume, but these are likely the skills that you will need as your baseline to serve an accounting function in the modern business industry.

4. Using pivot tables

Newer versions of Excel have sophisticated pivot tables that allow a user to simplify and analyze complex data manipulations. Pivot tables are interactive, giving users the ability to detect trends and make data comparisons. The PivotTable selection under the Data menu takes you straight into a Wizard that lets you choose the data for a PivotChart and easily format that data however you need.

Insert a pivot table with the Alt+N+V shortcut. Add fields in your Column, Rows, Filter and Values boxes and select the way to tally the data – averaging, counting or adding. You can then use Excel’s built-in functions or create your own calculated items to further shape your data. Your calculated fields are defined by the formulas you set, making it easy to draw derivative findings from a set of data.

Check out this tutorial on how to create pivot tables in Excel:

---

---

5. Auditing formulas

You no longer have to be a calculus whiz to get the most out of Excel formulas. The Formula Auditing toolbar gives you quick and dirty access to an error checking function that works especially well on large and complicated worksheets. Even if you are working with a smaller worksheet, you can still make the process of creating and auditing your formulas speed along much more quickly.

The Formula Tab is where you will find the Formula Auditing toolbar. From here, you can choose from a number of options, including formula execution, tracing formula precedents, hunting down errors and locating dependent cells.

6. Data validation

This function is a huge shortcut for worksheets that have many different forms of data. Using Data Validation ensures that a user immediately knows the data type that a cell holds. The user will also know if the cell can hold anything at all. If the data is not properly validated, an error message pops up that will certainly shorten the time that the user needs to go back over long stretches of data after formatting.

Depending on the option that you use, you can enter formulas, times and dates, floats or whole numbers. Users also have the ability to select from a wide set of options, including the option to enter only integer numbers. The custom formula feature lets the user set their own conditions using the built-in formulas from the program.

7. What If analysis

Excel also has the ability to analyze hypothetical scenarios with three main options – Goal Seek, the Data Tables, and the Scenario Manager. The Scenario Manager is perhaps the most robust of these three options, giving the user the ability to input variables of different values in different situations. Users have the option of numbering the scenarios and comparing them against each other, along with the ability to refer to the cells that are changing between scenarios.

The option for the Goal Seek iterates a situation until a preset requirement is met. This is a great way to shorten up the search for a particular cell value that you need to move forward with a set of data. The Data Table lets the user display more than one scenario at a time, which helps to increase productivity if many situations need to be compared against each other. Expanding the scope of this feature beyond bookkeeping, Excel serves as an excellent tool for the financial analytics of business development, especially for analysts and solopreneurs who prefer independent contracts with clients.

8. Using templates

You don’t have to reinvent the wheel every time you need to use Excel. Familiarizing yourself with the ever-expanding library of templates that are available for any Excel version can exponentially shorten your time between problem and solution. You can gain access to templates through the Insert command that is under the Spreadsheet Solutions tab. Here you will find many different kinds of templates including amortization schedules. Even if you do not necessarily want to go through the library of available templates, you can create your own as you problem-solve by simply saving a worksheet as a template.

9. From Data to Table

To finish up these vital skills, we will reel in the discussion with a shortcut that every accountant should know that turns data cells into a table. Click within the parameters of your data set and press Ctrl-T, and voilà – instant table.

Regardless of how long you have been using Excel, is always a good idea to reboot your skills every so often. You can create accounting magic with Excel that is instantly translatable across business disciplines since everyone knows Excel as the industry standard. Make sure that you keep up with new versions of Excel and take note of the new features that are consistently being added to this landmark program.

Learn Excel for Accounting

Want to level up your Excel skills? Try GoSkills Excel for Accounting course today.

In this beginner-friendly course, you will learn how to leverage Microsoft Excel to boost your performance in your day-to-day accounting, bookkeeping, and financial analysis tasks.

By the end of the course, you will be able to:

• Import, clean, and validate data
• Master specific Excel functions for accounting and finance
• Easily look up data using VLOOKUP, HLOOKUP, and XLOOKUP
• Analyze and present your data efficiently with PivotTables and charts
• Create your own tax and debt calculators, and so much more!

The course also includes downloadable Excel templates and exercises to help you get up and running quickly. Start learning for free today with a 7-day free trial.

Level up your Excel skills

Start learning Excel for Accounting today!

Start free trial