How to use the XOR Function

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

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

If you'd like to see my entry for this year, watch this: Excel Hash: Attendance Report with Storm Clouds & Fireworks.

Here are the other posts in the series:

  1. How to Use the XOR Function (this post)
  2. How to Use XLOOKUP for Reverse Order Search
  3. Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE
  4. How to Sort with a Formula in Excel Using SORT and SORTBY Functions

Bonus: How to Create the Dashboard that is compatible with All Versions of Excel

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.

Timestamp data for attendance report

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.

XOR function explained

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”)

Add the IF function to the XOR function

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.

Conclusion

If you don't want to use the XOR function, you can use a combination of COUNTIF and ISODD as an alternative.

It would look like this: =ISODD(COUNTIF(A$2:A2,A2))

See the video above for details about how to construct that.

The next video in the series will take a look at calculating the duration of time that the employees were in the office.

I hope this post was helpful in explaining how the XOR function works. Please leave a comment below if you have questions about it.

16 comments

Your email address will not be published. Required fields are marked *

  • Hi,

    Here is a parsed example from the workbook. For some reason, I’m returning TRUE even when a repeat name is present?

    Dwight Schrute TRUE =XOR(A1=A$1:A1)
    Oscar Martinez TRUE =XOR(A2=A$1:A2)
    Phyllis Lapin TRUE =XOR(A3=A$1:A3)
    Dwight Schrute TRUE =XOR(A4=A$1:A4)

    • Hey Colin,

      I’m sorry to hear that. I just reposted two files. One is the “follow along” version with the data only. The second is the final file.

      It doesn’t crash for me. However, it could be a compatibility issue. If you are on an older verison of Excel there could be an issue with the new Dynamic Array Functions that are being used in this file.

      You should still be able to open files with DA functions in older versions even though the formulas won’t work. Microsoft is slowly releasing the DA features and still working out the bugs.

      I hope that helps. Thanks again and have a nice day! 🙂

  • I had the same question, except I found that control-shift-enter makes it work as with other array functions I’ve used. But I also noted that many of your functions in the workbook are for future functionality not yet on my version of O365. Curious about what they are (since we are only on Video 1), but also when they might be available to a wider audience. Thank you for all of your videos. I learn something with each of them.

  • Hi John,
    First, congratulations on winning the competition.
    Secondly, the XOR function isn’t available on all Excel versions.
    Can you please specify which versions have it?

    Best Regards,
    Meni Porat

    • Thank you, Meni! I added a compatibility section above. XOR is available on Excel 2013 and later. On older versions, you can use ISODD(COUNTIF()) instead. I explain that in the video and article as well.

      Thanks again and have a nice day! 🙂

  • Great video! Is there any calculation speed difference between the XOR method vs. the ISODD(COUNTIF ) method. Also are structured references faster than cell references. I agree that the structured reference looks more confusing, and would rather avoid them if sharing the workbook.

    • Hey Rich,
      Great questions!

      I have not done speed tests on either. My hunch is that COUNTIF might be faster but I could be wrong.

      The structured references are going to be more bulletproof if you are adding rows to the table with that running total reference. The regular references with mixed absolute/relative references don’t always expand well in Tables.

      I hope that helps. Thanks again and have a nice day! 🙂

  • Congratulations Jon for winning the Excel hash competition, your solution is awesome and demonstrates a clever use of infrequent functions, great!!

    It’s the first time I hear about the XOR function, Excel never stops with surprises, wow!

    I have a question about using table references, why was the Index function used?

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly