F2 + F9

Reverse Vlookup

Reverse Vlookup... (much awaited formula)...
now copy paste below code in your VB script.. and enjoy the RVLOOKUP

--------------------------------------------------------------------------------------

Function RLOOKUP(Look_Value As Variant, Array1 As Range, Array2 As Range, Col_num As Integer)

On Error Resume Next
Dim ValFound

With WorksheetFunction
Set Array1 = .Range(Array1.Address)
Set Array2 = .Range(Array2.Address)
ValFound = .Index(Array2, .Match(Look_Value, Array1, 0), Col_num)
End With

If IsError(WorksheetFunction.Match(Look_Value, Array1, 0)) Or Look_Value = "" Then
ValFound = CVErr(xlErrNA)
RLOOKUP = ValFound
Else
RLOOKUP = ValFound
End If

Set Array1 = Nothing
Set Array2 = Nothing

End Function

How to Reduce the Excel size

Few VERY IMPORTANT tips to reduce your size of Excel (check this... it is very useful);

1. Press Alt + F3 .... if there are any Errors in Name delete those Names by selecting all items and press delete

2. on a worksheet press Alt + Backspace... it will move you to last active cell of the excel... now check is it actual last cell of your worksheet ??? if not then select the rows from that cell to act...ual last cell and delete those Rows and do similar things for columns... (now check the same for all the worksheet)

3. If there are many Pivot tables then try to minimze the Pivot tables and keep the requried Pivot table only

4. Save the file with *.xlsx or *.xlsb... don't worry your files will work properly it is just advance way to store the details.

Try these things.. and if you has any others please add to this list

Activate Fill Handle in Excel

If you are not able to see the fill handle . Snapshot below-

Step 1

Step 2

Courtesy : Ashish Kaul

Most useful shortcut of Excel

Short Key	Activity
Alt+A+Q	Advance Filter
Alt+D+F+A	Advance Filter
Alt+D+P	Pivot Table
Alt+E+I+S	Fill Series
Alt+E+L	Delete Sheet
Alt+H+B	For Border
Alt+H+F	Font Colour
Alt+H+F+P	Format Painter
Alt+H+FF	Font
Alt+H+FS	Font Size
Alt+H+H	Them Colour
Alt+H+I	Insert
Alt+H+L	Conditional Formating
Alt+H+S	Sort Normally
Alt+D+S	Sorting
Alt+M+M+D	Define Name
Alt+M+N	Name Manager
Alt+N+C	Insert Column Chart
Alt+N+E	Pie Chart
Alt+N+E	Bar Chart
Alt+N+K	Insert Chart
Alt+N+N	Line Chart
Alt+N+V+C	Pivot Chart
Alt+N+V+T	Pivot Table
Alt+O+DL	Conditional Formating
Alt+O+H+R	Rename Of Sheet
Alt+E+L	Delete Current Sheet
Alt+R+P+S	Protect Sheet
Alt+T+G	Goal Seak
Alt+W+F+F	Freeze
Alt+D+E	Text To Column
Alt+A+E	Text To Column
Alt+D+L	Data Validation
Alt+A+V+V	Data Validation
CTRL+9	Hides The Selected Rows.
CTRL+0	Hides The Selected Columns.

By Noorain Ansari

Inbuilt excel translator

Inbuilt excel translator (special gift to all my offshore friends :-) )...

Where you can convert your document in any language... yes and it is possible in excel it self...
Just press Alt + R + L ---> you will get a window in your right side of Excel... now select your Language in which you want to translate... and press Insert... thats it
Try it... may be it will help you

Calculate Sheet / Workbook

Calculate Sheet / Workbook (Very Useful)
Most of the time when you work on large size workbook / worksheet, you must have seen your file is taking toooo much time and going in "Calculating Mode"...

Now here is the Tip to avoid such time loss....
First convert your file to Manual Calculation mode ---> Formula ---> Calculation Option ---> Manual
Oops... but now your formula's are not updating after any updates

So now press "F9" now you can see your calculations are working fine at this time
and it will update all your open Excel workbooks.... but whatif when work in >10-15 files at a time and wanted to updated only current worksheet..... No probs... press Shift + F9 OR Alt + M + J... it will only update your current worksheet

Check this when you are working on many complex worksheets

Ctrl + Shift Shortcuts

