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

    Module 2 - Advanced Functionalities

    Module 3 - Adavanced Functionality & Collabaration using Word, Outlook

    Module 4 - VBA (Macros)

    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


      Need Help? Chat with us 24*7