Chart for Library Student Occupency Chart

Unable to apply point no 3

I want to prepare & update chart automatically something as attached in below screenshot.

I think you take my words in wrong way, I want chart as attached in screenshot same is update automatically when data update

How about this:

1. Prepare Your Data
   • Ensure you have columns for seat number, status (occupied or vacant), shift type, and date (column J).
   • Convert your data into a structured table for easier updating.
2. Use Conditional Formatting for Colors
   • Apply Green for occupied seats and Red for vacant seats using conditional formatting based on occupancy status.
   • You can do this by setting up rules in Excel’s Conditional Formatting section.
3. Create a Scatter Plot for Visual Representation
   • Insert a scatter plot, using seat number as labels.
   • Adjust the point size dynamically based on shift type:
     • Large circles for Full shift.
     • Small circles for Morning or Evening shifts.
4. Automatically Update Status
   • Use an IF formula to check whether the current date has passed column J’s date:

=IF(TODAY()>J2, "Vacant", "Occupied")

• This will ensure that occupancy status updates dynamically.
1. Insert Data Labels
• Enable data labels to display the seat number inside the circles.