facebook google plus twitter
Webucator's Free Intermediate Microsoft Excel 2013 Tutorial

Lesson: Working with Lists

Welcome to our free Intermediate Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2013 Training course.

In this lesson, you'll learn to work with lists.

Lesson Goals

  • Convert data into tables.
  • Remove duplicates from tables.
  • Sort data in Excel.
  • Filter data in Excel.
  • Use subtotals to automatically total related data.
  • Group and ungroup data.

Rows and Columns as Lists

Microsoft Excel treats all data in successive rows and columns as a list. For example, following is a list with three columns and seven rows:List

The image below includes three lists, two with four rows and three columns and one with nine rows and two columns:Three Lists

Converting a List to a Table

Converting a list to a table makes it very easy to format your data and to keep the formatting consistent as you make changes to the data. To convert a list to a table:

  1. Select a cell within the list you wish to convert to a table.
  2. On the INSERT tab, in the Tables group, click the Table command:Table Command
  3. In the Create Table dialog box, verify that Excel has correctly guessed the correct data range, check My table has headers if your table does have headers, and click OK:Create Table Dialog Box

Converting a List to a Table

Duration: 5 to 10 minutes.

In this exercise, you will practice converting a list to a table.

  1. Open List to Table.xlsx from your Excel2013.2/Exercises folder.
  2. In Sheet1, convert the list of houses to a table.
  3. In Sheet2, convert the data into a table. Be sure to include all data in the worksheet in the table.

Solution:

  1. In Sheet1, convert the list of houses to a table.
    1. Select any cell within the list.
    2. On the INSERT tab, in the Tables group, click the Table command:Table Command
    3. In the Create Table dialog box:
      1. Verify that Excel has correctly guessed the correct data range ($A$1:$F$133).
      2. Check My table has headers if it isn't already checked.
      3. Click OK.
  2. In Sheet2, convert the data into a table. Be sure to include all data in the worksheet in the table.
    1. Delete the blank row (row 5).
    2. Select any cell within the list.
    3. On the INSERT tab, in the Tables group, click the Table command:Table Command
    4. In the Create Table dialog box:
      1. Verify that Excel has correctly guessed the correct data range ($A$1:$C$8).
      2. Check My table has headers if it isn't already checked.
      3. Click OK.

Removing Duplicates from a List

Excel makes it easy to remove duplicates from a list, which can be really helpful when working with long lists. To remove duplicates from a list:

  1. Select a cell within the list that you wish to remove duplicates from.
  2. On the DATA tab, in the Data Tools group, click the Remove Duplicates command:Remove Duplicates Command
  3. In the Remove Duplicates dialog box:
    1. Depending on whether your list has headers, check or uncheck My data has headers.
    2. Check those columns that contain duplicates you wish to remove.
    3. Click OK.OK
  4. Click OK in the dialog box that appears telling you how many values were found and removed, and how many values remain.

Removing Duplicates from a List

Duration: 5 to 10 minutes.

In this exercise, you will practice removing the duplicates from a list.

  1. Open Remove Duplicates.xlsx from your Excel2013.2/Exercises folder.
  2. The list in this worksheet shows all sales by customer. You would like to see a list showing each customer just once. Use the Remove Duplicates command to create this list.

Solution:

  1. Select any cell within the list.
  2. On the DATA tab, in the Data Tools group, click the Remove Duplicates command:Remove Duplicates Command
  3. In the Remove Duplicates dialog box:
    1. Verify that My data has headers is checked.
    2. Under Columns, make sure Customer is checked and Sale is not checked.
    3. Click OK.OK Command
  4. Click OK in the dialog box that appears telling you how many values were found and removed, and how many values remain:OK in Dialog Box

Sorting Data in a List

