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:
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:
- First of all, select the column from the “Sort By”.
- After that, in “Sort On”, select “Cell Values”.
- 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.
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:
- Within: You can select the search area for the value. You can select between the active worksheet and the whole workbook.
- Search: Search through rows or columns.
- Look in: Look in formulas, values, comments, and notes (This option only works find not with find and replace).
- Match Case: Find and replace a value with a case sensitive search.
- Match Entire Content: Match values from the entire value of a cell with the searched value.
- 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.
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:
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.
- 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.
- 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.
- In the end, once you define all, you’ll have a pivot chart like below.
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).
- It will instantly show you the side pane and with all the recommendations.
- You can simply click on “Insert” to insert your preferable chart or pivot.
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:
- 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.
- Now, in the data validation dialog box, select the list from the “Allow” dropdown.
- 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.
- In the end, click OK.
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 basically a combination of two or more Excel functions to calculate a specific value. Once you learn to use functions you can be able to create basic as well as complex formulas.
Below is the list of some of the most useful accounting formulas.
- Calculate Ratio in Excel
- Weighted Average in Excel
- Compound Interest in Excel
- Average Highest Values in Excel
- Excel 3D Formulas
- Remove First Character from a Cell Value
17. Excel Charts for Accountants
Even if you work more with finance and accounts data, you do need to present it to others. The best way for this is by using charts, and Excel gives you a full range of charts to insert.
- Line Chart: It is best to show a trend over a period of time with a line.
- Area Chart: Area chart is basically a line chart where space to the X-axis is filled.
- Column Chart: It can be used to compare different sets of values using data bars.
- Bar Chart: It’s basically a column chart in a horizontal way and useful if you have long data bars.
- Pie Chart: With a pie chart, you can present a share of multiple categories in the whole.
- Donut Chart: It’s basically a pie chart with the blank at the center.
- Advanced Excel Charts
- Add Horizontal Line to Excel Chart
- Add Vertical Line to Excel Chart
- Copy Chart Format in Excel
- Interactive Charts in Excel
18. Visual Basic for Applications
As an accountant, you need to create a lot of reports and with VBA you can all automate all those reports which you usually create manually. The best example is, using a macro to create a pivot table.
19. Take Help of Power Query
If you deal with a lot of inconsistent data, which I’m sure you do, then you need to learn the power query.
Why? With the power query, you can write queries that can work in real-time. Here are some examples:
20. OFFICE Add-Ins
If you use Excel 2013 or a later version, then you can access the OFFICE ADD-IN store where you can find a lot of add-ins to increase the functionality of Excel.
It’s on the Insert Tab ➜ Add-Ins ➜ Get Add-Ins and you have thousands of add-ins to install (third party).
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.