Upgrade to remove ads
Only $35.99/year

Excel 3

Terms in this set (35)

In the annual sales worksheet, use the find&select tool to find the cell in the open spreadsheet that contains conditional formatting and apply a thick outside boarder
1. Home--> Find&Select-->conditional formatting
2. Home-->Font Group-->Borders drop down-->Thick outside borders
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 the editing group and click autosum
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 the Formulas tab, go to the Function Library and click Insert Function

2. In the dialog box inside the Search for a function box enter CONCATENATE. Click Go, and select from the list below

3. In the TEXT1 field enter: B4, in the TEXT2 field enter a Space, in the TEXT3 field, enter: A4, click OK
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 Home tab, locate the Editing group, click the Fill drop down menu and select Series

2. Click OK in the dialog box
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. n 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 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 home tab, locate the number group, click the numebr format drop down, and select currency
3. change font to courier new
4. highlight section as yellow
in the product worksheet, cahnge the format of the numbers in cells b8:e33 so that no decimal places are showing
1. highlight b8:e33

2. home tab--> number group --> decrease decimal, until there are no decimals on the numbers
merge & align right the content in cells a2 through d2 in the june worksheet
1. in the june worksheet, select cells a2:d2

2. Home tab --> alignment group --> merge&center

3. then click align text right
Add the title average to column H
select the cell h3 and type Average
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 the cell H4 type: = SUM(E4:G4)/3
Hide column A
right click on column A and select Hide from the menu
change the format of the cell containing the company name FusionTomo Inc. to the cell style Title
select title -->home-->styles-->cell styles-->title
Save this spreadsheet as a PDF file named EXCEL05 in the GmetrixTemplates folder. Do not open the file after publishing
1. In the file tab, select export
2. click create PDF/XPS
3. 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 publishing option if needed
6. click publish
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. Home tab--> editing group-->sort&filter-->custom sort

3. in the sort by row, select: total, sort on: values, order: largest to smallest
Click OK
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.
Apply a conditional formatting to the values in columns E, F, and G. Apple a yellow fill to any cells with a value equal to 5
1. Highlight the columns E, F, and G
2. Home tab--> Styles Group--> Conditional Formatting--> drop down menu click highlight cell rules--> 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 pop up box, click the Fill tab, select yellow, and click OK twice
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 Filter
3. In C3 click the drop down next to Position. In the drop down, unselect all the check boxes except Developer, and click Ok.
In Annual Sales worksheet, insert a column Sparkline in the cell G6 that references the data in the cells B6 through E6.
1. Highlight cell G6. in the Insert tab, locate the Sparkline group, and click Column.
2. In the dialog box for the Data Range field enter: B6:E6 and click Ok
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 Totals worksheet 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 Totals worksheet 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 Totals worksheet should read, =June!D34.
Change the chart in the Totals worksheet to the 3-D Pie type.
1. In the Totals worksheet, select the chart. In the Chart Design 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 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 Illustrations group, and click Pictures.
2. Locate your GMetrix Templates folder inside your documents folder, select ftlogo.gif and click Insert.
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.
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 Home tab, locate the Editing group, click the Find & Select dropdown, and click Data Validation.
2. With cell B2 selected, click the Data tab, locate the Data Tools group, select Data Validation and click Data Validation...
3. In the dialog box, click Clear All, then 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
Change the options on this worksheet so that Gridlines are visible.
1. In the View tab, locate the Show group, and check the Gridlines box.
Change the margins to the Narrow setting.
1. In the Page Layout tab, locate the Page Setup group, click the Margins dropdown, and select Narrow.
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 >5000
4. Click OK.
Insert the Basic Process SmartArt graphic below the data in this worksheet.
1. Select any cell below the data. In the Insert tab, locate the Illustrations group, click SmartArt, and select Basic Process from the Process section.
Insert the Basic Process SmartArt graphic below the data in this worksheet.
1. Select any cell below the data. In the Insert tab, locate the Illustrations group, click SmartArt, and select Basic Process from the Process section.
2. 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 the Insert tab, locate the Charts group, click the Column dropdown, and select Clustered Column from the 2D group.
2. Select the chart, and reposition it so that it is below the data.
In the Source Data worksheet Sort the Data alphabetically 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, select CustomerID, verify that sort on: is Values, and that the Order dropdown is A to Z.
3. Click the Add a Level button. In this new level, select Product in the Sort By box, and verify that the other 2 values are Values and A to Z. Click OK
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 Setup group, and click Print Titles.
2. In the Page Setup dialog window, in the Sheet tab, go to the Print titles section, click into the Rows to repeat at top field and select row 1 in the worksheet. Click OK.
Filter the data so that only rows with a value that is Above Average are displayed in the Fiction column.
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.
In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Sales worksheet, cell F16, multiplied by 1 plus the growth value in the Quarterly Sales worksheet, cell B3
1. In the Quarterly Sales worksheet, cell B9, begin a formula by entering the = character.
2. Switch to the Annual Sales worksheet, and click on cell F16.
3. Type *(1+ and then click on the Quarterly Sales worksheet, cell B3. Finally, close the formula by typing ). Press Enter
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 the Info tab, select Check For Issues and click the Inspect Document button.
2. In the dialog box, make sure Document Properties and Personal Information is selected and click Inspect.
3. Click Remove All from the Document Properties and Personal Information section. Click Close
Students also viewed