Few Ctrl + Shift Shortcuts....

Ctrl + Shift + ~ General Number Format
Ctrl + Shift + ! Number Formatting (Comma style - ,00)
Ctrl + Shift + @ Time Number Formatting
Ctrl + Shift + # Date Number Formatting
Ctrl + Shift + $ Currency Number Formatting
Ctrl + Shift + % Percentage Number Formatting

Code for creating file on specified path (Share Certificate)

Sub Share_Certificate()
'
' Share_Certificate Macro    Dim newname As Variant
    Sheets("1 PG").Select
    Sheets("1 PG").Copy After:=Sheets(3)
    ActiveSheet.Select
    newname = Range("D19").Value
    ActiveSheet.Name = newname
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B3:E5").Select
    Application.CutCopyMode = False
    Sheets("Master").Select
   
End Sub


Sub New_File()
'
' Macro1 Macro
'
    Dim Fpath As String
    Dim Fname As Variant
   
   
    Sheets(Array("1 PG", "2 PG")).Select
    Sheets("1 PG").Activate
    Sheets(Array("1 PG", "2 PG")).Copy
    Sheets("1 PG").Select
    Fname = Range("D19").Value & "Share Certificate.xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.CheckCompatibility = False
        Fpath = InputBox("Kindly provide folder address where you want to save your file", "")
    ActiveWorkbook.SaveAs Filename:= _
        Fpath & Fname, FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWindow.Close
    Sheets("Master").Select
   
End Sub

Lets Learn VB : Message box in VB

suppose you want to have a message box like below:




here is the code:
Sub msgboxyesno()
Dim answer As String

answer = MsgBox("Do you wish to continue ?", vbYesNo, "Please Answer")

If answer = vbYes Then
'if he or she selects yes below action will be happen
MsgBox " You selected yes"

ElseIf answer = vbNo Then
'if he or she selects no below action will be happen
MsgBox "You selected No"

End If


End Sub

suppose you want to have a message box like below:



code:


Sub msgboxokcancel()
Dim answer As String

answer = MsgBox("Do you wish to continue ?", vbOKCancel, "Please Answer")

If answer = vbOK Then

'if he or she selects vbok below action will be happen
MsgBox "Have a beautiful day... "

ElseIf answer = vbCancel Then
'if he or she selects vbcancel below action will be happen
Exit Sub
End If


End Sub

and if you want to have one like below



code:

Sub msgboxyesnocancel()
Dim answer As String

answer = MsgBox("Do you wish to continue ?", vbYesNoCancel, "Please Answer")

If answer = vbYes Then
MsgBox "Have a beautiful day... "

ElseIf answer = vbNo Then

MsgBox "You selected No"

ElseIf answer = vbCancel Then
Exit Sub
End If


End Sub

UID Card - useful information

Card’s configuration should be very simple to know person’s location without help of any device
The card should be available in form of smart card. (See the demo card)
Code # Use for Unique Identity cards (Indians) * Use for NRI. // Use for foreigners working in embassies or Ministers/ Officials. = Use for foreign visitors in India. */ Foreigners are working in Indian. 0xxx Country code for NRI where he is living now.. 0011 Delhi (state code) NRI belongs to Delhi state 0000 NRI belongs to particular district. **** **** **** The 12 digits will belongs to UID Number

Card’s numbers should be
# 0091 0011 0000 **** **** **** for Indian
* 0001 0212 0000 **** **** **** for NRI (US Citizen)
// 0001 0212 0000 **** **** **** for other foreigners working in India.
= 0001 0011 0000 **** **** **** for foreign visitors.
*/ 0001 0032 0000 **** **** **** for foreigners working in India.

How the card can be multipurpose for us:

