Excel Roundup 2020-12-31

Written by Debra Dalgleish from Contextures Blog

Happy New Year! To wrap up the old year (goodbye 2020!), here’s a roundup of Excel articles and links. I hope you find a few interesting things to read, and new Excel features to try.

NOTE: To get Excel links and articles every other week, sign up for my bi-weekly Excel newsletter.

Contextures Articles

First, here are a few of the Excel articles and sample files that I’ve published recently, and one of my latest Excel videos – Combine Text and Formatted Numbers in Excel

Dependent Drop Downs: Drop downs make it easy to enter data in a table. Dependent drop downs are based on your selections in other drop downs. My latest example has 3 levels of dependent drop downs, and it uses the new Excel functions, FILTER and SORT

Excel Error: Have you ever seen this Excel pivot table refresh error, with Data Model data? Thanks to UniMord, who told me about this strange error, and how he fixed the problem.

UserForms: See how to use a check box, option buttons and a listbox, on an Excel UserForm. Make selections, and the data is added to cells on the worksheet. Take a look at the code, to see how it works, then add similar features to your own projects. Look for sample file UF0051 – UserForm Controls Demo.

Data and Dashboards

Clean Your Data — The worst part of working with data is cleaning it up. To help save time, use these data cleanup techniques, from the Investintech blog.

Dashboard Setup: Mynda Treacy has a Power BI dashboard tutorial. There’s a step-by-step video, and you can download the sample workbook, to follow along. Mynda also has a Plan vs Actual Excel dashboard tutorial, with a video and sample file.

COVID-19: Excel MVP, Ingeborg Hawighorst, built an interactive dashboard that shows the development of the Covid-19 infection in New Zealand. She started with Excel and Power Query, then switched to Power BI. Learn how she built the dashboard, and you can access the live dashboard at this link.

Dashboard Tips — There are lots of great tips and examples in this Dashboard Design Essentials article by Spencer Baucke (via David Napoli)

Slicers: Robert Mundigl shows how to use table Slicers on an Excel dashboard, with a simple example file to download. You can download the fancy dashboard workbook too, to see how it works.

Functions

Spill Functions: Thanks to Dermot Balson, who sent me his Excel workbook with examples for all the new functions (dynamic arrays) in Excel for Office 365. To get the sample file, go to the Excel Spill Functions page on my Contextures site.

TEXTJOIN: In Excel 365 and Excel 2019, use TEXTJOIN to combine text from multiple cells, quickly and easily. It’s much easier than using CONCATENATE or the ampersand (&). To see a couple of easy TEXTJOIN examples, watch my new “Get Started” video.

XLOOKUP: Have you tried Excel’s new XLOOKUP function yet? Chandoo has 13 examples that show you how XLOOKUP works.

LET: In this video, Mynda Treacy shows clear examples for the new Excel LET function (currently in beta), with a handy troubleshooting tip at the end. Define names in a formula, assign a value to each name, then use those names in the calculation.

Excel Tips

Excel Tips: Jon Acampora shares his tips for naming Excel files, to keep things organizced

Mouse Wheel: On my Pivot Table blog, see more details about last week’s tip for using the mouse wheel to quickly show and hide Excel pivot table details.

Speed: See what Microsoft has done to improve Excel’s performance recently, and settings you can change on your computer.

Learning Resources

Excel Tips — Watch Excelling in Excel, a mini course from the University of Edinburgh. There 3 videos – data organization, tips and tricks, data visualisation

Excel Skills: Take a look at the free 4-week online Excel course, from the University of Colorado – Everyday Excel Part 2. It covers advanced data management, Excel for financial applications, and more.

Spreadsheets – The Essential Spreadsheets guide, on the University of York site, shows how to do things in Excel and Google Sheets, with written steps, videos and sample files. It’s interesting to see the comparisons!

Resources: There’s a helpful collection of Excel learning material on the Dalhousie University library site. You’ll find workbooks for beginners and experts, and exercises for working with data in Excel.

Programming: If you’d like to get started with Excel programming, check out this free 3-part course from the University of Colorado Boulder – Excel/VBA for Creative Problem Solving. (Level – Programming)

_____________________