Conditional Formatting in Excel - Create New Rule, Multiple Conditional Formatting, Basic Uses

In Microsoft Excel, Conditional Formatting is a very good tool to view or identify specific data very quickly. Conditional formatting is very popular and very useful tool because of its' property to highlight data based upon one or more condition provided. 

Conditional formatting is also able to highlight specific data depending upon the result of a given formula.

Conditional formatting is really a powerful feature in Excel when it comes to applying different formats to data that meets certain conditions. It can help you to highlight the most important information in your spreadsheets and identify variances of cells' values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners.

Please read this article till the end, practice this tool in Excel and you will be able to master this tool very beautifully.

So, friends, lets' learn it.

Excel conditional formatting - The Basic

We use conditional formatting in Excel to format our data in different ways by changing cells' fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain criteria or conditions or some output of formula.

You can apply conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell's value. We do this by creating rules or conditions where we define when and how the selected cells should be formatted.

Under the Home tab you can find Conditional formatting in the Styles section as shown in the below picture.

Conditional Formatting

When we click on the Conditional Formatting button we see a drop down list as shown in the below image.

Conditional Formatting

Conditional formatting in Microsoft Excel is one of the flexible tools as it allows you to use custom rules and in built rules. Let us discuss about the inbuilt or default rules in conditional formatting in Microsoft Excel. 

How to create Excel conditional formatting

In Home tab under Styles you will find Conditional formatting button, under this drop down list the first option is Highlight Cells Rules which is also sub categories into eight categories, each of these categories are self explanatory.

Default Rules in Excel Conditional Formatting

Conditional formatting rules in Excel define 2 key things:

• What cells the conditional formatting should be applied to, and

• Which conditions should be met.

Steps

1. In your Excel spreadsheet, select the cells you want to format then

2. Go to the Home tab > Styles group and click Conditional Formatting. You will see a number of different formatting rules.

3. Suppose we need to apply conditional formatting only to the Target Column's numbers Greater than 30.5, we choose Highlight Cells Rules > Greater Than... as shown in the below picture.

Default Rules Greater than in Excel Conditional Formatting

Of course, you can go ahead with any other rule type that is more appropriate for your data, such as:

o Format values greater than, less than, between, equal to

o Highlight text containing specified words or characters

o A date occurring

o Highlight duplicates

o Format specific dates

4. Select the format you want from the drop-down list. You can choose one of the pre-defined formats or click Custom Format... to set up your own formatting as shown in the below picture.

Custom Formatting in Excel Conditional Formatting

5. When you click on the OK button a formatting window will open where you can customize cell or font color according to you.

Formatting Window

Creating an Excel conditional formatting New Rule

If none of the ready-to-use formatting rules meets your needs, you can always create a new one. To create your own Conditional Formatting rule you need to follow few steps.

1. Select the cells to which you want to apply the conditional format and click Conditional Formatting > New Rule.

Conditional Formatting through New Rule

2. The New Formatting Rule dialog opens and you select the needed rule type. For example, let's choose "Format only cells that contain" and opt to format the cell values between 30 and 40.

Conditional Formatting through New Rule

3. Click the Format... button and set up your formatting.
Click OK to close the open windows and your conditional formatting is done!

Conditional Formatting Applied using New Rule

Excel conditional formatting based on cell value

Sometimes we need to format cells depending on the value they contains, Microsoft Excel's Conditional Formatting provides us options to do this. 

For Example if we need to highlight the cells in Target column containing value 22, we can use this property of Conditional Formatting to highlight those cells.

Steps are

1. Select the Column or select the range

2. Click on Conditional Formatting >Format all cells based on their values

3. Now select the formatting as per your requirement as shown in the below picture.

Conditional Formatting Based upon Cell Value

Multiple conditional formatting rules

When using conditional formatting in Excel, you are not limited to only one rule per cell. You can apply as many rules as your project's logic requires.

1. Click Conditional Formatting > Manage Rules... to bring up the Rules Manager.

Conditional Formatting with Multiple Rules

2. Click New Rule, this will open the New Formatting RUle window.

3. Click Format Only Cells That Contains for example,

4. Click on the drop down list near Cell Value and select Equal to and type 22 in the box

5. Select Format which will open Format Cells window

6. Select proper formatting and click OK twice.

7. The Rules Manager will look like the below image.

Conditional Formatting with Multiple Rules

