The purpose of the training is to broaden the knowledge on the advanced use of Excel application, which will not only maximize work ergonomics with Excel spreadsheet but also solve the problems encountered in professional work each day.
During the training we will discuss the advanced spreadsheet functions, which will allow to analyze data skillfully using the Ms Excel tools.
With the advanced Excel training, you will know how to:
- accelerate the work with Excel effectively using advanced tools and functions,
- make use of keyboard shortcuts as well as Excel tricks and gimmicks,
- compare tables, transfer data quickly from one table to another with VLOOKUP, INDEX or MATCH functions,
- create a data set, summary and analysis using PIVOTTABLE,
- make nested functions so as to get the best calculation results possible,
- use conditional formatting to select entire worksheet rows automatically, highlight errors with a chosen color and create automatic data formatting,
- create simple macrocommands, and thereby automatize arduously performed actions,
- present data using advanced charts,
- combine data from different sheets or documents quickly and effectively using Excel tools and functions,
- rapidly and effectively correct errors occurring while importing data from programs, such as SAP.
During this particular training, a great attention is paid to the calculations with logical functions (IF), nested in other functions. A number of text, date and counting functions (SUMIF, COUNTIF, SUMIFS), which are presented on practical exercises, give the participants a wide range of possibilities of using the Excel spreadsheet competently.
A large part of the training is also devoted to implement VLOOKUP, INDEX and MATCH functions, which allow to compare tables, transfer results quickly from one table to another and data consolidation.
The training also includes the rules of applying array functions and the issue of distinguishing them from the usual formulas.
The training participants also gain practical skills how to create and use simple macrocommands in order to accelerate the performance of repeated actions.
The outstanding features of this training are also “tricks and gimmicks”, presented by the lecturers during the course, as well as numerous keyboard shortcuts, the participants will be familiarized with and taught to use them while working with the Excel spreadsheet.
The training is aimed at those who have already used Excel sheets at work as well as for those who would like to broaden and expand their knowledge on the application.
All the exercises, performed by the participants, have a practical dimension. They have been prepared with a view to improve the work of our trainees.
Training materials
The participants are given training materials in the form of:
- A script including the description of tools and functions discussed during the training,
- A training script which contains the description of the methods and “step by step” solutions to every practical exercise done at the time of training,
- Practical exercises on Excel.
Each exercise, performed during the course, is sent to the participants by e-mail. All of these tasks also contain the solutions shown and done within the training duration.
After-training care
Every participant will also be provided after-training care. Within 12 months after completing the course, the trainees may contact the lecturer in order to get consultative advice. The contact with the lecturer is available via e-mail or by phone. The training participants are also able to make an appointment with the trainer in the headquarters of our company.
We will adjust the training to your needs!
Those, who have already submitted for the training, can send Excel files (regarding their work for instance), they would like to carry out in the form of exercises, to the e-mail of the lecturer or our company before the beginning of the course. In such a way, the trainer will be able to prepare practical exercises, which are based on the specific needs of our trainees. Those exercises will show different methods of solving these particular issues with the use of Excel tools and functions.
Diagnose your knowledge level of Excel
In order to help in the choice of Excel training, the most appropriate for your needs, we have implemented a test that will point out which level of Excel you should sign in for.
The test not only shows and checks the level of Excel, but also indicates thoroughly which skills (functions, tools) in Excel are necessary for you to perform rapidly specific tasks which you are likely to encounter in everyday work.
Each person, who will fill the test out, will receive in response the full analysis of their skills together with the diagnosis of those skills which should be gained so as to perform the work in Excel effectively.
In order to be given the test, contact us through e-mail address: szkolenia@akkom.net.pl or via phone.
Organizing data in Excel sheets
- Organizing data sets using Table object
- Working with data converted into a Table
Changing data range, copying formulas in table automatically, creating functions in Tables
- Creating automatic data series
Creating serial numbers, dates excluding weekends, making custom data series in order to automatize entering repeated data
Excel tools necessary in everyday work
- Methods of working on several sheets simultaneously so as to accelerate the performance of repeated actions
- Quick column and row grouping
- Using Excel keyboard shortcuts essential for navigating data sets
- Advanced methods of data selection with the „Go to” option
Methods of automatic selection and completion of empty cells in data sets using the „Go to” tool
- The advanced use of “Paste Special” tool
Using the tool for data transposition, arithmetic operations (add, multiply) and to change text into number – an option necessary to work with data sets retrieved from other programs
- Tracking dependents and precedents (formula tracking)
Pivot Tables and Pivot Charts
- Creating PivotTables
Preparing data, explaining errors regarding data, making Pivot Table, explaining elements and the method of using a Pivot Table
- Working with PivotTable
Practical examples of using Pivot Table to create reports and analyses (exercises on sales, accounting, financial and production data). The use of Pivot Table elements, navigating Pivot Table
- Adding data to row field
- Adding data to column field
- Adding data to data field (values)
- Adding data to filter field
- Modification of summary functions: sum, average, minimum, maximum
- Data grouping in pivot table
Creating calculations from data groups based on dates (for instance creating sums of months, years and quarters from dates), calculations by numerical data (intervals), text data grouping
- Data filtering in Pivot Table
Methods and means of filtering data collected in pivot table
- Displaying results as percentages
Presenting PivotTable results as percentages: column percentage and row percentage
- Pivot Table formatting
Changing table appearance, modifying pivot table layout (outline, tabular, compact), showing subtotals, repeating row labels (elements) of pivot table
- PivotCharts
- Automatic transition from pivot table to pivot chart
- Automatic creation of pivot charts
- Modifying pivot chart functions
- Pivot chart formatting
Calculations - Working with Excel formulas and functions
- Absolute references (cell blocking) and the advanced use of them
- Logical functions: IF, OR, AND
Nesting logical functions in order to define different conditions for calculating data
- Counting functions: SUMIF, COUNTIF, SUMIFS, COUNTIFS
Data summing with the use of one or several criteria, summing numerical data by text criteria, for instance by the first letter of a word or a part of the text. Counting data amount by several criteria
- Comparison and data mining functions in tables: VLOOKUP, HLOOKUP, INDEX and MATCH
Nesting comparison functions in other functions. Comparing two tables. The methods of inserting data among tables using comparison functions. Comparing tables by rows and columns using INDEX and MATCH functions. Combining VLOOKUP function with IFERROR and ISERROR functions in order to eliminate errors
- Date and time functions: TODAY, DATEDIFF, DATE, DAY, MONTH, YEAR, WEEKNUM, WORKDAY, TIME
Using nested date functions to subtract months and years. Converting a date stored as text into a date in order to make calculations. Calculating the number of the year’s week, calculating workdays among dates, calculating the exact amount of months and years among dates
- Text functions: LEFT, RIGHT, FIND, MID, TRIM, TEXT, SUBSTITUTE, UPPER, PROPER, LEN
Concatenating and splitting texts, rapid change of the case of text, removing unwanted white spaces (unnecessary spaces), the change of text into number, change of characters in text, separating texts in order to use them in other functions
- Using ISERROR, IFERROR, ISTEXT, ISNUMBER functions to detect and eliminate errors
- Creating and working with functions nested in other functions
Calculations with the use of array functions
Managing worksheets and workbooks
- Using the “Consolidation” tool to create links based on data stored in different files and workbooks
Using two possibilities of consolidation, either by column headers or by the rows from the first table column, retrieving data from different Excel files using SUM and AVERAGE and COUNT, data consolidation which includes displaying of data stored in other sheets and workbooks
- Creating calculations from data stored in other sheets and workbooks
- Combining sheets with the use of functions
Consolidation of data from different sheets and files using VLOOKUP function
- Working with sheet names
- Cell and range naming
Range naming, change of the range of name visibility, using name manager, deletion and correction of names
- Working with functions which use names (reference by name)
The use of names in calculations with SUM, AVERAGE, VLOOKUP functions. Calculating data retrieved from other sheets by name, the use of F3 keyboard shortcut to activate a name
- Concatenating workbooks and working with different workbooks
Data sorting and filtering
- Advanced sorting
Sorting data by several criteria, sorting data from left to right (by rows)
- Advanced filtering
Complex filtering, methods of using advanced filters, creating unique values using advanced filtering - only in 2003 Excel version
Data validation
- Limiting the errors of data insertion with Data Validation
Methods of reduction of data entering with the use of number, date and text length options, practical examples of applying DATA VALIDATION in order to limit the amount of errors in data entered into worksheet
- Fields of drop-down lists
- Creating drop-down lists
Drop-down lists based on data stored in one sheet, drop-down lists as a method for reduction of errors regarding data inserting, entering input messages and error alerts
- Data from other sheets into lists
Methods of retrieving data from other sheets to drop-down lists by name references, connections between drop-down lists and functions
Creating advanced charts
- Chart with many data series
Proper data preparation for charts with the use of functions, modification of data range in chart data source
- Creating charts with two axes
Assigning the second axis to chart, data change on axis
- Advanced chart formatting
Inserting graphic elements in chart, for instance the company’s logo, putting our own graphic objects as chart points, modifying the range of numbers on axes
- Creating data trends on chart
Information exchange between different programs and MS Office applications
- Importing data from external sources:
- Retrieving data from text files and other applications
Options of importing data, the use and modifications of the TEXT TO COLUMNS tool options in order to import data properly and without errors
- Applying Excel functions and tools so as to correct and eliminate errors occurring during data import
Using text functions for data „cleaning” and repair, methods of converting numbers stored as text into numbers in order to enable calculations on data, cleaning up data from redundant characters, converting dots to commas, eliminating unwanted spaces in data, removing empty rows from data quickly
- Adding a document from Office package program to Excel worksheet
Inserting Word documents or others into Excel sheets and the other way round
- Creating hyperlinks
Opening documents directly from Excel spreadsheet, creating navigation through document with the use of hyperlinks
- Mail merge
Excel sheet data used to create mail merge in Word program
The use of conditional formatting tool
Automatic change of the sheet cell, row and column appearance due to certain conditions
- Conditional formatting with already defined rules
Formatting cells with specified text, date formatting, number formatting
- Conditional formats in a graphic form
Creating histograms (data bars), adding graphic icons to data, creating color scale
- Conditional formatting of dates with the use of functions
Selecting rows with dates which are Saturdays and Sundays as well as holidays, selecting terms and dates, which have already passed
- Automatic change of colors of cells and entire rows using conditional formatting and simple formulas
Document protection
- Blocking the access to file using passwords
- Sheet and workbook protection
Entire sheet blocking from possible editing, blocking the sheet contents except for entries to certain cells, protecting formulas from viewing and accidental modification, hiding sheets
Printing Excel sheets
- Advanced functions of printing and preparation for printing
- Printing the header of data set on every page
Simple macrocommands
Automation of often repeated actions in Excel
- Creating macros with the use of recorder
Assigning a keyboard shortcut to macro, creating macros which format cells and others, saving macros in one workbook as well as within the whole Excel program
- Modification and deletion of macros
Activating created macros, deleting and running macros, viewing the macro code – simple edition
- Assigning macros to buttons
Adding macros to graphic elements (buttons, images, shapes)
Zapisz się na interesujący Cię termin szkolenia
Place:
Price
Duration:
Date:
970,00 PLN net
1193,10 PLN gross
970,00 PLN net
1193,10 PLN gross
970,00 PLN net
1193,10 PLN gross
The training has the form of practical exercises intended as computer workshops combined with the lecture part.
Each participant has their own computer station at their disposal, at which they perform exercises according to the lecturer’s instructions.
In the initial part of the course, the trainer interviews the participants so as to identify their preferences and training needs. In such a way, the lecturer is able to develop exercises, which both have a practical dimension for the trainees and solve their problems regarding the use of the application and its particular features.
The training is conducted on different versions of Excel, there are 2003, 2007 or 2010 editions to choose from.
The actual time, needed to acquire the practical skills in using Excel within 2 training days, involves 18 teaching hours. During the course, there are 15-minute breaks planned for lunch and coffee refreshments.
The trainings in the advanced use of Excel spreadsheet are organized in small training groups (up to 10 people).
AkKom Computer Academy also offers the course in the Excel spreadsheet for advanced users in a dedicated form – “made to measure” the needs and expectations of our Clients.
In this case, the training will be adjusted to you both in the substantive and technical aspect. We adapt the plan of the training and the exercises precisely to the expectations and preferences of our Client. The lecturer gets acquainted with the scope of your work in Excel and compiles practical exercises based on the files used in the Client’s company. With such a procedure, the participants can turn the knowledge, gained during the course, to practical account at once while working with Excel sheets in your company.
Trainings in this form are organized for groups of at least 4 people.
In order to match the technical aspects to your preferences, the courses can be conducted in our classrooms, in the Client’s headquarters or in any location within Poland or beyond its borders according to the Client’s convenience.
There are mobile training facilities (laptops) available, which enables the adjustment to the Client’s requirements considering where the training will be held.
