Topics 0 and 1: Excel Shortcuts, References, Named Ranges, Data Validation, and Spill

Cut

Ctrl + X

Copy

Ctrl + C

Paste

Ctrl + V

Undo

Ctrl + Z

Redo

Ctrl + Y

What key combination allows you to quickly highlight entire rows and/or columns of data without including all blank rows/columns below and to the right of your data?

Ctrl + Shift + Up/Down/Left/Right Arrow

What key combination allows you to add or delete a row or column without having to use the mouse?

Ctrl + or Ctrl -

Which key allows you to get into a cell to edit it (formula or content)

F2

Which key is the toggle key for static, dynamic, and hybrid cell references in a formula?

F4

The Small Green Box in the lower right corner of a cell allows you to copy the contents of the cell (including formulas) down and/or across columns and rows. This only works when…

There is data in the column to the left (i.e., the column to the left is not blank)

Which key combination allows you to duplicate whatever is in the cell directly above the cell you are in (you can use this to fill one or more cells at once)?

Ctrl + D

What does Paste Special — Transpose do?

Pastes a column as a row or paste a row as a column

How to use Paste Special — Transpose

• Copy the data that is to be used

• Right click in the destination cell and click “Paste Special”

• Click the icon with two arrows on it

What does Paste Special — Paste Values Only do?

Pastes the values without copying any of the formatting or formulas from the source cells

How to use Paste Special — Paste Values Only

• Copy the data that is to be used

• Right click in the destination cell and click “Paste Special”

• Click on the clipboard with the 1-2-3 digits at the bottom of it

Sort & Filter

Provides several opportunities to display data based on criteria you select

Where can you find Sort & Filter?

Under the Home tab on the top menu bar, under “Editing”

When you do not want to make a cell any wider but want to see all the text you’ve placed in it, you can select…

Wrap Text

Where can you find Wrap Text?

Under the Home tab on the top menu bar, under “Alignment”

Page Break Preview

• Allows you to see how your Excel worksheet will print

• Allows you to adjust the blue lines to fit content on to pages as you see fit

Where can you find Page Break Preview?

Click on the “View” tab on the top menu bar

Freeze Panes

• Keeps rows and columns visible while the rest of the worksheet scrolls

• Applying this to the top row or column will always be visible to you as you scroll down or across on the Excel worksheet

Which shortcut:

• Allows you to quickly create a list of unique values in your dataset

• Could be especially useful when creating a dropdown box

Remove Duplicates (from a column)

Which key combination allows you to instantly create a SUM function?

ALT + =

What is the shortcut to convert a selected range into a Table?

Ctrl + T

Which key combination allows you to create a line break within a cell so that you can start typing on the next line within a cell?

Alt + Enter

Which key combination allows you to toggle column filters on and off?

Ctrl + Shift + L

Types of Cell References

• Relative

• Absolute

• “Hybrid”

Relative Cell Reference | Definition and Example

• Cell references that automatically change when you drag formulas to new cells

• A4

Absolute Cell References

• Cell references do NOT change when you drag the formula to new cells

• $A$4

“Hybrid” Cell References

• Part of the cell reference changes and part of it does not when you drag formula to new cells

• A$4 or $A4

Named Range

• A group of cells (or single cell) that is referred to by a name you assign it

• Makes creating formulas easier (and decreases error likelihood) as you can reference it instead of highlighting the entire block of cells you are referring to (or manually typing it out)

Where to go in Excel to name a range

Go to the Formulas Tab and find “Name Manager” in the “Defined Names” section

Spill

A behavior where formulas that return multiple results "spill" these results into multiple cells automatically

A #SPILL! error happens when…

Something on the worksheet blocks a spilled array formulas

Spill Range

TDhe range of values returned by an array formula that spills results onto a worksheet

Data Validation

A feature that controls what values can be entered into a cell

Say that a column has people’s first and last names in its cells. How can you separate the first and last names without manually making another column and retyping names?

Use the Text to Columns feature

A dropdown list that restricts users to specified choices can be created through…

Data Validation

How do you create a dropdown list using Data Validation?

• Select the Cell

• Data Validation

• Choose List

• Specify the Source of List

Why use a named range with a Data Validation dropdown?

It allows the dropdown source to reference a meaningful range name instead of manually entering cell references

UNIQUE Function

Returns a list containing only the unique values from a range, removing duplicates

How can UNIQUE and Data Validation work together?

Unique can create a duplicate-free list that can then be used as the source for a Data Validation dropdown