Excel Community round-up - July 2019

Here is all of July'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".


Experimental Excel - Look, no formulae

Simon Hurst shows the potential of Power Query by building an automated management accounts consolidation tool entirely without traditional cell formulas.

Extracting accounting systems data in Excel

Tom Edmunds of MGR joins us to discuss some tips and tricks for reformatting accounting export data in Excel and making it usable.

Excel Tip of the Week

296 - Data validation redux (public)

A definitive review of the handy tool for constraining user input and improving data quality - including how to make dropdown menus.

297 - Revisiting using #N/A! in charts

Simple error values can make filtering which values are plotted in a chart a breeze.

298 - Formula issues redux

A troubleshooting guide to common reasons that formulas don't work as expected.

299 - Google Sheets: UNIQUE

A first dip into a Google Sheets TOTW - with the dynamic array function UNIQUE, that can remove duplicates formulaically.

300 - Copying worksheet tabs

The Tip of the Week reaches a milestone!  Plus, an overview of how to quickly and easily duplicate worksheets.

Other blogs

Accountant's guide to Excel - Conditional formatting: Two heads are better

Simon Hurst reviews the options for using automatic formatting to improve the readability and comprehension of accounting data.

The importance of knowing your functions

Sometimes functions don't do exactly what we think they do - and this can get us in trouble.  Simon covers a few common misconceptions.

Excel Fuzzy Match - perhaps fuzzier than I felt

A recent addition to Power Query allows for inexact matching - but, as Simon discovers, it's perhaps a little too inexact for now...