CIS105 Excel
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Telephone, Brick-n-Mortar, and Online. Include links to the source data to display subtotals. Font Size
On the Data tab, in the Data Tools group, click the Consolidate button. Click the Telephone worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Brick-n-Mortar worksheet tab. Click Add. Click the Online worksheet tab. Click Add. Click the Create links to source data check box. Click OK.
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Uptown, Downtown, and City Center. Do not include links to the source data. Font Size
On the Data tab, in the Data Tools group, click the Consolidate button. Click the Uptown worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Downtown worksheet tab. Click Add. Click the City Center worksheet tab. Click Add. Click OK.
This worksheet has validation rules applied. Find and circle cells that violate those rules. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button arrow and select Circle Invalid Data.
Data that violate the validation rules in this worksheet have been circled. Clear the validation circles. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button arrow. Click Clear Validation Circles.
Add Increase must be less than $5. as an error alert to the validation rules for the selected cells. Do not include a title. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button. Click the Error Alert tab. Type: Increase must be less than $5. Click OK.
Add Enter the dollar amount. as an input message to the validation rules for the selected cells. Do not include a title. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button. Click the Input Message tab. Type: Enter the dollar amount. Click OK.
Apply data validation rules to the selected cells to allow only decimal numbers less than 5.00. Allow blank cells. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button. Expand the Allow list and select Decimal. Expand the Data list and select less than. Type 5.00 in the Maximum box. Click OK.
In cells D2:D13, use data validation to display an in-cell drop-down list of values from the MedCodes named range. Allow blanks. Font Size
On the Data tab, in the Data Tools group, click the Data Validation button. In the Data Validation dialog, expand the Allow list and select List. In the Source box, type: =MedCodes Click OK.
Split the selected text into columns using the comma character as the delimiter. Font Size
On the Data tab, in the Data Tools group, click the Text to Columns button. Click Next. Click the Comma check box. Click Next. Click Finish.
Complete the one-variable data table in cells G2:H7 to calculate the total investor payout for varying percentage rates. The formula has been entered for you in cell H2. It references the original investor payout percentage in cell B9. The substitute values have been entered for you in cells G3:G7. Font Size
On the Data tab, in the Forecast group, click the What-If Analysis button, and click Data Table... Enter B9 in the Column input cell box. Click OK.
There is an error in cell D6. Accept Excel's suggestion for fixing the error. Font Size
Click cell D6. Click the Smart Tag. Click Copy Formula from Above.
Apply the Colorful- Accent Colors color set to the diagram. Font Size
Click the Change Colors button. Select Colorful- Accent Colors. It is the first option in the Colorful section of the gallery.
Apply the Style 5 Quick Style to the chart. Font Size
Click the Chart Tools Design tab. In the Chart Styles group, click Style 5. It is the fifth option in the Quick Styles gallery.
Apply the Colorful Palette 4 color scheme (the fourth option in the Colorful section) to the chart. Font Size
Click the Chart Tools Design tab. In the Chart Styles group, click the Change Colors button. Click Colorful Palette 4, it is the fourth option in the Colorful section.
Change the chart type to the first 100 % stacked column option (the third option along the top of the right pane). Font Size
Click the Chart Tools Design tab. In the Type group, click the Change Chart Type button. Click 100% Stacked Column (the third option along the top of the right pane). Click OK.
Create a new file based on the Personal expenses calculator template. Font Size
Click the File tab and select New. In the New section, click the Personal expenses calculator template. Click Create.
Create a new file by searching for a template based on the phrase loan calculator. Use the template titled Loan comparison calculator. Font Size
Click the File tab and select New. In the Search online templates box, type loan calculator and click the Start Searching button. Select the Loan comparison calculator template. Click Create.
Add the Developer tab to the Ribbon. Font Size
Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. At the right side of the Customize the Ribbon window, the Customize the Ribbon list shows all the tab and groups available. Click the Developer check box to add the Developer tab to the Ribbon. Click OK.
Add a custom tab named Company Styles to the Ribbon. Name the custom group Formatting. Do not add an icon for the group. Add the FormatTable macro to the new group. Font Size
Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. Click the New Tab button. Click New Tab (Custom) to select it. Click the Rename button. Type Company Styles. Click OK. Click New Group (Custom) to select it. Click the Rename button. Type Formatting. Click OK. Expand the Choose commands from list and select Macros. Click FormatTable and then click the Add button. Click OK.
Reset the Ribbon back to its original state. Font Size
Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. Click the Reset button and select Reset all Customizations. Click Yes.
Install the Solver add-in. Font Size
Click the File tab. Click Options. Click Add-Ins. Click Go¿ Click the Solver Add-in check box. Click OK.
Open the Compatibility Checker to check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes. Font Size
Click the File tab. Click the Check for Issues button, and then click Check Compatibility. Click OK.
Open the Trust Center and change the setting to disable all macros and display a warning in the Message Bar so you can choose to enable them on a case-by-case basis. Font Size
Click the File tab. Click the Options button to open the Excel Options dialog. Click Trust Center, and then click the Trust Center Settings¿ button. Click Macro Settings. Click the Disable all macros with notification radio button and click OK. Click OK to close the Excel Options dialog.
Add the folder named MacroEnabled Workbooks as a new trusted location. Font Size
Click the File tab. Click the Options button to open the Excel Options dialog. Click Trust Center, and then click the Trust Center Settings¿ button. Click Trusted Locations. Click the Add new location¿ button. Click the Browse¿ button. Click the MacroEnabled Workbooks folder. Click OK. Click OK. Click OK to close the Trust Center. Click OK again to close the Excel Options dialog.
Encrypt the workbook with this password: abc123 Font Size
Click the File tab. Click the Protect Workbook button. Click Encrypt with Password. Type abc123. Click OK. Type abc123 in the Confirm Password dialog. Click OK.
Switch to Page Break Preview view. Font Size
Click the Page Break Preview button at the lower right corner of the status bar, to the left of the zoom slider.
Switch to Page Layout view. Font Size
Click the Page Layout button at the lower right corner of the status bar, to the left of the zoom slider.
Add an element to the center section of the header to display the current date. Font Size
Click the Page Layout button on the status bar. Click in the center section of the header area above the worksheet grid. On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click the Current Date button.
Use the Quick Analysis tool to calculate totals for the selected cells. Insert the totals in the empty row below the cell range.
Click the Quick Analysis tool button and then click the Totals tab. Click the first Sum button. Click OK.
Apply the Data Bars conditional formatting option to the selected cells from the Quick Analysis tool. Font Size
Click the Quick Analysis tool button at the lower-right corner of the selection. On the Formatting tab, click the Data Bars button.
Spell check the Cash Flow worksheet and correct the misspelling of the word Accounting. Close the checker when the check is complete. Font Size
Click the Review tab and click the Spelling button. Click the Change button. Click OK.
Apply the Cartoon SmartArt style to the diagram. It is the third option in the 3-D section of the gallery. Font Size
Click the SmartArt Tools Design tab. Click the More button to display the gallery. Select the third option in the 3-D section of the gallery.
Return the zoom level back to 100%. Font Size
Click the View tab and click the 100% button.
Arrange the Excel windows in a row next to each other. Font Size
Click the View tab. In the Window group, click the Arrange All button. Select the Vertical option. Click OK.
Change the zoom level for the worksheet to be 110%. Font Size
Click the Zoom In button on the zoom slider.
Change the zoom level to 90%. Font Size
Click the Zoom Out button on the zoom slider.
Complete the two-variable data table in cells H3:K8. The formula has been entered for you in cell H3. The substitute values in cells I3:K3 reference the total expenses value in cell E5, and the substitute values in cells H4:H8 reference the original investor percentage in cell B9. Font Size
On the Data tab, in the Forecast group, click the What-If Analysis button, and click Data Table... Enter E5 in the Row input cell box. Enter B9 in the Column input cell box. Click OK.
Use Goal Seek to find the value for cell B2 that will result in a value of $200 for cell B6. Accept the solution. Font Size
On the Data tab, in the Forecast group, click the What-If Analysis button, and click Goal Seek... Enter 200 in the To value box. Enter B2 in the By changing cell box. Click OK. Click OK.
Add a from control button to run the FormatTable macro. The button should be placed at approximately cells G1:I3. Font Size
On the Developer tab, in the Controls group, click the Insert Controls button. Click the Command Button (Form Control) button. Click and drag over across cells G1:I3 to draw the button. Click FormatTable. Click OK.
Open the Watch Window and create a new watch for the selected cells. Font Size
On the Formulas tab, in the Formula Auditing group, click the Watch Window button. Click Add Watch. Click Add.
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument. Font Size
On the Formulas tab, in the Function Library group, click the Financial button, and click PMT. Enter B5/12 in the Rate argument box. Enter B6 in the Nper argument box. Enter -B4 in the Pv argument box. Click OK.
In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell references wherever possible. The annual interest rate is stored in cell B5, the number of payments in cell B4, and the monthly payment amount in cell B3. Remember to divide the annual interest rate by 12 and use a negative value for the Pmt argument. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click FV. Enter B5/12 in the Rate argument box. Enter B4 in the Nper argument box. Enter -B3 in the Pmt argument box. Click OK.
Switch to the footer. Font Size
On the Header & Footer Design Tools tab, in the Navigation group, click the Go to Footer button.
Add a footer that displays the page number in the center section. Font Size
On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click the Page Number button.
Apply bold and italic formatting to the selected cell. Font Size
On the Home tab in the Font group, click the Bold button and the Italic button.
Remove just the bold formatting from the selected cell. Font Size
On the Home tab in the Font group, click the Bold button.
Center the content in the selected cells horizontally. Font Size
On the Home tab, in the Alignment group, click the Center button.
Change the angle of rotation for the selected cells to 45 degrees (counterclockwise). Font Size
On the Home tab, in the Alignment group, click the Orientation button, and select Angle Counterclockwise.
Enter a formula in cell E4 to calculate the average value of cells B4:D4. Font Size
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.
Use a single command to clear everything (content and formatting) from the selected cell. Font Size
On the Home tab, in the Editing group, click the Clear button. Select Clear All.
Clear only the content from the selected cell (leaving the formatting). Font Size
On the Home tab, in the Editing group, click the Clear button. Select Clear Contents.
Clear only the formatting from the selected cells (leaving the content). Font Size
On the Home tab, in the Editing group, click the Clear button. Select Clear Formats.
Add a Blue-Gray, Text 2 (in the top row of theme colors, the fourth color from the left) top border to the selected cells. Use the thickest single line style available. Font Size
On the Home tab, in the Font group, click the Borders button arrow, and select More Borders... In the Format Cells dialog, Border tab, click the thickest line available in the Style section. Expand the Color palette and select Blue-Gray, Text 2. In the Border section, click the button representing a top border.
Remove the borders from the selected cells with a single command. Font Size
On the Home tab, in the Font group, click the Borders button arrow, and select No Border.
Apply the Top and Double Bottom Border to the selected cells with a single command. Font Size
On the Home tab, in the Font group, click the Borders button arrow, and select Top and Double Bottom Border.
Remove the fill color from the selected cells. Font Size
On the Home tab, in the Font group, click the Fill Color button arrow to display the color palette. Click No Fill.
Add a Blue, Accent 5 fill color to the selected cells. Font Size
On the Home tab, in the Font group, click the Fill Color button arrow to display the color palette. Click the Blue, Accent 5 color, the second color from the right in the first row of theme colors.
Change the font color for the selected cells to the Blue, Accent 5 color (the second color from the right in the top row of theme colors). Font Size
On the Home tab, in the Font group, click the Font Color arrow, and select Blue, Accent 5.
Change font size for the selected cells to 16. Font Size
On the Home tab, in the Font group, click the Font Size arrow, and select 16.
Change the font for the selected cell to Verdana. Font Size
On the Home tab, in the Font group, click the Font arrow, and select Verdana.
Apply the Double Accounting underline format to the selected cells. Font Size
On the Home tab, in the Font group, click the Font dialog launcher to open the Format Cells dialog. On the Font tab, in the Underline section, expand the Underline list and select Double Accounting. Click OK.
Apply the Single Accounting underline format to the selected cells. Font Size
On the Home tab, in the Font group, click the Font dialog launcher to open the Format Cells dialog. On the Font tab, in the Underline section, expand the Underline list and select Single Accounting. Click OK.
Apply the Accounting Number Format to the selected cells. Font Size
On the Home tab, in the Number group, click the Accounting Number Format button.
Modify the number format so no decimal places are visible after the decimal point. Font Size
On the Home tab, in the Number group, click the Decrease Decimal button twice.
Change the number format for the selected cells to the Currency number format. Font Size
On the Home tab, in the Number group, click the Number Format arrow and select Currency.
Apply the Calculation cell style to the selected cells. Font Size
On the Home tab, in the Styles group, click the Cell Styles button. Click the Calculation style.
Apply the Title cell style to the selected cell. Font Size
On the Home tab, in the Styles group, click the Cell Styles button. Click the Title style.
Apply conditional formatting to the selected cells using the Blue-White-Red color scale. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Color Scales and click Blue-White-Red color scale.
Apply conditional formatting to the selected cells using the blue solid fill data bar. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Data Bars, in the Solid Fill section, click Blue Data Bar.
Apply conditional formatting to the selected cells using the Three Symbols (Circled) icon set (the first icon set in the Indicators section). Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Icon Sets and click the first option under Indicators.
Apply conditional formatting to the selected cells so cells with a value greater than 10 are formatted using a yellow fill with dark yellow text. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Highlight Cells Rules, and click Greater Than... Type 10 in the Format cells that are GREATER THAN box. Expand the format selector box and select yellow fill with dark yellow text. Click OK.
Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Top/Bottom Rules, and click Above Average. Click the drop-down arrow and select Yellow Fill with Dark Yellow Text. Click OK.
Convert the cell range to a table using table style Table Style Light 10 (the third style in the second row under the Light styles). The table should include headers. Font Size
On the Home tab, in the Styles group, click the Format as Table button. Click Table Style Light 10. It is the third style in the second row under the Light styles. Verify that the My table has headers checkbox is checked. Click OK.
Change the worksheet orientation to landscape. Font Size
On the Page Layout tab, in the Page Setup group, click the Orientation button. Click Landscape.
Apply the Retrospect theme to the workbook. Font Size
On the Page Layout tab, in the Themes group, click the Themes button. Click the Retrospect theme.
Reset the picture to its original formatting. Font Size
On the Picture Tools Format tab, in the Adjust group, click the Reset Picture button.
Apply the Compound Frame, Black Quick Style. Font Size
On the Picture Tools Format tab, in the Picture Styles group, click the More button to expand the Picture Quick Styles gallery. Click Compound, Black Frame.
From the Ribbon, change the picture border color Blue, Accent 1 (the fifth color in the top row of theme colors). Font Size
On the Picture Tools Format tab, in the Picture Styles group, click the Picture Border button. Click the fifth color in the top row of theme colors.
Apply the Preset 10 picture effect to the picture (the second effect in the third row of preset effects). Font Size
On the Picture Tools Format tab, in the Picture Styles group, click the Picture Effects button. Point to Preset, and select the second effect in the third row of preset effects.
Add a calculated field to this PivotTable to calculate the average sales per visit by state (=Sales/Visits). Name the new field Average. Font Size
On the PivotTable Tools Analyze tab, click the Calculations button, and then click Fields, Items, & Sets. Select Calculated Field¿ In the Insert Calculated Field dialog, type Average in the Name box. In the Formula box, delete the 0 and type Sales/Visits. Do not delete the equals sign. Click OK.
Insert a PivotChart using the first bar chart type. Font Size
On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button. Click Bar in the list of chart types at the left side of the Insert Chart dialog. Click OK.
Apply the dark blue Pivot Style Dark 2 Quick Style (the second option in the first row under the Dark styles) to the PivotTable. Font Size
On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the Quick Styles gallery, and select Pivot Style Dark 2. It is the second option in the first row under the Dark styles.
Change the table design so the Total row is showing. Font Size
On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box.
Add a total row to this table and display the maximum value for the Total Spent column. Font Size
On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. In the Total row, click in the Total Spent column. Click the arrow and select Max.
Apply the Table Style Medium 3 Quick Style (the third style in the first row under Medium styles) to this table. Font Size
On the Table Tools Design tab, in the Table Styles group, click the Quick Styles button or click the More button to expand the Table Styles gallery. Click Table Style Medium 3. It is the third style in the first row under Medium styles.
Convert the table to a normal range. Font Size
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.
Change the button text to: Format Table Font Size
Right-click the button and select Edit Text. Edit the button text to Format Table and then click anywhere outside the button.
Change the color of the sheet tab for the PO Q4 worksheet to Blue, Accent 1, Lighter 60% (the fifth option in the third row of theme colors). Font Size
Right-click the worksheet tab and point to Tab Color. Click Blue, Accent 1, Lighter 60% (the fifth color in the third row of theme colors).
