How to Use Excel Cell Format Formula (4 Effective Methods)

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.

What is Cell Formatting in Excel?

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

Cell Format Formula in Excel: 4 Suitable Examples

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 on 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.

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

As you see in the explanation on the top-right side of the above picture, the TEXT function returns the given value in 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, and 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.

Here, C5 is the cell for the 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:

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 that are from “Ohio” states, you can also use the following formula by utilizing the conditional formatting tool.

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

You see that the whole item that 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.

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

The above formula utilizes the AND and NOW functions. 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.

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-

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 a price 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.

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 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 functions.

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 characters from left from “Order ID-Items”.

The formula will be:

Here, B5 is the cell of combined “Order ID-Items”, and 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:

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:

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

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

The formula will be:

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

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

Download Practice Workbook

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 or suggestions, do not forget to share them in the comments section below.

Related Articles

• How to Copy Cell Format in Excel
• [Solved] Excel Formatting Not Working Unless Double Click Cell