Excel 4 Flashcards

Questions and Answers

What is the first step to format the selected data as a table with Table Style Medium 20?

Select Table Style Light 15 from the gallery.

Click the Format as Table button. (correct)

Add a Total Row to the table.

Remove duplicates in the table.

How do you change the style of the table to Table Style Light 15?

Click the Table Tools Design tab, then click the More button and select Table Style Light 15.

What should you do to add a Total Row and a Count calculation to the Category column?

Click the Table Tools Design tab and select the Total Row check box, then click cell D31 and select Count.

How can you remove duplicates in a selected table based on Names and Amount columns?

Click the Table Tools Design tab, then click the Remove Duplicates button and ensure only the Name and Amount check boxes are selected. Signup and view all the answers

What steps do you take to add a slicer based on the Location column?

Click the Insert Slicer button, select Location, and then select Slicer Style Light 5. Signup and view all the answers

How do you convert the selected table back to a range?

Click the Table Tools Design tab and then click the Convert to Range button. Signup and view all the answers

What is the method to create a Conditional Formatting rule for cells Less Than 4000?

Click the Conditional Formatting button, hover over Highlight Cells Rules, and select Less Than. Signup and view all the answers

How can you add a Top 10% highlight rule to the selected cells?

Click the Quick Analysis button and choose Top 10%. Signup and view all the answers

What steps do you take to sort the data for the selected cell from A to Z?

Click the Data tab and then click the A to Z button. Signup and view all the answers

How do you filter the data on Rentals in the Category column?

Navigate to the Data tab, click the Filter button, select Rentals from the Category cell. Signup and view all the answers

What steps do you take to add subtotals at each change in Location?

Click the Subtotal button, choose Location, and select Transactions and Amount for subtotal. Signup and view all the answers

What is the process to import the ImportText-04.txt to the workbook?

Click the From Text/CSV button, locate ImportText-04.txt, and click OK to load it. Signup and view all the answers

How do you add the email address [email protected] in the current cell?

Type <a href="mailto:[email protected]">[email protected]</a> and press Enter, then use Flash Fill for the next address. Signup and view all the answers

What steps are needed to export the file as a tab delimited text file?

Click the File tab, select Export, choose Text (Tab delimited), and click Save. Signup and view all the answers

What is the procedure to add a new Recommended PivotTable showing the sum of Amount by Location?

Click the Recommended PivotTables button and select the Sum of Amount by Location preview. Signup and view all the answers

How do you modify the Visits field to use the AVERAGE function instead of the SUM function?

On the PivotTable Tools Analyze tab, click the Field Settings button and select Average. Signup and view all the answers

What steps do you take to add a total row and display the maximum value for the Total Spent column?

On the Table Tools Design tab, click the Total Row checkbox. Signup and view all the answers

Use a slicer to filter the data in this table to show only rows where the State value is ____.

MD or DE Signup and view all the answers

How do you modify the Order Cost field to use the COUNT function?

On the PivotTable Tools Analyze tab, click the Field Settings button and select Count. Signup and view all the answers

Study Notes

Formatting Tables

Format selected data as a Table using Table Style Medium 20 by selecting the Format as Table button and ensuring headers are included.
Change table style to Table Style Light 15 by using the Table Tools Design tab and selecting it from the options available.

Adding Data Features

Add a Total Row to a table to display calculations (Count) in specific columns such as Category by selecting the Total Row checkbox.
Remove duplicate entries based on selected columns (Names and Amount) using the Remove Duplicates button under Table Tools Design.

Slicers and Data Conversion

Insert a Slicer for filtering data by the Location column, applying Slicer Style Light 5 for visual formatting.
Convert a table back to a range by selecting Convert to Range from the Table Tools Design tab.

Conditional Formatting

Create a Conditional Formatting rule that highlights cells with values less than 4000 with a Light Red Fill and Dark Red Text.
Apply a Top 10% highlight rule to selected cells through Quick Analysis.

Data Sorting and Filtering

Sort data from A to Z by accessing the Data tab and clicking the A to Z button.
Filter data specifically to show "Rentals" in the Category column by using the Filter feature in the Data tab.

Subtotals and Data Import

Add subtotals for Transactions and Amount columns when changes occur in Location using the Subtotal feature.
Import external text files (e.g., ImportText-04.txt) directly into the workbook at a designated location using the From Text/CSV option.

Flash Fill and File Export

Utilize Flash Fill to automatically complete a series of email addresses after starting to type the first one.
Export the workbook as a tab-delimited text file while confirming file location and accepting potential feature loss warnings.

PivotTables and Charts

Generate a Recommended PivotTable showing the sum of Amount by Location through the Insert tab options.
Adjust the Rows area of the PivotTable by dragging the Classification field to the bottom.

PivotTable Style Customize

Set the PivotTable style to Pivot Style Light 7, including banded rows and columns, and remove grand totals in the PivotTable layout.
Insert a Clustered Column PivotChart based on a selected PivotTable for graphical data representation.

Field Modifications

Add the Visits field to a PivotTable by checking it in the PivotTable Fields pane.
Modify the Visits field calculation to use the AVERAGE function instead of SUM through the Field Settings option.
Display the maximum value in a total row for the Total Spent column within a table.

Advanced Data Filtering

Use a slicer to filter table data, focusing only on rows where the State value is either MD or DE.
Alter the Order Cost field in the PivotTable to utilize the COUNT function for accurate data representation.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge of Excel table formatting with these flashcards. Each card provides a key command or feature within Excel, paired with a definition to help reinforce your learning. Perfect for students looking to enhance their Excel skills!