3
Resize the selected chart so it is approximately 12 rows tall. Font Size
Click and drag a resize handle up or down toward the center of the chart to make it smaller.
Move the selected chart to the empty area of the worksheet below the data. Font Size
Click and drag the chart so it appears below the Misc row.
Move the image so it appears near the top of column A. Font Size
Click and drag the image to the left so it appears in first column
Set the print area. Font Size
Click the Back button. On the Page Layout tab, in the Page Setup group, click the Print Area button. Click Set Print Area.
Hide the chart title. Font Size
Click the Chart Elements button and click the Chart Titles check box.
Display the data table, including the legend keys. Font Size
Click the Chart Elements button and click the Data Tables check box.
Hide the primary vertical axis. Font Size
Click the Chart Elements button and click the arrow next to Axes and select Primary Vertical.
Display the data labels on this chart below the data markers. Font Size
Click the Chart Elements button. Click the Data Labels arrow and select Below.
Move the selected chart to a new chart sheet. Font Size
Click the Chart Tools Design tab. In the Location group, click the Move Chart button. Click the New Sheet radio button. Click OK.
Sort this data by the Date of Last Visit column so the oldest dates are listed first. Font Size
Click the Data tab. In the Sort & Filter group, click the Sort A to Z button.
Preview how the worksheet will look when printed. Font Size
Click the File tab to open Backstage view. Click Print.
Print this worksheet. Font Size
Click the File tab to open Backstage view. Click Print. Click the Print button.
Change the print option to print the entire workbook, and then print the workbook. Font Size
Click the File tab to open Backstage. Click Print. Click the Print Active Sheets button. Click Print Entire Workbook. Click the Print button.
Preview how the worksheet will look when printed without a defined print area. Font Size
Click the File tab. Click Print.
Save this workbook in the Excel 97-2003 format. Allow the file to save with compatibility issues. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Excel 97-2003 Workbook (*.xls). Click Save. Click Continue.
Cell F6 has been copied. Paste just the formula without the cell formatting into the selected cell (cell F7). Font Size
Click the Paste button arrow, and then click the Formulas option.
Redo the action last undone by the Undo command to restore the file to its previous state. Font Size
Click the Redo button on the Quick Access Toolbar.
Enter a formula in cell B2 to calculate Ken Dishner's bonus for the first quarter. Multiply his sales total (cell E8) times the bonus rate (the cell named BonusRate). Font Size
Double-click cell B2 to enter Edit mode. Type: =E8* and then type bo to display the Formula AutoComplete list. Double click BonusRate to enter it into the formula and then press Enter.
Enter a formula in cell E2 using AVERAGEIFS to calculate the average price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to the text string "DermoMagic". Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIFS. In the Average_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "DermoMagic". Click OK.
Hide the numbers at the left of each row and the letters at the top of each column. Font Size
On the Page Layout tab, in the Sheet Options group, click the View check box under Headings (to remove the checkmark).
Use the Ribbon to resize the selected picture to exactly .7 inches tall. Allow Excel to adjust the width to maintain the aspect ratio. Font Size
On the Picture Tools Format tab, in the Size group, type .7 in the Height box. Press Enter.
Protect this worksheet so users can select but not edit locked cells. Users should be able to select and edit any cells that are unlocked. Font Size
On the Review tab, in the Changes group, click the Protect Sheet button. Click OK.
This workbook has workbook protection applied. Remove it. Font Size
On the Review tab, in the Changes group, click the Protect Workbook button.
The Downtown worksheet has been protected. Unprotect it. Font Size
On the Review tab, in the Changes group, click the Unprotect Sheet button.
Delete the comment from cell E3. Font Size
On the Review tab, in the Comments group, click the Delete button.
Open the Trust Center dialog and review the settings. Close the Trust Center without making any changes. Font Size
Click the File tab, then click the Options button. Click Trust Center, and the Trust Center Settings... button. Click OK to close the Trusted Locations dialog. Click OK again to close the Excel Options dialog.
Save this file as a macro-enabled template. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Excel Macro-Enabled Template. Click Save. Click OK.
Use the Document Inspector to remove all comments and annotations and personal information and hidden information from this workbook. Close the Document Inspector when you are through. Font Size
Click the File tab. Click the Check for Issues button, and select Inspect Document. Click Yes. Click Inspect. Click the Remove All button next to Comments and Annotations. Click the Remove All button next to Document Properties and Personal Information. Click Close.
Insert a Clustered Column chart based on the first recommended chart type. Font Size
Click the Quick Analysis Tool button, and then click the Charts tab. Click Clustered Column.
A value was just changed in this worksheet. Undo that change. Font Size
Click the Undo button on the Quick Access Toolbar.
Edit the formula in cell B9 so the references to cell E2 will update when the formula is copied, and the reference to cell B8 will remain constant. Use AutoFill to copy the formula to cells B10:B12. Font Size
Double-click cell B9 to edit the formula. Change the formula to be =E2*$B$8). Press Enter. Click the Fill Handle tool and drag down to cell B12. Release the mouse button.
In cell D2, enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name, city, and state. Use a space as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument. Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select TEXTJOIN from the list. Type " " in the Delimiter argument box. Type TRUE in the Ignore_empty argument box. Type A2:C2 in the Text 1 box. Click OK.
Wrap the text in the selected cell. Font Size
On the Home tab, in the Alignment group, click the Wrap Text button.
Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected. Font Size
On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight.
Use Format Painter to copy the formatting from cell E1 and apply it to cell F1. Font Size
On the Home tab, in the Clipboard group, click the Format Painter button. Click cell F1.
In cell D2, use the CONCAT function to combine the values of cells B2 and C2 with a space in between. The result of the formula should look like this: Davidsonville MD Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select CONCAT from the list. Type B2 in the Text1 box. Type " " in the Text2 box. Type C2 in the Text3 box. Click OK.
Cells A3:B7 have been copied. Paste the copied cells into the selected worksheet location (cell D3) so the formulas, formatting, and source cell widths are pasted. Font Size
On the Home tab, in the Clipboard group, click the Paste button arrow, and then click the Keep Source Column Widths option.
Use Formula AutoComplete to enter a SUM function in cell E4 to calculate the total of cells B4:D4. Font Size
Type = in cell E4 to begin the formula. Type SU to shorten the Formula AutoComplete list. Double-click SUM to enter it in your formula. Click and drag to select cells B4:D4. Press ENTER.
Enter a formula in the selected cell using the SUM function to calculate the total of cells B4 through D4. Font Size
Type the formula =SUM(B4:D4) in the cell or the formula bar and press Enter.
Save this workbook as a template. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Excel Template (*.xltx). Click Save.
Remove duplicate rows where data in all the columns are identical. Font Size
Click the Table Tools Design tab. In the Tools group, click the Remove Duplicates button. Click OK. Click OK.
Use AutoSum to enter a SUM function in the selected cell. Font Size
On the Home tab, in the Editing group, click the AutoSum button. Press Enter.
Clear the print area. Font Size
On the Page Layout tab, in the Page Setup group, click the Print Area button. Click Clear Print Area.
Hide the gridlines on this worksheet only. Font Size
On the Page Layout tab, in the Sheet Options group, click the View check box under Gridlines (to remove the checkmark).
Changes have been made to the underlying data for this PivotTable. Refresh the PivotTable data. Font Size
On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.
Modify the status bar so it displays the maximum of the selected cells. Font Size
Right-click the status bar. Click Maximum to add a checkmark for that option.
Remove the maximum value of the selected cells so it does not display on the status bar. Font Size
Right-click the status bar. Click Maximum to remove the checkmark for that option.
Save this file as a macro-enabled workbook in the Data Files folder. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Excel Macro-Enabled Workbook. Click Save. Click OK.
Sort this data alphabetically from A to Z by values in the Last column. Font Size
On the Data tab, in the Sort & Filter group, click the Sort A to Z button.
In cell H1, enter a formula using COUNTIF to count the number of cells in the range named Delivery Time that have a value greater than 14. Remember to enclose the expression in quotation marks. Font Size
On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIF. In the Range argument box, type Delivery Time. In the Criteria argument box, type ">14". Click OK.
Insert the current date and time in the selected cell. Font Size
On the Formulas tab, in the Function Library group, click the Date & Time button. Click NOW. Click OK.
Enter a formula using DSUM to calculate the total value in the Total Spent column for rows that meet the criteria in the criteria range A1:G2. The database is defined by the named range CustomersDB. Font Size
On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. Expand the Or select a category list and select Database. Select DSUM, and click OK. Enter CustomersDB in the Database argument box. In the Field argument box, enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK.
Use the Function Arguments dialog to enter a SUM function in cell E4 to calculate the total of cells B4:D4. Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUM. Click OK in the Function Arguments dialog.
Enter a formula in cell E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument. Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIF. In the Range argument box, type Company. In the Criteria box, type "ColorFab". In the Sum_range argument box, type InStock. Click OK.
Use Solver to find the combination of attendees that will result in the maximum possible profit in cell G1. Use the range name Attendees as the variable cells. Add these constraints in order: values in the named range Attendees are less than or equal to the values in the named range MaxPerClass; values in the named range Attendees are whole numbers; values in the named range TotalAttendees are less than or equal to the values in the named range MaxAttendees. Run Solver and accept the solution. Font Size
On the Data tab, in the Analysis group, click the Solver button. Type G1 in the Set Objective box. (The Max radio button is selected by default.) Type Attendees in the By Changing Variable Cells box, and then click the Add button. Type Attendees in the Cell Reference box. Type MaxPerClass in the Constraint box. Click Add. Type Attendees in the Cell Reference box. Expand the drop-down list in the middle box and select Int. Click Add. Type TotalAttendees in the Cell Reference box. Type MaxAttendees in the Constraint box. Click OK. Click Solve. Click OK.
In cell A2, use Consolidate to create subtotals using the Sum function for values in the named range ItemsOrdered. Include both the top row and left column labels. The named range ItemsOrdered has been defined for you. The consolidation should show the subtotals only and not the details. Font Size
On the Data tab, in the Data Tools group, click the Consolidate button. In the Consolidate dialog, the Sum function is already selected. Type ItemsOrdered in the Reference box. Click the Top row check box. Click the Left column check box. Click OK.
Sort this data by the Visits column so the smallest numbers are listed first. Font Size
On the Data tab, in the Sort & Filter group, click the Sort A to Z button.
Run the FormatTable macro. Font Size
On the Developer tab, in the Code group, click the Macros button. In the Macro dialog, FormatTable is selected. Click the Run button.
Record a new macro with the name ConvertTable. Do not assign a shortcut key or description. The macro should convert the table to a range using the command on the Table Tools Design tab, Tools group. Font Size
On the Developer tab, in the Code group, click the Record Macro button. Type ConvertTablein the Macro name box. Click OK. On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. On the Developer tab, in the Code group, click the Stop Recording button.
Edit the Bonuses named range so it refers to cells B2:B5 on the Bonus worksheet. Close the Name Manager when you are finished. Font Size
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click in the Refers to box and edit the cell range reference to =Bonus!$B$2:$B$5. Click the checkmark to the left of the Refers to box to accept the change. Click the Close button.
In cell H2, enter a formula using COUNTIFS to count the number of rows where values in the range named Delivery Time have a value greater than 14 and cells in the range named ReorderStatus display "no". Font Size
On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIFS. In the Criteria_Range1 argument box, type Delivery Time. In the Criteria1 argument box, type ">14". In the Criteria_Range2 argument box, type ReorderStatus. In the Criteria2 argument box, type "no". Click OK.
Insert the current date in the selected cell. Do not include the current time. Font Size
On the Formulas tab, in the Function Library group, click the Date & Time button. Click TODAY. Click OK.
In cell B14, enter a formula using NPV to calculate the value today (the present value) of the tuition payment option 3. Use cell B7 as the Rate argument and the cell range B10:B13 as the Value1 argument. Use cell references for all values. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click NPV. Enter B7 in the Rate box. Enter B10:B13 in the Value1 box. Click OK.
In cell B10, enter a formula using PV to calculate the value today (the present value) of the four-year tuition plan. Use cell references wherever possible. The annual interest rate for your investment account is stored in cell B8, the number of monthly payments in cell B7, and the monthly payment amount in cell B6. Payments will be made at the beginning of every period. Pay attention to the time periods for the interest rate and payment schedule. Remember to express the Pmt argument as a negative. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click PV. Enter B8/12 in the Rate box. Enter B7 in the Nper box. Enter -B6 in the Pmt box. Type 1 in the Type box. Click OK.
Enter a formula using DAVERAGE to calculate the average value in the Total Spent column for rows that meet the criteria in the criteria range A1:G2. The database is defined by the named range CustomersDB. Font Size
On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. Expand the Or select a category list and select Database. Select DAVERAGE, and click OK. Enter CustomersDB in the Database argument box. In the Field argument box, enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK.
Enter a formula in cell C2 to return a value of yes if the value in cell E8 is greater than or equal to the value in B2 or no if it is not. Font Size
On the Formulas tab, in the Function Library group, click the Logical button. Select IF. Enter E8>=B2 in the Logical_test box. Enter yes in the Value_if_ true box, and no in the Value_if_false box. Click OK.
Enter a formula in cell E2 using SUMIFS to calculate the total price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to "Salon Supplies". Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIFS. In the Sum_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "Salon Supplies". Click OK.
Enter a formula in cell E1 using AVERAGEIF to calculate the average quantity in stock for items from the company DermoMagic. Use the range name Company for the Range argument, the text string "DermoMagic" for the Criteria argument, and InStock for the Average_range argument.
On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIF. In the Range argument box, type Company. In the Criteria box, type "DermoMagic". In the Average_range argument box, type InStock. Click OK.
Using the Replace command, find and replace all cells formatted with the Accounting Number Format with the Currency format. Close the Find and Replace dialog when you are finished. Font Size
On the Home tab, in the Editing group, click the Find & Select button and select Replace... Click the Options button. Click the Format... button next to the Find what box. In the Find Format dialog, select Accounting in the Category list. Click OK. Click the Format... button next to the Replace with box. Select Currency in the Category list. Click OK. Click Replace All. Click OK in the message that appears. Click Close.
Find all instances of the word Spa Pool in this worksheet. Font Size
On the Home tab, in the Editing group, click the Find & Select button, and then click Find. Type Spa Pool in the Find what box. Click the Find All button.
Replace all instances of the word Spa Pool in this worksheet with Pool. Do not replace them one at a time. Close the dialog when you are finished. Font Size
On the Home tab, in the Editing group, click the Find & Select button, and then click Replace. Type Spa Pool in the Find what box. Type Pool in the Replace with box. Click the Replace All button. Click OK. Click the Close button.
Clear the conditional formatting rules from the entire worksheet. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Clear Rules. Click Clear Rules from Entire Sheet.
Change the worksheet margins to the Narrow option. Font Size
On the Page Layout tab, in the Page Setup group, click the Margins button. Click Narrow.
Set row 1 to print on every page. Font Size
On the Page Layout tab, in the Page Setup group, click the Print Titles button. In the Page Setup dialog, on the Sheet tab, click in the Rows to repeat at top box. Click the row selector for row 1. Click OK in the dialog.
Change the scaling option so all columns will print on one page. Font Size
On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.
Modify the PO Q4 worksheet so gridlines will print. Font Size
On the Page Layout tab, in the Sheet Options group, click the Print check box under Gridlines.
Change the data source for this PivotTable to use the table named CustomersTable. Font Size
On the PivotTable Tools Analyze tab, in the Data group, click the Change Data Source button. Type CustomersTable in the Table/Range box. Click OK.
Apply workbook protection to this workbook. Font Size
On the Review tab, in the Changes group, click the Protect Workbook button. Click OK.
Remove sharing from this workbook. Font Size
On the Review tab, in the Changes group, click the Share Workbook button. In the Share Workbook dialog, click the Allow changes by more than one user at a time check box to remove the check mark and disallow sharing. Click OK. Click Yes to save the workbook and remove sharing.
Enable sharing so more than one person at a time can work on this workbook. Font Size
On the Review tab, in the Changes group, click the Share Workbook button. In the Share Workbook dialog, click the Allow changes by more than one user at a time check box. Click OK. Click OK to save the workbook and add sharing.
Turn on track changes to highlight all changes made in this workbook since it was last saved. Font Size
On the Review tab, in the Changes group, click the Track Changes button. Click Highlight Changes¿ Click the Track changes while editing check box. Expand the When box and select Since I last saved. Click OK. Click OK to save the workbook.
Add this comment to cell B4: Why did advertising increase? Font Size
On the Review tab, in the Comments group, click the New Comment button. Type: Why did advertising increase? Click outside the balloon.
Display all the comments in this worksheet at once. Font Size
On the Review tab, in the Comments group, click the Show All Comments button.
Split the worksheet into four panes at the selected cell. Font Size
On the View tab, in the Window group, click the Split button.
This worksheet has been split into four panes. Remove the split. Font Size
On the View tab, in the Window group, click the Split button.
