Make Google Sheets Better with Named Ranges

Turn unreadable spreadsheets into readable ones.

Spreadsheets are real game changers for doing almost any kind of work with data. However, if they get complex it can get very confusing to see a formula like =$A2+C5/(1+D12. After all, can you remember what C5 means? Sure, if you have only a few cells, it is easy enough to look, but when you start trying to do major spreadsheets, it gets confusing real fast.

One way to tame a complex spreadsheet is to use named ranges. That lets you give cells or ranges of cells meaningful names. So the above formula might read =InitialValue+TotalOffset/(1+Rate). Much easier to read.

The downside? There aren’t too many problems with named ranges. Probably the biggest potential problem is you can’t expect them to transform like regular formulas when you copy and paste them. That can be a good thing or a bad thing, as we’ll see later.

Look at this simple spreadsheet:

This isn’t that hard, but it will serve as a good example. The data is for a four week class with three students (very small class size, indeed). The cell at B7 has a simple formula: =Average(B2:B5). Sure, with such a simple sheet, it is easy to see what this means, but if you are just looking at the formula, it isn’t that simple.

To create a named range, select cells B2 to B5. Select the Data menu item and then click Named Ranges. That will open up a right-hand sidebar where you can put in a name — S1Grades for this one. You can also create S2Grades and S3Grades for columns C and D. Just to round things out you can select cell B7 and name it S1AVG, cell C7 to S2AVG, and D7 to S3AVG.

Now the formulas are very simple. In cell B7, type: =Average(S1Grades). You can repeat for the other cells using S2Grades and S3Grades, of course. In cell F8 you can write…

Related posts:

The clarithromycin market refers to the industry that produces and sells the antibiotic drug clarithromycin, which is used to treat various bacterial infections. Clarithromycin belongs to a class of…

Everybody love sugar. It is written deep down in our brain. And every software developer is lazy at least a little bit. So you use programming languages instead of burning bits directly in your…

When we think of the term fuel cell, the image that comes to our mind is that of a bulky car-battery-sized device which is normally not portable. But recently, researchers turned that visual into a…