Excel test
Insert a page break between row 18 and row 19 of the worksheet
- Choose page layout in the ribbon - click row 19 - the press break button - choose insert page break
Use the PivotTable command on the Insert tab to insert a PivotTable in a new worksheet using the WorkshopParticipants table as the source range.
- Click cell A4 - Click the Insert tab on the ribbon - in the Tables group, click the PivotTable button - click PivotTable - Click ok
Apply the Light Orange, Pivot Style Medium 21 style to the Workshop Participants PivotTable (Medium section, row 3, last option).
- Click cell A4 - Click the Design tab - in the PivotTable Styles group, click the more button - click the next more button - choose the correct style
In cell B9, create a formula using the ROUND function that rounds the value in cell K9 to an integer, with 0 (zero) decimal places.
- Click cell B9 - in cell B9 type =ROUND( - click cell K9 - The formula should be =ROUND(K9,0) - Click the enter button in the formula bar
In cell D14, enter a formula using the MEDIAN function to calculate the median value in range D4:D11
- Click cell D14 - On the formula bar, click the Insert Function (fx) button - Type MEDIAN in the search for a function box & and press go - Click ok - in the Number 1 slot type in D4:D11 - Click ok
Apply the 03/14/12 Date number format to cell D4
- Click cell D4 - In the home tab, choose the number option - in the bottom corner press the dialog launcher - choose the correct format and click ok
In cell F3, create a formula using the TODAY function to display the current date.
- Click cell F3 - click the Formulas tab - Click the Function Library button - Then choose Date & Time - Click TODAY - press ok
Use the Format Painter to apply the formatting from cell B11 to the range B12:B19
- select cell B11 - In the Clipboard group, click the Format Painter button (paint brush) - then highlight cell range
Align the contents of cell B4 vertically using the Bottom Align option
- select cell B4 - in the home tab, choose Alignment - press the dialog box at the bottom - Under vertical, choose bottom - press ok
Use the fill handle to copy the formula in cell B7 to cells C7 and D7
- select cell B7 - drag the corner of B7 over the cells C7 and D7
Center the contents of cell C3 horizontally
- select cell C3 -In the Alignment group on the Home tab, click the option Alignment - In the pop up click the center button - excel centers it
In cell E5, create a formula using the VLOOKUP function to determine the bonus percentage for employee Wanda Allen based on the performance rating in cell C5. Use range G4:H10 as the lookup table and the bonus percentage listed in column 2 of the lookup table. Do not enter a value for the optional range_ lookup argument
- select cell E5 - type =VLOOKUP(C5,G4:H10,2) - press enter
In cell F4, enter a formula using the IF function that returns a value of YES if cell E4 is greater than 100,000 and a value of NO if not
- select cell F4 - click the formulas tab on the ribbon - select the function library - click insert function - click the or select a category arrow - click logical - click IF - click ok - Tyoe E4>100000 in the Logical_test box - press TAB - Type YES in the Value_if_true box -press tab - type NO in the Value_if_false box - click ok
Apply Orange, Table Style Medium 14 (Medium section, row 2, last option) to the table.
-Click cell A5 -Click the Table design tab -In the Table Styles group, click the More button - Click the More button again when the tab is down - In the Medium section, click Green, Table Style Medium 7, the last option in the first row of the section
Apply the Integral theme (row 1, column 4) to the current workbook.
-Click the Page Layout tab on the ribbon - Click the Themes button - in the gallery choose the Integral Theme
Apply the Berlin theme (row 4, column 1) to the current workbook
-Click the Page Layout tab on the ribbon - In the Themes group, click the Themes button - In the themes gallery choose the correct theme
Use the Autofill feature to fill the range A4:A15 with the names of the months, in chronological order, starting with January in cell A4
-click cell A4 - Type in January - drag the corner down to cell A15
Convert the Workshop Participants table in range A3:D18 to a range
-click cell A5 - click the Table Design tab - in the Tools group, click the Convert to Range button - Click yes
Freeze columns A and B, and rows 1 through 2 in the worksheet
-click cell B4 -Click the View tab -in the window group, click the Freeze Panes Button - click Freeze Panes
Apply chart Style 4 to the chart (row 1, option 4)
-click the chart - click the chart design tab - in the Chart Styles group, click the More button - in the gallery, click Style 4
In cell B6, create a formula using the ROUND function that rounds the value in cell J6 to an integer, with 0 (zero) decimal places
-in cell B6 type =ROUND(J6,0)
In cell D14, create a formula using the MIN function to calculate the minimum value in the range D4:D11.
-select cell D14 - in the Editing group, click the AutoSum arrow - click Min - =MIN(D4:D11) enter
Change the worksheet margins to the Narrow margin style
- Select the page layout tab in the ribbon - select margins - choose narrow
In cell B7, create a formula using external and internal worksheet references, that subtracts cell B9 on the Expenses worksheet of the PB Expenses.xlsx file, from cell B5 on the Income Statement worksheet of the PB Financials.xlsx file.
- Click on cell B7 - type an = (equal sign) in cell B7 to start the formula - click cell B5 - Type the minus sign (-) in cell B7 - Click the View tab - Press the Window button - Press Switch Windows - Choose your files from the list that appears - On the sheet you have just opened click cell B9 - Press ENTER (Excel should switch you back to your original worksheet and complete the formula for you)
Delete the defined name Kitchen_January from the worksheet.
- Click the Formulas tab - In the defined Names group, click the Name Manager button - select Kitchen_January - at the top of the pop up press delete
Change the Pie chart to a Clustered Bar chart. Choose the option with all red data bars and city names listed in the vertical axis.
- Click the Pie chart - Click Chart Design - In the Type group, click the Change Chart Type button - click column - click the clustered column option with the red data bars and city names - press ok
Change the fill color of cell E3 to Orange, Accent 6, Lighter 60% (Theme Colors section, row 3, column 10).
- Click the cell E3 - in the Font group, click the Fill Color arrow - choose color
For range B4:B15, create a new conditional formatting rule that formats the top five values in the range with a Blue, Accent 5 cell background color (9th column, 1st row in the Background color palette)
- Highlight cell range - In the home tab, press conditional formatting - Press the button new rule - Select the option format only top or bottom-ranked values - In the box where it says ten, change it to the number 5 - press format - Then at the top press fill - Then choose your color and press ok
Apply the Accounting number format, using the dollar sign ($) and two decimal places, to cell range B5:B16
- Highlight cell range - On the home tab click number - press the arrow by custom down - choose accounting
Apply the comma style number format to range C5:C18
- Highlight cell range - in the home tab, press number - under the drop down arrow, press the comma
Insert a 2-D Line chart in the worksheet based on range A3:F8.
- Highlight cells A3:F8 - Click the Insert tab on the ribbon - In the Charts group, click the Insert Line or Area Chart button - Select a 2-D Line chart
Insert a Clustered Column - Line Combo chart in the worksheet based on range B3:D11. Plot the Number of Participants data series on a secondary axis.
- Highlight cells B3:D11 - Click the Insert tab on the ribbon - In the Charts group, click the Recommended Charts button - In the Insert Chart dialog box, click the All Charts tab - click combo at the bottom of the list - Choose the correct combo
Clear the conditional formatting rule from the range B4:B15
- Highlight the cell range - In the home tab, press conditional formatting - then choose clear rules - then press clear rules in selected cells
for values in the range B3:B12 adjust the number of decimal places so that two decimal places are displayed
- Highlight the cell range - in the home tab, press the number tab - in the number group, click the increase decimal button - click the button again
Copy the values and number formatting, but not the underlying formulas, of cells G15:I15 into range B9:D9
- Highlight the cells G15:I15 - in the Clipboard group on the Home tab, click the Copy button - Click cell B9 In the Clipboard group, click the paste arrow - in the Paste Values section, click the Values & Number Formatting option
Rotate the contents of the cells in range A3:D3 to exactly 45 degrees, which is three points up and to the left of the 0 (zero) degree marker on the Orientation arc
- Highlight your cell range - In the home tab press alignment - Press the dialog box launcher in the bottom right corner - On the arc, click the marker three points up and to the left of 0 (zero) degree marker - OR type 45 in the box where it says zero under the marker - press ok
Apply the percentage number format, with no decimal places, to the range of C4:C15
- Highlight your cell range - In the home tab, press numbers - click the percentage icon
Apply the currency number format, using the $ symbol and showing two decimal places, to range C4:C15
- Highlight your cell range - In the home tab, press the Number arrow down - press arrow down, and choose currency
Group the January, February, and March worksheets, then for range A3:C3 in all grouped worksheets, apply bold formatting to the text and change the fill color of the cells to Orange, Accent 6 (Theme Colors section, row 1, last option).
- Hold the Control button on your keyboard and press the January, February, and March tabs at the bottom of the worksheet OR press January then hold shift and press March and all of the tabs with group - select cells A3:C3 - On the Home tab in the Font group, click the Bold button - Then in the Font group, click the Fill Color arrow - click the appropriate color
Add the text Confidential to the center header section, and then deselect the header
- In the ribbon choose the View tab - Then choose page layout - Click Add Header - and type in Confidential - Click cell A4
Add the Sheet Name header element to the center header section and add the text Last Viewed_ followed by the Current date header element to the right header section. Click cell A4 to deselect the header when you are finished
- On the status bar ( middle button on the very bottom of the page), click the Layout button - click the center section of the header placeholder, which contains the text "Add header" - At the top, press Header & Footer - Select sheet name - Click the right header section - type Last Viewed_ in the right header section - then click Current Date Button at the top - then in the worksheet click cell A4
Enter a formula in cell C6 that divides the value in cell B6 by the value in cell B18, using an absolute cell reference to cell B18
- Select cell C6 - type =, then select cell B6/B18 - press F4 key (macbook: hold down fn key and it pops up on touch bar) - press enter
Bold the text in cell E13.
- Select cell E13 - press the bold button
Create a custom cell style named Column Header based on the formatting in cell A3. Add Bold formatting to the new cell style; remove the existing Border setting in the style
- click cell A3 - in the home tab on the ribbon, choose cell styles - at the bottom choose new cell style - in the style name box, type Column Header - press format - in the font style list, click Bold - click ok - Then click the Border check box to deselect it - then click ok
Add the state field to the Filters area of the PivotTable Fields pane
- click cell A4 - Choose fields to add to the report area, click and drag the region field to the Filters area. Release the mouse button
Assign the name Costs to the table.
- click cell A4 - click Table Design tab - Under the Table Name section press Table 1 and type in 'Costs' -press enter
in cell B18, create a formula using the AVERAGE function to calculate the average of the values in the range B5:B16
- click cell B18 - type =AVERAGE(B5:B16) - press enter
Use Goal Seek to calculate the changing value in cell B5 that will result in a set value in cell B20 of your $1,000
- click cell B20 - click Data tab on the ribbon - Click Forecast - click the What-If Analysis - click Goal seek - click the To value text box. Type 1000 in the text box, and press TAB - click cell B5 - press ok
Apply the Bad cell style to cell B8
- click cell B8 - On the home tab go to the styles group and press on cell styles - click the Bad style
In cell C6, enter a formula that multiplies the value in C5 for the month of January by the value in cell B6. Use a mixed reference for cell B6 so that only column B does not change when copied. Use relative references for all other cell references.
- click cell C6 - in cell C6, type =C5*$B6 and press enter
Use AutoFill to fill range D12:D19 with only the formatting from cell D11
- click cell D11 - drag the fill handle at the bottom of cell D11 to the bottom of cell D19 - click the Auto Fill Options button - click Fill Formatting Only
Change the fill color of cell D9 to Blue, Accent 1, Lighter 60% (column 5, row 3 in the Theme Colors palette)
- click cell D9 - In the home tab, press the font arrow down - Click the fill color arrow - choose your color
Change the color of the font in cell D9 to Blue (standard colors section, column 8)
- click cell D9 - In the home tab, press the font arrow down - choose the font color option - choose your color
Set row 3 as print titles for the worksheet
- click page layout in the ribbon - In the Page Setup group, click the Print Titles button - Click the Rows to repeat at top text box - click cell B3 - click ok
Insert the picture file Network.Jpg as the worksheet background
- click page layout in the ribbon - choose background - Click From a file - click the Network.jpg picture icon to select it - press insert
Use the Recommended PivotTables command to insert a blank PivotTable in a new worksheet using the Workshop Participants table as the source range.
- click the cell A4 - click the Insert tab - In the Tables group, click the Recommended PivotTables button - at the bottom of the dialog box, click the Blank PivotTable button
Edit the conditional formatting rule for range B5:B16 to highlight cells whose value is less than $25,000. use the same cell formatting as the original rule
- highlight cell range - in the home tab, choose conditional formatting - choose manage rules - select edit rule - in the edit formatting rule dialog box, under Format only cells with, click the greater than arrow - choose less than - click ok
Highlight cells in the range B4:B15 whose value is less that $50,000 using Yellow Fill with Dark Yellow Text
- highlight cell range - in the home tab, press conditional formatting - select highlight cell rules - click the less than option, and type in 50000 - then click the format cells arrow right by it, and choose Yellow fill and Dark yellow text - click ok
Set the print area as range A2:C16.
- highlight cells A2:C16 - click page layout - click the PrintArea button - click Set Print Area
Insert a 3-D Clustered Column chart (the 1st 3-D Column chart option) in the worksheet based on range A3:I6.
- highlight cells A3:I6 - click the Insert tab - In the Charts group click the Insert Column or Bar Chart button - Click a 3-D Clustered Column Chart
Indent the contents of range A11:A19 by a single indentation level
- highlight range - In the Home Tab click Alignment - In the Alignment group, click the Increase indent button
Merge and center the contents of the range A2:D2
- highlight range - In the home tab press alignment - press merge & center
In cell B12, use the Quick Analysis tool to create a formula that uses the Sum function to total the values in the range B3:B11
- highlight range - click the Quick Analysis button - click Totals - click sum
In cell D19, create a formula using the MAX function to calculate the maximum value in the range D4:D17
- in cell D19 type =MAX(D4:D17)
Find and replace all instances of the word Mgr with the word Manager in the current worksheet
- in the Home tab section choose editing - Press arrow down, and choose find and select - choose replace - in the find what box type "Mgr" - in the replace with box type "Manager" - Click Find next - click replace - Click Replace All and then ok - then click close
Edit the hyperlink by adding Go to BLS website as a ScreenTip.
- right click cell A24 - click edit hyperlink - click ScreenTip - Type Go to BLS website - press ok
Change the color of the sheet tab
- right click your tab - press tab color - change to the color
In Page Layout View, set custom margins by changing the left and right margins to 0.5 inches. Do not change the top and bottom margins
- select Page Layout in the ribbon - click on margins, and then choose custom margins - set the left and right margins to 0.5
