Add Indian rupee symbol in EXCEL / WORD

We going to see How can we use Indian rupee symbol in Ms Excel / Ms Word


Steps to follow


1. Download the font Rupee.ttf  from following link: http://goo.gl/9Ta7e or the new version Rupee_Foradian.ttf in Google

2. Just copy the font and paste it in "Fonts" folder in control panel

Then open the Excel and change the fond to "Rupee". Press ` key to get rupee symbol. (Key before number1)

How to remove hyperlink in excel in one shot?

May be you have using this macro already.

Option A


Select the Hyper-linked cells before you run following macro


Sub Macro()
    Selection.Hyperlinks.Delete
End Sub

If you don't know where to paste above macro.
     
              a)Press Alt+F11 to open VB editor
              b)Press Alt+I+M to open Madule
Then paste and press F5 to Run.


Option B


Select the Hyper-linked cells and copy. Paste special and value

Create a Drop Down List in Excel

To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere on the worksheet. When you create a drop-down list for a cell, it displays an arrow next to that cell. To enter information in that cell, click the arrow, and then click the entry that you want.


We can create drop-down list by using " Data Validation" in Data menu.

1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

2.Then select the cell where you want to apply drop-down list.

3. To open Data validation window Click "Data Validation" option in Data menu

4. Choose "List"  in drop-down menu and choose the source.

It will through the Error message if we do manual entry. We can also customise the Error message.

Error Window

 

Select "Error Alert" Tab in Data Validation window. 

After enter the error message click OK. after that you will get follow error message.

To delete a drop down list, select the cell with the list. Click the Data menu, and then click Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All.

The maximum number of entries that you can have in a drop-down list is 32,767.

Microsoft Excel DGET formula

DGET

We can use DGET function when we have multiple criteria, we can use this as a alternative function of VLOOKUP function.

    How many boxes of a particular item do we have in stock?

     What Does It Do ? 

 This function examines a list of information and produces one result. If more than one record matches the criteria the error #NUM is shown. If no records match the criteria the error #VALUE is shown.

     

    Syntax

=DGET(DatabaseRange,FieldName,CriteriaRange) 

 The DatabaseRange is the entire list of information you need to examine, including the field names at the top of the columns. 

 The FieldName is the name, or cell, of the values to Get, such as "Value Of Stock" or I3. 

 The CriteriaRange is made up of two types of information.

 The first set of information is the name, or names, of the Fields(s) to be used as the basis    for selecting the records, such as the category Brand or Wattage.    

The second set of information is the actual record which needs to be selected, such     as Horizon as a brand name, or 100 as the wattage. 

Formatting
 No special formatting is needed.

Example 1
 This example extracts information from just one record.

 How many boxes of a particular item do we have in stock?

Example 2 

 This example extracts information from multiple records and therefore shows the #NUM error. 

 How many boxes of a particular item do we have in stock?  

Example 3 

 This example extracts information from no records and therefore shows the #VALUE error. 

 How many boxes of a particular item do we have in stock? 

Example 4 

 This example uses the =IF() function to display a message when an error occurs. How many boxes of a particular item do we have in stock?  

 =IF(ISERR(F88),CHOOSE(ERROR.TYPE(F88)/3,"No such product.","Duplicates products found."),"One product found.")

  

How to use Ms Excel SUM and the =OFFSET function

    SUM and the =OFFSET function


       Sometimes it is necessary to base a calculation on a set of cells in different locations. An example would be when a total is required from certain months of the year, such as the last 3 months in relation to the current date.

One solution would be to retype the calculation each time new data is entered, but this would be time consuming and open to human error.

     A better way is to indicate the start and end point of the range to be calculated by using the =OFFSET() function.

The =OFFSET() picks out a cell a certain number of cells away from another cell. By giving the =OFFSET() the address of the first cell in the range which needs to be totalled, we can then indicate how far away the end cell should be and the =OFFSET() will give us the address of cell which will be the end of the range to be totalled.

The =OFFSET() needs to know three things; 1. A cell address to use as the fixed point from where it should base the offset. 2. How many rows it should look up or down from the starting point. 3. How many columns it should look left or right from the starting point.

                                  =SUM(E24:OFFSET(E24,0,0))
This example uses E24 as the starting point and offsets no rows or columns which results in the range being summed as E24:E24.

                                    =SUM(E29:OFFSET(E29,0,1))
This example uses E29 as the starting point and offsets 1 col to pick out cell F29 resulting in a the range E29:F29 being summed.

                                    =SUM(E34:OFFSET(E34,0,2))
This example uses E34 as the starting point and offsets 2 cols to pick out
cell G34 resulting in a the range E34:G34 being summed.




Using =OFFSET() Twice In A Formula


     The following examples use =OFFSET() to pick both the start and end of the range which needs to be totaled.  

                                   =SUM(OFFSET(E45,0,1):OFFSET(E45,0,1))

     The cell E45 has been used as the starting point for both offsets and each has been offset by just 1 column. The result is that just cell F45 is used as the range F45:F45 for the sum function to calculate.

                                     =SUM(OFFSET(E51,0,1):OFFSET(E51,0,2))
     The cell E51 has been used as the starting point of both offsets, the first offset is offset by 1 column, the second by 2 columns. The result is the range F51:G51 which is then totalled.  

                                     =SUM(OFFSET(E57,0,1):OFFSET(E57,0,3))
     The cell E57 has been used as the starting point for both offsets, the first offset is offset by 1 column, the second by 3 columns. The result is the range F57:H57 which is then totalled.  






Example
     The following table shows five months of data. To calculate the total of a specific group of months the =OFFSET() function has been used. The Start and End dates entered in cells F71 and F72 are used as the offset to produce a range which can be totaled.

Explanation 

 The following formula represent a breakdown of what the =OFFSET function does. The  formula displayed below are only dummies, but they will update as you enter dates into cells F71 and F72.  

       Formula 1 =SUM( OFFSET(D79,0,MONTH(F71)) : OFFSET(D79,0,MONTH(F72)) ) This is the actual formula entered by the user.        

       Formula 2 =SUM( OFFSET(D79,0,MONTH(2)) : OFFSET(D79,0,MONTH(3)) ) This shows how the =MONTH function calculates the month number. In this example the values of the months are 2 and 3 for Feb and Mar. These values are the 'offsets' relative to cell D79. 


       Formula 3 =SUM( OFFSET(D79,0,2) : OFFSET(D79,0,3) ) This shows where the month numbers are used in the =OFFSET function. 


       Formula 4 =SUM( F85:G85 ) This shows how the =OFFSET eventually equates to cell addresses to be used as a range for the =SUM function.        


Hopefully now you all can understand how to use OFFSET function in Ms Excel. If any one having any idea better than this please share your thoughts / comments here.


  

How to sort colored cells from left to right in ms excel ?

Excel provides support for sorting a range based on the cells colors. If you want to sort based on cell color (by either the background fill color or the font color),

Don’t worry about if picture is not visible, hope you can able to make out cell colored. Color given location wise.

We going to learn how to sort colored cells from left to right in Ms excel.

First select the Table.

Click the " Sort" under Data. or press Alt+A+S to open "sort" window

In sort window click

"Options..." to choose sort " sort top to bottom" or "sort left to right".

click ok

Then Choose following in sort window.

Sort by = Table Header Row number

Sort on = Cell color (Even you can choose "Cell font" color if u want to sort by cell font color)

Order = Choose which ever color you want first in left.

Add level 's

to order the color.

after added 4 level Click ok.

you can make out below table got changed.

Drop your comments

How to Split MsExcel worksheet? How to view multiple excel file in same window?

Mostly Known option

How to work multiple excel file in same window? How to split Excel sheet?

To open multiple window for current document

Click view and click"New window" thrice if you want to open 3 window for same file.

If you Click Arrage all , "Arrage windows" will open ,

we can choose the option we want. I have selected “Vertical”

If you want to work different location in a same sheet.

How can we split Microsoft Excel window?

We can split the window Vertical and Horizontal

Split the window Horizontal

Double click Right top corner to split Horizontal

Current window will split in to two. Double click on top of the line to come b

ack to normal view

Split the window Vertical

Double click Right Bottom corner to split Vertical.

Even you can select View-split

If you click "split" window will split, if you click again split will remove from window. It will work like ON OFF switch.

share your inputs.

Shortcut Keys WORD EXCEL POWERPOINT

Ms Excel

http://goo.gl/i2Co9

Ms Word

http://goo.gl/ZEety

Ms Powerpoint while creating

http://goo.gl/lPmw7

Ms Powerpoint while Delivering

http://goo.gl/AYdhI

Share with your friends.

Excel Dmax function

DMAX

What Does It Do ?

This function examines a list of information and produces the largest value from a specified column.

Syntax

=DMAX(DatabaseRange,FieldName,CriteriaRange)

The DatabaseRange is the entire list of information you need to examine, including the field names at the top of the columns.

The FieldName is the name or cell, of the values to pick the Max from, such as "Value Of Stock" or I3.

The CriteriaRange is made up of two types of information.

The first set of information is the name, or names, of the Fields(s) to be used as the basis for selecting the records, such as the category Brand or Wattage.

The second set of information is the actual record, or records, which are to be selected, such as Horizon as a brand name, or 100 as the wattage.

Formatting

No special formatting is needed.

Examples

The largest Value Of Stock of a particular Product of a particular Brand.

How to convert MS Excel to PDF or XPS ? & MS WORD to PDF or XPS

Microsoft Save as PDF or XPS

There are many situations where it is useful to save your file in a fixed-layout format that is easy to share and print and hard to modify. Examples of these situations include resumes, legal documents, newsletters, and any other file that is intended to be primarily read and printed. The 2007 Microsoft Office system offers a free add-in to save or export this type of file, but you must first install the add-in before you can use it. Other third-party products may also be available to export a Microsoft Office file into a fixed-layout document.

By using an add-in, you can save or export a file from your Microsoft Office program into the following formats:

Portable Document Format (PDF) PDF is a fixed-layout electronic file format that preserves document formatting and enables file sharing. The PDF format ensures that when the file is viewed online or printed, it retains exactly the format that you intended, and that data in the file cannot be easily changed. The PDF format is also useful for documents that will be reproduced by using commercial printing methods.

XML Paper Specification (XPS XPS is a fixed-layout electronic file format that preserves document formatting and enables file sharing. The XPS format ensures that when the file is viewed online or printed, it retains exactly the format that you intended, and that data in the file cannot be easily changed

It also allows you to send as e-mail attachment in the PDF and XPS formats in a subset of these programs

Refer link: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7

Use Excel's SUMPRODUCT To Summarize Worksheet Data

SUMPRODUCT

What Does It Do ?

This function uses at least two columns of values. The values in the first column are multipled with the corresponding value in the second column. The total of all the values is the result of the calculation.

Syntax

=SUMPRODUCT(Range1, Range, Range3 through to Range30)

Formatting

No special formatting is needed.

Example

The following table was used by a drinks merchant to keep track of stock.

The merchant needed to know the total purchase value of the stock, and the potential value of the stock when it is sold, takinging into account the markup percentage.

The =SUMPRODUCT() function in Excel is used to multiply the Cases In Stock with the Case Price to calculate what the merchant spent in buying the stock.

The =SUMPRODUCT() function in Excel is used to multiply the Cases In Stock with the Bottles In Case and the Bottle Setting Price, to calculate the potential value of the stock if it is all sold.

How to edit a cell of text in Excel ?

What Does It Do ?

This function replaces a specified piece of text with a different piece of text. It can either replace all occurrences of the text, or a specific instance. The function is case sensitive.

Syntax

=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse) The InstanceToUse is optional, if it is omitted all instances will be substituted.

Formatting

No special formatting is needed.

Note

To cope with upper or lower case in the substitution you can use other text functions such as =UPPER(), =LOWER() or =PROPER() to ensure that the substitution will take place.

Table 1 shows how differing text cases alter the result of the substitution.

Change The Angle Or Orientation Of Text In Excel 2007

Orientation

1. Type the text into a cell
2. Select the cell
3. On the home tab of the ribbon, look for the alignment group. There you will find a button with an ‘a’ and a ‘b’ and an arrow all on an angle. That’s the Orientation menu. Click on the little drop down arrow beside it.
4. Select one of the quick options to angle your text

If you want to be more specific about the angle of your text, you can select “Format Cell Alignment” and set down to the degree how “slanty” you want your text.

Calculates the difference between two dates in excel

DATEDIF

What Does It Do?

This function calculates the difference between two dates. It can show the result in weeks, months or years.

Syntax

=DATEDIF(FirstDate,SecondDate,"Interval")

FirstDate : This is the earliest of the two dates.

SecondDate : This is the most recent of the two dates.

"Interval" : This indicates what you want to calculate.

These are the available intervals.

"d" Days between the two dates.

"m" Months between the two dates.

"y" Years between the two dates.

"yd" Days between the dates, as if the dates were in the same year.

"ym" Months between the dates, as if the dates were in the same year.

"md" Days between the two dates, as if the dates were in the same month and year.

Formatting

No special formatting is needed.

You can put this all together in one calculation, which creates a text version.

Age is 51 Years, 5 Months and 20 Days

="Age is "&DATEDIF(D34,TODAY(),"y")&" Years, "&DATEDIF(D34,TODAY(),"ym")&" Months and "&DATEDIF(D34,TODAY(),"md")&" Days"

How to use date formula in Excel?

DATE

What Does It Do?

This function creates a real date by using three normal numbers typed into separate cells.

Syntax

=DATE(year,month,day)

Formatting

The result will normally be displayed in the dd/mm/yy format. By using the Format,Cells,Number,Date command the format can be changed.

In Excel Find number of combinations for fixed number of items.

COMBIN

What Does It Do ?

This function calculates the highest number of combinations available based upon a fixed number of items. The internal order of the combination does not matter, so AB is the same as BA.

Syntax

=COMBIN(HowManyItems,GroupSize)

Formatting

No special formatting is required.

Example 1

This example calculates the possible number of pairs of letters available from the four characters ABCD.

Example 2

A decorator is asked to design a colour scheme for a new office. The decorator is given five colours to work with, but can only use three in any scheme. How many colours schemes can be created ?