<p>Conditional formatting in Excel allows users to visually format cells based on specified conditions. While Microsoft Excel seamlessly integrates formatting with icons, providing enhanced and visually appealing spreadsheets, Google Sheets lacks this cool native feature.</p>
<p><img title="Conditional Formatting with Icons in MS Excel" src="/uploads/2023/11/conditional-formatting-icons-excel.png" alt="Conditional Formatting with Icons in MS Excel"></p>
<blockquote>
<p>Although we cannot create the icon in the same cell like Excel Sheet but we can achieve similar results with image formula in Google Sheet</p>
</blockquote>
<h2>How it Works</h2>
<p>The image formula helps to render an image form a url which plays the major role rendering the icon.</p>
<pre class="language-javascript"><code>=IF(logical_expression, value_if_true, value_if_false)
=IMAGE(url, [mode], [height], [width]) </code></pre>
<p>You can upload the image to any 3rd party server or cloud and just use the url in the formula</p>
<pre class="language-javascript"><code>=IF(A3&lt;50,IMAGE("your_icon_url",3),IMAGE("your_icon_url",3)) </code></pre>
<p>The above formula will render any one of the two images based on the condition A3&lt;50. You can even use nested IF when you have more than 2 conditions. Here is an example:&nbsp;</p>
<pre class="language-javascript"><code>=IF(D2="Yes",IMAGE("your_icon_url",3),IF(D2="No",IMAGE("your_icon_url",3),IMAGE("your_icon_url",3))) </code></pre>
<p>The above formula will show three different icons based on the cell value "Yes", "No" and "Maybe". Here is a <a href="https://docs.google.com/spreadsheets/d/1BdWhXhhh1GOUT2sE1t8TzDaEFpFXDlFtcwQX2Y8QX0s/edit#gid=1381015584" target="_blank" rel="noopener">Demo Spreadsheet</a> where you can see it all live.</p>
<h2>Change Icon Size</h2>
<p>The =IMAGE("your_icon_url", 2) formula accepts a 2nd parameter which sets the size mode.</p>
<table style="border-collapse: collapse; width: 100%; border-width: 1px; height: 180px;" border="1"><colgroup><col style="width: 49.9795%;"><col style="width: 49.9795%;"></colgroup>
<thead>
<tr style="height: 30px;">
<td style="height: 30px;">Options</td>
<td style="height: 30px;">Example</td>
</tr>
</thead>
<tbody>
<tr style="height: 30px;">
<td style="height: 30px;">Size to fit</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 1)</td>
</tr>
<tr style="height: 30px;">
<td style="height: 30px;">Stretch to fit</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 2)</td>
</tr>
<tr style="height: 30px;">
<td style="height: 30px;">Original size</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 3)</td>
</tr>
<tr style="height: 60px;">
<td style="height: 60px;">Custom size</td>
<td style="height: 60px;">=IMAGE("your_icon_url", 4, height(px), width(px))</td>
</tr>
</tbody>
</table>
<p><img title="Image Formula with Size Parameter" src="/uploads/2023/11/image-formula.png" alt="Image Formula with Size Parameter"></p>
<h2>Put the Icon and Value in the Same Cell</h2>
<p>In Google Sheet it is not possible to put the icon and value in same cell similar to Excel sheet, however there is a workaround. We can put the text and the icon side by side and have right the border same as the background.<br><img title="Same Cell Icons" src="/uploads/2023/11/same-cell-icons.png" alt="Same Cell Icons"></p>
<h2>Formatting with SPARKILINE</h2>
<p>Sparkline is a cute feature available in Google Sheets, designed to create tiny bar charts directly within cells. These tiny, in-cell charts provide a quick overview of data variations, making it easier to interpret large datasets at a glance.</p>
<pre class="language-javascript"><code>=SPARKLINE(E2,{"charttype","bar";"max",100})</code></pre>
<p><img title="Sparkline Chart" src="/uploads/2023/11/sparkline.png" alt="Sparkline Chart"></p>
<h3>SPARKLINE Syntax</h3>
<pre class="language-javascript"><code>=SPARKLINE(data, [options])</code></pre>
<ul>
<li>data - The range or array containing the data to plot.</li>
<li>options - [ OPTIONAL ] - A range or array of optional settings and associated values used to customize the chart.</li>
</ul>
<h3>Sample Usage</h3>
<pre class="language-javascript"><code>SPARKLINE(A1:F1)

SPARKLINE(A2:E2,{"charttype","bar";"max",40})

SPARKLINE(A2:E2,A4:B5)

