Excel Roundup 2021-06-24

Happy Summer! Or, if you’re south of the equator, Happy Winter! To help you get the new season off to a good start, 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 – How to Fix Excel MATCH Function Errors.

Date Range: How to sum or count in Excel, based on a date range, and other criteria.

Calculation Options: How to change Excel calculation options with command buttons or macros. Tricks to see which calculation mode is active, and prevent calculation problems.

Drop Downs: Use Excel SORTBY function to create drop down list with latest items at top, others below that, A-Z To get the file, go to the Excel Sample Files page. In the Data Validation section, look for sample file DV0078 – Drop Down with Latest Items at Top.

Excel Tips and Tools

Excel Plans: On the Forward Thinking CFO podcast, Brian Jones (Head of Product, Excel) discusses Microsoft’s strategy for Excel. There are 2 episodes — Part 1 and Part 2

Tables: Jon Acampora shows 10 shortcuts for working with Excel Tables. Do you use all of them? My favourite it dragging the heading cell to move a table column.

Excel Tools: Jan Karel Pieterse has a new version of his GitHub VBA Exporter tool. NOTE: Even if you don’t need that tool, take a look at the other free Excel files on that page, like AutoSafe, FlexFind, Follow Cell Pointer, and more.

Excel Functions

XLOOKUP: To see how the new XLOOKUP function works, take a look at these 6 examples on the Microsoft site.

Functions – Tom, at The Analyst Cave, is excited about Excel’s new LAMBDA function. But Mike James, at I Programmer blog, disagrees, saying “Spreadsheets are dangerous enough”

Functions: Excel functions expert, Norm Harker, reviewed all the Excel function descriptions on the Microsoft site’s list, and found many errors.

Excel Battle: Did you watch the Financial Modeling World Cup Multi-player Battle on June 8th? If not, you can watch the recording on YouTube, and get the sample files (“Cake to Bake”) to try the challenge on your own.

Charts and Dashboards

Charts: Jon Peltier shows how to make a histogram, using Excel’s new functions, like SORT, UNIQUE and SEQUENCE.

Dashboard: Chandoo has a new 3-part series on his blog – How to create a fully interactive Project Dashboard with Excel.

Charts: On his Junk Charts blog, Kaiser Fung shows how two charts can show a different story, with the same data and chart type. Click this link to learn more about the chart trifecta that he mentions.

Dashboards: Mynda Treacy has a new Personal Stock Portfolio dashboard tutorial. There’s a step-by-step video, and you can download the sample workbook, to follow along.

Charts: Carsten Sandtner shows how to make a better-looking chart in Excel, and create a chart template. That will save you time when making another chart later.

Power Stuff

Power BI: This is a fascinating way to browse a collection of Excel articles! Simon Hurst explains how the IEACW’s new Spreadsheet Knowledge Base was built, using Excel and Power BI. And if you’d like to try Microsoft’s free Power BI Desktop tool, Matt Allington shows how to get started.

Power Query: On the Excel Campus blog, Jon Acampora shows 15 shortcuts you can use with Power Query. There’s a video, written steps, and a sample file.

Power BI: Matt Allington shows how to build a P&L in Power BI without any DAX. And Chris Webb is excited about the new Power BI and Excel integration features

____________________