Excel for Accountants (20 Skills to Master in 2020)

If you are an accountant, one of the most important skills for you to master is Microsoft Excel, period.

Why I’m saying this?

Well, financial data is what you deal with as an accountant, and you need to be good at managing and analyzing data, right?

And Microsoft Excel is the tool you need.

To get started with it, you can learn:

BUT.

You are an accounting professional, so you also need to learn all those specific things which can help you to thrive in your work.

Let me tell you that this is the most comprehensive guide which you can find on the internet to help you to be a better accountant.

So let’s get started.

20 Excel Tips for Accountants and Finance Professionals

Below is the list of the 20 Excel Skills which every accountant needs to master this year.

1. Keyboard Shortcuts for Accountants

No matter if you are an accountant, finance professional, or in some other profession, using keyboard shortcuts can help you to save a lot of time.

Here’s the Excel Keyboard Shortcuts Cheat Sheet with 82 shortcuts that you can use in your daily work and below are the top 10 out of them:

  • Ctrl + Shift + $ (Applies the currency format)
  • Ctrl + ! (Open the format option dialog box)
  • Control + T (Applies the Excel table to the selected data.)
  • Alt +(Sums the values from the upper or left cells)
  • Alt H W (Applies the wrap text to the selected cell)
  • Alt W F F (Applies the freeze pane from the selected cells)
  • Alt N V (Insert a pivot table with the selected data)
  • Alt + F11 (Inserts a chart from the selected data)
  • Control + Shift + D (Apply filters to the data)
  • ALT A M (Remove duplicates)

Here are my few tips for every accountant about keyboard shortcuts:

  • Replace your 10 most used options with keyboard shortcuts.
  • Try to locate shortcut keys by pressing the ALT key.

2. Power of Paste Special

One of the options which you need to learn is PASTE‌ SPECIAL. With it, you can do a lot of things other than a normal paste.

To open the paste special option, you need to go to the Home Tab ➜ Clipboard ➜ Paste Special, or you can also use the shortcut key Control + Alt + V.

Once you open it you can see there are more than 16 options to use, but let me share the most useful options which you need to learn:

  • Values: It only pastes values, ignoring formulas and formatting from the source cell and best to use if you want to convert formulas into values.
  • Formulas: It only pastes formulas, instead of values and best to use if you want to use apply formulas.
  • Formats: It copies and pastes the formatting, ignoring values and formulas from the source cell (Quick Tip: Format Painter).
  • Column Width: It only applies the column width to the destination column, ignoring the rest.
  • Operations: With this option, you can perform simple (multiply, divide, subtract, and addition) calculations (check out this trick).

3. Sort Data Like a PRO

In Excel, there are multiple ways to sort data. When you open the sort dialog box (Data Tab ➜ Sort & Filter ➜ Sort), you can add a level to sort.

Imagine if you want to sort the below data using the first_name column, you need to add a sorting level for this:

  1. First of all, select the column from the “Sort By”.
  2. After that, in “Sort On”, select “Cell Values”.
  3. In the end, in “Order”, A to Z.

Once you click OK, you’ll get the data sorted like below:

Well, this was the basic way to sort data which is mostly used, but apart from that, there are some advanced sorting options that you can use.

1. Sort On

From the “Sort On” drop-down, you can select the option to sort with font color, cell color, or conditional formatting.

So if you select the cell color, it gives you the further option to show it on the top or at the bottom after sorting.

2. Custom List

You can also create a custom list of sorting values. Imagine you have a list of names and you want all the names in a particular order, you can create a custom list for that.

3. Sorting Column

By default, when you use sort option it sort by rows but there’s an option which helps you to sort data by column. Open the “Options” and tick mark the “Sort Left to Right”.

4. Advanced Options to Filter Data

Excel filter is fast and powerful. It gives different ways to sort data from a column. When you open a filter you can see there are a lot of options that you can use.

Below I have listed the most useful options which you can use:

1. Filter by Color

So if you have cell color, font color, or even applied conditional formatting, you can filter all those cells as well.

2. Custom Filter

With custom filter, you can filter using conditions, partial match, wildcard characters, much more.

