Advance Excel

Course Title Course Language Est.Duration-days Est.Duration-Hours
Advance Excel 2016 English Flexible 8

Learning Objective

This course is designed for students who are interested in going beyond basic skills and need to chart and format using Microsoft® Office Excel® 2013/2016.

Course Contents

Formula and Function Tips and Shortcuts

  • Displaying and highlighting formulas
  • Save Displaying and highlighting formulas
  • Using Auditing tools
  • Save Using Auditing tools
  • Using entire row and column references
  • Save Using entire row and column references
  • Copying column formulas instantly
  • Save Copying column formulas instantly
  • Converting formulas to values with a drag
  • Save Converting formulas to values with a drag
  • Updating values without formulas
  • Save Updating values without formulas
  • Debugging formulas with the F9 key
  • Save Debugging formulas with the F9 key
  • Enhancing readability with range names
  • Save Enhancing readability with range names
  • Tabulating data from multiple sheets
  • Save Tabulating data from multiple sheets

Formula and Function Tools

  • Hierarchy of operations in formulas
  • Save the Hierarchy of operations in formulas
  • Formulas tab for locating functions
  • Save the Formulas tab for locating functions
  • Insert Function for learning about unfamiliar functions
  • Save Insert Function for learning about unfamiliar functions
  • Extending the capabilities of AutoSum
  • Save Extending the capabilities of AutoSum
  • Absolute and relative references
  • Save Absolute and relative references
  • Using mixed references in formulas
  • Save Using mixed references in formulas
  • Using auto calculate in the status bar
  • Save Using auto calculate in the status bar

IF and Related Functions

  • IF logical tests
  • Save IF logical tests
  • Expanding nested IF statements
  • Save Expanding nested IF statements
  • AND, OR, and NOT functions with IF
  • Save AND, OR, and NOT functions with IF
  • Using IFS for multiple conditions
  • Save Using IFS for multiple conditions

Lookup and Reference Functions

  • Looking up information with VLOOKUP and HLOOKUP
  • Save Looking up information with VLOOKUP and HLOOKUPFinding
  • approximate matches with VLOOKUP and HLOOKUP
  • Save Finding approximate matches with VLOOKUP and HLOOKUP
  • Finding exact matches with VLOOKUP
  • Save Finding exact matches with VLOOKUP
  • Nesting lookup functions
  • Save Nesting lookup functions
  • Using VLOOKUP with large tables
  •  Save Using VLOOKUP with large tables
  •  Finding table-like information within a function using the CHOOSE function
  •  Save Finding table-like information within a function using the CHOOSE function
  • Using the SWITCH function for formula-embedded selection
  • Save Using the SWITCH function for formula-embedded selection
  • Locating data with the MATCH function
  • Save Locating data with the MATCH function
  • Retrieving information by location
  • Save Retrieving information by location
  • Using MATCH and INDEX functions together
  • Save Using MATCH and INDEX functions together

Power Functions

  • Tabulating data using a single criterion
  • Save Tabulating data using a single criterion
  • Tabulating data using multiple criteria
  • Save Tabulating data using multiple criteria
  • Using MAXIFS and MINIFS
  • Save Using MAXIFS and MINIFS
  • Preventing double counting
  • Save Preventing double counting

Statistical Functions

  •  Finding the middle value with MEDIAN and most common value with MODE
  • Save Finding the middle value with MEDIAN and most common value with MODE
  • Ranking data without sorting with RANK and RANK.EQ
  • Save Ranking data without sorting with RANK and RANK.EQ
  • Finding the largest and smallest values with the LARGE and SMALL functions
  • Save Finding the largest and smallest values with the LARGE and SMALL functions
  • Tabulating blank cells with the COUNTBLANK function
  • Save Tabulating blank cells with the COUNTBLANK function
  • Using COUNT, COUNTA, and the status bar
  • Save Using COUNT, COUNTA, and the status bar

Math Functions

  • Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
  • Save Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
  • Working with MROUND, CEILING, and FLOOR for specialized rounding
  • Save Working with MROUND, CEILING, and FLOOR for specialized rounding
  • Using the INT and TRUNC functions to extract integer data
  • Save Using the INT and TRUNC functions to extract integer data
  • Finding the remainder with MOD and using MOD with conditional formatting
  • Save Finding the remainder with MOD and using MOD with conditional formatting
  • Exploring practical uses for the RAND and RANDBETWEEN functions
  • Save Exploring practical uses for the RAND and RANDBETWEEN functions
  • Converting a value between measurement systems with CONVERT
  • Save Converting a value between measurement systems with CONVERT
  • Using the powerful AGGREGATE function to bypass errors and hidden data
  • Save Using the powerful AGGREGATE function to bypass errors and hidden data
  • Using the ROMAN and ARABIC functions to display a different number of systems
  • Save Using the ROMAN and ARABIC functions to display a different number of systems

