Naming Cells in Excel

Microsoft Excel 2016: Part 5

Naming Cells

Creating a Drop-down List,

Conditional Formatting,

Sort/Filter/Divide Data

Naming Cells in Excel

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference,

constant, formula, or table, each of which may be difficult to comprehend at first glance. For

example, when you are adding up a range of cells that include first quarter sales, such as

(C20:C30), compare how meaningful if you name that range as “firstquartersales” and use in the

formula as follows:

Regular formula to sum up: =SUM(C20:C30)

With the named range: =SUM(firstquartersales)

Syntax Requirements for Naming

Source: Microsoft.com

Be aware of the following rules when you create and edit names.

 Valid characters: The first character of a name must be a letter, an underscore character (_),

or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and

underscore characters.

 Cell references disallowed: Names cannot be the same as a cell reference, such as Z$100 or

R1C1.

 Spaces are not valid: Spaces are not allowed as part of a name. Use the underscore character

(_) and period (.) as word separators; for example, Sales_Tax or First.Quarter.

 Name length: A name can contain up to 255 characters.

 Case sensitivity: Names can contain uppercase and lowercase letters. Excel does not

distinguish between uppercase and lowercase characters in names. For example, if you

created the name Sales and then create another name called SALES in the same workbook,

Excel prompts you to choose a unique name.

 NOTE: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined

name, because they are all used as a shorthand for selecting a row or column for the currently

selected cell when you enter them in a Name or Go To text box. If your data is formatted as a

Table, Excel automatically names that table by using a generic pattern name such as “Table

1”.

Defining Names

You can define names in three ways:

1. Typing in the Name box in the left of the formula bar: This is

best used for creating a workbook level name for a selected

range.

Let’s use WeekTwo worksheet in the ExcelPart5.xlsx workbook to practice this concept. Assume you

are trying to figure out how much total spent on Produce and Meat only.

 Select cell ranges F4:F8 (cells for produce).

 Click in the name box and type in “Produce”. After typing, hit Enter button to complete.

 Select cell ranges F13:F15(cells for meat).

 Click in the name box and type in “Meat”, then hit Enter.

 Now you have two cell ranges named Produce, and Meat to use in

formula.

Let’s use these two names in a formula:

 Select an empty cell, cell H2.

 Start typing the =SUM formula “=SUM” followed by the open

parenthesis and the letter “pr” to look for the name “Produce”. The

name “Produce” will show up on from the Formula AutoComplete

drop-down list. Double-click on it to select it. [Alternatively, you can

type in the entire word “Produce”.]

 Type in comma “,” on keyboard to add “Meat” cells into the formula.

Start typing the letter “me” and “Meat” will show up top on the list.

Double-Click to include that in the formula and then type in close parenthesis “)” on keyboard

to end the formula. Then hit Enter.

 Place close parenthesis and hit Enter. You will get the sum result of those cell ranges (F4:F8

and F13:F15) in cell H2. The answer should be 47.31.

2. Select existing row and column labels: You can use the Create from Selection command

(under Formulas tab in Defined Names

group) to conveniently create names

from existing row and column labels by

using a selection of cells in the

worksheet. Let’s use My List worksheet

to practice the following concept.

 On the My List worksheet, select the entire column A or block cell ranges A1:A10, and click

on Create from Selection command. Note: If you use the range of cells, you will have to

extend the cell ranges later if you add more on the named list. By using the entire column

would give you a freedom of adding to or deleting

from your list in later.

 Create Names from Selection box comes up. Accept

to use Top row as the name and click on OK. Now you

have the name “Category”, the same as your column

heading.

 Do the same for the columns with headings: Unit.

 You should be able to see two names in the drop-down list on the name box. If your

column heading has more than one word, an “underscore” will be

automatically placed between those words.

3. Clicking on the Define Name in the

Defined Names group will bring up New

Name dialog box. This is best used for

when you want more flexibility in

creating names, such as specifying a

local worksheet level scope or creating a

name comment.

 In the same worksheet – My List,

select the cell ranges C1:C19.

 New Name dialog box will

appear.

 The name “Monthly_Expense”

will appear (Note the underscore as names has space in between two words) in the

Name box.

 To specify the scope of the name, in the Scope drop-down list box, select Workbook or

the name of a worksheet in the workbook. (Using the Workbook will allow you to use

this name anywhere in the entire workbook while selecting a particular worksheet will

only allow you to use the name for the chosen worksheet.)

NOTE: By default, names use absolute cell references.

 Click on OK.

Besides, typing the entire name out in the

formula or selecting from the Formula

AutoComplete as shown previously, you can

