23 August 2013

Unlock Cells In Locked Datasheet With Excel Cell Style

Excel iconEarlier this week I wrote about Excel formulas for multiplication and division, and I mentioned that I used cell styles to show what cells contained formulas. Today I will present a demo that explains more about Excel cell styles.

Save cell formats in cell styles
There are several ways of giving a certain style to multiple cells in an Excel datasheet without applying them separately to each cell. You can for example format one cell manually and then copy the format to other cells, which I also show in the tutorial about multiplication and division formulas.
Excel cell styles button
When you use cell styles for formatting, you will have the different styles easy at hand in the banner. Excel has a number of pre-defined cell styles, and you can also define your own cell style, like I do in the demo below.

Sales prognosis
It is common to use Excel for sales prognoses, because in Excel it is easy to see how the result will be affected by changes. To get a realistic result it is however important that changes are entered in the correct cells. In the demo I show examples on that.

Lock cells
To prevent users from changing values in the wrong cells it is a good idea to lock the cells that should not be touched. I demonstrate how to do that in two steps:
  1. Create a cell style that unlocks the cells it is applied to.
  2. Lock the whole datasheet for editing.
Excel sheet

Now all cells are locked except those that I applied the unlocked cell style to, and if someone tries to change the value in another cell Excel will give a message that it is protected.

To make it more obvious what cells can be changed I finally modify the cell style I created so that all unlocked cells will be filled with a green color.

Use with products
Most of the Outlook add-ons have Excel reports on how the product is used, and you can of course use cell styles or any other formatting with those reports.

They are standard Excel sheets that get data from a database or a SharePoint site connected to the add-on, and they can be handles just like any other Excel sheets.

Please watch the demo and visit the Tips section for more Excel tutorials!

By Peter Kalmstrom
CEO and Systems Designer Business Solutions