GMETRIX REVIEW EXCEL
Merge and center the Student Budget heading across columns A through M.
Click and drag to select cells A1 through M1 In the Home tab, Alignmentgroup, click the Merge & Center button
In the Expenses worksheet, change Column B to have a font size of 8.
1. In the Expenses worksheet, Select all of Column B. in the Home tab, locate the Font group, and change the Font Size dropdown to 8.
Add a Dark Blue border to the image.
1. Select the image. in the Picture Format contextual tab, locate the Picture Styles group, click Picture Border, and in the drop down select Dark Blue.
Calculate the total for cells B4, B5, and B6 in cell B8. AutoFill that total through cell M8. Then, in cell B9, calculate the sales tax through the rate in cell Q2. AutoFill those values through to cell M9.
Click in cell B8 Type: =B4+B5+B6 Click the fill handle in cell B8 and drag it through cell M8 Click in cell B9 Type: =B8*$Q$2 Click the fill handle in cell B9 and drag it through cell M9
Insert the Basic Process SmartArt graphic below the data in this worksheet.
1. Select any cell below the data. In the Insert tab, locate theIllustrations group, click SmartArt, and select Basic Process from theProcess section.2. Click OK.
Split the worksheet at cell A5 into two horizontal panes.
1. Select cell A5.2. In the View tab, Windowgroup, click the Split button.
Add the title Average to column H.
1. Select the cell H3 and typeAverage.
Remove all conditional formatting from the current worksheet.
In the Home tab, Styles group, click the Conditional Formatting drop-down button Hover the mouse over Clear Rules Click Clear Rules from Entire Sheet
In Annual Sales worksheet, insert a columnSparkline in the cell G6 that references the data in the cells B6 through E6.
1. Highlight cell G6. in the Inserttab, locate the Sparkline group, and click Column.2. In the dialog box for the Data Range field enter: B6:E6 and clickOk.
Freeze the top two rows of the current worksheet.
Select row 3 Click the View tab In the Window group, click the Freeze Panesdrop-down arrow Click Freeze Panes
Ensure that row and column headings and gridlines display when printing the worksheet.
1. In the Page Layout tab, Sheet Options group, select the Printcheck boxes for Gridlines andHeadings.
Set the tab color of the Summaryworksheet to Red, Accent 2, Lighter 40%.
1. Open the Summaryworksheet.2. In the Home tab, Cellsgroup, click the Formatbutton. Under Organize Sheets, select Tab Color > Theme Colors > Red, Accent 2, Lighter 40%.
In the Mileage Log worksheet, in cell J6, use a formula that subtracts G6 from H6 and multiplies the result by .36.
1. Select cell J6, enter the the formula, =(H6-G6)*.36. Press Enter.
In the Marketing Budget Planworksheet, add Column Sparklines in cells E10, E18 and E25 that use the data from cells D5:D8 , D13:D16 and D21:D23.
1. In the Marketing Budget Plan worksheet, select cell E10. 2. In the Insert tab, locate the Sparklines group, and click Column.3. In the Data Range, insert or select D5:D8, and click OK.4. Repeat steps 1 to 3 for E18using the data in D13:D165. Repeat steps 1 to 3 for E25using the data in D21:D23
With the cell range C3:J26, apply conditional formatting using blue gradient data bars.
1. Select cells C3:J26.2. In the Home tab, Styles group, click the drop down for Conditional Formatting and choose Data Bars > Gradient Fill > Blue Data Bar.
In the Mileage Log worksheet, Hide column J.
1. Select column J, in theHome tab, go to the Cellsgroup, click the Formatdrop down, select Hide & Unhide and select Hide Columns
Unmerge any merged cells in row 2 of the Review worksheet.
1. Select row 2 by clicking on the2 to the left of Cell A2.2. In the Home tab, locate theAlignment group, and click Merge and Center to remove the merges already in place.
In cell B9, multiply the contents of cell B8 and the named range, TaxRate. Then, AutoFill the formula to cell M9.
Click in cell B9 Type: =B8 * TaxRate Click the fill handle in cellB9 and drag it through cellM9
Display all of the formulas on the current worksheet.
Click the Formulas tab In the Formula Auditinggroup, click Show Formulas
Change the format for the numbers in cells B4 through M6 to show with dollar signs and decimal points with two numbers after the decimal point.
Select cells B4 through M6 In the Home tab, Number group, click the $
In the 2010 Sales worksheet, merge and center the content in the top row from column A to G.
1. Open the 2010 Sales.2. Select cells A1:G1.3. In the Home tab, Alignmentgroup, select Merge & Center.
In the June worksheet, apply Table Style Light 4 to the cells A3 through D34.
1. Select cells A3:D34 in the June worksheet.2. In the Home tab, go to theStyles group and click theFormat as Table drop down menu.3. In the drop down menu under the Light heading select Table Style Light 4.4. click OK.
Hide the label and data in the San Diego Satellite Office rows.
1. Select cells A48:G652. In the Home tab, Cells group, click the drop down for Format.3. In the Visibility section, choose the Hide & Unhide > Hide Rowsoption.
Format the cell range A5:D106 to use center horizontal alignment.
1. Select cells A5:D106.2. In the Home tab, Alignmentgroup, click the Center button.
Change the format of the cell containing the company name FusionTomo Inc. to the cell style Title.
1. Select the title cell. In the Home tab, locate the Styles group, click Cell Styles and click Title under the Titles and Headingssection.
Set the print area to include only cells in the Verigon Pharmaceuticals Quarterly Earningstable.
1.Select cells A1:K272. In the Page Layout tab, Page Setup group, click Print Area and choose the Set Print Area option from the drop-down list.
Assign a named range named, TaxRate, to cell Q2.
Click in cell Q2 Click in the Name box Type: TaxRate Press the Enter key
In cell Q2, insert a function that displays a YES if the total number of morning classes for January is at least 175 and a NO if not.
Click in cell Q2 Click the Formulas tab In the Function Library group, click the Logical drop-down arrow Click IF In the Logical_test field type: SUM(S6:S20)>=175 Click in the Value_if_true field Type: YES Click in the Value_if_false field Type: NO Click the OK button
In the table on the current worksheet, add a column with the name, Item Type, in between the Item and Clearwater columns. Then, add a row with an item name of Dumbbells in between the rows for Climbers and Treadmills.
Right-click any cell between B3and B11 Point to Insert Click Table Columns to the Left Click in cell B2 Type: Item Type Right-click any cell between A8and F8 Point to Insert Click Table Rows Above Click in cell A8 Type: Dumbbells Press the Enter key or click away from cell A8
In cell B18, enter a cell reference to cell A7.
1. In cell B18, begin a function by typing =. 2. Click in cell A7, and hit Enter.
In the Totals worksheet enter a formula in cell B2that displays the SUM of the Fiction12 named range.
1. In the Totals worksheet, select Cell B2. Click the Formulas tab, locate the Function Library, click the Auto Sumdropdown, and click Sum.2. In the formula, type the name Fiction12, and press enter.
Format the Student Budget heading as follows: Arialfont, size 14, dark blue text, and a dark blue outline border.
Select cell A1 In the Home tab, click the Font dialog box launcher In the Format Cells window, Font tab under Font select Arial Under Size select 14 Click the Color drop-down arrow and select Dark Blue Navigate to the Border tab Click the Color drop-down arrow select Dark Blue Under Presets select Outline Click the OK button
Sort the table data by the Variant column and then by the Year column in ascending order.
1. Select cells A5:D106.2. In the Data tab, Sort & Filtergroup, click Sort.3. In the Sort dialog box, from theSort by drop down, choose Variant. Click Add Level. From the Then by drop down, choose Year.4. Click Ok.
In the Top Performers worksheet, determine the highest sales number for 2011 in cell B3.
1. If the Top Performersworksheet is not open, click on the Top Performers tab.2. Select cell B3.3. In the Formulas tab, Function Library group, click the drop down for AutoSum. Select Max.4. Ensure that your cursor is blinking between the parentheses in the text =MAX(). 5. Open the Sales 2011worksheet. Select cells D3:D7.6. Hit the ENTER key.
Use the appropriate formulas and Autofill to display the names from cells A2 through B4 in cells D2 through E4, using all capital letters. Display the names from cells A2 through B4 in cells G2 through H4, using all lowercase letters. Use cells J2 through J4 to display the length of the text in cells A2 through A4.
Click in cell D2 Type: =UPPER(A2) Click the fill handle and drag to cell E2 Click the fill handle and drag to cell E4 Click in cell G2 Type: =LOWER(A2) Click the fill handle and drag to cell H2 Click the fill handle and drag to cell H4 Click in cell J2 Type: =LEN(A2) Click the fill handle and drag to cell J4
Set the cells in column A to allow for wrapping the text onto multiple lines.
Click the A to select column A In the Home tab, Alignment group, click Wrap Text
Delete all of the comments in the current worksheet.
1. Highlight cell A1. In theReview tab, go to theComments group and clickNext.2. For each comment, click the Delete button in theComments group.
In the Marketing Budget Plan worksheet, change the Page Size to A4.
1. In the Marketing Budget Plan worksheet, in the Page Layout tab, locate the Page Setup group, click Size, and select A4.
Change the options on this worksheet so that Gridlines are visible.
1. In the View tab, locate the Showgroup, and check the Gridlines box.
Save the entire workbook as a PDF file in your GMetrixTemplates folder, omitting document properties and tags.
1. Open the File tab and choose Export.2. In File Types, chooseCreate PDF/XPS Documentand click Create PDF/XPS. Make sure you navigate to the GMetrixTemplates folder.3. In the Publish as PDF or XPS dialog box, clickOptions.4. In the Options dialog box, under Publish what, clickEntire Workbook. Clear the check boxes Document properties and Document structure tags for accessibility.5. Click OK.6. Click Publish.
Hide column A.
1. Right click on Column A and select Hidefrom the menu.
Split the worksheet into two sections between rows 11 and 12.
Select row 12 Click the View tab In the Window group, click Split
Paint the format of cell B3 to cells C3 and D3.
Ensure cell B3 is selected In the Home tab, Clipboardgroup, click the Format Painter option Click and drag across cells C3 and D3
Using the locations and data, create a table with headers consisting of the data in row 2.
Select cells A2 through E11 Click the Insert tab In the Tables group, click Table Ensure the My table has headers check box is selected Click the OK button
Using the data in cells A2through E6, create a 2D Clustered Column Chart.
Select cells A2 through E6 Click the Insert tab In the Charts group, click the Insert Column or Bar Chart drop-down arrow Under 2-D Column, click the Clustered Column option
Apply a conditional formatting to the values in columns E, F, and G. Apply a yellow fill to any cells with a value equal to 5.
1. Highlight the columns E, F and G. In the Home tab locate the Styles group, click Conditional Formatting and in the drop down menu click Highlight Cell Rules and click Equal To...2. In the dialog box, enter 5, in the first section and in the drop down menu next to it select Custom Format.3. In the dialog box, click the Fill tab, select Yellow and click Ok twice.
In the Annual Sales worksheet, apply a Links to the title FusionTomo Inc. that links to the website www.fusiontomo.com.
1. In the Annual Sales worksheet, highlight the cell A1. In the Insert tab, locate the Links group and click link.2. In the dialog box, in the Address text field enter: www.fusiontomo.com. Click OK
In the Annual Sales worksheet, Sort the data in the cell range A5:F15 by the Total column and order the values by largest to smallest.
1. In the Annual Sales worksheet, select the cell range A5:F15.2. in the Home tab, locate the Editing group, clickSort & Filter and in the drop down menu clickCustom Sort.3. In the Sort by row, select the Total column, sort on Values, orderLargest to Smallest, and click Ok.
In the Budget Plan Chartworksheet, Change the Workbook View to Page Break Preview.
1. In the Budget Plan Chart worksheet, in the View tab, locate the Workbook Views group, and click Page Break Preview.2. If the Welcome to Page Break Previewdialog box apears, click OK.
Change the margins to the Narrowsetting.
1. In the Page Layout tab, locate the Page Setupgroup, click the Marginsdropdown, and selectNarrow.
In the Time Card worksheet, insert the image ftlogo.gif, from the GmetrixTemplates folder, at the top of this worksheet.
1. Click at the top of the worksheet. In Insert tab, locate the Illustrationsgroup, and click Pictures. 2. Locate your GMetrix Templates folder inside your documents folder, selectftlogo.gif and click Insert.
Change the Chart title to Annual Sales.
1. Click in to the title, and replace the wordsChart Title with the words Annual Sales
Using Autofill, add a Line sparkline to column L using the data in columns C through J.
1. Click on cell L3.2. In the Insert tab, Sparklinesgroup, click Line.3. In the Create Sparklines dialog box, for Data Range type C3:J3.4. Click OK.5. Move your cursor over theAutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.6. Click and drag to cell L27.
Rotate the direction of the arrow shape 90* Right.
1. Click on the arrow shape to select it.2. In the Shape Formatcontextual tab, Arrangegroup, click the drop down for Rotate and choose Rotate Right 90*.
Modify the pyramid SmartArt to use the Pyramid List layout and Sunset Scene style.
1. Click on the pyramid SmartArt to select it.2. In the SmartArt Design contextual tab, Layouts group, click the Pyramid List layout.3. In the SmartArt Design contextual tab, SmartArt Styles group, click More at the bottom-right.4. In the 3-D section, choose the Sunset Scenestyle.
In the Time Card worksheet, apply a red solid fill data bar conditional formatting to the cells D21:H27
1. Click the Time Card worksheet, and select Cell Range D21:H27.2. In the Home tab, locate the Styles group, and click Conditional Formating.3. Select Data Bars, and click Red Data Barfrom the Solid Fill group.
In the Summary worksheet, sharpen by 50% and apply the Watercolor Sponge artistic effect to the image.
1. Ensure that the image is selected so that the Picture Tools display in the ribbon.2. In the Picture Formatcontextual tab, Adjust group, click the drop down for Corrections and choose Sharpen: 50%.3. In the Picture Formatcontextual tab, Adjust group, click the drop down for Artistic Effects and choose Watercolor Sponge.
Move the chart to a new worksheet named Sales Chart in the workbook.
1. Ensure the chart is selected in the Combined Sales worksheet. In the Chart Design contextual tab, Location group, click Move Chart.2. In the Move Chart dialog box, select New sheet and type Sales Chart in the associated text box.3. Click OK.
In the Review worksheet, use the CONCATENATE function to display the contents of cell B4 and A4 separated by a space in cell J4.
1. Highlight Cell J4, in theFormulas tab, go to theFunction Library and clickInsert Function.2. In the dialog box inside theSearch for a function box enter Concatenate. Click Go, and select from the list below.3. In the Text1 field enter: B4in the Text2 field enter aSpace. In the Text3 field enter: A4. Click Ok.
Use the AutoSum tool to get the SUM of the values for each cell (B through F) in the Total row.
1. Highlight cells B16:F16.2. In the Home tab, go to theEditing group and clickAutoSum
In the Product worksheet, change the format of the numbers in cells B8:E33 so that no decimal places are showing.
1. Highlight the cells B8:E33. 2. in the Home tab, locate the Number group, and click Decrease Decimal until there are no decimals on the numbers.
In the Budget Plan Chart worksheet insert the values from the chart into the Budget row of the table below.
1. In cell B41, insert the number 0 from the Research section of the chart above.2. In cell C41, insert the number 0 from the Communications section of the chart above.3. Continue inserting the information in the chart to the rest of the columns in the "Budget" row.
Without using the average function, create a formula in the cell H4 that gets the SUM of the values in the cell E4 through G4 and divides the sum by three.
1. In cell H4 type:=SUM(E4:G4)/3
In the Budget Plan Chart worksheet, create a reference in cell E42 to EventTotal, cell F42 to ProTotal and G42 to AdTotal
1. In the Budget Plan Chart worksheet, select cell E42.2. Begin a cell reference by typing =, and then enter the predifined named range EventTotal.3. Follow steps 1 and 2 for cell F42with named range ProTotal, and cellG42 with named range AdTotal. Press Enter.
Filter the table to display data from only the Oncology and Gastrointestinal categories and those pharmaceuticals that end with the suffix ex or lux.
1. In the Data tab, Sort and Filter group, select Filter.2. Click the drop-down arrow in cell A2.3. Click to clear the (Select All) check box, and then click to select theGastrointestinal and Oncology check boxes.4. Click OK.5. Click the drop-down arrow in cell B2.6. Click the Text Filters > Ends With...option.7. In the Custom AutoFilter dialog box, type ex in the first box. Click Or. Chooseends with in the next drop-down and type lux in the last box.8. Click OK.
In the Expenses worksheet, in cell D46, use the SUM formula to get the sum of cell range D41:D45
1. In the Expenses worksheet, select D46 and enter the formula, =SUM(D41:D45).Press Enter.
In the Expenses worksheet enter the following text in the required cell: In cell B21, enter the text Graphics In cell B34, enter the text Food.
1. In the Expenses worksheet, select cell B21.2. Type the text Graphics.3. Select cell B34.4. Type the text Food.
In the Expenses worksheet in cell F2 with the contents Budget Document, create a Hyperlink that creates a new document called TomoCon Budget and Edit the new document later.
1. In the Expenses worksheet, select cell F2 that contains the contents Budget Document.2. In the Insert tab, go to the Links group and click Links.3. In the Insert Hyperlink dialog box, click the Create New Document, and name the document TomoCon Budget, check the Edit the new document later button and click Ok.
In the Expenses worksheet, in cell F54, create a formula that gets the AVERAGE of each Event item cost using the data in the Actual column heading in column D. Exclude the total rows.
1. In the Expenses worksheet, select cell F54, in the Formulas tab, go to the Function Library and click Insert Function2. In the Search field enter Average, click Go. Under the Select a function field, select AVERAGE and click OK.3. Starting with the Number1field select or enter D6:D9, in the Number2 field D13:D17, in the Number3 field D21:D23, in the Number4 field D27:D30, in the Number5 field D34:D37, in the Number6 field D41:D45, in the Number7 field D49:D50.4. Click OK.
In the Expenses worksheet, sort the data in cell B40:D45 by the Actual column, Largest to Smallest
1. In the Expenses worksheet, select cell range B40:D45, in the Data tab, go to the Sort & Filter and click Sort.2. In the Sort dialog box, Sort by Actualand Order by Largest to Smallest and click Ok.
Create a PDF of the workbook called FusionTomo.pdf, and save it in your GMetrixTemplates folder. Do not open the file after publishing.
1. In the File Menu, click theSave As tab.2. Browse to yourGMetrixTemplates folder.3. In the Save as type drop down select PDF and then in the File name field enter inFusionTomo.4. Click Save.
Inspect the current workbook for hidden properties such as Author. Remove any of these properties that are found and return to the document.
1. In the File button, go to theInfo tab, select Check For Issuesand click the Inspect Documentbutton.2. In the dialog box, make sureDocument Properties and Personal Information is selected and click Inspect.3. Click Remove All from theDocument Properties and Personal Information section. Click Close
Copy the Summary worksheet from the Quarterly_Earnings_Overview.xlsxworkbook, found in your GMetrix Templates folder, and place it as the last worksheet in the Quarterly_Earnings.xlsxworkbook.
1. In the File tab choose Open. 2. Choose theQuarterly_Earnings_Overview.xlsx from youGMetrix Templates folder and click the Openbutton.3. In the Home tab, Cells group, click theFormat button. Under Organize Sheet, selectMove or Copy Sheet... 4. In the To book drop down, chooseQuarterly_Earnings.xlsx, in the Before sheetlist, select (move to end).5. Select the Create a copy check box and clickOK.
Inspect the document and remove only the Comments and Annotations. (Close the dialog box once you have completed the task)
1. In the File tab, go to the Info group, select the Check for Issues drop down box and click Inspect Document.2. In the Document Inspector window, make sure everything is checked and click Inspect.3. Next to the Comments and Annotations group click Remove All, and click Close.
Save this file as a CSV (Comma delimited)document in the GMetrixTemplates folder and name the file EXCEL10.csv
1. In the File tab, go to the Save Asand then click on Browse 2. In the Save As Type drop down select CSV and then in the File name field enter in Excel10.csv.
Save this spreadsheet as a PDFfile named EXCEL05 in the GmetrixTemplates folder. Do not open the file after publishing.
1. In the File tab, select Export.2. Click Create PDF/XPS3. Navigate to the GMetrixTemplates folder, and enter EXCEL05 in the File name: box.4. Make sure PDF is selected for the Save as type.5. Uncheck the Open File After Publishingoption if needed.6. Click Publish.
In the Excel Options change the Formulas to not Enable background error checking.
1. In the File tab, select Options to launch the Excel Options dialog box.2. In the Formulas tab, locate the Error Checking group, uncheck the Enable background error checking option, and click OK.
In Excel Options, change the Save AutoRecover informationto every 5 minutes.
1. In the File tab, select Options to launch theExcel Options dialog box.2. In the Save tab, locate the Save Workbooksgroup, and change the Save AutoRecover information option to 5 minutes. Click OK
Remove all comments, annotations, personal information and document properties in the workbook.
1. In the File tab, select the Info option.2. Select Check for Issues. In the drop down menu, choose Inspect Document.3. Make sure that only Comments and Annotations, and Document Properties and Personal Information are selected. Click Inspect.4. Click Remove All for Comments and Annotations, and Document Properties and Personal Information.5. Click Close.
In the Annual Sales worksheet, use the Find & Select tool to find the cell in the open spreadsheet that contains Conditional Formating and apply a Thick Outside Border.
1. In the Home tab, go to the Editing group and click Find & Select drop down menu select Conditional Formatting2. With cell F16 selected in the Home tab, go to the Font group and click on the Borders drop down menu and select Thick Outside Borders.
Modify the Title style to use 20 pts
1. In the Home tab, go to the Stylesgroup, select the Cell Styles button (the drop down arrow) on the styles, right click on Title style and click Modify2. In the Style dialog window, click Format, in the Font tab, go to the Sizesection, select 20 and click OK twice.
In the Income worksheet, use the Fill tool to copy the SUM formula in G34 to H34.
1. In the Incomeworksheet, select cell rangeG34:H34. In the Home tab go to the Editing group, select the Fill drop down and select Right.
In the June worksheet, in cells E4:E33 use the IF formula to display a 1 if the values of the cells D4:D33 are greater than 2500, and a 0 if it is less than or equal to 2500.
1. In the June worksheet, highlight the cell E4.2. In the Formulas tab, go to the Function Library and click Insert Function.3. In the dialog box select the IF function and click Ok.4. In the new window for: Logical_test enter: D4>2500 and for Value_if_true: 1, and Value_if_false: 0 and click Ok.5. Highlight the cells E4: E33 and in the Hometab, go to the Editing group and click Fill and select Down.
In the June worksheet, define the name Fiction12 for the cell range B4:B33.
1. In the June worksheet,select the rangeB4:B33.2. Click the Formulas tab, locate the Defined Names group, and click Define Name. In theName field, type Fiction12. Click OK.
In the Marketing Budget Plan Worksheet, use the Go To Specialtool looking for Comments and Delete the comment.
1. In the Marketing Budget Plan worksheet, go the the Home tab, locate the Editing group, select theFind and Select option, and click Go To Special.2. In the Go To Specialdialog box, select Notes, and click OK.3. Cell A62 should be auto selected. Go to the Reviewtab, locate the Commentsgroup, and click Delete.
In the Marketing Budget Planworksheet, edit the cell range reference Research; have it only refer to the Research types under the heading.
1. In the Marketing Budget Plan worksheet, in the Formulas tab, go to the Defined Names group and click Name Manager.2. In the Name Manager window, scroll down the Name column and highlight Research and click Edit.3. In the Edit Name window, in the Refers tofield, delete the data in the field and highlight cells A5:A8.4. Click OK and click Close
In the Marketing Budget Planworksheet, in cells D10, D18 and D25 create a formula that gathers the SUM of the data above in each of the cells.
1. In the Marketing Budget Plan worksheet, select cell D10. In the Formulas tab, locate the Function Library group, click the AutoSum dropdown, and select Sum.2. Enter cell range D5:D9 as the range for the formula, and hit Enter.3. Repeat steps 1 to 2 for cell D18 using cells D13:D174. Repeat steps 1 to 2 for cell D25 using cells D21:D24
In the Marketing Budget Plan worksheet, unmerge cells A97:C97
1. In the Marketing Budget Planworksheet, select cells A97:C97.2. In the Home tab, locate the Alignment group, and click Merge & Center.
In the Mileage Log worksheet, apply a Simple White Frame and a Red Picture Border to the image.
1. In the Mileage Log worksheet, select the picture at the top, in the Picture Format contextual tab, go to the Picture Styles group, select the Simple Frame, White from the Styles.2. In the Format tab, go to the Picture Styles group, click the Picture Borderdrop down and select Red
In the Mileage Log worksheet, use the Find and Replace tool to find all occurrences of Personal and replace them with Client
1. In the Mileage Logworksheet, in the Hometab, go to the Editinggroup, click the Find & Select tool and selectReplace2. In the Find and Replacewindow, in the Find whatfield enter Personal and in the Replace with field enter Client and clickReplace All. Click OK and click Close.
Set the Page margins to the following specifications: Top & Bottom: 2"(5.08 cm) and Left & Right: 1" (2.54cm)
1. In the Page Layout tab, go to the Page Setup group, select the Margins drop down and click Custom Margins.2. In the Page Setup dialog window, set Top & Bottomto 2"(5.08 cm), set Left & Right to 1" (2.54 cm) and click OK.
Apply the Built-in theme Ion to the current worksheet.
1. In the Page Layout tab, locate the Themes group, click Themes, and in the drop down menu click Ion.
Change the theme of the current worksheet to Ion.
1. In the Page Layout tab, locate the Themes group, clickThemes, and select Ion from the dropdown.
Change the margins to the Narrow setting.
1. In the Page Layout tab, locate thePage Setup group, click the Marginsdropdown, and select Narrow.
In the Profit - Loss Summaryworksheet, insert the Lightning Bolt Shape to the right of the data. Adjust the Height of the shape to 2" (5.08 cm) and the Width to 1.5" (3.81 cm).
1. In the Profit - Loss Summary worksheet, in the Insert tab, go to the Illustrations group, click the Shapes drop down and select Lightning Bolt. and click and drag to the right of the data to create the lightning bolt.2. Highlight the Lightning Bolt Shape, in the ShapeFormat contextual tab, go to the Size group, and in the Height enter 2" (5.08cm) and in the Width enter 1.5" (3.81 cm)
In the Profit - Loss Summaryworksheet, apply a cell reference in cell D5 to the Incomeworksheet cell H31.
1. In the Profit - Loss Summary worksheet, select cell D5, and begin a cell reference by typing =.2. Click on the Income worksheet, and click cell H31, and hit Enter.
In the Profit - Loss Summary worksheet, in cell D8, use the IF function to show Close if the SUM of C9:D9 is greater than 22500and Not Close if it is less than or equal to 22500.
1. In the Profit - Loss Summaryworksheet, select cell D8, in the Formulas tab, in the Function Library group click Insert Function.2. In the Insert Function dialog box, type IF and click Go. Highlight IFfrom the Select a Function group and click Ok.3. In the Function Argumentsdialog box, for Logical_test enter SUM(C9:D9)>22500, for Value_if_true enter Close, and for Value_if_false enter Not Close and click Ok.
In the Profit - Loss Summary worksheet, change the chart under Profit vs. Loss to select the data from cells B4:D6
1. In the Profit - Loss Summaryworksheet, select the chart under Profit vs. Loss, in the Chart Design contextual tab, go to the Data group and click Select Data.2. In the Select Data Source, highlight cells B4:D6 and click Ok.
In the Profit - Loss Summary worksheet, Modify the FusionTomo Image to have a Height of 1" (2.54 cm).
1. In the Profit Loss Summaryworksheet, select the FusionTomo Image.2. In the Picture Format contextual tab, locate the Size group, and change the Height to 1" (2.54 cm).
In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Salesworksheet, cell F16, multiplied by 1plus the growth value in the Quarterly Sales worksheet, cell B3.
1. In the Quarterly Salesworksheet, cell B9, begin a formula by entering the =character.2. Switch to the Annual Salesworksheet, and click on cell F16.3. Type *(1+ and then click on the Quarterly Salesworksheet, cell B3. Finally, close the formula by typing ). Press Enter
In the Source Data worksheet use find & select to find the cell that contains Data Validation, and clear the validation.
1. In the Source Data worksheet, in the Hometab, locate the Editing group, click the Find & Select dropdown, and click Data Validation.2. With cell B2 selected, click the Datatab, locate the Data Tools group, select Data Validation and click Data Validation...3. In the dialog box, click Clear All, then clickOK.
In the Source Data worksheet, set the print settings to repeat row 1 at the top.
1. In the Source Data worksheet, in the Page Layout tab, go to the Page Setupgroup, and click Print Titles.2. In the Page Setup dialog window, in the Sheet tab, go to the Print titlessection, click into the Rows to repeat at top field and select row 1 in the worksheet. Click OK.
In the Time Card worksheet, add a header that uses the Current Date format in the left header field and Page Number in the right header field. Add the Sheet Name to the left footer field.
1. In the Time Card worksheet, in the Insert tab, go to the Text group and click Header & Footer2. In the Header click into the left field, in the Design tab, go to the Header & Footer contextual tab and click Current Date.3. In the Header click into the right field, in the Design tab, go to the Header & Footer contextual tab and click Page Number.4. In the Footer click into the left field, in the Design tab, go to the Header & Footer contextual tab and click Sheet Name and click out of the Header & Footer and into the sheet.(Note: If the Footer does not appear, change the Workbook View to Normal in the View tab and try again.)
Change the chart in the Totalsworksheet to the 3-D Pie type.
1. In the Totals worksheet, select the chart. In the ChartDesign contextual tab, locate the Type group, and click Change Chart Type.2. In the dialog box, click on Pie, and select 3-D Pie. Click Ok
In the Marketing Budget Plan worksheet, create a custom view named Layout 125 that displays in Page Layout and has a custom zoom of 125%.
1. In the View Tab, locate theWorkbook Views group and clickPage Layout2. In the View tab, locate the Zoomgroup, and click Zoom3. In the Zoom window, select custom, type 125% and click OK.4. In the View Tab, locate theWorkbook Views group and clickCustom Views.5. In the Custom Views window, click Add, in the Name field enterLayout 125 and click OK.
Insert a header that contains the filename on the left side and sheet on the right side. Close the header before grading.
1. In the View tab, Workbook Views group, select the Page Layout button.2. On the first page of the worksheet, select Add Header.3. Select the left section of the header.4. In the Header & Footercontextual tab, Header & Footer Elements group, select File Name.5. Select the right section of the header.6. In the Header & Footercontextual tab, Header & Footer Elements group, select Sheet Name.7. Click anywhere outside of the header to close.
Set the zoom to 150% and save as a custom view named PageZoom.
1. In the View tab, Zoomgroup, click the Zoombutton.2. Click the Custom radio button and type 150 in the text box.3. In the View tab,Workbook Views group, click the Custom Viewsbutton.4. Click Add...5. Type PageZoom in theName text box and clickOK.
Freeze the Top Row of the Loan Amortization Schedule worksheet.
1. In the View tab, locate theWindow group, click Freeze Panes, and select Freeze Top Row.
Change the worksheet view to Page Layout and hide rows 2 and 3.
1. In the View tab,Workbook Views group, click the Page Layout button.2. Select Rows 2 and 3.3. In the Home tab, Cellsgroup, click the drop down for Format.4. In the Visibility section, choose Hide & Unhide > Hide Rows.
Create a new worksheet called Cover Page, and position it before the Mileage Log worksheet.
1. Next to the Mileage Logworksheet, click the New Sheetbutton.2. In the Home tab, go to the Cells group, click the Formatdrop down and select Rename Sheet and type Cover Page. Press Enter.3. Click and drag the Cover Page worksheet to be in front of the Mileage Log worksheet.
Copy only the formatting from the cell range A1:G26 in the 2009 Sales worksheet to the 2010 Sales worksheet.
1. Open the 2009 Salesworksheet.2. Select cells A1:G26. In theHome tab, Clipboard group, select Copy.3. Open the 2010 Salesworksheet.4. In the Home tab, Clipboardgroup, select the drop down forPaste. Select Formatting.
In the 2010 Sales worksheet, modify the size of the Title style font to 24 and apply the Title style to cell A1.
1. Open the 2010 Salesworksheet.2. In the Home tab, Stylesgroup, click the drop down for Cell Styles.3. Under Titles and Headings, right click Titleand select Modify...4. In the Style dialog box, click Format...5. In the Format Cellsdialogue box, Font tab, select 24 from the Size list.6. Click OK twice.7. Click cell A1.8. In the Home tab, Stylesgroup, click the drop down for Cell Styles. Under the Titles and Heading section, select Title.
In the 2010 Sales worksheet, apply the Heading 1 cell style to cell A1.
1. Open the 2010 Salesworksheet.2. Select cell A1.3. In the Home tab, Stylesgroup, click the drop down for Cell Styles.4. In the Cell Styles drop-down list, under Titles and Headings, select Heading 1.
Using the data in the Combined Sales worksheet, add a Pie chart including labels that presents the combined sales for each representative.
1. Open the Combined Sales worksheet.2. Select cells E2:E7.3. In the Insert tab, Charts group, click the drop down for Pie. Choose Pie in the 2D Pie section.4. In the Chart Design contextual tab, Data group, click the Select Databutton.5. In the Select Data Source dialog, in the Horizontal (Category) Axis Labelssection, click Edit.6. Select cells A3:A7.7. Click OK twice.
In the Combined Sales worksheet, correct the formula in the cell range D3:D7 so that it calculates the percentage of change from Sales 2010 to Sales 2011.
1. Open the Combined Salesworksheet.2. Select cell D3.3. In the Formula Bar, add an opening parenthesis after the text = and a closing parenthesis before the text /.4. Change 'Combined Sales'!E3 to 'Sales 2010'!D35. Hit the ENTER key.6. Click on cell D3 again.7. Move your cursor over theAutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.8. Click and drag down to cellD7.9. When you release the mouse button, the cell range should now display change percentage for the other representatives.
In the Combined Sales worksheet, add a new column with the title % Total between the % Change and Sales columns.
1. Open the Combined Salesworksheet.2. Select cell E2.3. In the Home tab, Cells group, click the Insert button. Select Insert Sheet Columns.4. Click on cell E2 (now an empty cell in the new column) if it is not already selected.5. Type % Total and hit the ENTER key.
Define the name Bonus for the cell range A2:C6 in the Commission Rates worksheet.
1. Open the Commission Rates worksheet.2. Select cells A2:C6.3. In the Formulas tab, Defined Names group, select Define Name.4. For Name: type Bonus. Click Ok.
In the Summary worksheet, remove the background of the ice cream image.
1. Open the Summary worksheet.2. Click on the ice cream image to select it.3. In the Picture Format contextual tab, Adjust group, click Remove Background.4. In the Background Removalcontextual tab, Close group, click Keep Changes.
In the Top Performers worksheet, determine the highest sales number for 2010 in cell B2.
1. Open the Top Performers worksheet.2. Select cell B2.3. In the Formulas tab, Function Librarygroup, click the drop down for AutoSum. Select Max.4. Ensure that your cursor is blinking between the parentheses in the text =MAX().5. Open the Sales 2010. Select cells D3:D7 6. Hit the ENTER key.
Color each of the worksheet tabs: Expenses, Income and Profit - Loss Summary to have a different color.
1. Right click on the Expenses worksheet tab, select Tab color and pick a color.2. Right click on the Incomeworksheet tab, select Tab color and pick a color.3. Right click on the Profit - Loss Summary worksheet tab, select Tab color and pick a color.
In the cell range C3:J26, apply conditional formatting so that cells that contain values over 200,000 display with green fill and text and values under 10,000 display with a red border.
1. Select Cells C3:J26.2. In the Home tab, Styles group, click the drop down for Conditional Formatting and choose Highlight Cells Rules > Greater Than...3. In the Greater Than dialog, type200000 and choose Green Fill with Dark Green Text in the drop-down list.4. Click OK.5. In the Home tab, Styles group, click the drop down for Conditional Formatting and choose Highlight Cells Rules > Less Than...6. In the Less Than dialog box, type10000 and choose Red Border in the drop-down list.7. Click OK.
In the Source Data worksheet Unhide any hidden rows
1. Select all the cells by using Ctrl+A, or the Select All Button at the top left of the excel sheet.2. With all the cells of the table selected, in the Home tab, locate the Cells group, click Format, and under Visibility in the Hide & Unhide menu select Unhide Rows.
In the Mileage Log worksheet, in cell C16, add the word Delivery. In cell D16, add the word Business.
1. Select cell C16 and add the word Delivery.2. Select cell D16 and add the word Business.
In cell D3 create a formula that gets the SUM of cells I6:I33.
1. Select cell D3, enter the formula, =SUM(I6:I33). Press Enter.
In the Loan Amortization Scheduleworksheet, add the following comment to cell D6: Good Rate.
1. Select cell D6. Click theReview tab, locate theComments group, and clickNew Comment.2. In the new comment, type the words Good Rate. Click out of the comment when finished.
Insert a Line Sparkline in cell K6 with a data range of cells G6:I6
1. Select cell K6, in the Inserttab, go to the Sparklines group and click Line.2. In the Create Sparklinesdialog window, in the Data Range G6:I6 and click Ok.
Merge & Center cell range A1:D2
1. Select cell range A1:D2, in theHome tab, go to the Alignmentgroup and select Merge & Center.
In the Mileage Log worksheet, define the name for cell range I6:I33 as Miles. In cell D3, insert a SUM formula to show the sum of the Miles range.
1. Select cell range I6:I33, in the Formulas tab, go to theDefined Names group and click the Define Name.2. In the New Name dialog box enter the name Milesand click Ok.3. In cell D3, enter the formula, =SUM(Miles). Press Enter.
Apply a Filter to the data in the table that will only show miles driven for a Business Purpose.
1. Select cells A4:J20, in the Datatab, go to the Sort & Filter group and click Filter.2. Select the drop down arrow by the Purpose column and only have Business checked and click Ok.
Remove all duplicates from the table based on the values in the Year and Value columns.
1. Select cells A5:D106.2. In the Data tab, Data Tools group, clickRemove Duplicates.3. In the Columns list, clear the Variable andVariant check boxes.4. Click OK twice.
Apply Gradient Fill-Green Data Bar conditional formatting to the values in column E.
1. Select column E. by clicking the Eabove Cell E1.2. In the Home tab, locate the Stylegroup, select Conditional Formatting, click Data Bars, select Gradient Fill and click Green Data Bar.
Insert a new worksheet named 2009 Sales (Corrected) between the 2009 Sales and 2010 Sales worksheets.
1. Select the 2010 Salesworksheet. 2. In the Home tab, Cells group, select the drop down for Insert. Select Insert Sheet.3. Double-click on the new worksheet's tab named Sheet1.4. Type 2009 Sales (Corrected)and hit the ENTER key.
In the June worksheet, in Cell E34, enter a formula that returns the AVERAGE value of all the numbers in the range B4:D33.
1. Select the cell E34. Click theFormulas tab, locate the Function Library group, click the Auto Sumdropdown, and cick Average.2. In the formula, type B4:D33. Press Enter.
Filter the data so that only the people with a position of Developer are displayed.
1. Select the cell range A3:G33.2. in the Data tab, locate the Sort & Filter group and click Filter3. In C3 click the drop down next to Position. In the drop down, unselect all the check boxes except Developer, and click Ok.
In the Annual Sales worksheet, insert a 2-D Clustered Column chart based on the data in cell range A5:E15. Include the column headings and position the chart below the table.
1. Select the cell range A5:E15. 1. In theInserttab, locate theChartsgroup, click theColumndropdown, and selectClustered Columnfrom the2Dgroup.2. Select the chart, and reposition it so that it is below the data.
In cell range B16:F16 set the style of the cell with the following characteristics: Number Format = Currency, Font = Courier New, Fill Color = Yellow.
1. Select the cell range B16:F16.2. In the Hometab, locate the Numbergroup, click the Number Format drop down, and select Currency.3. In the Home tab, locate the Font group, click theFont drop down, and selectCourier New.4. In the Home tab, locate the Font group, click the Fill Color drop down, and selectYellow.
Hide the label and data in the Fax, Ext and Mobile columns.
1. Select the column D heading, then select F and G while holding the CTRL key.2. In the Home tab, Cells group, click the drop down for Format.3. In the Visibility section, chooseHide & Unhide > Hide Columns.
In the Mileage Log worksheet, apply conditional formating to the Descriptioncolumn that highlights cells that contain the text Deliver with a Light Red Fill with Dark Red Text.
1. Select the data in the Descriptioncolumn, in the Home tab, go to theStyles group, click the Conditional Formatting drop down, selectHighlight Cell Rules and selectText That Contains.2. In the Text That Contains dialog box, enter Deliver, set the with drop down to Light Red Fill with Dark Red Text. Click OK.
Set a Print Area for cells A1:J13
1. Select the range A1:J13.2. In the Page Layout tab, locate the Page Setupgroup, and click the Page Setup dialog box launcher.3. In the Sheet tab of the dialog box, click into thePrint area textbox, enterA1:J13, and click OK.
Format cells A3:G33 as a table using Table Style Light 2.
1. Select the range A3:G33. In the Home tab, locate the Styles group, click Format As Table, and select Table Style Light 2.2. Click OK to create the table.
Filter the data so that only rows with a value that is Above Average are displayed in the Fictioncolumn.
1. Select the range B3:D33. Click the Data tab, locate the Sort & Filter group, and click Filter.2. In the dropdown next to Fiction, select Number Filters, and click Above Average.
Apply the style Heading 3 to the title Blackbread Books.
1. Select the title Blackbread Books. In the Home tab, locate the Styles group, and click the Cell Styles/Moredrop down button2. In the drop down, under the Titles and Headingssection, select Heading 3.
Insert two columns between Decemberand January. Then, insert two rows below the top row on the worksheet.
Click and drag to select columns N and O In the Home tab, Cells group, click the Insert drop-down button Click Insert Sheet Columns Click and drag to select rows 2and 3 In the Cells group, click the Insert drop-down button Click Insert Sheet Rows
Using the Totals feature for tables, generate totals for columns Cthrough F within the table on the current worksheet.
Click anywhere within the table Click the Design tab under Table Tools In the Table Style Options group, select the Total Row check box Click cell C13 Click the drop-down arrow within the cell Click Sum Click cell D13 Click the drop-down arrow within the cell Click Sum Click cell E13 Click the drop-down arrow within the cell Click Sum
Apply the First Column and Banded Columns table style options to the table on the current worksheet. Turn the Banded Rows option off.
Click anywhere within the table Click the Design tab under Table Tools In the Table Style Optionsgroup, select the First Column check box In the Table Style Optionsgroup, select the Banded Columns check box In the Table Style Optionsgroup, clear the Banded Rows check box
Use AutoFill to add 12 months to row 2. Then, use AutoFill to fill the gym membership price of $10 a month through the month of December.
Click cell D2 Use the AutoFill feature to fill the cell values through cell P2 Click cell D3 Use the AutoFill feature to fill the cell values through cell M3
In cell D3, add a function to get the two leftmost numbers from cell B3. In cell E3, add a function to get the middle three numbers from cell B3. In cell F3, add a function to get the two rightmost numbers from cell B3. AutoFill the three results down through the remaining products.
Click in cell D3 Type: =LEFT(B3,2) Click in cell E3 Type: =MID(B3,4,3) Click in cell F3 Type: =RIGHT(B3,2) Select cells D3 through F3 Click the fill handle and drag through row 8
Display the combined first and lastname from cells A2 and B2 in cell L2, adding a space in between the first and last name. Fill those values for the other two names. In cell N2, combine the values of cells H2 andG2 with a delimiter of a comma and ignoring empty cells. Fill those values for the other two names.
Click in cell L2 In the Formulas tab, Function Library group, click the Text drop-down arrow Click CONCAT Click cell A2 to populate the Text1 field Click in the Text2 field Type: " " Click in the Text3 field Click cell B2 Click the OK button Ensure that cell L2 is still selected and click the fill handle and drag to cell L4 Click in cell N2 In the Formulas tab, Function Library group, click the Text drop-down arrow Click TEXTJOIN In the Delimiter field type:"," Click in the Ignore_empty field Type: TRUE Click in the Text1 field Type: H2 Click in the Text2 field Type: G2 Click the OK button Ensure that cell N2 is still selected and click the fill handle and drag to cell N4
Below each of the headings for Sum, Average, Min, and Max, add functions to get the sum, average, maximum, and minimum values for cells B6through M6. Then, AutoFill the results down through all of the morning classes.
Click in cell O6 Type: =SUM(B6:M6) Click in cell P6 Type: =AVERAGE(B6:M6) Click in cell Q6 Type: =MIN(B6:M6) Click in cell R6 Type: =MAX(B6:M6) Select cells O6 throughR6 Click the fill handle and drag down through row 20
In cell S6, use a function to display a count of the cells from B6 through M6 that contain a number. In cell T6, use a function to display a count of the cells from B6through M6 that contain nothing. AutoFill both cells down through all of the morning classes.
Click in cell S6 Type: =COUNT(B6:M6) Click in cell T6 Type: =COUNTBLANK(B6:M6) Select cells S6 and T6 Click the fill handle and drag down through row 20
Add the Beginners data series to the pie chart on theChart1 worksheet. While doing so, add the value in cell A8 as a series name to the chart.
Click the Chart1worksheet Click the Design tab under Chart Tools In the Data group, click Select Data In the Horizontal (Category) Axis Labelsarea, click the Edit button Select cells B2 through F2 Click the OK button In the Legend Entries (Series) area, click the Edit button Click the arrow to the right of Series Values Select cells B8 through F8 Press the Enter key Click in the Series Name field Click cell A8 Click the OK button twice
On the column chart on Sheet1, delete the horizontal axis title and set a vertical axis title with the name, Students. Then, change the chart title to the name, Swimming Attendance.
Click the Column chart on the Sheet1 worksheet Click the green plus sign Click Axis Titles While hovering over Axis Titles, click the arrow that appears to the right. Clear the Primary Horizontal check box In the chart, select the text, Axis Title Type: Students In the chart, select the text, Chart Title Type: Swimming Attendance Click anywhere on the chart
On the column chart, switch the source data so that the columns on the chart are showing numbers for locations by time of day instead of time of day by location.
Click the Column chart on the worksheet Click the Chart Design tab In the Data group, click Switch Row/Column
Import the 112-class schedule.csv file from the GMetrixTemplates folder as a table to the current worksheet.
Click the Data tab In the Get & Transform Data group, click From Text/CSV Browse to the GMetrixTemplatesfolder Click the 112-class schedule.csv file Click the Import button In the preview window, click the Load dropdown and select Load To... Ensure that Table is selected under Select how you want to view this data in your workbook. Under Where do you want to put the data?, select Existing worksheet Click OK
Open the 111-instructors.txt file from the GMetrixTemplates folder. Indicate that the data has headers. Tabs are the delimiters.
Click the File tab Ensure the Open tab is selected Browse to the GMetrixTemplates folder Click the File type drop-down arrow Click All Files Click the 111-instructors.txt file Click the Open button Ensure that the My data has headers check box is selected Click the Next button Ensure that the Tab check box is the only delimiter check box selected Click the Next button Click the Finish button
Add the file name as a header to the current worksheet. Then, add the current date to the left side of the footer.
Click the Page Layouttab Click the Page Setupdialog box launcher Click the Header/Footertab Click the Header drop-down arrow Click the file name (133-classes by month.xlsx) Click the Custom Footerbutton Ensure the cursor is in the Left section Click the Date icon Click the OK button twice
Change the chart type of the Swimming Attendance chart to a 3-D Clustered Column layout. Then, apply the Layout 5 layout to the chart.
Click the Swimming Attendance chart Click the Design tab under Chart Tools In the Type group, click Change Chart Type Make sure that Column is selected on the left-hand side Click the 3-D Clustered Column chart type from the list across the top of the All Charts tab Click the OK button In the Chart Layoutsgroup, click the Quick Layout drop-down arrow Click Layout 5
Apply the Style 9 style to the Swimming Attendance chart on Sheet1. Then, apply the Monochromatic Palette 3 color combination to the chart.
Click the Swimming Attendance chart on the Sheet1 worksheet Click the Design tab under Chart Tools In the Chart Styles group, click the Chart Styles drop-down arrow Click Style 9 In the Chart Styles group, click the Change Colors drop-down button Click the Monochromatic Palette 3color group option
On the Table worksheet, assign the name, Budget, to thetable on the worksheet.
Click the Table worksheet Click anywhere inside the table Click the Design tab under Table Tools In the Properties group, select the current table name Type: Budget Press the Enter key
View the current worksheet in Page Layout view. While in Page Layout view, add the sheet name to the right-side header.
Click the View tab In the Workbook Viewsgroup, click the Page Layout button Click in the right side of the header Click the Design tab under Header & Footer Tools (if necessary) In the Header & Footer Elements group, click the Sheet Name button Click anywhere on the worksheet
Insert a cell above cell A7. Then, delete cell B2, shifting the remaining cells upward.
Ensure cell A7 is selected In the Home tab, Cellsgroup, click the Insert drop-down button Click Insert Cells Ensure the Shift cells down option is selected Click the OK button Click cell B2 In the Cells group, click the Delete drop-down button Click Delete Cells Ensure the Shift cells up option is selected Click the OK button
In the Commission Ratesworksheet, for the cell range A2:C6, define the named range Bonus. In the Combined Sales worksheet, add a right-most column named Bonus Pay and calculate the bonus amount for each sales representative using the Bonusnamed range.
Open the Commission Rates worksheet. Select cell range A2:C6 In the Formulas tab, Defined Names group, click Defined Name. In the New Name window enter Bonus for the name. Click OK. Open the Combined Sales worksheet. Select cell F2. In the Home tab, Cells group, click the Insert button and choose the Insert Sheet Columns. Type Bonus Pay and hit ENTER. Select cell F3. In the Formulas tab, Function Library group, select Lookup & Reference. Select VLOOKUP. In the Lookup_value text box, type E3. In the Table_array text box, type Bonus, and in the Col_index_num text box, type 3. Click OK. Select cell F3 Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. Click and drag down to cell F7. When you release the mouse button, the cell range should now display bonus pay amount for the other representatives.
Sort the table on the current worksheet by Item Type and then by Item. Use ascending order for both sorts.
Right-click anywhere within the table Point to Sort Click Custom Sort Click the Sort by drop-down arrow Click Item Type Click the Add Level button Click the Then by drop-down arrow Click Item Click the OK button
Apply the 20% - Accent1 themed cell style to the categories and monthlyamounts on the current worksheet. Then, apply the Note style to the contents of cell A12.
Select cells A4 through M6 In the Home tab, Styles group, click the Cell Styles drop-down arrow Click the 20% - Accent1style Click cell A12 In the Styles group, click the Cell Styles drop-down arrow Click the Note style
Format the numbers for each categoryand city such that any number over 100 has a green fill with dark green text.
Select cells B3 through E6 In the Home tab, Styles group, click the Conditional Formatting drop-down button Hover mouse over Highlight Cells Rules Click Greater Than Type: 100 Click the Format drop-down arrow Click Green Fill with Dark Green Text Click the OK button
Clear the formatting from the months on the current worksheet.
Select cells B3 through M3 In the Home tab, Editing group, click the Clear drop-down arrow Click Clear Formats
Move the pie chart to its own worksheet. Accept the default worksheet name.
Select the Pie Chart Click the Design tab under Chart Tools In the Location group, click Move Chart Click the New Sheet option Click the OK button
Add the text, Percentage of totals for each swim class, as alternative text to the Chart Area of the pie chart.
Select the entire pie chart In the Chart Format tab, Current Selection group, ensure that the drop-down has Chart Area selected. In the Chart Format tab, Accessibility group, click Alt Text Click in the Alt Text field Type: Percentage of totals for each swim class
Change the view to Page Layout.
1. In the View tab locate theWorkbook Views group, and click the Page Layout button.
Freeze the first column in the current spreadsheet.
1. In the View tab, locate the Window group, click the Freeze Panes drop down menu, and click Freeze First Column.
Set the cells A1 through M17 to be the print area for this worksheet.
Select cells A1 through M17 Click the Page Layout tab In the Page Setupgroup, click the Print Area drop-down arrow Click Set Print Area
Change the view to the split screen view.
1. In the View tab, locate theWindow group, and clickSplit.
Use Fill to copy the Sparkline from cell F33 to F8:F32.
1. Select the range F8:F33. In the Hometab, locate the Editing group, select the Fill dropdown, and select Up.
In the Mileage Log worksheet, Fill Down on cell I6 to the end of the given data
1. Select cell range I6:I16, in the Home tab, go to the Editing group, click the Fill tool and select Down.
Add the text Sunday to cell A2 and enumerate the days of the week from cell A3 to A8.
1. Click cell A2.2. Type Sunday and hit theENTER key.3. Click on cell A2 again.4. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.5. Click and drag to cell A8.6. Release the mouse button. The cell range should now display the days of the week.
Add the text January to cell B1and enumerate the months of year from cell C1 to M1.
1. Click cell B1.2. Type January and hit the ENTER key.3. Click cell B1 again.4. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.5. Click and drag to cell M1. 6. Release the mouse button. The cell range should now display the months of the year.
Remove the comment on cell A2and replace it with the note Redundant variants removed.
1. Click on cell A2.2. In the Review tab, Comments group, click the Delete button.3. In the Review tab, Notes group, click Notes then New Note button.4. Type Redundant variants removed note.
Create a new worksheet based on the Expense Report sample template.
1. Click the File tab, select New. 2. Select Expense Report,from the list of templates, and click Create. Note: you may need to Search for online templates to find the Expense Report sample template.
In the Annual Sales worksheet, copy cells A7:F7and paste it into cells A19:F19
1. Highlight cells A7:F7 2. Copy the cells by clicking in the Home tab in the Clipboard group, on the Copy button.3. Highlight cells A19:F19 4. Paste the cells you copied by clicking in the Home tab in the Clipboard group, on the Pastebutton.
In the June worksheet, use Autofill to extend the series starting in the cell A4 to the cell A33.
1. Highlight the cells A4:A33 in the June worksheet, and in the Hometab, locate the Editing group, click the Fill drop down menu and selectSeries.2. Click Ok in the dialog box.
Set the labels in the fourth row to repeat on each page.
1. In the Page Layout tab, Page Setup group, click the Print Titles control.2. In Rows to repeat at top, type $4:$43. Click OK.
Change the Theme to Office.
1. In the Page Layout tab, go to the Themes group, click Themes and in the drop down click Office.
In the Flavor Cost worksheet, apply the formatting from cell A27 to A1 and from A28 to the cell range A2:E2 and A3:A25.
1. Open the Flavor Cost worksheet.2. Select cell A27.3. In the Home tab, Clipboard group, select Format Painter.4. Select cell A1 to apply the formatting.5. Select cell A28.6. In the Home tab, Clipboard group, double-click the Format Painter button.7. Select A2:E2.8. Select A3:A25.9. Hit the ESC key to exit the Format Painter.
In the Flavor Cost worksheet, format data in the Markup column to display as percentages.
1. Open the Flavor Cost worksheet.2. Select cells C3:C253. In the Home tab, Number group, click the Percent Style button.
In the Summary worksheet, apply a hyperlink to If you have any questions, please email the sales department, with the e-mail address [email protected] and subject Sales Summary.
1. Open the Summaryworksheet.2. Select cell A30.3. In the Insert tab, Linksgroup, select Links.4. In the Link section, select Email Address.5. For Email Address, type [email protected]. For Subject, type Sales Summary.6. Click Ok.
In the Budget Plan Chart worksheet, add to the note in cell A40 to say PowerPoint information instead of just being a blank note.
1. Select cell A40. In the Review tab, locate the Notes group, and click Notes then Edit Note.2. Enter PowerPoint information into the note and click out of the note.
Freeze the top four rows so that they are always visible when scrolling through the worksheet.
1. Select cell A5.2. In the View tab, Windowgroup, click the Freeze Panesbutton and choose the Freeze Panes option from the drop-down list.
Add a comment saying Full Nameinside of the cell J4.
1. Select cell J4. In theReview tab, go to theComments group and clickNew Comment.2. Inside the comment enter: Full Name then click out of the comment.
In the Marketing Budget Plan worksheet, remove the hyperlink attached to the title Fusion Tomo Marketing Budget Plan.
1. Select cell range A1:E1. In the Insert tab, locate the Links group, and click Links.2. In the Edit Hyperlink Dialog box, click Remove Link.
Copy the formula in cell H4 to cells H5:H33.
1. Select cells H4:H33, in theHome tab, locate the Editinggroup, click Fill, and selectDown.
In the Mileage Log worksheet, Hide Rows 31 to 33.
1. Select rows 31 to 33 in the Home tab go to the Cells group, click the Formatdrop down, select Hide & Unhide and click Hide Rows.
For this worksheet, set the page margins to wideand the orientation to landscape.
Click the Page Layout tab In the Page Setup group, click the Margins drop-down arrow Click Wide In the Page Setup group, click the Orientation drop-down arrow Click Landscape
In the Source Data worksheet Sort the Dataalphabetically by CustomerID and then by Product.
1. Click on the Source Data datasheet. Select cell A1, click the Data tab, locate the Sort & Filter group, and click Sort.2. In the Sort By box, selectCustomerID, verify that sort on: is Values, and that the Orderdropdown is A to Z.3. Click the Add a Level button. In this new level, select Product in the Sort Bybox, and verify that the other 2 values are Values and A to Z. Click OK.
In the Time Card sheet, Cell D30, change the formula to get the SUM of cells D21:D27 and multiply the result by cell D29.
1. Click the Time Card sheet, and selectCell D30.2. Change the current formula to read=SUM(D21:D27)*D29. Press Enter.
In the Combined Sales worksheet, calculate the sales percentage for each representative in the % Total column and change the cell formatting to Percentage.
1. If the Combined Sales worksheet is not open, click on the Combined Sales tab.2. Click on cell F3 and type the formula =E3/$E$8.3. Hit the ENTER key.4. Click on cell F3 again.5. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.6. Click and drag down to cell F7 7. When you release the mouse button, the cell range should now display sales percentage for the other representatives.8. Select the cells F3:F8.9. On the Home tab, Number group click % symbol.
In the Summary worksheet, determine the total units sold by flavor for each quarter based on the values in the 2009 Sales and 2010 Sales worksheets.
1. If the Summary worksheet is not open, click on the Summary tab.2. Select cell B3.3. In the Formulas tab, Function Librarygroup, click the AutoSum button.4. Ensure that your cursor is blinking between the parentheses in the text =SUM().5. Open the 2009 Sales worksheet. Click once on cell B3.6. Hold down the SHIFT key and then open the2010 Sales worksheet. This will create a 3-D reference across both worksheets.7. Hit the ENTER key and then click once on cell B3.8. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.9. Click and drag down to cell B25.10. When you release the mouse button, the cell range should now display the units sold in the first quarter of 2009 and 2010 for each flavor.11. Move your cursor over the AutoFill handleat the lower-right corner of cell B25. The pointer should change from a thick white cross to a thin black cross.12. Click and drag down to cell E25.13. When you release the mouse button, the cell range should now display the units sold in the remaining quarters of 2009 and 2010 for each flavor.
In the Budget Plan Chart worksheet, apply a Round Bevel (or Circle) to the illustration.
1. In the Budget Plan Chartworksheet, select the image above the chart.2. In the Picture Formatcontextual tab, locate the Picture Styles group, and select Picture Effects.3. In the dropdown menu, locate Bevel, and select Round Bevel, (or Circle) from the Bevel group.
In the Income worksheet, use the format painter to apply the format of the column headings in G4:H4 and apply the format to B5:C5, B12:C12, B19:C19 and B26:C26
1. In the Income worksheet, select cells G4:H4, in the Home tab, go to the Clipboard group, and click Format Painter.2. Select cells B5:C53. Repeat steps 1, apply the format of cells G4:H4 to cells B12:C12, B19:C19 andB26:C26
In the Income worksheet, insert a 3-D Column Chart with the data range G11:H15. Position the chart to the right of the data.
1. In the Income worksheet, select the data in cell range G11:H15, in the Insert tab, go to the Charts group, select the Column drop down and select 3-D Column Chart.2. Move the chart you created to the right of the data.
In the Income worksheet, format cell range B4:H34 to have a cell style of Currency.
1. In the Incomeworksheet, select cell rangeB4:H34.2. In the Home tab, locate the Number group, and change the Number Format dropdown toCurrency.
Merge & Align Right the content in cells A2 through D2 in the June worksheet.
1. In the June worksheet, select cellsA2:D2. In the Home tab, locate theAlignment group, and click Merge & Center.2. Select the merge cells in A2:D2. In theHome tab, locate the Alignment group, and click Align Text Right.
In the Marketing Budget Plan worksheet, sort the data in the cell range A78:D85 by Column B, Largest to Smallest.
1. In the Marketing Budget Plan worksheet, select the cell range A78:D85.2. In the Data tab, locate the Sort & Filter group, and click Sort.3. In the Sort dialog box, Sort By Column B, Sort On Values, and Order from Largest to Smallest. Click Ok.
In the Marketing Budget Plan worksheet, cell E51, insert a COUNTIF formula to count the data in cell range D43:D51 that is greater than 0
1. In the Marketing Budget Planworksheet, select cell E51.2. In the Formula tab, locate theFunction Library group, and clickInsert Function.3. In the dialog box, search for theCOUNTIF function. Select it from the list, and click OK.4. In the Function Argumentsdialog box, enter D43:D51 as the range, and ">0" as the Criteria, and click OK.
In the Marketing Budget Planworksheet, select cells A2:D95 and Filter the Category by the Black cell color.
1. In the Marketing Planworksheet select the cell range A2:D95. In the Datatab, locate the Sort & Filter group, and click Filter.2. Click the filter dropdown on the Category column, and select Filter by Color, Filter by Cell Color, and select the Black box.
In the Profit - Loss Summary worksheet, name cell C5EstIncome and C6 EstExpenses. In cell C9 use a formula that subtracts EstExpenses from EstIncome.
1. In the Profit - Loss Summary worksheet, select cell C5. Right click the cell, and select Define Name.2. In the dialog box, enter EstIncome. Click Ok.3. Repeat step 1 and 2 for cell C6 and name EstExpenses before continuing to step 4.4. In cell C9 enter the formula =EstIncome-EstExpenses to subtract the values of the 2 named ranges. Press Enter.
In the Totals worksheet in column B, enter a cell reference to the matching Totals values from the June worksheet.
1. In the Totals worksheet, select cell B2 and type = to begin a formula.2. In the June worksheet, select cell B34, and hit enter. The contents of Cell B2 in the Totalsworksheet should read, =June!B34.3. In the Totals worksheet, select cell B3 and type = to begin a formula.4. In the June worksheet, select cell C34, and hit enter. The contents of Cell B3 in the Totalsworksheet should read, =June!C34.5. In the Totals worksheet, select cell B4 and type = to begin a formula.6. In the June worksheet, select cell D34, and hit enter. The contents of Cell B4 in the Totalsworksheet should read, =June!D34.
In the 2010 Sales worksheet, format the data range A2:G26 as a table using the Table Style Dark 5 table style.
1. Open the 2010 Salesworksheet.2. Select cells A2:G26.3. In the Home tab, Stylesgroup, click the Format as Tablebutton and choose Table Style Dark 5 from the drop-down list.4. Click OK.
In the Annual Sales worksheet, in cell B17 use the COUNTIF function to count the number of times that the values in cell range B6:B15 exceed 5000.
1. Select cell B17. In the Formulas tab, locate the Function Library group, and click Insert Function.2. Type COUNTIF in the Search for a Function field. Select COUNTIF from the results and click OK.3. In th Range field, type B6:B15, and in the Criteria field, type >50004. Click OK.
In the June worksheet, enter a formula in cell B34 that will get the SUM of the cell range B4:B33. Copy the formula to columns C and D.
1. Select cell B34. In the Formulastab, locate the Function Library group, and click Insert Function.2. In the dialog box click SUM and click Ok.3. Enter B4:B33 in the Number1 area and click Ok.4. Select cells B34:D34. In the Hometab, locate the Editing group, select Fill, and click Right.
In the Product worksheet insert a LineSparkline into cell F33 that references the data in cells B33 through E33.
1. Select cell F33. In the Inserttab go to the Sparklines group, click Line.2. Insert B33:E33 in the Data Range: field, and click OK.
Correct the function in the Overall column. The values Profitable or Marginal should display, depending on whether the quarterly sales exceed research costs.
1. Select cell K3.2. In the Formula Bar, replace the comma afterSUM(D3,F3,H3,J3) with a greater than sign >. Add a quote " after the textProfitable and before the text Marginal.3. Hit the ENTER key.
Apply the Blue, Table StyleMedium 20 style to the table on the current worksheet.
Click anywhere within the table Click the Design tab under Table Tools In the Table Styles group, click the Table Styles drop-down arrow In the Medium section, click the Blue, Table Style Medium 20 style
Convert the table on this worksheet to a range of cells.
Click anywhere within the table Click the Design tab under Table Tools In the Tools group, click Convert to Range
Center-align the Month heading in cell A3. Indent the Total text in cell A8. Set the angle for the text in cell B3 to 30degrees.
Click cell A3 In the Home tab, Alignment group, click the Center option Click cell A8 In the Alignment group, click the Increase Indentbutton Click cell B3 Click the Alignmentdialog box launcher Click and drag the text orientation angle to 30degrees Click the OK button
In cell F3, add a line-based sparkline that plots the categories for the Clearwater location.
Click in cell F3 Click the Insert tab In the Sparklines group, click Line Select cells B3 through E3 to populate the Data Range field Click the OK button
In the table on the current worksheet, filter the records to display records with an item type of Machine.
Click the Filter drop-down arrow in cell B2 Clear the Select Allcheck box Select the Machinecheck box Click the OK button
Copy the totals from row 38 on the January worksheet and paste just the values into cells B38 through M38 on the Forecasting worksheet.
If necessary, click the January worksheet tab Select cells B38 through M38 In the Home tab, Clipboard group, click the Copy button Click the Forecastingworksheet tab Click cell B38 In the Clipboard group, click the Paste drop-down arrow Click the Paste Values option
Change the options on this worksheet so that Gridlines are visible.
1. Click the Page Layouttab, locate the Sheet options group, and selectView Gridlines.
Create a hyperlink in the cell B12 that links to the file EXCEL07.xlsx.in the GMetrixTemplatesfolder.
1. In Cell B12, click the Insert tab, locate the Links group, and click Links.2. In the Address box, type EXCEL07.xlsx, and click OK.3. Optionally, click on the file inside the GMetrixTemplates folder called EXCEL07.xlsx and click OK.
Change the page orientation to landscape.
1. In the Page Layout tab, Page Setup group, click theOrientation button and choose the Landscape option from the drop-down list.
Paste only the values from the cell range A1:G26in the 2009 Sales worksheet to cell range A1:G26in the 2009 Sales (Corrected) worksheet.
1. Open the 2009 Sales worksheet.2. Select cells A1:G26. In the Hometab, Clipboard group, select Copy.3. Open the 2009 Sales (Corrected)worksheet and select cell A14. In the Home tab, Clipboardgroup, select the drop down forPaste. Select Values.
In the Summary worksheet, link the text 2009 Sales to the 2009 Sales worksheet and 2010 Sales to the 2010 Sales worksheet.
1. Open the Summaryworksheet.2. Select cell A28.3. In the Insert tab, Links group, select Links.4. Under Link to, choose Place in This Document.5. Under the Cell Referencecategory, in the Or select a place in this document list box, click on the text '2009 Sales'.6. Click Ok.7. Select cell A29.8. Repeat steps 3 and 4.9. Under the Cell Reference category, in the Or select a place in this document list box, click on the text '2010 Sales'.10. Click OK.
Add "fitness; classes; location" as tags to the current workbook. Then, add Uptown Athletic Club as the company for this workbook.
Click the File tab Click Add a tag Type: fitness; classes; location Click Show All Properties Click Specify the company Type: Uptown Athletic Club Press the Enter key
Save the current file as a PDF to the GMetrixTemplates folder. Accept the default options for the file name and settings.
Click the File tab Click Save As Click Browse If necessary, navigate to the GMetrixTemplates folder Click the Save as type drop-down arrow Click PDF Click the Save button
Perform an accessibility check on the current workbook. Rename the first warning instance to January and the second to Forecasting.
Click the File tab Click the Check for Issuesdrop-down button Click Check Accessibility In the Accessibility Checkerpanel expand the Default Sheet Names warning Select Sheet1 and then the Sheet1 dropdown Under Recommended Actionsselect Rename Sheet Type: January Press the Enter key Back in the Accessibility Checker panel select Sheet2 and then the Sheet2 dropdown Under Recommended Actionsselect Rename Sheet Type: Forecasting Press the Enter key
Set the current worksheet to repeat the top two rows and the first column to repeat on every page when the worksheet is being printed.
Click the Page Layout tab In the Page Setup group, click Print Titles Click in the Rows to repeat at top field In the worksheet select rows 1 and 2 Click in the Columns to repeat at left field In the worksheet select column A Click the OK button
On the current worksheet, set the rowheight for row 2 to 30 and the width of columns F and G to 12.
Click the number 2 in the rows to select the entire row Click the Home tab (if necessary) In the Cells group, click the Format drop-down arrow In the Cell Size section, click Row Height Type: 30 Click the OK button Click and drag over the letters F and G to select columns F and G In the Cells group, click the Format drop-down arrow In the Cell Size section, click Column Width Type: 12 Click the OK button
Move the entire Loan Amortization Scheduleworksheet in the EXCEL07.xlsxworkbook located in the GMetrixTemplates folder and insert it after the Time Cardworksheet in the EXCEL06.xlsxworkbook.
In the File tab, click on Open and click Browse (you might need to select Computer first if Browse isn't immediately available). In the dialog window, go to your documents folder. In the GMetrixTemplates folder, select EXCEL07.xlsx and click Open. In the EXCEL07.xlsx workbook, right click the Loan Amortization Scheduleworksheet, select Move or Copy. In the Move or Copy window, in the To book drop down select EXCEL06.xlsx, in the Before sheet section select move to end and click OK.
Find all instances of the word, Yoga, on the class schedule worksheet. Then, change the first instance of the word, Yoga, to Hot Yoga.
In the Home tab, Editing group, click the Find & Select drop-down arrow Click Find Type: yoga Click the Find Allbutton Click the first instance of the word, Yoga (it will take you to cell A3) Change the contents of cell A3 to the words, Hot Yoga Press the Enter key
Navigate to the Instructor4 named range. Then, change the contents of the cell to Charles.
In the Home tab, Editing group, click the Find & Select drop-down arrow Click Go To Click Instructor4 Click the OK button Click into cell A5 Change the instructor name in cell A5 to Charles Press the Enter key
Add the Fill Color icon to the Quick Access Toolbar.
In the Home tab, Fontgroup, right-click the Fill Color button Click Add to Quick Access Toolbar
Insert a footer at the bottom that contains the author, page number, and current date in that order.
In the View tab, locate the Workbook Views group and click Page Layout. Place your cursor anywhere in the header or footer to make the Header & Footer contextual tab show. In the Header & Footer contextual tab, locate the Header & Footergroup and click the Footerdropdown. Select the option that includes the author's name, page number, and current date in that order.
