Skip to main content

How to Use Advance Number Formatting and Flash Fill?

Introduction of Advance Number Formatting

In this article, we will focus on Advance Number Formatting and create a formula in the number tab in Excel.

Generally speaking, Advance Number Formatting does not alter the number, actually it changes a value that appears in a cell or range of cells. A more accurate method of determining a cell's true value is to focus on the value as it appears in the formula bar.

Advance Number Formatting

To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value.

For Example, the following format displays numbers that are less than or equal to 5000 in a red font and numbers that are greater than 5000 in blue font.

Just select the cell with the number you want to format and hit (Ctrl + 1) and go to the number tab in the dialog box, select the Custom category and enter [Red][<=5000];[Blue][>=5000].


Hey, Are you curious know about advanced formattings for numbers and text;

Here is some example; If you need a different color for Positive, Negative, Zero values, and Text. Where are Blue mean Positive Number, Red means Negative Numbers, Green means Zero Number, Yellow mean Text.

Press (Ctrl +1), Click on custom under number tab, and enter; [Blue]General;[Red]General;[Green]General;[Yellow]General.


Follow the below mention for some other different types of Advance Number Formatting;

  1. [Red][<=10]0;[Green][>=50]0;[Black]0 Using “If, Elseif, Else” in a number format: if the value is <=10, display the value with Red Text, Else if the value is >=50, display the value with Green Text, Else display the value with Black.
  2. (;;;@) Suppresses numbers in cells. This format hides Positive, Negative, and Zero values, and displays only Text.
  3. (;;;) Suppresses numbers in cells. This format hides Positive, Negative, Zero value, and Text.
  4. (00000) Always displays 5 digits. Pads with leading zeros if the number contains fewer than 5 digits. This custom format is very useful when you work with zip codes.
  5. (0;-0;;@) Suppress zeros in cells. This format displays Positive values, Negative values, hides Zero values, and displays Text.
  6. (@*-) Show text leaders. In a number format, the asterisk (*) causes Microsoft excel to repeat the next character until the width of the column is filled. Text leaders are commonly used in tables of contents.
  7. [<=30]” Low”* 0;[>=80]” High”* 0;” Average”* 0 Using “If, Elseif, Else” in a number format: if the value is <=30, display the word “Low” with the value, Else if the value is >=80, display the word “High” with the value, Else display the word “Average” with value.
  8. (DDDD) The date will be displayed as a weekday.

How to Use Flash Fill in Excel Sheet?

One of the most useful and time-saving tools Microsoft had introduced in office 2013 is flash fill in Excel. Flash fill takes tedious tasks, often performed manually, and executes them automatically. You don’t have to insert a single formula – Excel learns and recognizes patterns of what you are trying to do and fills in the data for you in a flash fill.

Turn on Flash Fill: Flash Fill in on by default and automatically fills your data when it senses a pattern. However, if it's not working as expected, here is how you can check if Flash fill is turned on.

Follow the below step for on Flash Fill;

File Tab> Options.

Click on Advanced and click on Check Box now you are able to use Flash fill in Excel sheet.

Refer below image


How to Combine Two Columns Data into One Column?

In our daily work, We work on heavy data in excel and you need to combine the column data. Suppose You need to combine two columns of names [First Name & Last Name] and you want to combine them into one single column with the full name. Type First and Second names in column C, just press (Ctrl + E) for other names as we know Flash Fill.


How to Split Column Data from One Column to Two Column?

You can use flash fill if you want to split data from one cell to multiple cells. Perhaps you have a column with a full name but you want columns with first name and last name or you have a column with prices where you want to split numbers with numbers and currencies into two different columns. Type first and second names in a different column and use flash fill or press (Ctrl + E).


How to Remove Space from Data?

Flash fill can also be used if you want to clean or modify data. Perhaps you have a column with city names where some of them begin with a space? Just type in the city name the way it is supposed to be written and use flash fill or (Ctrl+E) will remove all space.


Reverse Order: Suppose you are maintaining data with First Name and Last Name. Now you want this into reverse order. Just type the Last Name then First Name and use Flash Fill (Ctrl+E) will pick out the rest for you.


© 2021 Sohan Lal Sharma

Related Articles