Contextures News 2021-11-16

Excel Make Flexible Formulas

November 16, 2021

List every 3rd item, make a flexible formula, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Thank you for reading today's news, and you'll get the next newsletter on November 30th. It will be the final edition, and will have links where you can get all my future Excel tips.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Quick Tip: Flexible Formula

Instead of typing an argument number into a formula, use a cell reference. You'll be able to change the results quickly, without editing the formula

For example, in the RANK function example shown below:

• Type a 0 in cell E1, and the items are ranked largest to smallest
• Type a 1 in cell E1, to show the the items ranked smallest to largest

Here's the formula in cell C2, that was copied down to C6

• =RANK(B2,\$B\$2:\$B\$6,\$E\$1)

Also, see how to use drop downs to choose options for SUBTOTAL and AGGREGATE functions.

Every 3rd Item

If you have a list in Excel, you can use a formula to return every nth item from the list. That's helpful if you want to create a "random" list of items for testing, or other reasons.

In older versions of Excel, you can use OFFSET and ROW to create the list, like this formula in cell E4:

You need to copy the formula down the worksheet, to as many cells as you need for the results.

In Excel 365, you can use FILTER, with MOD and ROW. Enter this formula in cell C4 only, and the results spill down the worksheet automatically.

For both formulas, change the number in cell D3 (named MyNbr), to see a different list of items.

To download the sample file with the two formulas, click here: Every nth Item Sample File. The zipped workbook is in xlsx format, and does not contain any macros.

Excel Articles

Here are some Excel-related links that you might find useful or interesting.

Excel Features: Jon Acampora listed the 7 most important features in Excel -- do you agree with all his choices? What's in your top features list? (Level - All)

Pivot Table Fun: Pivot tables are a great tool for analyzing your business data, and the Book Riot blog shows a different way to use them - look at your reading habits! (Level - All)

Charts: Jon Peltier shows how to make Excel chart gridlines square, instead of rectangular. (Level - Adv)

Last week, this robot vacuum was on half-price sale at Canadian Tire, so we got one. I charged it overnight, then started it the next day, to see how well it worked. It went about 2 feet, bumped into a wall, then stopped, and began beeping. After a few more attempts, I gave up, and checked the manual (as a last resort).

That beeping was a “low battery” signal. Oops! I had it backwards in the charging station overnight. (sigh) It's working perfectly now.