The Accountant's’ Guide to Important Microsoft Excel Functions

The Accountants Guide to Important Microsoft Excel Functions

Microsoft Excel is one the quintessential software programs used in the business community across the world for recording financial data to aid their planning, budgeting, forecasting and decision making processes. With the Google Drive, accounting enterprises are able to take the advantage of cloud to facilitate anytime, anywhere access to financial data.

Even after decades since its launch, MS Excel continues to remain the industry’s favourite for a host of reasons but, mainly due to its intrinsic nature of simplifying complex calculation using advanced formulae and IF functions. In the U.S. alone, about 69 percent of the companies reportedly use Excel, as per the 2016 Accounting and Finance Function reports by Robert Half.

For the accounting professionals, MS Excel is more of a productivity tool, if leveraged effectively. But without the know-how about the basic features and simple hacks, it can be daunting to perform even the slightest calculation. With the pressure of tax season getting on the accounting departments, tips and tricks with Ms Excel would make an accountant’s job easier.

With that in mind, we have compiled below a list of Excel hacks:

1, Ready-to-use Templates: MS Office Excel sheets have ready-to-use templates that can be used to create profit and loss statements, cash flows and budget reports. To perform much complex tasks and generate comprehensive reports in accounting, custom-built templates can be downloaded online or purchased from third-party software vendors.

2, Quickly Format Spreadsheets: For an accountant it is not unusual to format large volume of data on a spreadsheet, add borders, change the color of cells or format numbers, currency, etc for better comprehension and accuracy in reports. The Ctrl+ Shift key provide shortcuts to format some common functions such as:

• Ctrl+ Shift+1 to format number to include two decimal places.
• Ctrl+Shift+2 to format time.
• Ctrl+Shift+3 to format date.
• Ctrl+Shift+4 currency formatting.
• Ctrl+Shift+5 percentage formatting.
• Ctrl+Shift+6 for formatting scientific or exponential form.

Learn now to format better with Munimji’s Accounting Courses that also includes MS Office Excel training to get better at your accounting job.

3, Modify Data with PivotTable: The most important function of pivot tables is help users analyse and summarise data sets as well as make comparisons of reports and statements. Creating a PivotTable requires users to have a fairly thorough idea on the way their data has to be represented on the sheet. Below are simple instructions to use PivotTables:

• Choose a cell on the spread sheet where the relevant data is to be added.
• Click on the PivotTable option that pops on the insert tab.
• Select field that needs to be added on the PivotTable
• Drag label fields in the rows to add client names or account names.
• Drag a numerical field to add values of assets.
• Select the ‘show value as’ option on the value field to place value of the amount or figures as percentage, gross income, net income, grand total, etc.

4, Use Reference Options to Change or Copy Formula on Any Cell: By default, Excel sheets use the option ‘relative reference’ that automatically changes the formula when the user copies it to a different cell. By changing the default setting to ‘absolute reference’, the users can maintain the same formula when positioned or copied to different cells rows or columns. For this, the users should add the dollar ($) symbol to fix the row or column reference.

Another shortcut method to add absolute reference is by hitting the F4 key many times until the right reference option appears on the row or column.

5, Use Drop Down List on the Cells to Avoid Erroneous Entries and Changes: Drop down lists not only segments data as a list but also helps make data entries simpler if, many people are using the Excel sheet. It is useful to add entries with unique and special characters like codes, phone numbers, alpha-numerical symbols, etc. By adding error message, the users can also prevent entries that do not belong in the drop down list. Given below is a simple instruction
to create a drop down list:

• Select the row or column where you want to add the drop down list.
• Name your list by selecting the cells and clicking left on the formula bar where the name box appears. Then press ‘Enter’. Make sure the name does not have any spaces.
• To add the drop down list option to the cell, move to Data Tab where there are a number of option and select the ‘Data Validation’ option.
• The Data Validation window pops up after clicking on the option, choose the option of ‘List’ in the drop down option under the ‘Allow’ option available on the window. Manually type in the values you wish to add in the drop down option called ‘Source’ which is beneath the Allow in the window. The values should be separated by comma. Eg, Sunday, Monday,. Then click Ok.
• A new clickable icon appears at the cell, indicating the drop down the menu with values mentioned one below the other.
• To add an error message so that main data or value does not get altered, click on the Error Alter tab.

6, Merge Data from Multiple Cells into One Cell: The users can use the CONCATENATE function to automatically merge data in multiple cells into one. This would be useful when accountants are using spread sheet with data disbursed across multiple rows and columns. To merge data into one cell follow the simple steps below:

• Choose the column where you prefer to have all the data merged and type in =CONCATENATE(B2,C3,etc) on the formula bar that relevant cell references.
• You can also add space in between the cell reference by using the formula, =CONCATENATE(B1," ", C1).

7, Search and Select Cells Faster: To make move across a huge spread sheet, you can press down the Ctrl key longer and use arrow keys to navigate accordingly.

In order to select large amount of data, you can hit the Shift and Ctrl key and use arrow keys to move across the sheet.

8, Use F4 Key to Copy Formatting: F4 key helps copy formatting from one cell to another cell. Simply select a cell where you prefer the formatting to appear and click on the F4 key.

9, Use Countif to Make Quick Calculations: The Countif function when added on the formula bar helps count the number of times a particular data has appeared on the sheet. It is useful in recording the number of times a payment has been made or an entry is recorded.

If you want the total number of times the data is mentioned on the sheet to appear, then the formula, =COUNTIF(range,’”criteria”) has to be added on the formula bar. Range refers to the cells or columns and criteria refer to the data for which the user is making the search.

10, Reduce Time in Searching for Other Sheets: Accountants can reduce time wasted in looking for multiple worksheets just by clicking on the Files (hitting Ctrl F as shortcut) or Office Button based on the version of Excel software the user is using and hit on the ‘Recent’ option. This will instantly show the recent worksheets.

Additionally, the number of worksheets that shows up can also be altered in the ‘Advanced’ section of the ‘Options’ menu wherein the user can expand the view or even change the number of recent documents that can be viewed.

11, Swiftly Change between Formula and Results: Accounting reports make extensive use of formula and to manually change between the formula and results, it can be quite tasking. Therefore, users can make use of this shortcut key Ctrl+tilde or ~ to swap between formula and results.

12, Segregate Data with Tables: Use the option of drawing tables with rows and columns and color coding it to make data more presentable and comprehensible. The ‘Table’ option in the ‘Insert’ tool helps add the desired number of rows and columns, making it simpler to apply formula.

13, Insert Charts for Better Data Interpretation: Sometimes, a number report looks confusing and difficult to arrive at results. With the use of Chart, Line, Pie, Bar, Area, Scatter and Other Charts options under the Insert bar, users can use them to depict financial and numerical data for analysis, research and observations.

14, Make Thorough Analysis Using Analysis Tool Pack: Some version of Excel have the feature that allows users to install the analysis tool pack or the options ‘What-if-Analysis’ which can be used to make analysis, statistics and interpret data in a much better way. This will be helpful for accountants to study the effect of one variable on the other.

15, Use Cheatsheets: With the countless reporting and data entry tasks, accountants do not always have time to learn and use all the excel formula. It is better to invest time in referring to a cheatsheet of Excel functions from a reliable source to avoid wastage of time and errors in reports and calculations.

We hope, the above list eases your tax and accounting struggles for this tax season. For more details on accounting courses and training on using Microsoft Excel for accounting, contact us at Munimji.

Blog Archive

Financial Management