The post Excel Conditional Formatting Highlight Matches in List appeared first on My Online Training Hub.

]]>Let’s look at an example, below is our table of data:

And we want to highlight the rows that contain any of the categories in this Table:

Like so:

Note: My list is in an Excel Table in cells I7:I9 and I’ve given it the Named Range: List. We’ll be using this name in the Conditional Formatting formula.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

**Step 1:** To set up the Conditional Formatting we first select the Table cells we want to highlight, in my case A5:G47.

**Step 2:** Home tab > Conditional Formatting > New Rule > select ‘Use a formula to determine which cells to format’ from the Rule Type list.

**Step 3:** Insert the formula

=MATCH($D5,List,0)

In the dialog box as shown below:

Now if you remember my post from a couple of weeks ago with a similar example you’ll recall that I said Conditional Formatting formulas must always evaluate to TRUE or FALSE, or their numeric equivalents of 1 and 0.

And if you’re familiar with the MATCH Function you’ll know that it returns the position of a value in a list, and in this example that could be anything between 1 and 3. So you might be wondering how that MATCH formula works in Conditional Formatting.

Taking the formula above, it evaluates like so:

=MATCH($D5, List, 0)

=MATCH("Shirts", {"Shorts";"Shirts";"Skirts"}, 0)

=2

i.e. Shirts is the second item in the ‘List’.

So, the formula isn’t returning TRUE or FALSE, or their numeric equivalent of 1 and 0, yet the format is still applied. What?!

I discovered through experimenting that the conditional format will be applied as long as any (positive or negative) value other than zero is returned by the formula.

That means we could also use this formula to achieve the same results:

=COUNTIF(List, $D5)

Click here for a more thorough understanding of how Conditional Formatting formulas work.

**Step 4:** Click the ‘Format’ button in the dialog box above and set your format:

A big thank you to Cliff Beacham for sharing his 'Excel Conditional Formatting Highlight Matches in List' tip and for teaching me something new.

Cliff has an Excel book coming out soon. Keep your eye out on Amazon for it.

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

The post Excel Conditional Formatting Highlight Matches in List appeared first on My Online Training Hub.

]]>The post Excel Custom Number Format Guide appeared first on My Online Training Hub.

]]>To be clear, number formatting in Excel is used to specify how a value should appear in a cell or chart, but it doesn’t alter the underlying value that you can see in the formula bar. Unless of course you format a number as text, in which case it can no longer be treated as a number in math formulas.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

- Excel Custom Number Format Guide
- Download the workbook
- Applying Custom Number Formats
- Number Format Structure
- Hiding All Values
- Hiding Zero Values
- Custom Number Format Characters
- Literal Characters
- Special Characters
- Digit Placeholders
- Thousand Separators and Scaling
- Currency Formats and Parentheses for Negative Values
- Colors
- Date Characters
- Time Formats
- Fractions
- Percentages
- Scientific Notation
- Text/Labels
- Color Code based on Values/Conditions
- Repeating Leading and Trailing Characters
- Padding with Zeros
- Using Symbols in Formats

To apply a custom number format first select the cell or cells you want to format, then CTRL+1 to open the Format Cells Dialog box, or go to the Home tab and click on the arrow in the Number group:

This opens the Format Cells dialog box in the Number tab (below), where you can choose ‘Custom’ from the Category list and insert your format in the ‘Type’ field:

Let’s start with the anatomy of a number format.

A number format is made up of 4 components, with each component separated by a semi-colon:

Component1;Component2;Component3;Component4

- Component1 formats the POSITIVE value
- Component2 formats the NEGATIVE value
- Component3 formats ZERO values
- Component4 formats TEXT

In other words; POSITIVE ; NEGATIVE ; ZERO ; TEXT

*Note: spaces around semi-colons above are added for clarity but are not required in the format.*

You may be familiar with number formats you find in the Format Cells dialog box, like this:

In the above example only the positive and negative formats are specified, in which case the zero format will be the same as the positive format and text will be formatted as it was entered.

In fact when we omit components Excel behaves as follows:

- When just
**one component**is provided with no semi-colons in the code, Excel will check to see if the format is one of the default ‘Number’ formats and revert to that format. Otherwise, if the format is unique then all numbers will be formatted according to the format provided. - When the first
**two components**are provided Excel will use the same format for zero, and text will appear as entered. - When the first
**three components**are provided Excel will use the formats specified, and text will appear as entered. - If any components are skipped or left blank e.g. in this format the negative component has been skipped, #,##0;;0 and so the negative value will not display as demonstrated below:

