Perform basic spreadsheet tasks including navigation, data entry, and using formulas. Employ data quality techniques in Excel
-
to import
-
to clean data • Removing Duplicated, Irrelevant or Inaccurate Data o check spelling, o remove empty rows, o remove Duplicated rows, o Find & Replace to correct misspelling • Dealing with Inconsistencies in Data o PROPER, UPPER, LOWER, o Change date formatting, o Use Find & Replace to trim whitespace • More Excel Features for Cleaning Data o Use the Flash Fill feature to clean data o LEFT, RIGHT, LEN, and SEARCH functions Analyze data in spreadsheets by using filtering, sorting, look-up functions, and pivot tables.
-
Filtering and Sorting Data (Data tab/Sort, Data tab/Filter)
-
Useful Functions for Data Analysis (IF, IFS, COUNTIF, SUMIFS)
-
look-up functions (VLOOKUP, HLOOKUP, XLOOKUP)
-
Using Pivot Tables a. Create a pivot table, and perform a simple calculation in a pivot table b. Pivot Table Features (Recommended Charts, Filters, Slicers, Timelines)