Leskoven Bus 1305 Test 1
Vlookup (explained)
(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match - indicated as 0/FALSE or 1/TRUE
Find
(find_text,within_text,[start_num]) Finds find_text in within_text. Search begins at start_num and is case sensitive.
Vlookup (book)
(lookup_value,table_array,col_index_num,{range_lookup}) Find lookup_value in the first column of table_array and returns a value from col_index_num.
PMT
(rate,nper,pv{fv},[type]) Calculates periodic payment for a loan based on a constant interest rate and constant payment amounts
Trim
(text) Returns text with any leading, trailing, or extra spaces between words removed
4 ways to edit cells
1) formula bar 2) retype by clicking ones 3) double click on cell to edit 4) f2
Financial Terms
APR=annual percentage rate. NPV=net present value of future investments
Excel Workbook
An excel file that contains one or more worksheets
Dialogue Launch Box
An icon in a group that opens a corresponding dialog box or task pane
Naming worksheets
At bottom of page, double-click name and rename
Best uses for different chart types
Column - used to make comparisons-most common type; Excel's default chart type; F11 creates automatic column chart •Stacked column - used to show percent to total •Line - used to show trends over time-use sequential order and regular time intervals •Pie - used to show relative proportions or parts of a whole •Bar - same as a column; axis reversed
Flash Fill
Data tab, click on icon
How excel handles dates
Every date has a number, starting with January 1st, 1900. Any date after that, it will be able to pick up the pattern; before that, not so much.
Page setup
File>Page setup sets up things like orientation (landscape/ portrait)
Range
Group of cells in a worksheet that have been selected or highlighted Performed commands will affect the entire range.
Moving around and navigation in a worksheet
Home - column a of the current Ctrl+Home - A1 Enter - Down one row Shift+Enter - up one row Tab - One column to the right Shift+Tab - One column to the left
Tabs
Home, insert, etc.
Wrap text
Home>alignment group>wraptext
what does it mean to have 2 cells dynamically linked?
IF function. if one changes, the other follow suit
name box
Located on the formula bar, indicates the current address of the active cell or cell range
Formula bar
Located under the toolbars at the top of the screen Contains the edit line for working with formulas, and provides info on selected cell
median
Measure of the middle of the set
PMT Purpose
PMT, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate.
PMT (explained)
Rate Required. The interest rate for the loan. Nper Required. The total number of payments for the loan. Pv Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal. Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type Optional. The number 0 (zero) or 1 and indicates when payments are due. Set type equal to If payments are due 0 or omitted At the end of the period 1 At the beginning of the period
Print area
Select cells you want to be printed<page layout<page setup group<click print area<click set print area
Contextual tab
Specialized tabs at the top when a certain thing is selected
Active Cell
The cell that is the recipient of an action, like typing and whatnot Surrounded by dark green Only cell that can have data entered into it
Cell Addresses
The location of a cell on a worksheet, defined by the column letter and the row number
Fill handle
Thin black cross symbol in lower right corner of active cell. Clicking and dragging it can copy cell contents or create a series.
Date functions
Today(_____) Ctrl+ Shift; time Now: will return the current date
Named ranges
a cell or group or cells that have been given a name other than the default column and row cell address references that can then be used within a formula
Complex formulas
a function that combines multiple functions within a formula PEMDAS
Format Painter
a tool that allows you to copy a format and apply it to other sections
Rows
a vertical set of cells that encompasses all the columns in a worksheet
Columns
a vertical set of cells that encompasses all the rows in a worksheet
Using the sum function
adds all of the numeric information in a specified range, list of numbers, list of cells, or any combination =SUM(____)
Math functions
adds all of the numeric information in a specified range, list of numbers, list of cells, or any combination =SUM(____) int: Abs: Round:
Relative addressing (referencing)
basic cell references that adjust and change when copied or using autofill
Merge cells
combines cells as initial cell addresses Merge & Center button
autofill
copies info form one cell or a series in adjacent cells in the direction in which the fill handle is dragged
Use of copy key
copy data from one spot to another
Paste special
copy what you want to copy<go to the place where you want to paste and right click<click paste special
f11
creates a chart, but puts chart on its own page
Name Box
displays cell reference of a specific cell
autofit
double-click in between columns/rows home-->format--> click
Excel Worksheet
each instance of a spreadsheet grid of columns and rows in which data is entered
Changing column and row width height
format>column width autofit, set to certain width, etc.
use of shift key
highlight adjacent or continual ranges Move active cell
Hiding columns and rows
highlight desired range, right click, go down and click on this
If, And
if:(logical_test,[value_if_true],[value_if_false]) And:(logical1,logical2)
truncated text
is a column isnt wide enough it will automatically truncate
Text Functions
left:(text[num_chars]) Returns a string num_chars long from left side of text. Find:(find_text,within_text,[start_num]) Finds find_text in within_text. Search begins at start_num and is case sensitive. Trim: (text) Returns text with any leading, trailing, or extra spaces between words removed
Average
mean of specified range of cells
Custom header
page layout>Page setup dialog box> header/footer> custom header
Purpose of the alt key
pressing this key will display key tips or shortcuts for items on the RIBBON and QUICK ACCESS TOOLBAR
Data series
range of values with 3 components Series name - id's the data series Series values - actual data displayed Category values - the groups that the series values belong to
max
returns the maximum value of a range
Min
returns the minimum value of a range
Mode
returns the most frequently occurring number
counta
returns the number of cells in a range that contains any type of data
Count
returns the number of cells that contain numeric data
Bar
same as a column; axis reversed
Quick Analysis tool
select data> control+ Q> Preview
Moving data
select+ drag copy+ paste in ribbon
#######; Scientific notation
shows up if the number is too long, expand cell width
Absolute addressing (referencing)
the exact cell address when both the column and row need to remain the constant regardless of position of cell when the formula is copied to other cells use F4 function key
Cell
the intersection of a row and column in a table or worksheet
Building formulas
type in formula bar type in cell+ select cells
Column
used to make comparisons-most common type; Excel's default chart type; F11 creates automatic column chart
Stacked column
used to show percent to total
Pie
used to show relative proportions or parts of a whole
Line
used to show trends over time-use sequential order and regular time intervals
Mixed cell referencing
using a combination of absolute cell referencing and relative cell referencing for a cell address within a formula by preceding either the column letter or the row value with a dollar sign to "lock" as absolute while leaving the other portion of the cella dress as a relative reference
Category
way excel interprets data for graphs
