Study your flashcards anywhere!
Download the official Cram app for free >
 Shuffle Toggle OnToggle Off
 Alphabetize Toggle OnToggle Off
 Front First Toggle OnToggle Off
 Both Sides Toggle OnToggle Off
 Read Toggle OnToggle Off
How to study your flashcards.
Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key
Up/Down arrow keys: Flip the card between the front and back.down keyup key
H key: Show hint (3rd side).h key
A key: Read text to speech.a key
42 Cards in this Set
 Front
 Back
=D3*$B$7

=D3*$B$7
copying this formula keeping $B$7 value 

=$A1
=A$1 
=$A1 keeps column Absolute
=A$1 keeps Row Absolute =$A$1 keeps both Absolute 

F4 function key

F4 function key cycles through
e.g., Enter A1 in a formula F4 converts the cell reference to =$A$1 F4 again converts it to =A$1 F4 again displays =$A1 F4 again starts over 

Select a range that contains values,
Excel displays information about the selected range on the status bar 
To see some other statistic
relating to the selection, rightclick the text on the status bar at the bottom of the screen 

In Range A2
joHn is shown... How do I make it Proper? 
joHn
becomes John with =Proper(A2) 

What does this do?
=SUM(Sheet2:Sheet6!C1) 
=SUM(Sheet2:Sheet6!C1)
=SUM ( Sheet2 : Sheet6 ! C1 ) Sum C1 on Sheet 2 with with C1 on Sheet 6 

What does this formula do?
=SUM(Sheet2:Sheet6!C1:F12) 
=SUM(Sheet2:Sheet6!C1:F12)
= SUM (Sheet2 : Sheet6 ! C1 : F12) Add up C1:F2 from Sheet 2 through Sheet 6 

What does this formula do if in Sheet 3 of a 6 sheet workbook?
=SUM"*"!C1) 
If in Sheet 3 of 6 worksheets, then
=SUM"*"!C1) =SUM( " * " ! C1) does this: =SUM(Sheet1:Sheet2!C1,Sheet4:Sheet6!C1) 

=SUM(‘Region*’!C1)

=SUM(‘Region*’!C1)
=SUM (‘ Region * ’ ! C1) does this: =SUM(Region1:Region4!C1) if 4Region Worksheets exist 

=SUM(‘Sheet?’!C1)

=SUM(‘Sheet?’!C1)
=SUM ( ‘ Sheet? ’ ! C1) sums C1 in all sheets between 1 to 9 

= sign starts a formula

B2 + B3 without the =
enters B2 + B3 as text 

What does "&" do?

This character (&) joins
the content from 2 or more cells and places them all into one cell e.g. "For this month: " & B7 




Alt + =

Alt + =
AutoSum 

Populating a range with the same item

Highlight the area
Enter the item in the active cell Ctrl + Enter fills the highlighted area 

Evaluate a formula giving the wrong result:
=2+3*10 giving 32 and NOT 50 
Evaluate a formula giving the wrong result:
=2+3*10 giving 32 and NOT 50 Highlight Formula Go to Formulas tab then Formula Auditing 

=COUNT(Price)

=COUNT(Price)
counts the number of something 

=MEDIAN(Price)

=MEDIAN(Price)
What is the middle number of this Name Range? 

=SUMIF(Price,F3)

=SUMIF(Price,F3)
Sum the items in the Price Range meeting the criteria in F3 

=COUNTIF(Car_Type,F4)

=COUNTIF(Car_Type,F4)
Count the item in the Name range fitting the criteria in F4 

To name a cell or range

To name a cell or range:
Highlight cell or range Click in Name Box Type Name Hit Enter Now this more meaningful Name can be used in Functions because it is already selected 

To edit, delete, or create names for a Name Region?

To edit, delete, or create names for a Named Region:
Ctrl + F3 

Merging cells can cause later problems, so how do I handle
Titles, etc 
Merging cells can cause later problems,
so a better way to handle Titles, etc? Select a range of cells, then Ctrl + 1, then above illustration 

What are Ordering rule for borders?

Add Borders in this order:
1) Line 2) Color, 3) Border (add lines) 

Note:
Number Formatting is a Facade It sits on top of the number The number underneath the Number format may be different than the formatted number 
Using Currency or Accounting format
does not round a number To actually round a number, use the ROUND function = ROUND ( D1 , 1 ) to put the actual rounded number in calculations 

For example, the ROUND function can be used to reduce a value by a specific number of decimal places.
Unlike formatting options that allow you change the number of decimal places displayed, Excel's rounding functions actual alters the data in your worksheet 
The syntax for the ROUND function is:
= ROUND ( Number, Num_digits ) 

Ctrl + 1 with a chart does what?

Ctrl + 1
formatting chart area 

A chart title can be what is in a cell

Insert a Title,
then F2 to cell reference for a title 

To print only a selected area of a worksheet?

To print only a selected area of a worksheet
Select area Page Layout Set Print Area 

Alt+P+S+P

Alt+P+S+P
sets up the area to be printed after the area has been selected 

="AssumptionSheet"!B5

="AssumptionSheet"!B5
use data in another Worksheet other than the one currently on 

='Assumption Sheet'!B5
Why the single "'" 
='Assumption Sheet'!B5
has single ' because the Worksheet has a space in the name: Assumption Sheet NOT AssumptionSheet 

Why the [ ]

Brackets indicates going to another WorkBook
Note: using another workbook means the other workbook must be available the same way 

Note:
Enter enters data or formula, then moves down one cell Shift + Enter does the above, BUT moves up 1cell 
Note:
Ctrl + Enter keeps you in the cell without moving down Tab to go right Shift + Tab to go left 

=IFERROR(C2/B2,"Review")

=IFERROR(C2/B2,"Review")
The general format is: =IFERROR(if formula is wrong, err msg is generated) 

.



With Conditional Formatting
What does this do: =COLUMN()>$A2+1 
Select the range you want to apply the formatting to, starting with a cell in row 2 on your example (as per attached)
Conditional formatting > New Rule > Use a formula =COLUMN()>$A2+1 Use the format button to choose the formatting to color blank cells in a region 

With Conditional Formating
The focus is writing a formula to compare a selection to the "red" cell 
Think in terms
of the desired condition comparison of the primary cell 

Advanced Filter can be used with a TRUE/FALSE formula

Again
Advanced Filter can be used with a TRUE/FALSE formula 

If Function

And again


.

And again


More efficient way to do a pseudo If Function

And again
