The training is intended for professional development purposes and the certificate received does not replace the MYK authorization certificate required for trainings where MYK authorization is mandatory.

Training Objective

The purpose of this course is to provide participants with knowledge of the advanced features of Microsoft Office Excel. Participants will learn how to perfectly organize and manage all their data, enrich it with advanced functions, accelerate querying and reporting processes, and create visually rich and content-rich documents.

After this training, participants will be able to;

• Use keyboard shortcuts to speed up operations,
• Use functions used for simple calculations in nested ways to solve more complex problems,
• Create visual alert systems by combining conditional formatting and validation tools with formulas,
• Ensure consistency and organization of data in spreadsheets with many errors or problematic data using text functions,
• Obtain conditional data using logical functions,
• Compare data between spreadsheets and transfer information using Lookup & Reference functions,
• Perform detailed queries and reporting using Excel tools in the Data menu on spreadsheets from the system or manually prepared,
• Use various encryption methods to ensure confidentiality and security of prepared documents.

Training Content

• Module 1: Data Entry, Editing and Practical Tips
  • Entering, correcting and formatting data in cells
  • Working with rows and columns
  • Basic arithmetic formulas entry and editing
  • Quick date, time, and series data entry
  • Adding and editing logos and/or images
  • Keyboard shortcuts and practical tips
  • Selecting cells
  • Copy - paste special operations
  • Creating tables and working with tables
  • Find and replace
• Module 2: Working with Documents
  • Saving documents in Excel 97-2003 format
  • Excel formats: .xls, .xlsx etc.
  • Saving in PDF or XPS format
  • Saving password-protected documents
• Module 3: Worksheets
  • Adding, hiding, deleting, naming worksheets and tab colors
  • Copying and moving worksheets
  • Simultaneous operations on sheets
  • Using Freeze Panes, Split, and Arrange options
  • Adding background images to sheets
• Module 4: Working with Functions
  • Using function wizard
  • Quick formula entry
  • Referencing rows/columns in formulas
  • Updating data without formula (basic arithmetic)
  • Using SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF, TODAY, YEAR etc.
  • Basic text functions (UPPER, LOWER, PROPER)
  • Special search (find empty, formula, and custom cells)
  • Copying, replicating, and paste special for formulas
  • Step-by-step formula calculation
  • Using formulas across documents and sheets
  • Using absolute references in formulas
  • Custom views and quick navigation within documents
• Module 5: Formatting
  • Formatting cells
  • Adding comments
  • Formatting numeric and date values, adding custom units (kg, lt, pcs etc.)
  • Alignment, borders, and fills
  • Formatting as tables
  • Custom or user-defined cell formatting
• Module 6: Working with Data
  • Formulas across sheets and documents
  • Absolute references in formulas
  • Naming ranges and using them in formulas
  • Conditional formatting
  • Data bars, color scales, icon sets
  • Applying ready-made rules
  • Creating custom rules
  • Using formulas in conditional formatting
  • Restricting data entry
• Module 7: Querying and Reporting Tools
  • Sorting operations, sorting by format
  • Simple sorting
  • Multi-column sorting
  • Creating custom sort lists
  • Filtering operations
  • Filtering by text, numbers, and dates
  • Filtering by colors and icons
  • Advanced filter usage
  • Subtotals with tables
  • Working with 3D formulas
  • Splitting data into columns
• Module 8: Creating Charts
  • Creating pie charts and moving charts
  • Examining and editing chart elements
  • Formatting chart properties and styles
• Module 9: Page Setup and Printing
  • Page setup and orientation
  • Margins, header, and footer
  • Select/remove print area, print preview
  • Print options, printing titles
• Module 10: Logical Functions
  • Conditional operations (IF, AND, OR)
  • Writing nested formulas
  • Using multiple IF functions in nesting
  • Working with SUMIF, COUNTIF
  • Working with SUMIFS, COUNTIFS
  • Working with DSUM, DCOUNT, DAVERAGE
  • Errors in formulas and solutions
  • Writing functions without the wizard
  • Using information functions together with logical functions (ISERROR, ISERR, ISBLANK, ISNA, ISTEXT, ISNUMBER etc.)
• Module 11: Lookup & Reference Functions
  • Finding information in numeric or date ranges (VLOOKUP, HLOOKUP)
  • Comparing tables and data (VLOOKUP)
  • IFERROR error check function
• Module 12: Fixing Problematic Data and Creating New Data with Text Functions
  • Convert text to upper, lower, proper case (UPPER, LOWER, PROPER)
  • Extract information from text (LEN, LEFT, RIGHT, MID, FIND, SEARCH etc.)
  • Splitting text into columns
  • Creating new text from existing info (CONCATENATE, TEXT, REPLACE, REPT etc.)
  • Fixing problematic data (TRIM, VALUE etc.)
• Module 13: Working with Date Functions
  • Creating new dates from day, month, year (DAY, MONTH, YEAR, DATE, EDATE, WEEKDAY etc.)
  • Calculating number of days or workdays between two dates (NETWORKDAYS, WORKDAY etc.)
• Module 14: Creating Pivot Tables
  • Creating pivot tables
  • Creating percentages in pivot tables
  • Creating automatic pivot tables for each dataset
  • Creating calculated field(s)
  • Grouping data
  • Creating pivot charts

The training is open to institutional collaboration (institutions/companies as legal entities) and individual applications are not accepted. Training content can be reorganized according to institutional participant profiles and needs. After mutual discussions, the training scope and method (Face-to-Face, Online) are determined and the relevant processes are completed. Upon agreement, suitable days and times for participants and instructors, as well as the training location, are set.