Office 2016 For Seniors For Dummies (2016)

Part III

Excel

For an explanation of Excel's IF function, visit www.dummies.com/extras/office2016forseniors.

Chapter 10

Using Excel as a Database

Get ready to . . .

Understand Databases

Prepare a List for a Mail Merge

Store Data in a Table

Sort a Table

Filter Data in a Table

Split a Column’s Content

Merge the Contents of Columns

Besides calculations, the other main reason people use Excel is to store tabular data. For example, Excel works great to store the names and addresses of the people you send holiday cards to every year, or to store a home inventory that you create for insurance purposes.

Although spreadsheet programs like Excel weren’t originally created for this purpose, software designers quickly figured out that a lot of people were using Excel to hold lists of data, so they built in features to help do that more effectively. In this chapter, you can read about several Excel 2016 features — such as tables and filtering — for entering and manipulating data.

Understand Databases

A database is an organized collection of information about a subject. Examples of databases include an address book, a telephone book, or a home inventory.

Database data is stored in tables, which are row-and-column grids. Although Excel isn’t a full database program, the row-and-column format of its worksheets is easily adaptable to a simple database.

Each row is a record: a set of details about a specific item. For example, a record for a friend in your address book likely contains his name, mailing address, and phone number. A record about a possession in your home could include a name, description, acquisition date, and retail value. Each piece of information you store for each record is a field. Field names always appear in the first row of the table. See Figure 10-1.

Figure 10-1

Prepare a List for a Mail Merge

One reason why you might create a database in Excel is to store names and addresses for a mail merge — perhaps with a letter you created in Word, for example. (Read all about mail merges in Chapter 6.)

Excel is a great tool for creating such a list. There are just a couple of basic rules:

· The first row (row 1) must contain the field names.

· The data must start immediately beneath the field names (row 2).

When storing lists in Excel, some people like to put a descriptive title in row 1, like “Address Book.” That’s fine if you’re just going to be working in Excel, but it wreaks havoc with a mail merge — so you must delete any such rows in Excel before you use the Excel file for a merge. To delete a row, select it and then choose Home ⇒ Delete.

Store Data in a Table

To create a database in Excel, type the field names in row 1, and then type the records in the subsequent rows. Nothing else is required. You can format the data any way you want it.

However, if you choose to turn a range of cells into a table (Excel’s term for a database listing), you get some special benefits, such as easy-to-apply automatic formatting and easier sorting and filtering.

A table in Excel is entirely different from a table in Word. In Excel, a table is a range of cells defined as a database structure. In Word, a table is a simple row-and-column grid for holding text.

To convert a range of cells into a table and apply automatic formatting at the same time, follow these steps:

1. Select the range, including the field names in row 1.

2. Choose Home ⇒ Format as Table. A palette of table styles appears. See Figure 10-2.

3. Click the desired style. The Format as Table dialog box appears.

4. Confirm the range that appears in the box, and then click OK. Excel applies the formatting, and also sets up the data range as a table.

Figure 10-2

If you want just the formatting — and not the other features that a table provides in Excel — choose Table Tools Design ⇒ Convert to Range after performing these steps.

Setting up a range as a table has many benefits, including

· Sorting and filtering: Click the down arrow that appears next to each field name to open a menu from which you can sort and filter the data easily. (This is covered in more detail later in this chapter.)

· Automatic table formatting: Additional records (rows) you add to the table after its initial creation will be automatically formatted in the same way.

· Other formatting options: You can apply different formatting at any time with the Table Tools Design ⇒ Table Styles gallery. (The Table Tools Design tab is available only when working with a table.)

The drop-down arrow buttons that appear on each field name are for your convenience; you can click one to open a menu for that column for sorting and filtering the data (described later in this chapter). If you don’t want to see the arrows, you can turn off their display by choosing Data ⇒ Filter. (That’s an on/off toggle; choose that same command again to make the arrow buttons redisplay.)

If you ever want to revert to a regular range (as opposed to a table), you can easily convert the data back to normal cells. Click anywhere in the table and choose Table Tools Design ⇒ Convert to Range.

Sort a Table

Sorting rearranges the order of the records to meet criteria you specify. For example, you might sort an address list on the person’s last name or city.

If you convert the range to a table, as in the previous section, each column heading has a drop-down arrow button on it. Click that arrow to open a menu. From that menu, you can choose to Sort A to Z or Sort Z to A on that column. See Figure 10-3.

