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.

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

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.

Read more: Formula to Copy Cell Value and Format in Excel

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

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.

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.

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

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.

Similar Readings

• Dealing with Time Format in Excel (5 Suitable Ways)
• How to Custom Format Cells in Excel (17 Examples)
• Use Format Painter in Excel (7 Ways)
• How to Copy Cell Format in Excel (4 Methods)

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.

Read more: VBA to Format Cell in Excel

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

The formula will be-

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-

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 the least, we can utilize the FIND function to get the First name from the Name of 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.

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 them in the comments section below.

Further Readings

• Use Format Painter Shortcut in Excel (5 Ways)
• How to Change Time Format in Excel (4 Ways)
• Copy Formatting in Excel to Another Sheet (4 Ways)
• How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)