The problem

An organisation employs many people and every period it produces an employee report

January’s report contains all of the people employed in January.

February’s report contains everyone employed in February … but

there are people in the January report who are NOT in the February report … and there are people who were not in the January report but they are in the February report

Sonow we  are concerned with

  • employed staff
  • staff who have left the company
  • staff who have joined the company

Problem: how to identify who is who?

There is a file to download that shows the problem and solution. Read on to see how to do this yourself, using Conditional Formatting

The solution

The Worsheet tab contains two reports, Januar and February. To find employed, left, new, do this:

Select the Employee Number column in each Excel table … these are unique IDs

Click Conditional Formatting on the Home tab and select Highlight Cells Rules

Scroll to the bottom the menu you now see and click on Duplicate Values…

Make sure you see Duplicate on the left hand side and not Unique, select the colour you want to use for formatting

Click OK

Both columns are highlighted ,  as follows:

The highlighted employees in the January and February columns are employed in January and February

The ones NOT highlighted  in the January column are  employed in January but not in February … they are February’s leavers

The ones NOT highlighted  in the February column are newly employed in February … they are  February’s new starters

Worksheet (2) has the same information  as the Worksheet tab but I sorted the Employee Number  columns by colour to highlight employees … leavers … starters

Just to prove this works, the left tab lists the leavers and the new tab lists the starters … check that what I just said confirms that!

Duncan Williamson

14th November 2018

%d bloggers like this: