Facebook Instagram Youtube Linkedin
Tap the number to call us now
9975214585

Advanced Excel Session

Each one working in Corporate in any role needs to play with a lot of instant data coming from several sources. To process the data, present it and so on, one needs to use Excel extensively. As all of us admit, Excel has several advanced features and common users hardly use these features. Keeping that in mind, we are announcing an Advanced Excel session with Macros.

In response to that, I am announcing the Advanced Excel training batch starting from 3rd October 2020. I am sending this to the interested participants to register for the session. It will be in virtual classroom mode using Zoom. Since Excel sessions are hands-on, I plan to cover limited participants, hence it would be useful to register quickly.

To get an idea about the functionalities covered in the session, I have broken the course into 4 Modules. Module 1 is Basic Excel. These are simple things, which are pre-requisites for attending the course. We cover Module 2 and Module 3 in Advanced Excel and Module 4 in Excel VBA – Macros session. Document showing Excel functionalities in Module 1 to 4 can be accessed here.

The whole session is conducted using an Exercise book with dummy data for trials. The Exercise book itself is made using Excel. Typical Exercises covered in the Advanced Excel session are given below.
To get an idea about functionalities covered in session, I have broken course in 4 Modules. Module 1 is Basic Excel. These are simple things, which are pre-requisites for attending course. We cover Module 2 and Module 3 in Advanced Excel and Module 4 in Excel VBA – Macros session. Document showing Excel functionalities in Module 1 to 4 can be accessed here.

Exercises Covered in typical Advanced Excel session (Module 2 and 3)

(Based on duration and speed of learners, 70% to 80% of these exercises are covered in each batch)

Exercise Text

Create a formula in Highlighted Cell to show Multiplication of 2 and 2 I.e. Column and Row Headers. It might be =C2*B3. However the formula should then be "pull and dragged" horizontally and vertically, without changing It. It should show simple Number table.

Sheet Named Name_List has List downloaded from online system. It has Last Name of employee, a comma, First Name with other middle names, a # sign and City. Break it and make 2 columns showing First Name Last Name and City Create a table which shows number of associates from one City.

Break the text similar to last exercise. Use Formulas this time to solve it. Create Citywise summary for data provided.

In Emp_Master sheet, there is list of some employees below the main master list. For each given Emp ID, fetch his Last Name, Remaining Name and City in next 3 columns.

RateCard is a table showing Grades in Rows and Skill as columns. The table below is showing some Skills and Grades. Write a formula in highlighted cell to identify Rate for given Skill and Grade. Formula to be then pulled and dragged for all other roles.

Use grid in PM_RateCard Sheet for exercise. Sheet PM_Tasks is WBS or Work Breakdown Structure. It shows task, skill needed, percentage utilisation and grade. Create New sheet and break this table in separate Columns and Rows, so that you can use formula to identify Rate and using utilisation, calculate Estimated Cost. Add a column for Calculates cost for each Row.

In given Exercise, list of tasks for given project are given. They are all to be done sequentially. Assuming Project starts on next Monday, calculate start date and complete date for each task. Remember project works only from Monday to Friday. It also observes holidays, which are given in the list below.

It is continuation to PM_Tasks Sheet. Sheet created as solution to PM_Tasks could be used. Else use this sheet. Populate the column Estimate Adherence Category based on rule given in comment. To see, position cursor on the column header. Populate the column Grade Category based on rule given in comment. Give color as shown in table on right side based on Estimate Adherence and Grade.

Create a grid report showing Cost spent in each Adhearance Category and Grade Category. Identify which group has maximum contribution. Sponsor asked to change hours for which resources work per day to 9.5 and days per month to 22. Change it and see revised chart.

Customer expressed that his budget is only 5.00 INR. Identify how many days resources need to work per month, to deliver project in customer expected budget. Add customer special discount based on HTR of grade. If discount of x is given to T, then x - 2% could be given to M and x - 4% could be given to H. Calculate the discount you can given to reach the target number.

Create a summary showing Gradewise Cost for Each life cycle stage. E.g. U4 for Requirement Capture, etc.

Sheet TeamLoad has name of team member, his project, % Utilisation for that project, From Date and To Date. You want to check how is each person busy, vacant slots if any, double allocation if any. Create a Grid showing names of associate as Rows and Dates as Columns. Grid should show % Utilisation of given person on given date. Show Utilisation Exceeding 120% as "Red" and Utilisation Below 60% as "Amber". All other should be shown as "Green".

In sheet PM_TeamLoad where Employee Name and Email ID is provided, show the total Utilisation filtered for date range shown on top

In same sheet (PM_TeamLoad) where Employee Name and Email ID is provided, Send a mail to each person with his project assignments only

Populate the table shown in sheet with Assignment on which each person is working from Team_Load Sheet. It will show name of first assignment he is working, start date, end date and Utilisation. Also bring next assignment and details about it.

Send individual mail to each resource providing him details about his next and following assignment.

Create a slide pack showing assignments handled by each of the resources in Send Mail sheet.

Sheet named Publish Report has actual progress on 31 Jan 2015. It shows planned Start, Finish and utilisation. It also has Actual Start, Finish and Utilisation. Rows where Actual Finish is not mentioned, are tasks which are not completed. Calculate Schedule and Effort Variance. Computation logic is given in Comments.

