EXCEL – CHAPTER 3 NOTES PART A Criteria – the type of data you want to sort by Sort & Filter button on the Home tab in the Editing group Two ways to sort data: 

Quick sort – for one type of data 1. Place active cell in column to sort by 2. Use Sort A-Z or Sort Z-A buttons from the Sort & Filter button



Multi-level sort – for two or more types of criteria 1. Place active cell anywhere in list (Excel will select list) 2. Open the Sort dialog box (Custom Sort option, Sort & Filter button) 3. Add and delete levels based on how many criteria needed to sort by (order of levels is the order of sorting) 4. Choose with the header row or with no header row

PART B Drag & drop – selecting a range of cells, dragging them to a different area and dropping them there (use the arrow and drag the border) Drag & drop to copy – use the control key while dragging (get arrow pointer with + sign)

PART C Rename a worksheet 

Right-click the worksheet tab, select Rename



Double-click the worksheet tab

Insert a worksheet 

Right-click worksheet tab and choose Insert



Worksheet will be entered to the left of the worksheet tab you right-clicked

Move a worksheet 

Drag the worksheet tab you want to move to desired location

Copy a worksheet 

Hold down the control key and click and drag the worksheet to copy to desired location

Delete a worksheet 

Right-click the worksheet tab and choose Delete



Delete button on the Home tab in the Cells group

Tab scrolling buttons – used to help you navigate when you can’t see all of the worksheet tabs

PART D Cut – removes data from a worksheet 

Right-click the text, Cut



Cut button on the Home tab in the Clipboard group



CTRL + X



Data is not removed until: a.

the text is pasted

b.

the text is deleted

c.

the escape key is used to cancel the cut action

Copy – make a duplicate of data from a worksheet 

Right-click the text, Copy



Copy button on the Home tab in the Clipboard group



CTRL + C

Paste – to replace cut or copied text 

Right-click the text, Paste



Paste button on the Home tab in the Clipboard group



CTRL + V



Click the icon on the clipboard toolbar

PART E Inserting cells 

Right-click cell, choose Insert



Insert button on the Home tab in the Cells group



Choose how to shift cells to the right or down

Inserting rows 

Right-click row heading, choose Insert



Insert button on the Home tab in the Cells group



Rows are inserted above the active row

Inserting columns 

Right-click column heading, choose Insert



Insert button on the Home tab in the Cells group



Columns are inserted to the leftof the active column

Deleting rows 

Right-click the row heading, choose Delete



Select row, delete button on the Home tab in the Cells group

Deleting columns 

Right-click the column heading, choose Delete



Select column, delete button on the Home tab in the Cells group

AutoComplete – automatically fills in the rest of word for you 

To accept the AutoComplete entry – press the enter key



To ignore the AutoComplete entry – continue typing

PART F REMEMBER: All formulas (with cell references) will automatically recalculate their results based on changes made Three types of cell references:  Relative cell reference – a cell reference that changes relative to the cell from which the formula is being copied Examples:  Absolute cell reference – a cell reference that always refers to a specific cell; use of $ (dollar sign) before both the column letter and row number Examples:  Mixed cell reference – a cell reference that always refers to a specific row OR column; use of $ before the column letter OR the row number Examples: If the cursor is in the cell reference in the formula bar, using the F4 key makes the cell reference into an absolute cell reference

PART G Named range - a meaningful name to cells and ranges making it easier to refer to them To create a named range: 1. Select range of cells 2. Go to the Name box and type name and press enter OR 1. Select range of cells 2. Go to the Define Name button on the Formula tab in the Defined Names group OR 1. Select range of cells 2. Go to the Name Manager button on the Formula tab in the Defined Names group

To delete a named range: 1.

Click the Name Manager button on the Formula tab in the Defined Names group

2.

Select range to delete and use the delete button

To modify (change) a named range: 1.

Click the Name Manager button on the Formula tab in the Defined Names group

2.

Select range to edit and use the less/expand buttons to reselect range

Can use named ranges in formulas: 

Type formula – when you get to the part where you need the named range; go to the Use in Formula button on the Formula tab in the Defined Names group and select the named range needed



You can also type the named range EXACTLY as it was named

PART H Freeze panes command - used to retain the column and row headings when scrolling through a worksheet Freezes the row above and the column to the left of the active cell Freeze Panes button on the View tab in the Window group

PART I Outlines – allows you to view data in hierarchies or levels 

Points are specified to where subtotals should be calculated (subtotal function is used)



Grand total at end is also calculated from the subtotals

Detail data – the information appearing above each subtotal 

Set of outline symbols appears on the left side of a worksheet



Outline symbols are used to display/hide data

Can outline up to 8 levels To create an outline: 1. Click the Subtotal button on the Data tab in the Outline group 2. Specify “At each change in” – this creates a subtotal each time there is something new in this column 3. Use the level buttons to see/hide data according to the different levels

PART J Two ways to check spelling 1. Spelling button located on the Review tab in the Proofing group 2. F7