Chapter 4 Excel Exam- Getting and Managing Data

Enter a formula in cell D2 to display the length of the text in cell C2.

Click the Formulas tab. In the Function Library group, click the Text button, and select LEN. In the Text argument box, enter C2 Click OK.

Use a custom text filter to show only rows where the expense category (Category column) contains the word server.

Data Ribbon- Filter-B1-Click Sort Drop Down-Text filter contains-nside the Custom AutoFilter dialog, you typed server in the second filter input, clicked the OK button.

Modify the layout of this PivotTable to separate fields into columns.

Design Ribbon- Report Layout-Show In Tubular Form- Report layout-Repeat All Item labels-Subtotals-Show all subtotals at the bottom of each group

Export this worksheet as a tab-delimited text file.

File-Export-Change File Type-Text Tab Delimited-Save As-Save

Enter a formula in cell B2 to find the month number by extracting the first two letters of the sales date in cell A2. Use cell references where appropriate.

Formulas Tab- Text- Select Left- Function Arguments dialog, you type a2 in the Text input, typed 2 in the Num_chars input, and pressed the Enter key.

Refresh the PivotTable data.

In the Analyze Ribbon Tab in the Data Ribbon Group, click the Refresh button.

Import data from the StudentGPA text file into a table in a new worksheet.

In the Data Ribbon Tab in the Get & Transform Data Ribbon Group, you clicked the Get Data button. In the Get Data menu in the From File menu, you clicked the From Text/CSV menu item. You clicked StudentGPA. Inside the Import Datadialog, you clicked the Import button. Inside the StudentGPA.txt dialog, youclicked the Load button.

Enable filtering.

In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Filter button.

Sort the data so cells with the red down arrow icon in the Cost Level column appear on top.

In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Filter button. You clicked cell D1, clicked the Quick Sort dropdown. In the Table Filtermenu in the Sort by Color menu, you selected the red arrow option.

Show subtotals at the bottom of each group.

In the Design Ribbon Tab in the Layout Ribbon Group, you clicked the Subtotalsbutton. In the Subtotals menu, you clicked the Subtotals Bottom menu item.

Apply the Light Orange, Pivot Style Medium 3 Quick Style (the third option in the first row under the Medium styles) to the PivotTable.

In the Design Ribbon Tab in the PivotTable Styles Ribbon Group, you expanded the PivotTable Styles Gallery. In the Design Ribbon Tab in the PivotTable StylesRibbon Group in the PivotTable Styles menu, you selected the Light Orange, Pivot Style Medium 3 option.

Convert the table to a normal range.

In the Design Ribbon Tab in the Tools Ribbon Group, click the Convert to Range button. Inside the Microsoft Excel dialog, click the Yes button.

Add slicers to filter the data in this table by Date.

In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Insert Slicerbutton. Inside the Insert Slicer dialog, you checked the Date check box, clicked the OK button.

Filter the Category column so only rows with Coffee are shown.

In the Home Ribbon Tab in the Editing Ribbon Group, you clicked the Sort & Filterbutton. In the Sort & Filter menu, you clicked the Filter menu item. You clicked cell B1, clicked the Quick Sort dropdown. In the Table Filter menu, you clicked the (Select All) checkbox, clicked the Coffee checkbox, and clicked the OK button.

Apply conditional formatting to the selected cells using the Three Traffic Lights (Unrimmed) icon set (the first icon set in the Shapes section). 1/1

In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu in the Icon Sets menu, youselected the 3 Traffic Lights (Unrimmed) option.

Insert a recommended PivotTable, using the Sum of Bonus by Employee option.

In the Insert Ribbon Tab in the Tables Ribbon Group, you clicked the Recommended PivotTables button. Inside the Recommended PivotTables dialog from the PivotTables list, you clicked the Sum of Bonus by Employee item. Inside the Recommended PivotTables dialog, you clicked the OK button.

Use a slicer to filter the data in this table to show only rows where the Category value is Coffee or Meals.

Table Design Ribbon- Insert Slicer (Tools)- Click Coffee and Meals-select ok-Filter the table that appears by selecting the multi select option in the upper right hand corner- then select all other options besides Coffee or Meals and press ok.

Rename the table from Table1 to ExpensesTable.

Table Design Tab- Left side (Table Name)- Type "ExpensesTable" in the input, pressed the Enter key.

Sort this data by values in the Cost column so the most expensive items are listed first.

You click cell C1, click the Quick Sort dropdown. In the Table Filter menu, you click the Sort Z to A menu item.

Modify this PivotTable to repeat the item labels in every row.

You clicked cell A3, clicked the Quick Sort dropdown, and clicked the Table Filtermenu. In the Design Ribbon Tab in the Layout Ribbon Group, you clicked the Report Layout button. In the Report Layout menu, you clicked the Report Layout Repeat menu item.

Clear the filter from the Category column.

You clicked cell B1, clicked the Quick Sort dropdown. In the Table Filter menu, you clicked the Clear menu item.

Display the details for just the Office Expenses group.

You clicked cell B23, clicked the row 23 + symbol. (Left side looks for a + symbol)

Add a total row to this table and display the average for the Cost column instead of the sum of all the values.

You clicked cell C1. In the Design Ribbon Tab in the Table Style Options Ribbon Group, you checked the Total Row check box. You clicked cell C21, clicked the Total Row arrow. In the Total Row menu, you clicked the Average menu item.

Type Justin Fine in cell D2 and press Enter. Begin typing Julie Smith, and when Flash Fill suggests a pattern for the remaining cells, accept it.

You clicked cell D2, typed Justin Fine in cell D2, pressed Enter, typed Julie in cell D3, clicked cell D3, clicked the D3 Cell Input, and pressed Enter.

The total row for each expense category uses a SUM function to total the costs. Create an automatic outline from the rows in this data range.

n the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Group button arrow. In the Group menu, you clicked the Auto Outline menu item.