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.
☠️ Who Messed With My Spreadsheet?
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?
December 29th 2022
|
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.
Leila Gharani - XelPlus
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.
You've built this cool visual using formulas. Dynamic, clean, exactly what the dashboard needs. For example, a waffle chart Now you want to add it to the main dashboard sheet. But there's a problem. If people start clicking around, they might accidentally delete a formula. Or overwrite a cell. And now your carefully crafted visual is broken. You could protect the sheet. Enter password every time you need to make changes. Or you could get creative. Take a snapshot instead. Linked Picture and...
Someone asked me recently about automation tricks in Excel. Fair question. Everyone's talking about AI agents right now. Microsoft's pushing them hard, every tech company's got one. But here's what they don't mention in the demos: AI agents cost extra. They work maybe half the time. You're basically hoping they get it right. Meanwhile, you already have a tool that does exactly what you tell it to. Every single time. Power Automate. You've probably got access already (comes with most Microsoft...
AI is everywhere. And I mean everywhere. Your email tool has it. Your spreadsheet has it. Even your coffee maker is probably marketing itself as "AI-powered" these days. The pitch is always the same: "This will do your work for you." "Build fully functional apps without knowing how to code." "10x your productivity." And yeah, sometimes AI actually delivers. It's genuinely impressive when it does. But here's what I've noticed: when it works, the work doesn't disappear. It just changes. You go...