3. Date Filters

If you have dates in the column, you can use date filters to filter them in different ways, like weeks, months, and years.

4. Search Box

With the search box, you can filter values in a flash. You just need to type the value and hit enter.

Quick Tip: You can also filter values by using options from the right-click menu and the best thing that you can use is “RE-APPLY”, it’s like refreshing the filter you have already applied.

5. Apply Excel Table to the Data Every Time

If I have to give one tip Excel, I’d like to say “Use Excel tables Every Time”. Why I’m saying this? Well, there’s a huge benefit to using Excel tables.

To apply Excel table to data you can go to Insert Excel Table or you can also use the shortcut key Control + T.

When you refer to this data in a table, every time you update this data you need to change the reference.

Why? Because the range address of the data changes every time you update it.

The best example I can tell you is about using a table while creating a pivot table, you can use a table to update source range automatically for a pivot table.

6. Conditional Formatting for Better Presentation

Conditional formatting is smart formatting. It helps you to format data based on a condition or a logic and it helps you to present your data in a better way and also gives you quick insights.

To access CF, you need to go to the Home Tab ➜ Style ➜ Conditional Formatting.

Let’s say you want to highlight duplicate values, with the conditional formatting you can do this with a single click. Highlight Cell Rules ➜ Duplicate Values.

Or if you want to highlight the top 10 values, there an option in conditional formatting called “Top Bottom Rules” which you can use.

In the same way, you can also apply data bars, color scales, or icons sets on your data.

And if you want to create a custom rule to apply CF, click on the “New Rule” and you’ll get a dialog box to create a new rule to apply conditional formatting.

Related

7. Advanced Find and Replace for Smart Users

Apart from normal find and replace there are a few advanced options in Excel to use find and replace.

For this, you need to click on the “Options” button and you’ll get a bunch of options down the line. Below I have described them:

  1. Within: You can select the search area for the value. You can select between the active worksheet and the whole workbook.
  2. Search: Search through rows or columns.
  3. Look in: Look in formulas, values, comments, and notes (This option only works find not with find and replace).
  4. Match Case: Find and replace a value with a case sensitive search.
  5. Match Entire Content:‌ Match values from the entire value of a cell with the searched value.
  6. Format: With this option, you can search a cell based on its formatting. You can specify the formatting or you can use a selection tool to select it from a cell.

Related

8. GOTO Special for Fast Data Selection

With GOTO special, you can select specific cells just with a single click.

Once you open this, you can see the list of types of cells and objects.

Imagine if you want to select all the cells where you have formulas and those formulas show an error.

You just need to select the formula and tick mark only errors and click OK and all the cells with formulas with errors will get selected.

9. Use Sparklines for Tiny Charts

As an accountant, you need to deal with a lot of financial data in tabular form and sometimes for the end-user, these kinds of data take longer to understand.

But with sparklines, you can make it easily digestible by creating tiny charts. In the below example, I have product-wise and year wise data (DOWNLOAD FROM‌ HERE) and at the end of the rows, I have small charts which I have added by using sparklines.

Basically, there are three different types of sparklines which you can use:

  • Line
  • Column
  • Win/Loss

To add a sparkline you simply need (Insert Tab ➜ Sparklines) and select the type which you want to insert.

Once you click on the type, you’ll get a dialog box where you need to select the data range and then you need to specify destination the cell for the sparkline.

Once you insert it, there are multiple ways to customize it. Simply click on the cell and go to the Sparkline.

  • You can add and remove markers add high-low points.
  • You can change the color of the marker and the line.
  • You can also change the type of sparkline in your work.

10 Data Analysis with Pivot Table

A pivot table is the most important tool when it comes to data analysis in Excel. You can create a pivot table to create instant financial reports and account summaries of a large set of data.

Well, creating a pivot table is easy.

