Protect and hide formula in MS Excel Tutorial

Protect and hide formula in MS Excel Tutorial

Today let us learn how to hide formula in MS Excel.

Here the cells with red border are the cells where we put a
formula.

We will be able to see the formula contained in each cells
by selecting the respective cells.

To hide the formula in each cell.

First, select the whole work sheet by clicking on a cell and
pressing control + A.

Now right click,  select format cells, under  the protect tab, uncheck Lock and Hidden

Now select the Range of cells  where you want to hide the formula.

Right Click , select,  format cells, under the protection tab, checked,
Lock  and hide, then press, Ok.

To hide the formula you have to protect the sheet.

Go to the Ribbon Menu at , Review tab, click protect sheet,

In the pop up dialog,

Checked,  Select
locked cells,

Checked, select unlocked cells,

Typed your password, (any password of your choice to edit but
remember the password)

Again typed your password,

Clicked ok, (Now the sheet is protected and formula hidden and that's it)

Now click on the formula cells, the formula are hidden, so
it does not, show up, in the, formula bar, but you cannot edit the formula.

To edit the formula,

First You have to unprotect the sheet, 

Click on the unprotect sheet found under the reveiw tab.

Enter the password then click OK.

Now after disabling the Protection you will be again able to edit the formula.

But 

After editing dont forget to  again protect the sheet, so that, the hidden
formula, is again active .

Now anyone working on this sheet will not be able to view,
or change the hidden formula.

Excel Filter and Sort Tutorial

Excel Filter and Sort Tutorial:

To Sort Data alphabetically from A to Z or numerically in Ascending or Descending order use the Excel  Filter & Sort Function at the Right and corner of Excel Window.

Steps1:
Enter your Data

Step2:
Click on the Column Heading of your data

Step 3:
Go to the SORT & Filter > Filter (at the Right hand corner of the Excel Window)

Step4:
All your Column Headings will have small down arrow icons

Now you can sort your data A-Z /Ascending or Descending order.

Date in Excel tips

	A	B	C	D
1
2				Date Time Years
3
4	Age of person from DOB		01/20/80	37 Years, 8 Months, 26 Days
5	From to	12/31/12	04/14/13	0 Years, 3 Months, 14 Days
6
7	Adding Years to a Date	31-12-2012	30	31-12-2042
8	Number of days	31/12/2012	31/12/2042	10957
9

Age of person from DOB

Formula to be type at D4 should be

=DATEDIF(C4,TODAY(),"Y") & " Years, " & DATEDIF(C4,TODAY(),"YM") & " Months, " & DATEDIF(C4,TODAY(),"MD") & " Days"

Formula for Number of Years , month and days between two numbers D5

=DATEDIF(B5,C5,"Y") & " Years, " & DATEDIF(B5,C5,"YM") & " Months, " & DATEDIF(B5,C5,"MD") & " Days"

Adding Years to a Date D7

=DATE(YEAR(B7)+C7,MONTH(B7),DAY(B7))

Number of days between two dates D8

=DATEDIF(B8,C8, "d")

Excel Rounding Numbers

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. To know how to use all these formulas and how to round numbers based on any criteria, just read on.

Rounding Formulas in Excel

Before learning the tips, first lets understand various rounding formulas & what they do. Look at this:

Formula	What it does?
ROUND	Rounds a number to specified decimal points (or multiples of 10)
ROUNDUP	Rounds up a number
ROUNDDOWN	Rounds down a number
MROUND	Rounds to nearest multiple of specified number
INT	Rounds down to nearest integer
TRUNC	Gives you only integer portion
CEILING	Rounds up a number to nearest multiple of 1,10,100…
FLOOR	Rounds down a number to nearest multiple of 1,10,100…
EVEN	Gives next even number
ODD	Gives next odd number
FIXED	Rounds and converts to text format (with commas if you want)

Rounding Formula Tips

1. Round to 2 decimal points

Example: 1.2649 to 1.26

=ROUND(A1,2) Rounds value in A1 by 2 decimal points

2. Round up to 2 decimal points

Example: 1.2649 to 1.27

=ROUNDUP(A1,2) Roundsup value in A1 by 2 decimal points (ie away from zero)

3. Round to nearest integer

Example: 1.2649 to 1

=ROUND(A1,0) By using 0, we can round the value to nearest integer

4. Round to nearest multiple of 10

Example: 544.234 to 540

=ROUND(A1,-1) By using negative numbers, we can round the value to nearest multiple of 10, 100…

5. Round up to nearest multiple of 10

Example: 544.234 to 550

=ROUNDUP(A1,-1)

6. Round to nearest thousand

Example: 312789123 to 312789000

=ROUND(A1,-3)

7. Round to nearest million with one decimal point

Example: 312789123 to 312.8

=ROUND(A1/1000000,1) First we divide the number by million (1,000,000) and then round this to 1 decimal point.

8. Round to nearest multiple of 2

Example: 43 to 44

=MROUND(A1,2) Just like round formula, but for any multiple. So MROUND(A1,2) takes value in A1 and rounds it to nearest multiple of 2

9. Round to nearest multiple of 5

Example: 93 to 95

=MROUND(A1,5)

10. Round down to hundred

Example: 301 to 300

=FLOOR(A1,100) To round down, we can use FLOOR formula.

11. Get only the integer portion of a number

Example: -23.34 to -23

=TRUNC(A1,0) To extract only the integer portion of number, use TRUNC formula. Note: INT formula gives same result for positive numbers.

12. Round a number to 2 decimals and convert to text

Example: 312789.26921 to 312,789.27

=FIXED(A1,2,FALSE) In one shot, round and convert the number to text. Useful when you want text output.

13. Get next even number

Example: 42.1 to 44

=EVEN(A1) Gets you next EVEN number (away from zero)

14. Get next odd number

Example: 44.93 to 45

=ODD(A1)

15. Round to nearest quarter ($0.25)

Example: 19.14 to 19.25

=MROUND(A1,0.25) MROUND can be used with fractions too.

16. Round to next 9 (ie 19,29,39 etc.)

Example: 23 to 29

=ROUNDUP(A1,-1)-1 To do this, we just roundup the number to next 10 and then subtract 1 from it.

17. Round up to next 1000

Example: 124567 to 125000

=CEILING(A1,1000) Just like FLOOR, but takes you to next value.

18. Get only decimal portion of a number

Example: 23.345 to 0.345

=A1-TRUNC(A1) To get only decimal portion, subtract TRUNC value from original

The INDEX Function
• =INDEX(array,row_number,column_Number)
• =INDEX(D3:M9,6,3) returns content of the cell at the intersection of the sixth row and
third column of the range D3:M9.
Note: INDEX by itself is useful, but when combined with MATCH becomes very flexible.
INDEX and MATCH Together
• =INDEX(array,MATCH(lookup_value,lookup_array,[match_type]),[column_num])
• =INDEX(D2:M9,Match(A2,C2:C9,0),3)
• =INDEX(array, row_num,MATCH(lookupValue,Lookup_array, [matchType]))
• =INDEX(D2:M9,7,Match(A2,C2:C9,0))
Example: In the screen shot below, a value is interred in cell B15. The value in cell B15 is compared to a range
of information in cells A4:A12. If a MATCH is found, a row number is given. To find the column number, a
value is added to cell D15. This information is compared to the values shown in cells E3:K3. When an exact
match is found, a column number is returned. Having both a row and column number, a weekly test score is
displayed from the table D4:K12.
 =INDEX(D4:K12,MATCH(D15,A4:A12,0),MATCH(B15,D3:K3,0))
7
PARTICIPANT NOTEBOOK

WTEXF030116