Main Information:
To acquire the knowledge, skills and abilities to be able to use MS Excel functions to perform calculations and data processing. To be able to use MS Excel features to perform data processing and analysis on large tables: sorting, filtering and grouping. Generate summary reports using raster tables and raster diagrams. Apply conditional formatting for easier display of data. Perform protection of work grammars and worksheets.
Results:
- Apply arithmetic, logical functions to process data.
- Perform text and date processing functions.
- Perform data searches using the lookup and reference functions.
- Apply Data Aggregation Functions (Summary Functions)
- Perform data sorting and filtering.
- Perform data grouping and calculation of intermediate results (Subtotals);
- Perform summary reports using the Pivot Table and Pivot Chart features
- Perform Conditional Formatting of data
- Perform data entry constraints, creation of custom Dropdown lists.
- Ensure data protection using MS Excel capabilities.
- Working with Excel data: lists. Sorting. Filtering. Calculating intermediate results.
- Lists (Excel Table) and their rules.
- Simple Sort and Custom Sort;
- Data filtering (AutoFilter). Advanced Filters;
- Duplicate search and deletion;
- Data grouping and calculation of intermediate results (Subtotals);
- Working with pivot tables (PivotTable) and pivot charts (PivotChart)
- Creating a Pivot Table structure;
- Using the Sum, Min, Max, Average and Count functions to aggregate data in Pivot Tables;
- Creating a PivotChart.
- Conditional Formatting
- Working with Excel functions
- Function categories and searching for functions
- Arithmetic functions.
- Logical functions (IF, AND, OR, IFERROR, etc.)
- Word processing functions (LOWER, UPPER, PROPER, LEFT, MID, RIGHT, TRIM, CONCATENATE, etc.)
- Date functions (DATE, YEAR, MONTH, DAY, WEEKDAY, etc.)
- Lookup and reference functions (VLOOKUP, HLOOKUP, etc.)
- Data aggregation functions (summary functions) SUM, AVERAGE, COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, etc.)
- Data validation
- Creation of Dropdown lists;
- Data Validation and data entry restrictions;
- Data Protection
- Protection of individual worksheet cells;
- Formula hiding;
- Worksheet protection;
- Workbook structure protection;
- Excel file protection.
