B00d1wh4qm.pdf

www.ebook777.com

Microsoft® Excel® 2013: Working with Data Ranges and Tables

H. Albert Napier, Ph.D. Rice University

Ollie N. Rivers ISBN: 978-1-940079-13-4 ©2015 Napier-Rivers, LLC. Please email us at: [email protected] to provide feedback.



Microsoft® Excel® 2013: Working with Data Ranges and Tables H. Albert Napier, Ph.D. and Ollie N. Rivers

www.ebook777.com

Course Description Microsoft® Excel® 2013: Working with Data Ranges and Tables is a two-lesson intermediate level course that guides you through organizing, entering and validating worksheet data and then effectively sorting, filtering, subtotaling, and outlining the data. You also learn how to define well-organized data as an Excel table; then use Table features to work with the data.

In Lesson 1 you learn how to organize columns and rows of data into a range so that you can effectively sort, filter, subtotal, and outline the data. Then you learn how to protect the data from invalid data entry by setting data validation rules for specific cells. You learn how to copy these validation rules to other cells, how to remove validation rules from cells and how to use the Data Form dialog box to enter and locate data in a range.

Next you learn how to sort a data range on one column or on multiple columns and how to use a custom sort order, the Filter (AutoFilter) feature and the Advanced Filter feature to view data that meets specific criteria. Then you learn how to add and remove subtotals from a data range while viewing the data range in outline form. You also learn how to create a chart using an outline’s subtotals.

Finally, in Lesson 2 you explore the advantages of defining a range of data as an Excel 2013 table.

Prerequisites The Napier & Rivers courses Microsoft® Excel® 2013: Getting Started with Excel and Microsoft® Excel® 2013: Creating and Formatting Charts or the equivalent experience working with Excel 2013 in the Windows operating system environment are prerequisites for this course.

www.ebook777.com

Data Files If you have not yet downloaded the data files, visit http://ebooksforoffice.com/datafiles, locate Microsoft® Excel® 2013: Working with Data Ranges and Tables book title, and then click the ‘Download data files’ link.

Learning Objectives After completing this course you will be able to: identify the guidelines for a well-organized data range; select ways to create and test validation rules; select the Data Form for data entry, recognize ways to sort and filter a data range, identify the main differences between a data range and a table, select ways to add subtotals, a Grand Total and data grouping to a data range, identify multiple ways to define an Excel table using buttons on the HOME and INSERT tabs, the Quick Analysis feature, a keyboard shortcut and by formatting a data range as a table, and recognize ways to enter data and formulas in a table; change table formatting; resize a table; scroll, sort, filter and summarize a table; and convert a table to a data range

www.ebook777.com

Lesson Summary Lesson 1 In this lesson, you learn to: identify the guidelines for a well-organized data range, enter and test validation rules, copy validation rules, enter and locate data using the Data Form, search for data using the Data Form, remove validation rules, sort a data range on a single field, sort a data range on multiple fields apply a custom sort order to a data range, sort a data range by font or fill color use Filter (AutoFilter) to filter a data range, filter by one or more fields using the Filter feature, filter for the Top Items using the Filter feature, sort, copy and paste filtered data, create a custom filter, use Advanced Filter to filter a data range, add subtotals and an outline to a data range, expand and collapse the outline, create a chart from subtotals, insert nested subtotals, and outline data containing subtotals.

Lesson 2 In this lesson, you learn to: define and name an empty table, define a table using existing data, define a table using the Quick Analysis feature, format data as a defined table, enter data and expand the table vertically, enter and copy formulas in a table, use the Data Form with a table, resize a table using the mouse pointer, the Resize Table button and dynamically, select table elements, change table formatting, retain the Header Row text while scrolling a table vertically, sort and filter records in a table, add a Total Row to a table, and

convert a defined table to a data range.

As you work through this course, tip boxes provide additional information:

NOTE tips provide general information about course content.



KEYBOARD TIPs offer quick alternatives to performing tasks using the keyboard instead of the mouse.



WARNING! tips provide tips on ways to avoid specific problems while working in Excel.



TIME-SAVER tips provide alternate methods for performing tasks.

www.ebook777.com



Author Biographies

H. Albert Napier, Ph.D. H. Albert Napier is a Professor of Management in the Jones Graduate School of Business at Rice University, where he has taught graduate level courses related to entrepreneurship, information technology, and e-business. Dr. Napier also makes numerous management development program presentations on various topics of business and information technology topics. Additionally, he was a principal of Napier & Judd, Inc., a company engaged in computer training and consulting for 20 years.

Dr. Napier has trained thousands of CPAs and others to use various software packages in classroom and online environments. Dr. Napier is on the board of directors of three e-business companies. He is the author of more than 20 articles related to management information systems and application of computer-based decision processes in business and is the co-author of more than 60 textbooks. Dr. Napier holds a Ph.D. in Business Administration, an M.B.A. and a B.A. in Mathematics and Economics, all from The University of Texas at Austin.

Ollie N. Rivers

Ollie N. Rivers has more than 20 years’ business experience in financial and administrative management and more than 10 years’ experience as a corporate trainer. She is a co-author of two e-business textbooks, an Internet textbook and a Web design textbook and is a contributing author on more than 15 software package textbooks. Ms. Rivers has also developed and delivered numerous classroom and online continuing education seminars for CPAs. Ms. Rivers holds an M.B.A. and a B.S. in Accounting and Management from Houston Baptist University. COPYRIGHT (C) 2015 Napier-Rivers LLC Microsoft and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Microsoft product screen shot(s) reprinted with permission

from Microsoft Corporation.



www.ebook777.com

Lesson 1: Entering, Validating, Sorting, Filtering and Outlining a Data Range Introduction In Lesson 1, you first learn how to organize columns and rows of data in a worksheet data range so that the data can easily be rearranged (sorted) and specific data identified (filtered). Organizing your data carefully also allows you to add subtotals by data categories and to group or outline the data by categories.

It may be necessary for you to setup a worksheet with titles and column or row labels and then pass the workbook to someone else for data entry, especially when a large amount of data is involved. You can help ensure the quality of the data by adding data entry validation rules to specific cells. In this lesson you learn how to set and test validation rules, copy validation rules to other cells and remove validation rules from cells.

The speed and accuracy of data entry may be improved by using the Data Entry form instead of entering data directly by moving from cell to cell across a row or down a column in the worksheet. In this lesson, you also learn how to open and use the Data Form dialog box to enter and locate data.

You also learn how to rearrange or sort rows in a data range based on a single column or criterion, on multiple columns or criteria and on custom criteria. Next, you learn how to filter or view just those rows that contain data that meets specific criteria using two features: Filter (AutoFilter) and Advanced Filter.

You learn how to insert temporary column subtotals in a data range using the SUBTOTAL function. You also learn to expand and collapse the data range outline that is automatically created when you insert subtotals. Next, you learn how to create a chart using subtotal data, create nested subtotals and remove temporary subtotals. Finally, you learn how to group and ungroup subtotaled data.

Learning Objectives After completing this lesson you will be able to: Objective 1A: identify the guidelines for a well-organized data range; select ways to create and test validation rules; select the Data Form for data entry, Objective 1B: recognize ways to sort and filter a data range, and Objective 1C: select ways to add subtotals, a Grand Total and data grouping to a data range.

www.ebook777.com

Key Terms Add Level button Advanced Filter Auto Outline command Clear button Copy Level button Criteria button Criteria indicator criteria range custom filter custom sort order Data Form Delete button Delete Level button field filter Filter (AutoFilter) filter criteria Find Next button Find Prev button Form button (Data Form) Form button (QAT) Group button Group command header row hide detail symbol Information error alert input message My data has headers checkbox nested subtotals New button

New Record indicator Options button outline level symbols Paste Special command Paste Special dialog box record Restore button show detail symbol sort Sort & Filter button Sort A to Z button Sort Largest to Smallest button Sort Smallest to Largest button Sort Z to A button Stop error alert Subtotal feature SUBTOTAL function Ungroup button validation rules Warning! error alert wildcard symbols

www.ebook777.com

Objective 1A: Identifying the Guidelines for a WellOrganized Data Range; Selecting Ways to Create and Test Validation Rules; Selecting the Data Form for Data Entry Very large groups of data that are manipulated in sophisticated ways are usually stored and manipulated using relational database software, such as Microsoft® Access®. However, an Excel worksheet does provide an effective alternative to a relational database for storing and manipulating small to moderate amounts of data, such as small business sales or employee data.



Identifying the Guidelines for a Well-Organized Data Range

Worksheet data can be rearranged in a different order in a process called sorting and specific data can be selected for analysis in a process called filtering. The data can also be subtotaled by data categories and outlined (grouped), for example by details, then by subtotals, then by grand totals.

The key to successful sorting, filtering, subtotaling and outlining a data range is to carefully organize the data following a few simple guidelines.

The first row of the range should be a single header row containing column label text which describes the contents of each column. Format the label text differently than the data that follows, for example by applying the bold or italic font style and wrap long labels inside each cell. Do not use two separate rows for long labels; wrap the long labels

in the cells in a single row using the Wrap Text button in the Alignment group on the HOME tab. Each row below the header row should contain related data. For example, related sales data for a specific product might be product identification number, product description, sales department, store number and so forth, all entered across the same row in the range. The content of the cells in each column must be consistent. For example, cells in a column labeled State should only contain the names of states, not store numbers or product identification numbers. If more than one range of data is maintained on the same worksheet, then each range should be bounded by a blank row above and below the range and a blank column to the left (except for data beginning in column A) and to the right of the range. Blank row and column boundaries allow Excel to determine where the data range to be sorted or filtered is located and keeps all the range’s data together during the sorting or filtering process. While individual blank cells are acceptable, there should be no completely blank rows or columns within the range to be sorted or filtered. If it is important to retain the original order of the data, it is a good idea to add a sequential number to each row of data so that you can quickly sort the data back to its original order.

Setting up your data range following these guidelines allows Excel to identify the boundaries of the data and provides the information Excel needs to sort, filter, subtotal and outline the data.



You begin this lesson by opening an existing workbook and saving it with a new name. Then you review the field names in the data range’s header row.

If you have not yet downloaded the data files, visit http://ebooksforoffice.com/datafiles, locate Microsoft® Excel® 2013: Working with Data Ranges and Tables book title, and then click the ‘Download data files’ link.

www.ebook777.com



Step 1 Open the Lesson 1 Data File workbook; then save the workbook as Lesson 1 Organizing and Validating Data

Cells in the range A5:H10 are already formatted and cell H5 already contains a formula you copy to other cells.

Step 2 Observe the formatted header row in the range A4:H4 and the formula in cell H5

The worksheet on your screen should look similar to Figure 1-1.



FIGURE 1-1 Range header row and formula





You are now ready to set up validation rules for data entry in the range A5:H10.

Entering and Testing Validation Rules

You can set validation rules for specific cells to protect your worksheets from invalid data entry. Setting validation rules allows you to control the type of data entered in a cell and, at the same time, provide the person entering the data with useful information.

When you set validation rules, you can restrict data entry in a specific cell by specifying that the cell must contain:

values from a list; the user selects from a list of data entry choices, such as State names, whole numbers within limits, such as a Store # from 1-7, decimal numbers within limits, such as a per item sales price in dollars and cents, a date or time within a specific timeframe, or text of a specified length, for example limiting a name to a maximum number of characters.

You can also:

base the data entry restrictions on the contents of another cell, or create a custom formula to control the data entered in a cell.

You set validation rules in the Data Validation dialog box. To launch the dialog box, click the Data Validation button face in the Data Tools group on the Ribbon’s DATA tab.

The Data Validation dialog box has three tabs: Settings, Input Message and Error Alert. Use options on the Settings tab to specify the validation rule for the cell. The Input Message tab is used to add a title and descriptive message for the cell to help the user enter valid data. The Error Alert tab contains options for handling invalid data by:

providing additional information to the user, or displaying a warning message, or stopping the process until valid data is entered.

As discussed earlier, adding a unique number to the Record Number field for each new record allows you to easily place the data back into its original order after it has been sorted by another field, for example by Department or State.

www.ebook777.com

enter incremental record numbers in the range A5:A10 using the fill handle and the CTRL key.

You also can copy the formula in cell H5 to the range H6:H10 using the fill handle.



To fill a range with incremental numbers and fill a range with a formula:

Step 1 Enter 1 in cell A5

Step 2 Activate cell A5, if necessary; then press and hold down the CTRL key

Step 3 Drag the fill handle in the lower right corner of the cell A5 boundary to cell A10; release the mouse button and then release the CTRL Key

Step 4 Observe the incremental numbers 1-6 in the range A5:A10

Step 5 Activate cell H5

Step 6 Observe the formula =ROUND(F5*G5,0); this formula multiplies the Items Sold value by the Sales Price value and then rounds the result to the nearest whole number

Step 7

Drag the fill handle from cell H5 to cell H10 to copy the formula with relative references to the range H6:H10

Now you are ready to set data validation rules for the Description, Department, Store #, State, Items Sold and Sales Price fields in the cells in row 5.

You set the validation rules, input messages and error alerts for data entry in the range A5:H10 following the information in TABLE 1-1. You set the rules in cells in row 5 and then copy the settings to cells in rows 6:10.



TABLE 1-1 Validation Rules

To restrict the Description data entry text length to a maximum of 20 characters and ignore blank fields:

Step 1 Activate cell B5; then click the DATA tab on the Ribbon, if necessary, and locate the Data Tools group

Step 2 Click the Data Validation button face to launch the Data Validation dialog box

Step 3 Click the Settings tab in the dialog box, if necessary

Step 4 Click the Allow arrow; then click Text Length

www.ebook777.com

Click the Data arrow; then click less than or equal to

Step 6 Key 20 in the Maximum text box

The Settings tab in the dialog box on your screen should look similar to Figure 12.



FIGURE 1-2 Settings tab in the Data Validation dialog box





An input message providing data entry instructions is optional, but adding one can be helpful to someone entering data in an unfamiliar workbook.

To add an input message:

Step 1 Click the Input Message tab in the dialog box

Step 2 Key Item Description in the Title text box

Step 3 Key Enter the item description in 20 or fewer characters. in the Input Message text box

The Input Message tab in the dialog box on your screen should look similar to Figure 1-3.



