Allowing users to edit ranges in excel

You can allow users to only edit certain cells, or ranges of cell in an excel, keeping the rest protected (See Protecting a worksheet in excel if you want to protect the whole sheet).

1. Tools -> Protection -> Allow Users to Edit Ranges














2. Click on New















3. Give appropriate title for the range, and define the range

4. You can add several ranges using steps 2 & 3
5. Click OK

6. You'd come to the New-modify-delete form again, where you have the option of protecting the sheet. Click the button marked "Protect Sheet".
(remember, we have to protect the sheet so that only the defined ranges are editable)

7. You would be asked to provide a password, and confirm the same. Once you enter the passwords, you are all set !!

Protecting a worksheet

If, for whatever reasons, you do not wish people to mess with the data that you are sending out in your sheet, you can simply protect the sheet.

PS: In case you want them to be able to edit parts of your sheet, you can use "Allow Users to Edit Ranges".

For protecting a worksheet, select the appropriate worksheet.
Go to the tools menu.
Tools -> Protection -> Protect Worksheet


Tools -> Protection -> Protect Worksheet











- At this stage, excel would ask you for a password. Provide a password of your choice.
- Excel then asks you to re-enter the same password.
- Click OK, and you are all set.

A Quick Gannt in excel

See the sample file here.


1. Open up a new excel file
2. Under column A, we would have the Task Names
3. Under column B, we would have the Start dates
4. Under column C, we would have the End dates
5. Column D onwards we have the dates for which we are building the Gantt.
6. Select cell D2 and copy this formula into the cell:
=IF(AND(D$1>=$B2,D$1<=$C2),1,0)
7. This formula essentially says, "If the date at the top of this column falls between the start and end dates, then have the value 1 in this cell; else have 0 in this cell.
8. copy the contents of the cell having the formula (Cell D2 in the example)
9. Select all the cells under the date columns (Cell D2 to AH7 in the example)
10. Paste the formula into all the selected cells
11. In the toolbar, select Format -> Conditional formatting
12. Select "Cell Value is" , "equal to" , and type the number "1" without quotes in the condition.
13. Click on Format
14. Select Patterns tab
15. Select a color of your choice (not white!!)
16. Add one more condition.
17. Perform steps 14-15 and select white this time.
18. Click OK
You're all set !! Fill in the dates and see the Gantt chart appear !