Course Objective

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

The training is for professional development purposes, and the certificate obtained does not replace the MYK (Vocational Qualifications Authority) certificate in courses that require the MYK certification.

After this training, participants will be able to:

• Use keyboard shortcuts to speed up tasks.
• Use simple functions for basic calculations and apply them to solve more complex problems.
• Create visual warning systems by using tools such as conditional formatting and validation with formulas.
• Ensure data consistency and organization in tables with problematic or incorrect data by using text functions.
• Obtain data that changes based on conditions using logical functions.
• Compare data between tables and transfer information across tables using Lookup & Reference functions.
• Use the Excel tools under the Data menu for detailed querying and reporting on tables pulled from the system or customized by them.
• Use different encryption methods to ensure the confidentiality and security of the documents they prepare.

Training Content

• Module 1: Data Entry, Editing, and Practical Tips
  • Data entry, correction, and formatting in cells.
  • Working with rows and columns.
  • Entering and editing basic arithmetic formulas.
  • Quick data entry for dates, times, and serial data.
  • Adding and editing logos and/or images.
  • Keyboard shortcuts and practical tips.
  • Making selections in cells.
  • Copying - special paste operations.
  • Creating and working with tables.
  • Searching, finding, and replacing.
• Module 2: Working with Documents
  • Saving documents in Excel 97 – 2003 format.
  • Excel file formats: .xls, .xlsx, etc.
  • Saving as PDF, XPS format.
  • Saving password-protected documents.
• Module 3: Working with Worksheets
  • Adding, hiding, deleting, renaming worksheets, and setting tab colors.
  • Copying and moving worksheets.
  • Simultaneous operations in worksheets.
  • Using Freeze Panes, Split, and Arrange options.
  • Adding background images to sheets.
• Module 4: Working with Functions
  • Working with the function wizard.
  • Quick formula entry.
  • Referencing rows/columns in formulas.
  • Updating data without formulas (basic arithmetic).
  • Using functions like SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF, TODAY, YEAR, etc.
  • Basic text functions (UPPER, LOWER, PROPER).
  • Special search (finding empty, formula-filled, and special content cells).
  • Formula duplication, copying, and special paste.
  • Stepping through formulas step-by-step.
  • Working with formulas across documents and sheets.
  • Using fixed references in formulas.
  • Adjusting special views and navigating quickly within documents.
• Module 5: Formatting
  • Cell formatting.
  • Adding comments.
  • Formatting numerical and date values, adding custom units (kg, liters, pcs, etc.).
  • Setting alignment, borders, and fill.
  • Formatting as tables.
  • Custom or adaptive cell formatting.
• Module 6: Working with Data
  • Working with formulas across documents and sheets.
  • Using fixed references in formulas.
  • Naming ranges and using them in formulas.
  • Conditional formatting.
  • Data bars, color scales, icon sets.
  • Applying preset rules for formatting.
  • Creating custom rules.
  • Using formulas for conditional formatting.
  • Limiting data entry.
• Module 7: Querying and Reporting Tools
  • Sorting, sorting by format.
  • Simple sorting.
  • Simultaneous sorting across multiple columns.
  • Creating custom sorting lists.
  • Filtering operations.
  • Filtering by text, numeric, and date values.
  • Filtering by color and icons.
  • Using the advanced filtering tool.
  • Creating subtotals in tables.
  • Working with 3D formulas.
  • Splitting data into columns.
• Module 8: Creating Charts
  • Creating pie charts and moving charts.
  • Reviewing and modifying chart elements.
  • Formatting chart properties and applying styles.
• Module 9: Page Setup and Printing
  • Setting page setup, orientation.
  • Adding margins, headers, and footers.
  • Selecting/removing print areas, print preview.
  • Printing options, printing headers.
• Module 10: Logical Functions
  • Performing operations based on conditions (IF, AND, OR).
  • Writing formulas using nested functions.
  • Using multiple IF functions nested together.
  • Working with SUMIF, COUNTIF functions.
  • Working with SUMIFS, COUNTIFS functions.
  • Using DSUM, DCOUNT, DAVERAGE functions.
  • Addressing common formula errors and handling errors.
  • Writing functions without using the wizard.
  • Using information functions with logical functions (ISERROR, ISERR, ISBLANK, ISNA, ISTEXT, ISNUMBER, etc.).
• Module 11: Working with Lookup and Reference Functions
  • Searching for data within numerical or date ranges (VLOOKUP, HLOOKUP).
  • Comparing tables and data (VLOOKUP).
  • Using the IFERROR error checking function.
• Module 12: Fixing Problematic Data and Creating New Data with Text Functions
  • Converting text to upper, lower, or proper case (UPPER, LOWER, PROPER).
  • Extracting information from any text (LEN, LEFT, RIGHT, MID, FIND, SEARCH, etc.).
  • Splitting text into columns.
  • Creating new text from existing data (CONCATENATE, TEXT, REPLACE, REPT, etc.).
  • Fixing problematic data (TRIM, VALUE, etc.).
• Module 13: Working with Date Functions
  • Creating new dates using day, month, year information (DAY, MONTH, YEAR, DATE, EDATE, WEEKDAY, etc.).
  • Finding the number of days and business days between two dates (NETWORKDAYS, WORKDAY, etc.).
• Module 14: Creating Pivot Tables
  • Creating Pivot Tables.
  • Creating percentage values in Pivot Tables.
  • Automatically creating separate Pivot Tables for each data point.
  • Creating calculated fields in Pivot Tables.
  • Grouping data.
  • Creating Pivot Charts.

The training is open to corporate collaboration, and individual applications are not accepted. Training content can be re-planned according to the corporate participant profile and needs. After mutual discussions, the scope of the training and the method (In-person, Online) will be determined, and the relevant processes will be completed. Suitable days and times for your organization's participants and our instructors, as well as the training location, will be determined once an agreement is reached.