Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

How to Create Conditional Formatting Between two Workbooks Without Extra Columns

In this second Excel example I will show a different way of how we can use Conditional Formatting to create a visual warning in the event that the data do not match in the two Excel Workbook .If you want to create Conditional Formatting (CF) when using two workbooks in Excel, then read this and the previous tutorial and observe how you can create conditional create a warning to two dates or different data for a particular condition. About basics Conditional Formatting I have already written to the respective link.

In this second case there are two Excel Workbook that are placed in the same folder (C:\Test or C:\Temp).

  • Workbook1.xlsx (source data with which to compare data Workbook2)
  • Workbook2.xlsx (destination data in which we create a conditional format for the case to be different the dates of Workbook1)

In the workbook "Workbook1" are UNIQUE names of persons and corresponding dates or data. Our goal is to enter the dates for a particular person in a workbook "Workbook2", and if you enter the wrong date or the date is different for a certain person then we want to be Excel, warn about not entering the correct date for the person concerned. This problem can be solved using the Conditional Formatting between two Workbooks.

Note: If you formulas do not work, replace the separator semicolon with a comma

IMPORTANT: In this second case, the original workbook 'workbook1.xlsx' must be opened in order to update the data in the workbook 'workbook2.xlsx'

In the picture below you see the data in Workbook1

In this workbook "Workbook1.xlsx" are the original data (dates) with which we compare dates in the second workbook, "Workbook2.xlsx". This Excel Workbook I have defined names for certain cell ranges (if you do not know how to define a name for the cell or range then see respective link).

Source data for Conditional Formatting

In the following tutorial, followed by the names of defined cell range or range of data in Workbook1.xlsx.

  • alldata => =Sheet1!$A$2:$D$20 (Range for all date into ranks, This range is used for the VLOOKUP formula in Workbook2.xlsx)
  • date1 => =Sheet1!$C$2:$C$20 (Start Date)
  • date2 => =Sheet1!$D$2:$D$20 (End Date)

Defined Names range in Name Manager of cells in the source Excel workbook

Conditional Formatting Tetween Two Excel Workbooks

Solving that particular problem at the previous of the tutorial, I've made without using additional columns (as opposed to the previous tutorial).

On Sheet1 in 'Workbook2.xlsx' there are only three columns. The column 'B' and the column 'C' set the Conditional Formatting (see figure below).

 Use the Conditional Formatting between two Workbooks

It is known that in Excel we can define the name of the cell or range of cells (range of data). In this case the 'Workbook2.xlsx' I have not defined the name of the cell, but I have defined the name of the formula. So, the first step I defined the names for the two formulas in the Name Manager (the formula used in the previous tutorial with extra columns).

Defined Names to the following formula: (there is a difference between formulas when Workbook1 is open and when Workbook1 not open, although both workbook in the same folder). You see the image below.

In the formulas below, used a named range data => alldata => =Sheet1!$A$2:$D$20

Defining name of formula in Excel Name Manager


If 'Workbook1.xlsx' is open

The formula is defined as "ColumnB" =VLOOKUP(Sheet1!A2;workbook1.xlsx!alldata;3;FALSE)

The formula is defined as "ColumnC" =VLOOKUP(Sheet1!A2;workbook1.xlsx!alldata;4;FALSE)


If 'Workbook1.xlsx' is not open

The formula is defined as "ColumnB" =VLOOKUP(Sheet1!A2;'C:\Temp\workbook1.xlsx'!alldata;3;FALSE)

The formula is defined as "ColumnC" =VLOOKUP(Sheet1!A2;'C:\Temp\workbook1.xlsx'!alldata;4;FALSE)

Creating a name for the formula to Excel Name Manager

NOTE: Both Workbook open.

In the next step you need to apply Conditional Formatting to cell ranges in 'Workbook2.xlsx' where we want to have a visual warning if dates are not identical in both Workbook for the person concerned. Name of the person's condition in the VLOOKUP formula. This range varies depending on how much and in the future plan to have rows of data.

Selected a range of cells' B2:B20. Start = Conditional Formatting> New Rule => click "Use a formula to determine which cells to format" and then in the 'Refers to' below, enter the following formula

=IF($B2<>ColumnB;TRUE;FALSE) set the Format, click OK => Apply

Selected a range of cells 'C2:C20'. Start = Conditional Formatting> New Rule => click "Use a formula to determine which cells to format" and then in the field 'Refers to:' below, enter the following formula.

=IF($C2<>ColumnC;TRUE;FALSE) set the Format, click OK => Apply

After setting the Conditional Formatting in the 'Workbook2.xlsx' visually you can to spot the differences between the Dates (data) contained in Workbook1 and Workbook2.

Setting Conditional Formatting rules for column B (picture below).

 Conditional Formatting Rule for Column B between two Workbooks

Setting Conditional Formatting rules for column C (image below).

Conditional Formatting Rule for Column C, CF between two Workbooks

Download a template example file in this tutorial. Unzip the ZIP file to the C: partition of your HDD. Read this tutorial, and yourself set formula (copy from the website) as well as the appointment in order to understand how the Conditional Formatting between two Workbooks.