When all components are omitted and just the 3 semi-colons specified, Excel will not display anything in the cell. It’s a clever way to hide data in cells and charts.

Another common question is ‘how do I hide zero values from displaying in my chart labels’. The answer is simply to use the technique in point 4 above and omit the format for the zero component. See the example below:

You may have noticed custom number formats that contain unusual characters like this:

These characters are used as codes to tell Excel how to format the value. Let’s look at them in turn.

**Literal Characters** – these appear as entered in the custom number format:

**Special Characters** – define how other characters are treated

**Digit Placeholders** – used to control how numbers that contain decimal places are displayed:

The comma is used to display the thousand separators in a number when it is enclosed in # signs or by zeros, and when it follows a digit placeholder it scales a number by 1000.

**Tip 1:** We can scale a value to millions by using two commas, as you can see in the example in the last column above. And for billions we add 3 commas.

**Tip 2:** When appending ‘K’ for thousands we simply type it in the format, but ‘M’ for millions must be entered with a preceding \ as the example above, or inside double quotes. This is because ‘M’ is already reserved as a character for the date formats. More on date formats soon.

**Tip 1: **In the example in the last column above I’ve used the underscore and right parenthesis to align the decimal places. You can see them in red in the number format here: $#,##0.0,K_);($#,##0.0,K)

Remember the underscore is a special character that adds a space according to the width of the following character, in this example the right parenthesis.

**Tip 2:** if you don’t have a particular currency key on your keyboard, you can enter it into the Custom Number Format dialog box using the ANSI code. To enter an ANSI code, hold the ALT key and type in the 4 digit number. Here are some example ANSI codes that might be useful:

Alternatively, you can use the Symbol tool (Home or Insert tab) to find the character you want and then insert it into a cell. From the cell you can copy it to your clipboard and paste it into the Custom Number Format dialog box.

**Tip**: You can also insert symbols like up or down triangles, just make sure they’re the Arial Geometric Shapes (subset) variety and not Wingdings:

For example, this format displays positive percentages with an upward facing triangle and negative values with a downward facing triangle.

▼0.0%;▲0.0%

Symbols can be quite effective when used in reports or charts like the one below:

We can specify a color for a format, for example you might like to format negative values in red. Color formatting is done in one of two ways:

- We can use the color name surrounded by square brackets, like so:

$#,##0.0,K_);[red]($#,##0.0,K)

Which results in negative values like this: ($1.3K)

However, with color names we’re limited to these:

[black]

[green]

[white]

[blue]

[magenta]

[yellow]

[cyan]

[red]

- Alternatively we can specify a color index number e.g.:

$#,##0.0,K_);[color 3]($#,##0.0,K)

Using the color number opens up a total of 56 different colors, although 5 of them are duplicates, as you can see by the hex codes in red below:

**Tip:** Apply color formats to chart axes or labels. Note: if you have labels then you don’t also need the vertical axis or gridlines, unlike my example below:

We can combine the month, day and year characters together in various configurations to get the desired format. Here are a few examples:

Click here for more on how dates and time are handled in Excel.

Just like the date characters, we can combine the time characters to get the format we’re after. For example:

And when we’re adding time that exceeds 24 hours, or 60 minutes, or 60 seconds we can display the elapsed time like so:

Let’s go back to school and recap some of the different types of fractions:

- Proper – where the absolute value of the fraction is less than 1 e.g. 1/2
- Improper – where the absolute value of the fraction is greater than or equal to 1 e.g. 4/3
- Mixed Fractions – are another way to write improper fractions where the whole is written separate to the fraction, for example 4/3 would be written 1 1/3

Use the ? symbol to specify the varying number of digits to display.

It’s important to remember that the underlying value in the cell, as displayed in the ‘Value’ column above, is what is used in your formulas that reference those cells. The Formatted result is simply how it appears, but you must be careful if you are using those formatted results in calculations that don’t reference the cell directly. For example if you were to enter those formatted values into a calculator you’d get a different result to a formula that referenced that cell.

Use the % sign to display numbers as a percentage of 100. Remember, a zero in the custom number format displays both significant and insignificant digits, and the # will only display significant digits as per the examples below:

Scientific Notation allows us to express very large numbers in a decimal form of shorthand. It’s commonly used by scientists and mathematicians. Let’s use 7,830,000,000 as our example value. I think you’ll agree it’s a pretty big number.

In scientific notation we can express it as 7.83 x 10^{9}

