Excel notes 17,18,19,20

Freezing rows and columns

- allows you to *keep headings visible* as you work with the data in a large worksheet - to perform freezing, select the cell immediately below the row(s) and to the right of the column(s) you want to free. click the "view tab" on the ribbon. then in "window group", click the "freeze panes" button

To apply multiple filters

Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date. The Filter menu will appear. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we'll uncheck everything except for August. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.

To clear a filter

Click the drop-down arrow for the filter you want to clear. In our example, we'll clear the filter in column D. The Filter menu will appear. Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out". The filter will be cleared from the column. The previously hidden data will be displayed.

Types of sorting

Sort sheet organizes all of the data in your worksheet by one column. Related information across each row is kept together when the sort is applied. In the example below, the Contact Name column (column A) has been sorted to display the names in alphabetical order. 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.

Using Find and Replace

When working with a lot of data, it can be difficult and time consuming to locate specific information.

Adding conditional formatting

allows you to automatically apply cell formatting—such as colors, icons, and data bars—to one or more cells based on the cell value.

Summarizing data

allows you to quickly summarize your data. In our example, we've created a subtotal for each T-shirt size, which makes it easy to see how many we'll need in each size.

Filtering data

can be used to narrow down the data in your worksheet, allowing you to view only the information you need

Sorting data

can quickly reorganize a worksheet by sorting your data. Content can be sorted alphabetically, numerically, and in many other ways.

To unfreeze panes

if you want to select a different view option, you may first need to reset the spreadsheet by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.

To filter data

In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail, and so on.Select the Data tab, then click the Filter command.A drop-down arrow will appear in the header cell for each column.Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.The Filter menu will appear. Uncheck the box next to Select All to quickly deselect all data.Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment.The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and projectors are visible.

Visualizing data with charts

It can be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and trends.

Formatting data as a table

Just like regular formatting, tables can improve the look and feel of your workbook, but they'll also help you organize your content and make your data easier to use.

To sort a sheet

Select a cell in the column you want to sort by Select the Data tab on the Ribbon, then click the A-Z command to sort A to Z, or the Z-A command to sort Z to A.The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name.

To sort a range

Select the cell range you want to sort. In our example, we'll select cell range G2:H6. Select the Data tab on the Ribbon, then click the Sort command. The Sort dialog box will appear. Choose the column you want to sort by. In our example, we want to sort the data by the number of T-shirt orders, so we'll select Orders. Decide the sorting order (either ascending or descending). In our example, we'll use Largest to Smallest. Once you're satisfied with your selection, click OK. The cell range will be sorted by the selected column. In our example, the Orders column will be sorted from highest to lowest. Notice that the other content in the worksheet was not affected by the sort.

To freeze columns

Select the column to the right of the column(s) you want to freeze. In our example, we want to freeze column A, so we'll select column B. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. 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. In our example, we've scrolled across to column E.

To freeze rows

Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we'll select row 3. On the View tab, 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. In our example, we've scrolled down to row 18.