FIGURE 1-3 Input Message tab in the Data Validation dialog box



An error alert indicating invalid data and the action to be taken should appear when the TAB or ENTER key is pressed to enter the keyed data.

The Information and Warning error alert options both display icons and messages but allow the user to enter the invalid data and continue to the next cell.

www.ebook777.com

cell before continuing to the next cell.

To add a Warning error alert for the Description data:

Step 1 Click the Error Alert tab in the dialog box

Step 2 Click the Style arrow; then click Warning

Step 3 Key Item Description in the Title text box; then key The item description exceeds 20 characters. in the Error Message text box

The Error Alert tab on your screen should look similar to Figure 1-4.



FIGURE 1-4 Error Alert tab in the Data Validation dialog box



Step 4 Click OK to apply the settings and close the dialog box

Step 5 Observe the input message attached to the active cell B5

The input message on your screen should look similar to Figure 1-5.



FIGURE 1-5 Input message for cell B5



To test the validation rule in cell B5:

Step 1 Verify that cell B5 is the active cell; then key at least 21 lowercase ‘x’ characters in the cell (Do not press the ENTER key)

Step 2 Press the TAB key to move to cell C5

The Item Description Warning error alert dialog box appears. The dialog box on your screen should look similar to Figure 1-6.

www.ebook777.com



FIGURE 1-6 Item Description Warning error alert dialog box



You can click the Yes dialog box button to accept the invalid data and move to the next cell or the No dialog box button to reenter a valid item description in 20 or fewer characters.

Step 3 Click the No dialog box button to correct your data entry

Step 4 Key baseball bats in cell B5; then press the TAB key to move to cell C5; no Warning error alert appears, the item description is entered in cell B5 and cell C5 is now the active cell

You continue by setting and testing the remaining validation rules.



Each of the items sold in the Jack’s Sports Warehouse stores is assigned to a sales department: Clothing, Fitness, Outdoors and Sports.

To facilitate data entry, you set a validation rule that allows the user to either key the department name or select the department name from a list.

If the name is keyed, it must match one of the department names in the list for the entry to be valid.

To set and test the validation rule for the Department field:

Step 1 Activate cell C5, if necessary

Step 2 Click the DATA tab on the Ribbon, if necessary; then locate the Data Tools group

Step 3 Click the Data Validation button face in the Data Tools group to launch the Data Validation dialog box

Step 4 Click the Settings tab in the dialog box, if necessary; then click the Allow arrow and click List

Step 5 Key Clothing, Fitness, Outdoors, Sports in the Source text box; be sure to key the comma that separates each item in the list

The Settings tab in the dialog box on your screen should look similar to Figure 17.

www.ebook777.com



FIGURE 1-7 Settings tab in the Data Validation dialog box





You skip the input message for this rule and add a Stop error alert.

To add a Stop error alert for the Department data:

Step 1 Click the Error Alert tab in the dialog box

Step 2 Click the Style arrow; then click Stop, if necessary

Step 3 Key Department in the Title text box; then key The Department name must be one of the following names either keyed in the cell or selected from the list: Clothing, Fitness, Outdoors or Sports. in the Error message text box



The Error Alert tab in the dialog box on your screen should look similar to Figure 1-8.



FIGURE 1-8 Error Alert tab in the Data Validation dialog box



Step 4 Click OK to apply the settings and close the dialog box

Step 5 Observe the list arrow attached to cell C5

Step 6 Click the list arrow to view the list; then click Sports in the list

Step 7 Observe the cell C5 now contains the Sports department name

Your worksheet should look similar to Figure 1-9.

www.ebook777.com



FIGURE 1-9 Worksheet with validated data in cells B5 and C5



To test the validation rule in cell C5 by keying an invalid department name in cell C5:

Step 1 Verify that cell C5 is still the active cell

Step 2 Key Camping in cell C5; then press the TAB key

Step 3 Observe the Department Stop error alert dialog box

The Department Stop error alert dialog box launches. The dialog box on your screen should look similar to Figure 1-10.



FIGURE 1-10 Department Stop error alert dialog box



You can click either the Cancel button or the Retry button to close the dialog box and correct the data entry in cell C5.

When you click the Retry button, the dialog box closes, cell C5 is opened for editing and its contents selected, ready for you to key the correct department name.



Step 4 Click the dialog box’s Retry button; cell C5 is still the active cell and its contents are selected

Step 5 Key Sports in cell C5; then press the TAB key to move to cell D5

Step 6 Observe that, since the keyed department name matches a name from the list, the name in cell C5 is accepted as valid data; cell D5 is now the active cell

You continue by setting the validation rule for cells that contain the Store # and the name of the State where the store is located.



Jack’s Sports Warehouse has seven stores located in three States: Idaho, North Dakota and Wyoming.

www.ebook777.com

First you set the Store # data validation rule to a whole number between 1 and 7. Then you set the State validation rule to select from a list of the three States.

Although input messages can sometimes be helpful to the user entering the data, some users may find the same input message repeated over and over to be distracting. You can omit input messages for the remaining validation rules.



To set the Store # validation rule:

Step 1 Activate cell D5, if necessary; then launch the Data Validation dialog box

Step 2 Click the Settings tab, if necessary; then click the Allow arrow and click Whole number

Step 3 Key 1 in the Minimum text box; then key 7 in the Maximum text box

The Settings tab in the dialog box on your screen should look similar to Figure 111.



FIGURE 1-11 Settings tab in the Data Validation dialog box

To add a Stop error alert for the Store # data and then test the validation rule:

Step 1 Click the Error Alert tab; then click the Style arrow and click Stop, if necessary

Step 2 Key Store # in the Title text box; then key The Store # must be a whole number between 1 and 7. in the Error message text box

Step 3 Click OK to apply the settings and close the dialog box

Step 4 Key 12 in cell D5; then press the TAB key; the Stop error alert dialog box launches

Step 5 Observe the Store # Stop error alert dialog box indicating that the number 12 is invalid for the Store #

The dialog box on your screen should look similar to Figure 1-12.

www.ebook777.com



FIGURE 1-12 Store # Stop error alert dialog box

When you click the Cancel button, the dialog box closes and the data entry is cancelled in the active cell.

Step 6 Click the Cancel button; the previously entered data is cancelled and cell D5 remains the active cell

Step 7 Key 1 in cell D5; then press the TAB key to move to cell E5

Your worksheet should look similar to Figure 1-13.



FIGURE 1-13 Worksheet with valid data in the range B5:D5

To set and test the validation rule for the State data:

Step 1 Activate cell E5, if necessary

Step 2 Launch the Data Validation dialog box; then click the Settings tab, if necessary

Step 3 Click the Allow arrow; then click List

Step 4 Key Idaho, North Dakota, Wyoming in the Source text box (remember to key the comma to separate each item in the list)

The Settings tab in the dialog box on your screen should look similar to Figure 114.



FIGURE 1-14 Settings tab in the Data Validation dialog box



www.ebook777.com



Step 1 Click the Error Alert tab

Step 2 Click the Style arrow; then click Stop, if necessary

Step 3 Key State in the Title text box; then key The State name must be one of the following names either keyed in the cell or selected from the list: Idaho, North Dakota or Wyoming. in the Error Message text box

The Error Alert tab in the dialog box on your screen should look similar to Figure 1-15.



FIGURE 1-15 Error Alert tab in the Data Validation dialog box

Step 4 Click OK to apply the settings and close the dialog box

Step 5 Click the list arrow in cell E5; then click Idaho in the list



Step 6 Observe that cell E5 now contains the State name, Idaho

To test the validation rule in cell E5 by keying a State name not in the list:

Step 1 Key Oregon in cell E5 and press the TAB key

The Stop error alert dialog box appears. The dialog box on your screen should look similar to Figure 1-16.



FIGURE 1-16 State Stop error alert dialog box

Step 2 Click Cancel to remove the invalid data

Step 3 Click the cell E5 list arrow and click Idaho, if necessary; then press the TAB key to move to cell F5

Your worksheet should look similar to Figure 1-17.

www.ebook777.com



FIGURE 1-17 Worksheet with validated data in the range B5:E5

The Items Sold data should be a whole number between 1 and 999,999.

To set and test the validation rule for the Items Sold data:

Step 1 Activate cell F5, if necessary; then launch the Data Validation dialog box

Step 2 Add a Settings validation rule to allow whole numbers with the minimum of 1 and the maximum of 999999

Step 3 Add a Stop error alert with the title Items Sold and the message The number of Items Sold must be a whole number from 1-999,999.

Step 4 Key 0 in cell F5 and press the TAB key; the Stop dialog box indicates that the number 0 (zero) is invalid data for items sold

Step 5 Click Retry

Step 6 Key 1130 in cell F5 and press the TAB key to move to cell G5

Your worksheet should look similar to Figure 1-18.



FIGURE 1-18 Worksheet with validated data in the range B5:F5

The sales price data should be a decimal number.

To set and test the validation rule for the sales price data:

Step 1 Activate cell G5, if necessary; then launch the Data Validation dialog box

Step 2 Add a Settings validation rule to allow decimal numbers that are less than or equal to 9999.99

Step 3 Add a Stop error alert with the title Sales Price and the message The Sales Price should be less than or equal to 9,999.99.

Step 4 Key 10,000.00 in cell G5 and press the TAB key; the Stop dialog box indicates that 10,000.00 is an invalid decimal number for the sales price

www.ebook777.com

Step 5 Click Retry

Step 6 Key 21.95 in cell G5 and then press the TAB key

Step 7 Observe that the formula in cell H5 calculates the Total Sales for the item in row 5 rounded to the nearest whole number

Your worksheet should look similar to Figure 1-19.



FIGURE 1-19 Worksheet with validated data in the range B5:G5

Now that the validation rules are set and tested for the first row of data, you are ready to copy the rules to the remaining rows in the range.

Copying Validation Rules

Instead of manually setting each rule in cells in the remaining rows, you can quickly copy all the rules in the cells in row 5 and paste them into other cells using an option in the Paste Special dialog box.

You can launch the Paste Special dialog box by clicking the Paste Special

command on the Paste button menu in the Clipboard group on the Ribbon’s HOME tab. You can also launch the Paste Special dialog box by clicking the Paste Special command on a shortcut menu.

To copy the validation rules in the range B5:G5 and paste them into the range B6:G12 using a shortcut menu (you add data to the range later):

Step 1 Select the range B5:G5; then right-click the selected range and click Copy on the shortcut menu

Step 2 Select the range B6:G12: then right-click the selected range and click Paste Special on the shortcut menu to launch the Paste Special dialog box

Step 3 Click the Validation option button in the dialog box

The Paste Special dialog box on your screen should look similar to Figure 1-20.



FIGURE 1-20 Paste Special dialog box

www.ebook777.com

Step 4 Click OK; then activate cell A1

Step 5 Press the ESC key to clear the Clipboard

The validation rules are copied and pasted into the range B6:G12.

To quickly verify that the cells in the range B5:G12 contain validation rules, you can use the Data Validation option in the GoTo Special dialog box to select cells that contain validation rules.

To launch the GoTo Special dialog box, click the Find & Select button in the Editing group on the Ribbon’s HOME tab to view a menu of selection options; then click the GoTo Special command.



To verify that the cells in the range B5:G12 contain validation rules: Step 1 Click the HOME tab on the Ribbon, if necessary; then locate the Editing group

Step 2 Click the Find & Select button in the Editing group to view the button’s menu

Step 3 Click the GoTo Special command on the menu to launch the GoTo Special dialog box

Step 4 Click the Data Validation option button in the GoTo Special dialog box; then click the All option button below the Data Validation button, if necessary



The GoTo Special dialog box on your screen should look similar to Figure 1-21.



FIGURE 1-21 GoTo Special dialog box



Step 5 Click OK

Step 6 Observe that all the cells in the range B5:G12 are selected indicating that these cells contain validation rules

Step 7 Activate cell B6

Step 8 Continue by entering the data for records 2-4 in the range B6:G8 using the following TABLE 1-2

www.ebook777.com



TABLE 1-2 Data for records 2-4 to be entered in the range A6:G8

After entering the data, your worksheet should look similar to Figure 1-22.



FIGURE 1-22 Worksheet with validated data and copied formulas

Step 9 Save the workbook and leave it open for the next section



Instead of entering data in a range by moving from cell to cell across a row or down a column directly in the worksheet, it may be more efficient to use a form to enter the data.

Entering and Locating Data Using the Data Form

The Excel Data Form is a dialog box you can use to enter a complete record in the range. The Data Form dialog box provides all the fields in the range plus a box in which to enter text, numbers or other content in each field of the record. You can use the Data Form to enter a new record, change data in an existing record and delete a record.

The only way to access the Data Form dialog box in Excel 2013 is to add the Form button to the Ribbon or to the Quick Access Toolbar.



To add the Form button to the Quick Access Toolbar, if necessary:

Step 1 Click the Customize Quick Access Toolbar button on the Quick Access Toolbar to view a menu of customization options

Step 2 Click the More Commands command in the menu to launch the Excel Options dialog box with the Quick Access Toolbar options active

Step 3 Click the Choose commands from arrow and then click Commands Not in the Ribbon

Step 4 Click the Form button in the Commands Not in the Ribbon list (scroll the list if necessary) and then click the Add button to add the Form button to the Customize Quick Access Toolbar list

Your Excel Options dialog box should look similar to Figure 1-23,

www.ebook777.com



FIGURE 1-23 Excel Options dialog box



Step 5 Click OK; the Form button is added as the last button on the Quick Access Toolbar

Before you launch the Data Form dialog box, you activate a cell within the data range. This allows Excel to identify the boundaries of the range and the range’s header row.

To launch the Data Form dialog box:

Step 1

Activate any cell in the data range’s header row

Step 2 Click the Form button on the Quick Access Toolbar to launch the Sales by Item Data Form dialog box

The Sales by Item Data Form dialog box on your screen should look similar to Figure 1-24.



FIGURE 1-24 Sales by Item Data Form dialog box displaying data for Record Number 1



You can move from record to record by dragging the scroll bar or clicking the scroll bar arrows.

TABLE 1-3 lists the remaining data for records 5-6 to be entered in the range B9:G10. You use the Sales by Item Data Form to enter the data.

