Date and Time Functions – N to Z
Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]

How to use Excel Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]

Highlight unique values in a filtered Excel table
Table of contents Highlight unique values in a filtered Excel table Highlight unique values and unique distinct values in a […]

Prevent duplicate records in a worksheet
This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

Compare data in an Excel chart using drop down lists
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]

Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

Highlight a data series in a chart
This article demonstrates how to highlight a bar, group of bars, a line, and a column in their charts respectively. […]

Advanced Techniques for Conditional Formatting
Table of contents How to change cell formatting using a Drop Down list Highlight cells based on coordinates Highlight every […]

Prevent overlapping date and time ranges using data validation
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]

Highlight lookups in relational tables
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Watch schedule that populates vacation time
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

How to create permutations
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]

Add values to a regular drop-down list programmatically
Table of Contents Add values to a regular drop-down list programmatically How to insert a regular drop-down list Add values […]

Two-way lookup in multiple cross reference tables simultaneously
This article describes two ways to perform lookups in multiple in multiple cross reference tables simultaneously. The first one being […]

True round-robin tournament
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Find empty hours in a weekly schedule
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Plot date ranges in a calendar
Table of Contents Plot date ranges in a calendar Plot date ranges in a calendar part 2 1. Plot date […]

How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]