Date and Time Functions

  • Understanding Excel date and time capabilities in formulas
  • Save Understanding Excel date and time capabilities in formulas
  • Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
  • Save Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND
  • functions
  • Using the TODAY and NOW functions for dynamic date and time entry
  • Save Using the TODAY and NOW functions for dynamic date and time entry
  • Identifying the day of the week with the WEEKDAY function
  • Save Identifying the day of the week with the WEEKDAY function
  • Counting working days with the NETWORKDAYS function
  • Save Counting working days with the NETWORKDAYS function
  • Determining a completion date with the WORKDAY function
  • Save Determining a completion date with the WORKDAY function
  • Tabulating date differences with the DATEDIF function
  • Save Tabulating date differences with the DATEDIF function
  • Calculating end-of-month and future and past dates with EDATE and EOMONTH
  • Save Calculating end-of-month and future and past dates with EDATE and EOMONTH

Array Formulas and Functions

  • Extending formula capabilities with array formulas
  • Save Extending formula capabilities with array formulas
  • Counting unique entries in a range with an array formula
  • Save Counting unique entries in a range with an array formula
  • Determining frequency distributions with the FREQUENCY function
  • Save Determining frequency distributions with the FREQUENCY function
  • Flipping row and column orientation with TRANSPOSE
  • Save Flipping row and column orientation with TRANSPOSE
  • Building analysis via regression techniques with TREND and GROWTH
  • Save Building analysis via regression techniques with TREND and GROWTH
  • Using array formulas and the MATCH function for complex lookups
  • Save Using array formulas and the MATCH function for complex lookups

Reference Functions

  • Getting data from remote cells with the OFFSET function
  • Save Getting data from remote cells with the OFFSET function
  • Returning references with the INDIRECT function
  • Save Returning references with the INDIRECT function
  • Using INDIRECT with Data Validation for two-tiered pick list scenarios
  • Save Using INDIRECT with Data Validation for two-tiered pick list scenarios

Text Functions

  • Locating and extracting data with the FIND, SEARCH, and MID functions
  • Save Locating and extracting data with the FIND, SEARCH, and MID functions
  • Extracting specific data with the LEFT and RIGHT functions
  • Save Extracting specific data with the LEFT and RIGHT functions
  • Using the TRIM function to remove unwanted spaces in a cell
  • Save Using the TRIM function to remove unwanted spaces in a cell
  • Using ampersands and CONCATENATE to combine data from different cells
  • Save Using ampersands and CONCATENATE to combine data from different cells
  • Using the CONCAT and TEXTJOIN to combine data from different cells
  • Save Using the CONCAT and TEXTJOIN to combine data from different cells
  • Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
  • Save Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
  • Adjusting character content with the REPLACE and SUBSTITUTE functions
  • Save Adjusting character content with the REPLACE and SUBSTITUTE functions
  • Using the utility text functions: TEXT, REPT, and LEN
  • Save Using the utility text functions: TEXT, REPT, and LEN

Information Functions

  • Extracting information with the CELL and INFO functions
  • Save Extracting information with the CELL and INFO functions
  • Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
  • Save Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
  • Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
  • Save Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
  • Tracking and highlighting formula cells with the ISFORMULA function
  • Save Tracking and highlighting formula cells with the ISFORMULA function

Auditing and troubleshooting formulas

  • What are tracer arrows
  • Adding and removing tracer arrows
  • Auditing tools: Error checking and tracing
  • Step by step formula processing
  • Using the watch window in Troubleshooting

Goal Seek and Solver

  • What if analysis using Goal Seek
  • Activating the solver add-in
  • Using Solver to Complete a what if
  • Adding constraints to the solver

Creating and Pivoting PivotTables

  • Introducing PivotTables
  • Save Introducing PivotTables
  • Format data for use in a PivotTable
  • Save Format data for use in a PivotTable
  • Create a PivotTable
  • Save Create a PivotTable
  • Pivot a PivotTable
  • Save Pivot a PivotTable
  • Configure a PivotTable
  • Save Configure a PivotTable
  • Connect to an external data source
  • Save Connect to an external data source
  • Consolidate data from multiple sources
  • Save Consolidate data from multiple sources
  • Manage PivotTables
  • Save Manage PivotTables
  • Define a default PivotTable layout
  • Save Define a default PivotTable layout

Summarizing PivotTable Data

  • Manage subtotals and grand totals
  • Save Manage subtotals and grand totals
  • Change the data field summary operation
  • Save Change the data field summary operation
  • Summarize more than one data field
  • Save Summarize more than one data field
  • Manage dates in a PivotTable
  • Save Manage dates in a PivotTable
  • Create a calculated field
  • Save Create a calculated field
  • Use PivotTable data in a formula
  • Save Use PivotTable data in a formula
  • Drill down to the underlying data
  • Save Drill down to the underlying data

Sorting and Filtering PivotTable Data

  • Sort PivotTable data
  • Save Sort PivotTable data
  • Create a custom sort order
  • Save Create a custom sort order
  • Filter a PivotTable field by selection
  • Save Filter a PivotTable field by selection
  • Filter a PivotTable by rule
  • Save Filter a PivotTable by rule
  • Filter a PivotTable using a search filter
  • Save Filter a PivotTable using a search filter
  • Filter a PivotTable using slicers
  • Save Filter a PivotTable using slicers
  • Format slicers
  • Save Format slicers
  • Filter with report filter fields
  • Save Filter with report filter fields
  • Clear and reapply PivotTable filters
  • Save Clear and reapply PivotTable filters
  • Filter a PivotTable using a timeline
  • Save Filter a PivotTable using a timeline

