MS Excel - An overview of advanced tools
kod szkolenia: EX03 / ENG DL 3d
An optimal use of MS Excel in many companies becomes the basis for effective operation. A multitude of tools available in the application causes that becoming acquainted with them is ineffective and very time consuming. That’s why thanks to over 20 years of experience in providing training services it was possible to create an advanced training in such accessible a form.
The training is aimed at the people who are already working with MS Excel, and know some of its capabilities, but would like to be acquainted with the range of available tools and functions. Crossectional nature of the course causes that the knowledge acquired at the training may be used on every job position, in every branch. The course not only allows to systematize acquired knowledge on your own, but also introduces to the world of simple and effective solutions accelerating work. Compared to specialist trainings their main goal is being acquainted with the wide range of MS Excel capabilities based on most frequent problems and challenges encountered during everyday work with calculation sheet.
- Being acquainted with a large number of tools (Pivot Table, Text as columns, Data correctness…) and functions (VLOOKUP, SUMIFS, IF…) available in MS Excel together with examples of their practical use
- The participant will be given advice and shortcuts accelerating work
- Knowledge about available tools will allow to perform everyday tasks more effectively
- An ability to design tables correctly, as well as selecting and recognizing data types will allow to avoid problems with using embedded tools
- Ordering knowledge related to an opportunity of hundreds of embedded functions
- Being acquainted with many optimal solutions
The basic knowledge of MS Excel or completed EX02 training
- Training: English
- Materials: English
- Data preparation
- Data object
- Data correctness tool
- Flash Fill
- Data type conversion
- Data import – Power Query
- How to use named cells and scopes in formulas
- Data analysis tools review
- Data table
- Search for result
- Scenarios
- Prognosis sheet
- Advanced filtering
- Functions
- Searching (VLOOKUP, MATCH, OFFSET/INDEX; …)
- Logical functions (AND, OR, IF, IFERROR, …)
- Database functions (DCOUNT, DSUM, DAVERAGE, DMAX, DMIN, …)
- Date and time (DATE, NETWORK DAYS/NETWORK DAY, DAY, MONTH, YEAR, TODAY, …)
- Text (LEN, LEFT, RIGHT, MID, LOWER, UPPER, PROPER, TRIM, VALUE, CONCATENATE, FIND, SEARCH, …)
- Mathematical (AGGREGATE, SUM, SUMPRODUCT, SUMIFS, ROUND, …)
- Financial (PPMT, …)
- Pivot Table
- How to prepare data correctly
- Refreshing data – dynamic scope
- Drill down – moving on to source data
- Practical advice related to the use of various areas
- Grouping, calculating elements, calculating fields
- Automatic report generating for each value from the selected field
- Calculating percentage shares
- The use of fragmentator and timeline to create dashboards
- When to use Power Pivot (introduction to data model)
- Graphs
- Matching a graph to data
- Time course graph (one-cell)
- Presenting results on 2D
- Graph template
- Pivot Chart
- Advanced conditional formatting
- Formatting based on formulas
- The use of formants to navigate variables
- The use of data bars, color scales and icons
- Management of rules
- Introduction to macros
- Security
- Registering and launching
- Assigning macros to buttons on command bars