Chapter 1
Fill Handle- copying a formula from one cell to a range of cells.
Formatting is under Home -> Cell Styles (preselect cells)
Merge & Center button
To select multiple cells, press the Control key and select.
Accounting Format $ is not by number, Currency Format the $ is next to number.
Move Chart puts it on a new worksheet.
Double click on pie sections to change the color.
Chapter 2
You cannot sum a percentage. You repeat the formula.
AutoSum button also contains Average, Count Numbers,
Max, Min & other functions.
Format Painter is under Home.
Applying a border is under the Home -> Alignment
Change Row Height, Autofit Column/ Row is under Format.
Conditional Formatting -> Highlight Cell Rules then select
data
CTRL+ Accent Mark (`)-top, left on keyboard- will display
the worksheet with formulas. Press again to get back to
values.
Chapter 3
$B$4- Absolute Reference (both
In Home ->Alignment, you can rotate the text to 45 degrees for example.
Increase or decrease indented text within a cell. column and row remain the same)
=NOW () will give a system date. Then can format cell to date and pick type of B4- Relative Cell Reference (when
format (ex:03/14/2019 or 14- Mar) copied, both the column and row
IF Function: are changed)
=IF(logical test, value 1 if the test if true, value 2 if the test if false) B$4- Mixed Reference (column
=IF(5>4,1,2) would be 1 changes, row remains)
=IF(5<4,1,2) would be 2 $B4- Mixed Reference (Column
=IF(5>4, “Good”, “Bad”) remains, row changes)
=IF(5<4, A1, A2) to reference cell
=IF(5*3>4+2, 5+1, 6+3)
Column Sparkline Chart- place cursor in cell ->Insert ->Column Chart ->Select range. Can change colors under Design tab. Can use
Fillhandle.
View -> Zoom
Split is also under View into several windows.
Freeze Pane is under View. Place cursor in column or row after the area to be frozen.
What-If or sensitivity analysis to analyze changes to the worksheet for changing values
Goal Seeking to see what you need to do to achieve the goal. It is under the What-If Analysis. Box will open to set the cell to a certain
amount by changing another specific cell. Can go back to original information by clicking Cancel.
Chapter 4
Create Names for selections in the Formulas tab -> Create Selection
=FV(rate, periods, payment) : Returns the future value of an investment based on periodic, constant payments and interest rate.
o Must be on same basis (month (*12) or year) ; put – in front to make a positive number
=PMT(rate,periods,loan amount) : Calc the payment for a loan based on the amount, constant payments and interest rate.
o Must be on same basis (month (*12) or year); put – in front to make a positive number
=PV(rate,periods,payment) : Returns the present value of an investment-total amount that a series of future payments is worth
o Must be on same basis (month (*12) or year);
Total Interest= (Payment *Term *12(if monthly))- loan amount
Data Table- can reference column or row
Amortization Schedule- beginning amount, ending balance, what is to principle, interest and interest over life of loan
o Ending balance is PV(rate/12, (Term-year on schedule)*12, monthly pmt)
o Paid on principle is beginning balance- ending balance
o Interest paid is (Monthly payment *12)- paid on principle
Chapter 6 source was downloaded by 100000818837220 from CourseHero.com on 02-08-2022 04:54:12 GMT -06:00
This study
https://www.coursehero.com/file/45452946/MidTerm-Cheat-Sheetdocx/
Fill Handle- copying a formula from one cell to a range of cells.
Formatting is under Home -> Cell Styles (preselect cells)
Merge & Center button
To select multiple cells, press the Control key and select.
Accounting Format $ is not by number, Currency Format the $ is next to number.
Move Chart puts it on a new worksheet.
Double click on pie sections to change the color.
Chapter 2
You cannot sum a percentage. You repeat the formula.
AutoSum button also contains Average, Count Numbers,
Max, Min & other functions.
Format Painter is under Home.
Applying a border is under the Home -> Alignment
Change Row Height, Autofit Column/ Row is under Format.
Conditional Formatting -> Highlight Cell Rules then select
data
CTRL+ Accent Mark (`)-top, left on keyboard- will display
the worksheet with formulas. Press again to get back to
values.
Chapter 3
$B$4- Absolute Reference (both
In Home ->Alignment, you can rotate the text to 45 degrees for example.
Increase or decrease indented text within a cell. column and row remain the same)
=NOW () will give a system date. Then can format cell to date and pick type of B4- Relative Cell Reference (when
format (ex:03/14/2019 or 14- Mar) copied, both the column and row
IF Function: are changed)
=IF(logical test, value 1 if the test if true, value 2 if the test if false) B$4- Mixed Reference (column
=IF(5>4,1,2) would be 1 changes, row remains)
=IF(5<4,1,2) would be 2 $B4- Mixed Reference (Column
=IF(5>4, “Good”, “Bad”) remains, row changes)
=IF(5<4, A1, A2) to reference cell
=IF(5*3>4+2, 5+1, 6+3)
Column Sparkline Chart- place cursor in cell ->Insert ->Column Chart ->Select range. Can change colors under Design tab. Can use
Fillhandle.
View -> Zoom
Split is also under View into several windows.
Freeze Pane is under View. Place cursor in column or row after the area to be frozen.
What-If or sensitivity analysis to analyze changes to the worksheet for changing values
Goal Seeking to see what you need to do to achieve the goal. It is under the What-If Analysis. Box will open to set the cell to a certain
amount by changing another specific cell. Can go back to original information by clicking Cancel.
Chapter 4
Create Names for selections in the Formulas tab -> Create Selection
=FV(rate, periods, payment) : Returns the future value of an investment based on periodic, constant payments and interest rate.
o Must be on same basis (month (*12) or year) ; put – in front to make a positive number
=PMT(rate,periods,loan amount) : Calc the payment for a loan based on the amount, constant payments and interest rate.
o Must be on same basis (month (*12) or year); put – in front to make a positive number
=PV(rate,periods,payment) : Returns the present value of an investment-total amount that a series of future payments is worth
o Must be on same basis (month (*12) or year);
Total Interest= (Payment *Term *12(if monthly))- loan amount
Data Table- can reference column or row
Amortization Schedule- beginning amount, ending balance, what is to principle, interest and interest over life of loan
o Ending balance is PV(rate/12, (Term-year on schedule)*12, monthly pmt)
o Paid on principle is beginning balance- ending balance
o Interest paid is (Monthly payment *12)- paid on principle
Chapter 6 source was downloaded by 100000818837220 from CourseHero.com on 02-08-2022 04:54:12 GMT -06:00
This study
https://www.coursehero.com/file/45452946/MidTerm-Cheat-Sheetdocx/