8. Now if you want to enter another rule Click on New Rule and repeat the steps above.

9. You can Delete or Edit rules by clicking Delete Rule or Edit Rule button.

Conditional Formatting with Multiple Rules

This Steps on Conditional Formatting the Basics, in another article I will discuss about the advanced conditional formatting.
Hope you have enjoyed this article. Thank you for reading...

Excel Sort By Column, By Row, By Color, By Your Own Custom List

'Sort' is one of the tricky and very useful tools available in Microsoft Excel. This tool has immense power to reduce our job by several times. In this article I will show you How to Sort data Column wise, Row wise, Color wise and How to Customize Sort with your own list and many more things in detail step by step.

So, lets' start from basic to advanced level of Sort.

How to sort by column alphabetically or in ascending / descending order

It is very simple, all you need to do is click the Sort A to Z or Sort Z to A buttons available on the Home tab in the Editing group and on the Data tab in the Sort & Filter group as shown in the below picture.

Basic Sort option in Excel

Microsoft Excel's Sort feature provides more options and powerful capabilities that are not very difficult to master. Let us discuss Sorting of Columns and Rows.

How to Sort by columns, More than One

Sorting one column is very easy as mentioned above now I'm going to show you how to sort Excel data by two or more columns step by step.

1.  Go to Data Menu and Click the Sort button or Custom Sort on the Home Menu to open the Sort dialog. I  personally prefer to use sort from Data menu. This will open a new window as shown below.

Sort Window in Microsoft Excel

2.  Then click the Add Level (Circled in blue) button as many times as many columns you want to use for sorting

3.  From the "Sort by" and "Then by" drop down lists, select the columns by which you want to sort your data.

In the below picture I have selected Region, Territory Code and Employee Code. Now here we get few additional options Sort On & Order, if you click on Sort On drop down which in the picture is beside 'Values' word, you will find few more options which we will discuss letter. Order is how you want your sorting to be, from A to Z or Z to A and for numbers either ascending or descending.

Sort on Multiple Column

4.  Click OK and you are done, the table is now sorted.

Sometimes you may notice that Column Names are not showing in the Sort window, as shown in the below picture. To resolve this click to put a tick mark in the checkbox (my data has headers) at the right corner of this window circled by blue color and now Column headers will be visible.

Sort Multiple Column with Column Header

How to Sort by Rows

Generally we use sorting to data for columns only but some situations come when we need to sort data row wise, therefore, lets' see how it is done.

1.  Go to Data Tab click on Sort option which will open Sort window.

2.  Click on the Option button at the right corner of this window which will open another small window as Sort Options

Sort by Rows - Left to Right

3.  Here you will find three one checkbox asking whether sorting to be done in Case sensitive mode or not.

4.  Now there are two more options Sort top to bottom meaning Column wise sort and Sort left to right meaning row wise sorting. Select Sort left to right option and click Ok.

5.  The next process is same as we have done for column wise sorting i.e., Add Level, Sort on and Order.

Please refer to the below image which I have sorted row wise (row 1, 2 & 3)

Sort by Row

How to Sort by Color

This part of Microsoft Excel is most interesting and amazing. While preparing data for analytical purpose this property of Sort will help you most, Sort by Cell color or Sort by Font color or Sort by Cell icon. For example let suppose I have one column "Product 1" in one table as shown below with four colors green, red, blue and yellow.

Sort by Cell Color

Now if I need to sort it by Blue, Green, Yellow and lastly Red then firstly I have to open Sort window from Data and then by clicking on Sort button.

Now select Product 1 from the Sort by Drop down, then click on Sort on drop down list and select Cell Color, a new option will appear "No Cell Color", click in this drop down button.

Now you can see the same four colors which were applied in the Product 1 column. Select any one color, in my case it is Blue. Now you need to insert the same level, lets' use a shortcut, click on Copy Level, the same level will appear, select your color order one by one, click OK and you are done.

How to Sort by Cell Color

How to Sort data in custom order (using your custom list)

If you want to sort your data in some custom order other than alphabetical, you can use the built-in Excel custom lists or create your own. With built-in custom lists, you can sort by days of the week or months of the year. Microsoft Excel provides two types of such custom lists - with abbreviated and full names, as shown below

Sort by Custom List

Suppose we need a custom list to sort on regions as North then East then West and lastly South.

To do this we need to do the following steps

1.  Open the Sort window from Data then Click on Sort button,

