MS Excel 2007 application is widely being used in the organisations for data processing, financial modelling, data analysis and preparation of complex reports. Excel with its wide range of formula functions, embedded featured and adaptability makes the life of the accountant/ auditor rather simple.
Most commonly used features/ functions for accountants/ auditors.
Pivot Table
Pivot table gives you the option to prepare customized reports from the data range. The only task you have to do is to apply pivot table and rest would be done by the Excel.
- To apply pivot table just arrange the data with the headers.
Insert pivot table from the 'Insert' tab on the toolbar of the sheet. - Select the desired range of the data.
- Click 'OK' and you will see the window with all the fields in your data.
- Just select the desired field in order, you want to prepare report and copy it in a separate sheet if you want to create another report from the applied pivot table.
VLOOKUP
V lookup formula in excel is used to compare two separate set of data and retrieve any value on the basis of same control number/ text in both of the sheets. The formula can help you in preparing report from the million of records within few seconds. To apply the wonder, just follow following tips
- Sort the data in either way (ascending/ descending).
- Make sure that there is any common field in both set of data
- Apply V Lookup with the parameters given in guidance of the formula bar.
=VLOOKUP ( lookupvalue,table array,column index number, range lookup)
- Lookup value means the value you want to look in the destination/ source data
- Table array means range from which you want to retrieve value.
- Column Index number means the number of column in which there is desired value. Count number from the column you select the range. If you are selecting range from column C then Column C will be '1' instead of '3'.
Posts
