Questions and Answers
What is the procedure to rotate the pie chart so the Anne Cole data point appears at the left side?
On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button to open the Format Data Point task pane. In the Angle of first slice box, type 200 and press Enter.
How can you convert a table to a normal range?
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.
How do you collapse the entire outline to show just the subtotals?
Click the 2 outline symbol at the left of the column headings.
What steps are involved in using Goal Seek to find the value for cell D12?
Signup and view all the answers
What is the method to modify the number format so no decimal places are visible?
Signup and view all the answers
What steps do you need to take to change the outline width of the selected 2016 data point?
Signup and view all the answers
How do you import data from the text file StudentGPA into the current worksheet?
Signup and view all the answers
What formula would you enter in cell E15 to find the lowest line item cost this month?
Signup and view all the answers
What action do you take to switch to the view showing all worksheet elements as they will print?
Signup and view all the answers
How do you modify the status bar to display the results of the MIN function for selected cells?
Signup and view all the answers
What is the procedure to insert cut cells from D1:D6 at the current worksheet location (cell C1)?
Signup and view all the answers
How do you use Format Painter to apply formatting from cell D1 to cell E1?
Signup and view all the answers
What steps do you follow to show the tracer arrows for a selected cell?
Signup and view all the answers
How do you unlock selected cells to allow editing when the worksheet is protected?
Signup and view all the answers
What action do you take to add Column Sparklines to cells F2:F11?
Signup and view all the answers
How do you filter data to show only rows where the Category value is Meals and the Cost value is greater than 20?
Signup and view all the answers
What procedure do you follow to ensure the first row is always visible when scrolling down?
Signup and view all the answers
How do you remove duplicate rows where data in all columns is identical?
Signup and view all the answers
What steps do you take to add markers for all data points in a Sparkline group?
Signup and view all the answers
How do you add the Bonus field to a PivotTable?
Signup and view all the answers
What formula should you enter in cell B4 using the VLOOKUP function to find the meaning of the medical abbreviation in cell A4?
Signup and view all the answers
What procedure do you follow to use Flash Fill to autofill a column with a pattern from cell F2?
Signup and view all the answers
How do you hide formulas in a worksheet to display the values instead?
Signup and view all the answers
What steps do you take to sort data alphabetically by the Category column and then by the Date column?
Signup and view all the answers
How do you add a report filter for the Employee field in a PivotTable?
Signup and view all the answers
How do you create and apply a new conditional formatting rule to format cells equal to or below the average?
Signup and view all the answers
What steps are necessary to manually create groups of rows that can be expanded or collapsed?
Signup and view all the answers
Study Notes
Data Visualization and Formatting
- Rotate pie chart data points by navigating to Chart Tools Format tab; specify angle in Format Data Point task pane.
- Adjust outline width of chart elements for visual clarity; access Shape Outline settings under Chart Tools Format.
- Add Column Sparklines to visualize data trends quickly within a specified cell range using the Insert tab.
Data Management
- Convert a table to a normal range using the Table Tools Design tab.
- Collapse outlines in worksheets to simplify data presentation; use outline symbols for quick access.
- Remove duplicate rows from datasets using Table Tools Design tab; ensure all columns are considered in the duplicate check.
Data Analysis Techniques
- Use Goal Seek to determine input values needed for specific outcomes; found under the Data Tools group on the Data tab.
- Implement the MIN function in formulas to find minimum values within ranges, enhancing analytical capabilities.
- Utilize Flash Fill to automate data entry by detecting patterns based on initial examples in adjacent cells.
Data Filtering and Sorting
- Filter data in place by applying criteria set up in a specified range, utilizing the Data tab's Sort and Filter options.
- Sort datasets alphabetically and chronologically by adding sorting levels in the Data tab to maintain organization and clarity.
Advanced Excel Functions
- Apply VLOOKUP to search for corresponding values in lookup tables; specify range and exact match requirements.
- Add fields to PivotTables to enhance data analysis and reporting flexibility by checking desired fields in the PivotTable Field List.
Worksheet and Cell Management
- Freeze panes to keep specific rows visible when scrolling through large datasets; found in the View tab.
- Unlock cells to allow user edits in protected worksheets; modify cell formatting through the Home tab.
- Hide formulas to display only results in worksheets, helping to clarify presentation and prevent complex formula viewing.
Formatting and Presentation
- Use Format Painter to copy formatting styles quickly between cells for consistent visual representation.
- Create and apply conditional formatting rules to draw attention to key data points based on specified criteria.
User Interaction Enhancements
- Modify status bar settings to display specific calculation results such as MIN for quick data insights.
- Show tracer arrows for selected cells to visualize relationships in formula dependencies, aiding in formula auditing.
By embedding these functionalities, users can enhance their data management, analysis, and presentation skills through Excel effectively.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Test your knowledge with these flashcards designed for the CISM Practice Exam 1. Each card covers essential concepts and definitions related to data visualization and management. Perfect for students preparing for the CISM certification.