Publish independent report for each Project in a separate Web Page (MHT) Create a Summary Page for All projects with links to open details page created earlier.

Get Free Career Guidance


    Module wise topics covered in Excel Advanced Session

    Module 1 - Basic Excel

    • Custom formatting
    • Format Cells Wrap / Merge / Orientation, repeat format
    • Freeze Panes / Arrange windows
    • Paste Special: Operations, Transpose
    • Toolbar buttons: Format paint, Merge, borders, zoom
    • Hide / Unhide - Column, Row or Sheet
    • Text To Columns
    • Data Sorting
    • Auto Filter - Filter based on colour
    • Formulas
    • Formulas to get data from other workbook
    • SUM, COUNT
    • MaX, Min, Average
    • Data types
    • Entering and formating Dates and numbers

    Module 2 - Advanced Functionalities

    • Range Names – Productivity of managing worksheets
    • Pivot table – Quickest report writer
    • Pivot Charts
    • Break text based on separator e.g. comma, #
    • Label Ranges and Cells Names – Readable formulas
    • CONCATENATE - &
    • Custom Lists
    • What is $ sign. E.g. $A$4, Use of “$” in dragging formulae
    • Working across multiple sheets / files
    • Sum Product
    • Introduction to Graphs & Charts
    • Int, Ceiling, Floor, Mod
    • Sumifs, Countifs
    • Date Conversion - Date, DateValue, Converting date formats
    • Use of Subtotal instead of Sum
    • Lookups – Connect data
    • Lookup (VLOOKUP, HLOOKUP)
    • VLookup – Why there is “False” at end?
    • Validation
    • Conditional Formatting
    • Formula Auditing, Evaluate
    • Protection
    • Pivot Table: Field Settings, Table options, Page level filters, Pivot Chart, Calculated field

    Module 3 - Adavanced Functionality & Collabaration using Word, Outlook

    • PROPER, UPPER, LOWER, TEXT, LEN, REPT, FIND
    • Parameterise – Wherever e.g. Today, Ratios, Shift hours
    • Substring functions (Mid / Left / Right)
    • SUMIFS, COUNTIFS, RANK, CHAR
    • IF: Simple, Nested
    • AND, OR, HYPERLINK, ABS,
    • Grouping Data
    • Sum Product
    • Networkdays, Day, Month, Year, Now, Today, EOMonth, EDate, Date,
    • Day Calculations
    • Mail Merge - Using Excel data and sending mails
    • Creating Slides from Word or Excel - Word - PPT
    • Scenarios
    • Data Entry - Validation
    • Goal Seek
    • Solver
    • Link Tables with Access
    • Record macros
    • Create user-defined functions
    • Formula is a Function
    • Work with the VBA editor
    • MSGBOX - VBA

    Module 4 - VBA (Macros)

    • What is Excel Macro ?
    • How to Record / Run Excel Macro ?
    • How Add Developer’s Tab in Excel 2007 / 2010
    • How to add different types of controls like Text Box, Radio button, button etc. in Excel
    • Visual Basic Editor in Excel
    • VBA Control Property
    • How to write Excel Macro – Your First Excel Macro
    • Using MsgBox
    • Debugging Code
    • How to Run you code in Debug Mode?
    • What is Break Point and how to use it?
    • What is Compile Error?
    • Excel Macro Tutorial : Excel User Form
    • VBA Spin Button
    • Toggle Button in Excel
    • ComboBox / Drop Down List In Excel
    • ListBox In Excel
    • How to Open Excel Workbook using Excel Macro
    • Using RANGE Object in Excel Macro
    • Work with variables
    • Array
    • InputBox
    • Create procedures and sub routines
    • Creating User Defined Functions
    • If – End if, For – Next, Do While
    • Exercise - Break file into files based on rule
    • Sending automated mails from Macros
    • Modify the Excel menu system to show your macros
    • Make macros start automatically
    • Work with Excel objects
    • VBA Programming : Variables in Excel VBA
    • Implicit and Explicit declaration of variables in Excel Macro
    • Important : Declaring multiple Variables with one Dim Statement
    • For Next Loop in Excel Macro
    • While Loop and Do While Loop in Excel VBA
    • VBA Programming : If Then Statements / If else Statements
    • VBA Programming : Decision – Select Case in Excel VBA
    • Creating and updating Pivot tables using Macro
    • Opening file by File Open Dialouge
    teacher2.jpg
    Prateek Piley — “Happy to share this review, Nice trained & professional staff, Excellent coordination with everyone, help in solving your queries. Highly recommended.Keep up the good work👌👏👏.”
    Thursday, August 31, 2017
    team-7.jpg
    sudhir bagadhi — “I am very happy with the training and material .It is very effective and all topics were covered and overall maintained standards absolutely.Its a great experience to be a part of training.”
    Thursday, August 31, 2017
    team-6.jpg
    Anil Tatipaka — “I have recently completed PSM training through NeoSkills, this is my second training with them (previously I complete Prince2 Agile) and both the times I had a good experience with learning..”
    Thursday, August 31, 2017
    review_3.png
    amrut limkar — Nice coordination with the staff. Ashish was very helpful giving nice training Overall very nice experience to complete the training and exam..”
    Thursday, August 31, 2017