Sorting data allows you to see all related data in successive rows, which is both useful in and of itself and is necessary if you wish to add subtotals to your data. To sort data in a list in Microsoft Excel:

  1. Select any cell within the list you wish to sort.
  2. On the DATA tab, in the Sort & Filter group, click the Sort command:Sort Command
  3. In the Sort dialog box:
    1. Verify that My data has headers is checked if your list has a header row and not checked if it doesn't.
    2. Choose what column to sort by.
    3. Choose what to sort on. The default is to sort on Values, but you can also choose to sort on Cell Color, Font Color, and Cell Icon.
    4. Choose whether to sort ascending or descending.
    5. You can also:
      1. Add a level, meaning sort by one thing and then again by a second thing (click Add Level). Clicking Add Level adds a second set of Column, Sort On, and Order boxes to the Sort dialog box.
      2. Sort left to right instead of top to bottom (click on Options).
    6. Click OK. OK Option

Sorting Data in a List

Duration: 10 to 20 minutes.

In this exercise, you will practice sorting data in a list.

  1. Open Sort.xlsx from your Excel2013.2/Exercises folder.
  2. Sort the list by Town in ascending order.
  3. Further sort the list by Square Feet in descending order.

Solution:

  1. Select any cell in the list.
  2. On the DATA tab, in the Sort & Filter group, click the Sort command:Sort Command
  3. In the Sort dialog box:
    1. Verify that My data has headers is checked.
    2. Under Column, select to sort by Town.
    3. Under Sort On, accept the default, which is Values.
    4. Under Order, select A to Z to sort ascending.
    5. Click Add Level and then:
      1. Under Column, select to sort by Square Feet.
      2. Under Sort On, accept the default, which is Values.
      3. Under Order, select Largest to Smallest to sort descending.
    6. Click OK. OK Option

Filtering Data in a List

Filtering data makes it easy to look at subsets of your data. To filter data in a list in Microsoft Excel:

  1. Select any cell in the list.
  2. On the DATA tab, in the Sort & Filter group, click the Filter command:Filter Command
  3. Note that every heading now includes a drop-down arrow:Headings with Drop-Down Arrows
  4. Click any of the drop-down arrows to see a list of all individual items in that column. By default all are selected, but you can de-select all or any using the check boxes. Click OK to see only the selected records:Selected Records
  5. After setting the filter in one column, you can further filter your data by setting the filter in another column.
  6. To remove your filters, simply click the Filter command again:Filter Command

Advanced Filtering

You can also use number filters, text filters, or color filters to filter based on specified criteria. To apply filters based on specific criteria:

  1. These filters are also available from the filter drop-downs, so on the DATA tab, in the Sort & Filter group, click the Filter command:Filter Command
  2. Click any of the drop-down arrows and move your mouse over Number Filters, Text Filters, or Color Filters to see the list of options available.Available Options
  3. Select any of the options to open the Custom AutoFilter dialog box, in which you set your criteria. Then click OK:Custom AutoFilter Dialog Box

Filtering Data in a List

Duration: 10 to 20 minutes.

In this exercise, you will practice filtering data in a list.

  1. Open Filter.xlsx from your Excel2013.2/Exercises folder.
  2. On Sheet1, filter the list to show only houses in Fayetteville with 3 bathrooms.
  3. On Sheet2, filter the list to show only houses costing less than $100,000.
  4. On Sheet3, filter the list to show only houses in Jamesville that have 4 bathrooms and cost less than $100,000.