SPARKLINE(A1:A5, {"charttype","column"; "axis", true; "axiscolor", "red"})</code></pre>

Apply conditional formatting with icon sets in Google Sheets, utilizing a variety of pre-designed icon sets tailored for specific scenarios. Use sparkline to render in-cell bar charts.

How to Apply Conditional Formatting with Icons in Google Sheets

<h1>About Chat Plus</h1>
<p>Chat Plus is a chrome extension developed by Tabgraf that enables additional features in Google Chat some of which are:-</p>
<ol>
<li>Audio message</li>
<li>Reminders</li>
<li>Bookmarking message</li>
<li>Encrypt and Decrypt message</li>
<li>Quoting a message</li>
<li>Code and Code blocks</li>
<li>Location sharing</li>
<li>Copy link of the message</li>
<li>And Counting ...</li>
</ol>
<p>&nbsp;</p>
<h1>Send Audio message in Google Chat</h1>
<p>To send audio message in Google Chat first you need to install <a title="Chat plus" href="https://chromewebstore.google.com/detail/njkkenehdklkfdkmonkagaicllmnfcda?hl=en" target="_blank" rel="noopener">Chat plus</a> from the Chrome webstore. After the extension has been successfully installed you will be finding a lot of additional features has been enabled in Google Chat. Among this is the Audio Messaging. Here is a screenshot attached to help you locate this feature.</p>
<p><img src="/uploads/2023/10/chat-plus-audio-recorder.png"></p>
<p>&nbsp;</p>
<p>Once you click on the mic, a popup with a recorder will open (for the first time users, you must login with your Google account to use Chat Plus). This recorder will record your audio message and send it to Google Chat as a link which you send it in the chat. An audio preview will also be injected with the link for a quick preview of the message.</p>
<p><img src="/uploads/2023/10/chat-plus-audio-screen.png"></p>
<p>&nbsp;</p>
<h1>How the Audio messaging works ?</h1>
<p>The moment the record button is clicked, a instance MediaRecorder is created, and event listner `dataavailable` is attched to it, which provides the audio that is being recorder in chunk and that is being collected in chunks array.&nbsp; Here is a reference of how the MediaRecorder works:</p>
<pre class="language-javascript"><code>navigator.mediaDevices.getUserMedia({ audio: true }).then((stream) =&gt; {
  const mediaRecorder = new MediaRecorder(stream);
  let chunks = [];

  mediaRecorder.addEventListener("dataavailable", (event) =&gt; {
    if (event.data.size &gt; 400) {
      if (!animateDiv.classList.contains("animate-recorder")) {
        animateDiv.classList.add("animate-recorder");
      }
    } else {
      animateDiv.classList.remove("animate-recorder");
    }
    chunks.push(event.data);
  });
});
</code></pre>
<p>&nbsp;</p>
<h1>How the audio chunks is converted back to audio&nbsp;?</h1>
<p>The chunks array that is generated during the recording is converted to Blob of type `mp3` which is used to create a audio url. This audio url is again utilised for creating a base64 which is sent to the backend to save a audio file.</p>
<pre class="language-javascript"><code>const audioBlob = new Blob(chunks, { type: "audio/mp3" });
const reader = new FileReader();
reader.readAsDataURL(audioBlob);
reader.onload = () =&gt; {
  const base64AudioMessage = reader.result.split(",")[1];
  console.log(base64AudioMessage);
};
</code></pre>
<p>In the backend a new instance of Buffer is created which converts the `base64AudioMessage` to buffer. This buffer is used to create an audio file with help of node js file system(fs).</p>
<pre class="language-javascript"><code>const buffer = Buffer.from(base64AudioMessage , "base64");

fs.writeFileSync(audioFilePath, buffer);
</code></pre>
<p>So , this how the Audio messaging feature works in Chat Plus.</p>

Google natively does not provide a way to send audio messages in the chat. But with Chat plus chrome extension we can effectively send Audio messages in Google Chat with a lot of other features.

MS Excel

Tech Tips, Tutorials & How-to Guides

POSTS

How to Apply Conditional Formatting with Icons in Google Sheets

How to send audio message in Google Chat?

BROWSE ALL TABGRAF TOPICS

Even Guinea Pig Likes
Fresh Carrots🥕

Quick Links

Curiosity

MS Excel

Tech Tips, Tutorials & How-to Guides

POSTS

How to Apply Conditional Formatting with Icons in Google Sheets

How to send audio message in Google Chat?

BROWSE ALL TABGRAF TOPICS

Even Guinea Pig LikesFresh Carrots🥕

Quick Links

Curiosity

Even Guinea Pig Likes
Fresh Carrots🥕