Core Skill Training 2 and 3

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

In the Marketing Budget Plan worksheet, change the Page Size to A4.

1. in page layout tab locate page setup group click size and select A4

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

click cell L3 insert sparklines data range C3:J3 click ok autofill down

change the options on this worksheet so that gridlines are visible

click the page layout tab locate sheet options group click view gridlines

Using the data in the Combined Sales worksheet, add a Pie chart including labels that presents the combined sales for each representative.

select cells E2:E7 insert tab charts group pie drop down select data in select data dialog in the horizontal category axis labels section click edit select cells A3:A7 click ok twice

format the cell range A5:D106 to use center horizontal alignment

select cells A5:D106 in the home tab, alignment group, click the center button

Copy the Summary worksheet from the Quarterly_Earnings_Overview.xlsx workbook, found in your GMetrix Templates folder, and place it as the last worksheet in the Quarterly_Earnings.xlsx workbook.

1. in the file tab choose open 2. choose the quarterly earning overview from your gmetrix templates folder and click the open button 3. in the home tab, cells group, click the format button. Under organize sheet, select move or copy sheet 4. In the to book drop down choose quarterly earning clsc in the before sheet list, select move to the end 5. select the create a copy checkbox and click ok

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

move the entire loan amortization schedule worksheet in the EXCEL07.XLSX worbook located in the GMetrix Templates folder and insert it after the Time Card worksheet in the EXCEL06.xlsx workbook

1. in the file tab, click on open, select computer, and click browse. in the dialog window go to your documents folder, in the GMETRIXtemplates folder select EXCEL07.XLSX and click open 2. in the EXCEL07.xlsx worbook right click the loan amotixation schedule worksheet, 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 move to end an click ok

in excel options, change the save autorecover information to every 5 minutes

1. in the file tab, select the options to launch the excel options dialog box 2. in the save tab, locate the save workbooks group, and change the save autorecover information option to 5 minutes. click ok

modify the title style to use 20 pts

1. in the home tab, go to the styles group, select the cell styles button (the drop down arrow) on the styles, right click on the title style and click modify 2. in the style dialog window, click format, in the font group, go to the size section, select 20, and click ok twice.

1. 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 cell b5:c5 3. repeat steps 1, apply the format of cells G4:H4 to cells B12:C12, B19:C19, and B26:C26

In the marketing budget plan worksheet, edit the cell 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, in the name column look and highlight research and click edit 3. in the edit name window, int he refers to field, delete the data in the field and highlight cells A5:A8 4. Click Ok and click close

1. In the Marketing Budget Plan worksheet, 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 ws, 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 E18 using the data in D13:D16 5. Repeat 1 to 3 for E25 using the data in D21:D23

Set the labels in the fourth row to repeat on each page.

1. in the page layout group tab, page setup group, click the print tiles control 2. in rows to repeat at the top type $4:$4 3. 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 ws, in the insert tab, go to the text group and click header and footer 2. in the header click into the left field, in the design tab, go to the header and footer elements and click current date 3. in the header click into the right field, in the design tab, go to header and footer elements, and click page number 4. in the footer click into the left field, in the design tab, go to the header and footer elements and click sheet name and click out of the header and footer and into the sheet

Set the zoom to 150% and save as a custom view named PageZoom.

1. in the view tab zoom group click the zoom button 2. click custom and type 150 3. in the view tab, workbook views group, click custom views button 4. click add 5. type page zoom in the name text box and click ok

freeze the top row of the loan amortization schedule worksheet

1. in the view tab, locate the window group, click freeze panes, and select freeze top row

1. 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 the workbook views group and click page layout 2. in the view tab, locate the zoom group, and click zoom 3. in the zoom window, select custom, type 125% and click ok 4. in the view tab, locate the workbook views group and click custom views 5. in the custom views window, click add, in the name field enter layout 125 and click ok

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, select cell A27 2. in the home tab, clipboard group, select format painter 3. select cell A1 to apply the formatting 5. Select cell A28 6. in the home tab, clipboard group, double click the format painter buton 7. select cell A2:E2, A3:A25, hit the esc key to exit the format painter

