Class Five; Text (VLOOKUP , Proper function, Clean Function, Value Function, Trim Function and Conditional Formatting)

VLOOKUP is a function that performs a vertical look-up of data.

VLOOKUP should be used when the values you wish to compare your data with are stored in a single column.

The values to be looked up are stored in the columns to the right of the comparison

values. This is shown in the example below

Find the VLOOKUP  of the profit for shoe

A                                              B                                  C                                  D

=VLOOKUP (A3, A3:D8,4,2) the value should be 5,000

Formula breakdown; A3 is the cell that contain shoe, A3:D8 is the whole table that contains the items, 4 is the row number of the profit, 2 is exact match to give the same amount.

VALUE FUNCTION

The value function converts a text string that looks like a number into an actual number. For example, if you had a text string like “24.5” and you used the value function, it would convert that string into the number 24.5.

=value(A3) Hint Ok….  A3 is the cell that contain a text string that look like a number.

CLEAN FUNCTION

The next function is the clean function. The clean function removes non-printable characters from a text string. For example, if you had a string like “₿”, inside your string the clean function would remove the non-printable characters.

= Clean(B4) Hint Ok ….. B4 is the cell that contains the dirty data.

CONDITIONAL FORMATTING

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition.

There are 5 types of conditional formatting visualizations available:

I) Background Color Shading (of cells)

ii) Foreground Color Shading (of fonts)

iii. Data Bars.

iv) Icons (which have 4 different image types)

v) Values.

HOW TO USE CONDITIONAL FORMATTING

1.      Highlight the cell you want to conditionally formatting

click Conditional Formatting on the Home ribbon.

Choose from the list of rules (Highlight cell rules, Top/Bottom rules,

 Data bars, Color scales, Icon scales) depending on which rule you want to use

Set your formatting values or words (Greater than 200, or words with P)

click the Format button and select a red color.

1.      Select cell A2.

click Conditional Formatting on the Home ribbon.

click New Rule.

click format cell base on their values

on the format style click drop down to choose 2 or 3 colors depending on your choice

select colors for your highest and lowest values, look at preview to view if it’s the color you want then click Ok.