The key task of management accountants is to do lot of number crunching i.e. to analyse the financial as well as non-financial data, interpreting data, summarizing the results, and prepare reports and dashboards for the management and also give insights and recommendations the management. Microsoft Excel is the key tool of the management accountants for data analysis and reporting. Management accountants are therefore expected to be highly proficient in Excel. It is not enough for management accountants to just know the basic Excel. They MUST have expertise in using various advanced features of Excel. It will not be an exaggeration to say that without adequate Advanced Excel skills, management accountant will not be able to utilize his / her full potential.
I have observed that most of the Excel users are using not more than 40-50% of the Excel features. The users who learn and apply the advanced techniques of Excel are able to demonstrate better performance because they can do more sophisticated and in-depth analysis, which naturally bring better results, and also they save time by expediting the Excel related work by using appropriate features, tools and techniques of Excel.
For doing data analysis it is essential to feed data to the spread sheet (Excel sheet). Data can be fed in one or more of the following modes:
- Manual data feeding
- Linking from other sheets or other workbooks
- Copy – pasting from other sources
- Importing from databases such as ERP system or CRM system
Although, we people do not give much importance to data entry aspect in Excel, it is one of the most essential operations in the entire process of data analysis & reporting. First of all, the data should be accurate and complete. If accuracy aspect is compromised then the analysis, findings and reports based on such data will not be trustworthy. Secondly there are always time limits for issuing the reports to higher authorities, who are not going to see your base data. So it is observed that in various cases, especially where the work is done largely in Excel sheets, the base data is many times vitiated or not fully accurate due to undue pressures in data entry process or copy pasting errors or linking or importing errors. Data entry aspect is largely ignored by most of the users without understanding its importance in the entire analysis and reporting process.
There are certain techniques which expedites data entry process. Some of the techniques are presented below, which can help users to speed up data entry process.
Controlling direction of cell pointer
We all know that when we press <ENTER> the cell pointer moves to another row. Some times when we want to enter data in the same row across the columns i.e. horizontal, it becomes very disturbing and time consuming when the cell pointer moves down with <ENTER>. However if you want that cell pointer should move to right in the same row instead of moving down, you can do that through Excel Options from Office Button (in Excel 2007) or File Button (in Excel 2010). Follow the steps stated below:
- Office Button – Excel Options – Advanced Tab – Editing Options.
- Ensure that first option (check box) “After pressing Enter, move selection” is checked.
- Select the direction from the drop down list.
You can also uncheck the option to prevent the cell pointer move to another cell on ENTER. In such case you need to move the cell pointer with arrow keys. That is what I prefer!!!
Using AutoFill Command on Ribbon for entering series
Sometimes you are required to fill series in the rows or columns like numbers or dates. You can use fill-handle to auto fill series. For series up to say 100, 200, fill-handle is okay. But what if you need to insert 1 to 50,000 numbers, say to give numbers to the accounting entries downloaded from ERP system? Or you want to insert dates for 10 consecutive years? In such cases you need to use the Fill button which can be accessed by HomeàEditingàFill as shown in the figure below in the red circle:
- If you want sequential number from 1 to 50000, type 1 in the first cell and then select the entire column.
- Click Fill button
- Select Series from Drop Down list and you will get following dialogue box
- In the Series dialogue box type 50000 in Stop value field. In Series In check Columns.
- Press OK and you will get 1 to 50000 in column A.
- Similarly you can work with dates viz. days, weekdays, months and years.
- You can also project linear trend / growth by checking Trend check box. This tool can be effectively used in preparing tables for present values, future values, annuity, actuarial calculations etc.
- Using AutoCorrect for converting acronyms into full names
- Using Replace for converting acronyms into full names
Entering decimal points automatically
Many times we are confronted with the situation to make entries which includes decimal points also. It is very tiresome to use decimal points each and every time. One solution to the problem is to type full figure (including decimal places) without entering “decimal point” each and every time. Thereafter dividing the entire range by 100 either manually or using paste special command. There is nothing wrong in it. But there is more efficient way by telling Excel to instantly insert the decimal places when you type the full figure. You can do it through Excel Options. Go to Office Button à Excel Options à Advanced à Editing options à check “Automatically insert a decimal point” as shown in the picture below.
Start typing figures and you will observe that Excel inserts decimal places automatically. You can also specify the number of decimal places by selecting from the drop down list “Places”.
Converting into decimal points through number formatting
Number formatting is one of the very powerful tools of Excel because the way you display numbers in your report has impact on the readers / users. If you type full figures including decimal places but without inserting the decimal points and you want to display it into 2 decimal places you can do it by number formatting also with following steps:
- Select the range containing the numbers
- Right click and select Format Cells. From the Format Cells dialogue box select Custom in the Category drop box and then in the Type field enter following : 0”.”00 as shown in below picture.
- Click OK
You will find that all the numbers are displayed in hundreds with 2 decimal places. Remember, it is only display and the actual figures are intact in cells. If you use Excel Options or Paste Special techniques actual numbers are also changed.
Copying cells and ranges Down with CTRL – D
You can copy the entries downwards by using CTRL – D
Copying cells and ranges Right with CTRL – R
You can copy the entries on right side by using CTRL – R
Copying cells and ranges Up with ALT-H-FI-U Left with ALT-H-FI-L
Like CTRL-D and CTRL-R for fast copying down and in right, there are no specific built in key board short cuts for copying up or in left. But you can use ALT-H-FI-U for copying up and ALT-H-FI-L for copying on left side of active cell / range.
Key board shortcuts for formatting (number, dates, time, percentage, currency etc.
- For number formatting including 2 decimal places : CTRL-SHIFT-1
- For time formatting : CTRL-SHIFT-2
- For date formatting in format DD-MMM-YYYY : CTRL-SHIFT-3
- For Dollar currency symbol : CTRL-SHIFT-4 (unfortunately there is nothing for Rupee symbol)
- For percentage format i.e. (%) : CTRL-SHIFT-5
- For drawing gridlines : CTRL-SHIFT-7
- To switch on the formula mode (i.e. to see the formula in cells) : CTRL -~
- To switch off the formula mode (i.e. to see the actual and calculated results) : CTRL -`
Placing data in multiple cells by using CLTR – Enter
- Select the range
- Enter the number / formula in upper left cell
- Press CTRL-ENTER
You will get the results in all the cells.
Although the feature seems to be very simple and sometimes of no use, it is a very powerful tool for expediting the work, especially while copying calculated cells (i.e. formulas) in large number of cells or a very big range. See the examples in sheet Fill Button and Multi Cells” and sheet Multi Cells with Ctrl Enter in the file Fast Data Entry Techniques.xlsx
Generating “Pick-up from drop down list” with ALT – Down Arrow
If you are making list and there are frequent entries, you can use “Pick-up from drop down list” from short-cut menu (i.e. right click menu). But right clicking all the times is tiresome and frustrating. So instead of right clicking method, you can simply use ALT-Down Arrow key to generate “Pick up from drop down list” and you can select the appropriate data item.
Fast moving around sheet using CTRL – Arrow Keys
With combination of CTRL and Arrow Keys you can move fast around the data in a sheet. If there will be no data in the sheet CTRL-Arrow Keys will take you to the end / beginning of the sheet.
Certain Paste Special Techniques
Paste Special is a powerful tool.
Although Paste Special technique is not unknown or even advanced, its frequent use can help in speeding up the data entry and other primary tasks. For example mathematical operations are not frequently used especially in tricky situations. Some examples of such tricks are below.
- If you want to convert numbers into negative, you can do it either using Multiply option (multiply with -1) in Paste Special dialogue box or you can simply use Subtract option.
- You can copy and paste only comments / validation criteria in the cells without copying the contents of the cell.
- You can copy and paste only values or only formulas.
- You can link multiple cells or range with the single / multiple reference cells with paste link. It can be absolute reference or relative reference.
- You can transpose the rows / columns
Forcing new line within the wrapped cell
Excel is widely used as text records keeping tools such as notes, audit observations etc. Some times in the single cell there might be need to insert numbered points or bullet points as shown in the picture below:
The technique is to use ALT-ENTER to force cursor on new line within the cell.
I hope that these techniques will be quite useful in day to day data entry operations in Excel. In the next article I will come with some more Excel tips. You can mail me your suggestions / comments firstname.lastname@example.org
About the Author
Mehul Metha (CA, CMA Aus) is an Excel Expert. Mehul has wide experience in project finance, corporate finance, accounting & auditing, financial modelling and training. He imparts training in Advanced Excel, Financial Modelling and Data Analytics. Mehul is also CMA Program Manager at CMA India. You can contact him on email@example.com.