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..

Sort data

Read More..

Save as PDF File

Excel 2007 Save as PDF File,

1.       Click on “File”> Then click on “Save As”> PDF or XPS

2.       PDF should be selected by default, if not select “Save as type” of PDF

3.       Enter a “File Name”

4.       The Open file after publishing if checked will open the file after saving it

5.       Select whatever “Optimize for:” option you want...I will select “Standard (publishing online...)

6.   Click the “Options” button to publish the entire workbook, a selection or just the active worksheet(s)

7.       Click OK> Then Click “Publish”>

Read More..

COUNT Formula counts numbers and dates

Theory:
=COUNT(Range)
Example:
=COUNT(A2:A9)
The count formula above counts the number of times a date value or number value is listed. The count formula will not count anything other than dates and numbers.

Read More..

true/false match formula

Theory:
To test if the values of 2 adjacent cells match exactly do the following:
=cell1=cell2
The results will be displayed as "True" if the pairs match or "False" if the pairs don't match.
Example:
You want to compare cell A2 to cell B2.  Let's say both cells have the value of 108.  The formula would be:
=A2=B2
The result would be "True".

Read More..

Date Formula

Formula	Description	Value
=NOW()	Returns today's date and time	24/06/2012 20:15
=TODAY()	returns today's date	24/06/2012
=WEEKDAY(NOW())	Returns the day of the week for today (Sunday) as a number	1
=WEEKDAY(TODAY())	Returns the day of the week for today (Sunday) as a number	1
=DAY(TODAY())	Retruns the day of the month for today (24th)	24
=DAY(NOW())	Retruns the day of the month for today (24th)	24
=WEEKNUM(NOW())	Returns the week number of the year which is 26 weeks into the year for today	26
=WEEKNUM(TODAY())	Returns the week number of the year which is 26 weeks into the year for today	26
=MONTH(NOW())	Retruns the month of the year which is 6 (June)	6
=MONTH(TODAY())	Retruns the month of the year which is 6 (June)	6
=YEAR(NOW())	Retruns the current year which is 2014	2014
=YEAR(TODAY()	Retruns the current year which is 2014	2014

Read More..

Straight Line Value Formula

Excel Straight Line (SLN) Depreciation YouTube Video

The straight line value is an excel formula which calculates the total depreciation amount of an asset you may hold for you business. Such as manufacturing machines.

Theory:

=SLN(Cost of item, Lowest depreciated value, Life span of asset in years)

Example with number values:

=SLN(6789,1000,10)

Example using cell references:

=SLN(A2,A3,A4)

Where cells:

A2 = Cost of item

A3 = Lowest depreciated value

A4 = Life span of asset in years

Read More..

Growth Formula

The Growth formula is used to forecast predicted growth in for example a business's projected sales figures.
Theory:
=Growth(previous sales data range, previous month numbers data range, new month number or cell reference)
Example:
=Growth($B$2:$B$13,$A$2:$A$12,C2)
For the formula above make sure you use absolute cell referencing with the dollars on both sides of the letters as shown, a part from the last cell reference being "C2" in this case. Then fill the formula down.
OR
=Growth($B$2:$B$13,$A$2:$A$12,13)
For the formula above make sure you use absolute cell referencing with the dollars on both sides of the letters as shown, a part from the last part which is just a number in this case "13". This paticular formula using the number 13 and not a cell reference is only useful for single next month prediction.

Read More..