In some cases, we need to convert or adjust or transform the data cell in a specific format without changing the originality. In this article, I’ll discuss 4 methods of formulas to use cell format formula in Excel.

## Download Practice Workbook

## What is Cell Formatting

Cell formatting is a process of adjusting or changing the appearance without hampering any transformation of the original value of the cell.

## 4 Ways of Using Cell Format Formula in Excel

For today’s analysis, we have a dataset where the name of items are provided with their order id, date, states and sales.

### 1. Cell Format Using The TEXT Function

The **TEXT **function converts a value to text in a specific number format. You can visit the dedicated article of TEXT function where syntax and usage of the function are discussed elaborately.

However, if you input any value and use the **TEXT **function to convert the value, you’ll see the output like the following picture.

`=TEXT(B5,C5)`

Here, **B5 **is the cell of value and **C5 **is the format-text.

As you see the explanation in the top-right side of the above picture, the **TEXT** function returns the given value into many formats.

For example, the **TEXT **function returns “1000” from “-1000” and vice-versa.

Besides, it converts the value into a scientific format.

More importantly, the **TEXT **function extracts the name of day, month from a given date and the function adds leading zeros before any number.

More importantly, we may use the **TEXT **function to find the Sales (product of price and quantity) with the text “The Sales is” in front of the value of the sales.

In such a situation, the formula will be like the following.

`="The Sales is "&TEXT(C5*D5, "$###,###.00")`

Here, **C5 **is the cell for price of the items, **D5 **is the quantity (Qty), and **$###,###.00 **is the number format for the value of the sales in dollar (**$**) currency.

### 2. Cell Format Using Conditional Formatting in Excel

**Conditional Formatting** is a useful Excel tool that converts the color of cells based on specified conditions. We’ll see some specific and useful applications of **Conditional Formatting**.

#### 2.1. Highlighting Any Word in The Dataset

If you guys need to highlight any data for better visualizations, you may use the tool from the **Styles** command bar.

Assuming that you want to highlight the item “TV” and states “Ohio” along with the whole dataset.

For doing that follow the steps below.

⏩ Select the cell range **B5:F15**

⏩ Click on **Home** tab>**Conditional Formatting**>**New Rule**

⏩ Choose the option **Use a formula to determine which cells to format**

⏩ Insert the following formula under the **Format values where this formula is true:**

`=OR(B5="TV",B5="Ohio")`

Here, **B5 **is the cell of AC.

⏩ Lastly, open the **Format** option to specify the highlighting color.

⏩ Press **OK**.

Then the output will look like this.

The above picture reveals clearly that the conditional formatting highlights the word “TV” and “Ohio”.

If you want to change the color of the items which are from “Ohio” states, you can also use the following formula through utilizing the conditional formatting tool.

`=$E5="Ohio"`

Here, **E5 **is the starting cell of the ‘states’ field.

You see that the whole item which belongs to “Ohio” is highlighted with an individual color.

#### 2.2. Highlighting Dates in Next 30 Days

Again if you want to identify and then highlight the dates in the next 30 days from now, use the formula below in the **New Formatting Rule** dialog box.

`=AND(B5>NOW(),B5<=(NOW()+30))`

Here, **B5 **is the starting cell of the order date.

The above formula utilizes the **AND **and **NOW **function. The **AND** function returns the output into **TRUE** if all conditions are **TRUE** and the **NOW** function returns the current date.

#### 2.3. Highlighting Missing Values

Furthermore, we can show missing values by synchronizing the lists using the conditional formatting tool.

In that case, use the following formula for list 1.

`=COUNTIF($D$5:$D$11, B5)=0`

Here, **D5:D11** is the cell range of list 2, and **B5 **is the starting cell of List 1.

And the formula for list 2 is-

`=COUNTIF($B$5:$B$15, D5)=0`

Here, **B5:B15** is the cell range of list 1 and **D5 **is the starting cell of list 2.

#### 2.4. Applying Multiple Criteria As Cell Format

Lastly, we can apply **AND** logic with multiple criteria in the whole dataset and highlight the output with the conditional formatting tool.

For example, we can highlight the items having the price is less than or equal to $300 and sales are greater than or equal to $2000.

For this, put the following formula in the **New Formatting Rule** dialog box.

`=AND($E5<=300,$G5>=2000)`

Here, **E5 **is the starting cell of price and **G5 **is the starting cell of sales.

So, only 4 items have the requirements of having the price is less than or equal to $300 and sales are greater than or equal to $2000.

### 3. Cell Format Formula for VBA Coding

Now, let’s see how we can apply the **VBA** Format function to convert the value into a specific format.

**Step 1: **

Firstly, open a module by clicking **Developer**>**Visual** **Basic**>**Insert**>**Module**.

**Step 2: **

Then, copy the following code in your module.

```
Sub Format_func()
Range("C5").Value = Format(Range("B5"), "Currency")
Range("C6").Value = Format(Range("B6"), "Long Date")
Range("C7").Value = Format(Range("B7"), "True/False")
Range("C8").Value = Format(Range("B8"), "Standard")
Range("C9").Value = Format(Range("B9"), "Fixed")
Range("C10").Value = Format(Range("B10"), "Long Time")
Range("C11").Value = Format(Range("B11"), "Short Time")
End Sub
```

**Step 3:**

Finally, run the code.

**Notice:**

The following things are essential in the above **VBA** code.

- Worksheet name: Here, the worksheet name is “
**Format_func**” - Logic: The Format function returns the value in a specific format.
- Input Cell: Here, the input cell is
**B5**,**B6**, etc. - Output range: The output range is
**C5**,**C6**, etc.

After running the code, the output will be as follows.

### 4. Cell Format Utilizing LEFT, MID, RIGHT, LEN & FIND Function

Right now we’ll see some applications of **LEFT**, **RIGHT**, **MID**, **FIND**, and **LEN **function.

The **RIGHT** function is designed to return a specified number of characters from the rightmost side of a string. We can use the RIGHT function to remove left characters from “Order ID-Items”.

The formula will be-

`=RIGHT(B5,LEN(B5)-C5)`

Here, **B5 **is the cell of combined “Order ID-Items”, **C5 **is the “Num chars”.

Now, we have found the items in a text only cell format.

Moreover, we can also format the cells in numeric format from a combination text-number(“Order ID-Items”).

The formula will be-

`=VALUE(LEFT(B5,(LEN(B5)-C5)))`

Here, **B5 **is the cell of combined “Order ID-Items”, **C5 **is the starting cell of “Chars”.

Furthermore, the **MID** function can be used to extract the last from the ‘Name of orderer’.

The formula will be-

`=MID(C5,SEARCH(" ",C5)+1,LEN(C5)-SEARCH(" ",C5))`

Here, **C5 **is the starting cell of “Name of Orderer”

Last but not the least, we can utilize the **FIND** function to get the First name from the Name of orderer’.

The formula will be-

`=MID(C5,1,FIND(" ",C5)-1)`

Here, **C5 **is the starting cell of “Name of Orderer”.

These are not the conventional ways of formatting cell, but can be helpful while extracting to organize in a specific cell format.

## Conclusion

This is how you may adjust cell format using formula in Excel. I firmly believe this article will enhance your capability to do cell formatting in Excel. However, if you have any queries and suggestions, do not forget to share in the comments section below.