Excel Make Flexible Formulas
November 16, 2021
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.
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:
Here's the formula in cell C2, that was copied down to C6
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.
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)
Also see: Previous Newsletter Issues
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.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20211116ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 15, 2021 8:18 PM