Excel Community round-up - June 2020

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


The comprehensive guide to PivotTables

Get an overview of everything Pivots - including layout options, calculated fields, and an introduction to Power Query.  Includes a download of all the resources.

Excel essentials: Good spreadsheet practice and tips

Created for the ACA student community, you can also access this webinar on the essential core Excel skills for accountants.

Student Excel webinar - Your questions answered (public)

A free blog post in which we run down the nearly 100 questions we received during the student webinar above, including a download of the solutions.

Excel Tip of the Week

344 - Power Query: Getting started with web queries (public)

PQ is a powerful tool for data analysis - and you can even connect it easily to tables of data on the web.  Our simple guide shows you how to get started.

345 - Spilling errors with dynamic arrays

The new dynamic array functions in Excel 365 are powerful - but they don't always work as you might expect.  The TOTW takes a look at how you can puzzle out the problems and fix them.

346 - Revisiting case-changing functions

Switch text between UPPER, lower, and Proper Case easily using these simple functions.

347 - VBA case study: Filtering visible sheets

Change which sheets are visible at the push of a button with this simple macro.

348 - Fixing badly formatted dates

Whether it's converting unrecognised date formats or switching between US and UK date format, our guide has the lowdown on getting dates to behave in Excel.

Other blogs

Modelling 101: Part 60 - Variance Analysis with ONE formula

Liam Bastick looks at how to reformat and represent data into a monthly variance analysis report using a single monster formula.

Exploring Charts (Graphs) in Excel - Part 1: The Blog series (public)

John Tennent launches his new series exploring the visualisation of data in Excel - starting with an overview of the journey to come.

Accountants' guide to Excel - PivotTables - PowerPivot part 2

Simon Hurst explores date tables, DAX Measures, and KPIs in Power Pivot - an excellent companion to our webinar, above.

Intro to Financial Modelling - Part 6: Formula blocks and consistency (public)

Modeller Jo Hayes talks through building consistent formulas and more in the latest of our free guide to implementing the lessons of the Financial Modelling Code.

PowerPivot and Power BI Data Analysis Expressions (DAX) - the next level in the great Excel adventure game

Simon digs deeper still into the world of DAX, and introduces the powerful all-purpose CALCULATE function.

Power Query - think laterally to dramatically improve performance

Simon explores how different approaches to PQ problems can reap significant time savings.