Creating a Google Sheets Specific Use Calculator Using Functions, Conditional Formatting, Data Validation, and Sheet Protection

Hi, I'm Smart_TJ, and for this project I will be showing you how to build a google sheet using various features in order to create a function specific calculator. For this project I will be showing you how to create a calculator that takes budgeted amounts, applies tax to them, and compares them to your budget, but you can use it to create a calculator for pretty much anything you might need in day to day life.

I decided to make this project after doing some research on functions in google sheets for school projects, with me introduced to the basics by This article that shows how to use basic functions in google sheets using calculators such as the one I will be showing you here now as an example. However, I have since learned several more advanced features of google sheets that can help make more effective and user friendly calculators

Supplies

A google account

A computer

That's it

Step 1: Create a Google Sheet

This step is fairly easy and simple. All you need to do is go to the "new" button, click on it, and find the option for "Google Sheets" and click on it. This will create the google sheet used in this project

Step 2: Create Your Categories

Before we can start calculating data, we need to figure out what we're calculating. In this case, we have a table containing common expenses and total on the y axis (the left side), and their base prices, tax, and prices with tax on the x axis (across the top), as well as having specific spots for the budget and price comparison. For yours, figure out what you're wanting to calculate and make a table (or possibly more than one) and individual entries accordingly.

Step 3: Format Your Data Columns

Before we start putting in data, we need to make sure its formatted correctly for the type of data. Google Sheets has preset formats in your toolbar for currency and percentages, which we will be using here, but if you click on the button that says "123", you will drop down a menu with all sorts of other formats and custom format options. We'll have the B and D columns be formatted as currency, and C as a percentage in this case.

Step 4: Data Validation

The next step is data validation. This will warn you when you input a piece of data that is invalid. In smaller sheets this might not be needed, but it's a nice touch, and in larger sheets can become more necessary. To do this, go to the "Data" menu, and select "Data Validation". It should bring up a popup menu titled "Data Validation".

Basically, what this is going to do is allow you to set a set of criteria for certain cells that will display an error when certain things are entered into them, as seen in the bottom and middle right pictures for this step.

This has a few different things you can adjust. First is the "Cell Range". This dictates what parts of the sheet are affected by the Data Validation. Whatever cells were selected when you open the Data Validation menu will currently be listed here. You can type in a new cell range if you don't like the listed one, or click on the icon of 4 cells in the right edge of the text field to select new ones. The second thing is your criteria type and specific. The dropdown menu allows you to pick what kind of criteria it is, such as a number within a range, a specific formula being included, etc. In this case, we will be using the number field. Now, for this specific purpose we just want to make sure that its a number. So, we're just going to have it be between as large of a negative number as seems appropriate, and a positive number that is just as long. Now, we can either set it to reject an invalid input, or display a warning message. What I've shown here is a warning message so that you don't loose anything valid. Finally, we can have it display help text while showing a warning, which should be set up to tell you what's wrong with it.

We'll apply these conditions, whatever conditions we decide on, to every place we'll be entering data or a formula.

Step 5: Entering Data and Using the Sum Function

Next, we'll enter our first section of data, as can be seen above. Then, we'll enter a "Sum" function. A sum function allows you to perform basic mathematical such as addition, subtraction, multiplying, and dividing. For our first use, all we need to do is add things together here. There are two easy ways to do this. The first is to select the ∑ button in the toolbar, which will automatically generate the function, and all you need to do is select the cells being added and then press Enter. Alternatively, you can just type is out by typing =sum( and then selecting the cells.

Step 6: Adding Tax and Tax Calculations

Next, we'll enter our tax percentages into the "tax" column. Next, we'll be using the sum function again. However, we'll be using it differently this time. This time, we'll be inserting something like this: =sum(B2+(B2*C2)). This multiplies the initial price times tax, thus finding how much tax costs, then adds it back to the original price.

Step 7: Extending the Formula to Other Cells

Spreadsheets software has an interesting and very useful function where if you click on the bottom left corner of a selected cell, you can drag it down into other cells, thus essentially copying the contents of the initial cell into any others that it is drug into. However, this is not simply a quicker copy paste. It looks for patterns and accommodates them. So, in this case, the formula being copied into the other cells is adjusted for their respective rows, meaning that they calculate from the entries in their new row rather than the old one. This means that we don't have to spend time writing out the same function over and over again with the only change being the cells used.

Step 8: More Sums

The next step in our example is simple, just another sum like the first, but this time in a different column.

After that, then we enter a number for our budget. Next, its time to calculate our costs versus our budget. We'll use another sum function to subtract the final total price (after tax) from our budget.

Step 9: Conditional Formatting

Next, we'll be using conditional formatting to give a quick and easy gauge of our costs vs our budget. We'll need to go to the format menu and select "Conditional Formatting". Now, from here we can also set it to check a range of cells for specific or a range of entries and change the cell's text properties such as font styles, font type, font size, font color, background color, etc. However, here we'll be using color scaling. The "Apply to Range" section is similar to how the earlier Data Validation worked. After that, you'll need to select either end and the middle of a scale, as well as the colors in each of those places. If the number is above or below the relative max or min, the color will simply show as the color selected for that end. What you'll want to do is select a range that's a fair bit above and below your expected results, but only 2-3 times that. In this case, I chose -1,000 as the minimum, 0 as the middle, and 1,000 as the maximum. Now, the higher the amount left, the darker the green it will show as, and the further below zero, the darker the red it will show as.

Step 10: Sheet Protection

Next we'll be adding in Sheet Protection to make sure that we don't unintentionally edit cells that we don't want to edit, such as section titles and formulas.

There's two ways to do this.

First is a range. This will protect anything within a single range, and not affect the rest of the sheet, so it's better for smaller sheets. However, it can only do one range at a time, so on larger sheets, not so much. You select the range the same way as any other range.

Second is a sheet. If you pick the sheet option, it will, by default, protect the entire sheet. However, if you select "Except Certain Cells", you can pick any number of ranges to be left unprotected. The problem is that it by default protects every cell in the sheet, so many cells that you may not want protected will now be protected, but, since you can pick any number of ranges at once, it makes protecting larger sheets far quicker.

After telling it to set permissions, a message will pop up asking if you want it to only allow certain people to edit it, or prevent other people from editing it. You'll want a warning assuming that it's a personal use thing, so that it actually does what it's intended to do- prevent you from accidentally messing things up. It does this by popping up a message making sure that you're sure about the edit, and offers to not provide warnings for the next 5 minutes.

Step 11: Test Out Your Calculator!

You've now finished creating a purpose-specific calculator! While this project only shows one specific project, it has also shown you how things work so that you can adapt what you've learned from this to fit your own project. I hope this has been helpful for you.