Excel : Custom Cell Formatting

Right Click on Cell-->Select Format Cells-->Select Custom


"#" is used as a placeholder for permitted digits/Digit placeholder. This code does not display extra zeros.
"?" indicates either a digit/Digit placeholder. This code leaves a space for insignificant zeros but does not display them
, (comma) Thousands separator. A comma followed by a placeholder scales the number by a thousand.

You can design your own custom number formats

Excel : Display Credit Card Number

To Display Credit Card Number in Cell you have to format the cell
if last digit of your credit card number is Zero then you can format cell as 0000 0000 0000 0000
if last digit is not zero than assuming your credit card number is in cell A2 Try below formula
'=LEFT(A2,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&RIGHT(A2,4)

Microsoft Excel retains 15 significant digits. To display all 15 digits, you must use a number format (custom or built-in) other than General. The General number format displays up to 11 numeric characters, with the decimal point counting as a numeric character. Therefore, if the number contains a decimal point, Excel can display up to 10 significant digits, but if the number does not contain a decimal point, Excel can display up to 11 significant digits Adding more numbers to the left of the decimal point causes the number to appear in exponential notation.

Create Expiry for Excel Workbook

Some times you have to set expiry date for your excel workbook. which will restrict use of workbook after certain period of time.  

  Steps:

1. Press "Alt+F11" to launch the Visual Basic Editor from Excel
2. Right-click "ThisWorkbook" in the "Project Explorer" window. Select "View Code" from the list of available options
3. copy below code

Private Sub Workbook_Open()

'If Sheets("sheet1").Range("a1").Value < Date Then ' you can set value on worksheet

If doe < Date Then 'You can define date as doe

MsgBox "This tool has expired, please contact Admin.", vbCritical, "Expired"

ActiveWorkbook.Close False

End If

End Sub

MS PowerPoint- Play Video with Timer

This requirement came from my one of my MBA college friend.
show the people that they have never seen\ heard before during presentation
You can play video in Excel and PowerPoint as well.
Many option's are available to get it done. If you use youtube video URL then you require internet connectivity during the presentation. If you use windows media player option you have to save the video on particular drive & carry the video during the presentation.

Best way i found is to convert your video in swf format & insert it in PowerPoint or Excel.
Software Link : http://www.dvdvideosoft.com/products/
download presentation : http://www.mediafire.com/file/n43cez8fic2itrc/Powerpoint-Play Video with Timer.ppt

Steps :

1. Go to developer tab
2. On the control group click on insert
3. Then click on more control (icon like hammer)
4. You will get More control box ; Select shockwave flash object & click ok
5. You will get cross sign (+) drag it on Powerpoint slide & select the area for video
6. Right click on square
7. Then select properties
8. Enter the full path to the Shockwave file (SWF) in the Movie property text-box. (e.g "C:\Users\MAHESH\Desktop\Download\abc.swf")
9. Set the EmbedMovie property to True
10. Run the slide show

For Countdown timer :

Amazing Excel VBA Creativity

I found below excel workbooks during learning excel VBA
you can download workbook from below links

• Painting in Excel 

         http://www.mediafire.com/?4aythpdtaj7irkm

         http://www.mediafire.com/file/hoe2pxsx1hdd0q6/Excel VBA_Painting In Excel.xls

       

• Excel VBA MP3 Player

         http://www.mediafire.com/file/q9hjmticqx0cyc1/Excel VBA-MP3-Player.xls

• Find Internet Browsing History in Excel sheet

        http://www.mediafire.com/file/bz7q8jf47b57za9/Excel VBA -Find Internet History.xlsb

Greetings in Excel

I have received amazing  response for New year greeting wishes in excel from all who seen them around the world

• valentine's day greeting cards in Excel

          http://www.mediafire.com/?7f4lhrwya97xzlf

• happy new year greetings in Excel

          http://www.mediafire.com/?9filaap09h4gqgq

          http://www.mediafire.com/?a5k4acw114g90s6

Excel : Find duplicate entry while entering data in column

For any column in worksheet .This will give you alert while entering duplicate entry with msg box & column header name. highlight the duplicate entry

Right click on sheet tab-> view code->copy below code


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range(Cells(2, Target.Column), Cells(Target.Row, Target.Column)), Target) > 1 Then
     Alert = MsgBox(Cells(1, Target.Column) & " Already Exist - Click Yes do delete", vbYesNo)
     If Alert = vbYes Then
     Application.Undo
     Else
     Target.Interior.ColorIndex = 6
     Exit Sub
     End If
End If
End Sub