Solution:

  1. On Sheet1, filter the list to show only houses in Fayetteville with 3 bathrooms.
    1. Select any cell in the list.
    2. On the DATA tab, in the Sort & Filter group, click the Filter command:Filter Command
    3. Click the drop-down arrow next to "Town" and de-select everything except Fayetteville, and click OK:De-Selected
    4. Click the drop-down arrow next to "Bathrooms" and de-select everything except 3, and click OK:De-Selected Options
  2. On Sheet2, filter the list to show only houses costing less than $100,000.
    1. Select any cell in the list.
    2. On the DATA tab, in the Sort & Filter group, click the Filter command:Filter Command
    3. Click the drop-down arrow next to "Listing Price," move your mouse over Number Filters, and select Less Than:Less Than Option
    4. Fill in "100000" and click OK:100000
  3. On Sheet3, filter the list to show only houses in Jamesville that have 4 bathrooms and cost less than $100,000.
    1. Select any cell in the list.
    2. On the DATA tab, in the Sort & Filter group, click the Filter command:Filter Command
    3. Click the drop-down arrow next to "Listing Price", move your mouse over Number Filters, and select Less Than:Less Than Option
    4. Fill in "100000" and click OK:100000
    5. Click the drop-down arrow next to "Town" and de-select everything except Jamesville, and click OK:De-Selected Options
    6. Click the drop-down arrow next to "Bathrooms" and de-select everything except 4, and click OK:De-Selected Options

Adding Subtotals to a List

It is often desirable to add subtotals to your data. Fortunately, Excel can do this for you, including:

  1. Adding the rows on which the subtotals will appear.
  2. Adding the subtotals themselves.
  3. Bolding the subtotals so they stand out.
  4. Grouping your data to make it easy to show only the subtotals and totals.

To add subtotals to a list in Microsoft Excel:

  1. Sort your data by the item(s) for which you wish to get subtotals.
  2. Select any cell in the list.
  3. On the DATA tab, in the Outline group, click the Subtotal command:Subtotal Command
  4. In the Subtotal dialog box:
    1. Choose what you want to acquire subtotals for in the At each change in box.
    2. Choose the function you want to use in the Use function box. The most common function to use for subtotals is Sum, but you can use Count, Average, Min, Max, and other functions.
    3. Choose what you want to add subtotals to. This is the column in which the function (Sum, Count, Average, etc.) will be applied.
    4. Click OK.Subtotals

Grouping and Ungrouping Data in a List

Again, when you add subtotals, Excel groups your data so you can collapse and uncollapse it to show all the data, just subtotals and totals, or just totals.

For example:

  1. The following data includes subtotals and is currently set to show all data:Show All Data
  2. The following image shows the same data in the same worksheet, but set (by clicking 2 in the upper left corner) to show only subtotals and totals:Subtotals and Totals
  3. And the following image again shows the same data, but set (by clicking 1 in the upper left corner) to show only totals:Show Only Totals

Adding subtotals is one way to group data in Excel, but you can easily group data in any list. To group data in Excel:

  1. Select the rows or columns you wish to group.
  2. On the DATA tab, in the Outline group, click the Group command:Group Command
  3. In the Group dialog box, select Rows or Columns and click OK:Rows and Columns Options

To ungroup data in Excel:

  1. Select the rows or columns you wish to ungroup.
  2. On the DATA tab, in the Outline group, click the Ungroup command:Ungroup Command
  3. In the Group dialog box, select Rows or Columns and click OK:Rows and Columns Command

Adding Subtotals to a List

Duration: 10 to 20 minutes.

In this exercise, you will practice adding subtotals to a list in Microsoft Excel.

  1. Open Subtotals.xlsx from your Excel2013.2/Exercises folder.
  2. Add subtotals showing total sales by customer.

Solution:

  1. In order to add subtotals showing total sales by customer, it is necessary to first sort by customer. To sort by customer:
    1. Select any cell within the list.
    2. On the DATA tab, in the Sort & Filter group, click the Sort command:Sort Command
    3. In the Sort dialog box:
      1. Verify that My data has headers is checked.
      2. Choose to sort by Customer.
      3. Click OK. Sort by Customer
  2. On the DATA tab, in the Outline group, click the Subtotal command:Subtotal Command
  3. In the Subtotal dialog box:
    1. In the At each change in box, select Customer.
    2. In the Use function box, choose Sum.
    3. In the Add subtotal to box, check Sale.
    4. Click OK.Subtotal Dialog Box