MS – EXCEL : AN “EXCEL” LENT AUDIT AND DATA ANALYSIS TOOL
Many people use Excel for doing day to day activities. The items covered under might used by many among the users. This is an effort to point out some minor but very important and time saving techniques in MS-Excel. In this part only the basic tings are covered. For Pivot table and “V-lookup” function, next module is in the process.
A. BASIC CONCEPTS
- USE OF KEY-BOARD
Use of Key-board will give you more speed than use of mouse. Some important keys which can save your lot of time are as follows:
Ctrl + arrows: for going directly to the last cell whether upward, downward or right or left.
Ctrl + 1 : Formatting menu
Ctrl + “-“: Delete option
Ctrl + “0”: Hiding a column
Ctrl + “9”: Hiding a row
(these are few examples, there are lot of short-cut keys)
- USE OF MENU BAR THROUGH KEY-BOARD
It is very easy to use key-board for navigating through various options provided in the “Menu-Bar”. In all the menus in “Menu-Bar” one letter is always in the underline format. Whenever you will press the key “Alt” these options will get activated.
It begins with “File” option in the “Menu-Bar”.
Now you just need to press the letter with underline to activate that particular option in the menu bar.
The setup of Menu bar is such that initially you will find the simple menus and which are regularly used by all of us. In the same (simple menus) certain items are there which we usually did not use, but are very useful options which can save our lot of time. We will go one by one menu wise to find out these options.
I. File Menu
a. Page setup : In “File” menu “Page Set up” option is there. In the option you will find the “Sheet” tab. In that tab under the heading of Print titles you will find heading of Rows to repeat at top. With the help of this option you can give same heading to all the pages of the same table (in case table is on more than one page) which you are printing. This will save your time.
b. Print: In case of printing of excel file, involving more than one sheet, you need not to go to each and every sheet to take the print out. You need to go to the file and set the mouse cursor on the sheet tab, press “right click” and click on “select all sheets” option. Thus you have selected all the sheets at a time, now is the time to take print of all the sheets at a time. The same option can be used where you want to search something on the whole excel file containing more than one excel sheet.
II. Edit Menu
a. Paste Special: After copying you are having a wonderful option of Paste special. This option can be used in lot of ways. The first is value paste. It pastes only the value and not other things like formulae, format, etc. Second is format paste. It pastes only the format and no other thing. Third important option in this menu is Paste link button in that option. This will paste the link on the cell along with the value of the cell. Forth and last is Transpose button. Certain items are there in the row heading but you want them in the column heading, just select those items, copy them to the destination and click on Transpose button, you will find the headings of row are now headings of columns.
b. Find and Replace: This is very simple option which everybody knows very well. In this option one can find the required data in a sheet as well as in all the sheets of excel file. Go to the Find option in Edit menu , one dialogue box will appear. In that, type or paste in the area provided for Find. In the dialogue box one “option” button is provided. This button provides the option of finding the data in whole workbook which is very important audit tool. With the help of Replace option in the same dialogue box one can replace the wrong contents with the right ones in a single click in all the sheets. While replacing the content one can choose the required format for the cell which is going to be replaced. Format can be selected by using the Format button provided to the right of dialogue box.
c. Move or copy sheet: This option proves to be very useful, when we want the copy of the sheet for doing some working, while keeping the master file/sheet as it is. The sheet can be copied in the same file or any other file which is open.
d. Go To: When you start this option one dialogue box appears, in which press “special” button, which will open one more dialogue box. In the new box if you click on “visible cells only” button, it will select only those cells which are visible to our eyes. This option is specially useful in the case of selection in the “filter”.
a. Sort: This is one of the best menu available to analyse the data and keep the data in a proper format. One can sort the data from top to bottom as well as from left to right. For using this function one need to use the “option” button, in which one can sort by any manner and some default sorting are also available e.g. sorting of weekdays and months.
b. Filter: As the name of Menu suggest i.e. DATA menu, it deals with data and its analysis very effectively. Filter can filter the available data in as many ways as no of columns are available. For filtering the data the data should be in a tabular format and it is better if all the columns are having proper headings. For applying the filter one need to select the whole table or in certain cases selection of only first row will help. The option will get activated after one goes to “data” menu and then “filter”.
c. Text to Columns: This function is very useful when client didn’t give the data in excel format to you for verification. This menu will make your data fit for excel thus making it very easy to compare and analyse.
Freeze Panes: to freeze your worksheet, especially at column headings or at row headings, or at both this menu is being used. This will freeze the headings and keep the remaining sheet moving, so that one can navigate in the sheet while keeping an eye on the headings. This will help in updating the data in correct row or correct column.
B. FUNCTIONS AND FORMULAES
a. Sumif: Sumif is one of the best function used as an audit and control tool. This will give you the total of one single item which is present in a column at different places.
b. Subtotal : Subtotal is a function mostly used in case of “filtered” data. Instead of applying plane “sum” function at the end of a table(where filter is used), then whenever you will change the filter criteria it will give the total of each filtered criteria.
c. Sum: Sum function can be used in more than one ways. Those are
1. “Alt”+”=” or “+”
2. “Sum button”
3. “Sum formulae”
(the article is along with snap shots from excel, but they are not appearing here.)