Amount in words

Amount in words

Microsoft excel, does not by default, offers a function to convert a numerical value into text. However, it allows you to do so using its visual basic editor.

Open the excel workbook in which you intend to use the functionality. Press   Alt+F11  to open the Visual Basic Editor. Click  Insert  >  Module  (a new window will open). Copy and paste the code. Save and close the visual basic editor and then save the excel workbook in Excel macro-enabled format.

Now open the excel workbook and use the function as    =spellnumber(Cell reference)   

 If you are familiar with VB code, you can modify the code according to your own needs. For example, if your local currency is other than dollar i.e “Rupees” with sub unit “Paisas” you can find and replace “Dollars” and “Cents” with your relevant currency units.

Remember that this would work only in the excel workbook in which you have inserted the module.

 To download the VB code referred to in this post and the template files, please visit: https://sites.google.com/site/officetemplatesource/news/untitledpost

Precision as displayed

Precision as displayed

Excel stores numeric data as " Double Precision Floating Point" which is a design requirement but can result inaccuracies in certain exceptional cases.

Consider you have the expression  =103/7 in one cell and =99/6 in the other. Number that excel would be storing as the answer would be  14.7142857142857000...... for first cell and 16.500000000...... for the second cell.

Consider you have selected to display all amounts as number format with no decimal places. The amount would be appearing as 15 and 17 now if you add both cells in another cell, excel would give the answer as 31 which is correct mathematically but not if someone performs casting by simply adding the visible numbers i.e 15+17 = 32. This problem arises frequently while making financial statements where amounts are rounded to thousands or millions.

One solution to this problem is to round the source amounts down to zero decimal places using the round function. The other option is the use of "Precision as displayed" feature. To use it, simply go to Office button >  Excel options > Advanced > When calculating this workbook > Set precision as displayed.

For Excel 2003 and earlier versions, go to Tool > Options > Calculation > workbook options > Precision as displayed.

However there is one problem i.e the changes made by this setting cannot be undone, so preparing a copy of the document may be considered.

Shortcut Key: CTRL + SHIFT + 3 – Convert format to date

Automatically convert a cell to date format using the keys CTRL + SHIFT + 3. Excel saves date values as serial numbers (e.g. 39994 is 30 June 2009, 39993 is 29 June 2009 etc). Sometimes when you enter a date Excel shows the serial number instead of the date format. Furthermore, if you enter a date as 30/06/2009 Excel may also show the date in this format.

My personal preference is for dates to be shown as mm ddd yy (i.e. 30-Jun-09). You can quickly convert a cell to this format (or you default date format) by pressing CTRL + SHIFT + 3.Then using the shortcut it becomes (make sure you select the cell(s) you want change and press CTRL + SHIFT + 3):

Secondary Axis on Charts in Microsoft Office

Secondary Axis on Charts in Microsoft Office

If you are preparing a chart using two data fields that are so different in terms of data (i.e one data in numbers and the other in percentages ) they cannot be displayed on a single scale. For example:

Year        Population           Growth
2005     150,000,000             2.4%
2006     153,900,000             2.6%
2007     158,363,100             2.9%
2008     163,272,356             3.1%
2009     168,170,527             3.0%
2010     172,374,790             2.5%
2011     176,339,410             2.3%

without secondary axis

with secondary axis

To make a chart with secondary axis, first insert a chart using the chart wizard in the "Insert" menu (select a 2D chart).After you are finished you chart should appear as in picture-1 above.

To add secondary axis, right click on any data bar shown in the chart window. Then click "Format Data Series"  >  Series Options  >  Plot Series On  >  Secondary axis.

As a result of above, both data series population and growth %age will appear. Now you can decide which data series to show on primary or secondary axis simply by right using the format data series option as explained above.

You can use two different chart types for both axis for better presentation. In the above example we have used line graph for growth %age. To do that simply select the data bar (red) and go to  "Chart tools"  >  Design  >  Change Chart Type  >  Select any line graph.

Grouping Data in Excel

Grouping Data in Excel

If you have a lot of data in excel in columnar format and you want to arrange data so that only relevant information is available for view, grouping function can be used.

For example, we have data of monthly, quarterly and annual sales to different customers. Head of Sales would be interested in annual sales volume, Sales manager might be interested in quarterly or monthly sales. For that matter we can group the data for different level of detail.

Example data

To apply grouping select number of rows or columns to group and then go to Data > Group > Group

You can apply multiple grouping on same columns like a month can be grouped as a part of a quarter and at the same time it can be grouped as part of the year. After grouping all the levels available are displayed in the upper left portion of the worksheet in the form of 1,2,3......

Below is demonstration of three grouping levels applied on above data.

Level-3

Level-2

Level-1

Highlighting duplicate or unique values

Highlighting duplicate or unique values

If you have the data in excel that contains a mix of duplicate and unique values and you want to highlight either duplicate or unique values from your data.

This can be done using the "conditional formatting" option. Select the data and then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

By default, it highlights duplicate values in the selected range, however, the criteria can be changed to unique values from the dialog box that appears.

Generating Random Numbers

Generating Random Numbers

To generate random number in excel use following function.

=rand()
(This will create random decimal numbers each time the cell is refreshed)

If a specific range is required, the above formula can be amended as:


=rand()*(100-1)
(This will create random +ve decimal numbers between 1 and 100 each time the cell is refreshed. If -ve numbers are required the last part of the expression should be written as (1-100)

If only random integers are required, use the INT functions with the above formula as:

=INT(rand()*(100-1))

Change Case Key

Change Case Key

Press Shift + F3 to toggle between different cases of selected text. For example, when applied on text "microsoft" it gives three options.

Microsoft
MICROSOFT
microsoft


However, this works in  Microsoft Word and Powerpoint only.

Navigation Keys

Ctrl + Home Move to cell A1
Ctrl + End Move to last cell in worksheet
Ctrl + page down Move to previous worksheet
Ctrl + page up Move to next worksheet
Shift + F11 New worksheet
Alt + Tab Switch to next program
Alt + Shift + Tab Switch to next program
Ctrl + F Find
Ctrl + H Find and replace
Ctrl + G Go to a specific cell

Windows Shortcut Keys

Ctrl + C Copy
Ctrl + X Cut
Ctrl + P Paste
Ctrl + Z Undo
Ctrl + Y Redo
Ctrl + A Select All
Ctrl + S Save
Ctrl + O Open
Ctrl + W Close workbook
Ctrl + P Print
Ctrl + B Bold
Ctrl + U Underline
Ctrl + I Italic
Ctrl + F Find