The attention (Transient) span for a human is said to be as low as 8 seconds. Some researchers do disagree with this number, but the widely accepted fact is, span is low. Even the focused attention for a healthy adult is not more than 20 min.
And, hence the birth to data visualization and that’s where condition formatting comes into the picture.
Let’s consider a very simple example, below figure shows Monthly sales data of the sales persons without condition formatting:
Now here is the data with condition formatting using ‘Data bar’ option in condition formatting. Looks better!
Condition formatting can have major effect in the way data is presented. For example, below shows employee attendance schedule (template readily available in MS EXCEL)
WITHOUT CONDITION FORMATTING:
WITH CONDITION FORMATTING:
Condition formatting can be used to highlight cells as per predefined rule. You can use various color coding or icons to highlight current status, blank cells etc; also, condition formatting can be used to highlight rows or columns depending on the data updated in.
What is the advantage of conditional formatting?
You can used AVERAGE or AVERAGEA depending on your requirement.
AVERAGE is used if all data points are number only.
AVERAGEA function is used if all non-blank fields (text or number) should be included while calculating mean.
Syntax: AVERAGE (RANGE); AVERAGEA (RANGE)
For example shown below:
AVERAGE (B1:B16) = SUM (B1: B16)/COUNT (B1: B16)=30/11=2.727 (30/11)
AVAERAGA (B1:B16) = SUM (B1:B16) / COUNTA (B1:B16) = 30 / 15 = 2
If the data set doesn’t have any text then AVERAGE will be equal to AVERAGEA as COUNT will be equal to COUNTA.
How can I calculate the mean formula in Microsoft Excel?
Yes. For example, in below screenshot I have randomly written ‘Protect’ in three cells assuming those are the ones we want to protect and rest should be open for edit.
Step 1: Press Ctrl+A to select the whole worksheet
Step 2: Click on the small arrow box available in the bottom right corner of the Font section on Home tab to see additional formatting options
Step 3: Now, open Protection tab on the pop-up and un-check ‘Locked’ option
Step 4: Select the cell you want to protect, follow step 2 and 3, to ‘Lock’ the cell
Step 5: Click on Protect Sheet (in Review tab)
Step 6: Set a password
Now, you can work on rest of the Spreadsheet, but the Locked cells will be protected. Unprotect sheet to edit the Locked cells.
Is there a way to protect cells on an Excel spreadsheet while working on the spreadsheet?