Excel Crash Course Exam 128 Questions with Verified Answers
move back and forth between the worksheets in a workbook - CORRECT ANSWER *Ctrl PageUp* or
... [Show More] *Ctrl PageDown*
open a file - CORRECT ANSWER *alt f o*
*ctrl o*
save a file - CORRECT ANSWER *alt f s*
*ctrl s*
print a file - CORRECT ANSWER *alt f p*
*ctrl p*
navigating to the toolbars without the mouse - CORRECT ANSWER hit *Alt and the appropriate letter* (or use the right/left arrow keys) to get to the desired tab
once there, use the Tab and Shift Tab keys to navigate around
accessing settings - CORRECT ANSWER File > Options (*Alt f t*)
*Alt t o*
Disabling the Start Screen and adding more worksheets on startup - CORRECT ANSWER click on 'Blank worksheet'
Hit *Alt f t* go go to Excel options (you can also get there by clicking the file tab)
Under 'General' unselect "Show the Start screen when this application starts"
password protecting files - CORRECT ANSWER File
Info
password protect files: "Encrypt with Password"
password protect individual worksheet (so that people can't see or edit formulas): "Protect Current Sheet"
highlighting columns and rows - CORRECT ANSWER columns: *Ctrl Spacebar*
rows: *Shift Spacebar*
widening columns and rows - CORRECT ANSWER highlight column by pressing *Ctrl Spacebar*
*Alt h o i* to auto-fit the column width
-if you want a column to have a certain width, press *Alt h o w* and enter the desired width
highlight row by pressing Shift Spacebar
*Alt h o a* to auto-fit the row height
-if you want a row to have a certain height, press *Alt h o h* and enter the desired height
format cells - CORRECT ANSWER *Ctrl 1* pulls up the 'Format Cells' menu
navigating the 'Format Cells' menu - CORRECT ANSWER after hitting Ctrl 1 to pull up the 'Format Cells' menu, you can navigate around tabs by hitting *Ctrl tab*
each category within a tab can be accessed by pressing *Alt* and the appropriate underlined letter, or by hitting *tab* to move clockwise and *shift tab* to move counter clockwise
when on the desired tab, horizontal alignment can be accessed through *Alt h* and vertical alignment through *Alt v*
paste special - CORRECT ANSWER *Alt v s t* or *Alt e s t*
changing zoom size - CORRECT ANSWER *Alt w q* or *Alt v z*
Move/copy a tab - CORRECT ANSWER *Alt h o m*
freezing panes - CORRECT ANSWER click the cell BELOW the desired row and to the RIGHT of the desired column where you want to freeze panes and hit *Alt w f f*
to unfreeze panes: press *Alt w f f* again
splitting panes - CORRECT ANSWER splitting panes allows users to scroll in both areas of the worksheet, while rows and columns in the non-scrolled area remain visible
to split panes:
-click the cell BELOW the desired row and to the RIGHT of the desired column where you want to split panes and hit *Alt w s*
-to unspilt panes, click *Alt w s* again
-press *F6* to move from pane to pane in a clockwise direction; press *Shift F6* to move from pane to pane in a counter-clockwise direction
manipulating data inside a cell - CORRECT ANSWER *F2* is the command that puts you inside a cell:
-pressing the F2 key once shows you the components of the formula you created earlier, puts you inside a cell and enables you to navigate through it to edit the existing cell data
-pressing the F2 key twice activates the cursor and enables you to move to different cells, worksheets, and even other open Excel files
*Shift arrow key* will highlight characters one at a time
*Shift Ctrl arrow key* will highlight contiguous ranges of characters
deleting and clearing cell data - CORRECT ANSWER hitting *Delete* does NOT delete the formatting in the cell
to truly delete a cell:
-*Alt h e* (Home tab > Clear)
-from the drop-down, chose the appropriate actions
*(Alt h e) a*: clears ALL contents of the cell
*(Alt h e) f*: clears only the cell FORMAT
*(Alt h e) c*: clears only the cell CONTENT
-this is the same as hitting Delete
*(Alt h e) m*: clears only the cell COMMENTS
right and down fills - CORRECT ANSWER highlight the range
*Ctrl R* tells Excel to look at the LEFTMOST column of a range of highlighted cells and copy and paste the inputs and formulas in that column to all the columns to the right
*Ctrl D* does the same thing for rows that Ctrl R does for columns, telling Excel to look at the topmost row of a range of highlighted cells and to copy and paste the inputs and formulas in that row down to all the rows below it
inserting comments - CORRECT ANSWER enter the cell and type *Shift F2*
hit *Esc* twice to exit the comment area
to edit an existing comment: go to the cell and press *Shift F2*
to delete a comment: press *Alt h e am*
inserting rows - CORRECT ANSWER highlight the row BELOW the row where you want to add a row
press *Shift spacebar* to highlight the entire row
press *Ctrl Shift +* to insert the row
note:
Excel will insert the row ABOVE the row that is highlighted
inserting columns - CORRECT ANSWER *Ctrl Spacebar* to highlight the entire column
*Ctrl Shift +* to insert the column
note:
Excel will insert the row to the LEFT of the column that is highlighted
deleting rows and columns - CORRECT ANSWER row: *Alt h d r*
column: *Alt h d c*
OR
highlight row/column and press *Ctrl -*
Paste special - transpose - CORRECT ANSWER this feature allows users to convert a vertical list of data into a horizontal list of data, and vice versa
highlight the vertical list of data
in an open cell, press *Alt e s e*
-your vertical list of data should now be listed horizontally
*Ctrl Shift !* - CORRECT ANSWER number format: 2 decimals, 000 separator
*Ctrl Shift $* - CORRECT ANSWER currency format: 2 decimal places
*Ctrl Shift %* - CORRECT ANSWER percentage format with no decimal places
*Ctrl Shift #* - CORRECT ANSWER date format with the day, month, and year
*Ctrl Shift _* - CORRECT ANSWER remove all borders
anchoring cells - CORRECT ANSWER when you copy and paste a cell containing a formula from one cell to another, Excel automatically shifts the cells being referenced in the formula
sometimes this is not desirable, which is why Excel allows you to anchor cells
Anchoring (or "Fixing") cells that are being referenced in a formula tells Excel that even if you copy the formula to another cell, the cells in the formula that are anchored should not change
fix column: $ before letter
fix row: $ before number
fix entire cell: $ before letter and before number
renaming a worksheet you are working in - CORRECT ANSWER *Alt h o r*
inserting and deleting worksheets - CORRECT ANSWER add a new worksheet: *Alt h i s* or *Alt i w*
delete a worksheet: *Alt e l*
new tab - CORRECT ANSWER *Shift F11*
Grouping columns and rows - CORRECT ANSWER to group columns:
-hit *Ctrl Spacebar* to select the desired column(s)
-hit *Shift Alt right arrow key* to create the group
-hitting *Alt a h* will hide the columns
-hitting *Alt a j* will unhide the columns
-hitting *Shift Alt left arrow key* will remove the group
to group rows:
-hit *Shift Spacebar* to select the desired rows
-follow the other steps above
grouping vs hiding - CORRECT ANSWER another way to hide data is by hitting *Alt h o u r* for rows and *Alt h o u c* for columns, but don't ever use this method because under this method, columns and rows are hidden, but there are no indications (no minus or plus signs as in the first method) as to their location
so it's easy to forget which columns and rows you've hidden
grouping worksheets - CORRECT ANSWER if you want to format data in the same manner or enter the same data across multiple sheets, Excel allows you to do this
by grouping multiple worksheets, any data and formatting that you would perform in one of the grouped worksheets would automatically be reflected in all of them
to group worksheets:
-holding down *Ctrl Shift*, press *PageUp* or *PageDown* to reach the worksheets you would like to group
-all of the grouped worksheets are highlighted and the file name on top of the Excel screen should show [Group] after it
to ungroup worksheets - CORRECT ANSWER press *Ctrl PageUp* or *Ctrl PageDown* until you reach one of the ungrouped worksheets; this automatically groups all of the previously grouped sheets
auditing cells - CORRECT ANSWER go to the cel and hit F2
OR
hitting *Ctrl [* on a cell will highlight the precedent cells
-keep hitting *Ctrl [* and it will take you to the next precedent, and on and on
hitting *Ctrl ]* on a cell will do the same thing but for dependent cells
Excel's formula auditor - CORRECT ANSWER trace precedents: *Alt m p*
-to trace cells that provide data to a formula
-a worksheet icon indicates that the precedent cells are in another worksheet
-double clicking anywhere with a mouse on the black arrow brings up the 'Go To' screen; selecting either of the precedent cell locations and hitting 'Ok' will take you there
trace dependents: *Alt m d*
-to trace formulas that reference a particular cell (the cell highlighted)
remove trace arrows: *Alt m a a*
evaluate formula - CORRECT ANSWER *Alt m v*
track changes - CORRECT ANSWER *Alt r g*
zoom to selection - CORRECT ANSWER *Alt w g*
"go to" - CORRECT ANSWER *F5*
Error: formula treated as text - CORRECT ANSWER forgetting the equal sign (=) at the start or inserting a space before the equal sign of the formula is created by Excel as text
Error: #Div/0! - CORRECT ANSWER dividing a number by 0
Error: #REF! - CORRECT ANSWER an invalid cell reference
Error: #NUM! - CORRECT ANSWER inserting invalid numerical values in a formula or function
Error: #NAME? - CORRECT ANSWER unrecognizable text in a formula
Error: #VALUE! - CORRECT ANSWER inserting incorrect arguments in a function
Error: ##### - CORRECT ANSWER when the column is not wide enough to fit the data in a cell
you must widen the cell to fix the error (*Alt h o i*)
logical functions: IF - CORRECT ANSWER the IF function returns one value if a condition you specify evaluates to TRUE, and another value if it evaluates to FALSE
IF statements follow the same structure = IF(x,y,z) where:
x: criteria
y: output if criteria evaluates to true
z: output if criteria evaluates to false
text output is designated by quotation marks around the outputs
error-trapping function IFERROR - CORRECT ANSWER =IFERROR(value, value_if_error) returns a value you specify if a formula evaluates to an error. If the formula does not result in an error, IFERROR returns the result of the formula
creating dynamic headers by combining cell references with text ("&") - CORRECT ANSWER Excel allow users to combine (or "concatenate") cells with a text string in them with other text strings, creating one text string by using the "&" function
EOMONTH - CORRECT ANSWER =EOMONTH(start_date, months)
allows you to create monthly date headers, by outputting the last day of a specified month
start_date: represents a starting date reference
months: represents x number of months before or after the start_date
to output a date x months before a start_date, x should be negative
EDATE - CORRECT ANSWER =EDATE(start_date, months)
similar to EOMONTH, but the difference is that EDATE returns the exact date, x months from the start date
YEARFRAC - CORRECT ANSWER =YEARFRAC(start_date, end_date, basis) returns the proportion of the year between two given dates (the start_date and the end_date)
basis is an optional parameter that represents the number of days per month/year used to calculate the proportion of the year:
0 or omitted: US 30/360
1: actual/actual
2: actual/360
3: actual/365
4: European 30/360
DATE - CORRECT ANSWER =DATE(year, month, date) is a function that combines distinct year, month, and day elements into a valid date function in Excel
combining this function with DAY(serial number), MONTH(serial number), and YEAR(serial number) is sometimes helpful for creating date functions out of disparate data
cell contents as criteria in IF statements - CORRECT ANSWER a surprisingly useful criteria used in IF statements is a test of whether a cell has anything in it
ex:
the IF statement =IF(C1, C2, C3) would see if there is anything in cell c1, and if there was, it would output C2, otherwise C3
ISNUMBER and ISTEXT - CORRECT ANSWER both typically embedded within an IF statement, they test whether there is number or text inside a cell, respectively
AND - CORRECT ANSWER =AND(logical1, logical2,...) evaluates to true if all of its arguments are true; false if one or more arguments is false
OR - CORRECT ANSWER =OR(logical1, logical2,...) evaluates to true if at least one argument is true
HLOOKUP - CORRECT ANSWER =HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table/array
use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows
VLOOKUP - CORRECT ANSWER =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
searches for a value in the LEFTMOST column of a table and then returns a blue in the same row from a column you specify the table/array
use VLOOKUP when your comparison values are located in a column to the left of a table of data, and you want to look across (and to the right) a specified number of columns
range_lookup - CORRECT ANSWER TRUE: an approximate match to the lookup value is returned
FLASE: an exact match is returned
when lookup value is text or numbers is non-ascending order, the FALSE range is often preferred
OFFSET - CORRECT ANSWER =OFFSET(reference, rows,cols,height,width)
returns a cell value or range of cell values that is a specified number of rows and columns from a cell or range of cells
INDEX - CORRECT ANSWER =INDEX(array, row_num, column_num)
returns a value from a specified row and column from within a table or range
CHOOSE - CORRECT ANSWER =CHOOSE(index_num, value1, value2, value3,...)
returns a number from a specified list of up to 254 values
MATCH - CORRECT ANSWER =MATCH(lookup_value,lookup_array,match_type)
returns the relative position (number) of an item in an array that matches the specified lookup value
it does NOT return the value within the cell itself (as opposed to the HLOOKUP and VLOOKUP functions)
data validation - CORRECT ANSWER data validation is a utility in Excel, whose most frequently used feature is its ability to create simple and quick drop-down menus
to create a drop down menu:
-with the cell where you want your drop down menu active, open the data validation form (*Alt d l*
-within the Settings tab, select list from the dropdown menu
-within the 'Source' field, identify a contiguous cell range containing the data you want to include in your dropdown, and hit OK and you should your dropdown menu appear
note:
it only appears when you are on the active cell
building a vertical data table - CORRECT ANSWER 1. identify the output variable
-the variable you are trying to sensitize is the output variable
-must be referenced from your analysis into the TOP RIGHT CORNER of the data table
2. hard-code the input variable sensitivities
-the variables whose impact on the output variables you want to analyze are the input variables
-input variable assumptions should not be referenced from the analysis, but rather be hard-coded and arranged in the column to the right of the output variable
3. run the data table
-hit *Alt d t*: the Data Table dialog will appear
row input cell: not needed for vertical data tables
column input cell: reference the input variable from the model
-highlight the entire range (including the output variable) and hit OK when done - the data table should populate
-you. may need to hit *F9* if Excel is set to "manual" or "automatic calculations except for data tables"
NOTE:
data tables must always be in the same worksheet as the input variables
building a horizontal data table - CORRECT ANSWER 1. referenced output variable from your analysis into the BOTTOM LEFT CORNER of your data table
2. input the input assumptions in the row above and one cell to the right of the output reference
3. highlight the entire range (including the output variable) and hit *Alt d t*: the Data Table dialog will appear
row input cell: reference the input variable from the model
column input cell: not needed
4. hit OK when done - the data table should populate
building a 2-sided data table - CORRECT ANSWER same as vertical data table, but allows for 2 inputs instead of one
output variable must be referenced from the model into the TOP LEFT CORNER of the data table
now both row input cell and column input cell are needed
SUMPRODUCT - CORRECT ANSWER =SUMPRODUCT(array1,array2,array3,...)
multiplies corresponding components in two or more arrays, and returns the sum of those products
Booleans in Excel - CORRECT ANSWER when Excel spits out a TRUE or FALSE, you can convert them respectively into 1 or 0 by applying any operator on them
multiply the TRUE/FALSE cell by 1: will convert a TRUE to 1 and FALSE to 0
multiply the TRUE/FALSE cell by TRUE: will convert a TRUE to 1 and FALSE to 0
SUMIF - CORRECT ANSWER =SUMIF(range, criteria, sum range)
adds the cells specified by a given criteria
criteria can either be hardcoded (which requires quotation marks) or a direct cell reference (which does not need quotation marks)
SUMIFs - CORRECT ANSWER =SUMIF(range1, criteria1, sum range1, range2, criteria2, sum range2, etc.)
same as SUMIF but can handle multiple criteria and sum ranges
AVERAGEIF and AVERAGEIFs - CORRECT ANSWER identical to SUMIF and SUMIFS but instead of summing, this function averages the data in the range
ABS, CEILING, FLOOR - CORRECT ANSWER ABS(cell)
returns the absolute value of that cell
CEILING(cell, x)
rounds input cell up to nearest x
FLOOR (cell, x)
rounds input down to nearest x
COMBIN - CORRECT ANSWER Returns the number of combinations for a given number of objects
useful accounting application:
sum of years' digits n(n+1)/2
=COMBIN(cell+1,2)
ROUND, ROUNDUP, ROUNDDOWN - CORRECT ANSWER ROUND: rounds most accruately
ROUNDUP: rounds up
ROUNDDOWN: rounds down
MIN and MAX - CORRECT ANSWER =MIN(number1,number2,...) returns the smallest number in a specified set of values)
=MAX(number1,number2,...) returns the largest number in a specified set of values
COUNT - CORRECT ANSWER =COUNT(value1, value2,...)
counts the number of cells that contain numbers within the list of arguments
-cells with text are disregarded
COUNTA - CORRECT ANSWER same as COUNT except cells with numbers and text are counted
COUNTIF - CORRECT ANSWER =COUNTIF(range, criteria) counts the number of items in the range that satisfy a specific criteria
-similar to the SUMIF function
COUNTIFS - CORRECT ANSWER allows for multiple criteria
-similar to the SUMIFS function
PV function - CORRECT ANSWER returns the present value of a series of future payments
=PV(rate, nper, pmt, fv, type)
rate: interest rate per period
nper: total number of payment periods
pmt: payment made during each period (it cannot change)
fv: future value
type: indicates when payments are due (0 or omitted = end of period, 1 = beginning of period)
FV function - CORRECT ANSWER returns the future value of an investment based on constant payment and interest rate
=FV(rate, nper, pmt, pv, type)
rate: interest rate per period
nper: total number of payment periods
pmt: payment made during each period (it cannot change)
pv: present value
type: indicates when payments are due (0 or omitted = end of period, 1 = beginning of period)
NPV function - CORRECT ANSWER =NPV(rate, value1, value 2, ...)
returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values)
values are assumed to occur at the end of each time period and must be referenced in the order in which they occur
NPV vs. PV functions - CORRECT ANSWER PV assumes constant payments, while NPV cash flows can vary from period to period
NPV does not require the user to explicitly identify number of periods and simply assumes equal periods based on the number of values
NPV assumes payments occur at the end of the period (but formula can be adjusted to simulate payments accusing at beginning of period)
XNPV function - CORRECT ANSWER NPV performs equal discounting on each cash flow
=XNPV(rates, values, dates)
returns the net present value for a set of cash flows that don't necessarily occur t equal time intervals
unlike NPV, the first cash flow is not discounted, while dates of subsequent cash flows can be included in the formula in any order
IRR - CORRECT ANSWER =IRR (values, guess)
returns the IRR for a series of values
IRR is the rate corresponding to an NPV of 0
while values don't have to be identical each period, the periods are assumed to be equally far apart
'Guess' is an optional argument: Excel needs a starting point to iterate to the right IRR; if you don't input a guess, Excel will automatically use 10% as the guess
note:
there must be both positive and negative values
XIRR - CORRECT ANSWER =XIRR(values, dates, guess)
returns the IRR for a series of values which may not be periodic
the date of each cash flow must be referenced in the formula (the "dates")
note:
there must be both positive and negative values
Flash Fill - CORRECT ANSWER *Ctrl e*
sorting data - CORRECT ANSWER *Alt d s* (Data tab > Sort)
combining Sort & Subtotal - CORRECT ANSWER Subtotal: *Alt a b*
autofiltering - CORRECT ANSWER *Alt a t*
the filtered rows get hidden but not deleted
if you copy and paste the filtered range to another area of the worksheet or a different worksheet, id doesn't copy over the hidden rows
once a filter is applied, you can tell which column was filtered via the funnel icon
to remove individual filters, click on the filter icon and "Clear filter"
to remove all filters, hit *Alt a t* again
pivot tables - CORRECT ANSWER select any cell inside the table of data and hit *Alt n v*
-Excel will automatically highlight the entire table of data
-select to open the Pivot Table in a new worksheet
a new worksheet will open showing the Pivot Table Field List
-this shows a list of the data's categories and the 4 areas of the Pivot Table (filters, columns, rows, values)
using your mouse, you can drag any of the data categories into any of the 4 areas of the Pivot Table
-as you drag a category into one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to accommodate the newly added category
value field settings (pivot tables) - CORRECT ANSWER VALUES > Field Settings
the Field Settings enables you to show outputs in a variety of different ways (ex: to see counts instead of sums)
the Value Field Settings also has a "Show Values As" tab, which enables you to present the VALUES in a variety of useful ways
recording macros - CORRECT ANSWER rather than repeatedly going through the same series of steps, Excel allows you to record a sequence of instructions, and assign a keyboard shortcut to invoke them as desired
-these instructions are called macros
1. select 'Use Relative References'
2. place the cursor in any cell
3. *Alt l r* brings up "Record Macro" menu
4. input a name and shortcut key (ex: ctrl shift z)
-as soon as you hit "OK" you are starting to record this macro
5. format the cell
6. when finished, hit *Alt l r* to stop recording
now if you go to any other cell and enter the shortcut you inputted (in this case ctrl shift z), those cells' contents should be formatted per your preferences
repeat - CORRECT ANSWER *Ctrl Y*
Outline border - CORRECT ANSWER *Shift Ctrl &*
toggle Excel workbooks - CORRECT ANSWER *Ctrl tab*
Min/restore ribbon - CORRECT ANSWER *Ctrl F1*
print preview - CORRECT ANSWER *Ctrl F2*
close window - CORRECT ANSWER *Ctrl F4*
move to cell "A1" - CORRECT ANSWER *Ctrl Home*
move to cell above - CORRECT ANSWER *Shift Enter*
move to cell to the right - CORRECT ANSWER *tab*
move to cell to the left - CORRECT ANSWER *Shift Tab*
show formulas/values - CORRECT ANSWER *Ctrl ~*
fill selection with entry - CORRECT ANSWER *Ctrl enter*
recalculate all workbooks - CORRECT ANSWER *F9*
get inside a drop-down list - CORRECT ANSWER *Alt + up/down arrow key*
display "Insert Function" box - CORRECT ANSWER *Alt I F (Shift F3)*
display "Name" box - CORRECT ANSWER *Ctrl F3*
Enter array formula - CORRECT ANSWER *Shift Ctrl Enter*
replace - CORRECT ANSWER *Ctrl H*
insert AutoSum formula - CORRECT ANSWER *Alt +" (hold down Alt)
start a new line in same cell - CORRECT ANSWER *Alt Enter*
Welcome to Excel:
-Shortcuts (New Sheet) - CORRECT ANSWER SHIFT + FN11
Welcome to Excel:
-Shortcuts (Auto Column Width) - CORRECT ANSWER ALT HOI
Welcome to Excel:
-Shortcuts (Customize Column Width) - CORRECT ANSWER ALT HOW
*think W for Width
Welcome to Excel:
-Shortcuts (Customize Row Height) - CORRECT ANSWER ALT HOH
*think H for Height
Welcome to Excel:
-Shortcuts (Highlight column) - CORRECT ANSWER CTRL + SPACE
Welcome to Excel:
-Shortcuts (Highlight row) - CORRECT ANSWER SHIFT S+ SPACE
Welcome to Excel:
-Shortcuts (Formatting cells) - CORRECT ANSWER CTRL + 1
Welcome to Excel:
-Shortcuts (Highlighting cells that are hardcoded) - CORRECT ANSWER FN5
ALT + S
Constants --> unclick "Text" [Show Less]