is the process that enables you to keep headings on the screen as you work with large worksheets, rows and columns.
To do this to rows and columns:
1. Select the cell below the row(s) and to the right of the column(s) you want to freeze.
2. Click the View tab and click Freeze Panes in the Window Group.
is the process of making rows, columns, and sheets invisible.
To do this to a particular row or column in a worksheet:
1. Select the row or column you want to hide.
2. Click the Home Tab
3. Click Format in the Cells group and point to Hide & Unhide.
4.Click Format in the Cells group and point to Hide & Unhide.
4. Select the appropriate hide option.
To allow data to be entered in this:
1. Select the cells where entering or changing data will be allowed.
2. Click the Home Tab, click Format in the Cells group, and select Format Cells to open the Format Cells dialog box.
3. Select the Protection tab, clear the Locked check box, click OK.
4. Click the Home tab, click Format in the Cells group, and click Protect Sheet.
5. Select a password if desired and clear the Select locked cells check box.
some cells but not all
To do this to a worksheet:
1. Click the Home tab and click Format in the Cells group.
2. Click Protect Sheet.
3. Select a password if desired and click the options that users will be permitted in the worksheet.
4. Click OK.
To protect this:
1. Click the Review tab and click Protect Workbook in the Changes group.
2. Click the boxes for the protection desired.
3. Enter a password if desired in the Protect Workbook dialog box.
4. Click OK.
All dependent formulas recalculate every time a change is made to a value, formula, or name. Click this under Calculation Options in the Calculation group.
All dependent formulas recalculate except data tables, every time a change is made to a value, formula, or name. Click this under Calculate Options in the Calculation group.
Automatic Except for Data Tables
Turn off automatic recalculation and recalculate open workbooks only when desired. Click this under calculate options in the Calculation group.
manually recalculate. Click this in the calculation group on the Formulas tab, or press F9.
shows where page breaks occur and gives you the opportunity to change where the page breaks.
Page Break preview
To see and adjust this:
1. Click Page Break Preview on the status bar.
2. A watermark shows the page numbers.
3. Move the dashed blue lines as appropriate to adjust the page breaks.
To change this:
1. Click Orientation in the Page Setup group on the Page Layout tab.
2. Select Portrait or Landscape
Print this of a worksheet:
1. Select the portion of the worksheet you want to print.
2. Click the Page Layout Tab, and then click Page Setup Dialog Box Launcher in the Page Setup Group.
3. Click Print, and then click Selection in the Print what section.
4. Verify the selection using Preview.
5. Click Print in the Print group on the Print Preview tab.
selection or range
To change this of the printing pages:
1. Click the Page Setup Dialog Box Launcher on the Page Layout tab.
2. Click the Sheet tab.
3. Change the Page order options, as appropriate.
4. Click OK
Known as a list in previous versions of Excel; is an area in the worksheet that contains rows and columns of related data organized in such a way to facilitate data management and analysis.
filter drop-down lists for efficient sorting and filtering, predefined table styles to format table rows and columns with complementary fill colors, ability to create and edit calculated columns, calculated total row enabling the user to choose from a variety of functions, use of structured references instead of cell references in formulas, ability to export the table data to a SharePoint list.
To create without data:
1. Select a range of cells on a sheet.
2. Click the Insert tab and click Table in the Tables group. The Create Table dialog box opens, asking for the range of data for the table. If the table has (or will have) field names at the top of the columns, select the My table has headers check box.
3. Click OK
to create from already existing data:
1. Select the range of cells on the sheet that contains the data.
2. Click the Insert tab and click Table in the Tables group. The Create Table dialog box opens.
3. Click OK
some of these options, such as cell height and width, are available in the Format down arrow in the Cells group on the home tab.
this contextual tab provides a variety of formatting options for tables.
Table Tools Design
Table Style Option, turns on or off the header or top row of a table.
Table Style Option, turns on or off the totals or last row of a table.
Table Style Options, shows special formatting for the first column of a table.
Table Style Options, shows special formatting for the last column of a table.
Table Style Options, displays banded rows where even rows are differently formatted than odd rows.
Table Styles Options, displays banded columns where even columns are differently formatted than odd columns.
arranges records in a table by the value of one or more fields within a table.
puts lists in ascending or descending order according to specified sort fields.
You can do this for both ranges and table data:
Click Sort & Filter in the Editing group on the Home tab.
Click Sort A to Z, Sort Z to A, and Sort in the Sort & Filter group on the Data tab.
Right-click the field to sort, select Sort from the shortcut menu, and select the type of sort you want.
To perform this:
1. Click in any cell in the table
2. Click Sort in the Sort & Filter group on the Data tab. This opens the Sort dialog box.
3. Choose the primary sort level from the Sort by drop-down list, and then select the sort order from the Order drop-down list
4. Click the Add Level button and choose the second sort level from the Then by drop-down list, and then select the sort order from the Order drop-down list.
5. Continue to click the Add Level button and add sort levels until you have entered all desired sorts.
multiple level sort
refer to facts about a specific record or sets of records.
is data that have been arranged in some form and are viewed as useful.
You can use AutoFilter to set a _____ to display a subset of data from a table.
is a condition that displays a subset of data meeting your specifications.
If the header row with filter drop-down arrows is not visible or if you are filtering a range of data instead of a table:
1. Select the data table.
2. Click Sort & Filter in the Editing group on the Home tab.
3. Select Filter to show the filter arrows in each column header.
To insert these totals:
1. Ensure a cell in the table is selected so the Table Tools Design tab is available.
2. Click the Total Row check box in the Table Style Options group on the Design tab. Excel inserts a total row and totals the last column using the SUBTOTAL function.
3. Click in the total cell to see a drop-down arrow.
4. Click the drop-down arrow to select another function, such as Average, or select None to remove the total if it is not relevant to that column.
5. Click in other cells on the total row and repeat step 4 to apply totals to other columns.
To create this:
1. To make sure the data are a list and not a table, Click Convert to Range in the Tools group on the Design tab as necessary, and click Yes in the message box.
2. Click in the range of data, and then click Subtotal in the Outline group on the Data tab.
3. Select the appropriate options in the Subtotal dialog box, and click OK.
To create this:
1. Select data fields to be charted.
2. Click the Insert tab and select the type of chart appropriate for the data.
3. Format and move the chart as appropriate.