How to use the XOR Function in Excel

Tuesday, May 05, 2020 Mr King Excel Tips,

How to use the XOR Function in Excel

Bottom Line: Learn how to use the XOR function in Excel to analyze attendance data.

Skill Level: Intermediate

Download the Excel File

The file that I work within the video can be found below. You can use it to follow along and reconstruct what I'm doing in the video.

Dunder Mifflin Attendance Report - Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report - Final.xlsx (166.6 KB)

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I'm planning to post a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Part of a Series

Just so you have some context, this post is the first of six in a series that resulted from an Excel Hash competition. Each year, Excel geeks like myself are tasked with building a worksheet that contains specific features. We compete to see whose solution is best. It's a lot of fun.

My entry from this year is a salute to one of my favorite shows, The Office. It's a dashboard that takes simple timestamp data and turns it into an attendance reporting tool that Dwight Schrute could proudly use to police his fellow coworkers.

The XOR Function

Our first step in creating this attendance dashboard is to take all of the timestamp data and determine if each entry is an “In” or “Out” entry.

The logic is fairly straightforward. If there is one entry for any given employee, it means that they have come in to work and haven't yet left. A second entry would indicate that they are now out of the office. So what we are really looking for, initially, is to know if there are an odd or an even amount of entries in the running list. That way, we can label the employee “In” or “Out” of the office.

That's where the XOR function comes in. This function returns a TRUE result if there is an odd number of TRUE results in a range. Likewise, it returns FALSE if there is an even amount.

Note: For the purposes of our attendance tracker, the data should be sorted into chronological order.

Compatibility: The XOR function is available in Excel 2013 and later. If you have an older version you can use the ISODD(COUNTIF()) formula that I explain in the video and below.

Writing the XOR Function

To use the XOR function, simply type =XOR and Excel will prompt you to enter logical statements. You can also just feed it an array of true/false values.

For example in the video, the reference range that we want is (A2=A$2:A2). This tells Excel to return TRUE if the name in cell A2 is the same as any of the names found in the range from A2 to A2. Of course, that is only one cell, but as the formula is copied down to the cells below it, the range automatically expands. (The beginning of the range will always remain A2 because of the absolute reference (dollar sign), while the end of the range will change.)

The XOR function essentially evaluates how many times the selected cell matches the entries in the selected range. It returns TRUE when there is an odd amount and FALSE when even.

To make the report more understandable, we can wrap the function in an IF statement. If the function returns TRUE, the cell can read “In” and if FALSE, it can say “Out.”

So the final formula would be =IF(XOR(A2=A$2:A2),”In”,”Out”)

Using Table Range References

One quirk with Excel is that if you use the range references as I've outlined above (A$2:A2), it may not automatically extend when you add data to the bottom of your range. So an alternative is to use table range references.

Using INDEX, we can compare the first cell in a column with a range of cells from that same column. This accomplishes the same thing we did above with the regular range reference.

With our table reference, our range would be replaced with ([Employee]=INDEX([Employee],1):[@Employee]). This may look a bit gnarly if it's the first time you've dealt with the INDEX function. However, using this option is much better if data is continually being added to your report.