Analysing Percentage Changes with Google Sheets Formulas and Conditional Formatting

In the realm of data analysis and spreadsheet management, it's crucial to derive meaningful insights from numerical data. One common scenario involves tracking changes in values over time, such as comparing data from this month to the previous month. In this blog post, we'll explore a simple yet effective approach to visualise and understand these changes using Google Sheets formulas and conditional formatting.

The Formula: Percentage Change

To determine the percentage change, you need to calculate the difference (change) between the two numbers being compared:

Change = Original Number - New Number

Divide the change by the Original Number and multiply the result by 100 to convert this into a percentage.

% Change = (Change ÷ Original Number) × 100.

If the resulting value is negative, it signifies a percentage increase, whereas a positive answer would be a decrease.

When we use this within the Google Sheets formula, we take account of the increase or decrease separately with the initial IF statement, and the change is calculated using the absolute (ABS) function; more on that later.

To start, let's consider a scenario where you have two values, one from this month (let's say in cell C4 - this is the new number) and another from last month (in cell D4 - this is the original number). You want to visualize the percentage change between these two values along with an indicator of the direction of the change.

Here's a formula that achieves this:

=IF(C4 > D4, CHAR(9650), CHAR(9660)) & " " & ROUND(ABS(D4 - C4) / D4 * 100,0) & "%"

This formula has two components:

1. Arrow Symbol: It displays an upward-pointing triangle (▲) if the value in C4 is greater than D4, indicating an increase. Conversely, it shows a downward-pointing triangle (▼) if the value in C4 is less than D4, indicating a decrease.

2. Percentage Change: It calculates the percentage change between the two values, rounds it to the nearest whole number, and appends a percentage sign.

This formula gives you a quick visual cue about the direction and magnitude of the change.

Let's break down the formula step by step.

1. =IF(C4 > D4, CHAR(9650), CHAR(9660)): This part of the formula is an `IF` statement. It checks whether the value in cell C4 is greater than the value in cell D4. If this condition is true, it returns the character with Unicode value 9650 (▲, an upward-pointing triangle); otherwise, it returns the character with Unicode value 9660 (▼, a downward-pointing triangle). So, this part of the formula generates an arrow symbol pointing up or down based on whether the value in C4 is greater than D4.

2. & " ": This part concatenates a space character to the result obtained from the IF statement. This is done to separate the arrow symbol from the next part of the formula.

3. & ROUND(ABS(D4 - C4) / D4 * 100,0) & "%": This part calculates the percentage difference between the values in cells C4 (new number) and D4 (original number), takes the absolute value, rounds the result to 0 decimal places, and appends the percentage sign. Let's break it down further:

• ABS(D4 - C4): This calculates the absolute difference between the values in cells C4 and D4. Disregarding any positive or negative values, just the absolute difference.

• / D4 * 100: This computes the percentage change by dividing the absolute difference by the value in cell D4 and multiplying by 100.

• ROUND(..., 0): This rounds the result to 0 decimal places.

• & "%": This concatenates the percentage sign to the rounded result.

Putting it all together, the formula displays an arrow (▲ or ▼) based on whether the value in C4 is greater than D4 or not, followed by a space and then the percentage difference between the values in cells C4 and D4.

For this specific case, with C4=521 and D4=452, the value in C4 (521) is greater than D4 (452). Therefore, the formula displays an upward-pointing triangle (▲), a space, and the percentage difference rounded to 0 decimal places (rounded to the nearest whole number), followed by the percentage sign.

Conditional Formatting: Adding Colour to the Mix

To enhance the visual impact, you can employ conditional formatting to change the colour of the text based on the direction of the change. In this case, I’ve opted for red text for a decrease (▼) and green text for an increase (▲).

Here are the conditional formatting rules:

For Red Text (Decrease):

=LEFT(E4, 1) = CHAR(9660)

For Green Text (Increase):

=LEFT(E4, 1) = CHAR(9650)

These rules check the first character of the cell containing the formula (`E4` in this case) and apply the colour based on whether it starts with the downward-pointing triangle (▼) or the upward-pointing triangle (▲).

To find out how to apply Conditional formatting, check out the steps with the link and use the formatting rules above. You’ll need to create two separate rules for each colour.

Practical Application: Weekly Data Analysis

Now, let's apply this concept to a real-world scenario. Imagine you have a sheet where you track sales figures for a product or the number of calls and chats from a call centre each month. By applying the formula and conditional formatting, you can quickly identify products or agents with significant increases or decreases in metrics. This visual representation makes it easier to spot trends and anomalies, aiding in better decision-making.

This particular example is essentially the same as the original formula above, except the previous month is located on another sheet; therefore, a VLOOKUP formula is adopted to locate that figure:

VLOOKUP($B181,'Call Staging'!$B$333:$M$345,H$178,FALSE)

In this case, the column reference within the VLOOKUP also adopts a reference to a column number rather than an actual number. The reference H$178 is essentially the same as referencing column 4. This is the complete formula below; it's wrapped with an IFERROR function to ensure blank cells do not cause issues with # N/A and the ISBLANK function to return nothing if the lookup cell is empty.

=IFERROR(IF(ISBLANK($B181),"",
IF(C181>VLOOKUP($B181,'Call Staging'!$B$333:$M$345,H$178,FALSE),
CHAR(9650),CHAR(9660))
&" "& 
ROUND(ABS(VLOOKUP($B181,'Call Staging'!$B$333:$M$345,H$178,FALSE)-C181) / VLOOKUP($B181,'Call Staging'!$B$333:$M$345,H$178,FALSE)*100,0)
&"%"))

Conclusion

In conclusion, leveraging formulas and conditional formatting in Google Sheets provides a powerful tool for visualising and understanding changes in data over time. Whether you're tracking sales, expenses, or any other metric, this approach helps you make informed decisions based on a clear and concise representation of the data.

Remember, the key to effective data analysis is not just in the numbers but in the insights derived from them. Happy spreadsheeting!