Untitled Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What command do you use to import files in Excel?

Data > Get External Data (correct)

File > Open

Home > Import

View > Import Files

How do you add a new comment in Excel?

Review > New Comment

Where do you find the option to wrap text in Excel?

Home > Wrap Text

Which menu path do you use to center across selection?

Home > Alignment > Alignment Settings Signup and view all the answers

How can you insert a column in between existing columns?

Use CTRL when selecting the columns. Signup and view all the answers

What do you click to manually adjust column and row height?

Home > Format > Column Width Signup and view all the answers

What are the steps to export a worksheet to PDF?

File > Export > PDF Signup and view all the answers

How do you inspect a worksheet for personal information?

File > Check for Issues > Inspect Signup and view all the answers

How do you inspect a worksheet for accessibility or compatibility?

File > Check for Issues > Check Compatibility or Accessibility Signup and view all the answers

What steps do you take to adjust page breaks?

View > Page Break Preview Signup and view all the answers

How can you specify print titles in Excel?

Page Layout > Print Titles Signup and view all the answers

Where do you change page margins and scaling?

File > Print Signup and view all the answers

How do you outline data in Excel?

Data > Group > Auto Outline Signup and view all the answers

What path do you use for formatting cell borders?

Home > Font > Square Signup and view all the answers

Where can you find the format painter tool?

Select Cells > Clipboard > Format Painter Signup and view all the answers

How do you insert an image in a worksheet?

Format > Arrange and Align > Snap to Grid Signup and view all the answers

What do you click to sum using destination cells?

Click where you want it to go > Auto Sum Signup and view all the answers

How do you sum using source cells?

Select the cells you want it to use > Auto Sum Signup and view all the answers

What do you do to apply a table style to a cell range?

Home > Format as Table Signup and view all the answers

How do you turn a table into a cell range?

Design > Convert to Range Signup and view all the answers

Where do you find the command to remove duplicates?

Select Table > Remove Duplicates Signup and view all the answers

What steps do you take to sort data?

Select Column > Sort Signup and view all the answers

How do you insert subtotals?

Make sure it's not a table and it's properly sorted in the column you want subtotaled. Signup and view all the answers

What function do you use to count the number of cells with numbers?

