Excel Tips

Excel 2024: Formula for Unique or Distinct »

August 5, 2024

The UNIQUE function will provide either a list of unique or distinct values.

Excel 2024: Filter with a Formula »

August 2, 2024

The FILTER function is new as part of the dynamic arrays feature. There are three arguments: array, include, and an optional [if empty].

Excel 2024: Sorting with a Formula »

July 31, 2024

Sorting data in Excel is easy. Unless, you are building a dashboard for your manager's manager. You can't ask that person to select C3, go to the Data tab and click the AZ button every time they want an updated report. The new SORT and SORTBY functions allow you to easily sort with a formula.

Excel 2024: Dynamic Arrays Can Spill »

July 29, 2024

Introduced in 2019, the new Dynamic Arrays represent a major change to the Excel calculation engine. While the old Ctrl+Shift+Enter array formulas could return several results into a pre-selected range, these new formulas do not require Ctrl+Shift+Enter and you don't have to pre-select the range.

Excel 2024: Create Your Own Data Types »

July 24, 2024

Data types sound great, but wouldn't you like to have your own company data as a data type? Imagine if you had your company product list in a data type. You could store the SKU in a column and then extract item information using XLOOKUP or dot formulas such as =A2.Country.

Excel 2024: Get Historical Stock History from STOCKHISTORY »

July 23, 2024

In June 2020, Microsoft 365 added the new STOCKHISTORY function. It will pull historical stock data after the market has closed. The syntax is as follows:

Excel 2024: Geography, Exchange Rate & Stock Data Types in Excel »

July 22, 2024

In the past, Excel did not really handle data types. Yes, you could format some cells as Date or Text, but the new data types provide a whole new entry point for new data types now and in the future.

Excel 2024: Handle Plural Conditions with SUMIFS »

July 18, 2024

Did you notice the "S" that got added to the end of SUMIF starting in Excel 2007? While SUMIF and SUMIFS sound the same, the new SUMIFS can run circles around its elder sibling.

Excel 2024: Suppress Errors with IFERROR »

July 17, 2024

Formula errors are common. If you have a data set with hundreds of records, a divide-by-zero and an #N/A errors are bound to pop up now and then.