MGSC Exam 1
use autofill to copy the formula and formatting in cell B7 to cells C7:E7
click in cell B7, click the fill handle tool and drag across to cell E7. release the mouse button
switch to the view that shows all the worksheet elements as they will print, including headers and footers
click the page layout button at the lower right corner of the status bar, to the left of the zoom cider
add an element to the center section of the header that will always display the current date
click the page layout button on the status bar. click in the center section of the header area above the worksheet grid. on the header & footer tools design tab, in the header & footer elements group, click the current date button
cell F2 has been copied. paste the formula only into the selected cell (cell F3). Do not include the cell formatting
click the paste button arrow, and then click the formulas option
the selected column is too narrow to display all the values. auto fit the column to best fit the data
double-click the right column boundary for column E
enter a formula in the selected cell using SUMIF to calculate the total expenses for the category office expense. Use the range name category for the range argument, the text string "office expense" for the criteria argument, and cost for the sum range argument
on the formula tab, in the function library group, click the Math & Trig button. click SUMIF. in the range argument box, type category. in the criteria box, type "office expense". in the sum range argument box, type cost. click ok
preview how this worksheet would print with formulas showing instead of calculated values
on the formulas tab in the formula auditing group, click the show formulas button. click the file tab. click print
use the create from selection command to create named ranges for the data table B2:E6 using the labels in row 1 as the basis for the names
on the formulas tab, in the defined names group, click the create from selection button. the top row check box is checked by default. click ok
show the tracer arrows for the selected cell to display arrows to the cells containing formulas that reference the value or formula in the selected cell
on the formulas tab, in the formula auditing group, click the trace dependents button
in cell E15, enter a formula to find the lowest line item cost this month (cells E2:E14)
on the formulas tab, in the function library group, click the autism arrow, and click min. press enter
using cell references, enter a formula in cell B6 to calculate monthly payments for the lan described in this worksheet. the annual interest rate is stored in cell B3, the number of payments in cell B4, and the loan amount in cell B2. omit the optional arguments. the result of the formula should be a negative number
on the formulas tab, in the function library group, click the financial button, and click PMT. enter B3/12 in the rate argument box. enter b4 in the Nper argument box. enter B2 in the argument box. click ok.
enter a formula in cell B28 to return a value of 25,000 if the net profit after tax (cell B27) is greater than or equal to 250,000 or 1,000 if cell B27 is less than 250,000
on the formulas tab, in the function library group, click the logical button, select IF from the list. enter B27>=250000 in the logical test box. enter 25000 in the value if true box, and 1000 in the value if false box. click ok
enter a formula in the selected cell using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current month rents by the remaining months on each lease. use the range names rents and leases
on the formulas tab, in the function library group, click the math & trig button. click SUMPRODUCT. in the Array1 argument box, type rents. in the Array2 argument box, type leases. click ok
edit the formula in cell C2 so that references to cell B2 will update when the formula is copied, and the reference to cell B9 will remain constant
Double-click cell C2 to edit the formula. change the formula to be = B2+(B2^$B$9). Press enter
Enter a formula in cell B4 using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A4. Use the name abbreviation for the look up table. The item names are located in column 2 of the lookup table. be sure to require an exact match
On the formulas tab, in the function library group, click the lookup & reference button, and select VLOOKUP. type A4 in the lookup value agreement box. type abbreviation in the table array argument box. type 2 in the col num argument box. type false in the range lookup box. click okay
in cell D14, enter a formula using a counting function to count the number of cells in the billable? column that are not black (Cells D2:D13)
on the formulas tab, in the function library group, click the more functions button. point to statistical and select COUNTA from the list. type D2:D13 in the value 1 box. click ok
change the angle of rotation for the selected cells to 45 degrees
on the home tab, in the alignment group, click te orientation button, and deleted angle counterclockwise
wrap the text in the selected cell
on the home tab, in the alignment group, click the wrap text button
use format painter to copy the formatting from cell D1 and apply it to cell E1
on the home tab, in the clipboard group, click the format painter button. clicl cell E`
clear only the formatting from the selected cell (leaving content)
on the home tab, in the editing group, click the clear button, select clear formats
add a blue, accent 1 color, bottom border to the selected cells, use the thickest single line style available
on the home tab, in the font group, click the borders button arrow, and select more borders. in the format cells dialog, border tab, click the thickest line available in the style section. expand the color pallet and select blue accent 1. in the border section, click the button represent a bottom border
modify the number format so no decimal places are visible after the decimal point
on the home tab, in the number group, click the decrease decimal button twice
without adjusting the column widths, guarantee that all columns will print on one page
on the page layout tab, in the scale to fit group, click the width arrow. click page 1
modify the worksheet so the first row is always visible when you scroll the worksheet
on the view tab, in the window group, click the freeze panes button, click freeze top row
this worksheet has been split into four panes. return the worksheet to a single view
on the view tab, in the window group, click the split button
name cell B9 as follows: COLA
type COLA in the name box to the left of the formula bar. press enter
