Advanced Excel Dashboard Redesign & Automation

Excel Dashboard Enhancement & Automation

Overview

I have an existing Excel workbook used for monitoring and reporting ESP well operations in (X) area. The workbook already contains multiple sheets, formulas, logs, and operational data. I am looking for an Excel expert to redesign, automate, and improve the workbook while maintaining all existing functionality and calculations.

This is not a simple formatting task. The workbook contains many formulas, linked sheets, operational KPIs, and engineering data. I need someone with strong Excel expertise (advanced formulas, Power Query, VBA if needed, dashboards, automation, and workbook architecture).

Experience using AI tools for data visualization, dashboard design, and workflow optimization is highly preferred.

Main Objective

Create a professional, automated, and user-friendly workbook where data is entered only once and automatically reflected across all related sheets.

The first sheet (Overview Dashboard) should become a professional management dashboard with a modern layout, status summaries, charts, and visual indicators.

⸻

Key Requirements

1. Data Source Structure

The workbook should have a clear data hierarchy:

* Well Status Sheet = Primary Data Source
* Log Sheets = Operational Update Source
* All other sheets should automatically update based on these sources

No duplicate manual updates should be required.

⸻

2. Well Status Sheet

* Add dropdown selections for well status.
* User should easily select status from a dropdown menu.
* Status examples:
* Running
* Shutdown
* Shutdown for PMT
* Under Troubleshooting
* Failed
* Waiting for Commissioning

Any change here must automatically update:

* Overview Dashboard
* ESB Status Sheet
* ARL / MTBF calculations

⸻

3. ESB Status Sheet

Create automatic status counting and color-coded summaries.

Any status change in Well Status should immediately update:

* Running Count
* Shutdown Count
* Failed Count
* Troubleshooting Count
* Waiting for Commissioning Count

⸻

4. Overview Dashboard

This is the most important sheet.

Requirements:

* Modern executive dashboard design
* Clean color coding
* Operational summary
* Well status summary
* Charts and trends
* Daily updated indicators
* Attractive layout suitable for management presentations

I want the freelancer to use his experience and AI-assisted design skills to significantly improve the appearance while keeping all operational functionality.

⸻

5. X Dashboard

The current X Dashboard contains critical operational data.

Requirements:

* Preserve all existing data and calculations.
* Rebuild the dashboard using the layout and format of the reference sheet:
“Do Same As This Format”
* Keep all calculations accurate.
* Use supporting sheets (Job Sufficient & Options) if needed for formulas and calculations.
* Hide support sheets after implementation.

⸻

6. Logs Integration

All log sheets should be linked to:

* Overview Dashboard
* Well Status
* ESB Status

Example:

* O = Running
* SD = Shutdown

Any update entered in the log sheets should automatically reflect throughout the workbook.

⸻

7. PM Data Sheet Enhancement

Redesign the pm Data sheet while keeping all existing data.

Add KPI section showing:

* Total pm
* pm Completed
* Hot pm Completed
* Cold pm Completed
* Pending pm
* Overdue pm

Requirements:

* Automatic calculations
* Automatic sorting by Date and Well Number
* Count each well only once per year for completion statistics
* Maintain existing data structure

⸻

8. Sheet Cleanup

Remove unnecessary sheets where appropriate.

Potential sheets to remove:

* Downhole Equipment
* pm Summary

Other sheets may be hidden if required but should not break any formulas.

⸻

9. ARL / MTBF Automation

Ensure ARL and MTBF calculations are automatically linked to operational and non-operational well status.

Hide backend calculation sheets while keeping user-facing sheets visible.

⸻

10. Automation & Usability

* Automatic date update when workbook opens
* Dynamic tables
* Automatic refresh functionality
* Error-resistant formulas
* Improved performance and workbook structure

⸻

Expected Deliverables

* Clean and professional workbook
* Fully linked and automated sheets
* Improved dashboard design
* Preserved engineering calculations
* Documentation explaining the workbook logic
* Easy future maintenance

Required Skills

* Advanced Microsoft Excel
* Complex formulas
* Dashboard design
* Data modeling
* Power Query
* VBA (if needed)
* Workbook optimization
* AI-assisted workflow improvement

Please review the workbook carefully before starting because many sheets are interconnected and accuracy is critical.