2.  In the Sort by box, select the column you want to sort by, in our case it is Region. Then choose Custom List under Order as shown below

Sort by Custom List

3.  In the Custom Lists dialog box, click on New List, and enter the list items, in our case these will be North, East, West, South, separated by comma. Click OK.

Sort by Your Custom List

4.  Again click on OK button in the Sort window and you are done.

Sort by Your Custom List

Thank you friends for reading...
Stay blessed and grow your Excel Skill...

You can also view the YouTube video here about SORT in Excel.

Filter Text Number Date and Filter by Color in Microsoft Excel AutoFilter Shortcuts Step by Step

Data collection, data organization and data analysis, the main purpose of these steps is to find the relevant information for an organizations. Sometimes we only need to view the data for specific items. For example suppose your business territory is divided into four regions, North, South, East and West. Now let suppose you need to view the performance of North region and the South and so on. If you are working with large data then you might find that North's few rows are in 3rd and 4th rows and then East has began and then again North's data. In this situation FILTERs and SORT helps us greatly. Filter & Sort are very useful to analyze data, therefore, learn how to use. Filters can be used to find or display data according to cell or font color.

Lets' find out how to work with this essential tool in Microsoft Excel. We shall discuss about FILTER in this article today.

What is filter in Excel?

Microsoft Excel Filter or AutoFilter, a quick way to display only the information relevant at a given time and hides all other data from view. You can filter rows in Excel worksheets by value, by format and by criteria. After applying a filter, you can copy, edit, chart or print only visible rows without rearranging the entire list.

Filter in Excel

Once the column headings are in pace, select any cell within your data, and use one of the following methods to insert filter.
How to add filter in Excel

1.   On the Data tab, in the Sort & Filter group, click the Filter button.

How to Filter

2.   On the Home tab, in the Editing group, click Sort & Filter > Filter.

How to Filter

3.   Or you can use the Excel shortcut to turn the filters on or off,  Ctrl+Shift+L (or you can use Alt+D+F+F)

4.   Whatever method you use, the filter drop-down arrows will appear in each of the header cells and you can now filter by selecting items.

How to apply filter in Excel

A drop-down arrow in the column heading appearing means that filter is added, but not yet used. When you stays on this down arrow icon or hover over the arrow, a screen tip displays "Column Header: (Showing All)".

To filter data in Excel, you need to do the following:

1.   Click the drop-down arrow for the column you want to filter.

2.   Uncheck the Select All box to quickly deselect all data.

3.   Check the boxes next to the data you want to display, and click OK.

Use of Filter in Microsoft Excel

And you are done! The filter is applied to column B, temporarily hiding any regions other than East.

The drop-down arrow in the filtered column changes to the Filter button, (a small down arrow displayed on the left of this button) and hovering over that button displays a screen tip indicating which filters are applied:

How to know Filter has been applied

Filtering multiple columns

You can repeat the above steps for as many columns as you want and you are done for multiple selection.

For example, after selecting East region from the region Column header, you may further be interested to filter Territory Code T-14 or  something else. Just repeat the same steps.

How to use filter in Excel - AutoFilter

Apart from basic filtering options discussed above, AutoFilter in Excel provides a number of advanced tools that can help you filter specific data types such as text, numbers and dates exactly the way you want.

And now, let's have a closer look at each option and see how you can create a filter most suited for your data type.

In the filter drop down you will find one menu Text Filters and within this menu you will find some sub-menus also like Equals, Does not Equals, Contains, Does not Contains etc., as shown in the below image. These filters are advanced level filtering method and if you need specific filtered data, you can use these filter options. I shall discuss most of it.

Excel Text Filter

Filter Text Data

•   Filter cells that are exactly equal or does not equal to a specified data, as per your requirement and logic.

•   Filter cells that begin with or end with a specific character(s). This is very important feature in filter.

•  Filter cells that contain or do not contain a given character or word anywhere in the text.

Example :
If you click on Filter button and then Text Filters and then Equals Submenu, a small window opens (as shown in the below picture) and again click on the Drop Down Button where Equal word is displayed (as shown in the below image) you will find few options in that drop down list which is self explanatory.

Customize AutoFilter

Customize AutoFilter

Filter column with two criteria

To filter data in Excel with two text criteria, you need to do the following:

Apply Filter in the Columns, Click on Text Filters, Click Equals and Check And or Or radio button depending on whether both or either criterion should be true. In my case I've selected OR to see the data of East and North together as shown in the below image.

