Top Ten Ways to Manage Your Data

For a great many business people, the Excel worksheet provides the ideal place for managing and manipulating the vast amount of data that your particular job entails. Unlike dedicated database management programs that require a whole lot of specialized knowledge in order to set up and maintain data tables, data management in Excel 2019 requires mastery of only a few special features, all of which function within the confines of the new familiar worksheet grid. Here’s my list of the top ten features for helping you manage large amounts of data in Excel:

10. Format as Table. Don’t forget about the power of formatting the data list you create in a worksheet as a table (Alt+HT). When you do this, Excel formats all the records in your data list according to the table style you select. It also identifies the cell range in the worksheet containing the entire data list as well as the headings in the first line of the list as field names. The program also automatically adds AutoFilter buttons to each field name.

    Selecting the right type of banded table style can make the records in a data list easier to read just as the AutoFilter buttons make it easy to quickly sort and filter your data. On top of this, as you enter new records to a data list formatted as a table, Excel instantly applies the appropriate formatting to the new records as the program extends the cell range containing your entire data table.

    See Chapter 3 for more on formatting your data list as a table.

9. Data Form. As old-fashioned as the Excel Data Form may seem to be, it still provides a really efficient means for not only entering new records into a data list you’ve created in a worksheet but also for quickly finding any records that need updating.

   The only problem is that in order to use the Data Form in Excel 2019, you first have to add the Form command button from among the Commands Not in the Ribbon to either a custom Ribbon tab or to the Quick Access toolbar. Once added, you can use the commands in the Form dialog box to add new records, delete unneeded ones, as well as locate records in the data list that need checking and editing.

   See “Adding records to data lists” in Chapter 11 for more on adding the Form button to the Quick Access toolbar and using its features to maintain your data list.

8. Freeze Panes. Most, if not all, of the data lists you create and maintain in an Excel worksheet will end up having more records than your computer monitor can possibly display all at one time. If you use the Freeze Panes feature to freeze the first row of the data list with the field names, these labels will always remain visible on the screen as you scroll through any part of the data list.

   To freeze the row with the field names of a data list, simply position the cell cursor in any one of the data list’s cells and then select View ⇒   Freeze Panes ⇒   Freeze Top Row (Alt+WFR). After that, you will always be able to see the field names to identify the columns of data as you scroll through the list as well as use their AutoFilter buttons to sort and/or select the data.

   See Chapter 6 for more on Freeze Panes.

7. Sort. Being able to instantly arrange the records in a data list with the various Sort command options in Excel is a key component in maintaining a data list in an Excel worksheet. Sorting enables you to reorder the records of a data list in whatever way to you need to see them, whenever the need arises.

   The easiest way to sort an entire data list in ascending order (A to Z alphabetical and lowest to highest numeric), according to the entries in a single field, is to position the cell cursor in one of the cells in that column of the data list and then select Home ⇒   Sort ⇒   Sort A to Z (Alt+HSS). To sort the list in descending order (Z to A alphabetical and highest to lowest numeric), you simply select Home ⇒   Sort ⇒   Sort Z to A (Alt+HSO) instead.

   When you need to sort the records in the data list on more than one field to get the desired order, use the Sort A to Z or Sort Z to A option on each of the field’s AutoFilter drop-down menus.

   If you want to be able to display the records in your data list in the original order in which they were entered, you need to add a Records Number field as the first column of the data list before you do any sorting. Simply insert a new column at the beginning of the data list (Alt+HIC), label its first cell Record No. (or something like that), manually enter the first record number in the cell below, and then use AutoFill to extend the records numbers down the column to the last row of the list (remembering to hold the Ctrl key down as you drag the fill handle). Use the Convert to Range command on the Table’s Design tab under Table Tools to temporarily convert your data list back into a regular cell range and then use the Format as Table command on the Home tab to format the data list, including the new Record No. field, as table. Once you do this, no matter how the records are sorted, you can always restore the data list to its original data entry order by sorting list on the Record No. in ascending order.

   See Chapter 11 for more on using Excel’s various sorting options.

6. Flash Fill. Some lists of data come to you as text entered in other programs, such as Microsoft Word 2019. When you copy these data into Excel, the text entries are copied into the rows of a single, very wide column of the worksheet. You then need to split the long text entries up into individual components, such as First Name, Middle Initial, and Last Name, so that you can then effectively sort and filter the data list using their separate columns.

   The fastest way to split up text entries entered into a single worksheet column where the individual components are separated by a single space, such as First Name, Middle Initial, and Last Name, is with the Flash Fill feature. In a separate column to the immediate right of the one with the long text entry, type the first entry that should be extracted from the first long text entry (such as the first name). Then, in the cell in the row immediately below this, start entering the second entry that should be extracted from the second of the long text entries. Excel 2019 will then detect the pattern and extract the same components from all the subsequent long text entries and then into separate cells of the new column as soon as you press Enter.

   See Chapter 2 for more examples on how to use Flash Fill to split up a list of full names into their individual components.

