Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
59 Cards in this Set
- Front
- Back
Change worksheet tab color |
Right-click tab and select color |
|
Office theme |
File -> Options or ALT + F + T Choices: light grey dark grey |
|
Office background |
File -> Options or ALT + F + T |
|
Document themes |
Page layout tab - Themes group choose predefined or select a color, font, effect |
|
Add background |
Page layout tab -> Backgrounds in Page Setup group Add from file or Bing image search lowest layer on worksheet |
|
Watermarks |
Page layout tab -> Page Setup dialog box -> header/footer -> custom header -> choose left, center, right -> click Insert Picture button -> choose from file or Bing image search View via View tab -> page layout view and when printed delete via View tab -> page layout view |
|
Format cells dialog box |
CTRL + 1 tabs: number format alignment (text is left align by default) Font Border Fill Protection |
|
Indent cells |
Select cells -> CTRL + 1 -> Alignment tab -> Indent box |
|
Align cells - Horizontal |
select cells -> CTRL + 1 -> Alignment tab -> horizontal General Left indent Center Right indent Fill Justify Center across selection Distributed (indent) |
|
Align cells - Vertical |
select cells -> CTRL + 1 -> Alignment tab -> vertical Top Center Bottom Justify Distributed |
|
Merge cells |
Select cells -> Home Tab -> Alignment group -> choose: Merge & Center Merge Across Merge Cells Unmerge cells Merge cells only: CTRL + 1 -> Merge Cells (ALT + M) |
|
Number formats |
select cells -> CTRL + 1 or right-click -> Format Cells zipcode (special) phone numbers = text strings - left aligned by default add leading zeros = CTRL + 1 + Custom Format as Text (allows leading zeros) = CTRL + 1 -> Number tab -> Text -> Smart Tag indicating error -> Ignore error OR as needed, type ' and then number to enter as text then ignore smart tag error |
|
Word Art |
Insert tab -> Text group ->WordArt -> Select style -> Enter text Edit: Drawing Tools Format tab or Rt-Click object + Format Shape Existing text to WordArt = Select text + Copy -> Insert tab -> WordArt -> Select style -> Paste |
|
Soft return in cell |
ALT + ENTER |
|
Find |
CTRL + F Finds 1st instance Find All = all Find Next |
|
Replace |
CTRL + H Replace all Replace |
|
Find/Replace Options |
Within: sheet or workbook Match case Match entire cell contents Look in formulas Search by row or column |
|
Wildcards |
* = replace multiple characters ? = replace 1 character |
|
Sort data |
Select cell in range -> Home tab -> Editing group -> Sort & Filter -> choose: Sort A to Z Sort Z to A Custom Sort (multiple criteria/levels- check My Data Has Headers) can't have blank row |
|
Filter data |
Select cell in active range -> Home tab -> Editing Group -> Sort & Filter -> Filter -> Select column header/criteria |
|
Filter numbers |
|
|
Filter text |
|
|
Format as Table |
Select range -> Home tab -> Styles group -> Format as Table -> select format (headers, banded rows, etc.) + My Table has Headers if appropriate |
|
Table Tools -> Design Tab |
Name table Remove duplicates Select different style Convert to range (removes filter/sort) Select: banded rows/headers First/last column/row Total row Filter/sort option in header row |
|
Total row |
Table Tools - Design tab -> check Total Row Can change using drop-down menu |
|
Name table or range |
Table tools - Design tab -> Table Name in Properties group alphanumeric only can't start with a # no spaces no symbols but underscore Why? to reference from elsewhere |
|
Go To |
CTRL + G OR Home Tab -> Editing Group -> Find & Select |
|
Text formulas |
=concatenate equals symbol means dependency related Hit TAB to enter rest of formula when appears example: =concatenate(A2," ",B2) CONCATENATE as operation = =cell&" "&cell |
|
Display formula in active cell |
F2 View tab -> formula bar (if missing from ribbon) |
|
Edit formula |
double-click cell click in formula bar |
|
Copy formula down through column |
double-click autofill handle (bottom right corner of active cell) |
|
Text to columns |
Data tab -> Data tools group -> Text to Columns Delineated = characters separated by commas or spaces Fixed Width = fields are aligned in columns |
|
Show formulas in cells instead of resulting values |
CTRL + ` OR Formula Tab -> Show Formulas |
|
Convert formulas to text |
If one cell: select cell + F2 + F9 If all formulas - Paste Special: 1. Select range/cells 2. Copy 3. Set target location (where you want data to go) 4. Paste Special (ALT + E + S) -> Values |
|
move area code and phone #s into separate columns |
Select cells -> Copy -> Data tab -> Text to columns -> Delimited -> Other - dash ->finish |
|
Select ENTIRE column |
CTRL + SPACEBAR |
|
Select column in table |
CTRL + SHIFT + DOWN ARROW |
|
Delete Column/Row |
ALT + E + D -> entire row/column |
|
Change case to all UPPER/LOWER/PROPER |
example: =UPPER(cell) -> ENTER -> CTRL + SHIFT + DOWN ARROW (select column) -> CTRL + D (copy formula down column) OR =UPPER(cell) -> ENTER -> double-click autofill handle to copy down column |
|
Copy formula down column |
double-click autofill handle in active cell |
|
Extraction formulas |
LEFT RIGHT MID TRIM |
|
LEFT extraction formula |
=left(cell, #characters) *note space = character |
|
RIGHT extraction formula |
=right(cell,#characters) |
|
MID extraction formula |
=mid(cell,start#,#characters) |
|
TRIM extraction formula |
=trim(cell) + ENTER |
|
Operations order |
PEMDAS parentheses ( ) exponential ^ multiplication * division / addition + subtraction - example: =3*2^3/(10*5) = .48 so 10*5 = 50 2^3 = 8 3*8= 24 24/50 = .48 whereas =3*2^3/10*5 = 12 |
|
SUM |
=SUM(cell:cell) to add up range OR ALT + = OR Home tab -> autosum (sigma symbol) OR
|
|
Select cells above active cell |
CTRL + SHIFT + UP ARROW |
|
MINIMUM |
=MIN(cell:cell) identifies lowest value |
|
MAXIMUM |
=MAX(cell:cell) identifies highest value in range |
|
COUNT |
=COUNT(cell:cell) counts # of cells in range with numerical values - NOT TEXT |
|
COUNTA |
=COUNTA(cell:cell) counts all non-blank cells |
|
AUTOCALCULATE |
status bar in bottom right corner below worksheet select cells and see counts in status bar default are average, count, sum rt-click to pick others (COUNT, MIN, MAX) |
|
Trace DEPENDENTS |
in FORMULAS tab shows arrows indicating which cells are affected by the value in the active cell (locates formulas that include value in active cell) |
|
Trace PRECEDENT |
in FORMULAS tab Shows arrows indicating which cells affect value in active cell (locates cells included in formula of active cell) |
|
Relative cell reference |
adjusts as formulas are copied |
|
Absolute cell reference |
fixed cell address in formula uses $ before and after row and/or column # =cell*$J$5 |
|
Toggle between relative, absolute column, absolute row, absolute row & column |
F4 |
|
COUNTIF |
# cells meeting criteria |