• 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

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

image

Play button

image

Play button

image

Progress

1/63

Click to flip

63 Cards in this Set

  • Front
  • Back
  • 3rd side (hint)
Define: Absolute Refrence

(Address)
A cell Refrence that stays the same no matter where you copy a formula. The cell refrence contains the $ symbol before the column letter AND row number, such as $A$1.
Define: Active Cell
In a spreadsheet program such as Excel, the active cell is identified by a black border or outline surrounding the cell.

The cell you are working in.
Define: Argument
Definition:
In Excel, arguments are used in functions. They are the part of the function enclosed in round brackets (parentheses) following the function’s name. Arguments supply the data for the function to use in its calculations. In Excel, arguments are most likely to be cell references.


Example:

=SUM(A1:A6)
In this example, the argument for the SUM function is the range (A1:A6).
Define: Accounting Format
$ is left jsttified
Define Alignment

Cell Alignment
Text can be aligned:
Left center right

Top
Center
Bottom

0 to 90 degrees
Located in Home tab, alignment dialog box - click bottom right toolbar expansion button. Then the Format Cells dialog box opens.
Define: Assumption
A figure such as assumed average human weight when figuring out gravity of planets and how much that average human would weigh on each planet. It's fixed and assumed, but should be able to be changed and alter the weights.
Page 127 Solar system example.
Define Auto Fill

(Handle)
Small black square on bottom right of selection of active cells. Drag to copy.
Define: Bar Chart
A chart with horizontal orientation that compares categories: Useful when categorical labels are long
Define: Cell
The intersection of a column and row in a table or in a spreadsheet
Define: Cell Reference
The intercetion of a column and row designated by a column letter and a row number. For example C5.
Define: Column Chart
A chart that displays data vertically in a column formation and is used to compare values across categories.
Define: A constant
Such as the mathmatical equation for pie or a fixed and unchanging number or set.
Define: Data Point
A numeric value the defines a single value on a chart.
Define: Data series
A group of related data points that appear in row(s) or column(s) in the worksheet.
Define: Exploded Pie Chart
A chart that seperates one or more slices of the pie for emphasis.
Define: Format Cells


(Command)
Operations that control the formatting for numbers, alignments, fonts, borders, colors and patterns in a particular cell or range of cells.
Define: Fraction Format
Turns .5 into 1/2 in a cell.
Define: Freeze Panes
The process that enables you to keep heading on the screen as you work with large spreadsheets.

Go to View tab then in window dialog box click freeze panes..
Define: ABS function
In Excel, the Abs function returns the absolute value of a number.

The syntax for the Abs function is:

Abs( number )
Cells
A1 = -210.67
A2= 2.9
A3= -3

=Abs(A1) would return 210.67
=Abs(A2) would return 2.9
=Abs(A3) would return 3
=Abs(-4.5) would return 4.5

Negative numbers are returned as a positive number.
Define COUNTIF Function
Spreadsheets contain millions of cells ready to be filled with your data. The problem is who would want to fill in all those cells and have to keep track of them. Fortunately there are functions that help users identify how many cells contain specific data in the spreadsheet cells of Excel. COUNTIF is the best function for this and it only requires two elements to work; range and criteria.

Function Rules:

= COUNTIF(range,criteria)

range = the area you’re searching. This can be one cell or many cells.

criteria = the number, word or characters you are looking for.
Using the attached image. Use the COUNTIF formula to count the blank spaces, the number of times the word "Spreadsheet" appears, cells that equal 1000 and cells that are greater than 999.
If you include a mathematical operator =<> you must surround the criteria with "".

=COUNTIF(range,”>999")
Which of the following lets you see and / or modify page breaks that will accur when the worksheet is printed.

A) The Page Break Preview command
B) The Page Setup command
C) The Page Breaks command
D) The Print Preview command
A) The Page Break Preview command


To set or adjust page breaks in Page Break Preview:

Select View, Page Break Preview

Move the cursor to one of the blue lines separating a page until your cursor changes to a double-headed arrow. Drag to move the page break.
Define: Field Names
Definition:
In database terminology, a record holds all the information or data about one specific object that has been entered into the database.

Each individual element of information in the record - such as a telephone number or hair color - is referred to as a field.

To ensure that data is entered in the same order for each record, headings are added to the database. These headings are referred to as field names.
All of the following statements regarding fields are true except.

A) Field names must be entered in the first row of the list
B) Field names will change from record to record
C) Field names must be unique
D) Fields will be in the same order in every record.
B) Field names will change from record to record

--- Which is false ---

Side note:

Columns are Fields

Note: For help on these steps, refer to the image above.


While rows in an Excel database are referred to as records, the columns are known as fields. Each column needs a heading to identify the data it contains. These headings are called field names.


•Field names are used to ensure that the data for each record is entered in the same sequence.


•Make sure that all the data in a column is entered using the same format. If you start entering numbers as digits (such as 10 or 20) keep it up. Don't change part way through and begin entering numbers as words (such as ten or twenty). Be consistent.


•Leave no blank columns in the table.
You have a list of all the members of a club that you belong to. The worksheet contains other data as well. How can you be sure Excel recognizes the boundaries of the list?