www.ebook777.com



TABLE 1-3 Data for records 5-6 to be entered in the range B9:G10







To enter data for records 5 and 6:

Step 1 Scroll the Data Form dialog box to view record 5; the Record Number appears selected in the Record Number text box and the remaining text boxes (fields) are empty

Step 2 Press the TAB key to move the insertion point into the Description text box; then key catcher’s mitts

Step 3 Press the TAB key to move the insertion point into the Department text box; then key Sports

Step 4 Press the TAB key to move the insertion point into the Store # text box; then key 1



Step 5 Press the TAB key to move the insertion point into the State text box; then key Idaho

Step 6 Press the TAB key to move the insertion point into the Items Sold text box; then key 1,346

Step 7 Press the TAB key to move the insertion point into the Sales Price text box; then key 49.95

Step 8 Press the ENTER key to enter the data for record 5 and view record 6

Step 9 Press the UP ARROW key to view record 5

The record 5 Sales by Item Data Form dialog box on your screen should look similar to Figure 1-25.



FIGURE 1-25 Sales by Item Data Form dialog box displaying data for Record

www.ebook777.com





You can use the buttons to the right of the data text boxes to view and manage the records in the data range.

The New button displays a blank Data Form. The Delete button deletes the current record. The Find Prev button displays the previous record. The Find Next button displays the next record. The Criteria button displays the Criteria Data Form you can use to search the data range.

Step 10 Press the DOWN ARROW key to view record 6; then key the remaining data for record 6 using TABLE 1-3 as your guide

Step 11 Press the ENTER key when finished; a new blank record appears in the Data Form

The Sales by Item Data Form dialog box for a new blank record on your screen should look similar to Figure 1-26.



FIGURE 1-26 Sales by Item Data Form dialog box for a new blank record



You now use the blank Data Form to enter two additional records using the data in TABLE 1-4. Note that you do not key a Record Number; you use the fill handle to fill the Record Numbers.



TABLE 1-4 Data for two new records to be entered in the range B11:G12

To enter the data for two new records:

Step 1 Press the TAB key to move the insertion point to the Description text box; then key the batting gloves data for the first new record using TABLE 1-4 as your guide

Step 2 Press the ENTER key to add the data to the range B11:G11 and view a new blank Data Form

www.ebook777.com

Step 3 Press the TAB key to move the insertion point to the Department text box; then key the golf clubs data for the for the second new record following TABLE 1-4

Step 4 Press the ENTER key to enter the data in the range B12:G12

Step 5 Click the Data Form Close button

Step 6 Fill the range A11:A12 with sequential numbers using cell A10, the fill handle and the CTRL key; the formula is automatically copied to the range H11:H12

Step 7 Activate cell A1

Your data range should now look similar to Figure 1-27.



FIGURE 1-27 Data range with completed data entry

Searching for Data Using the Data Form



You can also use the Data Form dialog box to search for specific records based on field contents and then either modify or delete the records. To set the search criteria, click the Criteria button in the Data Form. Then key the criteria—which can be characters or an expression—in the appropriate text box.

For example, you can search for records that contain Fitness in the Department field or records in which the Items Sold field is >1,000. You can also use the question mark (?), asterisk (*) and tilde (~) symbols as wildcard symbols in a search.

The question mark (?) wildcard symbol is used to replace a single character, as in ?est, which finds records that contain either East or West in a specific field. The asterisk (*) wildcard symbol is used to replace any number of characters, as in B*, which finds records that contain words that begin with B in a specific field. The tilde (~) wildcard symbol is used to find records that contain the ?, * or ~ symbols in a specific field.

Suppose you want to change the sales price for backpacks. You can search for all records that have backpacks in the Description field and then view and modify each record. You move from record to record using the Find Prev and Find Next buttons.



To search for all the Fitness department records in the range B5:H12 and then change the sales price to 67.75:

Step 1 Activate cell C4 in the header row

Step 2 Click the Form button on the Quick Access Toolbar to launch the Sales by Item Data Form dialog box

www.ebook777.com

Click the Criteria button to view the Sales by Item Criteria Data Form dialog box; then key backpacks in the Description text box

The Sales by Item Criteria Data Form dialog box on your screen should now look similar to Figure 1-28.



FIGURE 1-28 Sales by Item Criteria Data Form dialog box



Three new buttons appear in the Criteria Data Form dialog box.

The Clear button clears the search criteria from the text boxes. The Restore button restores the search criteria from the text boxes. The Form button returns to the standard Data Form dialog box where it becomes the Criteria button.

Step 4 Click the Find Next button in the Sales by Item Criteria Data Form to view the first record that meets the search criteria



Step 5 Observe that record 4 for backpacks appears in the Data Form

Step 6 Edit the Sales Price field to be 67.75

Step 7 Click the Find Next button

Step 8 Observe that if your computer speakers are turned on you hear a beeping sound and record 4 remains in the Data Form; this indicates that there are no records beyond record 4 that meet the search criteria

Step 9 Click the Find Prev button

Step 10 Observe that if your computer speakers are turned on you hear a beeping sound and record 4 remains in the Sales by Item Criteria Data Form; there are no records prior to record 4 that meet the search criteria

Step 11 Click the Close button to close the Data Form dialog box

Step 12 Observe the cell G4 now contains 67.75 instead of 69.75

Now suppose you want to view all the records for items whose description begins with the lowercase character b.

To find all the records whose description begins with the lowercase character b:

Step 1

www.ebook777.com

Data Form dialog box

Step 2 Click the Criteria button to view the Sales by Item Criteria Data Form; then key b* in the Description text box

Step 3 Click the Find Next and Find Prev buttons to view the four records whose description begins with the character b

Step 4 Close the Data Form



To find records whose items sold value is less than 1,000:

Step 1 Verify that cell C4 in the header row is still selected; then launch the Sales by Item Data Form dialog box

Step 2 Click the Criteria button to view the Sales by Item Criteria Data Form

Step 3 Key <1,000 in the Items Sold text box; then click the Find Next and Find Prev buttons to view the first record (footballs) whose item sold value is less than 1,000

Step 4 Click the Find Next button to view the next record whose item sold value is less than 1,000

Step 5 Click the Close button



When you no longer need validation rules you can remove them.

Removing Validation Rules

You may choose to remove validation rules from all the cells or from a specific cell. To do this you must first select the cell or cells that contain the validation rules and then clear the data validation rules in the Data Validation dialog box.

If you select only one cell, clicking the Data Validation button face in the Data Tools group on the DATA tab launches the Data Validation dialog box immediately. Click the Clear All button in the Settings tab and then click OK to remove the validation rules for the selected cell.

If you have selected multiple cells, a Warning dialog box appears when you click the Data Validation button face. Click OK in the warning dialog box to launch the Data Validation dialog box.

To select all the cells containing data validation rules and then remove them:

Step 1 Click the HOME tab on the Ribbon, if necessary; then locate the Editing group

Step 2 Click the Find & Select button in the Editing group; then click the GoTo Special command to launch the GoTo Special dialog box

Step 3 Click the Data Validation option button; then click the All option button, if necessary, and click OK to select all the cells in the worksheet that contain data

www.ebook777.com



Step 4 Click the DATA tab on the Ribbon; then click the Data Validation button face in the Data Tools group

Because you have preselected all the cells in the worksheet that contains validation rules, the Warning dialog box appears.

Step 5 Click OK to close the Warning dialog box and launch the Data Validation dialog box

Step 6 Click the Settings tab, if necessary; then click the Clear All button in the lowerleft corner of the Settings tab

Step 7 Click OK to clear the validation rules from all the selected cells

Step 8 Activate cell A1

Step 9 Save the workbook and close the workbook

Objective 1B: Recognizing Ways to Sort and Filter a Data Range As noted earlier in this lesson, a well-organized data range makes it easy to sort and filter the data. For example, you can sort data in a data range on one or more criteria; plus you can create custom sort criteria.

You can also filter—or view specific data—using the Filter (AutoFilter) feature plus create a custom filter and use advanced filtering techniques, where necessary.

Sorting a Data Range on a Single Field

You can quickly rearrange or sort the records in a data range based on the data in a single field by first activating the field and then clicking the Sort & Filter button in the Editing group on the HOME tab.

You can also click the Sort A to Z, Sort Smallest to Largest, Sort Z to A, Sort Largest to Smallest and the Sort buttons in the Sort & Filter group on the DATA tab to sort the records in a data range.

TABLES 1-5 and 1-6 illustrate the default ascending and descending sort orders for different types of data.



TABLE 1-5 Default ascending sort order



www.ebook777.com





You begin this lesson by opening an existing workbook and saving it with a new name. Then you sort a data range in ascending and descending order by a single field.

To open and save a workbook:

Step 1 Open the Lesson 1 Data File 2 workbook; then save the workbook as Lesson 1 Sorting and Filtering Data

Step 2 Scroll the Qtrly Sales by Item worksheet to view the header row fields and records in the range A4:J130

Step 3 Observe that the data in the range is organized in ascending order by Record Number following the rules itemized previously; note that the data is also arranged in Store # order as that was the way the data was originally entered

The first step in sorting a range by a single field is to activate any cell in that field within the range boundaries which includes the header row.

Because the data range is bounded by blank columns and rows and has a uniquely formatted header row, Excel is able to identify the range’s boundaries and, thus, keep all the fields for each record together as it sorts the range.

To sort the rows in the range A4:I130 in ascending order by Department:

Step 1 Activate cell C7



Step 2 Click the DATA tab on the Ribbon; then locate the Sort & Filter group and use the mouse pointer, if necessary, to identify the buttons in the group

Because the active cell is in the Department field which contains text, the Sort & Filter group contains the Sort A to Z (Lowest to Highest or Ascending Order) and Sort Z to A (Highest to Lowest or Descending Order) text sorting buttons.

Step 3 Click the Sort A to Z button in the Sort & Filter group to sort the records by the Department field in ascending alphabetical order

Step 4 Observe that Excel quickly selects the 126 records below the header row in the range A5:I130 and rearranges the records in lowest to highest or ascending order by the Department field, beginning with the Clothing department

The top of your worksheet should look similar to Figures 1-29.



FIGURE 1-29 Records in the range sorted in ascending order by Department field



You can click the Undo button face on the Quick Access Toolbar to undo the just completed sort action.

www.ebook777.com

Click the Undo (Sort) button face on the Quick Access Toolbar

Step 6 Observe that the data range is selected and its records are returned to the original Record Number sort order

To sort the data in descending order by Items Sold:

Step 1 Activate cell F6

Because the active cell is in the Items Sold field which contains numbers, the Sort A to Z button becomes the Sort Smallest to Largest button and the Sort Z to A button becomes the Sort Largest to Smallest button.

Step 2 Click the DATA tab, if necessary; then click the Sort Largest to Smallest button in the Sort & Filter group; use the mouse pointer, if necessary, to identify the button

Step 3 Observe that records are selected and sorted in largest to smallest number or descending order, by Items Sold, beginning with the top selling item, football jerseys

The top of your worksheet should look similar to Figure 1-30.



FIGURE 1-30 Data range sorted in descending order by Items Sold



As you learned earlier in this lesson, you can quickly resort records in their original order if each record is numbered consecutively.

To sort the records in ascending order by Record Number:

Step 1 Activate any cell in the Record Number field (column A) within the range A4:A130

Step 2 Click the Sort Smallest to Largest button in the Sort & Filter group

Step 3 Observe that the records are sorted in ascending order by Record Number returning the data to its original order

Step 4 Activate cell A1 and leave the workbook open for the next section

www.ebook777.com



Now you are ready to sort the records using multiple field criteria.

Sorting a Data Range on Multiple Fields

Suppose you want to view the sales data in the active worksheet by Store # within each State. To do this, you must sort the records using multiple field criteria—the State field and the Store # field.

You can sort a data range on multiple fields using options in the Sort dialog box. To launch the Sort dialog box, click the Sort button in the Sort & Filter group on the DATA tab.

To launch the Sort dialog box:

Step 1 Activate any cell in the range A4:I130; remember that activating a cell inside the data range allows Excel to determine the boundaries of the range

Step 2 Click the Sort button in the Sort & Filter group on the DATA tab to select the data range and launch the Sort dialog box

Your worksheet should look similar to Figure 1-31.



FIGURE 1-31 Selected data range and Sort dialog box



The Sort dialog box allows you to set the sort criteria: the field (column) to be sorted, type of sort and the sort order.

You can click the: Add Level button to add another level of sort criteria, Delete Level button to remove a level of sort criteria, Copy Level button to copy the previous sort criteria to the next level, Options button to set top to bottom (records) or left to right (fields) sorting and handle case sensitivity or the My data has headers checkbox to toggle on or off the presence of a header row at the top of the data range.

To sort the records first by State in ascending order and then by Store # in ascending order within State:

Step 1 Click the Column Sort by arrow to display the list of field names; then click State in the list to set the primary sort criterion

www.ebook777.com

the Sort On criterion. The default Order for a text sort is Sort A to Z (or ascending) so no change is required for the Order criterion.

Step 2 Click the Add Level button to add another level of sort criteria

Step 3 Click the Column Then by arrow to display the list of field names; then click Store # in the list to set the secondary sort criterion

The default Sort On option is Values so no change is required for the Sort On criterion. The default Order for a numeric sort is Smallest to Largest so no change is required for the Order criterion.

Your Sort dialog box should now look similar to Figure 1-32.



FIGURE 1-32 Sort dialog box with sort criteria

Step 4 Click OK

Step 5 Scroll the worksheet and observe that the data is sorted first in ascending order by State (Idaho, North Dakota and Wyoming) and then by Store # within State

(Idaho stores 1, 3 and 5; North Dakota stores 4 and 6; Wyoming stores 2 and 7)



Now, suppose you want to modify the sort order to display the records in descending order by number of Items Sold for store by State. You can do this by adding another level of sort criteria following the Store # sort criteria.

To modify the most recent sort by adding a third level of sort criteria:

Step 1 Activate a cell inside the data range, if necessary

Step 2 Click the Sort button in the Sort & Filter group to launch the Sort dialog box; observe that the first sort level—the Sort by level—is selected

Step 3 Click the Then by wording that precedes the second sort level to select the level; then click the Add Level button to insert a second Then by sort level

