Excel Tips

Excel 2024: Stack Multiple Arrays »

August 29, 2024

If you need to combine multiple arrays into a single array, you can stack multiple arrays vertically using VSTACK. Stack multiple arrays horizontally using HSTACK.

Excel 2024: Complex Validation Using a Formula »

August 27, 2024

I was doing a seminar in Mobile, Alabama and several people there wanted to set up a three-level validation, but they did not care about having drop-downs to choose from. "I just want to validate that people are typing the correct values."

Excel 2024: Dependent Validation using Dynamic Arrays »

August 26, 2024

The Data Validation feature lets you choose from a dropdown list in Excel. It works great until someone wants to have two lists.

Excel 2024: Calculate Percent of Total with PERCENTOF Function »

August 22, 2024

When the Excel team created PIVOTBY, they wanted to be able to show percent of total within the pivot table. One of the side benefits is that you get a new PIVOTBY function.

Excel 2024: Moving from GROUPBY to PIVOTBY »

August 20, 2024

Once you are familiar with GROUPBY as described in the previous pages, it is easy to move on to the PIVOTBY function.

Excel 2024: Create a Summary Table With the GROUPBY Function »

August 19, 2024

The GROUPBY function debuted in late 2023. It makes it very easy to create a summary array. The optional arguments for the function provide flexibility.

Excel 2024: Replace a Pivot Table with 3 Dynamic Arrays »

August 15, 2024

As the co-author of Pivot Table Data Crunching, I love a good pivot table. But Excel Project Manager Joe McDaid and Excel MVP Roger Govier both pointed out that the three formulas shown here simulate a pivot table and do not have to be refreshed.

Excel 2024: Generating Random Numbers in Excel »

August 13, 2024

The new RANDARRAY function products an array of random numbers. Specify the numbers of rows, columns, minimum, maximum, and if you only want integers.

Excel 2024: SEQUENCE inside of other Functions such as IPMT »

August 12, 2024

After SORT, SORTBY, FILTER, and UNIQUE, the SEQUENCE and RANDARRAY functions seem pretty tame. SEQUENCE will generate a sequence of numbers.