A) Insert a comment in the upper-left corner of the list.
B) Insert a blanks row between the field names and the data.
C) Insert a blank row and blank column between the list and other data in the worksheet.
D) Type a row of dashes (---) after the last row of the list.
C) Insert a blank row and blank column between the list and other data in the worksheet.
You have a list of all the employees in your organization. The list contains employee name, office, title and salary. You want to list all employees in each branch. The branches should be listed alphabetically, with the employee earning the highest salary listed in each office. Which is true of your sort order?

A) Branch office is the primary sort and should be in descending order.
B) Salary is the primary sort and should be in ascending order.
C) Branch office is the primary sort and should be in ascending order.
D) Salary is the primary sort and should be in descending order.
C) Branch office is the primary sort and should be in ascending order.
How must data be arranged before creating a summary report?

A) In a table.
B) In ascending order by the contents of the first column.
C) In either a table or a list.
D) In a range.
D) In a range.

If you have a table you must select the table, go to "Design" tab and click convert to range. Now the subtotal button will work.
You want to show total sales for each location what should you do before executing the "Subtotals" command?
A) Sort by Sales, in ascending order.
B) Sort by Sales, in descending order.
C) Sort by Sales, in with ascending or descending order, then by location
D) Sort by Location, in either ascending or descending order.
D) Sort by Location, in either ascending or descending order.
You have a list of all the employees in your organization. The list contains employee name, location, title and salary. You want to list all employees in each location. The locations should be alphabetically, with the highest paid employees listed first for each location.
A) Sort by location ascending, then by salary ascending.
B) Sort by location ascending, then by salary descending.
C) Sort by salary descending, then by location ascending.
D) Sort by location descending, then by salary ascending.
B) Sort by location ascending, then by salary descending.
Which Office 2007 element provides commands that work with an entire document or file and displays by default in the title bar?
A) Galleries
B) Ribbon
C) Office Button
D) Groups
C) Office Button
If you need the entire screen to read a worksheet, which view would you use?

A) Outline View
B) Draft View
C) Print Layout
D) Full Screen View
D) Full Screen View
Which of the following displays when you select text in a DOCUMENT?
A) The Mini toolbar
B) The Quick Access Toolbar
C) A shortcut menu
D) The Ribbon
A) The Mini toolbar
If you see the term "C3" used in relation to Excel, this refers to what?
A) Absolute reference
B) Cell reference
C) Worksheet reference
D) Mixed reference
B) Cell reference
Pointing is a technique to:
A) Select a single cell
B) Select a range of continuous cells
C) Select a range on noncontiguous cells
D) All of the above
D) All of the above
Referring to charts:
If multiple data series are selected and rows are specified:
A) The first row will be used for the category labels.
B) The first row will be used for the legend.
C) The first column will be used for the legend.
D) The first column will be used for the category labels.
A) The first row will be used for the category labels.
If multiple data series are selected and columns are specified:
A) The first column will be used for the category (x axis) labels.
B) The first row will be used for the legend.
C) Both A & B
D) Neither A nor B
B) The first row will be used for the legend.
Define: DATE function.
Use:

=DATE(year,month,day)

=DATE(1967,12,6)

Returns = 24,812

=(TODAY()-DATE(1975,12,6))/365 Returns your age
Define: MONTH function
MONTH function will return a number from 1 to 12 based on a given serial number – such as one returned from the =TODAY() function.

Use:
Cell A1 =TODAY()

=MONTH(A1) Today is October, 11th so the formula will return a 10 for October.
Define: NOW function
***Time and Date***

The NOW function, one of Excel's date and time functions, is used to add the current time and date to a spreadsheet.


The syntax for the NOW function is:

= NOW ( )


Note: The NOW function takes no arguments.
Define: UPPER, LOWER & PROPER functions.
=UPPER(text or cell reference) changes case to all capitals

=LOWER(text or cell reference) changes case to all lower case

=PROPER(text or cell reference) changes first letter of each word to capital and the rest are lowercase.
Define: YEAR Function
It returns the year of a selected date.

Where C21 is the =TODAY() function.
=YEAR(C21) returns 2011 (general formatting)
How do you add a hyper-link to a spreadsheet?
Go to the insert tab and click Hyper-link. click the globe and open the page you wish to link. go back to excel and you will see your page, click OK.
What are mathmatical operators?
+, -, *, /, ^
What order are mathmatical operators calculated in?
( ), ^, *, /,+,-

* / or + - are calculated in order if none have ( ) around them.
Define the Name Box.
Definition: The Name Box is located next to the formula bar above the worksheet area. The Name Box displays the cell reference of the active cell. It will also show the name assigned to a cell or range of cells. The Name Box can also be used to assign names to cells or ranges of cells.
How do you protect a worksheet so some cells are editable?
How to Protect a Worksheet:

Select the whole worksheet and go to numbers tab to bring up the numbers dialog box. Select protection tab and make sure the check mark is checked to lock the whole worksheet.

Then select the fields you want unprotected and go to numbers tab and bring up the numbers dialog box select protection tab and uncheck the protection tab.

