'INDIRECT function' category
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Highlight cells based on ranges
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
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 […]
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 […]
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 […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Permutations with and without repetition
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
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. […]
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
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
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 […]
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 […]
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 […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Heat map yearly calendar
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Highlight a column in a stacked column chart
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
Highlight a bar in a chart
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
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 […]
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 […]
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 […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Use a calendar to filter an Excel defined Table
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
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 […]
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 […]
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
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 […]
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 […]
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 […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
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 […]
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
How to calculate a date based on specific weekday in a month
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
Choose between two data sets to VLOOKUP
Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]
Question: How do I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values