Visualizing SharePoint data dynamically and intuitively is pivotal in making informed decisions. One effective technique to achieve this is through the utilization of the Calculated Column for creating color gradients based on data values. This enables you to add visual cues like color coding and Key Performance Indicators (KPIs) to your SharePoint lists, thus enhancing data comprehension. This can also be done for calendars. Here we present an updated and improved approach on implementing calculated color gradients on SharePoint.
Creating a Green/Yellow/Red Gradient
The Green/Yellow/Red gradient is a popular color spectrum for dashboards, often used to indicate varying stages of a project. While these three colors denote distinct states, to visualize progress or measurements on a scale, a broader color palette is required. For instance, the color might represent the degree of completion (in %) in a tasks list. We strive to generate a calculated column in SharePoint that determines color based on the [%Completed] value.
Method 1: Nested IFs
The nested IFs method is a primary approach wherein conditions are established to select color by evaluating the [%Completed] value. For instance, if [%Completed] is greater than 90, pick green. Although this straightforward method works, it’s not the most efficient or dynamic approach.
Method 2: CHOOSE function
A more sophisticated option is SharePoint’s CHOOSE function, which naturally handles multiple options, thus offering greater elegance than nested IFs.
Method 3: Pure Calculation
For an approach that allows maximum customization, we can utilize pure calculation with RGB (Red Green Blue) codes, which significantly expands the color palette. Each color can be identified by its RGB code like red: rgb(255,0,0), green: rgb(0,255,0), yellow: rgb(255,255,0), white: rgb(255,255,255).
Thus, a red/yellow/green gradient can be created by interpolating between these RGB values. The following formula calculates the RGB value based on the [%Completed] value in a calculated column:
=”rgb(“&255&”,”&INT(MIN([% Complete]510,255))&”,”&INT(MAX(255-[% Complete]510,0))&”)”
This generates a smooth gradient from red to green via yellow, dependent on the [%Completed] value.
To achieve visual effects like those in the screenshot, we use the HTML Calculated Column method, as evidenced in the following formula:
json "elmType": "div", "txtContent": "@currentField", "style": { "color": "=if([$Status] <= 30, 'red', if([$Status] <= 70, 'yellow', 'green'))", "font-size": "14px", "padding": "5px" }
This generates a colored progress bar correlated directly with the [%Complete] value.
Please note that these calculation methods work in SharePoint 2010, SharePoint 2013, SharePoint 2016, SharePoint 2019, and SharePoint Online.
Conclusion
Exploiting the Calculated Column to generate color gradients is not only effective for data visualization but also makes SharePoint lists visually appealing. With the right blend of creativity and technical knowledge, you can utilize the RGB coding technique to represent data in a more colorful and understandable way, thus enhancing your SharePoint user experience significantly.
Leave a Reply