SIMnet
Add a footer that displays just the page number in the center section. Do not include the word Page.
Header and footer tab Click Page Number button
Apply the Style 7 Quick Style to the chart.
Chart design tab Quick styles click style 7
Display the data labels on this chart above the data markers.
Chart elements button data lables arrow Select above
Resize the selected chart so it is approximately 11 rows tall.
Click and drag a resize handle up or down toward the center of the chart to make it smaller
Sort this data alphabetically from A to Z by values in the Category column.
Click data tab Sort A to Z
Move the Salaries worksheet so it is positioned before the working data worksheet.
Click salaries wks tab hold down mouse and drag to infront of working data worksheet tab
Add a new worksheet to the right of the Dec 9 sheet.
Click the new sheet button to the right of the last
Switch to the view where you can manipulate where page breaks occur.
Click the page break preview lower right corner button - to the left of zoom bar
Complete the two-variable data table in cells A7:E12. The formula has been entered for you in cell A7. The substitute values in cells B7:E7 reference the original cost of goods percentage in cell B3, and the substitute values in cells A8:A12 reference the original owner withdrawal percentage in cell B4.
Data tab - What if analysis button Click data table Row input - B3 Coloumn input - B4 press ok.
Autofit column D to best fit the data.
Double-click the right column boundary for column D.
In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon : in between. The result of the formula should look like this: Rent:Parking
Formula tab - function library click text button - select concat Type the following text box 1 - B2 text box 2 - : text box 3- C2 press OK.
Hide all of the dependency tracer arrows at once.
Go to formulas tab Click remove arrows
Hide the TimeSheets worksheet.
Home tab - cells group Format button - point to Hide and Unhide Click hide sheet
Add a new worksheet to the left of the Dec 9 sheet.
Home tab - cells group Insert button arrow insert sheet
Insert a column to the left of column E.
Home tab - cells group insert button arrow Insert sheet columns
Convert the cell range to a table using table style Table Style Light 8 (the first style in the second row under the Light styles). The table should include headers.
Home tab - styles group Format as table button Table style Light 8
Rename Sheet1: Dec 16
Right click sheet 1 Click rename rename as Dec 16 and press enter
Switch to the view that shows all the worksheet elements as they will print, including headers and footers.
Page layout button to left of zoomer (its the middle button)
Apply the preset margin option that will add the least amount of white space around the edges of the printed page.
Page layout tab Margarins button Narrow
Insert a PivotChart using the first pie chart type.
PivotTable tab - tools group Click pivotchart button click pie click ok
Add slicers to filter the data in this table by Date.
Table design tab Insert slicer Click date check box click ok
On the Year1 sheet, in cell B8, enter a formula to display the value of cell B7 from the Salaries sheet.
Type = click salaries sheet tab Click cell B7 and press enter
Use Formula AutoComplete to enter a SUM function in cell B7 to calculate the total of cells B2:B6
Type = in B7 Type SU double click SUM click and drag to select cells B2 - B6
Name cell B9 as follows: COLA
Type COLA in name box to left of formula bar and press enter
There is an error in cell B7. Accept Excel's suggestion for fixing the error.
click cell B7 click the smart tag click update formula to include cells
Sort this data by values in the Cost column so the most expensive items are listed first.
datatab Sort Z to A
Enter a formula in cell C2 to calculate next year's salary increase by multiplying the previous year's salary (cell B2) times the Cost of Living Adjustment (cell named COLA). Use the cell name in the formula.
double click cell C2 Type =B2* then type c double click COLA and press enter
Preview how this worksheet would print with formulas showing instead of calculated values.
formulas tab - show formulas button file tab - print
Insert a Line with Markers chart based on the selected cells.
insert tab - charts group click insert line chart Line with markers
Modify this worksheet so the numbers at the left of each row and the letters at the top of each column do not show.
view tab Click freeze panes Freeze top row
Clear the filter from the Category column.
Data tab Clear button
Sort the Date column so the oldest dates are listed first.
Data tab Click sort A to Z
Enter a formula in cell B7 to display the text from cell A7 with only the first letter of each word in upper case.
Formulas Ribbon Tab Function Library Ribbon click the Text button. In the Text menu click PROPER Inside the Function Arguments dialog, you typed A7 in the Text input, clicked the OK button.
Show the tracer arrows from the precedent cells to cell C7.
Formulas tab click Trace Precendants
Insert the current date in cell A1. Do not include the current time.
Formulas tab Click date and time button click TODAY OK if says dat and time then select NOW
Hide the formulas in this worksheet and display the values instead.
Formulas tab Click the "show formulas" button
Enter a formula in cell B10 to return a value of 35000 if the Net Profit After Tax (cell B9) is greater than or equal to 470000 or 100 if it is not.
Formulas tab click logical button Select IF enter B9>=470000 in logical test box enter 35000 in the value if true box enter 100 in the value if false click ok
Enter a formula in cell B3 using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3. Use the name Abbreviation for the lookup table. The item names are located in column 2 of the lookup table. Be sure to require an exact match.
Formulas tab lookup and refernce Select VLOOKUP lookup value - A3 Table array - abbreviation col_num - 2 Range_lookup - False click ok
In cell D15, enter a formula using a counting function to count the number of cells in the Billable? column (cells D2:D14) that are not blank.
Formulas tab - click more functions COUNTA D2:D14 drag
Enter a formula in cell B7 to display the text from cell A7 in all lower case letters.
Formulas tab - Function library group Text button - select LOWER Type A7 in text box and press ok
In cell E15, enter a formula using a count function to count the numbers in the cost column (E2:E14)
Formulas tab - Function library group click more functions button point to statistical and select COUNT click and drag to select cells in E2:E14
Display the formulas in this worksheet.
Formulas tab - Show formulas
In cell E2, enter a formula using TEXTJOIN to combine the text from cells B2:D2 into a single text string. Use the # character as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument.
Formulas tab - Text button Select TEXTJOIN Type # in d argument box Type TRUE in the ignore empty box Type B2:D2 in text box 1 click ok
This workbook has two named ranges with the same name: Salaries_Year1 Rename the one that is limited in scope to the Projections list to: BaseSalaries Close the Name Manager when you are finished.
Formulas tab - click name manager Click Salaries_Year1 and then click edit Type BaseSalaries click ok click close
Use the Create from Selection command to create named ranges for the selected data table in cells B2:E6 using the labels in row 1 as the basis for the names.
Formulas tab - defined name groups click create from selection If top row box is checked then click ok
This workbook includes two named ranges that both refer to cell B9. Delete the named range named CostOfLivingAdjustment.
Formulas tab - defined names Click name manager button Click cost of living press delete, ok, and close
Edit the CurrentSalaries named range so it refers to cells B2:B6 on the Salaries worksheet. Close the Name Manager when you are finished.
Formulas tab - defined names group Name manager button Click current salaries Click check mark to left to accept changes ok
Enter a formula in cell B9 to display the text from cell A9 in all upper case
Formulas tab - function group - click the Text button. Select UPPER from the list. Type A9 in the Text box, click ok.
Using cell refrences enter a formula in B6 to calculate monthly payments for the loan described in this worksheet. Omit the optional arguments. Use a negative value for the Pv argument
Formulas tab - function l group - click finnanical button & click PMT. Enter B3/12 in rate argument box. B4 in the Nper box. -B2 in PV box click ok
In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14).
Formulas tab - function library group Click auto sum arrow - click max hit enter (For lowest line click min)
Use the Function Arguments dialog to enter a SUM function in cell B7 to calculate the total of cells B2:B6.
Formulas tab - functional l group Math and trig button click SUM click ok
Show the tracer arrows from cell C2 to the cells that are dependent on it (cells containing formulas that reference the value or formula in cell C2).
Formulas tab - trace dependents