Step 4 Click the second Column Then by arrow to display the list of field names and click Items Sold

Step 5 Click the second Order Then by arrow to display the list of sort Order options and click Largest to Smallest

www.ebook777.com





FIGURE 1-33 Sort dialog box with three levels of sort criteria

Step 6 Click OK

Step 7 Scroll the worksheet and observe that the records are now sorted in the following order: by State in ascending order; by Store # within State in ascending order; and by Items Sold within Store # in descending order

Step 8 Activate any cell in column A within the range A4:A130 and then sort the records in ascending order by Record Number

Step 9 Leave the workbook open for the next section

You can also create a custom sort order to sort a data range.

Applying a Custom Sort Order to a Data Range

Suppose you now want to sort the records by the Popularity Ranking field: items with a High popularity ranking first, items with a Medium popularity ranking second

and items with a Low popularity ranking third.

Sorting the records by the Popularity Ranking field in ascending order returns a High, Low, Medium result; sorting them in descending order returns a Medium, Low, High result. To get the exact sort result you want—High, Medium and Low— you must apply a custom sort order to the Popularity Ranking field.

A custom sort order is a list of sort criteria you create in the Advanced section of the Excel Options dialog box. You launch the Excel Options dialog box by clicking the Options command on the FILE tab in Backstage view.

After you create a custom sort order, you can then apply it by selecting the custom list in the Sort dialog box.

To create a custom sort order:

Step 1 Click the FILE tab on the Ribbon; then click Options to launch the Excel Options dialog box

Step 2 Click Advanced in the dialog box left pane to view Advanced options in the right pane

Step 3 Click the Edit Custom Lists button in the General group of Advanced options (scroll near the bottom of the pane to view the button) to launch the Custom Lists dialog box

Step 4 Key High, Medium, Low in the List entries text box; remember to key the comma between each item in the list—the space following the comma is optional

Step 5 Click the dialog box Add button to add the new custom list to the Custom Lists box

www.ebook777.com





FIGURE 1-34 Custom Lists tab in the Options dialog box



Step 6 Click OK to close the Custom Lists dialog box; then click OK to close the Excel Options dialog box



To apply the custom sort order using values in the Popularity Ranking column:

Step 1 Activate any cell within the data range; then launch the Sort dialog box

Because you resorted the records by Record Number, the three-level sort criteria created in the previous section is cleared from the Sort dialog box.



Step 2 Click the Column Sort by arrow; then click Popularity Ranking

Step 3 Click the Order Sort by arrow; then click Custom List to launch the Custom List dialog box

Step 4 Click High, Medium, Low in the Custom Lists box; then click OK

Your Sort dialog box on your screen should look similar to Figure 1-35.



FIGURE 1-35 Sort dialog box

Step 5 Click OK

Step 6 Scroll the worksheet to verify that the records are now arranged first by items with a High popularity ranking, then by items with a Medium popularity ranking and finally by items with a Low popularity ranking

Step 7

www.ebook777.com





You can also sort a data range by font or fill color.

Sorting a Data Range by Font or Fill Color

The cells in the last field in the data range—the Reorder or Discontinue field— contain a formula that evaluates the data in the Items Sold column. If the value in the Items Sold column is less than or equal to 500, the word ‘Discontinue’ is inserted; if the value in the Items Sold column is greater than 500, the word ‘Reorder’ is inserted.

Additionally, the Reorder or Discontinue column is formatted using a Conditional Formatting rule that formats the word ‘Discontinue’ in a Red font and fills the cell with the Red fill color.

Suppose you now want to sort the records so that all the items to be discontinued appear together in the data range. Because you have only two text choices, one way to perform this sort is to simply sort by the Reorder or Discontinue field in ascending or descending order.

Because font and cell color conditional formatting is applied to the cells, an alternative method is to sort the Reorder or Discontinue field by the applied conditional formatting.

To sort by font or cell color, change the Sort On criteria in the Sort dialog box to the Cell Color or Font color option.

To sort the records so that the items to be discontinued appear at the bottom of the range:

Step 1

Activate any cell in the data range, if necessary; then launch the Sort dialog box

Step 2 Click the Column Sort by arrow; then click Reorder or Discontinue

Step 3 Click the Sort On Sort by arrow; then click Font Color

The Order criterion now has two options: a font color and destination location option.

Step 4 Click the first Order Sort by arrow; then click the Red color square

Step 5 Click the second Order Sort by arrow; then click On Bottom

Your Sort dialog box should look similar to Figure 1-36.



FIGURE 1-36 Sort dialog box

Step 6 Click OK

www.ebook777.com

Scroll to view the six records for items to be discontinued at the bottom of the data range

The bottom of your data range should look similar to Figure 1-37.



FIGURE 1-37 Records for items to be discontinued

Step 8 Sort the records in ascending order by Record Number





When you want to view only those records that have specific data in certain cells, you should filter the data range.

Using Filter (AutoFilter) to Filter a Data Range

Suppose you want to view only a subset of the data in the range—for example, all the sales data for stores in Idaho or only the golf clubs sales data for stores in all States.



To view a subset of the data in the range, you filter the data by specifying filter criteria, such as the State data must equal Idaho or the Description data must equal golf clubs. When you specify filter criteria, only those records whose data meets the filter criteria are visible; all other records are hidden.

You can use the Filter (AutoFilter) feature to quickly specify the filter criteria. When you turn on the Filter feature, small Filter arrows appear in each cell in the header row. Clicking a Filter arrow displays a menu of the unique data entries in the field plus additional searching, sorting and filtering options, as shown in Figure 1-38.



FIGURE 1-38 Sample Filter arrow menu



www.ebook777.com



Filtering by One or More Fields using the Filter Feature

To turn on or off the Filter feature, click the Filter button in the Sort & Filter group on the DATA tab.

To turn on the Filter feature for the data range A4:I130 in the active worksheet:

Step 1 Activate any cell within the data range

Step 2 Click the DATA tab on the Ribbon, if necessary; then locate the Sort & Filter group

Step 3 Click the Filter button in the Sort & Filter group to turn on the Filter feature and add Filter arrows to the cells in the header tow

The top of your data range should look similar to Figure 1-39.



FIGURE 1-39 Filter arrows added to the header row cells



To filter the data range to display all the data for North Dakota:

Step 1 Click the State Filter arrow to view the Filter menu

Step 2 Click the (Select All) checkbox to remove the check mark; then click the North Dakota checkbox to insert a check mark

Step 3 Click OK to filter the data range to show only those records for North Dakota

Step 4 Scroll the worksheet and observe that only those records in which the State data equals North Dakota are visible; the records in rows 5:58, 73:90 and 113:130 are hidden

Step 5 Observe that the row numbers on the visible row heading buttons are now blue indicating that the data range is filtered and some rows are hidden

Step 6 Observe that the Filter arrow attached to the State cell in the header row is now a Filter icon indicating that filter criteria is set for the State column

Step 7 Point to the Filter icon attached to the State cell in the header row to see a ScreenTip with information about the current filter

The top of your filtered data range should look similar to Figure 1-40.

www.ebook777.com



FIGURE 1-40 Data range filtered to show data for North Dakota stores



To unfilter the data range:

Step 1 Click the State Filter icon to view the Filter menu

Step 2 Click the (Select All) checkbox to insert a check mark; then click OK

Step 3 Scroll the worksheet and observe that all of the records in the data range are now visible

Step 4 Observe that the row numbers are again their standard color and the State Filter icon is replaced by the Filter arrow indicating the range is not filtered

Suppose you now want to view all the Outdoors department sales data for Idaho. You can also use the Filter feature to specify multiple filter criteria.

To filter the data range to display all the Outdoors department sales data for Idaho:

Step 1

Click the State Filter arrow to view the Filter menu

Step 2 Click the (Select All) checkbox to remove the check mark, if necessary; click the Idaho checkbox to insert a check mark and click OK

Step 3 Click the Department Filter arrow to view the Filter menu

Step 4 Click the (Select All) checkbox to remove the check mark, if necessary; then click the Outdoors checkbox to insert a check mark and click OK

Step 5 Observe that only those records in which the State data equals Idaho and the Department data equals Outdoors are visible; all other records are hidden

Your filtered data range should look similar to Figure 1-41.



FIGURE 1-41 Data range filtered to show data for the Outdoors department in Idaho stores

You have two ways to unfilter a range when you use multiple filter criteria. You can click the (Select All) option on the Filter menu for each filtered field or you can click Clear button in the Sort & Filter group on the DATA tab.

www.ebook777.com

To unfilter the data range:

Step 1 Click the Clear button in the Sort & Filter group on the DATA tab

Step 2 Observe that the data range is now unfiltered

Filtering for the Top Items Using the Filter Feature

Suppose you now want to see the top five items for all stores based on Total Sales. You can use the (Top 10…) AutoFilter option to filter a range by the largest number or smallest number.

To filter the data range to display the top five items based on Total Sales:

Step 1 Click the Total Sales Filter arrow to view the Filter menu

Step 2 Point to Number Filters to view a submenu; then click Top 10 in the submenu to launch the Top 10 AutoFilter dialog box

Step 3 Key 5 in the second Show text box

Your Top 10 AutoFilter dialog box should look similar to Figure 1-42.



FIGURE 1-42 Top 10 AutoFilter dialog box

Step 4 Click OK

Step 5 Observe that records for the top five items based on Total Sales are now displayed; all other records are hidden

Your filtered data range should look similar to Figure 1-43.



FIGURE 1-43 Top five items based on Total Sales

Sorting, Copying and Pasting Filtered Data

You can edit or format cells in filtered data, print the filtered data, create a chart from the filtered data and sort the filtered data. You can also copy filtered data and paste it elsewhere on the same worksheet or in a different worksheet.

To insert and rename a worksheet:

Step 1 Click the New Sheet button to the right of the Qtrly Sales by Item sheet tab to insert a new worksheet

Step 2 Name the new worksheet Top Five

www.ebook777.com

Activate the Qtrly Sales by Item sheet tab

You can sort the filtered data using the Sort A to Z (Smallest to Largest) or Sort Z to A (Largest to Smallest) buttons, the Sort dialog box or the sorting commands in a field’s Filter menu.

To sort the filtered data in descending—largest to smallest—order by Total Sales and then copy/paste the filtered data to the new worksheet:

Step 1 Click the Total Sales Filter icon to view the Filter menu; then click the Sort Largest to Smallest command

Step 2 Observe that the filtered records are sorted in descending order by Total Sales

Step 3 Select the filtered data including the header row

You selected data and header row should look similar to Figure 1-44.



FIGURE 1-44 Selected data and header row

Step 4 Right-click the selection; then click Copy on the shortcut menu

Step 5

Activate the Top 5 worksheet

Step 6 Right-click cell A1; then click the first Paste icon on the shortcut menu

Step 7 Resize the columns as necessary; then activate cell A1

Your pasted data should look similar to Figure 1-45.



FIGURE 1-45 Filtered data pasted in a new worksheet

To unfilter the data on the Qtrly Sales by Item worksheet:

Step 1 Activate the Qtrly Sales by Item worksheet

Step 2 Press the ESC key to clear the Windows clipboard

Step 3 Activate any cell inside the range, if necessary, to deselect the data and reposition the active cell

Step 4 Click the Total Sales Filter icon to view the Filter menu

www.ebook777.com

Click (Select All); then click OK

Step 6 Observe that the range is unfiltered and remains in its original Record Number order

Creating a Custom Filter

Creating a custom filter allows you to specify complex filter criteria, such as filtering a range by two or more criteria in the same field, using the wildcard symbols to find specific text in the field or finding a number greater than or less than another number in the field.

For example, you can create a custom filter to:

display all the records for the Outdoors or Sports Departments, display all the records where the Description text begins with the characters ba, or display all the records where the Items Sold value is less than or equal to 1,000 and greater than or equal to 10,000.



To filter the data range to show all the records for the Outdoors or Sports Departments using a custom filter:

Step 1 Click the Department Filter arrow

Step 2 Point to Text Filters; then click Custom Filter to launch the Custom AutoFilter dialog box

The Custom AutoFilter dialog box contains four text boxes presented in two rows, as shown in Figure 1-46,

Step 3 Click the second text box arrow in the first row; then click Outdoors in the list

Step 4 Click the Or option button, if necessary

Step 5 Click the first text box arrow in the second row; then click equals

Step 6 Click the second text box arrow in the second row; then click Sports

Your Custom AutoFilter dialog box should look similar to Figure 1-46.



FIGURE 1-46 Custom AutoFilter dialog box

Step 7 Click OK

Step 8 Scroll the worksheet and observe that only those records where the Department field is equal to Outdoors or Sports are visible; the remaining records are hidden

www.ebook777.com

The top of your filtered data range should look similar to Figure 1-47.



FIGURE 1-47 Data range filtered for items in the Outdoors or Sports departments

Step 9 Unfilter the range

To filter the data range to view all the rows where the item Description begins with the characters ba using the asterisk (*) wildcard symbol:

Step 1 Click the Description Filter arrow

Step 2 Point to Text Filters; then click Custom Filter to launch the Custom AutoFilter dialog box

Step 3 Key ba* in the second text box

Your Custom AutoFilter dialog box should look similar to Figure 1-48.



FIGURE 1-48 Custom AutoFilter dialog box

Step 4 Click OK

Step 5 Scroll the worksheet and observe that only those records where the Description data begins with the characters ba are visible; the remaining records are hidden

Step 6 Unfilter the range

To filter the data range to display all the records whose Items Sold values are less than or equal to 1,000 and greater than or equal to 10,000:

Step 1 Click the Items Sold Filter arrow

Step 2 Launch the Custom AutoFilter dialog box

Step 3 Click the first text box arrow; then click is less than or equal to (scroll to view this option, if necessary)

Step 4

www.ebook777.com



Step 5 Click the Or option button

Step 6 Click the third text box arrow; then click is greater than or equal to

Step 7 Key 10,000 in the fourth text box

Your Custom AutoFilter dialog box should look similar to Figure 1-49.



FIGURE 1-49 Custom AutoFilter dialog box

Step 8 Click OK

Step 9 Scroll the worksheet and observe that only those records where the Items Sold value is 1000 or less OR is 10,000 or greater are visible; the remaining records are hidden

