Make your Own Systematic Intelligent tracking (Using MS Excel)

Thanks to my teachers, who guided me to the world of words. Thanks to my Guru who guides me to the world of Wisdom.

Dear co passengers, here I wish to share my a little Knowledge in Microsoft excel to create an intelligent tracking method. This method can be used for many purposes. I will be delighted if my information guides you a new knowledge.

Contents:

1. Aim
2. Flowchart
3. Implementation in Excel

• Creating Data Entry Table
• Pivot table
• Conditional Formatting

             4.  Conclusion

 1.  AIM

To create an Intelligent Tracking sheet in Microsoft Excel to accomplish following objective,

• Track the number of pending 11kV cable release order from a Government Agency.

2.   FLOW CHART

Flow chart is called the skeleton of any autonomous intelligence program. Create your on Flowchart in a rough book. Make sure all the conditions are logically linked perfectly. Once the Flow chart is ready you are welcome to read next point.

 Example:

 

 IMPLEMENTATION IN EXCEL

Microsoft Excel can be used as intelligent Data Manipulation with the support of powerful conditional formulas (If, And, Or, Nor,Nand etc.), Lookup functions (Vlookup, Hlookup etc.), Conditional Formatting, Pivot Tables etc. 

In this tutorial we are going to familiarize Pivot Table & Conditional Formatting. Implementation process contain following steps,

1. Make a Data Entry Table
2. Create Pivot Table.
3. Use Conditional Formatting & Column Filter.

Make a Data Entry Table

Make separate column for each blocks of Flow chart.

Of course you can ignore the meaningless columns.

 

 Now we are having a simple excel sheet.

 

 I have removed some data to show the realistic tracking.

 

Create Pivot Table.

Pivot Table is an auto categorizing tool in Microsoft Excel with a powerful user friendly customization controls. Using this tool we are able to convert any  passive tables to an well categorized auto corrective active table. 

  Insert > Pivot table.

  Select Table

  Try to select more rows.

Choose the Pivot Table placement option. 

Click the column titles which we chosen before. Now your Pivot table half way ready. 

Go to Design> Report Layout> Show in Tabular Column to make the Table more good looking

Off all Grand Totals.

Then OFF subtotals 

Pivot Table ready 

 Conditional formatting

 Select all to hide “(Blank)” texts.

 Home > Conditional Formatting > Text that Contain

 Here you can see after entering the “(Blank)” text, the cells automatically highlighted . Here I am using white text to remove the Blank texts.

 Now watch how the Cells contain Blank disappeared.

Below I want to swap the Project column to end.

 Just drag the Project bar to end in ‘rag fields between areas below’

Using IF condition to give some intelligence to your Excel Sheet.

 Add this extra Data formula in Pivot table.

 

Now the addition of new columns done.

Again I am going to Show you How to give Conditional Formatting to enhance the visual intelligence of the tracking.

Select the any row or values in a row.

Home> Conditional Formatting > New Rule

Click the selected option.

Select the cell which is the condition variable. Here I want highlight all the Projects which exceed a Total delay of More than 10 days. (G8 > 10 is the condition.).

Remove $ symbol from Row value. Play with Row value to apply the condition to more rows.

 

Still our Tracking is not ready. Final Process is Filter the Blank cells of Last Process. That is Cable Issue date column to be filtered. Only not received cells to be shown in the table. So we can easily understand the entire projects in pivot table if cable not received cases. And we can understand the delays made by each department. How to do this. See below.

Now our Tracking Table is Ready. You Can enter the new data in Data Entry sheet and whenever you need tracking just right click the Pivot table and refresh the table.

 

See the more applications using this method …..

 Overall Project Tracking-

My version of Material Tracking - 

Permit Tracking - 

Graphs (S-Curve)

1. CONCLUSION

Excel is powerful software. It can be modified into different tracking according to our wish. So that we can reduce human errors in great extent. Wishing you all a great success in your career.

 For any mistakes, please excuse me. You are welcome to highlight the mistakes.

Thanks

 

Sumith C Mohan

sumithcmohan@gmail.com

+974 70491827 (Doha – Qatar)