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