Microsoft Excel Tips and Tricks - Computergaga

Home / Archives for Formulas

Highlight Rows when Value Changes – Excel Conditional Formatting

In this blog post, we create a Conditional Formatting rule to automatically highlight rows in an Excel list at each value change.

I’ve got multiple guests from the same company attending an event and I want each company section to stand out. The default banded rows of Excel Tables does not help with reading my attendee tracker.

Default banded rows of Excel tables

For this example, I want to highlight groups of rows – each company in my list.

Let’s create a Conditional Formatting rule to automatically highlight each company section. The rule will format the entire row based on the company name changing from the previous row.

Automatic formatting of groups in Excel

This is much easier to read compared to the classic banded row formatting used by Excel tables.

So, how can we do this?

[Read more…] about Highlight Rows when Value Changes – Excel Conditional Formatting

TEXTSPLIT in Excel: 9 Practical Examples You Can Use Today

If you’ve ever extracted from messy text strings, you’ve probably spent far too much time wrestling with LEFT, MID, RIGHT, and FIND formulas.

TEXTSPLIT changes all of that.

Instead of performing formula gymnastics with the likes of MID and FIND, TEXTSPLIT can instantly convert a text string into a dynamic array that Excel can work with. Due to this, TEXTSPLIT has become one of the most useful text functions in modern Excel.

In this article, we’ll explore practical examples that show how TEXTSPLIT can simplify text manipulation and unlock more powerful dynamic array solutions.

[Read more…] about TEXTSPLIT in Excel: 9 Practical Examples You Can Use Today

Create Custom In-Cell Bar Charts in Excel

In Excel, it is possible to create a quick and effective visual that offers more flexibility than a traditional chart. With a clever combination of symbols, the REPT function, and a little formatting magic, you can build compact in-cell bar charts that are dynamic and informative.

In this article, we will create a bar chart inside a cell, colour it based on custom performance thresholds and add values as data labels.

Let’s create the following dynamic bar chart without relying on standard chart tools.

Excel progress bars created using the REPT function
[Read more…] about Create Custom In-Cell Bar Charts in Excel

Excel SWITCH Function – Nested IF Function Alternative

The Excel SWITCH function is a neat alternative to nested IF functions or the IFS function.

It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.

Watch the Video

SWITCH Function Syntax

The SWITCH function compares a value (known as the expression) against a list of values, and returns the result for the first matching value. If a match is not found, a default result can be returned.

=SWITCH(expression, value1, result1, [default_or_value2, result2])

Expression: is the value to be tested against the list of values. This is typically a reference to a cell value such as A2.

Value1: is the first value (SWITCH can evaluate up to 126 values) to be compared against the expression.

Result1: is the action to perform if its corresponding value is a match to the expression.

Default: This optional default value provides SWITCH with an else argument. This is the result to return if there is no match found.

Let’s see some SWITCH function examples.

[Read more…] about Excel SWITCH Function – Nested IF Function Alternative

Sum Formulas Only in Excel

A unique use case for summing values, could be to sum formulas only in Excel.

The SUM function will sum all numeric values in a given range, but you may have a range interspersed with other formulas that calculate subtotals or perform conditional calculations.

So, how do you only sum the cells that contain formulas. Let’s find out.

Watch the Video – Sum Formulas Only

[Read more…] about Sum Formulas Only in Excel