Excel Refresh training

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, Window group, click theFreeze Panes button and choose theFreeze Panes option from the drop-down list.

Create a new worksheet based on the Expense Report sample template.

. 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.

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.

1. Click cell D2 2. Use the AutoFill feature to fill the cell values through cell P2 3.Click cell D3 4. Use the AutoFill feature to fill the cell values through cell M3

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.

1. Click in cell B8 2. Type: =B4+B5+B6 3. Click the fill handle in cell B8 and drag it through cell M8 4. Click in cell B9 5. Type: =B8*$Q$2 6. Click the fill handle in cell B9 and drag it through cell M9

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 A2through 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.

1. Click in cell D2 2.Type: =UPPER(A2) 3.Click the fill handle and drag to cell E2 4.Click the fill handle and drag to cell E4 5. Click in cell G2 6.Type: =LOWER(A2) 7.Click the fill handle and drag to cell H2 8. Click the fill handle and drag to cell H4 9.Click in cell J2 10.Type: =LEN(A2) 11.Click the fill handle and drag to cell J4

Using Autofill, add a Line sparkline to column L using the data in columns Cthrough J.

1. Click on cell L3. 2. In the Insert tab, Sparklines group, click Line. 3. In the Create Sparklines dialog box, for Data Range type C3:J3. 4. Click OK. 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 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 Format contextual tab, Arrange group, click the drop down for Rotate and choose Rotate Right 90*.

Set the cells in column A to allow for wrapping the text onto multiple lines.

1. Click the A to select column A 2. In the Home tab, Alignment group, click Wrap Text

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.

On the current worksheet, set the row height for row 2 to 30 and the width of columns F and G to 12.

1. Click the number 2 in the rows to select the entire row 2.Click the Home tab (if necessary) 3.In the Cells group, click the Formatdrop-down arrow 4.In the Cell Size section, click Row Height 5.Type: 30 6.Click the OK button 7.Click and drag over the letters Fand G to select columns F and G 8.In the Cells group, click the Formatdrop-down arrow 9.In the Cell Size section, click Column Width 10.Type: 12 11.Click the OK button

In the Review worksheet, use the CONCATENATE function to display the contents of cell B4and 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 Text2field enter a Space. 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 click AutoSum

Copy the totals from row 38 on the January worksheet and paste just the values into cells B38 through M38 on the Forecasting worksheet.

1. If necessary, click the Januaryworksheet tab 2. Select cells B38 through M38 3. In the Home tab, Clipboard group, click the Copy button 4. Click the Forecasting worksheet tab 5. Click cell B38 6. In the Clipboard group, click the Paste drop-down arrow 7. Click the Paste Values option

Change the Theme to Office.

1. In the Page Layout tab, go to the Themesgroup, click Themes and in the drop down clickOffice.

In the Profit - Loss Summary worksheet, name cell C5 EstIncome and C6 EstExpenses. In cell C9 use a formula that subtracts EstExpenses from EstIncome.

1. In the Profit - Loss Summaryworksheet, 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 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, 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 Library group, click the AutoSumbutton. 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 the 2010 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 AutoFillhandle 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 handle at the lower-right corner of cellB25. 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 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 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 theResearch section of the chart above.2. In cell C41, insert the number 0 from theCommunications section of the chart above.3. Continue inserting the information in the chart to the rest of the columns in the "Budget" row.

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 Format contextual 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 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 Linksgroup 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 laterbutton and click Ok.

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, 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 Chartsgroup, 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 June worksheet, in cells E4:E33 use the IF formula to display a 1 if the values of the cells D4:D33are 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, 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 the Function Library group, and click Insert Function.3. In the dialog box, search for theCOUNTIF function. Select it from the list, and click OK.4. In the Function Arguments dialog box, enter D43:D51 as the range, and ">0" as the Criteria, and click OK.

In the Profit - Loss Summary worksheet, in cell D8, use the IF function to show Closeif the SUM of C9:D9 is greater than 22500 and 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 Designcontextual 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 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 Totals worksheet enter a formula in cell B2 that 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 Sum dropdown, and click Sum.2. In the formula, type the name Fiction12, and press enter.

Insert a footer at the bottom that contains the author, page number, and current date in that order.

1. In the View tab, locate the Workbook Views group and click Page Layout. 2.Place your cursor anywhere in the header or footer to make the Header & Footer contextual tab show. 3.In the Header & Footer contextual tab, locate the Header & Footergroup and click the Footerdropdown. 4. Select the option that includes the author's name, page number, and current date in that order.

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 wordDelivery.2. Select cell D16 and add the wordBusiness.

Merge & Center cell range A1:D2

1. Select cell range A1:D2, in the Home tab, go to the Alignment group and select Merge & Center.

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 Hometab, go to the Editing group, click theFill tool and select Down.

Using the locations and data, create a table with headers consisting of the data in row 2.

1. Select cells A2 through E11 2. Click the Insert tab 3. In the Tables group, click Table 4. Ensure the My table has headers check box is selected 5. Click the OK button

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 Rows option.

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 & Filter group, clickSort. 3. In the Sort dialog box, from the Sort by drop down, choose Variant. Click Add Level. From the Then by drop down, choose Year. 4. Click Ok.

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 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 Numbergroup, click the Number Format drop down, and select Currency.3. In the Home tab, locate the Fontgroup, click the Font drop down, and select Courier New.4. In the Home tab, locate the Fontgroup, click the Fill Color drop down, and select Yellow.

Set a Print Area for cells A1:J13

1. Select the range A1:J13.2. In the Page Layout tab, locate the Page Setup group, and click the Page Setupdialog box launcher.3. In the Sheet tab of the dialog box, click into the Print area textbox, enter A1:J13, and click OK.

Using the Totals feature for tables, generate totals for columns C through F within the table on the current worksheet.

1.Click anywhere within the table 2.Click the Design tab under Table Tools 3.In the Table Style Options group, select the Total Row check box 4.Click cell C13 5. Click the drop-down arrow within the cell 6. Click Sum 7. Click cell D13 8. Click the drop-down arrow within the cell 9. Click Sum 10. Click cell E13 11. Click the drop-down arrow within the cell 12. Click Sum

Display all of the formulas on the current worksheet.

1.Click the Formulas tab 2.In the Formula Auditing group, click Show Formulas

Apply the Style 9 style to the Swimming Attendance chart on Sheet1. Then, apply the Monochromatic Palette 3 color combination to the chart.

1.Click the Swimming Attendance chart on the Sheet1 worksheet 2.Click the Design tab under Chart Tools 3.In the Chart Styles group, click the Chart Styles drop-down arrow 4.Click Style 9 5.In the Chart Styles group, click the Change Colors drop-down button 6.Click the Monochromatic Palette 3 color group option

Move the entire Loan Amortization Schedule worksheet in the EXCEL07.xlsxworkbook located in the GMetrixTemplates folder and insert it after the Time Cardworksheet in the EXCEL06.xlsx workbook.

1.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. 2.In the EXCEL07.xlsx workbook, right click the Loan Amortization Scheduleworksheet, select Move or Copy. 3.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.

Add the Fill Color icon to the Quick Access Toolbar.

1.In the Home tab, Font group, right-click the Fill Color button 2.Click Add to Quick Access Toolbar