CISM Practice Exam 1 Flashcards

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?

On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Goal Seek. Enter 650000 in the To value box. Enter D12 in the By changing cell box. Click OK twice. Signup and view all the answers

What is the method to modify the number format so no decimal places are visible?

On the Home tab, in the Number group, click the Decrease Decimal button twice. Signup and view all the answers

What steps do you need to take to change the outline width of the selected 2016 data point?

On the Chart Tools Format tab, Shape Styles group, click the Shape Outline button, point to Weight, and select 2 1/4 pt. Signup and view all the answers

How do you import data from the text file StudentGPA into the current worksheet?

Import External Data - Text - StudentGPA - Okay - Okay. Signup and view all the answers

What formula would you enter in cell E15 to find the lowest line item cost this month?

=MIN(E2:E14) Signup and view all the answers

What action do you take to switch to the view showing all worksheet elements as they will print?

Page Layout mini button by Zoom Slider. Signup and view all the answers

How do you modify the status bar to display the results of the MIN function for selected cells?

Right Click Status Bar (at bottom). Signup and view all the answers

What is the procedure to insert cut cells from D1:D6 at the current worksheet location (cell C1)?

Home - Insert - Insert Cut Cells. Signup and view all the answers

How do you use Format Painter to apply formatting from cell D1 to cell E1?

Click the Paintbrush Icon on Home Tab (located by Paste/Clipboard) and click on the desired cell. Signup and view all the answers

What steps do you follow to show the tracer arrows for a selected cell?

Formulas Tab - Formula Auditing - Trace Dependents. Signup and view all the answers

How do you unlock selected cells to allow editing when the worksheet is protected?

Home Tab - Cells - Format (arrow) - Lock Cell. Signup and view all the answers

What action do you take to add Column Sparklines to cells F2:F11?

Insert Tab - Sparklines Group - Column. 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?

Data Tab - Sort and Filter - Advanced. Signup and view all the answers

What procedure do you follow to ensure the first row is always visible when scrolling down?

View Tab - Freeze Panes. Signup and view all the answers

How do you remove duplicate rows where data in all columns is identical?

Table Tools Design Tab - Tools Group - Remove Duplicates - OK - OK. Signup and view all the answers

What steps do you take to add markers for all data points in a Sparkline group?

Sparkline Tools Design Tab - Show Group - Markers Checkbox. Signup and view all the answers

How do you add the Bonus field to a PivotTable?

Scroll down in Fields - Check Bonus checkbox. 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?

Use the name Abbreviation for the lookup table; enter 2 in the VLOOKUP col_index_num field; enter A4 in the lookup_value field; use false for exact match. 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?

Data Tab - Data Tools Group - Flash Fill. Signup and view all the answers

How do you hide formulas in a worksheet to display the values instead?

Formulas Tab - Formula Auditing Group - Show Formulas. 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?

Data Tab - Sort - Category - Add Level - Date. Signup and view all the answers

How do you add a report filter for the Employee field in a PivotTable?

Drag Employees to Filter - Go to Employees Cell - Select drop arrow - Dr. Patella. 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?

Home Tab - Style - Conditional Formatting - New Rule - Format only values above/below avg - Edit Rule Description: (drop-down) equal or below - Format: Bold - OK - OK. Signup and view all the answers

What steps are necessary to manually create groups of rows that can be expanded or collapsed?

On the Data tab, in the Outline group, click the Group button. In the Group dialog, Rows is selected by default. Click OK. 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.