HOW TO STRIKETHROUGH IN EXCEL USING CONDITIONAL FORMATTING?

Table of Contents

INTRODUCTION

To have the knowledge of Conditional formatting is very important if we want to apply EXCEL in our day to day jobs.

Conditional formatting is the  formatting [ font, color, fill color , size etc. ] of data as per its value of the content of the cells.

It is one of the most versatile functions present in EXCEL and very easy to apply and learn.

It makes our task so easy which you can understand from the following example.

Suppose we have a group of 100 entries and we need to identify the entries between a very small range say 70 to 85. So conditional formatting lets us, set up a rule to highlight such cells with some color to immediately take our notice of those cells. Not just this but we can put a rule for ranges to highlight the cells with different colors. It can be applied to thousands of cells.

WHERE DO YOU FIND CONDITIONAL FORMATTING OPTION IN EXCEL?

CONDITIONAL FORMATTING OPTION can be found under the INSERT TAB.

STEPS TO OPEN :

  • Go to the INSERT TAB.
  • Choose CONDITIONAL FORMATTING.
  • The options will appear on the right side of the screen.
BUTTON LOCATION: CONDITIONAL FORMATTING

EXAMPLE 1:STRIKETHROUGH THE NUMBERS LESS THAN A CERTAIN NUMBER USING CONDITIONAL FORMATTING

The following picture shows the sample data we will be using in the example. Let us learn conditional formatting using a simple example.

We have data with different numbers. Let us highlight the numbers less than 100.

HIGHLIGHT THE NUMBERS LESS THAN 100 IN THE GIVEN DATA

The data used is shown below.

DATA SAMPLE: CONDITIONAL FORMATTING

STEPS TO HIGHLIGHT NUMBERS LESS THAN 100 USING CONDITIONAL FORMATTING

STEPS TO APPLY CONDITIONAL FORMATTING:

  1. Select the data. [ONLY THE CELLS WITH THE DATA ON WHICH WE WANT TO APPLY CONDITIONS].
  2. Go to INSERT TAB> HIGHLIGHT CELLS RULES> LESS THAN.

SELECT THE TABLE AND CHOOSE CONDITIONAL FORMATTING

  1. A small LESS THAN dialog box will open.
  2. Enter the LESS THAN value.
  3. Click the DropDown and choose CUSTOM FORMAT as shown in the picture below.
CHOOSE CUSTOM FORMAT
  1. After choosing the custom format, the Format Cells dialog box will open as shown in the picture below.
  2. Click STRIKETHROUGH and click OK.

CLICK STRIKETHROUGH
  1. We’ll again be back to the smaller dialog box with the selected settings 100 and custom format.
  2. Click OK.
  3. The following result will appear.

RESULT



EXAMPLE 2:REMOVE STRIKETHROUGH CONDITIONAL FORMATTING FROM THE CELLS IN EXCEL

Sometimes, we might need to remove all types of conditional formatting from our sheet.

It can be done using the following ways.

1.REMOVE STRIKETHROUGH CONDITIONAL FORMATTING FROM THE SELECTED CELLS

  1. Select all the cells in which you want to remove the conditional formatting.
  2. Go to HOME TAB>CONDITIONAL FORMATTING>CLEAR RULES>CLEAR RULES FROM SELECTED CELLS.[ Shown as 1 in the picture below.]
  3. All the STRIKETHROUGH conditional formatting will be removed i.e. All the cells containing the strikethrough data will be removed.

2.REMOVE STRIKETHROUGH CONDITIONAL FORMATTING FROM THE ENTIRE SHEET

  1. Select all the cells in which you want to remove the conditional formatting.
  2. Go to HOME TAB>CONDITIONAL FORMATTING>CLEAR RULES>CLEAR RULES FROM ENTIRE SHEET.[ Shown as 2 in the picture below.]
  3. All the STRIKETHROUGH conditional formatting will be removed i.e. All the cells containing the strikethrough data will be removed.
    * This method will remove all other types of conditional formatting from the entire sheet.

REMOVE STRIKETHOUGH CONDITIONAL FORMATTING

In this article we learnt the ways to strikethrough the specific data using conditional formatting and removing the same when needed.