The Excel Intermediate training is for participants who have basic Excel skills and are looking to enhance their knowledge in composing formulas and applying them to manipulate data. Participants will learn how to audit and correct their formulas. They will also learn to work with Excel functions from the date and time functions, text functions, statistics functions and lookup functions.
The training will also train them to work with many worksheets and large worksheets and finally print the worksheets.
This training will enable candidates to: –
- Create formulas
- Audit formulas
- Correct errors
- Apply Excel functions
- Apply Data validation
- Apply conditional formatting
- Work with many worksheets
- Work with large worksheets
- Create charts
MICROSOFT EXCEL (INTERMEDIATE)
Training Course Outline
DAY-1
CREATING AND AUDITING FORMULAS
- Constructing formulas in Excel.
- Understating Excel’s data types
- The four step process of auditing formula in Excel.
- Understanding Excel errors and how to correct them.
- Working with relative reference and absolute reference formulas.
CREATING FORMULA USING NAMED RANGE
- Naming a cell as an alternative to absolute reference.
- Naming a range of cells.
- Using the names in the formula. Editing and deleting the names.
- Using named formulas as an alternative to paste link.
FUNCTIONS
- Getting Help with Functions.
- Understanding function syntax and differentiating from required argument
- Applying the AND and OR function.
- Applying the IFS function (only for Excel version 2019 and above)
STATISTICAL FUNCTIONS
- The various COUNT functions to detect and optional argument.
- Reading the Microsoft documentation on help about function.
TEXT FUNCTIONS
- Functions to split data. Functions to join data.
- Functions to clean data with white spaces and unprintable characters.
- Functions to convert data types.
TIME & DATE FUNCTIONS
- The concept of date and DATESERIAL in Excel.
- The concept of time in Excel. The Date and Time data type.
- Solving problems relating to date and time.
- Calculating future and paste dates. Important Date and Time Functions.
LOGICAL FUNCTIONS
- How and when to use logical functions. The IF functions.
- Using nested IF function to solve multiple criteria problems.
- data irregularity.
- The conditional statistics functions: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS.
- Functions to calculate central tendencies.
DAY-2
LOOKUP FUNCTIONS
- Excels lookup functions.
- LOOKUP as an alternative to the IF logic. LOOKUP array form and vector form.
- Left lookup using LOOKUP. VLOOKUP and HLOOKUP.
- Understanding how to choose approximate match and exact match.
FORMATTING DATA IN EXCEL
- Using the preset formatting to format data.
- Creating your custom Number Formats. Creating formats for large numbers using prefixes such as “k”, “M” and “G”.
- Inserting symbols in number formats.
CONDITIONAL FORMATTING
- Apply preset formatting
- Create own rules
- Special techniques using icons
DATA VALIDATION
- Standard data validation
- Using built in validation rules to validate data.
- Creating input messages for users.
- Crating Error messages when users enter invalid data.
- Custom data validation
- Creating own custom validation formula.
- Creating cascading drop down options.
WORKING WITH MANY WORKSHEETS
- Creating multiple windows.
- Tilling the windows.
- Arranging the windows horizontal and vertical.
- Cascading the windows.
WORKING WITH LARGE WORKSHEET
- Splitting window to show various parts of a worksheet.
- Freezing top rows. Freezing left columns.
- Freezing rows and columns.
PASTE SPECIAL OPTIONS
- Using Paste Special to Add, Subtract, Multiply & Divide.
- Using Paste Special ‘Values’.
- Using Paste Special Transpose Option.
CHARTS
- Create sparklines
- Create charts and work with the various chart options.
- Standard charts will be covered: Column chart, Bar chart, multi column chart, pie chart and line chart