• 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/42

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;

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,
right-click 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 4-Region 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 1-cell
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