Effective Use of EXCEL

Introduction:

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.

Learning outcomes:

• 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.

Contents:

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 Pivot Tables
• Sorting, Grouping and filtering Pivot Reports
• Formatting PivotTables
• Creating dynamic Pivot Charts
• 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

Date:25^th and 26^thFebruary 2022

Mode:virtual

Time:9:30 am to 12:30 pm

Training Fees: PKR 25,000 /- + GST/-

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.