Excel Tips

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.

Excel 2024: Replace Nested IFs with a Lookup Table »

July 15, 2024

A long time ago, I worked for the vice president of sales at a company. I was always modeling some new bonus program or commission plan. I became pretty used to commission plans with all sorts of conditions. The one shown in this tip is pretty tame.

Excel 2024: Preview What Remove Duplicates Will Remove »

July 9, 2024

The new Remove Duplicates tool added in Excel 2010 was a nice addition. Remove Duplicates is found on the Data tab. It is to the right of the Text to Columns command and is often collapsed into a single column of 3 icons: Flash Fill, Remove Duplicates, and Data Validation.

Excel 2024: Twelve Benefits of XLOOKUP »

July 8, 2024

The new XLOOKUP function has been rolled out to Office 365 in November 2019. Joe McDaid of the Excel team designed XLOOKUP to unify the people who use VLOOKUP and the people who use INDEX/MATCH. This section will discuss the 12 benefits of XLOOKUP.

Excel 2024: Troubleshoot VLOOKUP »

July 4, 2024

VLOOKUP is my favorite function in Excel. If you can use VLOOKUP, you can solve many problems in Excel. But there are things that can trip up a VLOOKUP.