You Can Press the Ctrl+accent Mark(`) Keys to Display the Formulas in a Worksheet.

1. Charts (chapter 1)

a. Insert Select nautical chart blazon

b. Right click to change title and change mode.

c. MOVE Chart: Under chart tools Design click move nautical chart buttonnew sail

2. Formulas (chapter 2)

a. Gross pay=Hours worked*pay rate

b. Federal taxation= 22% * (gross pay – dependents * 24.32)

c. Country taxation= iv% * gross pay

d. Net pay= gross pay – (federal tax + state tax)

e. Tax %= (federal tax + land tax)/gross pay

f. TAX % CANNOT BE SUMMED!!!

i. =(Federal Tax + State Tax) / Gross Pay

g. Press CTRL+Emphasis MARK (`) to display the worksheet with formulas

h. After viewing the formulas version, press CTRL+ACCENT MARK (`) to instruct Excel to display the values version

i. Note Accent Mark is located at the upper left corner of the central lath, above the tab key.

iii. Worksheets, Charting & What-If Analysis (Chapter three)

a. Rotating text Home Alignment extension

b. Enter system date =Now()

c. Absolute $B$iv both column and row remain the same when you copy the cell. (B is the column, 4 is the row.)

d. IF Function: =IF(logical exam, value 1 if the test is true, value 2 if the test is false)

e. Operating income=gross margin-total expenses

f. Freezing column & row titles: View tab  Freeze Pane

g. What-If Analysis: Data tab  What-If Analysis  Goal Seek  set jail cell (which one yous're looking at), value (goal is), by irresolute cell

(what cell will get y'all to that goal)  OK  CANCEL

4. Fiscal Functions, Data Tables and Amortization Schedules (Chapter iv)

a. Creating names based on row titles: Formulas tab  create from selection  left cavalcade checked

b. Loan amount = price-down payment

c. =PMT(interest charge per unit, numbers of periods, loan corporeality)

i. All arguments must be on the same basis (calendar month or year)

ii. =PMT(6%/12, 3*12, 50000)

d. =FV(interest charge per unit, numbers of periods, PMT)

i. Same basis (calendar month or year)

ii. =FV(6%/12, 4*12, 200)

east. =PV(interest rate, numbers of periods, PMT)

i. Same ground (month or year)

f. Total Interest = (monthly payment*term*12)-loan amount

g. Total cost = downwards payment + loam amount + total involvement

h. Information table  data  what-if analysis  data table

i. Conditional formatting  home  conditional formatting

j. Amortization Schedule

i. Beginning of the loan; ending balances of a loan; corporeality of payment that practical to the principal and the interest for each

period over the life of the loan.

1. Get-go balance is loan amount.

2. Ending remainder = -PV(rate/12, (term-year)*12, monthly_payment)

3. Paid on principal = Beginning residuum – catastrophe balance

4. Interest paid = amount paid for the first year – the amount considered equally the payment towards principal

5. Creating, Sorting and Querying a Table (Chapter 6)

a. Formatting range as table  insert  table part  put a check mark

b. Ready validation: information tab  data validation  select list  commercial, residential, vacation

c. Adding a conditional formatting rule w/ icon set: blueprint  conditional formatting  new dominion

d. Total row check box: design  full row check box

e. VLookup Function: vlookup(a jail cell contains a value to look, a range contains vlookup table, a column number of the vlookup table that

the information will be retrieved from)

i. The left cavalcade needs to be one value, not a range

ii. The left column needs to exist sorted from minimum to maximum (if the data is number)

iii. The left column needs to be sorted ascending from A to Z (if the information is text)

four. Define tabular array name: formulas  define proper name  name cannot infinite

f. Using advanced filter: Unlike using the drib-downwards arrow on the side of header to TEMPORARY filter the data to display only the data

that match the condition, the Advance Filter can exist used to PERMANENTLY retrieved the data and place it in another area.

i. Criteria expanse – this area contains information about what the conditions are

ii. Information surface area – this expanse contains all data that we need to call back the specific information from

three. Output or Extracted area – this area is the area that we like to display the data nosotros retrieve.

g. =DAVERAGE(table range, "field proper noun", criteria range)

i. COUNTIF – this role will render count value in the range only if they encounter a criteria

ii. SUMIF – this function will return sum value in the range only if they meet a criteria

iii. AVERAGEIF - – this function will return average value in the range only if they run into a criteria

6. Macros and Visual Basic for Applications (VBA)

a. Developer tab  design fashion

i. One macro is added to format the information in the data ii to data 5 worksheet

2. Two macros are added to divide names into a right format

b. Recording a macro

i. Programmer tab  record macro

1. Don't switch tabs while recording

c. Benefits of Using Macros

i. A macro can be created to memorize all stepdue south conducted after clicking Start Recording until Stop Recording.

two. And so, a macro works well for whatever procedure that will be repeated again such as:

three. Dissever data every bit we just larn from previous slides

four. Format data into specific format

v. Setting printing expanse, etc.

d. Things to be aware of:

westknetch.blogspot.com

Source: https://www.studocu.com/en-us/document/texas-tech-university/intermediate-accounting-i/mid-term-cheat-sheet-3/3178815

Share this post