5. Text to Columns. Some lists of text data that copy into a worksheet in a single column are separated by other delimiting characters, such as commas, semicolons, or even full colons. When this is the case, you need to use the Text to Columns command on the Data tab rather than Flash Fill to split the entries up into individual columns.

   To use this feature, select the range of cells with the long text entries and then select Data ⇒   Text to Columns (Alt+AE). Excel opens the Convert Text to Columns Wizard where you specify the type of character or characters that separate the components of the long entry. When you click the Finish button after specifying these character(s), Excel splits the long text entries up by entering their delimited components into as many columns of the worksheet as required.

4. Web Query. Rather than making you manually enter all the data you need to follow in your data list, Excel provides several ways to import the data from external sources. If the data you need to track in a worksheet data list is available online in a website, you may be able to import the data directly from a web page into your Excel worksheet using the From Web option on the Get External Data menu.

   Before you can do such a Web Query, you need to know the URL address of the website that contains the data you want to import. The easiest way to do this is first use your computer’s web browser to go the desired website. Then, copy the URL address from browser’s address text box into the Office Clipboard (Ctrl+C). Next, switch back to Excel 2019 and select Data ⇒   Get Data ⇒   From Web (Alt+AFW). Click the Address text box and then paste the URL address into it (Ctrl+V) before you click the OK command button.

   Excel then connects to the web page. Excel opens the Navigator with a list of the tables of data on that page that you can download. Click a table name to preview its data on the Table View tab of the Navigator. To select more than one table, click the Select Multiple Tables check box before clicking the check box in front of each table name to select them. Once you select the table or tables of data you want to import into the Excel worksheet, click the Load button in the Navigator to import the selected web data table(s) into an existing or new worksheet at the cell cursor. To import the table(s) into a new worksheet, select the Load To option on the Load button’s drop-down menu. To further filter and refine the web data to be imported, click the Transform Data button to open the table(s) in the Power Query Editor (see Transform Data that follows).

   See Chapter 11 for more on importing data from the web.

3. Get Data. Many businesses maintain their corporate data in tables created and managed by stand-alone database programs, such as Microsoft Access or Microsoft SQL Server. If you have access to these types of corporate databases, you can directly import data from their tables into your Excel worksheet using the appropriate option (From File, From Database, From Online Services, or From Other Services) on the Get Data command button’s drop-down menu located on the Data tab of the Ribbon (Alt+APN).

   If you’re importing data from an Access database, select the From Database option followed by From Microsoft Access Database option. If you’re importing data from an SQL Server or other data feed, such Windows Azure Marketplace, select the From Other Sources option followed by the particular type of database program or feed.

   After selecting the source of the external data, you need to select the database file followed by the table or tables in that database file that contain the data you want to import into Excel in the Navigator dialog box that appears. Finally, click the Load button in the Navigator if you want to import the selected external data into the current worksheet starting at the cell cursor’s position. To import the table(s) into a new worksheet, select the Load To option on the Load button’s drop-down menu. To further filter and refine the web data to be imported, click the Transform Data button to open the table(s) in the Power Query Editor (see Transform Data that follows).

   See Chapter 11 for more on importing external data into your worksheet.

2. Transform Data. Sometimes you don’t need to import all the records stored in the tables of an external database or web page into the Excel worksheet. Before downloading the data, you can use the Power Query Editor to sort, filter, and otherwise refine the data records before loading them into your worksheet. To open the Power Query Editor, click the Transform Data button in the Navigator dialog box when setting the external data import. Excel then opens a new query in the Power Query Editor (whose interface greatly resembles that of Excel 2019). After editing the query in the Power Query Editor, you can save the query and download the remaining external data by selecting the Close & Load button to import the data into the current worksheet starting at the cell cursor’s position. Select the Close & Load To option on the Close & Load button’s drop-down menu to import the data to a new worksheet.
1. Relationships. When you import data maintained in database programs such as Microsoft Access, you can import more than one table into the different sheets of your workbook. Often these data tables are related to one another by the occurrence of a duplicate field known as the table’s key field. When such relationships occur between imported tables in an Excel workbook, it is known as a data model, and Excel enables you to create pivot tables and pivot charts using fields from any of the related tables.

   To see all the key fields in the imported data tables and how they’re related to one another, select Data ⇒   Relationships on the Ribbon (Alt+AA). Excel 2019 then displays a Manage Relationships dialog box that shows all the related data tables in the data model in the current workbook. This dialog box also displays the name of the key field in each of the related tables. If there are other relationships between fields of the imported data tables that you want Excel to recognize, you can select the Edit button and manually specify the key fields to be used.

   See Chapter 9 for more on building pivot tables using fields from a data model.