You can create a complex filter by combining a custom filter with a simple filter.

Step 10

Filter the already filtered range to show only those records for the Clothing Department in stores in the State of Wyoming

Step 11 Observe that only a single record meets the custom Items Sold filter criteria for the Clothing Department in Wyoming stores

Your filtered data range should look similar to Figure 1-50.



FIGURE 1-50 Data range filtered using Items Sold custom filter criteria plus Department and State filter criteria

Step 12 Unfilter the range

To turn off the Filter feature:

Step 1 Click the Filter button in the Sort & Filter group on the DATA tab to turn off the Filter feature

Step 2 Observe that the Filter arrows are removed from the header row

You can also enter filter criteria in a separate area of your worksheet and use these criteria to filter a data range.

Using Advanced Filter to Filter a Data Range

www.ebook777.com

option for creating complex filters, such as multiple filter criteria in a single field or multiple filter criteria across fields. In some instances, it may be faster to use the Advanced Filter feature instead of specifying a custom filter using the Filter feature.

To use the Advanced Filter feature, you first enter the filter criteria in a criteria range in the worksheet. It is a good idea to place the criteria range above and to the right of the data range to avoid interfering with the filtering process. It is important to set up your advanced filter criteria carefully:

The first row of the criteria range must contain field labels that exactly match the spelling of the comparable labels in the data range’s header row. The same formatting is not required. The remaining rows in the criteria range contain the filter criteria. Filter criteria for multiple fields entered across the same row specifies an ‘And’ condition. Filter criteria for multiple fields entered in subsequent rows specifies an ‘Or’ condition.

For example, suppose you now want to view all the records for Idaho stores whose Description data begins with the character b and whose Items Sold data is greater than 500 and less than 1,500 or records for Wyoming stores whose Description data begins with the character f and whose Items Sold data is greater than 1,000.

Figures 1-51 and 1-52 illustrate the advanced filter criteria in the criteria range L1:03.



FIGURE 1-51 Advanced filter criteria with ‘And’ conditions





FIGURE 1-52 Advanced filter criteria with both ‘And’ and ‘Or’ conditions



Remember that your field labels for the advanced filter criteria must exactly match the spelling of the data range header row field labels. A good way to avoid keying errors is to copy/paste the data range header row field labels into the first row of the criteria range.

To enter the advanced filter field labels and filter criteria in the range L1:O3:

Step 1 Copy and Paste the contents of cell: B4 into cell L1 E4 into cell M1 F4 into cell N1 F4 into cell O1

Step 2 Press the ESC key to clear the Windows clipboard

The criteria range L1:O1 on your worksheet should look similar to Figures 1-51 and 1-52.

The first group or set of filter criteria is: records for Idaho stores whose Description data begins with the lowercase character b and whose Items Sold data is greater than 500 and less than 1,500.

To enter the first set of criteria:

Step 1

www.ebook777.com

b* in cell L2 Idaho in cell M2 >500 in cell N2 <1500 in cell O2

Step 2 Left align the contents of the range L2:O2, if necessary

Each additional column of criteria adds an “And” condition; therefore only those records that meet all four criteria in the group or set are displayed. The criteria range L1:O2 on your worksheet should look similar to Figure 1-51.

The second group or set of filter criteria is: records for Wyoming stores whose Description data begins with the character f and whose Items Sold data is greater than 1,000.

To enter the second set of criteria:.

Step 1 Enter: f* in cell L3 Wyoming in cell M3 >1000 in cell N3

Step 2 Left align the contents of the range L3:N3, if necessary

Each additional criteria row adds an “Or” condition; therefore only those records that meet the first group of filter criteria or those records that meet the second group of filter criteria are displayed. The criteria range L1:O3 on your worksheet should look similar to Figure 1-52.

Now that the criteria range is complete, you are ready launch the Advanced Filter dialog box in which you specify the data range to be filtered and the location of the

criteria range. You can launch the Advanced Filter dialog box by clicking the Advanced button in the Sort & Filter group on the DATA tab.

To launch the Advanced Filter dialog box:

Step 1 Activate any cell inside the data range A4:I130

Step 2 Click DATA tab on the Ribbon, if necessary; then locate the Sort & Filter group

Step 3 Click the Advanced button in the Sort & Filter group to launch the Advanced Filter dialog box

In the Advanced Filter dialog box, you may either key the range references or collapse the dialog box and use the mouse pointer to select the range references.

You can also choose to filter the data range in place or copy the filtered data to a new location on the active worksheet.

To filter the data range and copy the filtered records to a new location using the Advanced Filter feature:

Step 1 Click the Copy to another location option button

Step 2 Key L1:O3 in the Criteria range text box

Step 3 Key A136 in the Copy to text box

Because the active cell is inside the data range to be filtered, Excel automatically inserts the range reference in the List range text box. Your Advanced Filter dialog

www.ebook777.com





FIGURE 1-53 Advanced Filter dialog box



Step 4 Click OK

Step 5 Scroll to view the filtered data in the range A136:I145

Step 6 Sort the filtered data in ascending order by State

Your filtered and sorted data should look similar to Figure 1-54.



FIGURE 1-54 Filtered, copied, pasted and sorted data

Step 7 Save the workbook and leave it open for the next section

It can be useful to add temporary subtotals for specific fields when analyzing data in a large data range.

www.ebook777.com

Objective 1C: Selecting Ways to Add Subtotals, a Grand Total and Data Grouping to a Data Range After sorting the data you can then insert subtotals based on changes to specific fields.

Outlining the data allows you to collapse or expand portions of the data range in order to view specific data.

You can use buttons in the Outline group on the DATA tab to insert subtotals and a Grand Total in your data range and outline (group) the data.

Adding Subtotals and an Outline to a Data Range

The Subtotal feature allows you to insert temporary field subtotals and a Grand Total in a data range.

When you insert temporary field subtotals, Excel also outlines the data range. You can expand or collapse the outline to view all the details, just the subtotals or just the Grand Total.

Before you insert temporary subtotals you should sort the data range in subtotal order. For example, to add Total Sales subtotals by State, you must first sort the data range by State.

After the data range is sorted in the desired order, you can insert subtotals by setting options in the Subtotal dialog box. Launch the dialog box by clicking the Subtotal button in the Outline group on the DATA tab.

To add temporary subtotals for Total Sales by State:

Step 1 Sort the data range A4:I130 in ascending order by State

Step 2 Click the DATA tab on the Ribbon, if necessary; then locate the Outline group

Step 3 Click the Subtotal button in the Outline group to launch the Subtotal dialog box

Step 4 Click the At each change in arrow; then click State to tell Excel that each time the State name changes, for example from Idaho to North Dakota, to insert a subtotal

Step 5 Click the Use function arrow; then click Sum, if necessary, to indicate the type of subtotal calculation

Step 6 Click the Total Sales checkbox in the Add subtotal to list to insert a check mark to tell Excel in which field to insert the subtotals

Step 7 Remove the check mark from all other Add subtotal to list checkboxes

Step 8 Click the Replace current subtotals checkbox to insert a check mark, if necessary

Step 9 Click the Summary below data checkbox to insert a check mark, if necessary; this places the subtotal data immediately below the applicable rows

Your Subtotal dialog box should look similar to Figure 1-55.

www.ebook777.com



FIGURE 1-55 Subtotal dialog box

Step 10 Click OK

Step 11 Scroll the worksheet to view the outlined data range, the Total Sales subtotals by State and the Total Sales Grand Total

Step 12 Widen column H, Total Sales, if necessary to view all the subtotals and Grand Total

The bottom part of your data range should look similar to Figure 1-56.



FIGURE 1-56 Outlined data range with Total Sales subtotal for Idaho and the Total Sales Grand Total



Step 13 Activate cell H133 to view the formula using the SUBTOTAL function in the Formula Bar

The SUBTOTAL function uses numbers from 1-11 (includes hidden values) and 101-111 (ignores hidden values) to indicate which function, such as AVERAGE, MAX, MIN, COUNT or SUM, is to be used in the calculation.

The SUBTOTAL function in cell H133 has two arguments: the function_num which indicates that the SUM function (9) is to be used and the range that contains the data (H97:H132) to be summed.

The formula in your Formula Bar should look similar to Figure 1-57.

www.ebook777.com



FIGURE 1-57 Formula to calculate the Idaho subtotal



Step 14 Activate cell H134 to view the formula using the Subtotal function in the Formula Bar; this formula sums the values in the range H5:H132

Expanding and Collapsing the Outline

Excel provides several symbols or icons you can click to expand or collapse the outlined data range to show or hide details, subtotals and the Grand Total. The outline hide detail and show detail symbols allow you to collapse or expand the group of rows related to a specific subtotal. The outline level symbols allow you to collapse or expand : all the details ( ), the subtotals and Grand Total ( ), or just the Grand Total ( ).

To expand and collapse a portion of the outline using the hide and show detail symbols:

Step 1 Scroll to view the Idaho subtotal and hide detail symbol in row 59

Step 2 Click the hide detail symbol; then scroll to view the top of the data range

Step 3 Observe that the Idaho detail rows are collapsed or hidden, the Idaho subtotal row is the first row in the range and the show detail outline symbol replaces the hide detail outline symbol

The top part of your data range should look similar to Figure 1-58.



FIGURE 1-58 Collapsed outline



Step 4 Click the show detail symbol to display the hidden Idaho detail rows



To expand and collapse the outline using the outline level symbols:

Step 1 Click the outline level 2 symbol at the top of the outline pane to view just the

www.ebook777.com



Your data range subtotals and Grand Total should look similar to Figure 1-59.



FIGURE 1-59 Data range subtotals and Grand Total



Step 2 Click the outline level 1 symbol at the top of the outline pane to view just the Grand Total

Step 3 Click the outline level 3 symbol at the top of the outline pane to view all the detail rows, subtotals and Grand Total

Step 4 Click the outline level 2 symbol at the top of the outline pane to view only subtotals and Grand Total

One useful way to use the temporary subtotals is to chart them.

Creating a Chart from Subtotals

You can select the summarized subtotals and create a chart on its own chart sheet or an embedded chart on the worksheet with the data. Then you can print the chart or chart and subtotals.





To create a pie chart on its own chart sheet using the temporary subtotals:

Step 1 Select the nonadjacent ranges that contain the subtotal descriptions and values using the CTRL key

Your selected subtotal data should look similar to Figure 1-60.



FIGURE 1-60 Selected subtotal data

Step 2 Press the F11 key to create a default column chart on its own chart sheet

Step 3 Convert the chart to a 3-D Pie chart using the Change Chart Type button on the CHART TOOLS DESIGN tab

You can now use various buttons on the CHART TOOLS contextual tabs to select, add and format chart elements as desired. Alternatively, you can use the Chart Elements and Chart Styles buttons in the upper-right corner of the chart to add and format chart elements.

www.ebook777.com

chart.

Step 4 Add missing chart elements, such as the Legend, Chart Title and/or Data Labels and format the chart as desired using buttons on the CHART TOOLS contextual tabs or the Chart Elements and Chart Styles buttons

Your formatted 3-D Pie chart should look similar to Figure 1-61.



FIGURE 1-61 3-D Pie chart created from temporary subtotals and formatted with the Chart Elements and Chart Styles buttons

When you no longer need the temporary subtotals you can remove them by clicking the Remove All button in the Subtotals dialog box.

To remove the temporary subtotals and Grand Total and sort the data range back into its original order:

Step 1 Activate the Qtrly Sales by Item worksheet

Step 2

Expand the data range to view all the detail rows

Step 3 Activate any cell inside the data range, if necessary

Step 4 Click the DATA tab on the Ribbon, if necessary; then locate the Outline group

Step 5 Click the Subtotal button in the Outline group to launch the Subtotal dialog box

Step 6 Click the Remove All button in the Subtotal dialog box

Step 7 Sort the data range in ascending order by Record Number, if necessary

Now that the temporary subtotals have been removed, the chart created from those subtotals is no longer valid. You first view the chart sheet and then you delete it.

Step 8 Activate the chart sheet

Step 9 Observe that Excel attempts to chart all the data in the range

Step 10 Right-click the chart sheet tab; then click Delete on the short cut menu and the Delete button in the confirmation dialog box

Inserting Nested Subtotals

You can also create nested subtotals—subtotals for a secondary group of data

www.ebook777.com



remember to first sort the data in the desired order, add subtotals for the outer or primary group, and then add subtotals for the inner or secondary group.



To insert nested subtotals for Total Sales for each store within each State plus Total Sales subtotals for each State:

Step 1 Activate any cell within the data range A4:H130, if necessary; then sort the data by State in ascending order, if necessary and then by Store # in ascending order using the Sort dialog box

Step 2 Launch the Subtotal dialog box

Step 3 Click the At each change in arrow; then click State, if necessary

Step 4 Verify Sum is in the Use function text box

Step 5 Verify Total Sales in the Add subtotal to list contains a check mark and all other checkboxes in the list are empty

Your Subtotal dialog box should look similar to Figure 1-62.



FIGURE 1-62 Subtotal dialog box

Step 6 Click OK to insert the outer or primary group subtotals

Step 7 Scroll the worksheet and observe that Total Sales subtotals are inserted for each State

Step 8 Launch the Subtotal dialog box

Step 9 Click the At each change in arrow; then click Store #

Step 10 Click the Replace current subtotals checkbox to remove the check mark, if necessary

Your Subtotal dialog box should look similar to Figure 1-63.

www.ebook777.com



FIGURE 1-63 Subtotal dialog box

Step 11 Click OK to insert nested subtotals for the inner or secondary groups

Step 12 Scroll the worksheet and observe that Total Sales subtotals are inserted for each store within State and for each State

The bottom of your data range should look similar to Figure 1-64.



FIGURE 1-64 Nested subtotals for Idaho Store #5 and for the entire State



When you add nested subtotals, you increase the number of outline level symbols. In the current outline with nested subtotals you have four outline level symbols.

To expand or collapse the outline using the outline level symbols:

Step 1 Click the outline level 1 symbol to view the Grand Total

Step 2 Click the outline level 2 symbol to view the State subtotals

Step 3 Click the outline level 3 symbol to view the Store #, State and Grand Totals

www.ebook777.com