We raise 10 to the power of 9 because 9 decimal places have been removed from the original number to be left with 7.83. And when we multiply 7.83 by 10^{9} we get our original value of 7,830,000,000. Ok, math lesson over.

In Excel (and some calculators) we can’t use ‘x’ in a number, nor can we insert superscript. Instead we use the upper (or lower case) E+ notation like so:

7.83E+09 or 7.83e+09

To apply this formatting the custom number format is 0.00E+00 or 0.##E+00.

Remember we can use 0 to display both significant and insignificant digits, or if we only want to display significant digits we use #.

Here are some examples:

Did you notice the precision in the ‘Formatted Result’ of the 3rd example above? See how it appears to round the value to one decimal place and therefore omits the ‘3’ in 7.83. Be wary of this if you’re likely to refer to the face value of these cells, as opposed to the actual underlying value.

Remember to use the 0 digit place holder to show both significant and insignificant digits, and use the # to only display significant digits.

Engineering notation is a version of scientific notation in which the exponent of ten must be divisible by three. The format contains 3 # before the decimal place and 3 zeros after the decimal place to force the result to return an exponent that is divisible by 3.

We can append or prefix our values with text while still maintaining the underlying number for use in formulas. This can be handy when formatting values like kilometres or days/hours/minutes, Over/Under etc.

To display both text and numbers in a cell simply insert your text in double quotes, or if it’s only a single character then precede it with a backslash (\).

*Don’t forget, Literal Characters are displayed without the need for quotation marks.

Here are some examples you might find useful:

The last example uses the @ symbol for Text. In other words, the cell is formatted as text, which is also why it is left aligned, but the custom cell format displays the # tag at the front of the text without the need to actually type it in the cell.

Remember that by default the custom number format is broken into the following components:

Positive ; Negative ; Zero ; Text

However, we can override the default components with up to two conditions and add color coding based on criteria we set.

For example, let’s say we have a threshold of 500 units per day. Anything below this should be red and anything 500 or above should be formatted green. We can use the format:

[red] [<500] #,##0;[color 10] [>=500] #,##0

Which yields the following results:

**Tip:** once a condition is met Excel doesn’t test the other conditions so it’s important you get the order of tests correct so it doesn’t stop at the first criteria. Just like a nested IF formula.

Of course we could also write this format like so:

[color 10] [>=500] #,##0; [red] [<500] #,##0

What if we want 3 conditions? Well, we can’t insert 3 conditions as such, but we can insert 2 and then apply a font color to the cells, which will be picked up for all remaining values. This works because custom cell formats override the font color.

In the example below values less than 500 are red, values less than 1000 are orange/amber and values 1000 and over, for which there is no criteria, pick up the cell font color, which is green:

We can fill a cell containing text values with a specific character using the asterisk symbol. For example:

Remember that zero is a digit placeholder which displays both significant and insignificant digits, so we can exploit this to force Excel to display an insignificant zero at the front of a value, or to apply spacing between digits.

It’s handy for phone number formats, bar codes, product codes etc. Here are a few examples:

- Custom Number formats are saved in the workbook in which they’re created and are not available in any other workbooks.
- Custom Number formats
__do not__change the underlying value. This is important if you want to reference those cells in formulas. - Formats can appear to round values which will not be reflected in the results of formulas that reference those cells
- Regional Settings – this post assumes your region uses the full stop/period character for a decimal place and a comma for the thousand separators. If your region differs, please amend accordingly.

I’d like to recognise the following resources which I learnt from in the writing of this guide:

- Microsoft Custom Number Formatting
- Jon von der Heyden’s Comprehensive Guide to Number Formats in Excel
- David McRitchie’s Color Index
- Wikipedia – Engineering Notation

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

The post Excel Custom Number Format Guide appeared first on My Online Training Hub.

]]>The post Excel Conditional Formatting to Highlight Matches appeared first on My Online Training Hub.

]]>Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Before we can set up the Conditional Formatting we need to set up the data validation list. So, over in column K I have the list of Salesperson’s names.

I’ve also added an item to the list for ‘No Highlighting’. Since there is no Salesperson called ‘No Highlighting’ it simply won’t display and conditional formatting if you select it.

In cell C4 I’ve got my Data Validation list which references the Salesperson’s names in cells K7:K18:

If you don’t know how to insert a Data Validation, or Drop Down list as they’re sometimes known, then click this link to read the tutorial on Data Validation: http://www.myonlinetraininghub.com/excel-drop-down-lists

Now all we need to do is set up the Conditional Formatting to highlight rows that match the salesperson selected in the Data Validation list.