use Use in Formula command in the

Defined Names group. This command will become active after you create

a name. Click on the drop-down arrow and select a defined name from a

list available to include in your formula.

We are going to use these names in formulas in creating drop-down list in next section.

Editing or Deleting Names

You can use the Name Manager command in the Defined Names group to: create a new name, edit an

existing name or delete a name. Note: if you delete an existing name being used in a formula,

deleting that particular name here will create an error in the cell that contains that formula.

To delete, select a name by clicking on it. Then Delete.

 To select more than one name in a contiguous group, click and drag the names, or press SHIFT

and click the mouse button for each name in the group.

 To select more than one name in a noncontiguous group, press CTRL and click the mouse

button for each name in the group.

 Click Delete. You can also press the DELETE key. Click OK to confirm the deletion.

Creating a Drop-down List:

You can make a worksheet more efficient by providing drop-down lists. It is especially useful when you

want users to limit the data entry to what’s available in the list. Users can click an arrow and then click

an entry in the list. Note: You can create a drop-down list for a single cell or block of cells. You can

copy and paste a list. You can also use the fill-handle to copy the list to adjacent cells like a formula.

We have named a number of block of cells above (“Category”, “Unit”, “Monthly_Expense”) to use in

our formula. Look in My List worksheet in Excel Part5.xlsx.

Let’s use the Practice worksheet from same workbook to practice this concept. Things to consider

before creating a drop-down list.

 It is recommended to create entries for your list in a row or a column in a separate worksheet

in the same workbook. (We have this done in our workbook. The worksheet My List contains

the lists we want to use.)

 You should sort your list the way you want it to appear. (Our lists are sorted.)

 You should name the block of lists to use in the formula. (We have named our lists.)

Drop-down list in Settings:

Let’s create a drop-down list for Category in cell A2 in

Practice worksheet.

 Click cell A2 in Practice worksheet.

 Click Data >Data Validation in the Data Tools

group.

 On the Settings tab, in the Allow box, click List.

 Check the In-cell dropdown box.

 If it’s OK for people to leave the cell empty, check

the Ignore blank box.

 Click inside the Source box; click on Formula tab;

Click on the arrow next to the Use in Formula and

select “Category” from the list.

 Now you will see an arrow next to the Cell A2

from which you can select your category form the

drop-down list.

 You can copy and paste that list to as many rows

below as you may fill in with other categories in

those rows. Copy Cell A2 up to row 9 before selecting any particular category either by copy-

paste or using your fill handle.

 Select Cell D2 to fill in with “Unit” from the list as explained in same steps above. Copy cell D2

up to row 9 too.

 Select Cell I2 to fill in with “Monthly_Expense” from the list as explained in same steps above.

Copy cell I2 up to row 9 as well.

Now practice filling in data in a few rows beginning in row 2 through 9 in Category, Unit and

Monthly_Expense columns. Your data should look like the data shown in rows 12 through 19

when finished.

Input-Message: When you want to caution the user

before entering data or give reminder for a desired

result in any particular cell, use this function.

 Click Data >Data Validation in the Data Tools

group.

 Click the Input Message tab.

 If you want a message to pop up when the cell

is clicked, check the Show input message

when cell is selected box, and type a title and

message in the boxes (up to 225 characters).

Example on the right show a reminder

message appear in a particular cell to remind the limit of weekly grocery expense. Try that in

cell F10.

Error Alert:

This function is used to “stop” the user from

inputting the invalid data. You will have

to use it in combination with the

“settings” function. Assume, in this

Practice sheet example that you want to

limit the user not to exceed the monthly

food expense of $750.

You can place your limits as follows:

 Let’s create the rule in Cell J9.

 Click on Data>Data Validation.

 In the Settings tab, select:

o Whole number in the Allow

section

o Less than or equal to in the

Data section

o Set the number to 750 as

shown.

 Next, click the Error Alert tab to

enter your “error message” if an

invalid data is placed in those cells.

Under Style, using Stop will not let

the user input any invalid data while

Warning and Information will show

the error message but will still let

the user input invalid data. Make

sure the Show error alert after

invalid data is entered box is

checked to show the Error

Message. Enter the proper error message in Error message box. Click OK.

 Entering any data more than 750 will give you an error message in cell J9.

 If invalid data is placed in those cells

and if you had used the Stop under the

Style to stop the user for doing so, a

window will pop up and make the user

correct the data until it meets the

criteria.

Conditional Formatting

This command can give you a visual analysis of your raw data to detect critical issues and identify

patterns and trends by applying formatting—such as colors, icons, and data bars—to one or more