Now go to the review tab click protect sheet and make sure “select locked cells” is unchecked.

Now your worksheet is locked and you can change the cells intended.
Define: Relative reference

(address)
In Excel and other spreadsheets, a relative cell reference identifies the location of a cell or group of cells.

It is a cell address with no $. Not Absolute and not mixed.

A1 = Relative address
Define the properties of a VLOOKUP function.
=VLOOKUP(Lookup Value, Table Range, Column Row you want returned)

The syntax for the VLookup function is:

VLookup( value, table_array, index_number)

value is the value to search for in the first column of the table_array.

table_array is two or more columns of data that is sorted in ascending order.

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.
Define: X and Y axis.
The X axis is the horizontal axis that depicts categorical labels.

The Y axis is the vertical axis that numerical values.

For a column chart and visa versa for a bar chart
Define: PMT function
The PMT function, one of Excel's financial functions, can be used to calculate the payments for a loan or the future value of an investment.

The syntax for the PMT function is:

= PMT ( rate , nper , pv )

rate - the annual interest rate for the loan. .435/12

nper - the total number of payments to be made on the investment. 30*12

pv - the present value of the investment. For future value this argument is omitted. -$174,000
How do you create custom numbers and dates?
In the Home Tab in the numbers dialog box expand by clicking the bottom right nub to open the Format Cells dialog box. Here click the category most closely associated with the thing you want to create. Then click custom and you can create a custom format.

Change Date Format:

Click Home Tab  Left click on the numbers dialog box bottom right nub to open the Format Cells dialog box. Click the Date category and then click custom so you can create a custom date format.

Type mmm,dd,yyyy
Define: IF formula
What the IF function does
The IF function is one of Excel’s most useful and most used functions. What it does, basically, is test to see whether a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The basic form or syntax of the function is:

=IF(logic test, value if true, value if false)

The logic test is always a comparison between two values. Comparison operators are used, for example, to see if the first value is greater than or less than the second, or equal to it. The "value if true" and "value if false" parts of the function are known as arguments. They tell the function what to do, depending on the outcome of the logic test.
A look at nested IF
=IF(A5<29701,A5*15%,IF(A5<71950,A5*25%,A5*28%))

The logic test of the Nested IF function, checks to see if a employee’s income is greater than or equal to $29,701, but less than $71,950. If it is, the deduction rate is 25%. If the income is greater than or equal to $71,950, the deduction rate is 28%. Additional rate changes could be added another nested IF functions inside the existing function.

Note: there is no comma separator in 29,701 or 71,950 in the above example. This is because the IF function uses the comma to separate the three sections of the IF function contained within the round brackets.

If you use a comma as a separator in numbers greater than a thousand, Excel will give you an error message saying you have too many arguments in your function.
How do select a large section of a workbook?
Go to a cell location. In name box type A10000 Select across columns to E10000.

Press Shift + END + Up Arrow.

You just selected 50000 cells.
In reference to a Bar Chart:

The X axis displays the?

The Y axis displays the?

(Legand, Numerical Values, Categorical Labels or Title)
The X axis displays the Numerical Values

The Y axis displays the Categorical Labels
In reference to a Column Chart:

The X axis displays the?

The Y axis displays the?

(Legand, Numerical Values, Categorical Labels or Title)
The X axis displays the Categorical Labels

The Y axis displays the Numerical Values
In reference to a Column Chart:

The X axis displays the?

The Y axis displays the?

(Legand, Numerical Values, Categorical Labels or Title)
The X axis displays the Categorical Labels

The Y axis displays the Numerical Values
Clustered Column Chart:

First row will be the...

First column will be (if to the left of data range) the...
First row will be the (X axis) catagorical labels

First column will be (if to the left of data range) the legand.
Match data types to chart types

Data:
 Displays a proportional relationship
 Shows the relationship of parts to a whole, but displays more than one series of data
 Compare values across different categories
 Shows the high, low and close or temperature range.
 Shows a trend over a period of time
 Shows a relationship between two variables and is used to represent the data from scientific or educational experiments.

Chart:
 Line
 Clustered Column
 Pie
 Scatter (XY) chart
 Doughnut
 Stock chart
Displays a proportional relationship = Pie chart

Shows the relationship of parts to a whole, but displays more than one series of data = Doughnut chart

Compare values across different categories = Clustered Column chart

Shows the high, low and close or temperature range = Stock chart

Shows a trend over a period of time = Line chart

Shows a relationship between tow variables and is used to represent the data from scientific or educational experiments = Scatter (XY) chart
How do you select an entire worksheet?
CTRL - a
Shift - F8 has the same affect as what other action for selecting cells?
Holding down the CTRL key.
1) Pressing Shift - F8 Puts you in what mode.

2) Pressing F8 again puts you in what mode.
1) Add Selection

2) Extend Selection
How would you combine the name below and have the cell containing the name not be a formula, but instead only contain the first and last name in text.

Cell A1 = Scott
Cell A2 = Johnson
Cell A3 = Combine the name into both fists and last name text only (no formula)
=A1&" "&B1 then copy the cell and past to A3 with paste special and select “value”.