조건부 서식은 이제 Microsoft Excel 환경의 필수적인 부분입니다. 이는 무작위 또는 대규모 데이터 그룹에서 특정 값을 식별하는 데 도움이 됩니다. 특정 값을 식별할 수 있을 뿐만 아니라 특정 범위에 속하는지 여부, 문자열 값, 숫자 값, 오류 등을 조건부 서식으로 식별할 수 있습니다. 이름에서 알 수 있듯이 Excel은 조건이 true인지 여부에 따라 셀 서식을 지정합니다. 이 자습서에서는 Excel의 수식을 사용하여 조건부 서식에 대해 설명합니다.
Excel에서 수식과 함께 조건부 서식 사용에 대한 비디오 자습서
이 비디오 강의에서:
- 수식 기반 조건부 서식을 사용하여 셀 서식을 지정하는 방법을 배웁니다.
- ISTEXT 함수에 대해 배우게 됩니다. .
- 수식에서 절대 참조를 사용하는 방법을 다시 배우게 됩니다.
아래 다운로드 링크에서 데모에 사용된 워크북을 다운로드할 수 있습니다.
Excel에서 수식과 함께 조건부 서식을 사용하는 방법
Excel에서 수식과 함께 조건부 서식을 사용하는 주요 주제는 매우 간단합니다. 조건부 서식 기능을 선택해야 합니다. 그런 다음 미리 결정된 옵션을 선택하는 대신 새 규칙을 선택한 다음 수식을 선택하여 입력해야 합니다.
수식을 통해 일반적으로 조건부 서식 상자에 조건을 입력합니다. 조건이 true이면 형식이 적용되고 그 반대의 경우도 마찬가지입니다.
다음은 Excel의 수식을 사용한 조건부 서식에 대한 자세한 데모입니다.
단계:
- 먼저 수식으로 서식을 지정할 셀 범위를 선택합니다.
- 이제 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택하십시오. 스타일 에서 그룹 섹션.
- 그런 다음 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 결과적으로 새 서식 규칙 이제 상자가 나타납니다.
- 그런 다음 이 수식이 참인 값의 서식 지정: 아래 필드에 수식을 입력할 수 있습니다.
- 형식 을 클릭하여 형식 스타일을 선택할 수도 있습니다.
- 위의 모든 단계와 셀의 서식 스타일을 완료했으면 확인 을 클릭하십시오. 이 상자에.
예를 들어, 이 모든 단계를 수행한 후 그림에 표시된 다음 공식을 입력합니다.
스프레드시트의 범위는 B3:B6입니다. . 이제 다음과 같이 보일 것입니다.
여기서는 셀 값이 3보다 높다는 조건을 입력했습니다. 따라서 이 선택에는 3보다 큰 값이 있는 경우 형식이 적용됩니다.
21 Excel에서 수식과 함께 조건부 서식을 사용하는 예
이제 이 섹션에서는 Excel의 조건부 서식 기능에서 수식을 사용하는 다양한 예를 보여줍니다. 이 기능을 사용하고 서로를 결합할 가능성은 매우 큽니다. 그래서 우리는 당신이 유익하다고 생각하는 몇 가지 주요 공식을 다루려고 노력했습니다. 특정 항목만 필요한 경우 위의 표에서 찾을 수 있습니다.
1. 텍스트 값 서식 지정
먼저 숫자와 문자열 값을 모두 포함하는 이 데이터세트를 살펴보겠습니다.
수식을 사용한 조건부 서식을 사용하여 Excel에서 문자열 값을 쉽게 골라낼 수 있습니다. 그러기 위해서는 ISTEXT 기능을 활용해야 합니다. . Excel의 조건부 서식에서 이 함수를 사용하여 서식을 지정하는 방법을 보려면 다음 단계를 따르세요.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=ISTEXT(B5)
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
조건부 서식에서 이 수식을 사용한 후 Excel에서 데이터 세트의 모든 텍스트 서식을 지정한다는 것을 알 수 있습니다.
2. 다른 셀과 동일한 셀 강조 표시
이제 일치하는 값의 다른 예를 살펴보겠습니다.
이 데이터세트에는 방대한 데이터 모음이 있습니다. 이제 E5의 셀 값과만 일치하는 형식을 지정합니다. .
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=B5=$E$5
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이것이 Excel의 수식을 사용하여 조건부 서식을 사용하여 다른 셀과 동일한 셀을 강조 표시하는 방법입니다.
3. 다른 셀을 기반으로 하는 Excel의 조건부 서식
이제 동등함에서 조금 이동해 보겠습니다. 이제 이 섹션의 다른 셀 값보다 크거나 작은지 여부에 따라 데이터 세트의 형식을 지정합니다. 그리고 Excel의 수식 방법을 사용하여 조건부 서식을 사용하여 강조 표시합니다.
다음 데이터세트에서 이 작업을 수행합니다.
C5:C9 범위의 형식을 지정하겠습니다. E5 셀의 값을 기반으로 함 .
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=C5>$E$5
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. . 이것이 이제 데이터세트가 됩니다.
- 마찬가지로, 더 낮은 값에 대해 동일한 과정을 반복하면 이와 같은 데이터세트가 생성됩니다.
이것이 Excel에서 다른 셀의 값을 기반으로 하는 수식과 함께 조건부 서식을 사용하는 방법입니다.
4. Excel에서 IF 수식을 사용한 조건부 서식
이제 IF 함수가 포함된 수식을 활용하는 방법을 살펴보겠습니다. Excel의 조건부 서식 수식으로 조건부 서식 상자에서 함수를 직접 사용할 수 없습니다. 그러나 이 함수를 사용하여 Excel에서 조건부 서식을 사용하여 셀 서식을 추가로 지정하는 데 사용할 수 있는 값을 얻을 수 있습니다.
먼저 이 예제에 대한 데이터 세트를 가져오겠습니다. 이것이 우리가 이 작업을 수행하고 있는 것입니다.
단계:
- 먼저 E5 셀을 선택합니다. 다음 공식을 적으세요.
=IF(D5>C5,"Profit","Loss")
- 그런 다음 Enter 키를 누릅니다. 키보드에서.
- 이제 채우기 핸들 아이콘을 클릭하고 열 끝으로 끌어 나머지 셀에 대한 수식을 복제합니다.
- 다음으로 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=E5="Profit"
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이것이 이제 데이터세트가 됩니다.
이것이 IF 를 활용하는 방법입니다. Excel의 조건부 서식 수식에 대한 함수입니다.
5. 여러 조건을 사용하여 셀 강조 표시
이제 첫 번째 데이터세트로 돌아가 보겠습니다.
이제 5,6이거나 "cat" 텍스트가 포함된 모든 셀을 강조 표시합니다.
Excel의 조건부 서식 수식 상자에서 이를 수행하려면 OR 함수가 필요합니다. .
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=OR(B5=5,B5=6,B5="cat")
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이제 데이터 세트를 살펴보십시오.
이것이 또는 을 사용하는 방법입니다. 여러 조건에 대해 Excel의 조건부 서식 수식에서 함수
6. 중복 행 강조 표시
계속해서 전체 행이 다른 행과 일치하는 전체 행의 셀을 강조 표시합니다. 조금 까다롭습니다. COUNTIF 가 필요합니다. 및 연결 이를 위한 기능입니다.
시연을 위해 샘플 데이터 세트를 사용하겠습니다.
여기에서 세 번째와 여섯 번째 행이 완전히 일치하는 것을 볼 수 있습니다. 다음 단계에 따라 이러한 기능을 활용하여 Excel의 열에서 중복 값을 강조 표시하는 방법을 확인하십시오.
단계:
- 먼저 F5 셀을 선택합니다. 다음 공식을 적으세요.
=CONCATENATE(B5,C5,D5,E5)
- 그런 다음 Enter 키를 누릅니다. .
- 이제 셀을 다시 선택하고 채우기 핸들 아이콘을 클릭하고 끌어 나머지 셀을 참조 공식으로 채웁니다.
- 조금 지저분해 보이지만 일단 무시하세요. 이제 B5:B10 범위를 선택합니다. .
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=COUNTIF($F$5:$F$10,$F5)>1
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. . 이것이 이제 데이터세트가 됩니다.
- 이제 F 의 열 헤더를 마우스 오른쪽 버튼으로 클릭해 보겠습니다. 숨기기 를 선택하십시오. 컨텍스트 메뉴에서.
이것이 그 후의 최종 스프레드시트 보기가 됩니다.
7. 수식이 포함된 셀 값 강조 표시
이제 완성할 수식이 포함된 데이터세트를 살펴보겠습니다.
이러한 셀을 강조 표시하기 위해 수식을 사용하는 조건부 서식을 사용할 것입니다. 이러한 이유로 ISFORMULA 함수를 사용해야 합니다. .
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=ISFORMULA(B5)
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이것이 이제 데이터세트가 됩니다.
8. 특정 지역의 매출 강조
이 예에서는 특정 지역에 속한 데이터 세트의 매출을 강조 표시합니다. 다음 데이터세트를 사용하겠습니다.
이제 애리조나에서 온 것을 강조 표시해야 한다고 가정해 보겠습니다. 그렇게 하려면 다음 단계를 따르세요.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=$D5="Arizona"
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
Excel은 이제 이러한 단계의 결과로 Arizona의 판매를 표시합니다.
9. 열 차이점 강조 표시
인접한 열과 다른 열이 있는 행을 강조 표시할 수도 있습니다. 예를 들어 다음 예를 살펴보겠습니다.
Excel에서 수식과 함께 조건부 서식을 사용하여 강조 표시하려면 다음 단계를 따르세요.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=$B5<>$C5
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이 단계의 최종 결과물이 됩니다.
10. 수식을 사용하여 누락된 값 강조 표시
이 섹션에서는 수식을 사용하여 Excel의 조건부 서식에서 누락된 값을 강조 표시합니다. 다음 데이터세트를 예로 들어 보겠습니다.
목록 1에서 사용할 수 있었던 목록 2에는 누락된 값이 있습니다. 우리는 그것들을 강조할 것입니다. 방법을 보려면 다음 단계를 따르세요.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=COUNTIF($D$5:$D$10,$B5)=0
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
Excel은 이 조건부 서식 수식의 셀을 강조 표시합니다.
11. 셀을 강조 표시하는 간단한 검색 상자 만들기
이제 다양한 변형을 조합하여 재미있는 것을 시도해 보겠습니다. 이를 위한 데이터세트입니다.
여기에서는 E4 셀에 값을 입력합니다. Excel은 수식 방법으로 조건부 서식을 사용하여 이전 범위에서 모두 강조 표시합니다. ISNUMBER 조합이 필요합니다. 및 검색 이 메서드에 대한 기능입니다.
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- 그런 다음 조건부 서식 을 선택합니다. 스타일 에서 그룹 섹션.
- 다음으로 새 규칙 을 선택합니다. 드롭다운 메뉴에서.
- 서식 규칙 상자에서 먼저 수식을 사용하여 서식을 지정할 셀 결정 을 선택하십시오. 규칙 유형 선택 아래의 옵션 .
- 그런 다음 이 수식이 참인 값의 서식에 다음 수식을 삽입합니다. .
=ISNUMBER(SEARCH($E$4,B5))
- 다음으로 원하는 형식 유형을 선택합니다.
- 마지막으로 확인을 클릭합니다. .
이제 데이터 세트를 살펴보십시오. 게임이라는 단어가 포함된 텍스트가 표시됩니다.
E4 셀의 값을 변경하면 , 예를 들어 "건강" 하이라이트가 변경됩니다.
12. 평균보다 작은 값 강조 표시
이제 이전의 데이터 세트 중 하나를 다시 살펴보겠습니다.
여기 Excel에서 수식과 함께 조건부 서식의 기본 응용 프로그램을 사용할 것입니다. 여기에서는 E 열에서 평균보다 작은 값을 강조 표시할 것입니다. . AVERAGE 함수가 필요합니다. 이 목적을 위해.
다음 단계에 따라 이를 수행하는 방법을 확인하십시오.
단계:
- 먼저 헤더를 제외한 데이터세트의 모든 셀을 선택합니다.
- 그런 다음 홈 으로 이동합니다. 리본의 탭입니다.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=E5<AVERAGE($E$5:$E$10)
- Next, select your preferred format type.
- Finally, click on OK .
This will highlight the values that are less than average in the dataset.
13. Highlight Values That Are Greater Than Average
Similar, to the previous one, we are going to highlight the values that are greater than average in this section using the formula method of conditional formatting in Excel. We will have to use the AVERAGE function for this task too. The dataset will be the same.
Follow these steps to see the formula and how you can use it.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=E5>AVERAGE($E$5:$E$10)
- Next, select your preferred format type.
- Finally, click on OK .
Thus Excel will highlight all the values that are higher than average in column E .
14. Find Nearest Value Including Exact Match
Now let’s take another unique example that is practical in data cleaning and analysis. We are going to find the nearest value to a number in a random set of data.
This is the dataset of this example.
As you can see from the figure, we are going to find the value that is closest to the one in cell C13 . If there is the same value in the dataset, it will highlight that cell instead of the closest value. For this purpose, we will utilize the MIN , ABS , and OR functions.
Follow these steps to see how you can do that.
Steps:
- First, we need to perform some calculations. We need to find the smallest difference from this value in that set of data. For that select cell C14 and write down the following formula.
=MIN(ABS(B5:D11-(C13)))
- Then press Enter .
- Now select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)
- Next, select your preferred format type.
- Finally, click on OK .
This will highlight the value that is closest to the value of 1, with conditional formatting formula in Excel.
15. Find Top 3 Values
Let’s go back to the random set of data.
This time we are going to find the top 3 values here using conditional formatting with a formula in Excel. The formula consists of the LARGE function .
Follow these steps to see how we can do that.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=B5>=LARGE($B$5:$D$11,3)
- Next, select your preferred format type.
- Finally, click on OK .
As you can see Excel will thus mark the top 3 values from the range using conditional formatting formula.
16. Find Bottom 3 Values
Again, we will use the same dataset as before. But this time we will use that to highlight the bottom 3 values from the dataset. Just like before we are gonna need the SMALL function for the formula. To see how we can do that, follow these steps.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=B5<=SMALL($B$5:$D$11,3)
- Next, select your preferred format type.
- Finally, click on OK .
This time, Excel will mark the bottom 3 values because of the conditional formatting formula.
17. Show Temperature with Color Scale
In this next example, let’s try another interesting usage of formula and conditional formatting in Excel. Here is the dataset for that.
We have taken two columns because we will use a formula to change the color based on the input in cell E5 . Also, there is a blank row at the start of the dataset. We will need that to utilize the formula we are going to use. The formula consists of IF and AND 기능. To see how you can change the color scheme of these temperatures based on the current temperature, follow these steps.
Steps:
- First, select cell C6 and write down the following formula in it.
=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))
- Then press Enter .
- Now select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.
- Next, go to Conditional Formatting from the Styles group of the Home
- Then hover your mouse over the Color Scales .
- After that, select your preferred color scale.
- The temperature scale for conditional formatting based on the formula for current temperature will now be complete.
- If we change the value of the current temperature in cell E5 , the temperature scale will change accordingly.
This is how we can show the temperature with color scale utilizing formula and conditional formatting in Excel.
18. Highlight Alternate Rows with Conditional Formatting
Now let’s go back to one of the previous datasets of random numbers.
With this dataset, we will highlight alternate rows with a color using the conditional formatting formula in Excel. For the formula, we will need the INT and MOD functions.
The steps to do that are below.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=INT(MOD(ROW(),2))
- Next, select your preferred format type.
- Finally, click on OK .
This will highlight the alternate rows using the conditional formatting formula in Excel.
19. Highlight Cells with Error
Next up, we will be using the conditional formatting formula in Excel to highlight cells with errors. This is particularly helpful with a large number of data in a sheet and where finding errors manually is tiresome. For demonstration, let’s take a look at the following dataset.
There contains two errors in the dataset. We are going to use the ISERROR function in the conditional formatting formula to detect them. The formula used below will help us identify them using conditional formatting in Excel.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=ISERROR(B5)
- Next, select your preferred format type.
- Finally, click on OK .
Excel will thus highlight all the errors as a result of using the conditional formatting formula.
20. Create Checklist with Conditional Formatting
Now let’s try another fun application of conditional formatting with a formula in Excel. This time we will make a checklist beside a set of data. With the options checked, the original data will change its format. We will use the following dataset.
This is a standard to-do list. Once we check a checkbox in the status column, the task beside it will change the format, let’s say a strikethrough. The basic idea is to use the boolean value of the checkbox to format in column B . Also, we will need the Developer tab to insert checkboxes. If you don’t have one in your ribbon, click here to display the Developer tab on your ribbon .
Follow these steps to see how we can use the Excel conditional formatting formula to create a checklist in detail.
Steps:
- First, go to the Developer tab on your ribbon.
- Then select Insert from the Controls group section.
- After that, select the Check box (Form Control) from the drop-down menu.
- Then place the box in its appropriate place.
- Next, right-click on the box and select Format Control from the context menu.
- Now go to the Control tab on the box and select cell C5 as its linked cell.
- Then click on OK .
- Now let’s remove the alt text and place it in the middle of the cell to make it look good.
- Now repeat the process for all of the tasks.
- Once checked, there will be a TRUE/FALSE value on the cells depending on it.
- So let’s use a white font color to make them invisible.
- Now select the task range you want to format.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=C5=TRUE
- Next, select your preferred format type.
- Finally, click on OK .
Now once we check a check box beside a task, it will format the task beside it. We did all of that using the help of the conditional formatting formula in Excel.
21. Highlight Weekends in a Week
Let’s try another example where we will use a formula to highlight weekends in calender week in Excel. We will use the OR and WEEKDAY functions to create this formula. As for the dataset, we will use the following.
Now follow these steps to see how we can highlight weekends in Excel using conditional formatting using the formula.
Steps:
- First, select all the cells in the dataset excluding headers.
- Then go to the Home tab on your ribbon.
- After that, select Conditional Formatting from the Styles group section.
- Next, select New Rule from the drop-down menu.
- In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
- Then insert the following formula in the Format values where this formula is true .
=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)
- Next, select your preferred format type.
- Finally, click on OK .
Thus it will highlight the weekends from the calender week, all with the help of conditional formatting in Excel.
결론
So these were the methods and different examples of using a formula for conditional formatting in Excel. I hope you have grasped the idea of the usage of the feature and can now use it accordingly. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.
For more guides like this, visit Exceldemy.com .