Status Column Tool

Formats a Column in a Table using a preset of Data Validation and matching Conditional Formatting.

It is available on Github at https://github.com/ceybers/excel-status-column.

What does it do?

Adds a set of Data Validation and Conditional Formatting to Column, based on a user-defined preset.

Why did you make this tool?

The pattern of having a column constrained to values such as "Yes, No, N/A" or "High, Medium, Low" is ubiquitous in office life.

Having a tool that can apply both Data Validation and Conditional Formatting from a predefined standard not only saves time by reducing the action to three clicks (Open Tool, Select Preset, Apply), it ensures that all the columns are formatted exactly the same, which means that once everyone has updated the spreadsheet, all the data should be the same, saving you time and effort in validating, cleansing and normalising your data.

Shortcomings

I haven't had a chance to work on this tool in a long time. I never finished the ability to allow the presets to be edited in the tool - you have to resort to editing the plain text file manually. I also never managed to find a good way of previewing all the colors at once. It seems the only usable way would be to open a new Excel window with a temporary workbook and show the previews in there.

Ideally I'd rewrite the entire tool from scratch so that it uses the same better principles I've since picked up for things like serialising the data, the user interface, and the MVVM pattern behind the scenes that ties everything together.

Fun Trivia

The original plan was to store the presets in CSS and the tool would translate the CSS into something that can be used to format Excel cells.