Ken Puls, FCPA, MS MVP

@excelguru.ca

I teach people how to use Excel (https://skillwave.training), host an Excel website (https://excelguru.ca), build software (https://monkeytools.ca) and enjoy watching football (aka soccer).

@excelguru.ca

More and more, we’ve been taking advantage of the LET() function when writing our #ExcelFormulas. The Letify Monkey will convert an existing formula with just a few clicks, though you can also define the auto-generated variable names and configure various options.
🐒Learn more: mkyt.ca/letify

Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist up for a fist bump) Letify Monkey We find using the LET() function makes our formulas easier to read, faster to calculate, and much easier to debug. Instead of trying to figure out how to incorporate LET into an existing formula, the Letify Monkey will convert it for you! (Screenshot of the Letify Monkey dialog, where it displays both the original formula and the new "letified" formula, and allows you to define the variables.) monkeytools.ca
@excelguru.ca

#PowerQuery caches preview values inside the file, so the Query Editor and hover cards can show stale results even after Refresh All has loaded fresh data to the worksheet. 🔄 It's even more likely if background data previews have been turned off to speed up large workbooks.

@excelguru.ca

During our last AKA session, an attendee described a puzzling issue: queries driven by dates in worksheet cells kept showing old values after a refresh. Only clicking Refresh Preview, or saving, closing and reopening the file, seemed to fix it.🤔 My diagnosis? The data probably wasn't wrong at all. 🧵

Is Power Query Showing You Old Data? If your worksheet seems to be showing old values, even after a refresh, first click ‘Refresh Preview’. Power Query caches preview values inside the file, so the Query Editor and hover cards can show stale results even after ‘Refresh All’ has loaded fresh data to the worksheet. (Screenshot of the Home tab in the Power Query Editor. The Refresh Preview button is highlighted with a callout reading, “Debugging step 1: Click this button!”) (Skillwave Training logo)
@excelguru.ca

Excel can be used for creating polished reports, dashboards, catalogs, heat maps, and more. 📉 At last week's Vancouver #PowerBI and Modern #Excel meet-up, Jakob Nielsen showed how his Flow add-in helps reduce repetitive layout, formatting, and report-building work. 🎥Watch here: youtu.be/nAl2ZyNz-9k

@excelguru.ca

🧭Takeaway: If a PivotChart keeps breaking on refresh, change the foundation instead of fighting the formatting. That's what the AKA is for: real problems from your own files, worked through live with someone who will tell you honestly what works and what doesn't.

@excelguru.ca

PivotCharts built on the #DataModel are notoriously fragile when a refresh adds new series, because the chart loses track of what it's supposed to format.
I offered practical workarounds:
🟢 Layer separate combo and line charts, OR
🟢 Try driving the chart from #DynamicArrays so it works off formulas.

@excelguru.ca

In the most recent AKA session, a student asked about a frustrating #DataVisualization problem: a carefully formatted combo #PivotChart looked perfect until new forecast data was added. One refresh later, the formatting was gone. 😠
I reproduced the issue and was upfront that there's no clean fix. 🧵

When Your PivotChart Falls Apart If a PivotChart keeps breaking on refresh, change the foundation instead of fighting the formatting. (Image of a plastered wall crumbling away to reveal the bricks underneath) (Skillwave Training logo)
@excelguru.ca

In this Monkey Shorts, I explore a #PowerQuery trick to fix non-aligned data. It all started with a PDF import where data shifted into different columns on different rows.👎
It all ended with success, and a recipe to solve this challenge for others!👍https://www.youtube.com/watch?v=TC_gRjjCnm0

#Excel

Older posts