excel Flashcards
(84 cards)
1
Q
what is a workbook
A
entire Excel file (including all worksheets)
2
Q
what is a Ribbon
A
set of menus at top of excel program
3
Q
what is a context menu
A
set of option that appears when right click
4
Q
what is paste special
plus what if chose transpose?
A
shortcut : ctrl+alt+v
paste only the selected features of a cel in another: ex; only the formula
transpose: column pasted appears in row and vice versa
5
Q
keyboard navigation
1)return to cell a1
2)move on the hedge of data
3)select datta
4select + move on the edge data
5) goes to last row/column with data
A
1) ctrl+home
2)ctrl+arrows
3)shift+arrows
4)ctrl+shift+arrows
5)ctrl+end
6
Q
how and why to sort data
A
why: organise the ex decreasing numerical order
how: data + sort …
7
Q
what is soft coding vs hard coding
A
soft coding: referencing a cell
hard coding: typing numerical shit
8
Q
what is relative reference
A
the cell is not locked, will move when dragged relative to the active cell
9
Q
what is
1)absolute row reference
2)absolute column reference
A
1) b$2
2) $b2
10
Q
what is sumproduct
A
multiplies corresponding arrays and gives their sum
ex: sumproduct(1;2 , 3;4)
will give 13+24
11
Q
what its means:
> =
<=
<>
A
greater or equal
smaller or equals
not equal to
12
Q
caracteristic of the AND foncti0n
A
AND(… , …) will display true only if all the elements are true
13
Q
OR foction
A
OR(… ,…) will display true if minimum 1 of the arguments is true.
14
Q
if fonction:
A
based on an argument: can display a specific value if true and another one if false
IF( logical test, value if true, value if false)
15
Q
nested if fonction
A
if( logical1, value if true, if(logical2, value if true, value if false) etc…
can display different answers based on different criteria.
the nested if is embedded in the value if false of the first formula
16
Q
count fonction
A
count(range)
count the number of cell that contains numbers
17
Q
counta fonction
A
counta(range)
count the number of cell that are not blank
18
Q
min fonction
max fonction
A
min(array) ; return the smallest number
max(array) ; return the biggest number
19
Q
small fonction
large fonction
A
small(array, k) ; return the k smallest number
large(array, k) ; return the k biggest number
20
Q
RANK fonction
A
to rank a number in a list of number
RANK.EQ(cell (number)to be ranked, array of the list of numbers (most be locked to drag after), order (0 or 1))
0=ascending
(plus petit au plus grand
1=descending
21
Q
average fonction
median fonction
A
average(array (list of numbers)
same for median
22
Q
var fonction
stdev fonction
A
var.s(array)
same for stdev
.s is for sample
.p is for popûlation
23
Q
covariance fonction
correlation fonction
A
covariance.s(array1, array2)
correl.s(array1,array2)
24
Q
countif fonction
countifs fonction
A
countif(range(Select the range where condition is written) , (select cell with condition))
a1 contains 30… or M
exempele of condition: a1, “>=”&a1, etc
for countifs: theire is multiple array and condition. all condition must be satisfied to be counted
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
25
sumif fonction
or averageif fonction
SUMIF(range (what to sum), criteria) → Sums values based on a condition
Example: SUMIF(A1:A10, ">=30")
or
SUMIF(criteria_range, criteria, sum_range(what to sum)) → Sums values in a different range based on a condition
Example: SUMIF(A1:A10, "M", B1:B10)
Similarly, AVERAGEIF can be used:
AVERAGEIF(range, criteria, [average_range]) → Computes the average based on a condition
Example: AVERAGEIF(A1:A10, ">=30", B1:B10)
26
sumifs fonction
or averageifs fonction
sumifs(range(what is to sum), crateria range1, creteria1, crateria range2, crateria 2, ...3, ...3)
be carefull, sumif and sumifs do not have the same form
27
what are the question an analyst should ideally be bable to answer before starting to build the spreadsheet model (4)
part of providing info relevant for valuation
1. what is the descision
2. what are the descision alternative
3) what is the desision creteria (s) ; on what basis we make the descision
4) what is the sistem that converts inputs and descision actions into inputs measured in therm of desision creteria. ex; what is the formula to derive profits
28
what are the 4 best practice in a excel model
1) clearly state inputs and assumption
2) separates model inputs from analysis(formula) ; no hard-coding ; set parameters of model so that they can be easely modified
3)perform analisys in easy to debug steps
4) design model to anable understanding and use of others
29
how can you reliably perform analisys
1. brake down complex formula into multiple more simple
2. asses relability of those smaller formulan WHEN THE MODEL IS DEVELOPPED
3. evaluate face validity: verify that outputs look correct at erach step
4. after compleation test simple inputs
30
3 steps to make a good model understood by others
1. label inputs and intermediate calculation with plane langage
2. flow of intermediate calculation should be logical
3. comunicates the result of the analisys (into a single section of the sheet)
31
how to name a cell
+ rules for name
name box in formula bar
1)click on cell /range to name
2, type name in name box
rules carl to fix:
1. must start by / or _
2, cannot contain space
3. cannot be cell reference ( ex; no B1)
4. not case sensitive; cannot have 2 time the same name even if one is in majuscule and the other in minuscule
32
what is formula auditing and how to do it
allowas you to trace precedent in a formula.
go to formula + trace precedent (arrows should appear)
33
countblanks fonction
countblanls(array)
count cell with noting in it
34
unique fonction
unique(array(the range to extract unique value), by_col (true or false), exacly_once(true or false))
when in default setting: show everithing that appears at least 1 in rows
optional items:
by_col: true → Extracts unique values by columns instead of rows.
false (or omitted) → Extracts unique values by rows (default).
exactly_once: true → Returns only values that appear exactly once.
false (or omitted) → Returns all unique values (default behavior).
35
round fonction
round(numbers (the thing to round, num_digits)
num_digits: the number of decimal you want. -2 will round to the 100. 3 will round to 3 numbers ofter the coma
36
not fonction
Flashcard 1
not(logical_value):
ex: B1 is 5
=B1=5 will displau true
but NOT(B1=5) will display false
Logical function that returns the opposite of a given value (e.g., changes TRUE to FALSE and vice versa).
37
freeze panes
Flashcard 2
Freeze Panes:
Used to lock row/column titles when scrolling down or right. Found under the “View” tab.
38
conditional formating
Flashcard 3
Conditional Formatting:
Applies formatting (e.g., color) based on defined conditions.
Found under “Home” → “Conditional Formatting” → “Highlight Cells Rules.”
+ set valu condition (greater/smaller/equals for that color to appear)
39
auto fill
Flashcard 4
Auto Fill:
Automatically continues a sequence by dragging down or double-clicking the fill handle.
40
flash fill
Flashcard 5
Flash Fill:
Automatically fills data based on patterns.
ex: whrite all the first name of everyone in a colomn
Found under “Home” → “Fill” → “Flash Fill.”
41
datta validation
Flashcard 6
Data Validation:
Restricts input in a cell. Example: Creating a dropdown list of predefined options.
1)datta + data validation + allow(list)
2) select source drag the options
42
what is csv file, what is the benefits compared to an excel file
A CSV (Comma-Separated Values) file is a plain text file that stores data in a tabular format, where each value is separated by a comma (,) or sometimes by a semicolon (;) or tab.
benefits: take way less space
43
xlookup fonction compasition
=xlookup(lookupvalue, lookuparray, returnarray, ((ifnotfound)), ((matchmode)), ((searchmodel)) )
(())= optional
44
describe the component of the 3 not optional components of the x lookup fonction
lookup value; the value to search for
lookup array; the range to seach the desired value
reterned array: the array of the information that will be reterned
45
describe the component of the 3 optional components of the x lookup fonction
if not found: the text that will be return if not found. if omitted return NA. only for match mode 0
match mode:
0; exact match, if not found return na or text
-1; exact match, if not found return next smaller item
1; exact match, if not found return next larger item
search mode:
1; (default) perform search starting at first item
-1; perform reverse search starting at last item
46
how to perform a rows and column xlookup search
=xlookup(colomnvalu, columnnamearray, xlookup(rowvalu, rownamearray, alldataarray))
inverse collomn and row also work
47
payment fonction
PMT(rate, nper, pv, [fv], [type])
watch out for sign: - = cash out
rate is not coumpound
nper; the number of payment
pv; the money you receive today
fv; the money you will have at the end. pricipal not repaid
type:
0 or ommited = payment at end of the period
1 = payment a begening of period
48
pv fonction
watch out for sign: - = cash out
PV(rate, nper, pmt, [fv], [type])
rate is not coumpound
nper; the number of payment
pmt; money you pay
fv; a sum at the end of all payment that will gets discounted to obtain its pv
type:
0 or ommited = payment at end of the period
1 = payment a begening of period
49
fv fonction
FV(rate,nper,pmt,[pv],[type])
watch out for sign: - = cash out
rate is not coumpound
nper; the number of payment
pmt; money you pay
pv: sum you pay at the begening
type:
0 or ommited = payment at end of the period
1 = payment a begening of period
50
when to use "..." in excel.
when setting the condition in a formula
or in formating formula like =text
or in value if not find
ex; "=30" or "apple"
51
#VALUE!
error when it finds spaces, characters, or text in a formula where it is expecting a
number.
52
#NAME?
error message appears if Excel can't understand the name of the formula you're
trying to run, or if Excel can't compute one or more values entered in the formula itself.
53
#####
means the column isn't wide enough to display the value you've inputted.
54
#DIV/0!
means divide a formula by zero or an empty cell.
55
#REF!
when a formula references a cell that is not valid.
56
#NULL!
errors occur when you specify an intersection of two areas that don't intersect, or
when an incorrect range operator is used.
57
.#N/A
means that the numbers you are referring to in your formula cannot be found.
58
#NUM!
contains numeric values that aren't valid.
59
IFERROR formula . write the formula and understand the components.
IFERROR(value, value_if_error)
if there is an error in a formula the cell will show what you want
60
Formula auditing
Trace precedents
Trace dependents
Show Formula
(ALT + M) , Formulas
61
Naming a Cell (3 strategies) with the rules
Method 1: Use the name box on the formula bar
Method 2: Use “Create from Selection” on the FORMULAS ribbon
Method 3: Use “Name Manager” on the FORMULAS ribbon
Rules: 1. Names must start with a letter, underscore (_), or a backslash (\). Remaining characters
in the name can be letters, numbers, periods, and underscore characters.
2. Names cannot contain spaces. Use the underscore character (_), period (.), or
capitalization to separate words.
* For example, Cost_Materials, Cost.Materials, or CostMaterials are all acceptable
names.
3. Names cannot be cell references. For example, you cannot name a cell B1.
4. Names are not case-sensitive. Names can contain upper and lower-case letters, but
Excel does not permit the use of the same text for another cell, even with different
capitalization.
* For example, if you tried to create cost_materials, Excel would indicate that the name
already exists by bringing your cursor to the cell we already named Cost_Materials.
62
Goal Seek. What it is and the components
Definition:it is the ability to calculate
backward to obtain an input that would result in
a given output
Set Cell: cell containing the formula that will
return the result you're seeking
* To Value: the target value you want the formula
to return
* By Changing Cell: the location of the input
value that Excel can change to reach the target
63
Data Table (1 way , 2 ways ). Do in excel to understand better.
Two-way data table:
* When the values to be used for the input variables are listed in a column
and in a row;
* The references are the cells of the input variables used in the formula
* The values for the first input variable are listed in the first column of the
desired range
* The values for the second input variable are listed in the row above, one
column to the right of the column with the values for the first input variable
* The cell above the first value for the first input variable contains the
formulas (or references to cells that contains the formulas) for the desired
output
* The range to highlight includes all the cells mentioned above.
64
How are dates and times represented in serial number format for calculations?
Dates are stored as sequential serial numbers for calculation purposes. For example, December 31, 1899, is serial number 1, and January 1, 2008, is serial number 39448 because it's 39,448 days after January 1, 1900. Time values are represented as a decimal portion of a date; for instance, 12:00 PM is represented as 0.5 because it's half a day.
65
DATEVALUE Function
DATEVALUE(date_text)
needs to be inside quotation mark""
The DATEVALUE function converts a date stored as text (ex;"1/30/2008" or "30-Jan-2008") The DATEVALUE function converts a date stored as text
into an actual date value that Excel recognizes (serial number).
66
TIMEVALUE function
TIMEVALUE(time_text) Time_text Required. A text string that represents a time in any one of the Microsoft Excel time formats; for example, "6:45 PM" and "18:45" text strings within quotation marks that represent time.
0.5 represents 12:00 PM (noon).
0.75 represents 6:00 PM.
0.25 represents 6:00 AM.
0 represents 12:00 AM (midnight).
1 represents 11:59:59 PM (end of the day).
67
VALUE function
VALUE(text) Text Required. The text enclosed in quotation marks or a reference to a cell containing the text you want to convert. Converts a text string that represents a number to a number.
68
DATE Fonction
=DATE(year, month, day) - Creates a date based on the year, month, and day provided as arguments.
69
Day Fonction
=DAY(serial_number) - Returns the day of the month from a date represented as a serial number.
70
MONTH Fonction
=MONTH(serial_number) - Returns the month as a number (1 to 12) from a date serial number.
71
Today Fonction
=TODAY() - Returns the current date as a serial number.
72
YEAR fonction
=YEAR(serial_number) - Returns the year part of a date represented as a serial number.
73
CONCAT Fonction
=CONCAT(text1, [text2],...) - Combines multiple text strings into one text string.
74
Find Fonction
=FIND(find_text, within_text, [start_num]) - Returns the starting position of a specified text within another text, optionally starting the search at a specified position
find_text (Required)
The substring (text) you want to find.
Example: "cat", "@", "abc".
within_text (Required)
The larger text string in which you are searching.
Example: "The black cat is here.".
start_num (Optional)
The position in within_text where the search should begin.
Default is 1 (start from the beginning).
Example: If you set start_num to 5, Excel will begin searching from the 5th character..
75
LEFT Fonction
=LEFT(text, [num_chars]) - Returns the leftmost characters from a text string, based on the number of characters specified.
76
LEN Fonction
=LEN(text) - Returns the number of characters in a text string
77
MID Fonction
=MID(text, start_num, num_chars) - Returns a specific number of characters from a text string, starting at the position you specify.
78
Right Fonction
=RIGHT(text, [num_chars]) - Returns the rightmost characters from a text string, based on the number of characters specified.
79
TEXT Fonction
=TEXT(value, format_text) - Formats a number and converts it to text.
80
TRIM Fonction
=TRIM(text) - Removes all spaces from text except for single spaces between words.
81
what is the coding langage of macro in excel
vba
82
when to use macro
formatting highlighting hiding resizing unhiding
can be connected to other office 365 application ex; outlook
automating task
83
what is the danger of macro
every click is recorded
84
what is macro, when to use it and how to use it
When: If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks.
What is: an action or a set of actions that you can run as many times as you want.
How to: When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.