• Debit Card (Before transaction the person has to enter its last four digits of virtual Debit cards)
• Credit Card (before transaction the person have to enter its last four digits of virtual cards)
• No need to keep huge foreign currency or Indian rupee in India.
• Bank Account opening, No need to file any paper just swipe your and guarantor’s UID card.
• In NREGA/MNREGA the contractor will file your card code in his attendance register and you will get your payment in your bank account.
• Employer just enter his/ her UID at the time of joining, his/ her all record will update with current employer.
• Just fill only your UID card no. in Online Tax returns; it will automatically deduct your tax after rebate (LIC, 80G, and Investment). Here no need to get refund and govt. can save thousands of Cr. Rupees as refund interest.
• During the shopping in your area you have to swipe your card and insert the pin for transactions. For this banks have to reduce transition charges till 0.1% or free.
• This card will be our driving license. On this card a picture will be published of authorized driving vehicle. (White car for pvt., and yellow car for commercial).
• Our all the personal records like, height, finger prints and education will be feed available in it.
• Card holder can cast his/ her vote at any where in India through ATM or any information kiyosk with the help of this card. (The person will enter its card in voting machine and soon all candidate lists will be available of his/ her locality in touch panel voting machine.
• No one cay buy or sell any property without UID card. Through it we can control the black money and confirm that how much property is belongs to with this person. Govt. can provide subsidy to whom have not any flat or property and get extra tax who have already flat or property.
• Through this card govt. will deduct Electricity bill, Water bill, house tax or other bills itself. No need to go anywhere to submit your bill. (It should be applicable first in A B and C category of Cities).

Stop the PDS system & start cash subsidy

Now Govt. should stop PDS system in India. We all are know that only powerful peoples are availing all the benefits of PDS system and subsidy. There is lots of persons who are paying income tax and getting BPL facility and using Ration Cards. We should use UID to divert subsidy in actual beneficiary at his/her UID account so that he can buy grain, rice and sugar from other departmental stores. The subsidy (It is will be in point) will directly deduct from his/her account according to his/her family unit and he /She can’t convert this subsidy into money. Through this channel the BPL holder will get lots of benefits like, No need to go @ Fair Price Shop during working Day, Any time shop from any departmental store, No need to setup fair price shop, It saves transportation cost & Manpower, No need to store buffer stock, Actual person will get benefit, No black marketing and lots of other benefits.

Today we are living in 21st century and we have not any information about the UID cards. We know that it is a use full for all Indians who is living in India and abroad. Now UID has not cleared that they will make UID card for NRI and foreigners. Because if we are providing UID to our entire motive is defeating. How do you identify the person that he/she is Indian, Brazilian, Paki or Bangladesi because all’s color are same and they can speak local language easily and what about the foreigner nationalist who is working in India? As per my assumption govt. should make UID cards for all as per his identity, If he/she is visitor he should a visitor UID card with all information and he/she will bear temp UID cost as per visa norms. Because here in India lots of foreign nationalist is living without visa and information. If he/she will lose his/her passport who will recognize his/her status and nationality. UID can solve the identification issue and help to deport unwanted person.


The other thing is that we have already lots of cards like Pan, Driving, Debit, Credit and lots of other cards with Voter I Card. In my previous blog I have mention that how can we use it dynamically for all thing and really it reduce our wallet load and provide single card for all transaction. We can merge all other cards in UID so that we will have not carry different card for different. Now govt. can merge PAN, Service Tax No., Passport, National Health Card, Bank Card and Voter ID cards in first phase because all cards provided by Central govt. and they can merge it easily. In second phase state govt. can merge PDS system, Driving License, Official cards etc. Phase by phase we can manage it and make it multiuser card. What do you think that govt. should issue UID cards for NRI and foreign nationalist or Not. We are waiting for your view. Because through this platform we reach our voice to govt. to take positive decision on UID for NRI.. The writer is Sr. Consultant in ITvision India. * We are changing continuously after your views and suggestion. Hope you will keep continue your support.. This website is not an official website of UIDAI. You can send your views and suggestions at info@uidaicards.com

FOR LATEST UPDATES, JOIN US.

___________________________________________________________

UID can replace voter Card

Election commission should associate with UID authority and it will really reduce to maintain two cards in wallet. According to UID database, voters will automatically be eligible for voting according to his/her DoB and when you will print the voter’s list there will be thousands of voters adding itself. It will really reduce your cost of.. BLO, Election offices in Delhi and printing cost of Voter ID card, Because UID will be made once in lifetime. Now time has come when we should introduce “Negative voting button” so that we can inspire negative voting against candidate. UIDAI authority should publish online application form for UID Numbers and inform them through SMS about the location where they can go to identification.

_______________________________________________________________________

Can UID control on unknown property ?

UID can be useful to control unknown property in India. We aware that most of the property has been purchased to illegal & corruption’s money. We also aware that it happens because of our politicians & corrupt officials. Now through UID we can watch & control on property & help to provide home to homeless in India. I am starting a poll to share your views. If you think that it can really control on unknown property & help to homeless. Please vote.

________________________________________________________________

Can UID Card replace voter Card ?

Election commission should associate with UID authority and it will really reduce to maintain two cards in wallet. According to UID database, voters will automatically be eligible for voting according to his/her DoB and when you will print the voter’s list there will be thousands of voters adding itself. It will really reduce your cost of.. BLO, Election offices in Delhi and printing cost of Voter ID card, Because UID will be made once in lifetime. Now time has come when we should introduce “Negative voting button” so that we can inspire negative voting against candidate.

Hide Cell Error while printing

If you want to hide the error while printing your spreadsheet, There is a simple print setting change you need to do.

Follow the below steps:-
1) Go to Page Setup
2) Select Sheet tab
3) Select blank or "--" in "cell error as:" option.
4) click Ok and print the page


