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
____________________
Excel Roundup 2021-06-24
____________________
Hi Debra,
I opened the Dinner Planner and see the Security Warning re Macros. What is your recommendation to end users? Can we use the Planner without the Macros? I’m also seeing the Save As “Read Only Workbook”. That may be typical due to the macro warning but thought I’d ask if saving in that format renders the Planner un-editable. Thanks in advance.
I haven’t had a chance to use your awesome planners and other creations. I hope to make that change!
Thanks, Carol, and Windows security often sets files to open as read only. You can change that setting after you download the files. There are steps on my sample files page:
https://www.contextures.com/excelfiles.html#unblock
If you downloaded the planner with the recipe search tool, that tool needs macros. The other parts of the planner should work correctly without macros.