Ready to take your Excel and Power BI skills to the next level?
Join me for this year’s virtual Global Excel Summit on February 6th-9th!
As the official Event Ambassador I'll give the keynote talk. This year’s theme is Excel Beyond Expectation, so I need a topic that's cool, innovative, and interesting to a room full of experts...
Tricky.
I've settled on a great idea, but you’ll have to attend to find out what it is 😉
The summit will feature three days of online masterclasses and panel discussions led by Microsoft MVPs, helping you apply cutting-edge techniques in Excel, Power BI, and the rest of the Office suite.
For a full list of events click here.
The first day (including my keynote talk) is free to attend! But with a full-access ticket, you'll also get:
🧑🏫 Two extra days of events
🎧 Recordings of every talk
✅ 30+ CPD hours (and a certificate of attendance).
I’m delighted to say that my community can get a 20% discount on tickets - just use the coupon code LEILA at checkout.
Sign up here: Global Excel Summit.
🤯 A Secret Way to Hide #DIV/0! Errors
Here’s a great tip I learned this from Bob Umlas (Microsoft MVP since 1994) at last year’s Excel Summit.
Let’s say you’re tracking your spending in Excel, and you have an Actual column and a Budget column. You want to calculate the percentage difference between your budget and what you’ve actually spent.
The standard formula for calculating this would be =B2/C2-1, so you divide Actual by Budget, and subtract 1 to get a proper percentage (some people prefer to write =(B2-C2)/C2 ).
But if you drag that =B2/C2-1 formula down to rows that have no budget amount you get a DIV error, because you’re dividing the Actual amount by zero (the empty Budget cell).
To get around this, most people use =IFERROR(B2/C2-1,"'’) so that any error messages show as blank cells ("'’). Others use =IF(C2=0,"", B2/C2-1), which gives the same result.
But here’s Bob’s trick: he doesn’t use a formula at all to hide the DIV error. Instead, he uses custom number formatting 👇
Here’s how:
1. Select the relevant cells, and apply the custom formatting [Black]0% (or switch out Black for whatever font color you’re using). Any numbers in your cells will now be formatted in black, but everything else will stay in default formatting.
2. Next, change your default font color to white (or the background color of your sheet)… now any DIV errors will disappear into the background!
Here's a step-by-step GIF:
So why use this method?
Well, if you have a complicated IF function that takes a long time to calculate, the IFERROR would prolong the calculation. In that case, Bob’s custom number formatting might be faster.
I did a Vogue-style Q&A in my house, answering 50 questions from the Excel Summit team. The questions included:
💀 Will Excel ever die?
🧠 Do I remember everything I teach in my videos?
📊 Which Global Excel Summit session am I looking forward to most?
📊 Excel - Automate Tab Available to Everyone.
Excel Desktop users will soon have access to the new Automate tab, which lets you automate repetitive tasks using Office Scripts. And if you’re an Office Insider, you’ll also be able to record worksheet actions using Office Scripts.
📊 Excel - Autocomplete for Dropdown Lists Version: Office Insider.
Last week I mentioned Google Sheets’ colorful new drop-downs. Well, Microsoft has also updated drop-downs in Excel with a new AutoComplete function that filters drop-down results as you type. No more scrolling!
🖼️ Art - Painting by Nikolaos Schizas.
I just bought a painting by Nikolaos to hang in our living room, and it’s changed the whole feel of the space. Small visual tweaks can make a big difference – just like in our Excel reports and PowerPoint presentations. The painting actually features here in my 50 Questions video.
🎬 YouTube Video - Why we all need subtitles now by Vox.
This great video explains why it’s difficult to understand dialogue in your favorite shows and movies. The answer: a mix of new recording technology, acting styles, and modern sound-mixing.
Congratulations to Kelly for completing our extensive Excel Power Pivot & DAX course. Thank you for the lovely feedback 😊
See you next week,
Leila
▶️ Subscribe on YouTube
🖇️ Follow me on LinkedIn
🥇 Join 300,000+ people in our courses
📣 Want to advertise on XelPlus Weekly? Get in touch here.
XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlus’ bestselling courses are popular among financial analysts, CFO’s, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
✨ Found something new hiding in plain sight. Opened Excel… and nearly missed it. There’s a new Get Data interface rolling out in the Beta channel. Cleaner design, searchable source list, and everything in one place. A small UI change, but a great excuse to revisit one of Excel’s most powerful tools: Power Query. If you're pulling tables from PDFs, scraping web data, combining files from a folder, or just cleaning up messy CSVs... Power Query makes it repeatable, reliable, and refreshable....
Ever typed an account number like 00123 in Excel… only for it to turn into 123? 😤 Excel thinks it's helping. But sometimes, we need those leading zeros - for IDs, ZIP codes, or product codes. The good news? You’ve got two easy ways to keep them: 1️⃣ Custom Number Formatting Tell Excel: “Hey, I want five digits, even if the first ones are zeros.” Just: Select your cells Press Ctrl + 1 (or right-click → Format Cells) Go to the Number tab → Custom Type 00000 (or however many digits you need)...
FILTER with multiple criteria? This used to break my brain. I’d stack together these fragile formulas - like Excel Jenga - hit Enter… …and brace for... #VALUE! It felt like defusing a bomb. Turns out, I just needed to understand how FILTER handles multiple conditions. And now? It’s my favorite Excel function by far. 👉 Check out the blog post that walks you through the exact steps to: ✔️ Filter rows that match multiple criteria ✔️ Decode that cryptic * and + logic ✔️ Return only the columns...