This will keep the error in spreadsheet but will not be displayed on printed pages.

Sometimes it’s useful to know where the errors are so you can correct any that are not expected, but if you regularly print reports you probably don’t want the errors displayed.

Do give a try when next time you print any spreadsheet.

How Big Is a Worksheet? (Interesting Facts)

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 ×1,048,576), and you'll see that a worksheet has 17,179,869,184 cells.

Remember that this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using a 1024 × 768 video mode with the default row heights and column widths, you can see 15 columns and 25 rows (or 375 cells) at a time - which is about .000002 percent of the entire worksheet.

In other words, more than 45 million screens of information reside within a single worksheet.
If you were to enter a single digit into each cell at the relatively rapid clip of one cell per second, it would take you about 545 years, nonstop, to fill up a worksheet.
To print the results of your efforts would require more than 40 million sheets of paper - a stack more than a mile high.

As you might suspect, filling an entire workbook with values is not possible. It's not even close to being possible. You would soon run out of memory, and Excel would probably crash.

You have complete control over the column widths and row heights - in fact, you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you have complete control over colors.

Text in a cell can be displayed vertically (or at an angle) and can even be wrapped around to occupy
multiple lines. NEW In the past, Excel was limited to a palette of 56 colors. With Excel 2007, the number of colors is virtually unlimited.

Add the frequent use function to Quick Access Toolbar

Add the frequent use function to Quick Access Toolbar... and you will get additional shortcut to use this....
How ???
Ok just add any function to QAT... now press Alt.... yupp... you can see a shortcut got highlighted on your QAT.. now next time just press that shortcut you can get result....

Shift + F9 or F9 ---> Calculate Sheet / Workbook (Very Useful)

Calculate Sheet / Workbook (Very Useful)
Most of the time when you work on large size workbook / worksheet, you must have seen your file is taking toooo much time and going in "Calculating Mode"...

Now here is the Tip to avoid such time loss....
First convert your file to Manual Calculation mode ---> Formula ---> Calculation Option ---> Manual
Oops... but now your formula's are not updating after any updates

So now press "F9" now you can see your calculations are working fine at this time
and it will update all your open Excel workbooks.... but whatif when work in >10-15 files at a time and wanted to updated only current worksheet..... No probs... press Shift + F9 OR Alt + M + J... it will only update your current worksheet

Check this when you are working on many complex worksheets

Ctrl + Shift + # - Convert numbers to Date format....

Ctrl + Shift + # - Convert numbers to Date format....

Many times when we type a date, it shows in numbers format and to change the format we have to follow the route of change of Cell Format and select the Date type.... OMG.. such a long step... no worries now just press Ctrl + Shift + #

Now tell me.... what you will do to change the number in % format ???

Convert numbers in millions.... "#,,.00"

Select Cell (numbers you have typed)----> press Ctrl + 1 ----> Custom -----> in "Type"

"#,,.00"

you can see your numebrs are converted in million without using any formula or disturbing any values

Ctrl + Shift + # - Convert numbers to Date format....

Ctrl + Shift + # - Convert numbers to Date format.... 

Many times when we type a date, it shows in numbers format and to change the format we have to follow the route of change of Cell Format and select the Date type.... OMG.. such a long step... no worries now just press Ctrl + Shift + #

Now tell me.... what you will do to change the number in % format ???