**Step 1:** Select all of the cells you want the Conditional Formatting to apply to. In my case it’s cells A7:G49.

**Step 2:** Home tab > Conditional Formatting > New Rule > select ‘Use a formula to determine which cells to format’ from the Rule Type list.

**Step 3:** Insert the formula =$C$4=$C7:

**Note:** The absolute referencing in this formula is the key to success. Get this wrong and you’ll be scratching your head trying to understand what is going on.

Let me explain:

Conditional Formatting formulas must evaluate to TRUE or FALSE. When the formula evaluates to TRUE the format is applied, and when it evaluates to FALSE the format isn’t applied.

$C$4: We absolute both the column and row reference here because this is the cell containing the Salesperson’s name selected in the Data Validation list and we want that cell tested for every row.

$C7: We reference the first cell in column C of the table, and we only absolute the column reference here because we want the Data Validation formula to move down through each row in column C to check if the salesperson matches the salesperson selected in the Data Validation list in cell C4.

If that’s hurting your head then I recommend you take a few minutes to read my tutorial on how Conditional Formatting formulas work. I think the images in that post will help you visualise what Conditional Formatting is doing in the background. Once you get this there’ll be no stopping you.

**Step 4:** Click the ‘Format’ button in the dialog box above and set your format:

Job done! The hardest part is getting your head around how to construct the formula.

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

The post Excel Conditional Formatting to Highlight Matches appeared first on My Online Training Hub.

]]>The post Return An Array From A UDF appeared first on My Online Training Hub.

]]>I'm going to modify that function so it becomes an array function, or an array formula as they are also known. Specifically it will become a multi-cell array function as it will return multiple values into multiple cells.

An array is just a list of values, e.g. 1, 2, 3, 4, 5 is an array with 5 values, or *elements*

That was an example of a one dimensional array. Think of one dimension representing values along a straight line, like the x-axis on a chart.

A two dimensional array holds values that can be used to represent something that requires two things to identify it, e.g. the x and y co-ordinates on a chart, the squares on a chess board, or even the cells on your worksheet (A1, etc).

Three dimensional arrays contain three lists of values used to identify something (the xyz co-ordinates) perhaps the latitude, longitude and height above sea level of a point on the Earth's surface.

You can have more dimensions of course if you need them, but for my example we're sticking with just one.

In order to process an unknown number of values, we need to work out how many values are passed into our function and then allocate the appropriate amount of storage in an array.

Initially we allocate an unknown amount of storage space for the array named *Result()*:

Dim Result() As Variant

Once the function is called we can work out how many cells are in the range we're passing into the function. We can do this by using Application.Caller which gives us a Range object, as long as our function is called from a cell. This range is the range of cells passed into the function.

We can work out how many cells in the range and whether we're passing a row or a column into the function

With Application.Caller CallerRows = .Rows.Count CallerCols = .Columns.Count End With

If CallerRows > 1 then a column is being passed into the function.

If CallerCols > 1 then a row is being passed into the function.

The number of cells in the range is CallerRows * CallerCols, so we redimension the array to hold a value for each of those cells.

'ReDimension the array to fit all the elements (cells) ReDim Result(1 To CallerRows * CallerCols)

If we pass in a range containing 6 cells, then our result will contain 6 values. Remember that this function is only dealing with one dimensional arrays, that is, values either in a row or a column.

If we pass in a range containing 10 cells, but allocate less than 10 cells for the result, we'll get #VALUE! errors.

If we pass in 6 cells, and allocate more than 6 cells for the result, cells 7 onwards will be filled with 0.

How you choose to deal with this is up to. You can write your own error handling, or just let Excel deal with it.

This is an array formula, so when you enter it you must use CTRL+SHIFT+ENTER. That is, hold down CTRL and SHIFT, then press ENTER.

If you know the number of elements in your array will be fixed you can specify this and not worry about working it out when the function is called, For example, here's a 10 element array:

Dim myResult(1 To 10) As Variant

But passing in a range with more or less than 10 elements could easily break your function.

By default a function will return an array as a row. If you want the results to go into a column, you need to transpose the array

'Transpose the result if returning to a column If CallerRows > 1 Then GetColor = Application.Transpose(Result) Else GetColor = Result End If

Here's a link to download a workbook with sample code and examples.

If you liked this please click the buttons below to share.

The post Return An Array From A UDF appeared first on My Online Training Hub.

]]>The post Return a Range from a UDF appeared first on My Online Training Hub.

]]>In this post I'm going to rewrite that code so that we can return a range from a UDF. This range contains all the cells that match the reference color, and you can then use this range in other functions and calculations.