Formatting PivotTables

  • Apply a PivotTable style
  • Save Apply a PivotTable style
  • Create a PivotTable style
  • Save Create a PivotTable style
  • Change the PivotTable layout
  • Save Change the PivotTable layout
  • Change the data field number format
  • Save Change the data field number format

Applying Conditional Formats to PivotTables

  • Highlight cells by applying a rule
  • Save Highlight cells by applying a rule
  • Highlight the top or bottom values in a PivotTable
  • Save Highlight the top or bottom values in a PivotTable
  • Format cells using data bars
  • Save Format cells using data bars
  • Format cells using color scales
  • Save Format cells using color scales
  • Format cells using icon sets
  • Save Format cells using icon sets
  • Edit a conditional formatting rule
  • Save Edit a conditional formatting rule
  • Control how multiple rules are applied
  • Save Control how multiple rules are applied
  • Delete a conditional formatting rule
  • Save Delete a conditional formatting rule

Creating and Formatting Pivot Charts

  • Create a PivotChart
  • Save Create a PivotChart
  • Pivot a PivotChart
  • Save Pivot a PivotChart
  • Filter a PivotChart
  • Save Filter a PivotChart
  • Format a PivotChart
  • Save Format a PivotChart
  • Change the layout of a PivotChart
  • Save Change the layout of a PivotChart
  • Change the chart type of a PivotChart
  • Save Change the chart type of a PivotChart
  • Add a trendline to a PivotChart
  • Save Add a trendline to a PivotChart

Printing PivotTables

  • Print a PivotTable
  • Save Print a PivotTable
  • Set PivotTable print options
  • Save Set PivotTable print options
  • Print each item on its own page
  • Save Print each item on its own page
  • Print a PivotChart
  • Save Print a PivotChart

Manipulating PivotTables

  • Record and review an Excel macro
  • Save Record and review an Excel macro
  • Run an Excel macro
  • Save Run an Excel macro
  • Create a simple PivotTable presentation kit
  • Save Create a simple PivotTable presentation kit
  • Change PivotTables using keyboard shortcuts
  • Save Change PivotTables using keyboard shortcuts

Enabling and Adding Tables to the Data Model

  • Create relationships between tables
  • Save Create relationships between tables
  • Create a PivotTable using the data model
  • Save Create a PivotTable using the data model
  • Edit table relationships
  • Save Edit table relationships
  • Deactivate and delete table relationships

Review the Basic Elements of an Excel Chart

  • Create Basic Charts
  • Style Your Charts with the Design Tab
  • Fine-Tune Your Chart Elements
  • Format Tab: Insert Pictures, Shapes, and Text Boxes
  • Adjusting Specific Chart Types
  • Customize column and bar charts
  • Save Customize column and bar charts
  • Customize line charts
  • Save Customize line charts
  • Customize pie charts
  • Save Customize pie charts
  • Customize area, stock, and XY charts
  • Save Customize area, stock, and XY charts
  • Customize doughnut, bubble, and radar charts
  • Save Customize doughnut, bubble, and radar charts

Other Specialized Excel Charts

  • Create combination charts
  • Save Create combination charts
  • Create Gantt charts
  • Save Create Gantt charts
  • Create dynamic charts with filtering
  • Save Create dynamic charts with filtering
  • New Chart Types in Excel 2016
  • Overview of new chart types
  • Save Overview of new chart types
  • Tree Map chart
  • Save TreeMap chart
  • Sunburst chart
  • Save Sunburst chart
  • Waterfall chart
  • Save Waterfall chart
  • Histogram chart
  • Save Histogram chart
  • Pareto chart
  • Save Pareto chart
  • Box and Whisker chart
  • Save Box and Whisker chart
  • Funnel chart
  • Save Funnel chart
  • Chapter Quiz
  • Change a Chart’s Data Source
  • Print and Share Charts
  • Macros
  • What is a Macro
  • Creating and running your first macro
  • Saving workbooks with Macros
  • Macro security settings for Workbooks with Macros
  • The Personal Macro Workbook
  • Deleting Macros
  • Use of relative or absolute referencing
  • Trigger a Macro with a Keyboard Shortcut
  • Formatting with a Macro
  • Switch Scenarios and Views with Macros
  • Use of worksheet buttons to Trigger Macros
  • Customizing form buttons and other shape triggers
  • Assigning Macros to Ribbon Icons
  • Create your own ribbon
  • View and edit Macro code

Certificate

Attendees will get a training certificate after the completion of the course.

Training Details

Training Fees : K.D 75.000
Training Days :  4 days
Training Date : Flexible
Training Timing : Flexible
Training Language : English
Material Language : English
Enquiry & Registration : For inquiry and registration please:
Call : 22204171
Whatsapp : 65701004
Email : info@bia.edu.kw