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.
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.
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.
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
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.