1. 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. right click, define name 2. in the dialog box enter EstIncome. click ok. 3. repeat steps 1 and 2 for cell 6 and name EstExpenses before continuing to step 4 4. in cell c9 enter the formula =EstIncome-EstExpenses to subtract the values of the 2 names ranges. press enter.

1. In the Marketing Budget Plan worksheet, in cells D10, D18 and D25 create a formula that gathers the SUM of the data above in each of the cells

1. select cell D10. In the formulas tab locate the function library group, click autosum dropdown and click sum 2. enter cell D5:D9 as the range for the formula and hit enter 3. repeat steps 1 to 2 for cell 18 using cells D13:D17 4. repeat steps 1 to 2 for cell D25 using cells D21:D24

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. select cell E51 2. in the formula tab, locate the formula library, and click insert function 3. in the dialog box, search for the countif function. select it from the list, and click ok 4. in the function arguments dialog box, enter d45:d51 as the range, and ">0" as the criteria and click ok

In the Budget Plan Chart worksheet, apply a Round Bevel (or Circle) to the illustration

1. select image above chart 2. picture tools/format 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

n the Combined Sales worksheet, calculate the sales percentage for each representative in the % Total column and change the cell formatting to Percentage.

=E3/$E$8

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

Autosum make sure cursor is blinking in parenthesis(Sum) open 2009 ws click on b3 hold down shift key and then open the 2010 worksheet hit the enter key and then click once on cell b3 autofill down to other necessary cells

1. 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 22500 and Not Close if it is less than or equal to 22500.

Function Argument logical test SUM(C9:D9)>22500 Value if true:Close False: not close

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

in the formula bar, add an opening parenthesis after the text = and a closeing parenthesis before the text/. 4. change 'combined sales'!E3 to 'Sales 2010!D3 5. Hit the enter key 6. click on cell d3 again 7. move your cursor over the autofill handle at the lower right corner of the cell, drag to cell D7

Correct the function in the Overall column. The values Profitable or Marginal should display, depending on whether the quarterly sales exceed research costs.

in the formula bar, replace the comma after SUM(D3,F3,Hd,J3) with a greater than sign >. Add a Quote " after the text Profitable and before the text marginal hit the enter key

. In the Top Performers worksheet, determine the highest sales number for 2010 in cell B2.

in the formulas tab function library group click the drop down menu for autosum, select max ensure that the cursor is blinking between parentheses in the text =MAX(). open the sales 2010 select cells D3:D7 hit the enter key

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.

number 1 field: D6:D9 number 2 gield D13:D17 and so on

Define the name Bonus for the cell range A2:C6 in the Commission Rates worksheet.

open commisions worksheet select cells A2:C6 in the formulas tab defined name group select define name for name type bonus and click ok

Save the entire workbook as a PDF file in your GMetrixTemplates folder, omitting document properties and tags.

open the file tab and choose export in file types choose ccreate pdf/xps document and click create, make sure to be in gmetrix folder in the publish as pdf or xps dialog box click options in the options dialog box under publish what click entire workbook clear the checkboxes document properties and document structure tags for accessibility and click ok click publish

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.

pay attention to details

In the Commission Rates worksheet, 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 Bonus named range.

second part home tab, cells group, click insert sheet columns type bonus pay and hit enter select cell F3 in the formulas tab function library group select lookup and reference. select v lookup. in the lookup value box: type E3 in the table array box: Bonus Col Index Num box: type 3 12. click okay 13. select cell F3 and autofill

Remove all duplicates from the table based on the values in the Year and Value columns.

select A5:D106 in the data tab, data tools group, remove duplicates in the columns list clear the variable and variant checkboxes click ok twice

In the Expenses worksheet, in cell B21, enter the text Graphics in the row, and in cell B34 name the row Food.

select b21 type the text graphics select cell b34 type the text food

1. Apply a Filter to the data in the table that will only show miles driven for a Business Purpose.

select business purpose sells A4:J20 in the data tab go to sort and filter and click filter select the drop down arrow by the porpose column and only have business checked and click ok

split the work sheet at cell A5 into two horizontal planes

select cell A5 in the view tab windows group click the split button

1. In the Mileage Log worksheet, in cell J6, use a formula that subtracts G6 from H6 and multiplies the result by .36.

select cell J6 and enter the formula =(H6-G6)*36 and press enter