Effective Use of MS Excel

About Training:

The course introduces Excel at intermediate level, introducing the new features of Excel. Features covered: calculations tips and tricks, keyboard shortcuts to insert formulas,creating reports with Excel Pivot Tables, presenting reports with charts & graphs, and highlighting data anomalies with conditional formatting, automating tasks using macros & more.

You Will Learn:

• Introduction, navigation & selection
• Analyzing data with excel aggregate functions
• Worksheet formatting and presentation
• Leveraging formulas and functions
• Managing and analyzing data
• Creating a visual presentation of data
• How to use Data tools in Data Tab
• Managing files and templates
• Automating tasks with macros

 Who Should Attend?

This Training is intended for professionals who need to extend their Excel skills from intermediate to advance level.

Introduction, Navigation & Selection

The Spreadsheet Model

• Understanding Excel new interface & features
• Worksheets and their contents
• Tabs, group, commands & their customization

Navigating The Interface

• General navigation
• The Ribbon
• Managing large spreadsheets by zooming
• Using Custom Views
• Manage versions of a workbook

 Data Editing

• Modifying cell contents and formats
• Inserting data with AutoFill
• Knowing when to use Paste Special
• Finding and replacing data
• Goto special command to select desired Data

Analyzing Data with Excel AGGREGATE Functions

• Summarizing data with Excel Aggregate functions
  • SUMIF()
  • COUNTIF()
  • AVERAGEIF()

Worksheet Formatting and Presentation

Initial Formatting

• Configuring cell numeric formats
• Aligning data within cells
• Efficient presentation using the Format Painter
• Fitting cells to data size

Worksheet presentation

• Freezing worksheet areas
• Hiding content in worksheets
• Editing,modifying, and saving templates

 Innovative formatting

• Styling cells and sheets with prebuilt styles
• Conditionally formatting cells and data
• Rotating and wrapping text

Leveraging Formulas and Functions

Formula basics

• Determining the order of operations
• Specifying relative and absolute cell references
• Introducing conditional logic in formulas
• Nesting expressions in formulas

 Working with Names & Formulas

• Cell and range names
• Creating and using names
• Managing names
• Keyboard Shortcuts to use Names
• Assigning values to names
• Calculating figures based on conditions

Extending formulas and functions

• Putting strings together with concatenation
• Finding data with a formula: VLOOKUP, HLOOKUP
• Using IFERROR and IFNA functions with VLOOKUP
• Copying and pasting formulas and/or values
• Handling formulas with dates

 Auditing worksheet formulas

• Deciphering tagged cells with errors
• Identifying formula error indicators
• Tracing spreadsheet formula errors

Managing and Analyzing Data

Managing and Filtering Data using Tables

• Filtering data
• Advanced filtering for complex queries
• Working with tables
• Understanding Table Nomenclature
• Slice & Dice Data with Slicers

 Subtotaling and Outlines

• Outlining and subtotals
• Outlining and consolidating data
• Creating Nested subtotals

Condensing and Refining Data With Pivot Table

• Creating multiple reports on sing data set using PivotTables
• Sorting, Grouping and filtering Pivot Reports
• Formatting PivotTables
• Creating dynamic PivotCharts
• Extracting multiple reports with Pivot filter option
• Conditional formatting with PivotTable

Creating a Visual Presentation of Data

• Charting your data
• Determining when charts are required
• Adjusting chart layouts, title and labels
• Switching chart type or data source
• Advanced charting techniques
• Chart formatting options
• Combination charts

 Highlight Data Anomalies with Conditional Formatting

• Highlight required values with Conditional Formatting
• Analyzing Trends with
  • Data bars
  • Color Scales
  • Icon Sets

Data Tools

Working with Flash Fill

• Flash fill to transform data
• Changing case with flash fill
• Extracting required Text with Flash fill
• Quick analysis to apply Charts, Tables, Sparklines& Formulas

 Splitting Text to Columns

• Splitting text form single columns to multiple columns
• Understanding Delimited and Fixed Width transformation
• Converting values formatting using Text to Columns

 Validating Data to avoid input Wrong Data

• Controlling the limits of data
• Getting list of values in Drop Down Menu

Remove duplicates values

• Removing duplicates values from a column
• Using multiple columns to remove duplicates

Consolidate multiple data sets

• Creating consolidated reports using multiple tables/sheets/ files
• Creating 3D and static consolidated reports

Managing Files and Templates

Managing Workbooks and Worksheets

• Using multiple worksheets and workbooks
• Using multiple workbooks Linking worksheets with 3D formulas
• Linking workbooks
• Managing workbooks

 Printing workbooks

• Setting options on the Page Layout tab
• Viewing and adjusting page breaks
• Developing appropriate headers and footers

Working with Templates

• Using Built – In Templates
• Customized your own Workbook & Worksheet Templates

Automating Tasks with Macros

• Understanding VBA & Editor’s Interface
• Recording Simple Macro for Number Formatting
• Putting your daily tasks in Macro
• Understanding Macro Security

Training time: 9AM to 5PM

Training Date: 25^th&26^th July, 2022

Training Investment: PKR 32,500 + ST

Training Location:  PSTD, Lahore

For Further Details 

Email: babar.khan@wordpress-764963-3017117.cloudwaysapps.com  / uzair.tariq@wordpress-764963-3017117.cloudwaysapps.com

Number: 0333-2406753 / 021-35857484/ 03319763144

Click here to Download Flyer

Payment Details:

The cheque will be made under the name of “Pakistan Society for Training and Development” and dispatch it to following address;

Pakistan Society for Training and Development
Plot # TC-3, 34th Street, Khayaban-e-Seher
Phase V, DHA, Karachi 75500.

Cancellation Policy:

PSTD Cancellation Policy – Cancellations made at least 5 working days prior to the program will be refunded 50%. If a booking is canceled in less than 5 working days, no refunds can be given. Cancellations must be confirmed by a letter or email. Substitutions may be made at any time for the same program only. In case of the participant not showing up on the day of the training a replacement can be sent.