Click the outline level 4 symbol to view the details and subtotals

To remove all the subtotals and return the data range to its original order:

Step 1 Launch the Subtotal dialog box; then click the Remove All button

Step 2 Sort the data range back into its original ascending Record Number order, if necessary

Step 3 Save the workbook and close it

Outlining Data Containing Subtotals

When your data range contains subtotals—either manually inserted or inserted using the Subtotal dialog box—you can use the Group and Ungroup buttons in the Outline group on the DATA tab to outline (group) the data range by rows and/or columns.

You begin by opening the Lesson 1 Data File 3 workbook and saving it with a new name.

Step 1 Open the Lesson 1 Data File 3 workbook; then save the workbook as Lesson 1 Grouping and Ungrouping Data

Step 2 Scroll the worksheet to view the data range; observe that the data range was sorted by State then by Department and then by Description with subtotals inserted at each sort level



You can outline (group) the data again by clicking the Group button arrow in the Outline group and then clicking a grouping command on the menu.

Click the Group command to launch the Group dialog box in which you can specify the data be grouped or outlined by rows or by columns. Click the Auto Outline command to allow Excel to group the data by both rows and columns.

To use Auto Outline to group or outline the data range:

Step 1 Click any cell in the data range, if necessary

Step 2 Click the DATA tab, if necessary; then click the Group button arrow in the Outline group to view its menu

Step 3 Click the Auto Outline command on the Group button menu to outline the data range’s rows and columns

Your worksheet should look similar to Figure 1-65.

www.ebook777.com



FIGURE 1-65 Outlined data range



Step 4 Click each of the row and column outline level symbols to view the Grand Total and different levels of subtotals

Step 5 Save and close the workbook

In this lesson, you learned how to organize columns and rows of data in a range so that the data can later be easily sorted or filtered. You also learned how to protect against invalid data entry by setting validation rules for cells in the range. Then you learned how to use the Data Form to enter and locate data in a range.

You learned multiple ways to sort and filter data in a data range. Then you learned how to insert and remove subtotals and a Grand Total for various fields in the data range. Finally, you learned how to outline data containing subtotals.

In Lesson 2, you learn about defined tables: why you might want to use a defined table instead of a data range, how to create a new defined table and multiple ways to convert a data range to a table. You also learn how to resize, scroll, format, sort, filter and summarize values in a table; then convert a table back to a simple data range.

www.ebook777.com

Lesson 2: Defining and Using Tables Introduction In Lesson 1, you learned how to organize data in a range in order to sort, filter, subtotal and outline the data. In Lesson 2, you follow these same rules when organizing data you then define as a table. Defining well-organized data as a table gives you access to Excel’s Table features, which make it easier to update, sort, filter, format and summarize the data.

Next, you learn to update, format, sort, filter and summarize table data using Table features.

Finally, you learn how to convert a defined table to a data range.

Learning Objectives After completing this lesson you will be able to: Objective 2A: identify data range and table differences; identify multiple ways to define an Excel table using buttons on the HOME and INSERT tabs, the Quick Analysis feature, a keyboard shortcut and by formatting a data range as a table, and Objective 2B: recognize ways to enter data and formulas in a table; change table formatting; resize a table; scroll, sort, filter and summarize a table; and convert a table to a data range.

www.ebook777.com

Key Terms calculated column column specifier Format as Table button Quick Analysis button semi-selection process structured reference table Table button

Objective 2A: Identifying Data Range and Table Differences; Identifying Multiple Ways to Define an Excel Table Using Buttons on the HOME and INSERT tabs, the Quick Analysis feature, a Keyboard Shortcut and by Formatting a Data Range as a Table Defining a range of data as an Excel table allows you to easily assign a name to the table and to more quickly access the Filter menu that contains commands you can use to sort and/or filter the data.

By default, Table style formatting is automatically applied to a defined table; however, you can quickly change the format by selecting a new style. Additionally, when you add a new row to the bottom of the table or a new column to the table, Excel automatically includes the row or column as part of the formatted table.

Other time-saving features include easy-to-read formulas where column names (labels) replace cell references, automatic display of Filter arrows, automatic formula copying, summary totals and more.

Because tables are so useful, Excel provides several ways to define them. For example, you can create a table by defining: blank cells as an empty table, existing data as a table using the Table button on the INSERT tab, existing data as a table using the Quick Analysis feature, existing data as a table using a keyboard shortcut, and by formatting existing data as a table using the Format as Table button on the HOME tab.

You begin this section by opening an existing workbook and saving it with a new name. Then you define and name an empty table.

To open and save an existing workbook:

Step 1 Open the Lesson 2 Data File workbook; then save the workbook as Lesson 2 Working with Tables

www.ebook777.com



Now you are ready to define and name an empty table.

Defining and Naming an Empty Table

To create an empty table you must: select a range of cells, define the range as a table, specify a custom header row (or allow Excel to insert a default header row), then enter your data.

To select a range of cells and define the range as a table:

Step 1 Click the Empty Table sheet tab, if necessary, to activate the worksheet

Step 2 Select the range A4:H5 using any method you prefer; you use this range to enter the field names in the header row (A4:H4) and the first data record (A5:H5)

Step 3 Click the INSERT tab on the Ribbon; then locate the Tables group

Step 4 Click the Table button in the Tables group to launch the Create Table dialog box

The dialog box and selected range on your screen should look similar to Figure 21.



FIGURE 2-1 Selected range to be defined as a table



Step 5 Click OK

Step 6 Observe that the range is formatted with the default Table style, default header text (Column1, Column2 and so forth) appear in the header row and Filter arrows appear in each column in the header row

Your new selected table should look similar to Figure 2-2.



FIGURE 2-2 Range header row



www.ebook777.com

To replace the default header text with the correct field names:

Step 1 Click cell A4 to activate the cell, if necessary; then key Record Number and press the TAB key

Step 2 Key Description in cell B4 and press the TAB key

Step 3 Continue to key the actual column labels as follows: C4=Department D4=Store # E4=State F4=Items Sold G4=Sales Price H4=Total Sales

Step 4 Activate cell A5 and then resize the columns A:H as desired using the mouse pointer in order to see both field names and the Filter arrows

Your table should now look similar to Figure 2-3.



FIGURE 2-3 Table with edited field names

When the active cell is inside a defined table, the TABLE TOOLS DESIGN contextual tab appears on the Ribbon. You learn how to use many of the buttons

on the TABLE TOOLS DESIGN tab as you work through this lesson.



For example, Excel assigns the default name Table1, Table2 and so forth as you define tables in your workbook. Table names are used when constructing formulas; you can also key a table’s name in the Name Box to select the entire table.

You can change the default table name to a more meaningful name by keying the new name in the Table Name box in the Properties group on the TABLE TOOLS DESIGN tab.



To rename the new Table1:

Step 1 Make certain the active cell is inside the table; then click the TABLE TOOLS DESIGN tab on the Ribbon, if necessary, and locate the Properties group

Step 2 Key NewTable in the Table Name box in the Properties group; then press the ENTER key

The Properties group on the TABLE TOOLS DESIGN tab should look similar to Figure 2-4.



www.ebook777.com



Step 3 Save the workbook and leave it open for the next section

Later in this lesson you enter a few records in the new NewTable. You also learn how to format and resize a table using the NewTable table.

Next, you learn to define a table using an existing data range.

Defining a Table Using Existing Data

You can define an existing well-organized data range as a table by activating a cell in the range and clicking the Table button in the Tables group on the INSERT tab to launch the Create Table dialog box.

To define and name a new table based on an existing well-organized data range:

Step 1 Click the Insert Table sheet tab to activate the worksheet

Step 2 Observe the data in the range A4:J130; this is the same well-organized data range you worked with in Lesson 1

Step 3 Click any cell inside the data range; then click the INSERT tab on the Ribbon, if necessary, and locate the Tables group

Step 4 Click the Table button in the Tables group to launch the Create Table dialog box

Step 5 Verify the selected data range is the absolute range $A$4:$J$130

Step 6 Click the My table has headers checkbox, if necessary, to insert a check mark to specify that the selected data range already contains the correct header text (field names)

Your selected range and Create Table dialog box should look similar to Figure 2-5.



FIGURE 2-5 Selected range and Create Table dialog box

Step 7 Click OK

Step 8 Observe that data range is now a defined table including the Filter arrows and Table style formatting; the TABLE TOOLS DESIGN contextual tab appears on the Ribbon

Step 9 Key InsertTable in the Table Name text box in the Properties group on the TABLE TOOLS DESIGN tab; then press the ENTER key

Step 10 Click cell A4; then save the workbook and leave it open for the next section

www.ebook777.com



Next, you learn how to define a table using the Quick Analysis feature.

Defining a Table Using the Quick Analysis Feature

When you select worksheet data, the Quick Analysis button appears in the lower-right corner of the selection. You can click the Quick Analysis button to view a gallery of analysis options: FORMATTING, CHARTS, TOTALS, TABLES and SPARKLINES.

Clicking the TABLES category in the Quick Analysis gallery displays buttons you can click to define a table or create a PivotTable.



To define a table using existing data and the Quick Analysis feature:

Step 1 Click the Quick Analysis Table sheet tab to activate the worksheet

Step 2 Key A4:J130 in the Name Box; then press the ENTER key to select the range

Step 3 Scroll to the bottom of the range; then click the Quick Analysis button in the lower-right corner of the selected range to view the Quick Analysis gallery

Your selected range and Quick Analysis gallery should look similar to Figure 2-6.



FIGURE 2-6 Selected range and Quick Analysis gallery



Step 4 Click the TABLES category in the Quick Analysis gallery to view the TABLES options

Step 5 Point to the Table button in the TABLES category in the Quick Analysis gallery to view a preview of the defined table

Your screen should look similar to Figure 2-7.

www.ebook777.com



FIGURE 2-7 Table preview



Step 6 Click the Table button in the Quick Analysis gallery to define the table

Step 7 Activate a cell inside the table, if necessary, to view the TABLE TOOLS DESIGN contextual tab; then name the table QuickAnalysis

Step 8 Save the workbook and leave it open for the next section

Next, you learn how to quickly format data as a defined table.

Formatting Data as a Defined Table

You can select well-organized data and then format it as a table by clicking the Format as Table button in the Styles group on the HOME tab.



To define existing data as a table by formatting it:

Step 1 Click the Format as Table worksheet to activate it; then click any cell in the data range

Step 2 Click the HOME tab on the Ribbon, if necessary; then locate the Styles group

Step 3 Click the Format as Table button to view a gallery of Table styles

Step 4 Click the light-colored Table style of your choice in the galley to select the absolute range $A$4:$J$130 and launch the Format as Table dialog box containing the same options as the Create Table dialog box

Step 5 Confirm the selected absolute range $A$4:$J$130 and a check mark is in the My table has headers checkbox

Your selected range and Format as Table dialog box should look similar to Figure 2-8.



FIGURE 2-8 Selected range and Format as Table dialog box

www.ebook777.com

Click OK

Step 7 Scroll to view the new table; then name the table FormattedTable

Step 8 Save the workbook and leave it open for the next section

Next, you use the tables you created in this section to learn how to enter and work with table data.

Objective 2B: Recognizing Ways to Enter Data and Formulas in a Table; Change Table Formatting; Resize a Table; Scroll, Sort, Filter and Summarize a Table; and Convert a Table to a Data Range

After you define a table, you are then ready to enter data and formulas if the table is empty or manipulate the data by sorting, filtering and summarizing it if the table contains data.

After defining a table with or without data, you may find that you need to expand the table by adding additional categories of data (field names in the header row) and/or by adding more records (rows) to the bottom of the table.

You may also decide to replace the table’s default Table style formatting with a different style or scroll a large table leaving the field names in the header row visible as you scroll from page to page.

You can quickly perform these and other tasks using defined Table features, such as the automatic:

addition of Filter arrows in the header row, table resizing when you add fields and records, use of table and field names instead of cell references in formulas, freezing the header row if you scroll to another page of the table, or summarization by turning on or off a total row.

Entering Data and Expanding the Table Vertically

You can enter data directly in the table’s cells just like you do in a simple data range by pressing the TAB or RIGHT ARROW keys to move from cell to cell across the row or by clicking a cell with the mouse pointer. Pressing the SHIFT+TAB or LEFT ARROW keys allows you to move back to previous cells.



www.ebook777.com

feature to select functions and to click cells instead of keying cell references.

The column in which you enter a formula becomes a calculated column; Excel automatically copies the entered formula down to other cells in a table’s calculated column.

To enter data in cells A5:G5, the first row of the NewTable table:

Step 1 Click the Empty Table sheet tab, if necessary, to activate the worksheet

Step 2 Click cell A5, if necessary

Step 3 Enter the following data for record 1 pressing the TAB or RIGHT ARROW keys to move to the next cell to the right (Do not key the = sign) A5=1 B5=baseball bats C5=Sports D5=1 E5=Idaho F5=1130 G5=21.95

Step 4 Press the TAB or RIGHT ARROW key to activate cell H5

Step 5 Observe that when you activate the last cell in the row, Excel automatically adds a second empty row to the table

Your table should now look similar to Figure 2-9.





FIGURE 2-9 Data entered in table



Step 6 Save the workbook and leave it open for the next section

Entering and Copying Formulas in a Table

Cell H5 should contain a formula that calculates the Total Sales value by multiplying the Items Sold value in cell F5 times the Sales Price value in cell G5. In the data range examples you worked with in Lesson 1, this formula is =F5*G5.

New table formulas, however, are generally created with structured references. A structured reference uses the table’s name and/or its field names (column labels), called column specifiers, in place of cell references.

The table name is important when accessing table data from outside the table. Using a column specifier instead of a cell reference makes it easier to understand a formula’s calculation.

Manually building or editing table formulas containing structured references requires that you follow a strict, complex syntax. To save time and avoid errors, you should use the mouse pointer to click a cell or cell range instead of manually entering the cell references.

Clicking a cell or cell range, sometimes called the “semi-selection process,” allows Excel to insert the appropriate syntax elements into the formula, for example special item or column specifiers surrounded by nested brackets.

www.ebook777.com



To enter the formula for the Total Sales calculation using the Formula AutoComplete feature and the ROUND function to round the results to zero decimal places:

