Excel 101 Flashcards
What is a Structured Reference? What is the quick function to create an ‘Excel Table’?
The combination of table and column names from an ‘Excel Table’ Make it so all fields in column automatically references
Excel Table Quick Function =CTRL+T
What is a nested function
in computer programming , a nested function is a function which is defined within another function , the enclosing function
How does the SUBTOTAL function operate in excel (syntax)? What are important about the numbers? What are things to remember
syntax is SUBTOTAL(Function_num, ref1, ref2)
the function is the type of subtotal you want to operate, options are 1-11 or 101-111, difference is for 1-11 hidden rows are taken into account for the calculation
*SUBTOTAL is designed for vertical ranges not rows
What is “IFS’ statement in excel as opposed to ‘IF”? what is the syntax for ‘IFS’
IF (A1 equals 1, then display 1, IF A1 equals 2, then display 2, ) IFS(A1=1,1, A1=2,2, A1=3,3)
To specify default value make argument with true (true, 4)
If going to use text in formulas need to wrap in quotes
What are the type of things ‘SUBTOTAL’ can tell you?
Average, Count, Max, Min , STD DEV, SUM, VAR
What is the shortcut key to see the available functions?
Shift + F3
What are the different formatting codes you can use for the TEXT function when converting date to name
‘ddd’ Mon, ‘dddd’ Monday, ‘mm’ 01, ‘mmm’ jan, ‘mmmm’ January , ‘hh’ hours
What is the main function of the Pivot Table
it is a tool used for summarizing and filter data like in salesforce reporting
What do quotation mark around something in excel tell it ? What is special about quotations when you have math argument like < or =
the quotation marks around a characteristic tell Excel that this is text
when using math logic, have to wrap everything in quotes even number for example “<35” is correct where “
What do ‘A’ and ‘1’ refer to in excel each seperate part
‘A’ refers to the column and and ‘1’ refers to the row
What is the difference between a relative cell and absolute cell?
By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another. For example, if you copy the formula =A2+B2 from cell C2 to C3, the formula references in C3 adjust downward by one row and become =A3+B3.
(relative is always changing, based on used format)
If you want to maintain the original cell reference when you copy it, you “lock” it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.
(locked down, will always reference the fields you want )
How does ‘SUMIFS’ operate?
SUMIFS allows you to look at the sum on a table that meets specefic criteria. for example, retrieve the sum if country is United States, revenue from year 2012, if product type is bicycle . The logic is SUMIFS(sum_range, criteria range1, criteria1, criteria range 2, criteria 2 )
How does ‘COUNTIFS’ operate?
CountIFS does exactly what you think, it will give you the count based on the criteria you put in. COUNTIFS (criteria_range1, crieria 1, criteria_range2, criteria 2 )
How does ‘Array’ function work? What is important to remember about the separators ?
Alllows you to look at mutiple criteria by using the “{“ and “}” brackets, for instance {“apple”,”bannans”} will look at both apple and bananas. if the text is seperated by “;” semi colon looks at vertical cells, if separated by “,” comma than horizontal cells
When using ‘array ‘ criteria for ‘SUMIFS’ what do you have to remember when you want to get the total sum value? HINT: You have to add something
How do you debug an array function
You have to add the ‘SUM’ function , if you dont , will give you a value for just one of the ‘or’ arguments. The SUM IFS argument has to be nested in “SUM’
Debug an array function by using “F9”
=SUM(SUMIFS($A$2:$A$9,$B$2:$B$9,{“Apples”;”Bananas”},$C$2:$C$9,”Tom”))
