Excel Tips

Excel 2024: Date Tricks in Excel »

September 23, 2024

You can use EOMONTH to get to the end or start of the month. You can break out the YEAR, MONTH, DAY and then put it back together using the DATE function.

Excel 2024: How to Provide Usable Feedback to the Excel Team »

September 19, 2024

If you encounter a bug in Excel, here is the best way to get it fixed quickly. On the Help tab in the Ribbon, choose the Feedback icon.

Excel 2024: Many Task Panes Now Collapse into a Tab Strip »

September 16, 2024

There are several features in Excel that open a task pane on the right side of Excel. Sometimes, you would end up with 2, 3, or even 4 task panes open and it would cover your entire screen.

Excel 2024: Number the Visible Rows in a Filtered Data Set »

September 10, 2024

You've applied a filter to a data set and would like to number the visible rows. Two functions that can ignore rows hidden by a filter are SUBTOTAL and AGGREGATE. The key to this solution is to use the COUNTA version of SUBTOTAL for every cell from the Heading row to the current row.

Excel 2024: Use A2:INDEX() as a Non-Volatile OFFSET »

September 6, 2024

There is a flexible function called OFFSET. It can point to a different-sized range that is calculated on-the-fly.

Excel 2024: Shuffling and Dealing a Deck of Cards »

September 4, 2024

Take a look at the figure below. The 13 playing card values are in A5:A17. The four playing card suits are in B4:E4. A simple =A5:A17&B4:E4 in cell B5 generates a 13-row by 4-column array of all 52 playing cards.

Excel 2024: Reshaping an Array to a Vector and Back »

September 3, 2024

You can unwind a rectangular range or array into a single column using TOCOL or a single row using TOROW. Each function can take an array or a range as the first argument. The optional Ignore argument can have Excel skip empty cells. The optional Scan_By_Column argument will control if values are read row-by-row (the default) or column-by-column.

Excel 2024: Dropping, Taking, or Choosing from an Array »

September 2, 2024

While DROP is designed to remove rows or columns from the start or end of an array, the TAKE function will keep only the beginning or ending rows or columns from an array.

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.