[TSG] You've Been Totaling Wrong All This Time!

Excel Education for the REAL WORLD!

I hope your day has been fantastic so far! Today we are likely going to break a habit that you unknowingly developed and likely leads to extra work while building & formatting your spreadsheets.

Let's dive into how you've likely been totaling numbers wrong in your spreadsheets all this time!

Have you ever found yourself setting up spreadsheets that subtotal data and, later, have to return and add more items to the data being summed? If yes, I’m sure you've experienced instances where you've added new rows and later realized that the total formula is not picking up the additional rows! This can be disastrous if it is not caught in time!

Why does this happen?

First, let’s understand when the SUM() and SUBTOTAL() functions resize their reference and when they don’t.

Both these functions will automatically resize if you add a value to the immediate bottom of the sum range (if you are using older versions of Excel, the resizing functionality might not exist at all).

Unfortunately, this convenient auto-resizing capability can easily be broken if you either:

1. Place a value immediately above the sum range, or
2. Insert multiple rows and don’t enter the additional values from top to bottom.

Once the auto-resizing is broken, it stays broken with no visible indicator, even if you enter the data at the very bottom the next time. This can make it very dangerous to rely on the SUM/SUBTOTAL functions to resize on their own.

So how do you overcome not knowing if the auto-resizing is broken?

To prevent this from occurring, I prefer to insert a thin row between my total and the data that is being totaled. I then ensure my SUM formula includes the thin row in its reference.

Intuitively, you will want to insert any new rows above the thin blank row which will guarantee its inclusion in the subtotal. For extra security, you could also insert a thin row above your data in the event data is added at the top.

Most Excel users indicate the subtotal of a group of numbers by placing a bottom border on the last cell being totaled. This, however, is not good practice!

Try inserting a new row at the bottom of your list of numbers and you’ll see how easily your formatting gets thrown off. Now you must manually erase a border and add a new one.

The best practice is to place a Top Border on the cell that is calculating your total. This way when a new item is added to your list, the border format stays in the proper place.

I format subtotals so often, in my Excel add-in myBrand, I created a dedicated button that inserts this top border automatically.

Totaling numbers might seem like the most basic of spreadsheet functionalities, but there can be a lot of instances where summing together numbers gets messed up and causes mistakes in your analysis (I know it's happened to me a number of times throughout my career).

By implementing the best practices I described in this email, you'll drastically reduce the risk of your summation formulas being disrupted over time.

These tactics will take time to get drilled into your head, so make sure you are intentionally taking extra time to think through how you are setting up your spreadsheet totals.

I've written A LOT about Excel over the years. Here are a few articles that might teach you something new today!

• Create A Bar Chart With Separate Positive And Negative Colors
• Adding Bullet Points To Your Excel Spreadsheet Cells & Text Boxes
• One Instance When Merging Cells Is Needed!
• How To Create Excel Progress Bar Charts (Professional-Looking!)

Over the years I've solved issues throughout my career by building my own Excel Add-in solutions.

With the launch of my website, I was able to take these tools I had developed for myself over the years and use them to fund operating TheSpreadsheetGuru website.

I tend to never give out discounts except during a new product launch, so this is definitely something that will not be commonplace going forward. As a special welcome gift to you, I'd like to gift you with a 30% off discount code that you can apply to any purchase in my product store (excludes T-shirts and other physical merchandise)

Each of my Excel add-ins or spreadsheet templates comes with a 30-day money-back guarantee so please don't feel hesitant in using this gift to make a purchase. Most of my add-in products also have a 2-week trial period that you can utilize to make sure the tool is right for you before making your purchase.

This offer is only valid for the first 7 days after you've subscribed to this newsletter. After that time, you will be locked from the coupon code.

You can check out even more of my product offerings by visiting my online store.

Today's random product recommendation is something I thought might be one of those "snake oil" products, but it turned out to be an amazing purchase for me.

As you can imagine, I sit at a desk for long periods of time and often will get minor aches/pain in my back. I had tried foam rollers and deep tissue pressure rods which worked to an extent but something always seemed to be unfulfilled with treating my back pain.

One day, I saw an ad for something called a Chirp Wheel and I vaguely remembered it being a product pitched on the TV show Shark Tank. After a bunch of additional research and a deep dive into customer reviews, I thought it was something worth putting on my Christmas list.

Needless to say, I fell in love with this product after receiving their 3-pack bundle as a gift. It put just a more targeted amount of pressure on my back muscles and also allowed me to crack my back in ways I never thought were possible.

I recommend getting the 6" wheel for deep tissue pressure and the 10" wheel for stretching your back (both will crack your back). The 4" is useless in my opinion and the 12" I don't have, so can't give any thoughts on it.

I use these wheels every day after I come home from work and they provide me with immediate relief! They're definitely worth checking out.

If this product interests you or you have some shopping to do on Amazon, make sure to use my "Check It Out" button link above. If you buy anything within 24 hours after clicking this link, a 1-2% portion of your sale will go directly to TheSpreadsheetGuru (at no cost to you).

This is a fantastic way for you to support the cost of running this newsletter without having to spend any extra money. I appreciate your willingness to help support this newsletter!

You are more than welcome to invite any one of your friends or colleagues who might also benefit from this newsletter. Simply send them the below link and they can get signed up for free as you did.

Newsletter Link: TheSpreadsheetGuru.com/newsletters

There are a bunch of social media platforms out there, and I have struggled to pinpoint where folks actually want to get their Excel tips. I've experimented with accounts on Instagram, Pinterest, Facebook, and many more (anyone up for TikTok Excel tips?!)...however, I never really found much interaction on those platforms.

What I decided to do is focus on more educational-based social media offerings. You can keep up with my latest writings/thoughts on:

Hopefully, this newsletter brought you some value today. I absolutely love sharing the tips I've come across or developed over the years and also receiving feedback from everyone.

If you have any thoughts on the topics covered in this email, feel free to respond back.

Tomorrow, we'll be discussing a powerful function that for some reason, the Excel developers have hidden from us. See you tomorrow!

Chris
Founder, TheSpreadsheetGuru.com