Excel: Use Conditional Formatting to format cells by formula

Categories: Development Notes; Tagged with: ; @ May 15th, 2014 23:31

Requirement:

As a (junior) Excel user, I want to compare two columns and change the background colour  for the cells which is not consistent.

SNAGHTML8b7b0b

 

Steps:

1. Create New Rule:

you may select the B2, and click Conditional Formatting > New Rule:

image

Configure the formula and the format.

2. Apply the the rule:

You may right click the ‘+’and then drug,  select the ‘Fill formatting Only’ when drop.

image

Or you can use Conditional Formatting > Manage Rules:

image

to change the “Applies to…” and then “Apply”

Excel 首字母大写

Categories: Development Notes分享; Tagged with: ; @ November 28th, 2012 20:38

使用Proper函数只能做到每个单词的首字母大写, 该单词的其余字母会变小写。

但如果有时只需要将首字母大写,剩余字符保持不变, 此种情况下可使用公式操作:

= UPPER(LEFT(A2))&RIGHT(A2,LEN(A2)-1)

 

image

Excel: count nonblank column numbers using COUNTA 使用COUNTA计算Excel非空行数

Categories: Development Notes; Tagged with: ; @ September 19th, 2012 19:16

Use COUNTA function:
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

>>COUNTA function Details<<

Adding drop down list in Excel 在Excel中增加下拉框

Categories: Development Notes; Tagged with: ; @ September 5th, 2012 17:33

1. preparing the data source:  just input your values in file;

2. Edit the validation of the target cell(s): Data/Validation:

3. Click ‘OK’, now, you can see the list when you click the cell. if needed, you can use the auto fill tool to set validation for other cells.

 

Excel: Get cell comments

Categories: Development Notes; Tagged with: ; @ August 23rd, 2012 19:05

1. Alt + F11, in the opening window, click: Insert>Module, paste the folling code:

Function getComment(incell) As String
On Error Resume Next
getComment = incell.Comment.Text
End Function

2. Alt + Q to quit the window

3. use the funciton in cell, like:
=getComment(A1) , will fill the cell with A1’s comments

 

Older Posts



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.