Excel Community round-up - November 2019

Here is all of November's new Excel content.  Excel Community and Tech Faculty members can access all posts by logging in to their accounts: Other users can access posts described as "public".


Dynamic arrays in Google Sheets and Excel (public)

In our final Excel Community webinar of 2019, I run through the power of multi-cell dynamic array formulas, and highlight the differences between how they work today in Google Sheets, and the proposed options for future versions currently being tested in Excel.

Dynamic arrays webinar - your questions answered (public)

In a follow-up blog post, I go through all of your questions from the dynamic arrays webinar.

Excel Tip of the Week

314 - ADDRESS and INDIRECT redux (public)

These unusual functions can let you refer to a cell by describing its location - or take a text input and find a named range that matches it.  They are useful to know about in a variety of cases - so take a look at our quick guide.

315 - Google Sheets: Counting distinct items

Following on from TOTW #305 on doing the same task in Excel, the Tip of the Week tackles how Google Sheets can help with additional functionality for counting how many unique values exist within a range, even when a condition is applied.

316 - Conditional summary functions redux

Whether it's the well know SUMIFS and COUNTIFS, or the wider world of AVERAGEIFS, MAXIFS, and MINIFS, the Tip of the Week is here to give a comprehensive overview of summarising data based on a condition.

317 - Basic arithmetic formulas

Not all calculations in Excel require functions - addition, multiplication, and more can be done with just simple operators.  But getting your brackets right is critical - as this blog explains.

Modelling 101

Part 54 - Keeping track of range names

Named ranges are a bone of contention for many modellers - adding clarity in some situations, but muddying the waters in others.  Liam Bastick shows you some tips for keeping them under control.

Part 55 - Depreciating opening net book values

Depreciation is an essential part of any forecast - but many organisations make faulty assumptions about how it works.  Liam explains the maths behind the mistakes, and how to do it properly.

Other blogs

XLOOKUP provides moving arguments

The ascendant replacement to VLOOKUP and INDEX MATCH, XLOOKUP, is still in beta in the Office Insider program - and Microsoft are still settling how it will work.  Liam Bastick explains how it will help when it is implemented.

Modern Excel: Charts & getting Ideas with AI

David Benaim continues his series on the features coming out in the latest versions of Excel - this time, looking at the new chart types added and the AI-driven data analysis suggestion engine, Ideas.

Accountant's Guide to Excel - Anatomy of a PivotTable

Simon Hurst gives a tour of the the essential parts of a PivotTable - one of the most essential data analysis tools in Excel.

Excel Tables bug hits key milestone (public)

Adding rows to an Excel Table is the whole point of what makes them great - but it doesn't work properly with sheet protection.  Simon provides an update on the campaign to get this fixed.

Dynamic arrays - blessing or curse

Simon Hurst looks at how the exciting new dynamic arrays feature - the subject of this month's webinar - work in some backwards compatibility cases - and what their introduction means for the future of Power Query.

Excel Tips for accountants - Maximising productivity: Data entry and spreadsheet navigation

Tom Edmunds continues his series on working with accounting data in Excel with a guide to the essentials of quick and easy navigation and data entry.