COUNT(cell #, cell #...) Signup and view all the answers

How do you count the number of cells with any content?

COUNTA(cell #, cell #...) Signup and view all the answers

What does the average function do?

AVERAGE(cell #, cell #...) Signup and view all the answers

What do the min and max functions do?

MIN(cell #, cell #...) or MAX(cell #, cell #...) Signup and view all the answers

How do you sum in a total table row and filter results?

Design > Total Row Signup and view all the answers

How do you apply conditional formatting for highlighting?

Home > Conditional Formatting > Highlight Signup and view all the answers

What steps do you take for applying icon sets in conditional formatting?

Conditional Formatting > Icon Sets Signup and view all the answers

What is relative cell referencing?

Cell reference in formula that changes automatically when formula is moved. Signup and view all the answers

What is absolute cell referencing?

Cell reference in formula that won't change when moved to different location. Signup and view all the answers

How do you describe mixed cell referencing?

Either the row or the column is an absolute reference. Signup and view all the answers

What does the INT function do?

Rounds number down to nearest integer. Signup and view all the answers

What is the purpose of the ABS function?

Returns a number without the - sign. Signup and view all the answers

What are some examples of statistical functions in Excel?

Median: MEDIAN(cell #), Mode: MODE.SINGLE(), mode.MULT. Signup and view all the answers

What function do you use for the current date and time?

TODAY() and NOW() Signup and view all the answers

What syntax is used for DATEDIF?

=DATEDIF(cell #, cell #, 'Y or M or D') Signup and view all the answers

What does the FIND function do?

Looks for a given character. Signup and view all the answers

How do the LEFT and RIGHT functions work?

LEFT(cell #, # of char wanted) or RIGHT(cell #, # of char wanted) Signup and view all the answers

What does the UPPER function do?

Converts text to uppercase. Signup and view all the answers

What is the CONCATENATE function used for?

Joins text strings together. Signup and view all the answers

What does VLOOKUP do?

Looks up the matching value in a table. Signup and view all the answers

How do you use the PMT function?

Finds periodic payment for loans: =-PMT(rate per month, # of payments, amount borrowed) Signup and view all the answers

What does the IF function do?

=IF(test, return if true, return if false) Signup and view all the answers

How do you move a chart to a new sheet?

Chart Tools > Design > Move Chart Signup and view all the answers

What steps are taken to create different charts?

Select the table > Box in BR > Select wanted chart. Signup and view all the answers

How can you create a combination chart?

Select the table > Box in BR > More > All Charts > Combo. Signup and view all the answers

How do you change series names in a chart?

Design > Select Data > Edit Series. Signup and view all the answers

How do you insert an object into a chart?

Click on the chart first before insertion. Signup and view all the answers

What should you do to explode a pie chart?

Click on single slice, then drag out. Signup and view all the answers

How do you change a chart to 3D?

Change chart type. Signup and view all the answers

What steps do you take to insert sparklines?

Home > Sparklines. Signup and view all the answers

How can you insert data bars in a worksheet?

Select reference cells > Conditional Formatting > Data Bars. Signup and view all the answers

What is the purpose of the COUNTIF function?

Count only the values that meet a specific criteria: COUNTIF(range, 'criteria'). Signup and view all the answers

What does the AVERAGEIF function do?

Average only the values that meet a specific criteria. Signup and view all the answers

What is the function of the SUMIF?

Sum only the values that meet a specific criteria. Signup and view all the answers

What is needed for writing a nested IF?

Signup and view all the answers

How do you create a backup copy of data?

Copy sheet within the page, rename backup. Signup and view all the answers

What are advanced filters in a data table?

Copy column headings, place above the table, put criteria under column. Signup and view all the answers

How do you insert slicers in a data table?

Insert > Slicer > Select column to filter. Signup and view all the answers

How can you summarize a dataset?

Make a little table with average, subtotal and average if. Signup and view all the answers

What is the initial step to create a pivot table?

Column > Design > Summarize with PT. Signup and view all the answers

What is involved in setting up a pivot table?

Choose fields to add, switch to rows and columns. Signup and view all the answers

How do you modify pivot tables and work with totals?

Analyze, Active Field, Field Settings. Signup and view all the answers

What do you do to insert slicers in pivot tables?

Analyze > Insert Slicer. Signup and view all the answers

How can you add a pivot chart?

Analyze > Pivot Chart. Signup and view all the answers

How do you trace precedents and dependents?

Formula > Trace Precedents. Signup and view all the answers

How do you use a watch window?

Formulas > Watch Window > Select cells to watch. Signup and view all the answers

How do you utilize data validation?

Data > Data Validation. Signup and view all the answers

What is needed for a codification scheme for generating numbers?

IF(cell # > 0, TEXT(cell #, 'YYYMMDD', '')&IF(cell#>0, TEXT(cell#, 'HHMM'), '')&IF(cell#>0, VLOOKUP(cell#, table, column to look at, FALSE)) Signup and view all the answers

What are custom validation rules?

Data > Data Validation > Allow: Custom. Signup and view all the answers

How do you add trusted locations?

File > Options > Trust Center > Settings > Trusted Location > Add New Location. Signup and view all the answers

What steps are involved in recording macros?

Add developer tab > Use relative references > Record macro > Give it a shortcut key. Signup and view all the answers

How do you create a macro button?

Developer > Insert > Button > Select cells to place. Signup and view all the answers

How do you modify macros using VBA?

Developer > Macros > Select Macro > Edit. Signup and view all the answers

What steps do you take to hide or unhide a worksheet?

RC Surface Tab > Hide > File > Options > Advanced > Show Sheet Tabs. Signup and view all the answers

How can you unlock cells or protect a sheet?

Home > Format > Lock Cell > Protect: Home > Format > Protect Sheet. Signup and view all the answers

How do you hide formulas in Excel?

Format > Format Cells > Protection > Hidden. Signup and view all the answers

What steps do you take to encrypt a workbook?

File > Protect Workbook > Encrypt (with password). Signup and view all the answers

What do you do to mark a workbook as final?

File > Protect Workbook > Mark as Final. Signup and view all the answers

Study Notes

Excel Certification Exam Study Notes

Importing Files: Use Data > Get External Data feature to import files, specify delimiters and column formats.
Adding Comments: Access comments through Review > New Comment to annotate cells.
Text Wrapping: Enable text wrapping in cells via Home > Wrap Text for better readability.
Centering Across Selection: To center text across a selection, go to Home > Alignment, access alignment settings, and choose Horizontal Alignment.
Inserting Columns: Hold down CTRL while selecting columns to insert a new column between existing ones.
Adjusting Column and Row Heights: Manually adjust the height by navigating to Home > Format > Column Width.
Exporting to PDF: Export a worksheet by selecting File > Export > PDF.
Inspecting Workbooks: Check for personal information in a worksheet using File > Check for Issues > Inspect, then remove document properties.
Checking Accessibility and Compatibility: Use File > Check for Issues to assess a worksheet's compatibility and accessibility.
Adjusting Page Breaks: Activate View > Page Break Preview to adjust page breaks by dragging them around.
Specifying Print Titles: In Page Layout, set print titles by selecting which rows and columns should repeat on every printed page.
Changing Page Margins and Scaling: Adjust margins and scaling options through File > Print.
Outlining Data: Create automatic outlines by using Data > Group with existing subtotals.
Formatting Cell Borders: Apply borders to cells via Home > Font, selecting the square border button.
Using Format Painter: Use the Format Painter from Clipboard in Home to copy and apply formatting; press ESC to stop.
Inserting Images: Position images easily by using Format, Arrange, Align, and enabling Snap to Grid.
Summing Cells: Utilize the Auto Sum feature by clicking on the destination cell or selecting the source cells.
Table Styling: Apply a table style to a range by selecting Home > Format as Table.
Converting Tables to Cell Ranges: Change a table back to a range via Design > Convert to Range.
Removing Duplicates: Eliminate duplicates from a table by selecting it and using the Remove Duplicates feature.
Sorting Data: Sort data in a selected column by using the Sort option; custom lists can be created for specific sorting.
Inserting Subtotals: Ensure the data is sorted; use Subtotal feature while making sure it’s not in table format.
Counting with Functions:
- Use COUNT(cell#...) to count cells with numbers.
- Use COUNTA(cell#...) to count non-empty cells.
Calculating Averages: Implement the AVERAGE(cell#...) function to compute average values in a range.
Min/Max Functions: Use MIN(cell#...) or MAX(cell#...) to find the smallest or largest value in a range.
Adding a Total Row with Filtering: Enable a total row in a table and use filters to adjust displayed results.
Conditional Formatting Options: Highlight data through Home > Conditional Formatting or use Icon Sets.
Cell Referencing: Understand referencing types:
- Relative: changes when the formula moves.
- Absolute: remains constant (e.g., $A$4).
- Mixed: either row or column is fixed.
Numerical Functions:
- INT(cell#) rounds down values.
- ABS(cell#) returns absolute values.
Statistical Functions:
- MEDIAN(cell#...) finds the median.
- MODE.SNGLE identifies the most frequent value, MODE.MULT identifies multiple modes.
Date Functions:
- TODAY() returns the current date.
- NOW() returns current date and time.
- DATEDIF(start, end, unit) calculates the difference between dates.
Text Functions:
- Use FIND("char", cell) to locate characters.
- Use LEFT(cell#, number) or RIGHT(cell#, number) for extracting text.
Text Formatting Functions:
- UPPER(cell#), LOWER(cell#), PROPER(cell#) adjusts text cases accordingly.
Concatenation: Use CONCATENATE(cell#...) to join text strings.
Lookup Functions: VLOOKUP(value, table, column_index, [true/false]) retrieves values based on criteria.
Payment Calculation: Use -PMT(rate, nper, pv) to compute loan payments.
Logical Functions:
- IF(condition, true_value, false_value) makes conditional decisions.
- AND(condition1, condition2) checks multiple true/false conditions.
Chart Management:
- Move charts to new sheets via Chart Tools > Design > Move Chart.
- Create and customize different charts from a selected table.
Data Bar Visualization: Utilize conditional formatting for data bar visuals in selected reference cells.
Criteria-based Functions:
- COUNTIF(range, criteria) counts specific criteria.
- AVERAGEIF(range, criteria) averages specific criteria.
Using Macros: Access macro development from the Developer tab, define actions, and use relative references.
Protecting Data:
- Use Format Cells for cell protection.
- Encrypt workbooks File > Protect Workbook > Encrypt with Password.
Creating Pivot Tables: Choose a data range, then go to Insert > PivotTable. Design the table by adding fields and configuring settings.
Tracking Changes: Use Formula > Trace Precedents to visualize cell relationships.
Managing Data Validation: Set restrictions and input messages using Data > Data Validation.
Adding Trusted Locations: Define secure file paths through File > Options > Trust Center settings.
Custom Validation Rules: Implement rules using specific formulas under Data > Data Validation.
Backup Procedures: Create duplicate sheets and rename them for data security.

Studying That Suits You

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