Removing spaces

1.       Highlight column in question.  In this case I will highlight column A.

NB: Make sure to highlight the required column or all spaces in the other columns will be replaced as well.

2.       Select key CTRL + H...which is the keyboard shortcut for Find and Replace.  Or go to the Home tab> Then go to the Editing group and Click “Replace”.

3.       In the “Find What” text box, enter 1 single space by pressing the space bar

4.     Make sure there are no data and no spaces in the “Replace With” text box

5.       Click “Find Next”> Then click “Replace All”.  This will replace all the spaces in the column highlighted.

6.       In my case 10 spaces have replaces with nothing that is there are no longer any spaces in the highlighted column.

Read More..

Date

DATE for today,   

   1.       Go to> Formula> Date & Time> Date

      2.       In the Year text box enter Year(now())

3.       In the Month text box enter Month(now())

4.       In the Day text box enter Day(now())

5.       Click on OK

6.       You now have today’s date, 31-JUL-2012 as M/D/YYYY

Read More..

SECOND

How to display the seconds value in a specific time,

1.      Go to the Formula tab> Select the Date & Time drop down list> Click on SECOND

2.      The function argument window will popup

3.      In the Serial_number input box enter the required time including seconds

4.      I have entered "10:25:31"

5.      The resulting value of the seconds within the time is 31.

6.      Click on OK

7.      The result is now displayed in the active cell of excel

Read More..

Weekday

WEEKDAY function for today’s date,

1.       Go to “Formula” tab> Select “Date & Time” drop down list and select "Weekday"

2.       In the “Serial_number” text box> enter "now()" and click OK

3.       The weekday of Monday is now entered in excel which is the number 2 for Monday.  This is as the first day of the week is Sunday.

Read More..

VLOOKUP Exact Match

Exact Match, Excel VLOOKUP formula,

The theory for vlookups is as follows:

=VLOOKUP(cell reference, corresponding range of cells to link to, column number to output, false)

OR

=VLOOKUP(cell reference, corresponding range of cells to link to, column number to output, 0)

Note:

"False" means Exact Match 

OR

0 means Exact Match

Excel VLOOKUP Tutorial on YouTube

Read More..

AND/OR Formula

OR Formula

=OR(A2,B2)

If either cells adjacent in column A OR B are not equal to 0 then the answer will be True or if any of the adjacent cells are equal to 0 it will answer false.

AND Formula

=AND(A2,B2)

If both cells adjacent in column A AND B are not equal to 0 then the answer will be True or if any of the adjacent cells are equal to 0 it will answer false.

Read More..

Protecting Cells in Worksheet

 1.   Highlight entire worksheet by clicking immediately to the left of the column A header and immediately above row 1 header.

  2.   Right click on highlighted worksheet and then left click on format cells.

  3.   Click on the protection tab and uncheck Locked.  Then click OK.

   4.   Then select the cells that you are interested in protecting.

   5.   I have selected cells A1 to Q30

  6.   Right click on the highlighted area and left click on format cells.

  7.   Select the protection tab and tick the locked check box.  Then click OK.

  8.   Click on the Home tab> then click the format dropdown button in the cells section> then click protect sheet.

  9.   Select the options you want to allow such as Format Cells> Then enter a password> Then click OK.

 10.   Enter the password again for confirmation. Click OK.

 11.   Finally, save the workbook.

Read More..