How to Calculate Percentage Differences Between Two Numbers in Excel - Cell Percentage Change Tutorial

Originally published for freeCodeCamp

Spreadsheets are powerful and awesome. 💪

In this tutorial I will show you four ways to find the percentage difference between two numbers in Excel. I'll also show you how to use custom functions in Google Sheets. 👍

The four techniques (and one bonus) we'll use are:

1. Using a Formula (lvl 1 easy mode)

2. Using the LAMBDA FUNCTION + Name Manager (lvl 2 normal mode)

3. Using Visual Basic for Applications (VBA) (lvl 3 hard mode)

4. Using the Office JavaScript API (lvl 4 ultra-mode)

5. Using Custom Functions with Google Sheets (💥 bonus level)

Formula for Percentage Change Between Two Numbers in Excel

The formula is the first thing most people will reach for when making a calculation in Excel. It allows us to make explicit calculations using data in cells.

Suppose we have sales data for one year in cell B3 and sales data for the second year in cell C3. By typing the formula below we can calculate the percentage difference from the first year to the second:

=(C3-B3)/B3

Typing a custom formula has the advantage of being quick and straightforward, especially for simple calculations.

Additionally, formulas may be copied down and/or across cell ranges for quick reuse. And formulas are used in exactly the same fashion in Google Sheets as in Microsoft Excel.

However, when calculations become lengthy and/or complex, it can be helpful to know about some alternative methods.

How to Use a LAMBDA Function and Name Manger

Building on our first example, the LAMBDA function allows us to take a custom operation and codify it for reuse throughout our worksheet.

Using the same data as before (this time in cells B4 and C4) we write the LAMBDA Function like so:

=LAMBDA(year1,year2,(year2-year1)/year1)(B4,C4)

At first glance you might wonder why on earth we should type out this lengthy mess, but hang with me and you'll see it is arguably cleaner for reuse than simply defining a function.

Here's what's happening:

The first thing we're doing is defining the parameters of our function and separating them by commas. You can define as many of those as you need (well, up to 253 that is 🤣). We only have two: year1 and year2.

After listing the parameters, we write the formula we want Excel to calculate. This is the same thing we did in the first Formula section only this time we're using our parameter names instead of the explicit cell names: (year1-year2)/year1.

Last, we close the parenthesis of the LAMBDA function and then call it by writing the actual cells being used: B4,C4. This is telling the function that it needs to use the value in cell B4 for the parameter year1 and the value in cell C4 for the parameter year2.

What a mess, right!? Yes, and technically, writing out the whole LAMBDA function here is simply good practice to make sure the thing works before we do the next step...

This is the cool part. Click the Formula tab in the Ribbon at the top and select Name Manager.

Select New.

Then enter the Name of your formula and write an optional description in the Comments. In the Refers to line, you'll copy in the LAMBDA Function.

You'll be able to use it in the same way you would use a built-in function by typing the following into a cell

=Percentage_Change(B5,C5)

Now we have all the ease of a regular built-in function at our disposal. Google Sheets has similar functionality, which we'll discuss at the end of this article.

How to Use Visual Basic for Applications

If you're using the desktop version of Excel, you have access to Visual Basic for Applications. This is an event-driven programming language by Microsoft and you can use it to do almost anything you can dream (and code) up.

If you wanted to find the percentage difference between two numbers using VBA, you would go to the Developer tab in the Ribbon (or press alt + F11).

If you don't see the Developer tab, you may need to enable it by selecting File -> Options. Then look for Customize Ribbon. From here you can select the box next to Developer.

Bonus: If ALT + F11 doesn't work, GeForce Experience may be interfering with the built-in shortcuts. Change the shortcut for whatever is using ALT + F11 in the settings. For me it was the Toggle comments on/off while broadcasting to Facebook setting.

Once you've opened the VBA window, select Insert -> Module from the menu. This will open up a blank window where we will write our program. Think of this like an IDE inside Excel. We'll program here and then utilize that program in our worksheet.

Here we can enter the same type of commands we used with the Named LAMBDA function above.

Function PERCENTFUNCTION(year1, year2)
    PERCENTFUNCTION = (year2 - year1) / year1
End Function

And voil! We can now use PERCENTFUNCTION in our Excel Sheet in the same way we used the Percentage_Change named function.

VBA is useful for more complicated programs and would be overkill for our example. Incidentally, Google Sheets does not have VBA functionality.

How to Use the Office JavaScript API

Now the real good stuff! Did you know you can write JavaScript and TypeScript within Excel? Me either. But you can.

Script Lab is an Add-on by Microsoft that allows us to explore the JavaScript API within Office Apps as well as declare custom functions by writing them as scripts.

You can add it to Excel here. And read more about it here.

Unlike VBA, this is usable on the web version of Excel too.

Once it's installed, select it from the Ribbon and click Code.

This will bring up a legit code editor on the sidebar.

We can create a custom function using JavaScript by selecting a New Snippet from the Hamburger menu at the top left of the Scripts Lab window.

By typing the following function we can again define a percent difference function, but this time using JavaScript.

/** @CustomFunction */
function percent_change_javacscript(year1, year2) {
  return (year2 - year1) / year1;
}

In order to use this function, select Script Lab -> Functions from the Ribbon:

This will open another sidebar tab and because of the first line in the snippet: /** @CustomFunction */ it will register that custom function.

In the worksheet, you'll be able to use it just like we've been using custom defined functions. This time, though, when you start typing the title, you'll see it registered with a scriptlab prefix on the name. Select this, and it will return the percent change just like the other methods.

Once again, this is major overkill for a simple function, but quite handy to put in your toolbelt nonetheless! 👍

How to Use Custom Functions with Google Sheets

As promised, here's a bonus for how to create a Named Function in Google Sheets. This is very similar to using the Name Manager in Excel.

Select Data -> Named Functions in Google Sheets.

This will prompt you to name and describe your function as well as provide arguments, if applicable.

Last, you'll define the function's operation.

The next screen prompts you to add argument descriptions and examples if you'd like. This is optional, but will be included in the drop down help menu when you use the function in your sheet.

Then it's as easy as typing in your custom function and selecting the cells. You can see below how the help menu is displayed with the information you provided.

Conclusion

Yes, often you will opt for simplicity's sake to use a quick formula in Excel or Google Sheets. But now you know several other ways to find the percentage change between two numbers.

I hope you've found this useful, and good luck in your own spread-sheeting adventures!

You can find and follow me on YouTube & LinkedIn. I'd love it if you said hey! 👋