Step 1 Verify that cell H5 is the active cell; then key =R to display the Formula AutoComplete list

Step 2 Double-click ROUND in the Formula AutoComplete list to inserting the ROUND function

Your cell H5 with the incomplete formula should look similar to Figure 2-10.



FIGURE 2-10 Incomplete formula in cell H5



You complete the formula by using the mouse pointer to select the arguments for the formula.

Step 3 Click cell F5 to allow Excel to insert the first structured reference with appropriate syntax

Step 4

Observe that Excel inserts the [@[Items Sold]] structured reference instead of the F5 cell reference; nested brackets surround the @ special item specifier indicating the current row and the Items Sold column specifier

Step 5 Key an asterisk (*) to indicate multiplication

Step 6 Click cell G5 to insert the second structured reference with appropriate syntax

Step 7 Observe that Excel inserts the [@[Sales Price]] structured reference instead of the G5 cell reference; nested brackets surround the @ special item specifier indicating the current row and the Sales Price column specifier

Step 8 Key ,0) to end the formula by specifying rounding to zero decimal places

Your formula in cell H5 should look similar to Figure 2-11.



FIGURE 2-11 Formula in cell H5



Step 8 Press the ENTER key to enter the formula

Because you are working in a defined table, Excel automatically created a calculated column for column H and copies the formula in cell H5 to cell G5. Excel uses the AutoCorrect feature to automatically copy the formula in cell H5 to H6

www.ebook777.com



Step 9 Click cell H6 and observe the copied formula in the Formula Bar

Step 10 Observe the AutoCorrect Options icon to the right of cell H6



Step 11 Click cell A6; then enter a second record to the table using the empty table row A6=2 B6=baseballs C6=Sports D6=1 E6=Idaho F6=1500 G6=3.95

Step 12 Press the TAB or RIGHT ARROW key to activate cell H5; then observe copied formula’s results in cell H5 calculates

Your updated table should look similar to Figure 2-12.



FIGURE 2-12 Updated table





Step 13 Save the workbook and leave it open for the next section

Using the Data Form with a Table

In Lesson 1 you learned to use the Data Form to quickly enter data in a range. You can also use the Data Form to enter data in a table.





To enter records 3-5 using the Data Form:

Step 1 Verify the active cell is in the table

Step 2

www.ebook777.com



Step 3 Enter the records shown in Figure 2-13 using the Data Form



FIGURE 2-13 Additional records

Step 4 Click the Data Form Close button after you enter the data for record #5

Your table with five records should now look similar to Figure 2-14.



FIGURE 2-14 Updated table

Resizing a Table Using the Mouse Pointer, the Resize Table Button and Dynamically

You have learned how to automatically resize a table vertically by pressing the ENTER key or using the Data Form to add additional records.

You can manually resize a table by dragging the small resizing symbol in the lower-right corner of the table down and/or to the right. You also can manually resize a table using the Resize Table button in the Properties group on the TABLE TOOLS DESIGN tab to specify a new range for the table.



Manually Resizing a Table Using the Mouse Pointer as a Resizing Pointer

The lower-right corner of a table contains a small bracket or resizing symbol. When you place the mouse pointer on this resizing symbol, the mouse pointer becomes a double-headed arrow sizing pointer, as shown in Figure 2-15.



FIGURE 2-15 Mouse pointer as resizing pointer



Dragging the resizing symbol to the right adds columns to the table; dragging the resizing symbol downward adds rows to the table.

To resize the NewTable table using the mouse pointer:

Step 1 Position the mouse pointer on the resizing symbol in the lower-right corner of the NewTable table, as shown in Figure 2-15

Step 2 Drag the resizing symbol two columns to the right to move the table’s right boundary between columns J and K; release the mouse button and observe the additional two fields added to the table

Step 3 Drag the resizing symbol down to move the table’s bottom boundary between rows 20 and 21

www.ebook777.com

Your resized table should look similar to Figure 2-16.



FIGURE 2-16 Resized table



You can quickly undo a table’s resizing by clicking the Undo button on the Quick Access Toolbar.

Step 4 Click the Undo button face on the Quick Access Toolbar twice to undo the table resizing

Manually Resizing a Table Using the Resize Table Button

You can also resize a table by redefining its range in the Resize Table dialog box. To launch the Resize Table dialog box, click the Resize Table button in the Properties group on the TABLE TOOLS DESIGN tab.

To manually resize a table by redefining its range:

Step 1 Click any cell in the table, if necessary, to add the TABLE TOOLS DESIGN

contextual tab to the Ribbon

Step 2 Click the TABLE TOOLS DESIGN tab, if necessary; then locate the Properties group

Step 3 Click the Resize Table button in the Properties group to launch the Resize Table dialog box, which resembles the Create Table dialog box

Step 4 Change the absolute range for the table to $A$4:$J$20

Your Resize Table dialog box should look similar to Figure 2-17.



FIGURE 2-17 Resize Table dialog box

Step 5 Click OK to resize the table

Step 6 Observe that the table again extends to row 20 and column J

Step 7 Click the Undo button face on the Quick Access Toolbar once to undo the table resizing

Automatically Resizing a Table Horizontally by Adding Columns

www.ebook777.com

Most tables of data are dynamic—new records are added frequently and additional fields of data might also be added. You have already seen how Excel automatically incorporates new rows into a table during the data entry process, thereby resizing the table vertically as necessary.

You can dynamically add additional fields to a table by inserting a new column in the table or by keying new field names in adjacent columns in the header row.

To add two fields to an existing table:

Step 1 Click cell I4; then key Popularity Ranking as the field name and press the TAB key

Step 2 Key Reorder or Discontinue in cell J4 and press the ENTER key

Step 3 Observe that the two new fields are incorporated into the table







Step 4 Save the workbook and leave it open for the next section

Selecting Table Elements

You can select table elements—columns, rows, cells and the entire table—using three methods:

dragging the mouse pointer as a selection pointer, clicking a command on a shortcut menu, and pressing keyboard shortcut keys.

Selecting Table Elements Using the Mouse Pointer

You are likely very familiar with the common way to select a small range of cells in a data range or table by dragging across the cells with the mouse pointer as a large white plus sign selection pointer.

To select a small range of table cells using a selection pointer:

Step 1 Move the mouse pointer to cell C5

Step 2 Click and hold the left mouse button and then drag down to cell C9; the range of cells C5:C9 is selected

Step 3 Click any cell in the worksheet outside the table to deselect the table range

You can select the data in a specific field or record in a table using the mouse

www.ebook777.com

use to select the entire column or row in a worksheet).



To select all the cells in the table’s Items Sold field using a selection pointer:

Step 1 Slowly move the mouse pointer down slightly into the table’s Items Sold field name header text in cell F4; the mouse pointer becomes a small black selection pointer as shown in Figure 2-18



FIGURE 2-18 Selection pointer and the table’s Items Sold field



Step 2 Click the left mouse button when you see the selection pointer to select the cells containing data in the Items Sold field (cells F5:F9)

Your table with selected cells should look similar to Figure 2-19.



FIGURE 2-19 Selected data in the table’s Items Sold field



You can display the black selection arrow and alternate between selecting just the data cells or all the cells, including the header text and the total row cell, if available.



Step 3 Display the black selection arrow, if necessary; then click the left mouse button again to select the header cell and the data cells, F4:F9

Step 4 Click the left mouse button again to reselect just the data cells

Step 5 Click any cell outside the table to deselect the cells

To select an entire record using the mouse pointer:

Step 1 Slowly move the mouse pointer slightly inside the left boundary of cell A6; the mouse pointer becomes a small black selection pointer as shown in Figure 2-20

www.ebook777.com



FIGURE 2-20 Selection pointer in the table’s second row of data



Step 2 Click the left mouse button when you see the selection pointer to select the entire record with data in cells A6:J6

Step 3 Click any cell outside the table to deselect the record and the table

You can select all the data cells in the entire table by moving the mouse pointer to the upper-left corner of the table; the mouse pointer becomes a small thin black selection pointer.

To select all the data cells in the NewTable table:

Step 1 Slowly move the mouse pointer toward the upper-left corner above cell A4; the mouse pointer becomes a small think black selection pointer as shown in Figure 2-21



FIGURE 2-21 Selection pointer in the upper-left corner of the table



Step 2 Click the left mouse button when you see the selection pointer to select all the data cells in the table in the range A5:J9

Step 3 Click any cell outside the table to deselect the table

Selecting Table Elements Using a Shortcut Menu

You can right-click a cell in a specific field to display the Mini Toolbar and a shortcut menu. Then point to the Select command on the shortcut menu to see a submenu of selection options.

Table Column Data—selects the data cells in the a field and ignores the header text and total row cells Entire Table Column—selects all the cells in a field including the header text and total row cells Table Row—selects an entire record

To select Items Sold data in cells F5:F9 using a shortcut menu.

To select the data cells in the Items Sold column using a shortcut menu:

Step 1

www.ebook777.com

shortcut menu

Step 2 Point to Select on the shortcut menu to view the selection submenu as shown in Figure 2-22



FIGURE 2-22 Shortcut menu and Select options submenu



Step 3

Click the Table Column Data command in the submenu to select the data cells in the field

Step 4 Deselect the cells

Step 5 Using the previous Steps 1 and 2 as your guide, display the Select options submenu and select the entire Items Sold field including the header text

Step 6 Deselect the cells

Step 7 Display the Select options submenu and select an entire record by clicking the Table Row command; the row that is selected is based on the cell you right-click in the Items Sold column

Selecting Table Elements Using Keyboard Shortcuts

If you work with tables extensively, you may choose to add two useful keyboard shortcuts—one for selecting fields and one for selecting records—to your library of often used shortcuts.

To select a field or record using the CTRL+SPACEBAR and SHIFT+SPACEBAR keyboard shortcuts:

Step 1 Click any cell in the Items Sold field; then press and hold the CTRL key and press the SPACEBAR to select all the data cells in the Items Sold field

Step 2 Deselect the cells

Step 3

www.ebook777.com

key and press the SPACEBAR to select the entire record

Step 4 Deselect the cells



Step 5 Save the workbook and leave it open for the next section

Changing Table Formatting

You can format numbers, align cell contents and change column widths in the table just as you would elsewhere in a worksheet.

Applying Number Formatting

You can apply number formatting to data in selected cells using options in the Number tab in the Format Cells dialog box or in the Numbering group on the Ribbon HOME tab.

To apply number formatting to the Items Sold data using the Number Format dialog box launcher to launch the Number tab in the Format Cells dialog box:

Step 1 Select the data cells only in the Items Sold column using the small, black selection pointer (review the previous section, if necessary)

Step 2 Click the HOME tab on the Ribbon, if necessary; then locate the Number group and point to the Number Format dialog box launcher in the lower-right corner of the Number group

Your screen should look similar to Figure 2-23.



FIGURE 2-23 Selected table cells and the Number Format dialog box launcher



Step 3 Click the Number Format dialog box launcher to launch the Number tab in the Format Cells dialog box

Step 4 Format the selected data as numbers with zero decimal places and a thousand separator using options in the Number tab in the Format Cells dialog box



To apply number formatting to the Sales Price data using a keyboard shortcut to launch the Format Cells dialog box:

Step 1

www.ebook777.com

the shortcut menu

Step 2 Press the CTRL+1 keys to launch the Format Cells dialog box

Step 3 Format the data as numbers with two decimal places

To apply number formatting to the Total Sales data using buttons in the Numbering group on the HOME tab:

Step 1 Select the data cells only in the Total Sales column using the CTRL+SPACEBAR shortcut keys (review the previous section, if necessary)

Step 2 Click the Comma Style button in the Numbering group to add the style’s thousand separator and two decimal places

Step 3 Click the Decrease Decimal button in the Numbering group twice to remove the decimal places

Step 4 Click any cell outside the table to deselect the data

Aligning Cell Contents

You can apply alignment formatting to data in selected cells using options in the Alignment tab in the Format Cells dialog box or in the Alignment group on the Ribbon HOME tab.

To align cell contents using buttons in the Alignment group on the HOME tab:

Step 1 Select the Record Number data cells using any selection method you prefer; then click the Center button in the Alignment group on the HOME tab to center align the record number data

Step 2 Select the Store # data cells using any selection method you prefer; then center align the Store # data in the cells

Changing Column Widths and Wrapping Long Text in a Cell

You can change column widths using options on the Format button (menu) located in the Cells group on the Ribbon HOME tab. You can also quickly change column widths using the mouse pointer as a sizing pointer.

To wrap long text inside a selected cell, click the Wrap Text button in the Alignment group on the HOME tab or the Wrap Text checkbox on the Alignment tab in the Format Cells dialog box.

To change column widths and wrap long text in the header row cells:

Step 1 Position the mouse pointer as a sizing pointer between worksheet column headings A:B; then and drag to the left to resize the column approximately half its current width

Step 2 Click the Record Number cell in the header row; then click the Wrap Text button in the Alignment group on the HOME tab to wrap the long header text in the cell

Step 3 Resize additional column width as necessary and wrap the header text to make the wrapped and non-wrapped header row text easier to read

Step 4 Make any addition column width and row height adjustments desired to make the

www.ebook777.com



Your table with formatted numbers, resized columns and wrapped text should now look similar to Figure 2-24.



FIGURE 2-24 Table with formatted cell contents, resized columns and wrapped header text

Changing the Table Style

If you do not care for the default Table style applied to the table, you can easily change it by applying a new style using the Table Style gallery on the TABLE TOOLS DESIGN tab.

You can also control the look of a Table style by toggling off or on banded rows, the header row, the total row and so forth with options on the TABLE TOOLS DESIGN tab.

To apply a different Table style to the NewTable table:

Step 1 Click any cell inside the NewTable table, if necessary, to add the TABLE TOOLS DESIGN contextual tab to the Ribbon

Step 2 Click the TABLE TOOLS DESIGN tab, if necessary; then locate the Table Styles group

Step 3

Click the More button, shown in Figure 2-25, to expand the Table Styles gallery



FIGURE 2-25 More button in the Table Styles gallery

You can use the Live Preview feature to see how your table will look with a different style applied.

Step 4 Point to the Table style of your choice in the gallery; then observe the live preview of the style applied to the NewTable table

