Module 1: Review in Creating and copying formula with references
Know the different characters used in creating formulas for arithmetic, comparison and text operations.
2. Cell references
Know the different types of references (relative, absolute, & mixed) and when to use it.
Module 2: Working with Functions
1. Working with Dates and Time
Dates and Time appear to be tricky at times. Know how they are used in Excel using the TODAY, NOW, NETWORKDAYS, WORKDAY, EOMONTH & EDATE functions.
2. Converting Time into whole numbers and vice-versa
Learn how to convert time into number of hours, minutes, seconds.
3. Auditing your Formula
Formula auditing commands such as tracing precedent and dependent cells and evaluate formula help in tracing references and even errors. Learn how to troubleshoot formulas in your spreadsheet.
4. VLOOKUP using Exact Match (false)
Let us dissect the arguments needed in order to use VLOOKUP correctly. Get the corresponding value from another column based from the exact match of a lookup value.
5. VLOOKUP using Approximate Match (true)
Let us dissect the arguments needed in order to use VLOOKUP correctly. Get the corresponding value from another column based from an approximate match of a lookup value. Determine the rules to make the formula work.
6. Other Lookup Functions
Find out what other Lookup functions to use and when to use them: LOOKUP, INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.
7. Introducing: XLOOKUP for recent MS Excel licenses
XLOOKUP, one of the newest functions in Microsoft 365, is now available in recent licenses of MS Excel. Know how to search for lookup values with wildcards, or bring out a message if lookup value isn’t found.
8. Setting Conditions in getting the SUM, AVERAGE & COUNT
Learn how to get the sum, average and count based on defined conditions through SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIF
Module 3: Handling Data Efficiently
1. Giving Name to Ranges
Assigning names to ranges lessens time in referencing to other cells or tabs. Know how this can be applied to formulas.
2. Create / Format as Table
Create tables to organize and analyze related data.
3. Record and Run Macros
Do you have repetitive tasks or actions in your spreadsheet? You can record basic Excel commands to automate your actions (no programming background needed)
Module 4: Analyzing Data
1. Using PivotTables
Easily handle complex data with the use of PivotTables. Know how to arrange and summarize your data into basic PivotTables.
2. Formatting PivotTables
Learn to apply formatting of fonts, numbers and styles in PivotTables.
3. Using SLICERS and TIMELINES
Learn to filter quickly using slicers and times for PivotTables.
4. What-If Analysis: Goal Seek
Know how to meet a goal (value needed) by letting Goal Seek determine the input value.
Module 5: Reporting Data through Consolidation and Data Visualization
Learn to summarize data from separate ranges or sheets, consolidating in a single output range.
2. Outline Group and Ungroup
Quickly display summary rows or columns, or bring out detailed data for grouped columns or rows.
3. Inserting Subtotals
Learn how to quickly calculate rows of related data by inserting subtotals and totals
4. Customizing Number Formats
Number formats may be customized as percentages, currency, dates and more.
5. Conditional Formats with Default Rules
Identify critical data by highlighting cell/s based on rules. Use icon sets and data bars to emphasize on value (applicable only to Excel 2013 until recent version).
6. Conditional Formats with Customized Rules
Highlight data by using custom conditional formatting rules based on created formulas.
7. Building Combination Charts
Learn how to graph multiple data series, and combine at least 2 different charts at the same time.
Module 6: Addressing Data/Values Restriction through Data Validation
1. Various Types of Data Validation
Know the different types of Data Validation and when to use them. Put restrictions of number or text value input.
2. Creating Data Validation Lists
Create drop down list to ensure encoding of uniform data. Avoid misspellings and invalid entries.
Module 7: Printing your Spreadsheets
1. Page Layout
Set up your worksheets by going through the various options in Page Layout. Create headers/footers and print titlles per printed page.
Module 8: Sharing your Spreadsheets
1. Sharing the workbook
Collaborate with others by sharing your workbook to selected users (edit or view access only).
2. Protection and security in Excel
Protect your files by using passwords on your worksheets or on the file itself. Be aware of the limitations in using passwords.