MGSC Exam 1 Flashcards

Questions and Answers

How do you edit the formula in cell C2 to update references?

= B2+(B2^$B$9)

What formula should be entered in cell B4 using the VLOOKUP function?

VLOOKUP(A4, abbreviation, 2, false)

How do you use SUMIF to calculate total expenses for the category 'office expense'?

SUMIF(category, 'office expense', cost)

What is the procedure to add a blue, accent 1 bottom border to selected cells?

Select More Borders from the Borders button and choose the thickest single line style in blue accent 1. Signup and view all the answers

How do you make the first row always visible in a worksheet?

Click Freeze Top Row in the Window group. Signup and view all the answers

What does clicking the Trace Dependents button do?

Displays arrows to cells containing formulas that reference the selected cell. Signup and view all the answers

How do you preview a worksheet with formulas showing instead of calculated values?

Click Show Formulas on the Formulas tab. Signup and view all the answers

What formula should be entered in cell B6 to calculate monthly payments for a loan?

PMT(B3/12, B4, B2) Signup and view all the answers

How do you clear only the formatting from the selected cell?

Click Clear Formats from the Clear button in the Editing group. Signup and view all the answers

What steps do you need to follow to change the angle of rotation for selected cells to 45 degrees?

Click the Orientation button in the Alignment group, and select the angle option. Signup and view all the answers

How do you use Autofill to copy the formula and formatting in cell B7 to cells C7:E7?

Drag the fill handle from cell B7 to cell E7. Signup and view all the answers

How do you paste the formula only into cell F3 from cell F2?

Click the Paste button arrow and choose Paste Formulas. Signup and view all the answers

How do you use Format Painter to copy formatting from cell D1 to cell E1?

Click Format Painter and then click cell E1. Signup and view all the answers

How do you modify the number format so no decimal places are visible?

Click the Decrease Decimal button twice in the Number group. Signup and view all the answers

How do you auto-fit a column to best fit the data?

Double-click the right column boundary for the column. Signup and view all the answers

What procedure allows you to wrap text in a selected cell?

Click the Wrap Text button in the Alignment group. Signup and view all the answers

How can you switch to a view that shows all worksheet elements as they will print?

Click the Page Layout button in the status bar. Signup and view all the answers

What steps should you take to return a split worksheet to single view?

Click the Split button in the Window group. Signup and view all the answers

How do you ensure that all columns print on one page without adjusting column widths?

Click the Width arrow in the Scale to Fit group and select Page 1. Signup and view all the answers

How do you add an element to the center section of the header that displays the current date?

Click the Current Date button on the Header & Footer Tools design tab. Signup and view all the answers

What formula do you enter in cell B28 to return a value based on the net profit after tax?

IF(B27>=250000, 25000, 1000) Signup and view all the answers

Which formula calculates the total value of current leases in a selected cell using SUMPRODUCT?

SUMPRODUCT(rents, leases) Signup and view all the answers

How do you create named ranges for the data table B2:E6 using the labels in row 1?

Click Create from Selection in the Defined Names group. Signup and view all the answers

What steps should you follow to name cell B9 as 'COLA'?

Type COLA in the name box next to the formula bar and press Enter. Signup and view all the answers

How do you enter a formula in cell D14 to count the number of non-black cells in the 'billable?' column?

COUNTA(D2:D13) Signup and view all the answers

How do you enter a formula in cell E15 to find the lowest line item cost this month?

MIN(E2:E14) Signup and view all the answers

Study Notes

Excel Formula and Function Techniques

Edit formula in cell C2 to update references dynamically with = B2+(B2^$B$9).
Use VLOOKUP in cell B4 to find meanings for medical abbreviations with =VLOOKUP(A4, abbreviation, 2, FALSE) for exact matches.
Calculate total office expenses in a selected cell using SUMIF: =SUMIF(category, "office expense", cost).
Apply a blue, accent 1 bottom border to cells by selecting the thickest line style in the borders menu.
Keep the first row visible while scrolling by using the Freeze Panes option in the View tab.

Excel Visualization and Formatting Skills

Show tracer arrows for dependencies in formulas via the Trace Dependents button.
Preview worksheets to print formulas instead of values by selecting the Show Formulas option.
Use PMT function in cell B6 for calculating loan payments with =PMT(B3/12, B4, B2), returning a negative result.
Clear formatting from a selected cell without affecting its content by selecting Clear Formats from the Home tab.
Rotate text in selected cells to 45 degrees by adjusting orientation in the Alignment group.

Data Management and Presentation

Autofill from cell B7 to E7 using the fill handle for quick formula copying.
Paste only the formula from cell F2 into F3 without formatting by using the Formulas paste option.
Copy formatting from D1 to E1 using the Format Painter from the Clipboard group.
Remove decimal places in a number format by clicking the Decrease Decimal button in the Home tab.
Auto fit column widths for better data visibility by double-clicking the right boundary of the desired column.

Wrap text in a selected cell to enhance readability through the Wrap Text option.
Switch to Print Preview by clicking the Page Layout button in the status bar for an overview.
Return to a single view from split panes by using the Split button in the View tab.
Ensure all columns fit on one page without adjusting widths by setting Width to Page 1 in the Page Layout tab.
Add the current date to the header center section with a dedicated button in the header tools.

Conditional Logic and Data Analysis

Create a formula in cell B28 using IF function: =IF(B27>=250000, 25000, 1000) for conditional returns based on net profit.
Calculate current lease values using SUMPRODUCT: =SUMPRODUCT(rents, leases) for financial analysis.
Utilize the Create from Selection command to name ranges based on row labels for efficient data reference.
Name cell B9 as COLA directly in the Name Box next to the formula bar.
Count non-black cells in the Billable? column within cells D2:D13 using COUNTA function: =COUNTA(D2:D13).
Identify the lowest line item cost in cells E2:E14 using the MIN function: =MIN(E2:E14).

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Prepare for your MGSC Exam 1 with these flashcards designed to help you master important Excel functions like formula editing and VLOOKUP. Each card provides concise definitions and practical examples to reinforce your learning. Test your knowledge and improve your skills efficiently!