Course Outline
Excel course for consistent users who are interested in expanding their knowledge of how to work with the application’s complex and intelligent functions and formulas. The objective of the course is to teach proper and efficient Excel work practices and offer tools that can help manage databases in faster and more useful ways.
Upcoming Meetings
There are no upcoming meetings for this course. Contact us to schedule this course, which will be customized specifically for your organization.
info@hackerupro.comModules
Cell Design
- Cell definition: number, text, date, currency, thousands separator, percentage
- Design customization (data display, such as date format, can be changed)
- Conversion of dates displayed as text (with periods) via the search and replace method
- Text in columns: Dividing a column into two or more columns (for example, one column for a private name, one for a family name), or merging several columns into a single column
- Mastery of data management, including cell definition
- Data conversion tools for new templates
- Flash Files – automatic data population
Conditional Criteria Based Design
- Data display using colors & symbols in accordance with criteria defined by formulas
Data Verification to Prevent Duplications and Errors
- As examples, a box should not be opened, a table should not be designed and columns should not be deleted without data verification
Sorting and Filtering Table Data in a Fast and Efficient Manner
- Customized and advanced data sorting
- Filtering data into a new table without modifying the original table
Calculations and Formulas
- Simple formulas - Sigma
- Cell fixing
Functions
- Logical – IF, nested IF, IFERROR, combining AND, OR and IF
- Calculation – subtotal, sumif, sumifs, countif, averageif
- Information – vlookup, hookup, index, match (alternative for vlookup), combinations of those functions, how to add an IFNA function for cases in which N/A error messages are generated
- Date – month, day, weekday, workday
- Text – extraction, find and replace (to remove ### for example), merging texts
Two Methods of Data Cross-Checking
- Vlookup
- Conditional design
Two Methods of Table Data Merging
- Consolidate
- Power queries
Adding and Editing Sparklines
Reports
- Pivot tables
- GetPivotData function
Dashboards – Excel’s Control Center
- Creating interactive & efficient dashboards and indicators, with tables, data, functions & charts that will be updated automatically
External Internet-Based Data & Printing
Prerequisites
- None