cells based on the cell value. To detect the trend correctly over a period of time, it is recommended

to exclude the column or row with total values. To learn this command, open ExcelPart5.xlsx

workbook and use the worksheet ConditionalFormatting.

 We want to learn whether all sales people are meeting their monthly quota of $5000. We will

apply the rule as - “If the value is greater than $5000, color the cell green." By applying this

rule, you'd be able to quickly see which cells contain values over $5000.

 Select the desired cells for the conditional formatting rule. In our example, cells B3:G23.

 From the Home tab, click the Conditional Formatting command. A drop-down menu will

appear.

 Hover the mouse over the desired conditional formatting type, then select the desired rule

from the menu that appears. In our example, we want to highlight cells that are greater than

$5000.

 A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll

enter 5000. If you’d like to have a different formatting, click the drop down arrow and change

the style to your choice such as “Red Text” or “Red Border”, etc.

 The conditional formatting will be applied to the selected cells. In our example, it's easy to see

which salespeople reached the $5000 sales goal for each month.

Multiple Conditional Formatting Rules: You can apply multiple conditional formatting rules to a cell

range or worksheet, allowing you to visualize different trends and patterns in your data.

 For example, if you wanted to see

how many cells in that selected

data has unusually high data, use

the color data bar to identify the

cells. The larger the data, the

longer the color bar will be. In our

example, select the Purple Color

Bar for the same cells B3:G23.

 The new formatting with color

data bar should apply over the

previous conditional formatting of

values more than $5000. See

below.

To remove conditional formatting:

 Click the Conditional Formatting command. A drop-down menu will appear.

 Hover the mouse over Clear Rules, and choose which rules you wish to clear. In our example,

we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the

worksheet.

Sorting

When your data has increased in size, you may want to organize more systematically for easy retrieval.

Use Sort function to organize a list of information alphabetically, numerically, and in many other ways.

When sorting data, it's important to first decide if you would like the sort to apply to the entire

worksheet or just a cell range.

Sort Sheet by One Column: Organizes all of the data in your worksheet by one column. Related

information across each row is kept together when the sort is applied. Let’s use the same worksheet

Spring to practice. In the example below, we want to sort by the name of the Employees (column A).

Follow the steps. Click on Data tab.

 Click on any cell in the Employee column.

 Click on either AZ for Ascending or

ZA for Descending order.

 The entire worksheet will be sorted

by the Employee column.

Sort Sheet by One or More Columns: You can use this command when you have more than

one column to sort your data.

1. Click the sort command shown on right.

2. The Sort dialog box will appear.

3. Under Column, in the Sort by box, select the column that you want to sort. Select Car Rental.

4. Under Sort On, select the type of sort. In our example, keep at values since we are sorting the

number values.

5. Under Order, do one of the following:

o For text values, select A to Z or Z to A.

o For number values, select Smallest to Largest or Largest to Smallest. We will select

Smallest to Largest for this example.

o For date or time values, select Oldest to Newest or Newest to Oldest.

6. Check the My data has headers box to indicate if you have a Header row (labels at the top

row of the columns like in this example) or No header row (if none). Normally, Excel can sense

the column headings and the selection box is already marked if number values are detected in

one of the column.

7. To add another column to sort by, click Add Level (up to 64 levels) and then repeat steps 3

through 5 as necessary. To delete a level, click Delete Level.

Note: If you are sorting rows, then click Options and change the Orientation to Sort left to right. Just

remember to change it back when you resume sorting columns.

Sort a Specific Cell Range: If your worksheet has different sets of data and you only want to sort a

certain part of the worksheet, select that range of data only before you apply sort either by one

column or by multiple columns as explained above. By selecting a specific range of cells, the other

content in the worksheet was not affected by the sort.

Sort by Cell Formatting: One useful feature in Sort command is

that you can sort your data based on the Cell Color. This

feature can be found under the Sort On drop-down list in the

Sort dialog box. This feature is especially useful if you format

your cell to show with a particular color by using the Cell

Formatting function then want to sort out those cells in color.

Assume we have a workbook tracking on payments for different regions by Sales Reps. Your

conditionally formatted worksheet shows Full Payment in Green, Billed in Yellow, and Overdue in Red

colors already. Open the worksheet SortbyColor in the same workbook to practice.

You want to sort those lines with Overdue on top then Billed followed by Full in Payment column.

Follow these steps:

 Click anywhere within the data on the sheet.

 Click on the Sort function under the Data tab.

 Select Payment for Column box; Cell Color for Sort On box; and Select

the Red color first to show Overdue and select On Top in next box.

 Click on Add Level button to repeat the process above to add the Yellow color and Green

