How to use the QUARTILE.EXC function

The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. This […]

This extensive list provides detailed information including syntax, arguments, return values and examples for most of the functions used in Excel.

The list is easy to search and lets you sort by function name or category.

This is a great place to start learning Excel functions.

Learn to build charts that let you visualize values in order to analyze and comprehend data.

Learn to format data based on a condition or criteria to make you more productive and effective.

How to use the QUARTILE.EXC function

The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. This […]

How to use the QUARTILE function

The QUARTILE function returns the quartile of a data set, use the QUARTILE function to divide data into groups. This […]

Highlight unique/duplicates [Conditional formatting]

Excel has a few built-in conditional formatting features, one of them highlights values that have at least one duplicate. The […]

Smallest greater than condition

Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

How to find the smallest number excluding zeros

Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

How to highlight weekends [Conditional Formatting]

The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]

How to use the LOGNORM.DIST function

The LOGNORMDIST function calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev. This […]

How to use the GOTO statement [VBA]

The image above demonstrates the GoTo statement. It "jumps" or "navigates" to another place in the code, in this case, "Start". […]

Sort a range from A to Z [Array formula]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

How to use the LOGNORMDIST function

The LOGNORMDIST function calculates the cumulative lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of […]

Use slicers to quickly filter chart data

Slicers let you control data displayed in a chart, simply click on a button to quickly filter data. How to […]

How to use the WEEKNUM function

The WEEKNUM function calculates a given date's week number based on a return_type parameter that determines which day the week […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

Unique distinct list from a column sorted A to Z

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Answer: […]

Quickly jump to last row in a data set using excel hyperlink function

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take […]

Remove print preview lines (Page Breaks)

Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

SMALL function – multiple conditions

The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]

Merge tables based on a condition

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]

How to automatically fill all blanks with missing data or formula

Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Extract largest values from two columns

Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]

Match a range value containing both text and numerical characters

Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array […]

DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

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})) […]

Running totals within date range

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]