• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/59

Click to flip

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:
white


light grey


dark grey

Office background

File -> Options or ALT + F + T

File -> Options or ALT + F + T



Document themes

Page layout tab - Themes group


choose predefined or select a color, font, effect

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

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

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



Formulas tab -> autosum

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)

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