color to be sorted in that order.

 Click on OK.

 Your data should be sorted by Overdue rows followed by the Billed and Full in Payment column

as below.

Filtering Data:

If you further want to filter out your data into a smaller list to view or print for a particular purpose,

use AutoFilter function in Excel. In order for filtering to work correctly, your worksheet should include

a header row, which is used to identify the name of each column such as in our example, Order Date,

Item, Region, etc. Let’s say, in our example, we want to just view data for a region separately.

 Click anywhere within the data. Select the Data tab, then click the

Filter command.

 A drop-down arrow will appear in the header cell for each

column.

 Click the drop-down arrow for the column you wish to filter. In our

example, we will filter column B to view only certain regions.

 The Filter menu will appear.

 Uncheck the box next to Select All to quickly deselect

all data.

 Check the boxes next to the data you wish to filter,

then click OK. In this example, we will check Central to

view only that region.

 The data will be filtered, temporarily hiding any

content that doesn't match the criteria. In our

example, only Central Region is visible.

Applying Multiple Filters:

You can apply multiple filters to help narrow down your results. In our example, we've already filtered

our worksheet to show the Central Region only, and we'd like to narrow it down further to only show

rows with Overdue in Payment column. In the same worksheet SortbyColor.

 Click the drop-down

arrow for the column you

wish to add filter. In this

example, we will add a

filter to column H to view

information by Payment.

 The Filter menu will

appear.

 Check or uncheck the

boxes depending on the

data you wish to filter,

then click OK. In our

example, we'll uncheck

everything except for

Overdue.

 The new filter will be

applied. In our example,

the worksheet is now

filtered to show only

Overdue from

Central Region.

Clearing Filter: If you want to clear filters one at a

time, click the drop-down arrow for the filter you wish

to clear. Choose Clear Filter From [COLUMN NAME]

from the Filter menu.

To remove all filters from your

worksheet, click the Filter

command on the Data tab again.

Clear all filters for next topic.

Divide Spreadsheet Data into the Smallest Parts:

Information in a column can be divided into the smallest parts for each filtering. For example, if a

column contains both last and first names together, you can split that column into two separate

columns; one for the last names, and the other for the first. By doing so, you can filter your data more

efficiently. Let’s use summer worksheet in the same workbook to practice. First, select the entire first

row that has the Title “Travel Expense Log Sheet” and delete it. In your spreadsheet where in column

A, both last and first names are entered. We want to separate column A into two columns: one for the

last names and the other for the first.

 First, an empty column needs to be inserted between columns A and B to

make a space for the newly separated last name column. Select the entire

column B and click on the Insert command under the Home tab in the

Cells group. A new empty column will be inserted between Employee and

Registration columns.

 Select the entire column A (the column you wish to divide into two).

Click the Data tab and click on the Text to Columns in the Data Tools

Group. Convert Text to Columns Wizard dialog box will appear.

 You are asked to choose between the Delimited or Fixed width option buttons—although Excel

automatically will suggest something for you. To understand the

choices, you must understand what is meant by a delimiter. A

delimiter is simply a character that identifies (delimits) the end of one

number or word and the beginning of another. The character can be a

comma, space or a tab. Excel is smart enough to examine your data

and suggest whether you have delimited or fixed-width data. If your

data appears in neatly aligned columns, as shown in the section of

image on the right , it will select the Fixed width option button. If the

data does not appear in neatly aligned columns such as in our example

where the width of each first and last names are not aligned or even, it

will choose the Delimited option button. Click the Next button to go

onto step 2.

 Check the space box as we have a

space between the names; uncheck

the Tab box; click on Next.

 Click Finish to finalize your process

and confirm to replace the empty

new column you just added above

with the split data by clicking on

OK.

 The names column now should split

into two columns: last and first

names. See below. You can

rename the header rows

appropriately if you desire.

Combine two or more columns by using a function

Suppose you like to put together two or more columns of data that you want to combine in a single

column, such as the name and phone number of a person. To combine two or more columns, use the

CONCATENATE function in a formula in a nearby cell (typically to the right of the last column of data

that you want to combine), and then drag that formula down through the rows that contain the data.

When you create your formula, you can add a space or comma to cleanly separate names and

addresses in the new column by enclosing them in quotation marks (" "). See below image. In this

example, The CONCATENATE function combines column A, a space character (enclosed in quotation

marks, like this: " "), column B, another space character, and column C into a single column D.

Open CombineCols worksheet and try formula in D2. Combine columns A, B, C into a single column D.

Alternatively, you can also use the “&” in place of comma “,” to get the same result as above.