Step 5 Continue to preview several Table styles; then click the Table Style Medium 15 or other style of your choice to apply the style to the NewTable table

Your table with a new table style applied should look similar to Figure 2-26.



FIGURE 2-26 New Table style applied

www.ebook777.com

TOOLS DESIGN tab to toggle on or off style features.

To explore the Table Style Options:

Step 1 Click various checkboxes in the Table Style Options group to toggle on or off the row and column formatting applied by the Table Style Medium 15 style

Step 2 Return to the style formatting similar to Figure 2-26

Step 3 Save the workbook and leave it open for the next section



Retaining the Header Row Text While Scrolling a Table Vertically

Under three specific criteria, Excel automatically replaces the worksheet column header A, B, C and so forth with the table’s header text as you scroll a table vertically.

The active cell must be inside the table. The header row must not be visible. There must be at least one table row visible.

To retain the header row text while scrolling a table vertically:

Step 1

Click the Insert Table worksheet to activate it, if necessary; then click any cell inside the InsertTable table

Step 2 Drag the scroll box on the vertical scroll bar down until row 7 (record # 3) appears at the top of the table

Step 3 Observe that the table’s header text replaces the column header A, B, C and so forth

All three criteria are met: the active cell is inside the table, the table’s header row is not visible, and there is at least one table row visible.

The top of your table should look similar to Figure 2-27.



FIGURE 2-27 Vertically scrolled table



Step 4 Click any cell outside the table; observe that the column headers A, B, C and so forth replaces the table heading text

Step 5 Click any cell inside the table; the table header text replaces the column headers

Step 6 Press the CTRL+HOME keys to activate cell A1; observe that the column headers A, B, C and so forth reappear

Sorting and Filtering Records in a Table www.ebook777.com

In Lesson 1 you learned several ways to sort and filter data organized in a data range. You can sort and filter data in a defined table using the same methods you learned to use in Lesson 1 when you worked with data in a range; for example, by:

clicking the sorting and filtering buttons in the Sort & Filter group on the Ribbon DATA tab, or clicking the Sort & Filter button in the Editing group on the HOME tab.

To review sorting using a defined table and the Sort & Filter button in the Editing group on the HOME tab:

Step 1 Click the Format as Table sheet tab to activate the worksheet, if necessary; then click any cell in the Record Number field inside the table

Step 2 Click the HOME tab on the Ribbon, if necessary; then locate the Editing group

Step 3 Click the Sort & Filter button in the Editing group to display the button’s menu

Your Sort & Filter button menu should look similar to Figure 2-28.



FIGURE 2-28 Sort & Filter button menu

Step 4 Click the Sort Largest to Smallest command on the menu; observe that the table is sorted in descending order by the Record Number field

Step 5 Click the Sort & Filter button in the Editing group; then click the Sort Smallest to Largest command on the menu to return the table to its original order

As noted earlier in this lesson, one advantage in defining a table is that Excel automatically displays the Filter arrows in each cell in the header text row—no need to manually display the Filter arrows. Notice the Filter command in the menu in Figure 2-28 is selected indicating the Filter arrows are toggled on.

To toggle the Filter arrows off and on using the Sort & Filter button in the Editing group:

Step 1 Click the Sort & Filter button in the Editing group; then click the Filter command to toggle off the display of the Filter arrows in the header text row

Step 2 Observe that the Filter arrows no longer appear in the table’s header row

Your table’s header row should look similar to Figure 2-29.



FIGURE 2-29 Table’s header row without Filter arrows

Step 3 Click the Sort & Filter button in the Editing group; then click the Filter command to toggle back on the display of the Filter arrows in the header text row

To review filtering using a defined table and the Filter (AutoFilter) feature:

www.ebook777.com

Click the State Filter arrow to display the menu of sorting, searching and filtering options for the State field; this menu is the same as appears when you click a Filter arrow for a field in a data range (see Figure 1-37)

Step 2 Click the (Select All) checkbox to remove all the check marks; then click the North Dakota checkbox to insert a check mark

Step 3 Click OK to filter the table to show only those records for North Dakota

Step 4 Click the Popularity Ranking Filter arrow to display the menu; then filter the North Dakota data to display only those records with a High popularity ranking

Your filtered table should now look similar to Figure 2-30.



FIGURE 2-30 Table filtered to show all the records where the state equals North Dakota and the Popularity Ranking equals High

Step 5 Click the Sort & Filter button in the Editing group; then click the Clear command to clear the filter from both fields at one time



Adding a Total Row to a Table



You can quickly add a Total Row to a table to summarize one of more columns (Fields) by: pointing to the Table command on a shortcut menu and then clicking the Totals Row command on a submenu, clicking the Total Row checkbox in the Table Style Options group on the TABLE TOOLS DESIGN tab to toggle on or off the Total Row, or entering a formula in a cell below the last record (row) and selecting to add the formula to the Total Row.



When you add a Total Row, Excel inserts the word Total in the first cell in the Total Row and calculates the total of the right-most field by default. Excel uses the SUBTOTAL function (you learned about in Lesson 1) for a sum calculation.

To customize the Total Row calculations, click the drop-down list arrow that appears in the active cell in the Total Row to view a list or menu of function options.

Adding a Total Row Using a Shortcut Menu Command

You begin by switching to the QuickAnalysis table on the Quick Analysis Table worksheet. Then you use a shortcut menu command to add a Total Row.

To add a Total Row using a shortcut menu command:

Step 1 Click the Quick Analysis Table sheet tab, if necessary, to activate the worksheet

Step 2 Right-click any cell in the QuickAnalysis table

Step 3 Point to the Table command to display the submenu

www.ebook777.com

Your table and shortcut menu should look similar to Figure 2-31.



FIGURE 2-31 Shortcut menu and Table submenu



Step 4 Click the Totals Row command on the submenu to add a Total Row

Step 5 Scroll to view record 126 in row 130, the last record in the table, if necessary; observe the new Total Row with the word Total in the left-most cell and a calculation in the right-most cell



The bottom of your table with a new Total Row should look similar to Figure 2-32.



FIGURE 2-32 New Total Row



Step 6 Click cell J131, the last cell in the Total Row

Step 7 Observe the formula in the Formula Bar; Excel uses the SUBTOTAL function to count the number of non-blank entries in the field, 126 (one entry for each record)

Customizing the Total Row

Excel’s default calculation in the right-most cell of the Total Row is not especially helpful in this example; what you want to see is the total of the Total Sales column. You can now customize the Total Row by removing the calculation in cell J131 and adding a calculation to cell H131.

To customize the Total Row by removing the formula in cell J131 and inserting a formula in cell H131:

Step 1 Click cell J131 to display the drop-down list arrow, if necessary

Step 2 Click the list arrow to view the list of function options

Your list of function options should look similar to Figure 2-33.

www.ebook777.com



FIGURE 2-33 List of function options





Step 3 Click None in the list to remove the formula from cell J131

Step 4 Click cell H131; then click the drop-down list arrow to view the function options

Step 5 Click Sum in the list; then observe the grand total for Total Sales and the formula in the Formula Bar

Excel again uses the SUBTOTAL function to calculate the sum of Total Sales. Your table’s Total Row should now look similar to Figure 2-34.



FIGURE 2-34 Sum of Total Sales calculation



Toggling the Table’s Total Row Off and On

You can add a Total Row or toggle off or on an existing Total Row by clicking the Total Row checkbox in the Table Style Options group on the TABLE TOOLS DESIGN tab to insert or remove a check mark.

To toggle off and on the Total Row:

Step 1 Click any cell in the table, if necessary, to display the TABLE TOOLS DESIGN contextual tab on the Ribbon

Step 2 Click the TABLE TOOLS DESIGN tab, if necessary; then locate the Table Style Options group

Step 3 Click the Total Row checkbox to remove the check mark; observe that the Total Row is not now visible

Step 4 Click the Total Row checkbox to insert a check mark; observe that the Total Row is again visible

Manually Entering a Formula to Add a Total Row

www.ebook777.com

Another way to add a Total Row to a table is to manually enter a formula in a cell in the blank row below a table’s last row (record). Excel then automatically creates a Total Row to include the formula and gives you two additional options: Place Formula Inside Table and Place Formula Below Table.

To automatically add a Total Row using a manual formula:

Step 1 Click the Format as Table sheet tab, if necessary, to activate the worksheet

Step 2 Scroll to view the bottom of the FormattedTable table; then click cell H131

Step 3 Enter =SUM(H5:H130) in cell H131

Step 4 Observe that Excel automatically inserts the formula and its resulting calculation in a new Total Row and presents the AutoCorrect Options icon with the default Place Formula in Table Total Row selected (click the AutoCorrect Options icon, if necessary, to display the menu)

Step 5 Observe that the word Total is not automatically inserted in the left-most cell (A131) in the new Total Row

The bottom of your table should look similar to Figure 2-35.



FIGURE 2-35 New Total Row with sum of Total Sales calculation



Step 6 Press the ESC key to clear the AutoCorrect Options menu

Converting a Table to a Data Range

When you no longer need or want to use the special features offered by defining well-organized data as a table, you can convert the table to a simple data range by clicking the Convert to Range command on the Table submenu on the shortcut menu.

To convert a table to a data range:

Step 1 Click the Insert Table sheet tab, if necessary, to activate the worksheet

Step 2

www.ebook777.com



Step 3 Point to the Table command; then click the Convert to Range command on the shortcut menu

Step 4 Click Yes in the warning dialog box; observe that the data is no longer in a defined table

Step 5 Save and close the workbook

In Lesson 2 you learned some advantages of placing your well-organized data into a defined table and learned multiple ways to create a defined table. Then you learned how to enter data in a table, resize a table, select table elements, change table formatting, sort and filter table data, add a Total Row to a table and convert a table to a simple data range.



Glossary Add Level button: a button in the Sort dialog box used to add another level of sort criteria Advanced Filter: manually created set of filter criteria entered in cells above and to the right of the data range; used for complex filtering Auto Outline command: a command on the Group button menu that allows Excel to group data by both rows and columns calculated column: a column in a table in which you enter a formula Clear button: a button in the Data Form dialog box used to clear search criteria column specifier: column labels in the header row of a defined table Copy Level button: a button in the Sort dialog box used to copy the previous level of sort criteria to the next level Criteria button: a button in the Data Form dialog box you can click to display the Criteria Data Form used to search the data range Criteria indicator: an indicator in the Data Form dialog box that shows you are performing a search of records in a data range based on specified criteria criteria range: an area generally above and to the right of a data range in which Advanced Filter criteria are entered custom filter: complex filter criteria you specify in the Custom AutoFilter dialog box custom sort order: a list of sort criteria you create in the Advanced section of the Excel Options dialog box Data Form: a dialog box you can use to enter, edit or delete a record (row) of data in a data range Delete button: a button in the Data Form dialog box you can click to delete the current record Delete Level button: a button in the Sort dialog box used to remove a level of sort criteria field: the same type of data entered in a column for all records in a data range filter: viewing only those records that meet specified criteria Filter (AutoFilter): filter arrows that appear in each cell in the header row when the Filter (AutoFilter) feature is turned on; clicking a filter arrow allows you to select filter criteria filter criteria: identified criteria for filtering a data range Find Next button: a button in the Data Form dialog box you can click to view the

www.ebook777.com

Find Prev button: a button in the Data Form dialog box you can click to view the previous record Form button (Data Form): a button in the Data Form dialog box used to return to the standard data entry Data Form Form button (Quick Access Toolbar): a button or icon added to the QAT in order to access the Data Form in Excel 2013 Format as Table button: a button in the Styles group on the HOME tab you can click to format selected data as a defined table Group command: a command on the Group button menu that launches the Group dialog box Group or Ungroup buttons: buttons in the Outline group on the DATA tab you can click to outline a data range by rows and/or columns header row: the first row of a range containing column label text hide detail symbol: a minus sign symbol you can click to hide detail in an outlined worksheet Information error alert: data validation error alert that displays icons and messages but allow the user to enter invalid data and continue to the next cell input message: data entry instructions in a validation rule My data has headers checkbox: a checkbox in the Sort dialog box used to toggle on or off the presence of a header row nested subtotals: subtotals for a secondary group of records within a larger, primary group New button: a button in the Data Form dialog box you can click to view a blank form New Record indicator: an indicator in the Data Form that identifies a new record being added to the data range Options button: a button in the Sort dialog box used to specify sorting by records or by fields outline level symbols: symbols you can click to expand or collapse a worksheet outline Paste Special command: a command on the Paste button menu in the Clipboard group on the HOME tab or on a shortcut menu; clicking the Paste Special command launches the Paste Special dialog box Paste Special dialog box: a dialog box that contains a variety of useful paste options, for example, the option to paste Validation Rules into selected cells

Quick Analysis button: a button that appears below and to the right of selected data and provides access to formatting, charting, subtotaling features as well as features to define a table record: a row of data in a data range; contains all the related data fields Restore button: a button in the Data Form dialog box used to restore cleared search criteria semi-selection process: the process of selecting a cell or range as you build a formula instead of keying the cell or range references in the formula show detail symbol: a plus sign symbol you can click to show detail in an outlined worksheet sort: to rearrange records in a data range based on specific criteria Sort & Filter button: a button in the Editing group on the HOME tab you can click to set sort criteria for a data range Sort A to Z, Sort Smallest to Largest, Sort Z to A and Sort Largest to Smallest: sorting buttons located in the Sort & Filter group on the DATA tab Stop error alert: data validation error alert that requires the user to enter valid data before continuing to the next cell structured reference: table and/or field names used in formulas instead of cell references Subtotal feature: allows you to insert temporary subtotals and a Grand Total in a data range SUBTOTAL function: a function that uses numbers from 1-11 and 101-111 to indicate which function (AVERAGE, MAX, MIN, COUNT or SUM) is to be used in a subtotal calculation table: a well-organized range of data defined as an Excel table in order to more quickly filter the data and access useful Table features Table button: a button in the Tables group on the INSERT tab you can click to define a selected data range as a table validation rules: data entry rules allowing control over the type of data entered in cells Warning! error alert: data validation error alert that displays icons and messages but allow the user to enter invalid data and continue to the next cell wildcard symbols: symbols used in a search, such as ?, * and the ~

www.ebook777.com