So you’ve spent ages building an Excel spreadsheet for your team.

All of your formulas are working perfectly…

Until someone overwrites them by inputting a (possibly incorrect) hard-coded value 😡

It can take a long time to track down this type of error!

Luckily, there’s an easy way to spot cells with hard-coded values:

1. Highlight your data, press CTRL + G, and then ALT + S to bring up the Go to Special menu.

2. Select Constants → Numbers → OK.

3. Any cells with hard-coded values will now be highlighted in a darker color! Check the formula bar and you’ll see those cells have no formulas behind them.

4. Now you want to get the correct total. Double-click the fill handle on the bottom corner of the column to apply your formula to every cell (you can also use the Ctrl+D shortcut).

Done!

Now, the above method works well. But you have a few other options for dealing with sneaky colleagues:

• Use CTRL + ` to show all formulas in your sheet.
• Add Sheet Protection to any cells with formulas. That way, people have to follow your system. 😉

💚 This Week’s Favorites

🎬 YouTube Video - 10 Free Excel Add-Ins. This video is about some of my favorite free Excel add-ins. FRED, for example, gives you free access in Excel to over 800,000 data series from various sources of macroeconomic data. And the Mini Calendar and Date Picker lets you add an interactive mini calendar to your sheet, for quickly picking relevant dates.

📚 Book - Anxiety Rx by Dr. Russell Kennedy. If you’ve ever struggled with stress or anxiety, I highly recommend this book. Dr. Kennedy suffers from anxiety himself, and teaches several techniques for understanding and working through the condition.

🍚 Cooking - Zojirushi NS-TSQ10 Sushi Rice Cooker. Cooking sushi rice has always made me a little bit anxious. It’s so easy to make it either too grainy or too squishy… which ruins the whole meal. So I finally decided to buy a rice cooker from the Japanese company Zojirushi.

It was really hard to find (we got it shipped from Holland) – but now we get restaurant-grade rice every time, with no more anxiety or mistakes!

Excel is a bit like this. You CAN do a lot of things manually, especially if you don’t want to invest time or energy into learning something new. But learn some automations, (especially formulas and Power Query), and you’ll save yourself a lot of time and stress in the long run. Plus your analysis will turn out perfectly every time!

🤓 Geeky News

🌈 Google Sheets - Colorful Drop-Down Lists. This fairly new Google Sheets feature lets you create a ‘dropdown chip’ (colorful drop-down list) inside a cell. These can also reference a range from your spreadsheet, so you don’t have to hard code your options.

Let’s hope we get colorful drop-downs in Excel too - It’s not a secret that Microsoft and Google “inspire” each other. Sheets had the IMAGE function - Excel got it. Excel introduced LAMBDA, now Sheets has it.

☎️ Microsoft Teams - 'Teams Premium’ Launches in February. Microsoft is making some Teams features exclusive to their new ‘Premium’ edition. This isn’t a surprise: as Microsoft adds more features, the price goes up (this happened with PowerBI). But it’d be nice if they made Teams Premium available to Enterprise customers for no extra charge.

🤖 ChatGPT - Logical Errors. People are starting to find logical errors in ChatGPT’s thinking, as you can see from the tweet below. When I sent this link to my son (he’s a programmer) he said ‘this is the mindset of every Python programmer’ 😆 (ChatGPT is based on Python). I wonder if the upgrade to GPT-4 will solve this?

Mustafa Khundmiri 👋 @mkhundmiri Stop bullying ChatGPT 😆 pic.twitter.com/4fAiiMWThj December 29th 2022 244 Retweets 1,800 Likes

Meanwhile, let’s hope people stop bullying the poor AI 💙

🚀 Power Stories

Anthony’s story and feedback brought me great joy! Excel doesn’t have to be a burden.

📈 Would You Like to Become an XelPlus Affiliate?

If you have a large audience who will benefit from becoming Excel or Power BI experts, join our new XelPlus affiliate program. You’ll earn a 20% commission on every sale of one of our courses made using your affiliate link or code.

We have 10+ essential courses and hundreds of successful students (like Manish and Christopher), so you’ll be promoting quality education while adding to your income. Sign up here.

Let’s make data analysis easy 😊

⛰️ Global Excel Summit

The virtual Global Excel Summit will be from 6th-8th February this year! It’ll be an action-packed 3 days of Excel, Power BI & Financial Modelling, and I’ll be one of the main speakers. To get 20% off your tickets, use the coupon code LEILA at the checkout.

See you next week!

Leila

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow me on LinkedIn​

🥇 Join 300,000+ people in our courses​

🤝 To sponsor the next issue of XelPlus Weekly, get in touch here.

​