Questions and Answers
What is the first step to format the selected data as a table with Table Style Medium 20?
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?
Signup and view all the answers
What steps do you take to add a slicer based on the Location column?
Signup and view all the answers
How do you convert the selected table back to a range?
Signup and view all the answers
What is the method to create a Conditional Formatting rule for cells Less Than 4000?
Signup and view all the answers
How can you add a Top 10% highlight rule to the selected cells?
Signup and view all the answers
What steps do you take to sort the data for the selected cell from A to Z?
Signup and view all the answers
How do you filter the data on Rentals in the Category column?
Signup and view all the answers
What steps do you take to add subtotals at each change in Location?
Signup and view all the answers
What is the process to import the ImportText-04.txt to the workbook?
Signup and view all the answers
How do you add the email address [email protected] in the current cell?
Signup and view all the answers
What steps are needed to export the file as a tab delimited text file?
Signup and view all the answers
What is the procedure to add a new Recommended PivotTable showing the sum of Amount by Location?
Signup and view all the answers
How do you modify the Visits field to use the AVERAGE function instead of the SUM function?
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?
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 ____.
Signup and view all the answers
How do you modify the Order Cost field to use the COUNT function?
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!