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.

🎬 My Latest Video

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?

🤓 Geeky News

📊 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!

💚 My Favorite Things

🖼️ 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.

🚀 Power Stories

Congratulations to Kelly for completing our extensive Excel Power Pivot & DAX course. Thank you for the lovely feedback 😊

See you next week,

Leila

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow me on LinkedIn​

🥇 Join 300,000+ people in our courses​

📣 Want to advertise on XelPlus Weekly? Get in touch here.

​