Working with Data


Whenever you're working with a lot of data, it can be difficult to compare information in your workbook.

7.1 Freezing Panes and View Options

Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, such as the ability to freeze panes and split your worksheet.

To freeze rows

You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you'll be able to scroll through your content while continuing to view the frozen cells.
  1. Select the row below the row(s) you wish to freeze.
  2. Click the View tab on the Ribbon.
  3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.  
The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top.

To freeze columns

  1. Select the column to the right of the column(s) you wish to freeze.
  2. Click the View tab on the Ribbon.
  3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.
  4. The column will be frozen in place, as indicated by the gray line. You can scroll across the worksheet while continuing to view the frozen column on the left.
To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.

To split a worksheet

Sometimes you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately.
  1. Select the cell where you wish to split the worksheet.
  2. Click the View tab on the Ribbon, then select the Split command.  
  3. The workbook will be split into different panes. You can scroll through each pane separately using the scroll bars, allowing you to compare different sections of the workbook.
To remove the split, click the Split command again.

Challenge!

  1. Open an existing Excel workbook.
  2. Try freezing a row or column in place.
  3. Use the Split command to split your worksheet into multiple panes.

7.2 Sorting Data

As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.

When sorting data, it's important to first decide if you would like the sort to apply to the entire worksheet or just a cell range.
  • Sort sheet organizes all of the data in your worksheet by one column.
  • Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet.

To sort a sheet

In our example, we'll sort a T-shirt order form alphabetically by Last Name (column C).
  1. Select a cell in the column you wish to sort by. In our example, we'll select cell C2. 
  2. Select the Data tab on the Ribbon, then click the Ascending command to Sort A to Z, or the Descending command to Sort Z to A. In our example, we'll click the Ascending command.  
  3. The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name. 

7.3 Filtering Data

If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.

To filter data

  1. In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column.
  2. Select the Data tab, then click the Filter command.  
  3. A drop-down arrow 
         will appear in the header cell for each column.
  4. Click the drop-down arrow for the column you wish to filter.
  5. The Filter menu will appear.
  6. Uncheck the box next to Select All to quickly deselect all data.
  7. Check the boxes next to the data you wish to filter, then click OK.
To remove all filters from your worksheet, click the Filter command on the Data tab.
Subscribe to: Posts (Atom)