Figure 10-3

If you don’t want to use the drop-down menu on the column, you can instead click anywhere in the column and use buttons on the Ribbon:

· Home ⇒ Sort & Filter ⇒ Sort Smallest to Largest (A to Z)

· Home ⇒ Sort & Filter ⇒ Sort Largest to Smallest (Z to A)

· Data ⇒ Sort A to Z

· Data ⇒ Sort Z to A

Not all of these commands are available on every field’s menu. Columns with numeric values show Smallest to Largest and Largest to Smallest, whereas columns with text values show Sort A to Z and Sort Z to A.

The method I just showed you works great for single-field sorts. Sometimes, though, you might want a multifield sort, in which you sort the list by one field, and then if two or more records have the same value for that field, another field is the tie-breaker.

For example, you might sort by the Last Name field; and if two people have the same last name, you break the tie by sorting on the First Name field.

To do a multilevel sort, follow these steps:

1. Click anywhere inside the table.

2. Choose Data ⇒ Sort, or Home ⇒ Sort & Filter ⇒ Custom Sort.

3. In the Sort dialog box that opens, open the Sort By list and choose the field by which to sort first. For example, you might sort an address list by Last Name.

4. Leave the Sort On box set to Values.

5. Leave the Order box set to the default (A to Z for a text field, for example), or change it to the opposite order if desired.

The choices in the Order box depend on the type of data. For example, if the field holds dates, your choices are Oldest to Newest or Newest to Oldest.

6. Click the Add Level button to add another set of controls. See Figure 10-4.

7. Repeat Steps 3-5 to complete the second set of controls.

8. Add other levels if needed. When you’re finished, click OK.

Figure 10-4

You can sort a regular range of cells (that is, not a table), but you have to select the entire range before performing the sort. It’s much easier if you convert the range to a table first, as I show you how to do earlier in this chapter.

Filter Data in a Table

Filtering data enables you to temporarily hide certain records so that the ones you do want to see become easier to find. Filtering is based on criteria that you specify. For example, you might want to see only items of a certain category or only people who live in a certain state.

The simplest way to filter is to choose which of the existing values for a particular column to include. Follow these steps:

1. Click the down-arrow button to the right of the column name to open its menu.

If you don’t see down-arrow buttons on the column names, choose Data ⇒ Filter to make them appear.

2. A list of all the entries in that column appears, with check boxes for each one. Mark or clear the check boxes as needed to choose which values to include. See Figure 10-5.

3. Click OK at the bottom of the menu. The list is filtered as you specified.

Figure 10-5

If you want to turn off the filtering, you can do any of the following:

· Reopen the menu and mark the Select All check box.

· Choose Data ⇒ Clear.

· Choose Data ⇒ Filter to toggle off the filtering (and also the down-arrow buttons).

Choose Data ⇒ Filter again to turn the button display back on.

If your filtering needs are more complex than a simple inclusion or exclusion, use one of the filters from the Text Filters submenu. (Well, it’s Text Filters if that field contains text; otherwise it’s Date Filters, Number Filters, or whatever is appropriate for the field type.)

Follow these steps to use a filter from the submenu:

1. Click the down-arrow button to the right of the column name to open its menu.

If you don’t see down-arrow buttons on the column names, choose Data ⇒ Filter to make them appear.

2. Depending on the field type, the command you want next will have different names. To open a submenu, point to the appropriate command:

· Text fields: Text filters

· Numeric fields: Number filters

· Date fields: Date filters

3. Choose the filter that most closely matches what you want to do. For example, if you’re working with a Date field, after pointing to Date Filters in Step 2, you might choose Before. See Figure 10-6.

4. The Custom AutoFilter dialog box appears for you to finish filling in the desired filter information. For example, if you choose Before in Step 3, it looks like Figure 10-7, prompting you to specify the date that all included records must be prior to.

5. Type the value you want.

Depending on the field type, helpers might also be available. For example, in Figure 10-7, you can click the Date Selector button to open a calendar from which you can choose a date.

6. Click OK. The filter is applied to the table.

Figure 10-6

Figure 10-7

To remove the filter, choose Data ⇒ Clear, or reopen the field’s drop-down list and choose Clear Filter from fieldname.

Split a Column’s Content

