Final Exam: Business Analyst

WHAT YOU WILL LEARN

• 

  Apply workbook-level protection options such as open in read-only mode, encrypting with the password, add a digital signature, and marking as final
  

  visualize foreign-key relationships use entity-relationship diagrams in powerpivot
  

  insert charts and customize chart elements such as the axes, title, data labels, data callouts, and gridlines
  

  use scatter plots to examine relationships between two numeric data column
  

  correctly utilize absolute and relative cell references in worksheet formulae
  

  bin data by category, by bin width, or by specifying a fixed number of bins
  

  configuring autosave and autorecover behavior and the default save location
  

  use the row() function to alter highlighting based on cell location
  

  create new formatting rules and varying both conditions governed by the rule as well as precise formatting aspects
  

  enable the powerful solver add-in
  

  implement common data-cleaning operations such as the elimination of duplicates via the data menu
  

  enable analysis toolpak
  

  utilize the what-if analysis feature in excel
  

  specify data validation constraints to prevent typos and reduce the need for data cleaning
  

  use conditional aggregate functions such as countif() and countifs() to perform aggregate operations based on specific conditions
  

  use the index() and match() functions to perform two-dimensional lookups
  

  generate random numbers drawn from various distributions use analysis toolpak
  

  utilize the powerful and popular vlookup() and hlookup() functions for one-dimensional lookups that join ranges of data based on a common column
  

  introduce the use of worksheet formulas and the semantics of copy-pasting formulas with relative cell references
  

  enable analysis toolpak and use it to compute correlation as well as covariance matrices
  

  construct at-a-glance visualizations use sparklines to construct lightweight charts without axes or units contained within individual cells
  

  open comma- and tab-delimited text files use the data import menu item
  

  use the chart design item from the toolbar ribbon to quickly change chart layout and color
  

  edit external links use the data menu
  

  use the conditional formatting menu item to highlight cells based on rules
  

  understand the role of powerpivot in making excel a powerful tool for analytical big data processing
  

  apply custom formatting to first and last points
  

  perform conditional analysis use the worksheet if() function
  

  use the protect sheet feature from the review menu to control how a sheet can be edited
  

  introduce excel data tables and understand the #spill! error
• 

  launch microsoft excel, explore available templates, and customize the quick-access toolbar
  

  create links to external workbooks use the paste link option from the paste menu
  

  combine multiple visualizations into a single chart using the combo chart type
  

  customize headers and footers
  

  efficiently and quickly perform simple statistical analysis of two-dimensional data
  

  implement hypothesis testing
  

  customize the quick-access toolbar
  

  recognize different charts
  

  combine the print and page layout menu features to precisely control how workbooks are printed
  

  implement anova (analysis of variance) use analysis toolpak to analyze variances
  

  customize that ribbon by creating a new ribbon tab
  

  export workbooks to formats such as pdf/xps
  

  use the filter feature from the data menu to focus on specific subsets of data
  

  contrast comments and notes in excel
  

  recognize the pitfalls of create such pivot tables without accurately capturing relationships between relations
  

  recognize the role of powerpivot in analytical big data processing
  

  use excel's powerful functionality for filling in blank/missing values use the find & select menu option and the go to special feature
  

  and create pivot tables by attempting to link multiple such tables
  

  perform univariate eda (exploratory data analysis) use box-and-whisker plots
  

  insert and delete rows and columns
  

  implement anova (analysis of variance) use analysis toolpak to analyze variances within groups and between groups
  

  understand the pitfalls of create such pivot tables without accurately capturing relationships between relations
  

  use keyboard shortcuts to insert line breaks inside a cell
  

  identify the capabilities and limitations of the auto outline feature
  

  copy-paste data and use the format cells right-click menu option to customize
  

  auto-detect relationships between different relations use the pivottable feature
  

  use built-in templates and saving a commonly-used workbook as an excel template (.xltx) file
  

  analyze data use the subtotal feature from the data menu
  

  use the logical or function to construct a boolean predicate that can be used to highlight weekends in a range of dates
  

  use goal seek for solving