Untitled Quiz

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.