As you’re entering data into Excel, you might not give much thought to how that data will be used later. For example, when entering people’s names, perhaps you used a Name field that contained both the first and last name of each person, as in Figure 10-8.

Figure 10-8

Suppose that you then decide that you want to sort the list by the last name of the person. You can’t do that with the data shown in Figure 10-8 because sorting takes place according to the first characters in the cell, and that’s the first name.

Your best bet is to split the content into two separate cells, so that every field you want to sort or filter on is in its own separate cell. If there are only a few names, like in Figure 10-8, you could easily retype them. But what if there were hundreds?

Excel offers a feature called Text to Columns that can do a split for you automatically. It requires there to be a consistent indicator of where the split should occur, though: a delimiter. For example, in Figure 10-8, each first and last name are separated by a space, so the space is the consistent delimiter (separator) character.

The Text to Columns feature doesn’t work on data that’s in a table in Excel. If your data is in a table (from previous work in this chapter), convert it back to a range with the Table Tools Design ⇒ Convert to Range command before you go any further.

Have a look at the column you want to split, and decide what the delimiter character is. Then follow these steps:

1. Make sure there is an empty column to the right of the one you are going to split. Insert a new column if needed (by choosing Home ⇒ Insert ⇒ Insert Sheet Columns). For example, in Figure 10-8 you would insert a new column between columns A and B.

2. Select the range containing the data you want to split. In Figure 10-8, for example, the range would be A2:A5.

3. Choose Data ⇒ Text to Columns. The Convert Text to Columns Wizard Step 1 of 3 dialog box opens.

4. Leave the Delimited option selected and then click Next.

5. In the next page of the wizard, select the appropriate check boxes to indicate what delimiter character(s) to use. (For example, in Figure 10-9, Space is chosen as the only allowed delimiter character.) Then click Next.

6. In the final page of the wizard, leave the default choices for field types and destinations, and then click Finish. Excel splits the data into the adjacent blank column. Figure 10-10 shows the split data from Figure 10-8.

7. Type a new field name in the first row of the new column, and change the field name for the split column if needed. For example, in Figure 10-10, I might change the text in cell A1 to First, and I might type Last in A2.

Figure 10-9

Figure 10-10

Merge the Contents of Columns

You can start with data in two columns (first and last names, for example) and then combine all the data in one column. The process of combining the columns is merging, or concatenating.

Concatenating doesn’t work with data that’s in a table in Excel. If your data is in a table (from previous work in this chapter), convert it back to a range with the Table Tools Design ⇒ Convert to Range command.

Concatenating two or more columns is a four-step process:

1. Use the =CONCATENATE function to combine the values from two or more cells in the same column.

2. Copy the function to other cells so that the whole list is processed.

3. Use the Paste Values feature to paste the values from the functions into a new column.

4. Delete the original columns containing the split data, and you delete the column containing the functions.

Here’s the process in detail.

In the first step, the =CONCATENATE function combines the values from two or more cells into a single cell. So, for example, if you want to combine the value from A2 with the value from B2 with a space between the two values:

=CONCATENATE(A2,” “,B2)

The commas separate the pieces to be combined. The space in quotation marks (" ") creates the space between the two values.

After creating the function, copy it into the other cells in that column. You can use any of the standard copying methods to do this, including the Copy and Paste buttons on the Home tab, the Copy and Paste shortcut keys (Ctrl+C and Ctrl+V), or dragging the fill handle (the black square in the bottom-right corner of the selected cell).

Now you have a concatenated column, but you also have a problem: You can’t delete the original two columns because the functions are based on their contents. You could hide them, but that would make it awkward later when you added new records.

To work around this problem, you can use the Paste Values feature to copy the value (that is, the result) of the formula to yet another new column. Then you can delete both the original columns and the column containing the function. Follow these steps:

1. If the column to the right of the one containing the functions is not empty, insert a new blank column there.

2. Select the range of cells that contain the CONCATENATE functions.

3. Copy them (press Ctrl+C or choose Home ⇒ Copy).

4. Click in the first cell where you want to begin pasting the values.

5. On the Home tab, click the down arrow under the Paste button, opening a menu.

6. Click the first icon in the Paste Values section.

7. Select and delete the columns that contained the original data and the column that contained the CONCATENATE functions. (Choose Home ⇒ Delete.)