Multiple Filter in Excel

•   Select the comparison operator for the second criterion, and enter a text value in the box right to it.

How to create filter in Excel with wildcard characters

If you don't remember exact search or want to filter rows with similar information, you can create a filter with one the following wildcard characters:

Wildcard in Excel Filter

How to filter numbers in Excel

Excel's Number Filters allow you to filter out numeric data in a variety of ways, for example,

•         Filter top 10 or bottom 10 numbers.

•         Filter numbers equal or not equal to a certain number.

•         Filter numbers, greater than, less than or between the specified numbers.

•         Filter cells with numbers that are above average or below average.

Use of Filter to Number in Excel

How to filter dates in Excel

Excel Date Filters provide lots of varieties of choices that let you filter records for a certain time period quickly and easily.

By default, Excel AutoFilter groups all dates in a given column by a hierarchy of years, months, and days. You can expand or collapse different levels by clicking the plus or minus signs next to a given group. Selecting or clearing a higher level group selects or clears data in all nested levels.

In addition, Date Filters allow you to display or hide data for a particular day, week, month, quarter, year, before or after a specified date, or between two dates. The screenshot below demonstrates all available date filters:

Apply Filter to Date in Excel

You can customize filter for date.

Customize Date Filter

How to filter by color in Excel

If the data in your worksheet is formatted manually or through conditional formatting, you can also filter that data by color.

Clicking the autofilter drop-down arrow will display Filter by Color with one or more options, depending on which formatting is applied to a column:
      Filter by cell color
      Filter by font color
      Filter by cell icon

How to copy filtered data in Excel

The fastest way to copy a filtered data range to another worksheet or workbook is by using the following 3 shortcuts.

1.   Select any filtered cell, and then press Ctrl + A to select all filtered data including column headers.
To select filtered data excluding column headers, select the first (upper-left) cell with data, and press Ctrl + Shift + End to extend the selection to the last cell.

2.   Press Ctrl + C to copy the selected data.

3.   Switch to another sheet/workbook, select the upper-left cell of the destination range, and press Ctrl+V to paste the filtered data.

Usually, when you copy the filtered data elsewhere, filtered-out rows are omitted. In some rare cases, mostly on very large workbooks, Excel may copy hidden rows in addition to visible rows. To prevent this from happening, select a range of filtered cells, and press Alt + ; to select only visible cells ignoring hidden rows. If you're not accustomed to using keyboard shortcuts, you can utilize the Go To Special feature instead (Home tab > Editing group > Find & Select > Go to Special... > Visible Cells only).

How to remove filter in Excel

After applying a filter to a certain column, you may want to clear it in order to filter the data in another way.

To clear filter in Excel, do any of the following.

To remove a filter from a column, click the filter button in the column's header, and then click Clear Filter from <Column name>:

Customize Date Filter

To remove all filters in a worksheet, either:

•         Go to the Data tab > Sort & Filter group, and click Clear.

•         Go to the Home tab > Editing group, and click Sort & Filter > Clear

•         Or You can remove all the filters by pressing Ctrl+Shift+L or Alt+D+F+F

Filter not working in Excel

If Excel's AutoFilter stopped working partway down a worksheet, most likely it's because some new data has been entered outside the range of filtered cells. To fix this, simply re-apply filter. If that does not help and your Excel filters are still not working, clear all filters in a spreadsheet, and then apply them anew. If your dataset contains any blank rows, manually select the entire range using the mouse, and then apply autofilter. As soon as you do this, the new data will be added to the range of filtered cells.

Basically, this is how you add, apply and use filter in Excel. But there is much more to it! In the next tutorial, we will explore and capabilities of Advanced Filter and see how to filter data with multiple sets of criteria. Please stay tuned!

Things to Remember

a)   Column headers should not be blank in between Columns or Filter will be applied only to the continuous field and rest will be ignored.

b)   Different Excel filter types are mutually exclusive. For example, you can filter a given column by value or by cell color, but not by both at a time.

c)   For correct results, do not mix different value types in a single column because only one filter type is available for each column. If a column contains several types of values, the filter will be added for the data that occurs the most. 

For example, if you store numbers in a certain column but most of the numbers are formatted as text, Text Filters will appear for that column but not Number Filters.

Thank you for reading ...
You can also watch the below YouTube video for further explanations.