Excel Tips & Tricks + Advanced Training

The purpose of education 

At the education is informed about advanced Microsoft Office Excel. The participants will learn data management, data tabulation and data enhancing with the use of advanced functions so participants will be able to learn to prepare documents with rich content. 

Who can participate in the education 

It is suitable for the participation of individual participants and corporate employees who can use MS Office and Excel programs. 

Learning Outcomes 

• Will be able to keyboards shortcut which is process of speed up. 
• Will be able to use in the solution of complex problems is used functions for simple calculation 
• Will be able to create visual warning systems by using tools such as conditional formatting and verification with formulas. 
• Will be able to whit use textual functions at tables which is have a bad data will be able to ensure referential integrity and data outline. 
• Will be able to get can changed data according to conditions by using logical functions. 
• Will be able to get comparison between tables and information transfer between tables by using lookup and reference functions. 
• Will be able to make at tables detailed questioning and reporting by using excel tools in data menu. 
• Will be able to use different ciphering methods for privacy and security in documents they have prepared. 

The process of education 

Education Dates: First is got preregistration then educations are dated and informed according to demand. 

The method of education: Online education (synchronous) 

Duration of education: 80 hours 

Number of participants: Maximum of 15 people in Closed Group Education / Minimum of 10 people in Open Group Education 

Education Content 

Module 1: Data input, Arrange and Practical hints 

• Data entry into cells, edit cells, format cells 
• Work with line and column 
• Entry and editing of four operations formula  
• Entry of quick date, clock, and serial data 
• Add logo or picture and editing 
• Keyboard shortcut and practical hints 
• Making selection in cells 
• Copying and paste special processes 
• Working with tables and create table  
• Searching, finding and changing  

Module 2: Working with documents  

• Save a document, Excel 97 – 2003 format 
• Excel format; .xls, .xlsx
• Save in PDF, XPS format 
• Save encrypted file  

Module 3: Worksheets 

• Add worksheets then delete, hide, give a name and give a tabbing color. 
• Copy worksheets and move 
• Simultaneous operations in worksheets. 
• Use of options Freeze Panes, Split, Arrange  
• Adding background picture to worksheets. 

Module 4: Working with functions  

• Working with function wizard. 
• Making a quick formula entry. 
• Referencing row and column in formulas. 
• Update data without written formula.  
• Use of functions SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF, TODAY, YEAR  
• Basic textual functions (UPPER, LOWER, PROPER) 
• Custom search (Find cells with formula, scratch, custom content in table.) 
• Making a copy, paste special and formula duplication. 
• Making a step-by-step calculation to formula. 
• Making an action with formula between document and worksheets. 
• Use of fixed reference in formulas. 
• Arrange custom view and move quickly through the document. 

Module 5: Formatting 

• Format cells 
• Add description to cells. 
• Adding special unit like kg, It, Ad  
• Formatting numeric value and historical value. 
• Making an aligning, border and padding. 
• Making a formatting shaped like table. 
• Making a custom formatting to cell. 

Module 6: Working with data 

• Making an action with formula between document and worksheets. 
• Use of fixed reference in formulas. 
• Conditional format 

-Data elements, color index, icon options 

-Formatting with available rules. 

-Making a special rule. 

-Use of formula in conditional format. 

• Restriction of data entry. 

Module 7: Interrogating and reporting tools. 

• Enumeration operations and sequencing according to format. 

-Simple sequence operations. 

-Sequencing with simultaneous at more than one column. 

-Creating a special sequencing list. 

• Filtration operations. 

-According to textual, numerical and historical values filtration. 

-According to colors and icons filtration. 

-Use of advanced level filter tool. 

• Taking subtotal with tables. 
• Working with 3D formulas. 
• Separating data into columns. 

Module 8: Create a Graphic 

• Creating pie charts and carrying graphics. 
• Analyzing and changing of graphic items. 
• Formatting and styling of graphic properties. 

Module 9: Page setup and Printing 

• Making a page setup and redirection. 
• Adding margins, header and footer. 
• Choosing print area and print preview. 
• Printing titles and print options. 

Module 10: Logical Functions 

• Making conditional transactions. 
• Writing formula using nested functions. 
• Use of nesting multiple if functions. 
• Working with SUMIF and COUNTIF functions. 
• Working with SUMIFS and COUNTIFS functions. 
• Working with DSUM, DCOUNT, DAVERAGE functions. 
• What to do in case of error occurrence and errors that may occur when writing formulas. 
• Write of functions without wizard. 
• Using simultaneous knowledge functions and logical functions. (ISERROR, ISERR, ISBLANK, ISNA, ISTEXT, ISNUMBER) 

Module 11: Working with search and reference functions. 

• Searching knowledge at numeric range and historic range. (VLOOKUP, HLOOKUP) 
• Comparing data and tables. (VLOOKUP) 
• IFERROR error-checking function. 

Module 12: Fixing bad data with textual functions 

• Converting texts to lowercase or uppercase letters (UPPER, LOWER, PROPER) 
• Getting information from inside any text (LEN, LEFT, RIGHT, MID, FIND, SEARCH) 
• Dividing the text into columns. 
• Creating new texts from existing information. (CONCATENATE, TEXT, REPLACE, REPT) 
• Fixing problematic data (TRIM, VALUE) 

Module 13: Working with date functions 

• Creating new date with day, moon, year knowledge. 
• Find the number of days and working days between two dates (NETWORKDAYS, WORKDAY) 

Module 14: Creating pivot table 

• Creating pivot table 
• Creating percental value at pivot table 
• Automatically create a separate pivot table for each data 
• Creating a calculated field 
• Grouping the data 
• Creating pivot graphics  

The Process of Education:

• The training will be broadcast live simultaneously over the Internet.
• In order to participate in the training, you must have internet access and a suitable internet package in your current environment.
• In order for the educational system to be used,camera and microphone equipment have been introduced, a device that can connect to the Internet(smartphone, tablet and preferably computer) is needed.
• Participants will log in to the system with their cameras and microphones turned on together with our instructor who provides the training during the specified lesson hours.
• User names and passwords will be sent to the participants' e-mails before the training starts.
• After the trainings are completed, the system will be kept open for participants for another 7 days. By the end of the seventh day at 23:00, the system will be completely shut down.
• In order to help us during the training period, a whatsapp group will be created in the form of sending messages only to the administrator, and all kinds of support information will be shared by the administrator during the lesson period. Participants have all kinds of questions and problems usem@uludag.edu.tr they can write to our address. The mail will be followed up instantly during the training.

Important Information:

• Certificates are issued for participation of 80% and above of the total training period. for participation of 79% and below, no documents will be issued. 
• After the completion of the training period, certificates will be issued after the controls belonging to our institution and will be sent to the addresses you entered at the time of registration with PTT Cargo. Your address and contact information must be up-to-date.
• Participants have all kinds of questions and problems usem@uludag.edu.tr they can write to our address. The mail will be followed up instantly during the training.
• Application for education and paying the tuition fee online are made via the Internet. Installment payments cannot be paid on the participants' bank credit cards.