Hope many of you would have downloaded powerutils from the website www.powerutilsnet.com. Continuing the previous article some important functions that are provided by powerutils:
Powerutils >> Number
Shift “-“ from end to front: This is useful function when some data is downloaded from ERP (Enterprise Resource Planning). In case of an ERP like SAP the negative sign is displayed at end, due to this the data that is downloaded cannot be used in any formula. This function helps us shift the “-“ sign from front to end.
Maximum and Minimum: The maximum or minimum value in a range of cells can be easily identified using “Select cells with highest/lowest value in selected range”.
Write numbers in words: Conversion of any number in words is an important function for cheque printing, invoice printing, etc. Convert from number to text is useful function to carry such activities.
Powerutils >> Sheet
Create an Index Sheet: Many of us work on excel files that have too many sheets to control. In such a case it is always good to have an index sheet which has link to all the sheets. Doing this manually by hyperlink will consume a lot of time.
Other functions: Many other functions like sorting of sheets, delete empty sheets, insert the name of file/sheet, copy sheets, delete page breaks, etc. are available.
Powerutils >> Range
Format number style in crores/lakhs: This is one of the few requirements that comes up in all my seminar on excel. Standard excel can format numbers in million/billion format, but many of us need the same in crores/lakhs format. This is a simple click in powerutils.
Round off: Applying round functions to each cell is time consuming and error prone method. Hence using powerutils for rounding off all the cell in a range is easier option.
Concatenate: Use of standard concatenate function to join cells is also a bit time consuming process and can be done by a simple click of button in powerutils.
Delete all comments: There is no standard excel function to delete all the comments in a particular range. The only option available would be to go in each cell and delete comments. This can be done easily in powerutils.
Other useful functions
Insert “N” rows/columns after every row/column: This is a common requirement many times and can be done without consuming a lot of time from powerutils >> Column and Rows.
Rename current file and delete old file: The normal process to accomplish this is to close the current file, go to the folder where this file is stored and rename the same. However this can also be done by Powerutils >> File >> Rename. This does not require us to close the existing file.
Sum cells based on colour/font: Though it seems a bit weird but this is an excellent function. Assume you have some data that has different background colour and you need to do a sum/count function based on the background colour. Try powerutils >> Mathematical functions.
Copy paste special values: A normal process to carry this function is Ctrl+C and then do a paste special value. Both this activity can be combined in one step by powerutils >> Miscellaneous
As many of you read this article it would be a hot summer in Mumbai, hence it would be important for all to complete the routine activities as fast as possible and leave for home. Hope powerutils helps you in saving your time in routine activites. I have restricted my discussion to some important functions that are useful in day to day activities, but would recommend all of you to explore other functions as they may be equally useful.
Sunday, March 23, 2008
Sunday, February 17, 2008
Case Changes and Delete N Chararcters
Dear friends, this article of mine is a bit different from what you all have been reading earlier. My earlier articles revolved around various excel functions that were of use in our day-to-day operations, however today we discuss about some of our day-to-day requirements that are not met by standard excel functions and also there are many standard excel functions which consume a lot of time to perform. Powerutils is an add-in that can help us in both the above dilemmas. http://www.powerutilsnet.com/ is a website where you can download an add-in that contains the add-in.
Steps to download the add-in:
Before starting the download you have to ensure that the macro security is low. By default the security is high. To make it low go to Tools >> Security and select low.
Start your internet connection and visit website http://www.powerutilsnet.com/
On the home page of the website there is a download option (third button on the left side of your screen). Click the download button.
There is a link available to download powerutils 1.9, click the link and save the zip file on your hard disk.
After the zip file is saved open the same and double click the setup/exe file.
After performing all the above steps in sequence, your excel page would look as shown below:
Steps to download the add-in:
Before starting the download you have to ensure that the macro security is low. By default the security is high. To make it low go to Tools >> Security and select low.
Start your internet connection and visit website http://www.powerutilsnet.com/
On the home page of the website there is a download option (third button on the left side of your screen). Click the download button.
There is a link available to download powerutils 1.9, click the link and save the zip file on your hard disk.
After the zip file is saved open the same and double click the setup/exe file.
After performing all the above steps in sequence, your excel page would look as shown below:
You will see an additional option between Window and Help options as highlighted above.
Various options available in powerutils:
When you click the above tab i.e. powerutils you can see the following options:
Use of each option in powerutils add-in
Text
Change cases: Conversion of lower, upper or proper cases is easy in excel by use of function =upper(), or =lower() or =proper() as the case may be. However it is a time consuming process. In case of powerutils you just need to select the range of cells that you want to change and select the option.
Delete characters: Deletion of a particular number of starting or ending characters can be done in excel with the help of =left, = right or =mid. However use of these functions is also time consuming. Hence the delete options available in Powerutils >> text are very useful.
Split Name: In case we have a database where a particular column has full name of all the candidates it would be difficult for us to split the same in name and surname. We can use Powerutils >> Text >> Split names option to break the full name into surname, middle name and name.
We will continue with other options available in powerutils in next article.
Various options available in powerutils:
When you click the above tab i.e. powerutils you can see the following options:
Use of each option in powerutils add-in
Text
Change cases: Conversion of lower, upper or proper cases is easy in excel by use of function =upper(), or =lower() or =proper() as the case may be. However it is a time consuming process. In case of powerutils you just need to select the range of cells that you want to change and select the option.
Delete characters: Deletion of a particular number of starting or ending characters can be done in excel with the help of =left, = right or =mid. However use of these functions is also time consuming. Hence the delete options available in Powerutils >> text are very useful.
Split Name: In case we have a database where a particular column has full name of all the candidates it would be difficult for us to split the same in name and surname. We can use Powerutils >> Text >> Split names option to break the full name into surname, middle name and name.
We will continue with other options available in powerutils in next article.
Wednesday, January 9, 2008
Advanced find and replace
Our friend Bahadur Singh has a peculiar problem today. He says “I’ve marked a few hundred cells in a large workbook using a red font. My manager is fussy and wants all the red cells changed to blue. The red cells are not continuous.” Many of us would have faced such situations and than either would have spent hours doing it or might not have followed the orders of manager.
Also we frequently use find and replace functions to change a particular word to another but can we use the same to find and change a particular colour, font, etc to another?
We can use Find and Replace to change formats. Here’s what to do:
1. Select the entire range that contains the red cells.
2. Select Edit – Replace or use “Ctrl + H”. Excel will display the Find and Replace dialog (see Figure 1130).
3. Click the Options button to show additional options.
4. Leave the Find What and Replace With boxes blank. On the right side, choose the dropdown next to the top Format button. Excel will offer options for Format, Choose Format from Cell, and Clear Find Format. For the maximum flexibility, choose the Format option. Excel will display the Find Format dialog.
5. In the Find Format dialog, go to the Font tab. Change the Color dropdown to Red. Do not select a font. Don’t make any selections on any other tab. Click OK to return to the Find and Replace dialog.
6. Click the second Format button. On the Font tab, choose blue as the Color.
7. After specifying both the original and new font colors, click the Replace All button in the Find and Replace dialog.
All the red fonts are changed to blue.
The option tab in the find and replace dialog box can be used to find and replace font style, font size, format, font colour, etc.
Also at times we need to find a particular word in the entire file i.e. across sheets. The option tab can be useful to find and replace the entire workbook. This can be done by changing the within field from sheet to workbook (as highlighted in the picture above).
Also we frequently use find and replace functions to change a particular word to another but can we use the same to find and change a particular colour, font, etc to another?
We can use Find and Replace to change formats. Here’s what to do:
1. Select the entire range that contains the red cells.
2. Select Edit – Replace or use “Ctrl + H”. Excel will display the Find and Replace dialog (see Figure 1130).
3. Click the Options button to show additional options.
4. Leave the Find What and Replace With boxes blank. On the right side, choose the dropdown next to the top Format button. Excel will offer options for Format, Choose Format from Cell, and Clear Find Format. For the maximum flexibility, choose the Format option. Excel will display the Find Format dialog.
5. In the Find Format dialog, go to the Font tab. Change the Color dropdown to Red. Do not select a font. Don’t make any selections on any other tab. Click OK to return to the Find and Replace dialog.
6. Click the second Format button. On the Font tab, choose blue as the Color.
7. After specifying both the original and new font colors, click the Replace All button in the Find and Replace dialog.
All the red fonts are changed to blue.
The option tab in the find and replace dialog box can be used to find and replace font style, font size, format, font colour, etc.
Also at times we need to find a particular word in the entire file i.e. across sheets. The option tab can be useful to find and replace the entire workbook. This can be done by changing the within field from sheet to workbook (as highlighted in the picture above).
Saturday, December 1, 2007
Pivot Tables - 2
Hope many of you are now comfortable with creating pivot tables? The first article ended with creation of a simple pivot table. To summarise, while we create a pivot table we have to select characteristic fields for row, column and page (each can have more than one field) and a numeric field for data.
How to change a pivot table?
Once a pivot table is created it can be changed by going back to the wizard. Just right click any cell in the pivot table and select ‘pivot table wizard’. This will take you to Step 3 of the wizard. Select layout option and do the necessary changes needed.
In case you do not want any change in the structure of pivot table but would only like to update the pivot table with fresh data, than right click any cell in the pivot table and select ‘refresh data’.
Hiding a field – row, column and page
To remove data from a single row or column in a pivot table, hide the associated item. Double-click the field containing the item you want to hide. In the Hide Item box in the Pivot Table Field dialog box, select the items you want to hide. Repeat the above process to unhide and hidden item.
Working with Totals in a Pivot Table
Unless specified otherwise, Excel automatically adds subtotals and grand total lines to pivot table.
Grand Totals provide total values for all cells in rows and columns. Subtotal provide total values for outer items when the pivot table contains more than one row/column field.
To hide the grand total in the pivot table, right click on the pivot table and go to ‘Table Options’. Remove the ticks for ‘Grand Totals for rows’ and ‘Grand Totals for columns’, as shown below.
To hide the subtotals in the pivot table, right click the characteristic field for which you do not need pivot table and select ‘field setting’. Select ‘none’ as shown below.
Other mathematical functions
The default mathematical function would be sum. You can use other mathematical functions like average, counts, maximum, minimum, etc. To use any other mathematical function right click the pivot table and select the pivot table wizard, go to the layout designer wizard. Double click the data field and choose the functions required as shown below.
Use the ‘Options’ button (shown above) to display the data in other forms like % total/row/column, etc. This will be more helpful for analysis.
Sorting of data or Looking at Top N
To sort the data in ascending/descending order of a particular characteristic field right click the field and go to ‘field settings’. Select ‘advanced’ (shown in pic.2 above). A pop-up box as shown below will appear.
Do the needful setting and select Ok.
Pivot Charts
These are as powerful as a pivot table. They present the pivot table in a graphical format and it is very easy to prepare the same. After preparing the pivot table, right click any of the cells on a pivot table and select ‘Pivot Chart’. A pivot chart will be created automatically in a different sheet. The row fields of pivot table would be available on X axis, column field on Y axis and the page field on top left corner of the chart. By default the chart would be a bar chart. To use any other chart like line, pie, etc. select the ‘Chart Wizard’ and select the type.
How to change a pivot table?
Once a pivot table is created it can be changed by going back to the wizard. Just right click any cell in the pivot table and select ‘pivot table wizard’. This will take you to Step 3 of the wizard. Select layout option and do the necessary changes needed.
In case you do not want any change in the structure of pivot table but would only like to update the pivot table with fresh data, than right click any cell in the pivot table and select ‘refresh data’.
Hiding a field – row, column and page
To remove data from a single row or column in a pivot table, hide the associated item. Double-click the field containing the item you want to hide. In the Hide Item box in the Pivot Table Field dialog box, select the items you want to hide. Repeat the above process to unhide and hidden item.
Working with Totals in a Pivot Table
Unless specified otherwise, Excel automatically adds subtotals and grand total lines to pivot table.
Grand Totals provide total values for all cells in rows and columns. Subtotal provide total values for outer items when the pivot table contains more than one row/column field.
To hide the grand total in the pivot table, right click on the pivot table and go to ‘Table Options’. Remove the ticks for ‘Grand Totals for rows’ and ‘Grand Totals for columns’, as shown below.
To hide the subtotals in the pivot table, right click the characteristic field for which you do not need pivot table and select ‘field setting’. Select ‘none’ as shown below.
Other mathematical functions
The default mathematical function would be sum. You can use other mathematical functions like average, counts, maximum, minimum, etc. To use any other mathematical function right click the pivot table and select the pivot table wizard, go to the layout designer wizard. Double click the data field and choose the functions required as shown below.
Use the ‘Options’ button (shown above) to display the data in other forms like % total/row/column, etc. This will be more helpful for analysis.
Sorting of data or Looking at Top N
To sort the data in ascending/descending order of a particular characteristic field right click the field and go to ‘field settings’. Select ‘advanced’ (shown in pic.2 above). A pop-up box as shown below will appear.
Do the needful setting and select Ok.
Pivot Charts
These are as powerful as a pivot table. They present the pivot table in a graphical format and it is very easy to prepare the same. After preparing the pivot table, right click any of the cells on a pivot table and select ‘Pivot Chart’. A pivot chart will be created automatically in a different sheet. The row fields of pivot table would be available on X axis, column field on Y axis and the page field on top left corner of the chart. By default the chart would be a bar chart. To use any other chart like line, pie, etc. select the ‘Chart Wizard’ and select the type.
Thursday, November 1, 2007
Pivot Tables - 1
I have been receiving numerous feedbacks to write about pivot tables. It was only when I started writing I realised that writing about pivot tables in a manner that can be easily understood by layman is not an easy task. In this article I have put down an introduction to pivot tables and method of creating a simple pivot tables. We will continue this with more enhanced features on pivot table in articles to follow.
Pivot Table – An introduction
A pivot table is an interactive worksheet table that quickly summarises large amounts of data using a format and calculation methods you choose. It provides an easy way for you to display and analyse summary information about data already created in Microsoft Excel.
For example, with a Microsoft Excel database that contains sales records for several sales managers, a pivot table can help analyse sales data by name, region, month, year, etc that can be chosen from source data.
The table below is the source data:
Month
Year
Type
Sales Managers
Sales Rs.
Sales Units
Region
Jan
2005
Beverages
Mr. A
2354
50
East
Jan
2005
Hair Oil
Mr. B
3312
100
Nort
Jan
2005
Edible Oil
Mr. C
5456
50
South
Feb
2005
Beverages
Mr. B
5419
300
West
Feb
2005
Hair Oil
Mr. C
8943
200
East
Feb
2005
Edible Oil
Mr. A
9866
500
Nort
Mar
2005
Beverages
Mr. C
1425
100
South
Mar
2005
Hair Oil
Mr. A
3218
55
West
Mar
2005
Edible Oil
Mr. B
6589
30
East
Apr
2005
Beverages
Mr. C
6456
110
Nort
Apr
2005
Hair Oil
Mr. A
1211
250
South
Apr
2005
Edible Oil
Mr. C
9553
355
West
A sample pivot table based on above table is shown below:
Sum of Sales Rs.
Sales Managers
Month
Mr. A
Mr. B
Mr. C
Grand Total
Jan
2354
3312
5456
11122
Feb
9866
5419
8943
24228
Mar
3218
6589
1425
11232
Apr
1211
16009
17220
Grand Total
16649
15320
31833
63802
‘Month’ and ‘Sales Managers’ field is used from the source table to organise data in pivot table.
The data of ‘Sales Rs.’ is used from the source table and is summarised and totaled.
Steps to create a simple pivot table
You create a pivot table using the Pivot Table wizard. The Pivot Table wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table.
From Data menu, choose the Pivot Table command. Step 1 of the wizard will pop-up as shown:
Where is the data that you want to analyse?
Select Microsoft Office Excel list or database (Option 1)
What kind of report do you want to create?
Select Pivot Table (Option 1)
Click Next
Step 2 of the wizard will pop-up as shown:
Select the entire source data as the range and click Next
Step 3 of the wizard will pop-up as shown:
If you want your pivot table in the same sheet as the source table select Existing worksheet, else select New worksheet.
Click on ‘Layout’, the layout designer wizard will appear as follows:
Drag-drop the ‘Month’, ‘Sales Manager’ and ‘Sales Rs.’ field as shown above. The layout should look as shown below:
Click Ok and then click Finish. Your Pivot table as required is ready.
Try creating similar pivot tables with other fields. Also use Page (as highlighted above) in the layout designer and see the powerful analysis that it helps you with. The above article will help you create simple pivot tables, we would see some more features of pivot tables in articles to follow.
Pivot Table – An introduction
A pivot table is an interactive worksheet table that quickly summarises large amounts of data using a format and calculation methods you choose. It provides an easy way for you to display and analyse summary information about data already created in Microsoft Excel.
For example, with a Microsoft Excel database that contains sales records for several sales managers, a pivot table can help analyse sales data by name, region, month, year, etc that can be chosen from source data.
The table below is the source data:
Month
Year
Type
Sales Managers
Sales Rs.
Sales Units
Region
Jan
2005
Beverages
Mr. A
2354
50
East
Jan
2005
Hair Oil
Mr. B
3312
100
Nort
Jan
2005
Edible Oil
Mr. C
5456
50
South
Feb
2005
Beverages
Mr. B
5419
300
West
Feb
2005
Hair Oil
Mr. C
8943
200
East
Feb
2005
Edible Oil
Mr. A
9866
500
Nort
Mar
2005
Beverages
Mr. C
1425
100
South
Mar
2005
Hair Oil
Mr. A
3218
55
West
Mar
2005
Edible Oil
Mr. B
6589
30
East
Apr
2005
Beverages
Mr. C
6456
110
Nort
Apr
2005
Hair Oil
Mr. A
1211
250
South
Apr
2005
Edible Oil
Mr. C
9553
355
West
A sample pivot table based on above table is shown below:
Sum of Sales Rs.
Sales Managers
Month
Mr. A
Mr. B
Mr. C
Grand Total
Jan
2354
3312
5456
11122
Feb
9866
5419
8943
24228
Mar
3218
6589
1425
11232
Apr
1211
16009
17220
Grand Total
16649
15320
31833
63802
‘Month’ and ‘Sales Managers’ field is used from the source table to organise data in pivot table.
The data of ‘Sales Rs.’ is used from the source table and is summarised and totaled.
Steps to create a simple pivot table
You create a pivot table using the Pivot Table wizard. The Pivot Table wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table.
From Data menu, choose the Pivot Table command. Step 1 of the wizard will pop-up as shown:
Where is the data that you want to analyse?
Select Microsoft Office Excel list or database (Option 1)
What kind of report do you want to create?
Select Pivot Table (Option 1)
Click Next
Step 2 of the wizard will pop-up as shown:
Select the entire source data as the range and click Next
Step 3 of the wizard will pop-up as shown:
If you want your pivot table in the same sheet as the source table select Existing worksheet, else select New worksheet.
Click on ‘Layout’, the layout designer wizard will appear as follows:
Drag-drop the ‘Month’, ‘Sales Manager’ and ‘Sales Rs.’ field as shown above. The layout should look as shown below:
Click Ok and then click Finish. Your Pivot table as required is ready.
Try creating similar pivot tables with other fields. Also use Page (as highlighted above) in the layout designer and see the powerful analysis that it helps you with. The above article will help you create simple pivot tables, we would see some more features of pivot tables in articles to follow.
Monday, October 15, 2007
Print options
Dear Friends, it’s been a year since I have been communicating to all of you by way of this article. In today’s article instead of covering a particular feature I have covered various printing options available in excel and how to use them in our day-to-day activities.
Setting up the page
To
From the File menu, choose page setup and then
Set Margin
Select the Margins tab. In the Top, Bottom, Left, Right, Header and Footer boxes, enter the measure of margins you want.
Set Alignment on the page
Select the Margins tab. Under Center On Page, select the option you want.
Add or edit headers and footers
Select the Header/Footer tab and select the text you want.
Portrait or Landscape
Select the page tab. Under orientation you can select the required option as portrait or landscape.
Choosing What to print
To
From the File menu, choose page setup and then
Specify a worksheet range to print
Select the Sheet tab. In the Print Area box, enter the range to print.
Repeat print of rows or columns on all pages
Select the Sheet tab. Under Print titles, enter the row or column ranges that you want to be printed as titles on all the pages.
Control whether gridlines, cell notes or row/column headings are printed
Select the Sheet tab. Under Print, select the check boxes for the items you want to print.
Previewing what will print
To
Do this
Preview the print
Click the print preview toolbar button or while in the page setup dialog box you can choose the print preview button.
Fitting your work to the page
To
From the File menu, choose page setup and then
Enlarge or reduce your printed data
Select the Page tab. Select the Adjust To option button, and then type or select the scaling percentage you want.
Reduce your data so that it fits on the number of pages you specify
Select the Page tab. Select the Fit To option button, and then type or select the number of pages you want.
Controlling page breaks, page numbers and print order
To
Do this
Display page breaks on screen
Tools >> Options >> View >> Select the page break check Box.
Set manual page breaks (vertical and horizontal)
Select the cell where you want to start a new page and choose page break from insert menu.
Set a page print order
File > Page Setup >> Sheet (Tab). Under the page order, select the printing order.
Beginning numbering pages at a specified page number
File >> Page Setup >> Page (Tab). In the First Page Number box, type the starting page number you want.
Setting up the page
To
From the File menu, choose page setup and then
Set Margin
Select the Margins tab. In the Top, Bottom, Left, Right, Header and Footer boxes, enter the measure of margins you want.
Set Alignment on the page
Select the Margins tab. Under Center On Page, select the option you want.
Add or edit headers and footers
Select the Header/Footer tab and select the text you want.
Portrait or Landscape
Select the page tab. Under orientation you can select the required option as portrait or landscape.
Choosing What to print
To
From the File menu, choose page setup and then
Specify a worksheet range to print
Select the Sheet tab. In the Print Area box, enter the range to print.
Repeat print of rows or columns on all pages
Select the Sheet tab. Under Print titles, enter the row or column ranges that you want to be printed as titles on all the pages.
Control whether gridlines, cell notes or row/column headings are printed
Select the Sheet tab. Under Print, select the check boxes for the items you want to print.
Previewing what will print
To
Do this
Preview the print
Click the print preview toolbar button or while in the page setup dialog box you can choose the print preview button.
Fitting your work to the page
To
From the File menu, choose page setup and then
Enlarge or reduce your printed data
Select the Page tab. Select the Adjust To option button, and then type or select the scaling percentage you want.
Reduce your data so that it fits on the number of pages you specify
Select the Page tab. Select the Fit To option button, and then type or select the number of pages you want.
Controlling page breaks, page numbers and print order
To
Do this
Display page breaks on screen
Tools >> Options >> View >> Select the page break check Box.
Set manual page breaks (vertical and horizontal)
Select the cell where you want to start a new page and choose page break from insert menu.
Set a page print order
File > Page Setup >> Sheet (Tab). Under the page order, select the printing order.
Beginning numbering pages at a specified page number
File >> Page Setup >> Page (Tab). In the First Page Number box, type the starting page number you want.
Monday, October 1, 2007
Use of Option Tab
What is the first thing we do once we open Excel for new file? May be changing the font type, font size, etc. Would it be easier if we can decide the default excel settings? You can change most if the elements of the Excel workbook, sheet, workspace and window display. You can also turn many features of Microsoft Excel on or off, or change the way they work to best suit your working style.
Options You Can Change
You use the tabs in Options dialog box to change Excel defaults and settings. Choose the Options command from the Tools menu to display the dialog box.
Tools >> Options
1. View Tab: Change the way workbooks, sheets, objects and windows are displayed. Some important settings include hiding of gridlines, deciding the colour of gridlines, viewing sheet tabs, scroll bars, etc.
2. Calculation Tab: Change how and when Excel recalculates data, how it manages links to data on other sheets and the date system it uses. An important setting for precision as displayed is available here.
3. Edit Tab: Change how you enter, edit, copy and move data. Few important setting useful in day-to-day operations include edit directly in cell (also useful as tracing tool) and number of decimal places.
4. General Tab: Change options such as the number of sheets in a new workbook, the standard font, recently used file list, etc. This is one of the most useful tab for users who want all their files to be in a specific font style and specific font size.
5. Custom Lists Tab: Create custom lists that can be used with AutoFill feature and with custom sort orders.
6. Chart Tab: Define how empty and hidden cells are plotted for line charts and how charts are sized with windows.
7. Colour Tab: Change the default colour palette, including the colours that are used for chart fills and lines, and copy colour palettes from other workbooks.
8. International Tab: Change the default decimal and thousands tab from “.” and “,” respectively and other print and view options.
9. Save Tab: Automatically makes a workbook recovery file at the interval you enter in the Minutes box (enter a number from 1 to 120). If your computer hangs (stops responding) or you lose power unexpectedly, Microsoft Excel opens the AutoRecover file the next time you start Excel. You can also change the default auto recovery location.
10. Error Checking Tab: You can modify the error checking setting here. The default colour is green, which can be changed in error indicator colour.
11. Spelling Tab: Change the default dictionary language. Select English (U.K.) to accept words like colour, recognising, etc. instead of color and recognizing. The autocorrect button in this tab also helps you to decide the spellings that excel would automatically change. You can add CA with Chartered Accountants and the next time you type CA excel will automatically convert it to Chartered Accountants.
12. Security Tab: Provide the security password to open or modify files.
For more information about the choices in the Option dialog box, you can click the Help button on any of the tabs.
Options You Can Change
You use the tabs in Options dialog box to change Excel defaults and settings. Choose the Options command from the Tools menu to display the dialog box.
Tools >> Options
1. View Tab: Change the way workbooks, sheets, objects and windows are displayed. Some important settings include hiding of gridlines, deciding the colour of gridlines, viewing sheet tabs, scroll bars, etc.
2. Calculation Tab: Change how and when Excel recalculates data, how it manages links to data on other sheets and the date system it uses. An important setting for precision as displayed is available here.
3. Edit Tab: Change how you enter, edit, copy and move data. Few important setting useful in day-to-day operations include edit directly in cell (also useful as tracing tool) and number of decimal places.
4. General Tab: Change options such as the number of sheets in a new workbook, the standard font, recently used file list, etc. This is one of the most useful tab for users who want all their files to be in a specific font style and specific font size.
5. Custom Lists Tab: Create custom lists that can be used with AutoFill feature and with custom sort orders.
6. Chart Tab: Define how empty and hidden cells are plotted for line charts and how charts are sized with windows.
7. Colour Tab: Change the default colour palette, including the colours that are used for chart fills and lines, and copy colour palettes from other workbooks.
8. International Tab: Change the default decimal and thousands tab from “.” and “,” respectively and other print and view options.
9. Save Tab: Automatically makes a workbook recovery file at the interval you enter in the Minutes box (enter a number from 1 to 120). If your computer hangs (stops responding) or you lose power unexpectedly, Microsoft Excel opens the AutoRecover file the next time you start Excel. You can also change the default auto recovery location.
10. Error Checking Tab: You can modify the error checking setting here. The default colour is green, which can be changed in error indicator colour.
11. Spelling Tab: Change the default dictionary language. Select English (U.K.) to accept words like colour, recognising, etc. instead of color and recognizing. The autocorrect button in this tab also helps you to decide the spellings that excel would automatically change. You can add CA with Chartered Accountants and the next time you type CA excel will automatically convert it to Chartered Accountants.
12. Security Tab: Provide the security password to open or modify files.
For more information about the choices in the Option dialog box, you can click the Help button on any of the tabs.
Saturday, September 1, 2007
School Mathematics
We have been reading a lot about sophisticated functions, features, etc. available in excel. Today as I sit to pen my new article in rain lashed Mumbai, my memory flew back to school days and there is a strange thought, can excel solve various mathematical problems we solved during school time. For all of us today if a kid asks us how to calculate Least Common Multiple (LCM as it is commonly known as) of some numbers do we have an answer?
Our best friend, Excel has it. Let us refresh some of these.
1. Least Common Multiple (LCM)
LCM is defined as the least integer that is a multiple of given integers. It is often used for additions or subtractions of fractions with different denominators. Hence if you want to add 3/4 to 5/6, the first step is to derive LCM of both the denominators i.e. 4 and 6. To arrive at LCM we need to split each number in all it multiple. Hence
4 = 2 x 2
6 = 2 x 3
Now the LCM of 4 and 6 is 12. This is calculated by taking the common multiple 2 and other factors 2 and 3. Now this was easy as the numbers were small, but what if the numbers are big?
Excel has a simple solution, just type =LCM(4,6). Output will be 12. In case there is an error go to Tools >> Add-Ins and ensure that Analysis ToolPak is checked.
The syntax is =LCM(cell1,cell2,cell3,…cell29). It can compute LCM for maximum of 29 values.
In our above problem for adding 3/4 to 5/6 once we have 12 as LCM of denominators we convert individual fractions into common denominators. Hence 3/4 5/6 are converted 9/12 and 10/12 respectively and the solution is 19/12.
2. Greatest Common Divisor (GCD)
GCD is defined as the largest number that divides without remainder into a given set of numbers.
GCD of 4 and 6 is 2. This is calculated by taking the common multiple as 2. Similar to LCM this was easy as the numbers were small, but what if the numbers are big?
Excel has a simple solution, just type =GCD(4,6). Output will be 2. In case there is an error go to Tools >> Add-Ins and ensure that Analysis ToolPak is checked.
The syntax is =GCD(cell1,cell2,cell3,…cell29). It can compute GCD for maximum of 29 values.
3. Roman Numerals
The number range that we use in common language has a corresponding roman number. Example 1 is represented as I, 2 as II, 5 as V, 10 as X, 50 as L, 100 as C, 1000 as M, etc. Though many of us know the above convention if we are asked to represent a number like 3249 in roman number many of us would find this difficult. But if u have excel just type =roman(3249) the output would be MMMCCXLIX.
The syntax is =roman(cell).
4. Factorial
Many of us would be surprised but there is a standard function in excel for factorial also. Factorial is most commonly used in permutation and combination. Factorial is product of all the integers of a particular number till 1. Hence 5 factorial represented as 5! is equal to 5 x 4 x 3 x 2 x 1 = 120. But in excel it is as simple as typing =fact(5). The output would be 120.
The syntax is =fact(cell).
5. Permutation
A permutation is defined as a possible selection of a certain number of objects taken from a group with regard to order. The permutations of two letters from the group of three letters A, B and C would be as follows:
AB, AC, BC, BA, CA, CB
The mathematical formula is 3P2, represented by 3!/(3-2)!. Hence the solution is 6.
For excel users it is as easy as typing =permut(5,3). The output would be 60.
The syntax is =permut(cell1,cell2)
6. Combination
A combination is defined as a possible selection of a certain number of objects taken from a group without regard to order. For instance, suppose we were to choose two letters from a group of three letters. If the group of three letters were A, B, and C, we could choose the letters in combinations of two as follows:
AB, AC, BC
The order in which we wrote the letters is of no concern; that is, AB could be written BA, but we would still have only one combination of the letters A and B.
The mathematical formula is 3C2, represented by 3!/{(3-2)!*2!}. Hence the solution is 3.
For excel users it is as typing =combin(3,2). The output would be 3.
The syntax is =combin(cell1,cell2).
Our best friend, Excel has it. Let us refresh some of these.
1. Least Common Multiple (LCM)
LCM is defined as the least integer that is a multiple of given integers. It is often used for additions or subtractions of fractions with different denominators. Hence if you want to add 3/4 to 5/6, the first step is to derive LCM of both the denominators i.e. 4 and 6. To arrive at LCM we need to split each number in all it multiple. Hence
4 = 2 x 2
6 = 2 x 3
Now the LCM of 4 and 6 is 12. This is calculated by taking the common multiple 2 and other factors 2 and 3. Now this was easy as the numbers were small, but what if the numbers are big?
Excel has a simple solution, just type =LCM(4,6). Output will be 12. In case there is an error go to Tools >> Add-Ins and ensure that Analysis ToolPak is checked.
The syntax is =LCM(cell1,cell2,cell3,…cell29). It can compute LCM for maximum of 29 values.
In our above problem for adding 3/4 to 5/6 once we have 12 as LCM of denominators we convert individual fractions into common denominators. Hence 3/4 5/6 are converted 9/12 and 10/12 respectively and the solution is 19/12.
2. Greatest Common Divisor (GCD)
GCD is defined as the largest number that divides without remainder into a given set of numbers.
GCD of 4 and 6 is 2. This is calculated by taking the common multiple as 2. Similar to LCM this was easy as the numbers were small, but what if the numbers are big?
Excel has a simple solution, just type =GCD(4,6). Output will be 2. In case there is an error go to Tools >> Add-Ins and ensure that Analysis ToolPak is checked.
The syntax is =GCD(cell1,cell2,cell3,…cell29). It can compute GCD for maximum of 29 values.
3. Roman Numerals
The number range that we use in common language has a corresponding roman number. Example 1 is represented as I, 2 as II, 5 as V, 10 as X, 50 as L, 100 as C, 1000 as M, etc. Though many of us know the above convention if we are asked to represent a number like 3249 in roman number many of us would find this difficult. But if u have excel just type =roman(3249) the output would be MMMCCXLIX.
The syntax is =roman(cell).
4. Factorial
Many of us would be surprised but there is a standard function in excel for factorial also. Factorial is most commonly used in permutation and combination. Factorial is product of all the integers of a particular number till 1. Hence 5 factorial represented as 5! is equal to 5 x 4 x 3 x 2 x 1 = 120. But in excel it is as simple as typing =fact(5). The output would be 120.
The syntax is =fact(cell).
5. Permutation
A permutation is defined as a possible selection of a certain number of objects taken from a group with regard to order. The permutations of two letters from the group of three letters A, B and C would be as follows:
AB, AC, BC, BA, CA, CB
The mathematical formula is 3P2, represented by 3!/(3-2)!. Hence the solution is 6.
For excel users it is as easy as typing =permut(5,3). The output would be 60.
The syntax is =permut(cell1,cell2)
6. Combination
A combination is defined as a possible selection of a certain number of objects taken from a group without regard to order. For instance, suppose we were to choose two letters from a group of three letters. If the group of three letters were A, B, and C, we could choose the letters in combinations of two as follows:
AB, AC, BC
The order in which we wrote the letters is of no concern; that is, AB could be written BA, but we would still have only one combination of the letters A and B.
The mathematical formula is 3C2, represented by 3!/{(3-2)!*2!}. Hence the solution is 3.
For excel users it is as typing =combin(3,2). The output would be 3.
The syntax is =combin(cell1,cell2).
Wednesday, August 1, 2007
Data Validation
Back from his refreshing trip to Himalaya, Bahadur Singh is back to his routine work, though a bit dissatisfied. Dissatisfied because he only got a few minutes of discussion with Excel, whereas he wanted much more. As soon as he joins office he is assigned an activity for creation of checklist. This checklist has a number of multiple choice questions. The requirement is against each question there should be a drop-down box of answers from which a user can select his reply. Not knowing which software can meet his need he comes to us for a solution. How can we achieve this in Excel?
Select the cell where u want to configure validation and Go to Data >> Validation. The screen as shown below appears.
The box contains three tabs:
1. Settings – The first tab i.e. settings tab gives us the validation criteria. By default the selection is any value. Other options include:
1.1. Whole Number – On selection of this option one can specify the parameter of data like between, equal to, greater than, etc. and also provide for values satisfying the above criteria. On activating this, the cell will accept only those value that satisfy the criteria entered.
1.2. Decimal – This is similar to Whole number except that one can also enter decimal numbers in the criteria.
1.3. List – This is one of the most useful option in validations. Here one can specify any value in the source field. Example the value can be Yes, No, NA or they can be Good, Bad, OK, etc. While activating this option there is a checkbox on the right side of screen for ‘In-cell dropdown’. By keeping this option active i.e. by checking this box the value as provide in the source box will be available as a drop-down to the user. The user will than only need to select from that list. This can be very useful while we prepare some checklist like applicability of list of accounting standards for a particular company. We can configure a validation for Yes, No and NA.
1.4. Date – When one wants to restrict a data entry in form of date with a certain criteria this option is useful.
1.5. Time – This is similar to Date for using Time.
1.6. Text length – This is an option where a length of a particular statement to be entered in a cell can be controlled. This can be useful for remarks column in many of our checklists.
2. Input Message – Here one can provide the statement that is seen by user when a cell is selected. On selecting this tab the following screen will appear.
Select the cell that has a particular validation discussed in first tab and than go to Data >> Validation >> Input Message (Second Tab). Check the box ‘Show input message when cell is selected’. Give a Title and an input message. By doing this whenever a user clicks that particular cell the message as given here will appear.
3. Error Alert – This tab is useful to decide whether an incorrect entry in a cell with validation has to be rejected out-rightly, accepted with a warning or an information message has to be given to the user. On selecting the third tab the following screen will appear:
Here in the style menu one can select Stop, Warning or Information. Selection of Stop would mean an incorrect entry would be rejected. Warning and Information will accept an incorrect entry but will throw up a message. The message in all the above cases can be configured in the space provided for Title and Error Message.
The above function is very useful to prepare audit checklists, AS applicability checklists, feedback forms, etc.
Excel the saviour again…
Select the cell where u want to configure validation and Go to Data >> Validation. The screen as shown below appears.
The box contains three tabs:
1. Settings – The first tab i.e. settings tab gives us the validation criteria. By default the selection is any value. Other options include:
1.1. Whole Number – On selection of this option one can specify the parameter of data like between, equal to, greater than, etc. and also provide for values satisfying the above criteria. On activating this, the cell will accept only those value that satisfy the criteria entered.
1.2. Decimal – This is similar to Whole number except that one can also enter decimal numbers in the criteria.
1.3. List – This is one of the most useful option in validations. Here one can specify any value in the source field. Example the value can be Yes, No, NA or they can be Good, Bad, OK, etc. While activating this option there is a checkbox on the right side of screen for ‘In-cell dropdown’. By keeping this option active i.e. by checking this box the value as provide in the source box will be available as a drop-down to the user. The user will than only need to select from that list. This can be very useful while we prepare some checklist like applicability of list of accounting standards for a particular company. We can configure a validation for Yes, No and NA.
1.4. Date – When one wants to restrict a data entry in form of date with a certain criteria this option is useful.
1.5. Time – This is similar to Date for using Time.
1.6. Text length – This is an option where a length of a particular statement to be entered in a cell can be controlled. This can be useful for remarks column in many of our checklists.
2. Input Message – Here one can provide the statement that is seen by user when a cell is selected. On selecting this tab the following screen will appear.
Select the cell that has a particular validation discussed in first tab and than go to Data >> Validation >> Input Message (Second Tab). Check the box ‘Show input message when cell is selected’. Give a Title and an input message. By doing this whenever a user clicks that particular cell the message as given here will appear.
3. Error Alert – This tab is useful to decide whether an incorrect entry in a cell with validation has to be rejected out-rightly, accepted with a warning or an information message has to be given to the user. On selecting the third tab the following screen will appear:
Here in the style menu one can select Stop, Warning or Information. Selection of Stop would mean an incorrect entry would be rejected. Warning and Information will accept an incorrect entry but will throw up a message. The message in all the above cases can be configured in the space provided for Title and Error Message.
The above function is very useful to prepare audit checklists, AS applicability checklists, feedback forms, etc.
Excel the saviour again…
Sunday, July 1, 2007
Protect sheets and files
While Bahadur Singh is still in Himalayas let us ask a question to us…
How confidential are the excel files that you have on your PC? Ask this question to any of our friend and he will reply saying very confidential. What do all of us use excel for – preparation of statement of total income, preparation of final accounts, maintaining contact list, etc. If all these are so important than how many of us ensure that they stay protected from any invasion or information leakage. How many of us use various protection features provided by excel. In this article we will discuss about various ways and means one can protect a particular excel file or some data from an excel file.
Password protect an excel file:
An excel file can be password protected to open or to modify. To do the same go to File >> Save As >> Options… (Button available on the right side). This will provide you with a small window as shown below.
There are two options available as seen above.
1. Password to open: In case one wants to avoid any unknown person from opening this file, this option should be used. Enter the password in first option and press OK. A window to confirm the password will appear. Repeat the same password and press OK. Now save the file. This will provide a password protection to open this file. Henceforth whenever one wants to open this file, the system will prompt for a password request. Only on entering correct password will the file open.
2. Password to modify: This option should be used for files that are generally referred to. For example in case of a practicing CA there would be a file indicating rate table for NSC. Whenever he is preparing a statement of total income he would need to refer this NSC rate table file. However this file is updated rarely. Hence if one wants to protect it from modification by any unknown person, this option will be helpful. To activate this option enter password in the second box above. A window to confirm the password will appear. Repeat the same password and press OK. Henceforth whenever one wants to open this file, the system will prompt for a password request. However unlike option 1 above, even if one does not know the password he will still be able to open the file but only in the read-only mode i.e. he will not be able to modify the file. Hence modification of this file is possible only by a person who know the password.
3. Read-only recommended: The check-box available at the bottom of the window is also very useful. If one does not want to use password, as there is always a probability that one may forget the password, this option should be tried. On activating this option by checking the box whenever this file is opened system will prompt a message as shown below:
Here if one wants to do some modification in this file than press No else Yes. This is important for files that are not confidential but are referred to very often and whenever we open that file there is a probability that we may make some error and save it. To avoid this, reference file like NSC rate table should be activated with this option.
Password protect a worksheet:
In the above example we saw how to protect a file. However at time we would like to protect a sheet and not the entire file. Example, in a file for tax return, one sheet has statement of total income and another sheet has capital account and balance sheet. There could be a need to protect the sheet for statement of total income and keep all other sheet open. In such a case go to Tools >> Protection >> Unprotect Sheet… Window as shown below will pop-up.
Enter the password or leave it blank and press OK. This will make this sheet un-editable and any effort to modify the sheet will give an error message. Hence this will not allow any kind of data entry in the sheet where this setting is activated. If one now wants to remove this protection go to Tools >> Protection >> Unprotect Sheet… the same window will appear. Enter the password that was entered earlier and press OK. This will un-protect the worksheet.
Protecting a cell and a formula!!!
In all the above examples we saw protection of file or sheet, however at times there is a need to protect a few cells or keep open few cells in a particular sheet. Example, while preparing statement of total income one may think of protecting all cells except the tax rate. To do the same first one needs to un-protect the sheet. After un-protecting the worksheet go to the cell that has to be kept open for editing. Tax rate in our example above. Go to Format >> Cells >>Protection (Last Tab). A window as shown below will appear:
By default the Locked option is always checked. Uncheck this option and press OK. Now protect the worksheet as shown earlier. Try editing any cell in the worksheet. All the cells except the cell that was un-locked (tax rate) have been protected. Modification of any cell will not be allowed except tax rate.
In the above window there is one more option for hidden. This is useful to hide the formulas that are written in the cell. This is true in case a complex model is being built in excel and one does not want to share the formula that has been used in a particular cell. In such a case go to Format >> Cell >> Protection and check the Hidden checkbox. Now protect the worksheet with Tools >> Protection >> Protect Worksheet. The formula in the cell that has been formatted will become invisible!!!
How confidential are the excel files that you have on your PC? Ask this question to any of our friend and he will reply saying very confidential. What do all of us use excel for – preparation of statement of total income, preparation of final accounts, maintaining contact list, etc. If all these are so important than how many of us ensure that they stay protected from any invasion or information leakage. How many of us use various protection features provided by excel. In this article we will discuss about various ways and means one can protect a particular excel file or some data from an excel file.
Password protect an excel file:
An excel file can be password protected to open or to modify. To do the same go to File >> Save As >> Options… (Button available on the right side). This will provide you with a small window as shown below.
There are two options available as seen above.
1. Password to open: In case one wants to avoid any unknown person from opening this file, this option should be used. Enter the password in first option and press OK. A window to confirm the password will appear. Repeat the same password and press OK. Now save the file. This will provide a password protection to open this file. Henceforth whenever one wants to open this file, the system will prompt for a password request. Only on entering correct password will the file open.
2. Password to modify: This option should be used for files that are generally referred to. For example in case of a practicing CA there would be a file indicating rate table for NSC. Whenever he is preparing a statement of total income he would need to refer this NSC rate table file. However this file is updated rarely. Hence if one wants to protect it from modification by any unknown person, this option will be helpful. To activate this option enter password in the second box above. A window to confirm the password will appear. Repeat the same password and press OK. Henceforth whenever one wants to open this file, the system will prompt for a password request. However unlike option 1 above, even if one does not know the password he will still be able to open the file but only in the read-only mode i.e. he will not be able to modify the file. Hence modification of this file is possible only by a person who know the password.
3. Read-only recommended: The check-box available at the bottom of the window is also very useful. If one does not want to use password, as there is always a probability that one may forget the password, this option should be tried. On activating this option by checking the box whenever this file is opened system will prompt a message as shown below:
Here if one wants to do some modification in this file than press No else Yes. This is important for files that are not confidential but are referred to very often and whenever we open that file there is a probability that we may make some error and save it. To avoid this, reference file like NSC rate table should be activated with this option.
Password protect a worksheet:
In the above example we saw how to protect a file. However at time we would like to protect a sheet and not the entire file. Example, in a file for tax return, one sheet has statement of total income and another sheet has capital account and balance sheet. There could be a need to protect the sheet for statement of total income and keep all other sheet open. In such a case go to Tools >> Protection >> Unprotect Sheet… Window as shown below will pop-up.
Enter the password or leave it blank and press OK. This will make this sheet un-editable and any effort to modify the sheet will give an error message. Hence this will not allow any kind of data entry in the sheet where this setting is activated. If one now wants to remove this protection go to Tools >> Protection >> Unprotect Sheet… the same window will appear. Enter the password that was entered earlier and press OK. This will un-protect the worksheet.
Protecting a cell and a formula!!!
In all the above examples we saw protection of file or sheet, however at times there is a need to protect a few cells or keep open few cells in a particular sheet. Example, while preparing statement of total income one may think of protecting all cells except the tax rate. To do the same first one needs to un-protect the sheet. After un-protecting the worksheet go to the cell that has to be kept open for editing. Tax rate in our example above. Go to Format >> Cells >>Protection (Last Tab). A window as shown below will appear:
By default the Locked option is always checked. Uncheck this option and press OK. Now protect the worksheet as shown earlier. Try editing any cell in the worksheet. All the cells except the cell that was un-locked (tax rate) have been protected. Modification of any cell will not be allowed except tax rate.
In the above window there is one more option for hidden. This is useful to hide the formulas that are written in the cell. This is true in case a complex model is being built in excel and one does not want to share the formula that has been used in a particular cell. In such a case go to Format >> Cell >> Protection and check the Hidden checkbox. Now protect the worksheet with Tools >> Protection >> Protect Worksheet. The formula in the cell that has been formatted will become invisible!!!
Friday, June 1, 2007
Small tricks
“Where can I meet XL?” asked Bahadur Singh to his office colleague. Seeing an opportunity to take Bahadur Singh for a ride the colleague replied “XL stays in snow clad mountain of Himalaya. All the meetings with XL are held there.” Believing in his colleague’s reply our friend Bahadur Singh started his preparation for visiting Himalaya. He was through with his shopping for winter garments, applied for leave from office, packed his bags and most important prepared a questionnaire listing all that he would like to ask XL.
The journey begins… Even while on the way, he was going through the questionnaire ensuring that he has not missed any and modifying the existing questions. First day in Himalaya, after getting ready he started search for XL. Asked a few of people “Where does XL stay here?” To his surprise everyone laughed at him. Aghast on the thought that his office colleague has fooled him, he remembered of Valmiki and thought of performing a similar act. XL, XL, XL he began chanting for a couple of days and finally an angel appeared. “Yes my little child, what do u want?” asked the angel. Bahadur Singh replied “I want to meet XL. Who are you?” “I am assistant to XL” said the Angel. “Since you have put in so much of effort I cannot deny your wish, but you will be allowed only two minutes meeting with him. This is because at any point of time there is someone somewhere in this world working with XL taking some acquisition decision, some punter playing in the stock market and making his calculation on XL, some accountants closing their accounts on XL, etc. Hence if XL were to be physically present with you, all these people will not be able to carry on their work or in other words XL will hang.” Bahadur Singh though not satisfied with the time replied “That will do”. “We shall meet in the morning tomorrow at 5 o’clock, same place” said the angel and disappeared.
Fortunately for me I was on Himalayan tour. Walking across early morning I saw our friend Bahadur Singh (BA) with his questionnaire. Exactly at 5.00 XL appeared. Given below is the verbatim transcription of discussion that I overheard. Hope this is useful to all of the readers.
BA: Good morning Sir.
XL: You have only two minutes. So please start with your questions.
BA: How do I change the default font of excel? Example, the moment I open a new sheet I should have ‘verdana’ as the font.
XL: Tools >> Options >> General (Tab) >> Standard Font (Select the font from drop down box)
BA: How do I remove all the Gridlines in Excel?
XL: Tools >> Options >> View (Tab) >> Gridlines (Check Box on bottom left of the screen)
BA: How do I decide the standard font size?
XL: Same as above. Look at right side of standard font.
BA: Whenever I open a excel file, I get three sheets by default. How do I change them to five sheets?
XL: Tools >> Options >> General (Tab) >> Sheets in new workbook (Select the number you want)
BA: How do I find a particular word in excel sheet and replace it with a new word? For example replace FY06 with FY07.
XL: Edit >> Replace or Ctrl+H
BA: When I click on File menu I get the latest four files opened. Can I change it to latest 8 files opened?
XL: Tools >> Options >> General (Tab) >> Recently used file list (Select the number you want)
BA: How do I remove unwanted toolbars and bring in some other toolbars?
XL: View >> Toolbars
BA: How do I change the content of a particular cell from horizontal to vertical?
XL: Format >> Cell >> Alignment (Tab) >> Orientation (On the extreme right)
BA: What is a macro and how do I write the same?
XL: Will take two months to explain the same and you have only one more minute.
BA: How do I cancel a particular word? i.e. I want to show that a word is there and then cancelled.
XL: Format >> Cell >> Font (Tab) >> Effects (Bottom left) >> Strikethrough (Check box)
BA: What about designing the excel cells with various colourful borders.
XL: Format >> Cell >> Border (Tab).
BA: How to save an excel file automatically?
XL: Tools >> Add-Ins >> AutoSave (Checkbox)
BA: When my database is very huge thousands of rows and hundreds of columns, what facility do I have to view it easily so that column and row headers are always available on the screen?
XL: Windows >> Freeze Panes. Last 30 seconds.
BA: What is a pivot table and its usage? (I think he was not able to resist himself)
XL: Last 30 seconds and not 30 days dear.
BA: How do I attach a password to excel file so that no one can open it without a password?
XL: File >> Save as >> Options (Button on the right hand side). Good bye…
BA: Last question… How do I insert a comment in excel cell?
XL: Insert >> Comment. Good bye dear…
BA: One final question… How to solve other questions?
XL: Use F1 or come to Himalyas!!!
Well that was end of two minute. In case any of you visit Himalaya please ensure to carry forward this discussion, though it would require a couple of days of meditation!!!
The journey begins… Even while on the way, he was going through the questionnaire ensuring that he has not missed any and modifying the existing questions. First day in Himalaya, after getting ready he started search for XL. Asked a few of people “Where does XL stay here?” To his surprise everyone laughed at him. Aghast on the thought that his office colleague has fooled him, he remembered of Valmiki and thought of performing a similar act. XL, XL, XL he began chanting for a couple of days and finally an angel appeared. “Yes my little child, what do u want?” asked the angel. Bahadur Singh replied “I want to meet XL. Who are you?” “I am assistant to XL” said the Angel. “Since you have put in so much of effort I cannot deny your wish, but you will be allowed only two minutes meeting with him. This is because at any point of time there is someone somewhere in this world working with XL taking some acquisition decision, some punter playing in the stock market and making his calculation on XL, some accountants closing their accounts on XL, etc. Hence if XL were to be physically present with you, all these people will not be able to carry on their work or in other words XL will hang.” Bahadur Singh though not satisfied with the time replied “That will do”. “We shall meet in the morning tomorrow at 5 o’clock, same place” said the angel and disappeared.
Fortunately for me I was on Himalayan tour. Walking across early morning I saw our friend Bahadur Singh (BA) with his questionnaire. Exactly at 5.00 XL appeared. Given below is the verbatim transcription of discussion that I overheard. Hope this is useful to all of the readers.
BA: Good morning Sir.
XL: You have only two minutes. So please start with your questions.
BA: How do I change the default font of excel? Example, the moment I open a new sheet I should have ‘verdana’ as the font.
XL: Tools >> Options >> General (Tab) >> Standard Font (Select the font from drop down box)
BA: How do I remove all the Gridlines in Excel?
XL: Tools >> Options >> View (Tab) >> Gridlines (Check Box on bottom left of the screen)
BA: How do I decide the standard font size?
XL: Same as above. Look at right side of standard font.
BA: Whenever I open a excel file, I get three sheets by default. How do I change them to five sheets?
XL: Tools >> Options >> General (Tab) >> Sheets in new workbook (Select the number you want)
BA: How do I find a particular word in excel sheet and replace it with a new word? For example replace FY06 with FY07.
XL: Edit >> Replace or Ctrl+H
BA: When I click on File menu I get the latest four files opened. Can I change it to latest 8 files opened?
XL: Tools >> Options >> General (Tab) >> Recently used file list (Select the number you want)
BA: How do I remove unwanted toolbars and bring in some other toolbars?
XL: View >> Toolbars
BA: How do I change the content of a particular cell from horizontal to vertical?
XL: Format >> Cell >> Alignment (Tab) >> Orientation (On the extreme right)
BA: What is a macro and how do I write the same?
XL: Will take two months to explain the same and you have only one more minute.
BA: How do I cancel a particular word? i.e. I want to show that a word is there and then cancelled.
XL: Format >> Cell >> Font (Tab) >> Effects (Bottom left) >> Strikethrough (Check box)
BA: What about designing the excel cells with various colourful borders.
XL: Format >> Cell >> Border (Tab).
BA: How to save an excel file automatically?
XL: Tools >> Add-Ins >> AutoSave (Checkbox)
BA: When my database is very huge thousands of rows and hundreds of columns, what facility do I have to view it easily so that column and row headers are always available on the screen?
XL: Windows >> Freeze Panes. Last 30 seconds.
BA: What is a pivot table and its usage? (I think he was not able to resist himself)
XL: Last 30 seconds and not 30 days dear.
BA: How do I attach a password to excel file so that no one can open it without a password?
XL: File >> Save as >> Options (Button on the right hand side). Good bye…
BA: Last question… How do I insert a comment in excel cell?
XL: Insert >> Comment. Good bye dear…
BA: One final question… How to solve other questions?
XL: Use F1 or come to Himalyas!!!
Well that was end of two minute. In case any of you visit Himalaya please ensure to carry forward this discussion, though it would require a couple of days of meditation!!!
Tuesday, May 1, 2007
Filter option
Hi friends, hope calculation of formulas are not going incorrect now after setting the ‘precision as displayed’ indicator, lookups are in place for starting your audit assignments and also searching of internet has become easier than before. Our friend Bahadur Singh is now in a new dilemma, how to convert data to information?
He downloaded a sales register file from an accounting package / ERP that has huge data with some header. The data contains posting date, invoice number, customer name, amount, narration. He feels that this data is very difficult to analyse. He wants to perform the following functions but is not sure how to do the same:
1. Select top 10 invoices by amount.
2. Select invoices with customer name beginning with alphabet ‘A’.
3. Select invoices with narration containing words ‘cash’.
4. Select invoices for amount more than Rs. 1 Lac.
There are many more such questions that he wants to answer but the only option he knows is going through each and every record and identifying the above needs. In case the data set is for few records approx 20 in numbers one may go through each record and select these records but what if the number of records ranges in few thousands.
To satisfy the above needs excel has a auto filter function that enables a user to answer such queries easily.
Let us see this function in detail, its features and usage.
To use the filter function select the whole range of database (though this is not mandatory it is recommended as in case the database has a blank row or column than it may not consider the entire database if it is not selected) and then go to
Data >> Filter >> AutoFilter. This will bring drop down boxes in all the cells of header. The screen short gives a view of how the database would now look.
Once we have the drop down boxes on the header of data converting this data to information and replying to various questions is just a matter of few seconds. Let us now see the usage of this option:
1. Displaying top/bottom N number of records: In the drop down menu select option ‘(Top 10…)’. This will give a selection option for Top/Bottom and number of records needed. Select OK to get only the required records. All the other records are automatically hidden. Hence one can always use this option to select Top 10 records while auditing, doing Pareto analysis, etc.
2. Displaying a particular record: When one is in search of a particular record than it can be directly selected from the drop down menu. Example, in the above database if one is searching for invoices raised to customer name ‘Rahul Patel’ then by selecting the same in the drop down menu of ‘customer name’ all the records with customer name as ‘Rahul Patel’ will be available.
3. Displaying the records with particular field being blank / non-blank: In case any of the column of database has blank in particular records selection of blank or non-blank records is made easy by just selecting the same in drop down menu. In any drop down menu the last two options are ‘(Blanks)’ and ‘(NonBlanks)’ respectively.
4. Custom Selection: The custom option of filter helps replying answers like records greater than or less than a particular amount, beginning or ending with a particular alphabet, not beginning or not ending with a particular alphabet, etc. Selecting the ‘(Custom…)’ option in the drop down menu can do this. To list down the custom options available:
i) equals
ii) does not equal
iii) is greater than
iv) is greater than or equal to
v) is less than
vi) is less than or equal to
vii) begins with
viii) does not begin with
ix) ends with
x) does not end with
xi) contains
xii) does not contain
Selection of the above options based on the needs can help us convert data to information in less than few seconds. The same can answer many analytical questions. In addition one can also use ‘And’ “Or” option to select two options of the above.
To come out of the filter mode, one may select ‘(All)’ from the drop down menu and in case one does not need filter at all than the drop down boxes can be removed by once again selecting:
Data >> Filter >> AutoFilter. This will remove the drop down boxes and the entire data will now be visible.
Important points while using of Auto Filter option:
Using of sum function on the visible records: Once you select a filter option based on the requirement and than try to use the sum function on the visible records, it will always give incorrect results. This is because in filter mode all the records that do not meet your conditions are hidden. Usage of sum function on the visible records does not ignore those records that are hidden and hence result of sum function includes all the records in that range whether hidden or not. In such a scenario one has to use the subtotal function. The syntax has to be =subtotal(9,range).
In this syntax subtotal ensures that only visible cells are considered, 9 would suggest that we need to use addition of visible cells and range would be the selection of cells that the user wants to be considered.
Bahadur Singh is happy learning about this function. It has enabled him answer all the questions and eased analysis of any data irrespective of the size.
He downloaded a sales register file from an accounting package / ERP that has huge data with some header. The data contains posting date, invoice number, customer name, amount, narration. He feels that this data is very difficult to analyse. He wants to perform the following functions but is not sure how to do the same:
1. Select top 10 invoices by amount.
2. Select invoices with customer name beginning with alphabet ‘A’.
3. Select invoices with narration containing words ‘cash’.
4. Select invoices for amount more than Rs. 1 Lac.
There are many more such questions that he wants to answer but the only option he knows is going through each and every record and identifying the above needs. In case the data set is for few records approx 20 in numbers one may go through each record and select these records but what if the number of records ranges in few thousands.
To satisfy the above needs excel has a auto filter function that enables a user to answer such queries easily.
Let us see this function in detail, its features and usage.
To use the filter function select the whole range of database (though this is not mandatory it is recommended as in case the database has a blank row or column than it may not consider the entire database if it is not selected) and then go to
Data >> Filter >> AutoFilter. This will bring drop down boxes in all the cells of header. The screen short gives a view of how the database would now look.
Once we have the drop down boxes on the header of data converting this data to information and replying to various questions is just a matter of few seconds. Let us now see the usage of this option:
1. Displaying top/bottom N number of records: In the drop down menu select option ‘(Top 10…)’. This will give a selection option for Top/Bottom and number of records needed. Select OK to get only the required records. All the other records are automatically hidden. Hence one can always use this option to select Top 10 records while auditing, doing Pareto analysis, etc.
2. Displaying a particular record: When one is in search of a particular record than it can be directly selected from the drop down menu. Example, in the above database if one is searching for invoices raised to customer name ‘Rahul Patel’ then by selecting the same in the drop down menu of ‘customer name’ all the records with customer name as ‘Rahul Patel’ will be available.
3. Displaying the records with particular field being blank / non-blank: In case any of the column of database has blank in particular records selection of blank or non-blank records is made easy by just selecting the same in drop down menu. In any drop down menu the last two options are ‘(Blanks)’ and ‘(NonBlanks)’ respectively.
4. Custom Selection: The custom option of filter helps replying answers like records greater than or less than a particular amount, beginning or ending with a particular alphabet, not beginning or not ending with a particular alphabet, etc. Selecting the ‘(Custom…)’ option in the drop down menu can do this. To list down the custom options available:
i) equals
ii) does not equal
iii) is greater than
iv) is greater than or equal to
v) is less than
vi) is less than or equal to
vii) begins with
viii) does not begin with
ix) ends with
x) does not end with
xi) contains
xii) does not contain
Selection of the above options based on the needs can help us convert data to information in less than few seconds. The same can answer many analytical questions. In addition one can also use ‘And’ “Or” option to select two options of the above.
To come out of the filter mode, one may select ‘(All)’ from the drop down menu and in case one does not need filter at all than the drop down boxes can be removed by once again selecting:
Data >> Filter >> AutoFilter. This will remove the drop down boxes and the entire data will now be visible.
Important points while using of Auto Filter option:
Using of sum function on the visible records: Once you select a filter option based on the requirement and than try to use the sum function on the visible records, it will always give incorrect results. This is because in filter mode all the records that do not meet your conditions are hidden. Usage of sum function on the visible records does not ignore those records that are hidden and hence result of sum function includes all the records in that range whether hidden or not. In such a scenario one has to use the subtotal function. The syntax has to be =subtotal(9,range).
In this syntax subtotal ensures that only visible cells are considered, 9 would suggest that we need to use addition of visible cells and range would be the selection of cells that the user wants to be considered.
Bahadur Singh is happy learning about this function. It has enabled him answer all the questions and eased analysis of any data irrespective of the size.
Sunday, April 1, 2007
How to use search engine
Search Engine? Many of us would know this term and rightly so as a place where one can search the www i.e. world-wide-web.
All of us would be using some search engine in a normal working day. From my experience I find it difficult to find a single day in office without going to the plain white screen of google. We are so used to the method of search that we never think about any possibilities of optimising the search results that we do. Today we discuss ways and means to improve our search results in google.
Search Toolbar
Why type google.com to start your search. Set google.com as home page. This would mean that whenever you click the Internet explorer (IE) it begins with google. To do this right click the IE icon and go to properties. In the general tab set the home page.
Why set google as home page also, it takes up a few more seconds to start and slows the speed. Head to toolbar.google.com to download the google toolbar. Once you have downloaded the toolbar whenever you start the Internet the toolbar would be present and you can just type the search term and see the results. The toolbar has other utilities also like blocking the pop-ups, etc.
Quotes
Use of “ ” quotes gives the most relevant results and the desired results appear as high up in the list as possible. Example, if you want to know who was ICC cricketer of the year in 2005 instead of just typing the same, try “ICC cricketer of the year” followed by “2005”. Check the results!!!
Excluding unwanted results
Use of – minus sign excludes certain words from your search. Example if you want to search the details of 8.3 processor, typing “8.3 processor” would probably give a lot of results that speak about other processors like 5.3, 6.3 and 7.3. Hence the search term should be “8.3 processor –5.3 –6.3 –7.3”.
Forcing some keywords
Whenever the search term is a bit long you will sometime find that there are pages which don’t contain one or more of your search terms at all. Here you would like to force a certain keyword or keyphrase to be included in the search, use the + plus sign to do the same. Example, typing in “favourite example hardware sata mouse” will bring up some results without one or more of those words. If you need the word “example” (which is a common word, so google might leave it out) type “favourite +example sata hardware mouse”
Personalised Search
To make the search results more and more relevant over time try using google search only after signing in, the link is at the top right corner. You need to use your gmail account username and password. Once u sign in and start your search google remembers what you searched for and where all have you clicked and thereby give you more personalised and accurate results. Some of us may not want to share this with google but some of us are not bothered if it improves the search quality. That’s left to individual choices… this tip is for the people who fall in later category.
Definitions
What about using google as a online dictionary. Do you want to know the meaning of a particular word say imagine, type define:imagine and see the results.
Searching within sites
Many sites have google search on their home page. However this forces us to go to this site for search. But this can be avoided by typing the name of the site in the search term. Example, you want to get details about AS 11 on our institute’s site then the search term “AS 11 site:www.icai.org” would work wonders.
Search on file types
If you are searching for a document on the web than you can also specify the file type to be used for the search. Hence if you are searching for a pdf document for derivative trading than try the search term “Derivative filetype:pdf”. U can also try for other file types like xls, doc, mp3, etc.
Search the URL (link)
Instead of searching for the content of a site if you are in search of a particular word in URL then try the inurl option. Hence a search for “inurl:cvoca” would search for all the urls having cvoca word in the same.
A combination of above tips can also be used to further filter the search results.
All of us would be using some search engine in a normal working day. From my experience I find it difficult to find a single day in office without going to the plain white screen of google. We are so used to the method of search that we never think about any possibilities of optimising the search results that we do. Today we discuss ways and means to improve our search results in google.
Search Toolbar
Why type google.com to start your search. Set google.com as home page. This would mean that whenever you click the Internet explorer (IE) it begins with google. To do this right click the IE icon and go to properties. In the general tab set the home page.
Why set google as home page also, it takes up a few more seconds to start and slows the speed. Head to toolbar.google.com to download the google toolbar. Once you have downloaded the toolbar whenever you start the Internet the toolbar would be present and you can just type the search term and see the results. The toolbar has other utilities also like blocking the pop-ups, etc.
Quotes
Use of “ ” quotes gives the most relevant results and the desired results appear as high up in the list as possible. Example, if you want to know who was ICC cricketer of the year in 2005 instead of just typing the same, try “ICC cricketer of the year” followed by “2005”. Check the results!!!
Excluding unwanted results
Use of – minus sign excludes certain words from your search. Example if you want to search the details of 8.3 processor, typing “8.3 processor” would probably give a lot of results that speak about other processors like 5.3, 6.3 and 7.3. Hence the search term should be “8.3 processor –5.3 –6.3 –7.3”.
Forcing some keywords
Whenever the search term is a bit long you will sometime find that there are pages which don’t contain one or more of your search terms at all. Here you would like to force a certain keyword or keyphrase to be included in the search, use the + plus sign to do the same. Example, typing in “favourite example hardware sata mouse” will bring up some results without one or more of those words. If you need the word “example” (which is a common word, so google might leave it out) type “favourite +example sata hardware mouse”
Personalised Search
To make the search results more and more relevant over time try using google search only after signing in, the link is at the top right corner. You need to use your gmail account username and password. Once u sign in and start your search google remembers what you searched for and where all have you clicked and thereby give you more personalised and accurate results. Some of us may not want to share this with google but some of us are not bothered if it improves the search quality. That’s left to individual choices… this tip is for the people who fall in later category.
Definitions
What about using google as a online dictionary. Do you want to know the meaning of a particular word say imagine, type define:imagine and see the results.
Searching within sites
Many sites have google search on their home page. However this forces us to go to this site for search. But this can be avoided by typing the name of the site in the search term. Example, you want to get details about AS 11 on our institute’s site then the search term “AS 11 site:www.icai.org” would work wonders.
Search on file types
If you are searching for a document on the web than you can also specify the file type to be used for the search. Hence if you are searching for a pdf document for derivative trading than try the search term “Derivative filetype:pdf”. U can also try for other file types like xls, doc, mp3, etc.
Search the URL (link)
Instead of searching for the content of a site if you are in search of a particular word in URL then try the inurl option. Hence a search for “inurl:cvoca” would search for all the urls having cvoca word in the same.
A combination of above tips can also be used to further filter the search results.
Thursday, March 1, 2007
Lookup functions
Dear Friends
Hope that casting errors have now become a history. In this article we would dwell upon one of the most powerful function of excel namely LOOKUP.
This time around Bahadur Singh was given a due diligence assignment on the financials of a company for a particular year. To begin with, he met the client and asked for the closing and opening trial balance. The company had approximately 9000 account codes and hence the print out given by the client was 150 pages each for closing and opening trial balance. Seeing this our friend was shocked and he had no idea as to how would he check if the opening balance for each and every account code matches with the closing balance. He thought for a day and than came to us to seek our opinion as to if there is some better way to do the same.
Traditionally there are two processes, which we follow for such an activity:
1. Send a couple of staff to the client place and then do a calling comparing i.e. one person would call the account code and amount from a particular trial balance and the other person would check the same in other trial balance.
2. In case the trail balance is as huge as described in our example above than we carry out a test check basis based on some samples.
I hope that many of you would agree that these are the two methods followed by us when it comes to checking of balances before starting our audit or due diligence assignments. Let us see what are the faults or risks in carrying the above two methods:
1. The first method as described and in practice is very clumsy. We can follow the same in case the trial balance is small, but it is not practical to follow while we are engaged in an assignment with big companies. Also the calling and comparing method is not a foolproof method. There is always a probability of human error due to the mundane process involved. Also what we do after this process is over? We file these print outs in our audit file. At this point of time let us ask a question to ourselves, is this appropriate documentation? I doubt. The process itself is full of errors and we document that process to ensure that we had documented it as per the required standards. Hence it would be apt to say that the first method should not be followed in case of big companies and it may be followed in case of small sized companies but still a better solution ought to be there.
2. The second method that many of us follow is even more dangerous. Sampling should be done in case a particular activity is impossible to be carried out for the entire population. Like in case of vouching for expenses we rely on sampling. It may be appropriate, as vouching each and every expense item is impossible. But application of sampling in case of matching opening and closing balances is not recommended.
In such a case what are we left with. Do we have any better solution? This remembers me of a maxim ‘ignoratia law non excusat’ that means ignorance of law cannot be an excuse to justify a sub standard quality of work. Similarly I see a maxim in future, stating ‘ignoratia tech non excusat’ that would mean ignorance of technology couldn’t be an excuse to justify a sub standard quality of work. If we are using a particular technology (in our case excel) and just because we are not aware of its functions it would not be an excuse to justify a sub standard quality of work. In these kind of cases we should make use of LOOKUP function available in excel. To make it more generic whenever we are faced with a situation where we want to retrieve values from a reference file and match these with the data contained in the transaction file we should use LOOKUP function.
Below we detail the function in detail, explaining Bahadur Singh how he can apply the same in his case and get the balances match in less than one minute.
1. Ask the client to give a soft copy of both opening and closing trial balance. Given below is an extract of both the trial balance:
File 1
File 2
Col A
Col B
Col C
Col D
Col A
Col B
Col C
Col D
Trial Balance 2004-05
Trial Balance 2005-06
Open
Close
Open
Close
Account
Code
Account
Description
Amt
Amt
Account
Code
Account
Description
Amt
Amt
21121
Mr. ABC
2000
0
21121
Mr. ABC
0
0
21125
CVOCA Assoc
5000
1000
21125
CVOCA Assoc
1000
200
21238
Hasmukh Dedhia
1500
2000
21238
Hasmukh Dedhia
2000
0
21251
Chirag Savla
1000
50
21251
Chirag Savla
55
5000
2. File 1 is the trial balance for 2004-05 i.e. for previous year and File 2 is for 2005-06 i.e. for current year. Hence File 1 becomes our closing trial balance and File 2 is our opening trial balance. Now we would like to match column D of File 1 with column C of File 2.
3. To achieve our objective we would use the VLOOKUP function. Go to File 2 and in column E against the first account code i.e. 21121 (assume it is in row 6) write the following formula =VLOOKUP(A6, ‘File 1’$A$6:$D$9,4,false). Copy this formula in all the rows below. The output would be as follows:
Col A
Col B
Col C
Col D
Col E
Trial Balance 2005-06
Open
Close
Account
Code
Account
Description
Amt
Amt
21121
Mr. ABC
0
0
0
21125
CVOCA Assoc
1000
200
1000
21238
Hasmukh Dedhia
2000
0
2000
21251
Chirag Savla
55
5000
50
4. Let us first understand the formula that we have written in step 3 above. We said
· VLOOKUP – meaning vertical lookup
· A6 – The value available in cell A6. This is the account code for which we want to get a particular value.
· ‘File 1’$A$6:$D$9 – This is the range of cells and the file from which that value needs to be derived. In our case the source value was in file 1 in the range given.
· 4 – This refers to the column number of the source file from which the value needs to be derived. In our case we needed to pick value from column D that was the fourth column from account code.
· False – Here we command the system to find an exact match.
5. Now in column F of file 1 we give the command column C – column E and get to a conclusion that wherever the amount is not equal to zero the balances are not matching.
In case one finds it difficult to type the VLOOKUP command one can always use the function wizard from Insert >> Function.
What are the benefits to use this function:
· An activity that would otherwise need a few man-days can be completed in less than a minute.
· It is a foolproof method to replace any kind of calling and comparing of figures in excel that we carry in our day to day activity.
· On a lighter note it saves a huge lot of paper and also the soft copies would be a perfect documentation.
Further you would have noticed that I have used the word LOOKUP at times and VLOOKUP at times. Here LOOKUP can be VLOOKUP or HLOOKUP. VLOOKUP stands for vertical lookup and HLOOKUP for horizontal lookup. In our case above since that data arrangement was in a vertical format we used VLOOKUP. In cases where the data arrangement is in horizontal format we should use HLOOKUP. I have not specifically covered the explanation to HLOOKUP as it is in line with VLOOKUP.
Well, no more calling comparing henceforth. Bahadur Singh was the happiest person as we would have saved at least a couple of weeks for him. We shall continue with more excel features in the forthcoming issues.
Hope that casting errors have now become a history. In this article we would dwell upon one of the most powerful function of excel namely LOOKUP.
This time around Bahadur Singh was given a due diligence assignment on the financials of a company for a particular year. To begin with, he met the client and asked for the closing and opening trial balance. The company had approximately 9000 account codes and hence the print out given by the client was 150 pages each for closing and opening trial balance. Seeing this our friend was shocked and he had no idea as to how would he check if the opening balance for each and every account code matches with the closing balance. He thought for a day and than came to us to seek our opinion as to if there is some better way to do the same.
Traditionally there are two processes, which we follow for such an activity:
1. Send a couple of staff to the client place and then do a calling comparing i.e. one person would call the account code and amount from a particular trial balance and the other person would check the same in other trial balance.
2. In case the trail balance is as huge as described in our example above than we carry out a test check basis based on some samples.
I hope that many of you would agree that these are the two methods followed by us when it comes to checking of balances before starting our audit or due diligence assignments. Let us see what are the faults or risks in carrying the above two methods:
1. The first method as described and in practice is very clumsy. We can follow the same in case the trial balance is small, but it is not practical to follow while we are engaged in an assignment with big companies. Also the calling and comparing method is not a foolproof method. There is always a probability of human error due to the mundane process involved. Also what we do after this process is over? We file these print outs in our audit file. At this point of time let us ask a question to ourselves, is this appropriate documentation? I doubt. The process itself is full of errors and we document that process to ensure that we had documented it as per the required standards. Hence it would be apt to say that the first method should not be followed in case of big companies and it may be followed in case of small sized companies but still a better solution ought to be there.
2. The second method that many of us follow is even more dangerous. Sampling should be done in case a particular activity is impossible to be carried out for the entire population. Like in case of vouching for expenses we rely on sampling. It may be appropriate, as vouching each and every expense item is impossible. But application of sampling in case of matching opening and closing balances is not recommended.
In such a case what are we left with. Do we have any better solution? This remembers me of a maxim ‘ignoratia law non excusat’ that means ignorance of law cannot be an excuse to justify a sub standard quality of work. Similarly I see a maxim in future, stating ‘ignoratia tech non excusat’ that would mean ignorance of technology couldn’t be an excuse to justify a sub standard quality of work. If we are using a particular technology (in our case excel) and just because we are not aware of its functions it would not be an excuse to justify a sub standard quality of work. In these kind of cases we should make use of LOOKUP function available in excel. To make it more generic whenever we are faced with a situation where we want to retrieve values from a reference file and match these with the data contained in the transaction file we should use LOOKUP function.
Below we detail the function in detail, explaining Bahadur Singh how he can apply the same in his case and get the balances match in less than one minute.
1. Ask the client to give a soft copy of both opening and closing trial balance. Given below is an extract of both the trial balance:
File 1
File 2
Col A
Col B
Col C
Col D
Col A
Col B
Col C
Col D
Trial Balance 2004-05
Trial Balance 2005-06
Open
Close
Open
Close
Account
Code
Account
Description
Amt
Amt
Account
Code
Account
Description
Amt
Amt
21121
Mr. ABC
2000
0
21121
Mr. ABC
0
0
21125
CVOCA Assoc
5000
1000
21125
CVOCA Assoc
1000
200
21238
Hasmukh Dedhia
1500
2000
21238
Hasmukh Dedhia
2000
0
21251
Chirag Savla
1000
50
21251
Chirag Savla
55
5000
2. File 1 is the trial balance for 2004-05 i.e. for previous year and File 2 is for 2005-06 i.e. for current year. Hence File 1 becomes our closing trial balance and File 2 is our opening trial balance. Now we would like to match column D of File 1 with column C of File 2.
3. To achieve our objective we would use the VLOOKUP function. Go to File 2 and in column E against the first account code i.e. 21121 (assume it is in row 6) write the following formula =VLOOKUP(A6, ‘File 1’$A$6:$D$9,4,false). Copy this formula in all the rows below. The output would be as follows:
Col A
Col B
Col C
Col D
Col E
Trial Balance 2005-06
Open
Close
Account
Code
Account
Description
Amt
Amt
21121
Mr. ABC
0
0
0
21125
CVOCA Assoc
1000
200
1000
21238
Hasmukh Dedhia
2000
0
2000
21251
Chirag Savla
55
5000
50
4. Let us first understand the formula that we have written in step 3 above. We said
· VLOOKUP – meaning vertical lookup
· A6 – The value available in cell A6. This is the account code for which we want to get a particular value.
· ‘File 1’$A$6:$D$9 – This is the range of cells and the file from which that value needs to be derived. In our case the source value was in file 1 in the range given.
· 4 – This refers to the column number of the source file from which the value needs to be derived. In our case we needed to pick value from column D that was the fourth column from account code.
· False – Here we command the system to find an exact match.
5. Now in column F of file 1 we give the command column C – column E and get to a conclusion that wherever the amount is not equal to zero the balances are not matching.
In case one finds it difficult to type the VLOOKUP command one can always use the function wizard from Insert >> Function.
What are the benefits to use this function:
· An activity that would otherwise need a few man-days can be completed in less than a minute.
· It is a foolproof method to replace any kind of calling and comparing of figures in excel that we carry in our day to day activity.
· On a lighter note it saves a huge lot of paper and also the soft copies would be a perfect documentation.
Further you would have noticed that I have used the word LOOKUP at times and VLOOKUP at times. Here LOOKUP can be VLOOKUP or HLOOKUP. VLOOKUP stands for vertical lookup and HLOOKUP for horizontal lookup. In our case above since that data arrangement was in a vertical format we used VLOOKUP. In cases where the data arrangement is in horizontal format we should use HLOOKUP. I have not specifically covered the explanation to HLOOKUP as it is in line with VLOOKUP.
Well, no more calling comparing henceforth. Bahadur Singh was the happiest person as we would have saved at least a couple of weeks for him. We shall continue with more excel features in the forthcoming issues.
Thursday, February 1, 2007
Round off functions
Dear Friends
Hope all of you remember our friend Bahadur Singh and the problems he faced in his routine day to day activities. This time around our friend was asked to do an analysis on the annual reports of various companies for the year 2005-06. On collecting the annual reports of various companies he was surprised when he saw casting (mathematical totaling) errors on the face of the report. He was not sure if these errors could have been avoided and hence he came to us for help. Let us first see the extract of the errors that he showed us:
Pictures to be included
(Please note that the tables shown are actual extracts from annual report for listed companies for the year 2005-06. However I have intentionally not disclosed the source of the same.)
Let us analyse why such errors happen so frequently and do we have a solution for the same.
In excel if we type 3.333333 in one cell and 3.333333 in second cell and then use a sum function for both these cells the result would be 6.666666. Now if we format all these three cells with two decimals format, the output is 3.33+3.33 = 6.67. This is where the casting errors happen. To make the same easy to understand, given below is an excel sheet for the same.
Input Data
Output Data
Formatted with 2 decimals
3.333333
3.333333
3.33
3.333333
3.333333
3.33
=SUM(A3:A4)
6.666666
6.67
Such errors are very common in many of our excel sheets. Further these errors are more prominent when we sign the audited accounts in multiples of thousands, lacs, millions, etc. The reason for such casting errors in excel is that the number that is visible to us (3.33 in above case) is not the same as the number that excel uses for calculation purpose (3.333333 in above case).
Bahadur Singh says that he knows few functions namely ‘round’ and ‘trunc’ in excel by which he claims that he can provide a solution for such problems. Let us analyse these functions one at a time.
1. ROUND function: This would round the number to specified number of digits.
Syntax: ROUND(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUND(A4,2)
5346747.4000000
=ROUND(A4,4)
5346747.3985000
=ROUND(A4,-3)
5347000.0000000
Uses: The round formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. Hence this could also be used in computation of taxable income that is rounded off.
2. ROUNDUP function: A variant of normal ROUND function is ROUNDUP function. This would round the number to specified number of digits in upper direction.
Syntax: ROUNDUP(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUNDUP(A4,2)
5346747.4000000
=ROUNDUP(A4,4)
5346747.3985000
=ROUNDUP(A4,-3)
5347000.0000000
Uses: The round up formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on upper side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the upper side.
3. ROUNDDOWN function: Similar to round up, a variant of normal ROUND function is ROUNDDOWN function. This would round the number to specified number of digits in lower direction.
Syntax: ROUNDDOWN(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUNDDOWN(A4,2)
5346747.3900000
=ROUNDDOWN(A4,4)
5346747.3984000
=ROUNDDOWN(A4,-3)
5346000.0000000
Uses: The round down formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on lower side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the lower side.
4. TRUNC function: This function is used to truncate the additional decimal places or the rightmost digits.
Syntax: TRUNC(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=TRUNC(A4,2)
5346747.3900000
=TRUNC(A4,4)
5346747.3984000
=TRUNC(A4,-3)
5346000.0000000
Uses: This function is useful when instead of rounding off there is a need to truncate the additional digits after the decimal point. In addition to truncation of additional digits after the decimal point this can also be used to represent a particular number in thousands (as shown above), lacs, millions, etc. on the lower side. If one notices carefully the output of this function would be similar as the round down function.
Coming back to our case of casting errors, can we apply any of the above functions for the same? Bahadur Singh says that application of the above function would have avoided such casting errors. Hence the correct formula for adding 3.333333 and 3.333333 would be a combination of rounddown and sum. The same should be =ROUNDDOWN(SUM (A3:A4),2). In this case the output would have been 6.66 as required instead of 6.67.
How many of us use such functions to ensure that there are no rounding off errors in final accounts? But even after using such functions how many of us can be 100% sure that there would be no casting errors? Let us see what are the disadvantages of using such functions for final accounts:
ü The casting errors could be either ways, i.e. on lower side or on upper side hence we are not sure of whether to use plain round function or its variants. Hence this makes the formula very rigid.
ü The formula also becomes very cumbersome. It is impractical to enter such formula for a schedule like fixed assets where there has to be a sum function horizontally and vertically.
After reading this Bahadur Singh asks us, is there a way out? Discussed below is a setting in excel that will help all of you to resolve the casting errors completely without any rounding or truncating formulae.
Go to the following path:
Tools à Option à Calculation Tab.
In the lower left corner there are workbook options. In these options there is a setting for ‘Precision as displayed’ (As shown below). By default this box is blank. Just tick this box and press OK.
Picture to be included
There is a message that reads ‘Data will permanently lose accuracy’. Say OK and then find for any casting error in this file. You can be 100% sure that you will find none.
Important points to be noted for this setting:
1. This setting is applicable to the entire file and hence activation of this setting once will be applicable to all the sheets in the file.
2. The information message ‘Data will permanently lose accuracy’ is important to understand. It means that now the number will be read in the manner it is displayed. Hence number 3.333333 if displayed for 2 decimals will be read as 3.33 only and this will be permanent.
3. The setting once activated cannot be undone i.e. undo function is not applicable here. However one may decide to close the file without saving to bring back the original data.
Bahadur Singh was surprised that he was ignorant about such a simple setting available in excel to solve all such problems. He is sure that he would not make casting errors as done by many of us.
Hope that the fixed asset schedule would not be as difficult as it has been. Keep a look at this space, as there are more such tricks to come that would make your life much easier.
Hope all of you remember our friend Bahadur Singh and the problems he faced in his routine day to day activities. This time around our friend was asked to do an analysis on the annual reports of various companies for the year 2005-06. On collecting the annual reports of various companies he was surprised when he saw casting (mathematical totaling) errors on the face of the report. He was not sure if these errors could have been avoided and hence he came to us for help. Let us first see the extract of the errors that he showed us:
Pictures to be included
(Please note that the tables shown are actual extracts from annual report for listed companies for the year 2005-06. However I have intentionally not disclosed the source of the same.)
Let us analyse why such errors happen so frequently and do we have a solution for the same.
In excel if we type 3.333333 in one cell and 3.333333 in second cell and then use a sum function for both these cells the result would be 6.666666. Now if we format all these three cells with two decimals format, the output is 3.33+3.33 = 6.67. This is where the casting errors happen. To make the same easy to understand, given below is an excel sheet for the same.
Input Data
Output Data
Formatted with 2 decimals
3.333333
3.333333
3.33
3.333333
3.333333
3.33
=SUM(A3:A4)
6.666666
6.67
Such errors are very common in many of our excel sheets. Further these errors are more prominent when we sign the audited accounts in multiples of thousands, lacs, millions, etc. The reason for such casting errors in excel is that the number that is visible to us (3.33 in above case) is not the same as the number that excel uses for calculation purpose (3.333333 in above case).
Bahadur Singh says that he knows few functions namely ‘round’ and ‘trunc’ in excel by which he claims that he can provide a solution for such problems. Let us analyse these functions one at a time.
1. ROUND function: This would round the number to specified number of digits.
Syntax: ROUND(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUND(A4,2)
5346747.4000000
=ROUND(A4,4)
5346747.3985000
=ROUND(A4,-3)
5347000.0000000
Uses: The round formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. Hence this could also be used in computation of taxable income that is rounded off.
2. ROUNDUP function: A variant of normal ROUND function is ROUNDUP function. This would round the number to specified number of digits in upper direction.
Syntax: ROUNDUP(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUNDUP(A4,2)
5346747.4000000
=ROUNDUP(A4,4)
5346747.3985000
=ROUNDUP(A4,-3)
5347000.0000000
Uses: The round up formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on upper side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the upper side.
3. ROUNDDOWN function: Similar to round up, a variant of normal ROUND function is ROUNDDOWN function. This would round the number to specified number of digits in lower direction.
Syntax: ROUNDDOWN(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=ROUNDDOWN(A4,2)
5346747.3900000
=ROUNDDOWN(A4,4)
5346747.3984000
=ROUNDDOWN(A4,-3)
5346000.0000000
Uses: The round down formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on lower side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the lower side.
4. TRUNC function: This function is used to truncate the additional decimal places or the rightmost digits.
Syntax: TRUNC(Number,Digits)
Example:
Original Number
Function
Output
5346747.3984632
=TRUNC(A4,2)
5346747.3900000
=TRUNC(A4,4)
5346747.3984000
=TRUNC(A4,-3)
5346000.0000000
Uses: This function is useful when instead of rounding off there is a need to truncate the additional digits after the decimal point. In addition to truncation of additional digits after the decimal point this can also be used to represent a particular number in thousands (as shown above), lacs, millions, etc. on the lower side. If one notices carefully the output of this function would be similar as the round down function.
Coming back to our case of casting errors, can we apply any of the above functions for the same? Bahadur Singh says that application of the above function would have avoided such casting errors. Hence the correct formula for adding 3.333333 and 3.333333 would be a combination of rounddown and sum. The same should be =ROUNDDOWN(SUM (A3:A4),2). In this case the output would have been 6.66 as required instead of 6.67.
How many of us use such functions to ensure that there are no rounding off errors in final accounts? But even after using such functions how many of us can be 100% sure that there would be no casting errors? Let us see what are the disadvantages of using such functions for final accounts:
ü The casting errors could be either ways, i.e. on lower side or on upper side hence we are not sure of whether to use plain round function or its variants. Hence this makes the formula very rigid.
ü The formula also becomes very cumbersome. It is impractical to enter such formula for a schedule like fixed assets where there has to be a sum function horizontally and vertically.
After reading this Bahadur Singh asks us, is there a way out? Discussed below is a setting in excel that will help all of you to resolve the casting errors completely without any rounding or truncating formulae.
Go to the following path:
Tools à Option à Calculation Tab.
In the lower left corner there are workbook options. In these options there is a setting for ‘Precision as displayed’ (As shown below). By default this box is blank. Just tick this box and press OK.
Picture to be included
There is a message that reads ‘Data will permanently lose accuracy’. Say OK and then find for any casting error in this file. You can be 100% sure that you will find none.
Important points to be noted for this setting:
1. This setting is applicable to the entire file and hence activation of this setting once will be applicable to all the sheets in the file.
2. The information message ‘Data will permanently lose accuracy’ is important to understand. It means that now the number will be read in the manner it is displayed. Hence number 3.333333 if displayed for 2 decimals will be read as 3.33 only and this will be permanent.
3. The setting once activated cannot be undone i.e. undo function is not applicable here. However one may decide to close the file without saving to bring back the original data.
Bahadur Singh was surprised that he was ignorant about such a simple setting available in excel to solve all such problems. He is sure that he would not make casting errors as done by many of us.
Hope that the fixed asset schedule would not be as difficult as it has been. Keep a look at this space, as there are more such tricks to come that would make your life much easier.
Monday, January 1, 2007
Blank the ppt
Dear Friends
We will embark on a journey to make our daily work more efficient and effective by means of simple tricks and tips that could be implemented on day to day basis. I am grateful to our association for providing me this opportunity to share with all of you, my knowledge on day to day office applications such as excel, power-point, word, etc. This is the first of the articles that would follow, which will discuss various functionalities in these applications that would be useful to all of us. All our discussions would revolve around a person called ‘Bahadur Singh’.
Let me give you a brief introduction of Bahadur Singh. Mr. Bahadur Singh is a management graduate with specialisation in finance. He works as an analyst in one of the leading research firm. Physically short and stout he is a sweet person with a great sense of humour. While he carries his day to day activities, he comes across various problems, which we could have also encountered in our day to day life. Whenever he has some problem he comes to us for a solution. In all my next articles we will study the various problems that he faces and how the same are resolved using simple functions available with us.
In this first article we would discuss a very small tip in power-point that many of us would not be aware of.
Bahadur Singh has been given an assignment to find a FMCG company that could be taken over. Our dear friend has spent all night working with huge data and is ready for a presentation to the top management. In the midst of the presentation there is a slide that involves some discussion among the people in the boardroom and some calculations that he would like to do on the board with the help of marker pens. While the discussion is going on our friend has to do something to hide the presentation being focused on the board or what we can say he wants the screen to go black. What does he do?
ü Covers the lens of proxima with a paper or a book or a lens cover.
ü Switches off his computer.
ü Press Function + F5 keys together or switch of the proxima.
The problem that Bahadur Singh faces is not unique. All of us have this problem whenever we are giving any presentation to a close group of people or may be in a seminar. Our friend knows the above three solutions to his problem but are these solutions the best one or can there be a better solution. Bahadur Singh comes to us and asks the same question. Let us see the disadvantage of each of the above solution:
ü The process of covering the lens of proxima is very common. However the book that is used to cover the same may fall or the paper may fly. Also there is a possibility that there is no place to keep the object. All the proximas have a lens cover, which could also be used. But even this may not be possible as the proxima at times may be placed at a height that u cannot reach or even after covering it with the lens cover, the presentation screen does not go perfect black as there is still some dim light coming.
ü Switching off the computer is something that a few people do but all of us agree that this is not an elegant way for the same. The moment you want to start the presentation again you would need to start the computer which will be a time consuming process.
ü Pressing of Function + F5 is the best among the three solutions above. Technically, this would break the connection between the computer and proxima. However, the computer monitor still remains on and at times that might have an irk some people in the board-room. Also when one wants to start the presentation again, he presses Function + F5 again, to enable the connection which might take time. Similarly you would agree that switching off the proxima would also not be the most effective solution and may damage the proxima, if done frequently.
As we said earlier, can we provide some better solution to Bahadur Singh, so that he can make the presentation screen and the monitor go blank to enable some discussion
Well here it goes, while the power-point slide show is still on, just press the alphabet ‘b’ on the keyboard. Try this and having discussions during presentations would not be as difficult as it has been. Bahadur Singh went back happily with this solution and was successful in convincing the top management for the proposed takeover.
Hope this helps all of you as it did to our friend. We will see this and much more as days go by. Logging off my first article here.
We will embark on a journey to make our daily work more efficient and effective by means of simple tricks and tips that could be implemented on day to day basis. I am grateful to our association for providing me this opportunity to share with all of you, my knowledge on day to day office applications such as excel, power-point, word, etc. This is the first of the articles that would follow, which will discuss various functionalities in these applications that would be useful to all of us. All our discussions would revolve around a person called ‘Bahadur Singh’.
Let me give you a brief introduction of Bahadur Singh. Mr. Bahadur Singh is a management graduate with specialisation in finance. He works as an analyst in one of the leading research firm. Physically short and stout he is a sweet person with a great sense of humour. While he carries his day to day activities, he comes across various problems, which we could have also encountered in our day to day life. Whenever he has some problem he comes to us for a solution. In all my next articles we will study the various problems that he faces and how the same are resolved using simple functions available with us.
In this first article we would discuss a very small tip in power-point that many of us would not be aware of.
Bahadur Singh has been given an assignment to find a FMCG company that could be taken over. Our dear friend has spent all night working with huge data and is ready for a presentation to the top management. In the midst of the presentation there is a slide that involves some discussion among the people in the boardroom and some calculations that he would like to do on the board with the help of marker pens. While the discussion is going on our friend has to do something to hide the presentation being focused on the board or what we can say he wants the screen to go black. What does he do?
ü Covers the lens of proxima with a paper or a book or a lens cover.
ü Switches off his computer.
ü Press Function + F5 keys together or switch of the proxima.
The problem that Bahadur Singh faces is not unique. All of us have this problem whenever we are giving any presentation to a close group of people or may be in a seminar. Our friend knows the above three solutions to his problem but are these solutions the best one or can there be a better solution. Bahadur Singh comes to us and asks the same question. Let us see the disadvantage of each of the above solution:
ü The process of covering the lens of proxima is very common. However the book that is used to cover the same may fall or the paper may fly. Also there is a possibility that there is no place to keep the object. All the proximas have a lens cover, which could also be used. But even this may not be possible as the proxima at times may be placed at a height that u cannot reach or even after covering it with the lens cover, the presentation screen does not go perfect black as there is still some dim light coming.
ü Switching off the computer is something that a few people do but all of us agree that this is not an elegant way for the same. The moment you want to start the presentation again you would need to start the computer which will be a time consuming process.
ü Pressing of Function + F5 is the best among the three solutions above. Technically, this would break the connection between the computer and proxima. However, the computer monitor still remains on and at times that might have an irk some people in the board-room. Also when one wants to start the presentation again, he presses Function + F5 again, to enable the connection which might take time. Similarly you would agree that switching off the proxima would also not be the most effective solution and may damage the proxima, if done frequently.
As we said earlier, can we provide some better solution to Bahadur Singh, so that he can make the presentation screen and the monitor go blank to enable some discussion
Well here it goes, while the power-point slide show is still on, just press the alphabet ‘b’ on the keyboard. Try this and having discussions during presentations would not be as difficult as it has been. Bahadur Singh went back happily with this solution and was successful in convincing the top management for the proposed takeover.
Hope this helps all of you as it did to our friend. We will see this and much more as days go by. Logging off my first article here.
Subscribe to:
Posts (Atom)