I had already planned to do this, but following a comment on the last post, Catalin (our support guru) posted his code which does the same job, so some of the credit must go to him too.

The UDF is called *FindColors* and is really a pretty straightforward few lines of VBA:

' ' Written by Philip Treacy ' myonlinetraininghub.com/return-a-range-from-a-udf ' Function FindColors(InputRange As Range, ReferenceCell As Range) As Range Dim ReferenceColor As Long Dim Cell As Range, Result As Range ReferenceColor = ReferenceCell.Interior.Color For Each Cell In InputRange If Cell.Interior.Color = ReferenceColor Then If Result Is Nothing Then Set Result = Cell Else Set Result = Union(Result, Cell) End If End If Next Cell Set FindColors = Result End Function

All we need to do is check each cell in the *InputRange* sent to the function, to see if that cell has the same background color as our *ReferenceCell*.

A regular old *For ... Next* loop does this job for us.

If we find cells that match the color of our *ReferenceCell*, we use the UNION operator to join these separate cells together into a single range.

Set Result = Union(Result, Cell)

The last thing to do is return the result of the function, which is the range of colored cells matching our *ReferenceCell*.

But, because we are returning a range object we must **Set** the function result like so:

Set FindColors = Result

If you want to find the SUM of all cells colored the same as B9, and where ColoredCells is a named range, you'd enter this in a cell:

=SUM(FindColors(ColoredCells,B9))

There are several other examples in the workbook you can download :

If you change the background color of the cell using the Ribbon (or a couple of other ways), the function output is not recalculated. That's just the way Excel works and I wrote about this in a previous post - check the section headed **Recalculation**.

This was in the previous workbook too, but I didn't mention it.

Use this function to get the hex value of a cell's background color. Or to check that certain cells all have the same color.

To use it just call the function from a cell:

=GetColor(B9)

Creating a Reference to PERSONAL.XLSB

Creating an Excel Add-In For UDF's

Count, Sum and Average Colored Cells

If you liked this please click the buttons below to share.

The post Return a Range from a UDF appeared first on My Online Training Hub.

]]>The post Locating Values in Large Excel Tables appeared first on My Online Training Hub.

]]>The previous post on this topic included a method for finding the cell address for the maximum or minimum number in a table.

Thanks for the correspondence -- and especially that from Hervé, Saskia and Trevor for the questions about what happens if there are multiple maximum or minimum values.

The previous method sometimes failed where there were multiple maximum or minimum values.

This version shows a different approach that works reliably under that situation too.

**The ‘index table’ method**

Instead of setting up the reference row and column against the table as shown in the previous model (shown in light green here):

We use a method that works with an ‘index table’ related to the table we are investigating (shown in blue below). And we set up the ‘index table’ (shown in green below) to have one row for each cell in the blue table.

In the index table:

Row 1, Col 1 refers to cell January, Wk1 in the main table

Row 1, Col 2 refers to cell January, Wk2 in the main table

Row 2, Col 3 refers to cell February, Wk3 in the main table

etc.

It may take a few moments to set up the index table, since it needs to have one row for every cell in the table we are investigating (and could be very long if we have a large number of rows and columns in the table we are investigating).

The aim is to get an entry in the index table for each cell in the original table. In the index table column headed ‘Match’, we do a computation on whether the cell in the original table matches the Maximum (or Minimum if selected).

The formula for each cell in the Match column is:

=IF(INDEX(MyTable,[@Row],[@Col]+1)=Selected,TRUE,"")

This may look a bit complex, but it’s not too difficult if dissected, and it says in English:

Go to the blue table (MyTable). Using the INDEX function, go to the cell given by the Row and Column numbers in the green table.

*But wait a minute . . . . how does the ‘+1’ come into the formula?*

The first column of MyTable contains the month names, and since we are only interested in the numbers, we add one to the column so we can skip over the month names. So that means we are considering the Wk1 column to be the first column, the Wk2 column to be the second column, whereas in the Excel table structure, the Month is considered to be the first column and Wk1 is the second column etc.

For each cell in the original table, the index table entry is TRUE if the cell matches the Maximum (or Minimum if selected) and blank if there is no match.

This leaves us with a TRUE value in the index table for every match. It’s easy to see and verify, too.

**Getting the match information from the index table**

Say this is our result after refreshing the worksheet:

To find the number of matches, we count the number of times the value TRUE appears in the index table column ‘Match’:

=COUNTIF(IndexTable[Match],TRUE)

To find the table position of the first match, the row of the first match in the original table is given by the OFFSET function:

=OFFSET(IndexTable[[#Headers],[Row]],MATCH(TRUE,IndexTable[Match],0),0)

i.e. find the first value of TRUE down the index table (the MATCH function will provide the number of entries down the index table where the first match is located), then use the OFFSET function to count down that same number of rows from the top of the **Row** column in the index table.

Similarly to finding the row of the first match, the column of the first match in the original table is given by the OFFSET function:

=OFFSET(IndexTable[[#Headers],[Col]],MATCH(TRUE,IndexTable[Match],0),0)

i.e. find the first value of TRUE down the index table, then use the OFFSET function to count down that same number of rows from the top of the **Col** column in the index table.

Once the row and column of the first match are found, OFFSET can be used again to locate the first matched call in the original table, and to work out its cell address:

=CELL("address",OFFSET(MyTable[[#Headers],[Month]],$L$12,$L$13))

**Some neat conditional formatting**

Mynda recently discussed using logical functions to drive conditional formatting, so in this example, I’ve extended that to differentiate between maximum values being found or minimum values being found.

The cell used to denote Maximum or Minimum has the range name ‘SelectType’

So we can set conditional formatting in the original table to be driven by two logical conditions:

If SelectType is set to Minimum, then paint the minimum cells red:

If SelectType is set to Maximum, then paint the maximum cells green:

So, we’ve covered a more reliable way to locate the row number, column number and cell address largest and smallest numbers – plus some more effective conditional formatting for easily highlighting the locations of those numbers.

There’s a whole lot more on how to use these conditional formatting and formula techniques in the Excel for Customer Service Professionals course.

The post Locating Values in Large Excel Tables appeared first on My Online Training Hub.

]]>The post Finding a Needle in an Excel Haystack appeared first on My Online Training Hub.

]]>When dealing with large tables containing ‘buckets’ of numbers, we often need to find the location of the largest number, the smallest number, etc.

There are a couple of techniques in Excel for doing this:

__By eye__: using conditional formatting etc.

__By formula__: using some relatively simple formulas to find the row, the column, and the exact cell address of the largest/smallest numbers.

We’ll be looking at a small table for the examples, but these techniques work equally well for hundreds of rows and columns.

Note: we’re using Excel table names and range names throughout – it makes the refences so much simpler, and you can follow the details in the example workbook.

This method sets up a simple visual method for identifying large and small numbers by applying ‘Heat Map’ formatting to the data:

Select the data table you want to format:

From the Conditional Formatting menu on the Home tab, choose Color Scales:

And you will have a clearly laid out ‘heat map’ with the largest numbers in green, and the smallest in red. The colours get bolder as you approach the ends of the range, with the middle numbers shaded in white.

This is a useful technique for looking through the data, but doesn’t give you the ability to work directly with the largest or smallest numbers.

Firstly, outside the table, identify the maximum and minimum numbers with formulas:

The cell holding the maximum number is given the range name Cmax, and the cell with the smallest number is given the range name Cmin.

The you can set up two simple conditional formats on your data table

From the Conditional Formatting menu on the Home tab, select New Rule:

and then ‘Use a formula . . . ‘

The formula to use looks like this (and you could easily modify it to your own requirements):

i.e. the maximum number will always be rendered with a green background, and the minimum will be rendered with a red background.

Again, this is a useful technique for looking through the data and directly identifying the largest and smallest, but doesn’t give you the ability to work directly with those numbers.

This method uses the MATCH formula to directly address the largest and smallest numbers in the table, and the CELL formula to identify the exact cell address.

Because the method is identical for the largest and smallest, we have set up a drop-down control, using Data Validation to run the method for either the largest or the smallest – you get two methods of analysis for the price of one . . .

In K7, we can select ‘Maximum’ or ‘Minimum’ by using Data Validation, and the formula in L7 is:

=IF(K7="Maximum", MAX(MyTable),MIN(MyTable))

This cell is then given a range name of ‘Selected’ so that the formulas can easily refer to it.

Now, the main table in which we’re searching for data needs to have alongside it a reference row and a reference column (shown in green here):

The reference column at right, which has been given the range name ‘RowMatch’, shows the value X where the highest number is located by the formula:

=IF(ISNUMBER(MATCH(Selected,C5:G5,0)),"X","")

The reference row below, which has been given the range name ‘ColMatch’, shows the value X where the highest number is located by the formula:

=IF(ISNUMBER(MATCH(Selected,MyTable[Wk1],0)),"X","")

These values of X are then matched to the table to provide the row, column and full address of the Selected cell (i.e. the maximum or the minimum or the lowest).

The row of the Selected cell is given by =MATCH("X",RowMatch,0)

The column of the Selected cell is given by =MATCH("X",ColMatch,0)

And the full address of the Selected cell is given by:

=CELL("address",INDEX(MyTable,MATCH("X",RowMatch,0),MATCH("X",ColMatch,0)))

And of course conditional formatting is used to highlight the Selected cell, using the conditional formatting rule:

This is the best technique when you need to locate the specific row, column and cell address of the largest and/or smallest number.

It then gives you the data for extracting, comparing and further processing that data.

So we’ve covered three ways in which you can locate the largest and smallest numbers – and some neat formulas based on the MATCH function for extracting the locations of those numbers.

There’s a whole lot more on how to use these conditional formatting and formula techniques in the Excel for Customer Service Professionals course.

The post Finding a Needle in an Excel Haystack appeared first on My Online Training Hub.

]]>The post Toggle Excel Conditional Formatting On and Off appeared first on My Online Training Hub.

]]>Unfortunately Excel Tables aren’t efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the click of a button like this:

First of all we need a check box or radio button form control. You’ll find the form controls on the Developer tab > Insert:

*Note:** if you don’t see the developer tab **click here for instructions on how to enable the Developer Tab.*

Draw the check box form control onto your worksheet.

To edit the default text hold CTRL and left-click the form control; this will display the pull handles to adjust the size, and you can click inside the box to edit the text.

It is a floating object so you can move it around by left clicking and dragging while the pull handles are visible.

Set the Cell Link: Right-click > Format control

Choose a cell to house the status of the check box. Mine is in cell I3:

When the check box is checked the cell link, cell I3, will contain TRUE, and when it’s unchecked it contains FALSE. We use this in our Conditional Formatting formula.

To set the conditional formatting we first select the range of cells we want to apply the formatting to. In my case it’s A6:G48.

**Tip:** I don’t recommend you format the whole row/column as this will just bloat your file. Better to only format the rows and columns that contain data.

Then on the Home tab > Conditional Formatting > New Rule…:

In the Edit Formatting Rule dialog box (shown below), choose ‘Use a formula to determine which cells to format’:

And in the formula field we use this formula:

=AND(MOD(SUBTOTAL(3,$A$5:$A5),2),$I$3=TRUE)

In English the formula reads:

Count the number of visible rows in column A (that’s the SUBTOTAL(3… part, where 3 is the COUNTA function for SUBTOTAL), divide the count SUBTOTAL returns by 2, and return the remainder, (that’s the MOD part), which will always evaluate to either 1 or zero AND check to see if cell I3 contains TRUE

Conditional Formatting formulas must always evaluate to either TRUE or FALSE, or a 1 or 0, which is the numeric equivalent of TRUE and FALSE. A TRUE or 1 will apply the format, and FALSE or 0 hides the format.

To summarise the formula; If the MOD(SUBTOTAL formula evaluates to 1 AND $I$3=TRUE then Excel will apply the format.

Let’s look at the key components of the formula.

The SUBTOTAL function in the formula allows us to apply filters to the table and have the banding automatically adjust so that only the visible/unfiltered cells are banded. This is because SUBTOTAL can ignore hidden rows. Learn more about the SUBTOTAL function here.

We use absolute referencing to tell Excel which cells to reference as the conditional formatting moves through each row. This post explains how conditional formatting formulas work.

The MOD Function:

MOD Syntax:

We use SUBTOTAL to return the number argument for MOD, and the divisor is 2.

You can see it below where I’ve entered the MOD(SUBTOTAL part of the formula in column I:

Lastly, we check the value in cell I3 to see if the check box is checked (TRUE), or not checked (FALSE).

**Tip 1:** Column A must not have any blank cells, otherwise the SUBTOTAL count will be wrong. You can choose a different column for SUBTOTAL, just so long as there are no empty cells.

**Tip 2:** If you don’t need to filter your data then you can simplify the formula to this:

=AND(MOD(ROW(),2),$I$3=TRUE)

**Tip 3:** if you don’t want to toggle the banding on and off then you can omit the second logical test like so:

=MOD(ROW(),2)

Or to allow for filtering:

=MOD(SUBTOTAL(3,$A$5:$A5),2)

Ok, that’s the formula done, now you can click ‘Format’ to set the fill colour:

You can use the check box technique with other conditional formats. Simply add the AND wrapper like so:

=AND(your conditional format rule, check box cell link = TRUE)

You can also modify the MOD formula to band groups of rows like this:

The post Toggle Excel Conditional Formatting On and Off appeared first on My Online Training Hub.

]]>The post Excel Icons appeared first on My Online Training Hub.

]]>There’s a gallery of icons available and they’re grouped into categories to make them easily searchable:

Update: Icons are only available to Office 365 users. If you are not an Office 365 subscriber then you won't get the Icons gallery.

Simply select the Icons you want to insert by clicking on them which will check the box in the top left of the icon and then click ‘Insert’:

This downloads a scalable vector graphics (SVG) file.

**Note:** you must be connected to the internet for Icons to be available as they are downloaded each time you insert them. The downloaded icons are then stored in your workbook so you can go offline and they will remain intact.

Resize the icons using the pull handles which are available when the icon is selected, or adjust the height and width on the Format tab:

**Tip: **use the corner pull handles to retain the aspect ratio when resizing.

You can format the icon colour via the Graphics Tools: Format contextual tab which is available when the icon is selected:

You can also add an outline in a different colour:

You can’t colour separate parts of an icon, however if it has a cut out section then you can insert other shapes behind the icon and colour those shapes:

Some icons lend themselves to nice layering effects:

**Tip**: use the ‘Bring Forward’ and ‘Send Backward’ tools on the Graphics or Drawing Contextual tabs to arrange the order of the icon and shape:

Graphics Effects like shadows, reflections etc. are also available:

Use the Rotate menu to flip or change direction of an Icon:

I’ll mostly be using icons for my blog posts, but other ideas that come to mind are:

- Use them as a button; assign a hyperlink or macro to them (right-click > Assign Macro):

- Use them to create info graphics
- Use them (sparingly) to add visual interest in your Excel workbooks and Dashboards
- Icons are also available in other Office programs like PowerPoint and Word

The post Excel Icons appeared first on My Online Training Hub.

]]>The post Excel Charts for Surveys appeared first on My Online Training Hub.

]]>Let’s start by looking at what you shouldn’t do, and that is a 100% stacked bar chart like this:

The above chart plots the 15 questions on the vertical axis and the responses colour coded in the stacked bars.

I’ll give it one thing, it looks nice and it’s eye catching, but the problem with all stacked charts is it’s very difficult to compare the series in the middle since the starting point is staggered for all but the series on either end.

And that means it’s going to be difficult to interpret for the reader.

Let’s look at some better charts for survey data. The first one uses in-cell charts, or you could use Conditional Formatting Data Bars if you prefer.

This chart is better than the first because the bars for each answer group start at the same point. It makes it quick and easy to compare the responses within a column. We also have the values for each question as opposed to an axis for scale.

We could improve this chart by sorting the answers in descending order for either ‘strongly agree’, or ‘strongly disagree’. This would prioritise or focus the reader’s attention, or even better, give the reader the ability to choose which column to sort on.

In the chart above I’ve omitted the N/A responses so each question won’t add up to 100%, but that’s ok because I’m really only interested in the responses that were applicable.

Fellow Excel MVP, Jon Peltier, calls this the ‘converging’ stacked bar chart because the strongest responses are on either side of the vertical axis. This chart enables the reader to see the ‘strong…’ responses and more easily compare them, although we could take it a step further.

This chart is sorted in descending order on the ‘Strongly Disagree’ response, as it would be reasonable to assume that this is where you want to focus your attention.

But wouldn’t it be nice to allow the reader to choose what order to sort the chart in….

This chart allows the reader to choose whether to sort by question number, 'Strongly Agree' or 'Strongly Disagree'.

A converging or diverging stacked bar chart is really just a stacked bar chart with a few tricks, which you can see in the Excel file available for download above. They are:

1. Change the values you want to display on the left of the vertical axis to negative numbers.

2. Use a custom number format for the horizontal axis labels (0%;0%;0%) so that the labels on both sides of the vertical axis are displayed as positive percentages.

The takeaway should be that changing the way your data is presented and sorted can alter the focus for the reader and more quickly convey a particular point.

If you’re interested in reading more about different charts for survey data then check out this post by Jon Peltier. Warning, get supplies as it’s long!

And if you’d like to learn more cool chart ideas and tricks, including the sort button option above, please consider my Excel Dashboard course.

The post Excel Charts for Surveys appeared first on My Online Training Hub.

]]>