Business Analyst to Data Analyst Literacy (Beginner Level)

Topics covered

• construct at-a-glance visualizations using sparklines to build lightweight charts without axes or units contained within individual cells, and use named ranges to control data displayed in a sparkline visualization
• control cell-level protection and understand the semantics of the locked and hidden checkboxes, apply workbook-level protection options, such as open in read-only mode, use password encryption, add a digital signature, mark as final, and outline the semantics and limitations of opening in read-only mode
• create new formatting rules and vary both conditions governed by the rule as well as precise formatting aspects, highlight outliers that lie a certain number of standard deviations from the mean, highlight unique and duplicate values, use the Find & Select menu item to locate all cells governed by conditional formatting rules, and clear conditional formatting rules  
• enable Analysis ToolPak and use it to compute correlation and covariance matrices, interpret the results, and recognize the link between covariance and variance
• implement common data-cleaning operations, such as eliminating duplicates via the Data menu, use CLEAN() to remove unwanted whitespace characters, correctly interpret numbers stored as text, and use CONVERT() for an extensive range of unit conversion operations  
• import data in relational (tabular) form, create pivot tables by attempting to link multiple tables, and specify issues that can arise if pivot tables are created without accurately capturing relationships
• insert and delete rows and columns, alter cell fill color and line borders, and merge cells via the Home menu
• load data from a variety of file types into Tableau in order to perform an analysis
• perform bivariate EDA (exploratory data analysis), use scatter plots to examine relationships between two numeric data columns and the Chart Design menu to create linear, exponential, and other types of trendlines, and identify positively and negatively trending data
• perform conditional analysis using the IF() function, and combine logical conditions using the AND() and OR() functions
• perform operations in Power BI to remove columns and top rows, split columns by delimiter, filter rows, and delete a query
• perform operations in Power BI using the UI and the Advanced Editor to sort data, change data types, rename columns, and use uppercase text
• prepare your data for visualization by applying transformations, which are more meaningful for data analysis
• recognize Flash Fill as a way to fill in missing data intelligently, import a file as an Excel data table, export workbooks to formats such as PDF/XPS, and identify the potential for data loss when a workbook is saved as a CSV file
• recognize the #SPILL! error and data tables features, such as named columns, calculated columns, and headers, use data tables to filter, sort, and remove duplicate data, and convert to and from traditional Excel ranges
• specify data validation constraints, configure bounds on acceptable values, create user-friendly error messages, and validate data based on decimal, whole number, text length, and date/time values
• use conditional aggregate functions, such as COUNTIF() and COUNTIFS(), to perform aggregate operations based on specific conditions and recognize the semantics of predicates created from strings for use within these functions
• use histograms to summarize the statistical properties of data, bin data by category, bin width, or fixed number of bins, perform automatic binning, and set overflow and underflow bins
• Use the What-If Analysis feature in Excel, create scenarios based on specific cell values, assign names to these scenarios, and toggle between them, summarize a range of outcomes across scenarios by performing What-If Analysis, and interpret the returned results  
• utilize VLOOKUP() and HLOOKUP() functions for one-dimensional lookups that join data ranges based on a common column, then use relative cell references for the lookup value and absolute cell references for the lookup range