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 know the result you want. Excel just wants you to write the perfect formula to get there. And we both know that part can slow us down. Excel recently introduced Formula by Example, which generates a formula from a pattern you provide. This can be helpful. But Power Query has had such a feature for ages. Go to Add Column > Column From Examples. Provide the pattern based on values in other columns, and let it do its magic. And if you have the formula bar enabled (highly recommended), you...
Recently, I spent an hour testing something. I copied data from a PDF into Excel. You might already know what happens when you do that manually: everything from the row jams into one cell. Complete mess. So I opened Copilot in Excel and asked it to split the data for me. It immediately went to formulas. Simple ones first, then increasingly complex combinations. I tested each one. None of them split the values correctly. Here's what I should have done: skipped the manual copy entirely and used...
Someone just joined your team. They got a quick walkthrough from IT. Maybe from you. Now they're staring at a spreadsheet trying not to look lost. You see, there are a few very simple features that would make the life of an Excel newbie a whole lot easier. I cover 8 of them in this video: #1 was shocking when I discovered it. And #7 saved me days when we had to update all our group reporting files after a rebrand. If you already know your way around Excel, be honest: did you know all 8?...