You need to have source data just like I have in the below example, but make sure there shouldn’t be any blank row or column.

  • Now, go to the Insert Tab and click on the insert pivot table.
    click-pivot-table
  • It will show you a dialog box to define the source data range but as you have already selected the data it takes it automatically.
    insert-pivot-table-dialog-box
  • Once you click OK, you will have sidebar just like below where you can define the rows, columns, and values for the pivot table. You can simply drag and drop.
  • So now, add “Age” to the rows, “Education” to the column, and “First Name” to the values.
    pivot-table-field-list
  • In the end, once you define all, you’ll have a pivot chart like below.
    new-pivot-table

11. Take the Help of Idea Button

The idea button is a new feature introduced by Microsoft in Excel.

The idea behind this button: It can analyze the data with a single click and recommends all the possibilities.

  • Category Pivot Table and Charts
  • Trendline Charts
  • Frequency Distribution Charts

Here’s how to use it:

  • Once you are ready with your data click on the IDEA button (Home Tab ➜ Ideas ➜ Ideas).
    idea-button-1
  • It will instantly show you the side pane and with all the recommendations.
    report-suggestion-by-idea-button-1
  • You can simply click on “Insert” to insert your preferable chart or pivot.
    insert-chart-from-idea-button

There’s also an option to specify the fields which are important and you want IDEA button to make a recommendation based on those.

12. Use Drop Down List for Fast Data Entry

Accountants need to deal with a lot of data entry work. In that case, it’s important to work with ease and speed.

DROP DOWN list gives you both. You can specify multiple options in a drop-down to select which can save your time to enter a value manually.

Follow the below steps:

  1. First, select the cell where you want to add a drop-down and then go to the Data Tab ➜ Data Tools ➜ Data Validation ➜ Data Validation.
    data-validation-option
  2. Now, in the data validation dialog box, select the list from the “Allow” dropdown.
  3. After that, you need to select the range where you have the values you want to add in the drop-down list or you can also enter them directly, using a comma (,) to separate each value.
    data-validation-dialog-box
  4. In the end, click OK.
    drop-down-list

You have a total of 8 different ways to create a drop-down and apart from that, you can also create a cell message and an alert message for a cell.

13. Using Comments and Notes for Audit

Comments and Notes can be super useful when it comes to auditing reports.

Well both of these are equally useful, but there is one plus point with the comments, you can create a conversation.

To add a comment, go to the Review Tab ➜ Comments ➜ New Comment.

And when you enter a comment, it looks like below example where it says “Start a Conversation”.

Now when you type your comment and hit the enter button and Excel creates a thread where you or anyone (if you share this file with others or use co-authoring) can add their comments.

You can navigate to comments with the “Previous” and “Next” buttons and you can also see all the comments on the side pane.

And just next to the comments there’s a button to add a note. Basically notes are the compatibility version of notes and you can also convert all the notes into comments.

14. Use Named Ranges in Calculations

In financial data, you do have a lot-specific data which you use frequently and a named range can super useful in for that.

Let me show you an example. Imagine you have a discount rate (11%) which you use frequently use.

So instead of using the hard value everywhere you can create a named range of that rate and use it in all the formulas.

  • First, go to the Formula Tab ➜ Defined Names ➜ Define Name.
  • In the dialog box, you need to enter the following:
    • Name of the range: Discount1
    • Scope: Workbook
    • Comment: General Discount
    • Refers to: You can refer to a range of input the value.
  • In the end, click OK.

Now you can use this named range where ever you want to use the discount just by entering “Discount1”.

And if you want to update the rate of discount you simply need to edit the value from the define name dialog box.

15. Functions for Accountants

Excel has a whole bunch of functions (See this:‌ Excel Functions List) and below you have top functions for accountants:

  • ABS: This function converts a number (negative to positive) into an absolute number.
  • SUMIFS: With this function, you can sum values from an array using multiple conditions.
  • AVERAGEIFS:‌ With this function, you can average values from an array using multiple conditions.
  • COUNTIFS: With this function, you can count values from an array using multiple conditions.
  • SUMPRODUCT: This function calculates the products of two or more arrays and then returns the sum of those products.
  • EOMONTH: It returns the last day of a future or a past month using the number you provide.
  • DATEDIF: It returns the number of days between two days using different parameters (days, months, or years).
  • FV: It calculates the future value of an investment using constant payments and a constant rate of interest.

16. Formulas for Accountants

A formula is