Tip of the Week: How to Use Named Spreadsheet Cells and Ranges

Spreadsheets are a staple in the workplace, but even with these tools to help manage data, it’s easy to lose track of it as the size and scope grows larger. Today, we want to help you better reference your spreadsheets through the use of naming cells.

How Naming Cells Works

Cells are already named according to where they’re located on the x and y axes. This is why you’ll see formulas that are made up of functions followed by an equation. They might look like this: =SUM(A1+B2)

In Microsoft Excel and Google Sheets, you can see a little box to the left of the formula bar. This tells you what the name of the currently selected cell is. You can name cells in such a way where the input is not affected.

The name box gives you the power to create named ranges in the spreadsheet to replace various coordinates you might use in your formulas. For example, if you want to track how many goods or services were provided as different line items, but still wanted to see the total revenue, you could name different cells with the names of your products to get that result.

If you were selling different types of produce, asparagus, carrots, eggplants, and peas, you could name your cells as such and input the total sales for each. For this example, we’ll set our prices at $50 for asparagus, $43 for carrots, $62 for eggplants, and $49 for peas. The total could be calculated with this formula:

=SUM(asparagus+carrots+eggplants+peas)

The result for this would be $204.

You can take an alternative route as well by naming a collection of cells from A1 to D1 “All_Produce” and using this formula:

=SUM(All_Produce)

This feature is under the Formulas tab under Define Name in Microsoft Excel. Google Sheets has it under the Data menu under Named ranges. 

We hope you find this tip helpful!