Contextures News 2021-11-30

Excel News - The Final Issue

November 30, 2021

Thank you for reading today's newsletter, and this is final issue of Contextures News.

• 10 Years of Tips
• Interactive Filter
• Quick Tip: Flash Fill
• Keep In Touch
• Weekly Photo

10 Years of Tips

Thank you for coming along on my Excel journey! It was lovely of you to read my newsletters, and wonderful to get your feedback, over the past 10 years. There's always something new to learn about Excel, no matter how long we've been using it!

After a break for the holidays, I'll be back to work on my Contextures website, creating new tutorials. Excel's new Spill functions have opened up a whole new realm of possibilities, so I'll focus on those for a while.

Note: You can find all my prevous Excel newsletters online, if you go to the searchable newsletter index page.

Interactive Filter

Speaking of Excel spill functions, here's my latest tutorial - an interactive filter. You choose the criteria settings, and the columns shown in the results.

• Blue cells have drop down lists to choose criteria and columns headings
• Orange cell has a FILTER formula, to show the results

Another sheet has spill functions that create the source lists for the drop downs. For example, here is the formula in cell B2, that creates a list of years, based on the dates in the food sales table:

• =SORT(UNIQUE(YEAR(ColDate)))

To see the setup details, and to download the sample workbook, go to the Interactive Filter page.

TIP: If your version of Excel doesn't have spill functions, you can use an Advanced Filter, with Slicers and macros, to create an interactive filtered list

Quick Tip: Flash Fill

Here's a quick tip, if you need to split a full address into separate columns for Street, City, State and Zip Code.

Use this Flash Fill shortcut (Excel 2013 or later):

• In the first row, type the street, city, state and zip code for the first full address
  • Tip: Type an apostrophe at the start of the zip code, so you don't lose any leading zeros
• Next, select the cell below the street that you typed
• Press the Flash Fill shortcut - Ctrl + E
• Repeat those steps to fill in the remaining columns

Watch the Video

To see a short video that shows the steps, go to the Split First and Last Names page on my Contextures Site

Use Formulas Instead

If you'd rather split the data with formulas, check out the examples on my Split Address With Formulas page. We had a formula challenge in May/June 2017, and that page has the data, solution formulas, and an Excel workbook to download.

Keep In Touch

Only the newsletter is ending -- I'll still be working on Excel content, so please stay in touch. You can comment on my blog posts, or send me an email. I look forward to hearing from you!

I'll continue posting on these sites:

Contexture Website || Contextures Blog || Pivot Tables Blog

Debra D's Blog || Spreadsheet Day

These are Frying Times

Finally, here's why I won't have time to work on my Excel newsletter now -- I have new technology to play with.

It was time to replace our old toaster oven, and now we have a 7-in-one appliance instead. This is what happens when your husband goes Black Friday shopping, unsupervised!

Do you think it can flash fry a buffalo in 40 seconds? (from The Simpsons - short video clip on YouTube)

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20211130ctx.html
I'll also post any article updates or corrections there.

Thanks for reading my newsletter, and if you have any comments or questions, send me an email.

Debra Dalgleish
[email protected]

P.S. You can find all my prevous Excel newsletters online, if you go to the searchable newsletter index page.