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.
When you open a new workbook in Excel, what’s the first thing you adjust? Maybe the font? or you add 5 more sheets because who uses Excel with just one sheet?! Here’s the thing - Excel doesn’t have to stick to its defaults. You can make it work the way YOU want. This week in Between the Sheets we're looking at small but useful enhancements you can make to your future workbooks. As well as: introducing the Core Visuals vision board in Power BI catching up on new developments in AI tools...
Do you hate reading instructions? Frustrated when teammates ask questions they could easily find the answer to? Well, then Copilot in OneDrive might be just what you need. Also in Between the Sheets today: Python Editor in Excel for user-friendly coding Excel compatibility (using the latest functions in shared workbooks) Practice creating interactive dashboards using pivot tables 🎬 Copilot in OneDrive Recently, I shared a video about the potential of Copilot in Excel. One cool feature was the...
It starts small. That mysterious icon you’ve always ignored? You finally click it. Boom! A whole new world opens up. Or maybe you jump into an Excel course you thought was “too techy.” Next thing you know, you’re spotting features that make your work faster and easier. That’s how it started for Maria. Maria took our Master Excel Power Query course. She didn’t overthink it. She watched the lessons, followed along, and started clicking, dragging and building. Right away. Before long, she built...