5 Groovy SUMPRODUCT Examples – Advanced Excel Formula Tutorials

In this blog post, we look at 5 groovy examples of the SUMPRODUCT function. This is one of the great functions of Excel. A function that can turn you from being an Intermediate/Advanced Excel user to an Excel guru instantly.

The SUMPRODUCT function is powerful, versatile and expansive. It is the go to function when looking for an alternative to array formulas.

If you are a newbie – check out our SUMPRODUCT function guide to get an understanding of how it works.

Ok, are you ready to rock on with these 5 awesome SUMPRODUCT examples?

Let’s do this.

If you prefer to watch videos, check out this video covering the tutorials from this blog post.

Continue reading

10 Great Word Productivity Hacks

This blog post looks at some productivity hacks for creating professional, consistent and awesome MS Word documents, the other day we had a Rush Printing some business cards we made in MS, luckily we found a great place to print them.

These 10 skills are sure to save time and improve overall efficiency in MS Word.

1. Quick Parts and Building Blocks

Building Blocks is a fantastic feature in MS Word where you can store frequently used content for quick insertion to a document.

There are many galleries to the Building Blocks feature including cover pages, tables, headers, footers and text boxes. Quick Parts is the gallery for miscellaneous content that does not fit any of the other galleries.

For example, say I work for a company with 4 different offices. I frequently have to enter the address of these offices into documentation. This entails looking up the correct postcode etc. and then typing and formatting it. With Quick Parts these can be stored for easy re-use.

Continue reading

Conditional Formatting – Multiple Columns – 3 Examples

In this blog post we will explore using Conditional Formatting to test multiple columns. And we will run through three different examples of doing so. For additional information you can visit zotapro.com to become efficient in these regulations so you can become properly certified.

Conditional Formatting is typically applied to one cell, or column. It evaluates the cells in that range, and also applies the formatting to that same range.

In this blog post though we take things further to see example of testing and formatting multiple columns.

Using the AND Function with Conditional Formatting

In this first example we want to apply a Conditional Formatting to the data set below.

The dataset that the article will use. A list of quartely sales.

We want to format the row if every value in columns B, C, D and E is greater than or equal to 100.

  1. Select the range of cells you want to format (B2:E7 in this scenario).
  2. Click Home > Conditional Formatting > New Rule.
  3. Select the Use a formula to determine which cells to format option.
  4. Enter the formula below into the box provided.
=AND($B2>=100,$C2>=100,$D2>=100,$E2>=100)
The AND function used in a Conditional Formatting feature.

The column are made absolute in the formula so that it does not move to each cell when we copy it to the right.

Click the Format button and choose what formatting you would like.

The finished article looks like below. Two of the persons met this target.

The completed Conditional Formatting rule with the AND function.

Testing a Threshold Value in a Another Cell

In this example, we would like to format the row if any one of the cells has a value greater than or equal to the value in another cell.

In the data below, we have a threshold value in cell G3 which we would like to test against. The Conditional Formatting rule from the previous example is also still applied.

Sample data of quarterly sales and that has a threshold value entered in cell G3.

Because we are hoping to apply the formatting if any one of the cells is true – we will use the OR function.

Follow the steps from the previous example to create a new rule and enable us to enter a formula.

We will use the following formula. The reference to cell G3 is absolute.

=OR($B2>=$G$3,$C2>=$G$3,$D2>=$G$3,$E2>=$G$3)
OR function within a Conditional Formatting rule to test if at least one values meets our condition.

The rule is applied to the dataset.

Threshold Conditional Formatting rule applied.

You can see that an additional row has been formatted, but it has also overwritten one of the rows from the previous example (Joseph).

Now, the previous rule was more important than this one. So we need to change the order of the rules so that if they are both true, then the rule with the AND function has priority.

Click on one of the values in the range and click Home > Conditional Formatting > Manage Rules.

Use the arrows as indicated in the image below to change the order of the rules so that the AND function is on top. The higher in the list, the greater the priority.

Changing the order of Conditional Formatting rules

Now this is what we wanted.

Conditional Formatting rules applied in the correct order.

And if the value in cell G3 is changed. the Conditional Formatting rule will react to this.

More Than Two Columns Meet Criteria

In this final example, we would like to apply the Conditional Formatting rule if more than 2 of the columns have values of 100 or more.

We will use the COUNTIF function for this.

This function can count how many cells contain a value of 100 or more. And then we can test if the answer to that is more than two.

Follow the steps from the previous examples to create a new Conditional Formatting rule using a formula.

The formula below can be used.

=COUNTIF($B2:$E2,">=100")>2
Using the COUNTIF function with Conditional Formatting

The rule is applied to the data range. The previous rules have been removed.

Formatting when more than two column meet criteria.

In this blog post we looked at three different examples of how and why to test multiple columns with Conditional Formatting.

I hope it has given you some ideas where you might be able to apply similar techniques.

Excel VBA Find Function

In this blog post we take an in-depth look at the Find function in Excel VBA. This function is very useful for (yes, you guessed it), finding things in a list.

The Find function works similar to the Find feature within Excel. Upon finding what you ask for, this function will return the range. We can then perform many actions, or return further information about the found item.

This blog post will demonstrate a few useful examples of the Find function in action.

How Does the Find Function Work?

It runs from a specified range and the only information the Find function needs is the value you want to search for. It does have quite a few optional arguments that can also be used.

You can see a list of these below with a brief explanantion of their use. This blog post will demonstrate some of the more useful options.

  • What – The only required option. The value you want to search for.
  • After – A starting cell range for the search
  • LookIn – Where to search for the value – comments, formulas or values.
  • LookAt – Whether you want to search for a complete match on the value, or a partial match.
  • SearchOrder – You can order to search the range by rows, or by columns.
  • SearchDirection – You can search the range forwards (xlNext), or backwards(xlPrevious).
  • MatchCase – A True or False value as to whether you want a case sensitive search or not.
  • MatchByte – A True or False value or double byte languages.
  • SearchFormat – A true or False value to search by format, or not.

Performing a Simple Find

Let’s see a simple example of the FIND function in action.

We have the list of names below.

List of names for Find examples

The following VBA can be used to find the name Gita and return the address of the found cell. The address (A5) is printed to the Immediate window so that we can see the result.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Gita")

Debug.Print FoundItem.Address

Beginner to Excel VBA? Enrol in our online Excel VBA course and get up to speed fast.

You can create a similar list to play around with this code and learn what else you can do with a found item.

The address is returned in this example to see it working correctly. Depending on your real-world requirements, you can return any information about that cell. Or perform actions to it.

Take the following example, where we want to write the current date to the cell in the column next to the found item.

List of names and dates
Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Gita")

FoundItem.Offset(0, 1) = Date

When the Find function is used in Excel VBA, the criteria of the last search will be visible when using the Find feature in Excel.

This also applies to some of the options explained soon and is the same as how the Find dialog in Excel normally operates.

Find dialog with previous search criteria

Preparing for When a Value is Not Found

There may be times when the Find function cannot find the value it is looking for. We need to write some code to handle these scenarios. Otherwise our macro will return an error message.

When the Find function cannot find the value, it returns the range object set to nothing.

So to handle this we can use an IF statement to test if the returned object is equal to nothing. And if so take a different course of action.

In the code below a message is shown to the user, and then the macro is aborted.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Paul")

If FoundItem Is Nothing Then

MsgBox "The name was not found."
Exit Sub

Else

FoundItem.Offset(0, 1) = Date

End If

Using the LookIn Option with Find

The Find function can do more than just look for the values in a cell. It can also look for text within formulas and comments.

In the image below you can see the word “Completed” has been entered into a comment for cell A4.

Search for text in a comment

The following code searches for text within a comment, and prints the address of that cell to the Immediate window.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Completed", LookIn:=xlComments

Debug.Print FoundItem.Address

Performing a Partial Match When Searching for a Value

Another really useful option when working with the Find function is the ability to look for a partial match.

The Find function has an argument named LookAt which we can use to specify to look at the whole value, or just part of it.

In the list of buildings below, we could use this option to search for “Chicago”, even though it is only a part of the value of a cell.

List of buildings for a partial match with find

The code below searches for “Chicago” as a partial match and prints the address to the Immediate window.

Dim FoundItem As Range

Set FoundItem = Range("A2:A7").Find("Chicago", LookIn:=xlValues, LookAt:=xlPart)

Debug.Print FoundItem.Address

Remember that previous Find criteria can remain after a user has run Find from a macro, or the dialog in Excel.

Therefore it can be a good idea to specify arguments such as LookIn:=xlValues to overwrite any previous setting such as comments, which we used in the previous example of this blog post.

Find a Cell with Specific Formatting

The Find function can also be used to find a cell based on its formatting.

So for example in the list of cities below, I want to find the “Rome” in a bold format.

Finding a cell with specific formatting

To use this option, you first need to set the format separately. And then use the SearchFormat argument of the Find function.

The code below shows the bold font formatting being set, and then in a separate statement a search for that formatting is set to true.

Dim FoundItem As Range

Application.FindFormat.Font.Bold = True

Set FoundItem = Range("A2:A7").Find("Rome", SearchFormat:=True

Debug.Print FoundItem.Address

Any formatting that you set will still be there when you run the Find function again. Therefore it is a good idea to clear the FindFormat property after its use with the following statement.

Application.FindFormat.Clear

The Find function can be very useful and the various options provided with it means that we have great ability to find exactly what we want.

This blog post explored some of the more useful options. Please explore what else it can do and see how it can help you in your future macros.

Excel IF Function Contains Text – A Partial Match in a Cell

In this post we will look at how to use the IF function to check if a cell contains specific text.

The IF function when used to compare text values, checks for an exact match. But in this blog post we want to check for a partial match. We are interested if the cell contains the text anywhere within it.

For our example, we have a list of addresses as shown below. And we want to display the word ‘local’ if the address contains the text ‘CB2’.

The sample data of address for our partial text IF function examples

So any postcode beginning with ‘CB2’ is considered local. Anything else is not.

Watch the Video

Check If Cell Contains Text

As mentioned, the IF function always performs an exact match. Therefore we need a different function to determine if the text is in the cell or not.

The function we will use is SEARCH. This function will return the position of the text inside the cell, if it is present.

=SEARCH("CB2 ",A2)

You have to be careful when testing for partial matches. And in this example a space is added after CB2 in the string to search for. This is done to prevent it finding matches for postcodes such as CB25 or CB22.

There is also a function called FIND which could be used instead of SEARCH. The key difference between the two is that FIND is case sensitive, and SEARCH is not.

Now this function returns the position of the text if found. We are not interested in this, and simply want to know if it exists.

So we will surround the SEARCH function with ISNUMBER to return TRUE if the text is found, and otherwise return FALSE.

 =ISNUMBER(SEARCH("CB2 ",A2))

We can now finally add the IF function to display the word ‘local’ if ISNUMBER reports TRUE, and the word ‘far’ if FALSE is returned.

=IF(ISNUMBER(SEARCH("CB2 ",A2)),"local","far")
The result of our partial text match

How about Searching for Two Pieces of Text

Lets take it a step further. And what if we consider an address to be local if it contains the text ‘CB2’ or ‘CB3’.

We will bring in the OR function for this. A function that can test multiple conditions and returns TRUE if any one of its tests are met.

The formula below can be used. This time the cell is made blank if it is not local.

=IF(OR(ISNUMBER(SEARCH("CB2 ",A2)),ISNUMBER(SEARCH("CB3 ",A2))),"local","")
The results from searching for two text matches


IF Functions in Power Query Including Nested IFS

IF functions are some of the most popular formulas in Excel, and in this blog post we will look at how to write them in Power Query.

There are some key differences between IF function in Power Query and their worksheet equivalent, but you can pick them up quickly.

In these examples we will use the sample data below, already loaded into the Power Query Editor.

We have some salespeople, their sales figure and a status that they have attained.

Writing IF Statements in Power Query

In our first example, we will pay our salespeople a bonus of 15% of their sales, only if they are of an Executive status. They will get 8% of their sales if not “Executive”

We will to add a new column to the table, so click the Add Column tab of the Ribbon.

In Power Query there is a Conditional Column button which presents a window to make writing conditional statements user friendly. Good for beginners.

However this is limited and I feel you are much better served by learning to write If statements purely. So we will click the Custom Column button.

Adding a Custom Column in Power Query

The Custom Column window appears. Firstly we can enter a name for the column – for example Bonus.

Creating a custom column

We will then write the If statement in the Custom column formula box provided.

Underneath the box is a message informing you of any errors in your syntax. And to the right, a list of the columns from our table. We can use this list to enter the columns into our formula instead of typing them (and potentially making silly mistakes, so I’m a fan).

We will enter the following formula.

= if [Status] = "Executive" then [Sales] * 0.15 else [Sales] * 0.08
First If statement example

There are a few things you need to know when writing If statements in Power Query.

  • The formula is written in lower case.
  • It is case sensitive and there is a difference between “If” and “if”.
  • There are no commas. Instead the words “then” and “else” are used to separate the test, the value if true and value if false (this will be familiar to VBA users).
  • Spaces are typically entered between the words to make it more readable.

Notice there are no errors detected in our formula.

With the new column added and still selected, you can see the M code (name of the language) in the Formula bar. And you can edit it up here in the future.

It is also added as an applied step on the right and can easily be removed also.

New column and formula added

So that is our first If statement example. Let’s do another one.

Using And and Or in your If Statements

You may have used the AND and OR functions in your IF formulas before in Excel to create more complex logic. This can be difficult when users are just beginning with formulas.

Well the good news is that it is actually easier in Power Query.

For this example, we will pay our salespeople a bonus of 15% is their status is “Executive” or “Gold”, otherwise they get 8% of their sales.

We can use the following formula.

= if [Status] = "Executive" or [Status] = "Gold" then [Sales] * 0.15 else [Sales] * 0.08
Example of If statement with Or in Power Query

All we need to do was enter the word “or” between the two conditions. Wonderfully easy.

The same would apply with the And logic. Simply enter the word “and” between the conditions.

It is also possibly to have more than two conditions.

Writing Nested IFs in Power Query

For one last example, we will look at writing nested If formulas in Power Query. Once again this is nothing to fear, and is actually quite simple.

For this example we will pay a different bonus depending on whether they are of “Executive”, “Gold” or “Silver” status.

The key differences again when writing nested ifs in Power Query is that it must be in lower case, and also there will be no commas or brackets.

We can use the following formula.

= if [Status] = "Executive" then [Sales] * 0.15 else

if [Status] = "Gold" then [Sales] * 0.1 else [Sales] * 0.05
Nested ifs in Power Query

It was not necessary to write this on two lines, but it does helping reading nested ifs.

So If functions are a little different in Power Query than in worksheet formulas, but they are simple to learn and a nice step into the world of writing M code in Power Query.

How to Hyperlink to a Hidden Worksheet in Excel

Creating a hyperlink to another worksheet in Excel is easy. But if that worksheet is hidden then the hyperlink will not work. In this tutorial we provide a solution to follow a hyperlink to a hidden worksheet.

The example is that we have a “Main” worksheet with hyperlinks to the 4 other sheets of the workbook.

In the image below you can see that the “Apples” sheet is hidden.

Hyperlinks to other worksheets

This is going to take a little Excel VBA to get this working. In this tutorial I will show two ways of making this hyperlink work, even when the sheet is hidden.

If you are a beginner in Excel VBA, why not speed track your learning by enrolling in the Excel VBA for beginners course.

Watch the Video

Excel VBA to Follow Hyperlink to Hidden Worksheet

Lets begin by opening the Visual Basic Editor by clicking Visual Basic on the Developer tab, or by pressing Alt + F11.

We need to use this code on the Worksheet_FollowHyperlink event so that it occurs when the link is clicked. We need to open the code window for the “Main” worksheet because that is where the event resides.

Do this by double clicking on the sheet in the Project Explorer window. The Title will display the currently active code window.

To access the Worksheet_FollowHyperlink event, select Worksheet from the Object list, and then FollowHyperlink from the Procedures list.

The Object and Procedure lists

We can now enter some code inside the Worksheet_FollowHyperlink sub provided.

What is quite misleading is the name of this event. It is called FollowHyperlink but it does not actually follow the link. It is just an event that is triggered by someone clicking a hyperlink on that sheet.

We will need to use code to identify what link was clicked, unhide the required sheet, and take the user to it.

For the first example, the code below will do the trick.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim ShtName As String

ShtName = Target.Name
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select

End Sub

This code declares a string variable named ShtName. It then assigns the name of the clicked hyperlink (the text displayed by the hyperlink) to that variable. A variable named Target is provided to us for this.

In this example the hyperlink text and the name of the worksheets is exactly the same, so this is a good idea. If the hyperlink text and worksheet name are different, then the second example is better for you.

This is then used to make the sheet visible and then select it.

This second example of code does not rely on the link text and sheet names matching and is more durable.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim ShtName As String

ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select

End Sub

In this example we extracted the sheet name from the sub address of the clicked hyperlink. A sub address is when you link to a location within the current file.

This was done by extracting all the characters up to the exclamation mark in the sub address. This is because exclamation marks separate the sheet name and cell address or range in Excel i.e. Sheet3!D2.

The Left function was used for this, and the Instr function located the position of the exclamation mark.

Hiding the Sheet Again When Leaving

Now that solves our problem and the purpose of this tutorial. However, you might want the sheet to automatically hide itself again when you have finished with it.

You could be leaving the sheet by following a hyperlink on that sheet, or maybe just by clicking a sheet tab at the bottom. I want some code to run, no matter the method of leaving.

This code will be placed in the Deactivate event of each worksheet.

Start by double clicking on a sheet in the Project Explorer window to open its code window.

Select Worksheet from the Object list (just like previously in this tutorial) and then Deactivate from the Procedures list.

And enter the line below inside the sub.

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub

This code refers to itself as Me. Nice simple code to make the sheet hide itself on the deactivation of the sheet.

Excel VBA enables you to take control when Excel tasks don’t quite work how you need them to, like in this example of following a hyperlink to a hidden worksheet. It is a fantastic skill to have.

Using the ABS Function to Return the Absolute Value – and 2 Reasons Why

In this blog post we will look at how to get the absolute value of a number using the ABS function – and 2 examples WHY you might want to do that in Excel.

Lets start by defining what exactly an absolute value is.

An absolute value of a number is its distance from 0 regardless of its sign (positive or negative. For example, the absolute value is the same for 150 and -150 because the distance from 0 for both is 150.

Therefore the most common reason in Excel to calculate an absolute value, is to convert a number from negative to positive within a formula.

The ABS Function

This is done using the ABS function. You can see a simple example of this function used below in column B to return the absolute value of each number in column A.

=ABS(A2)
Return the absolute value of a number


Watch Two Useful Examples of the ABS Function

Sum the Absolute Values in Excel

A real-world example could be that you want to sum a list of values.

And these values are a mixture of negative and positive. However you wish to sum the absolute values of those numbers, and not treat the negative values as negatives.

One way that we could do this, would be to embed the ABS function within a SUM function like below.

=SUM(ABS(A2:A8))

Because we are providing the ABS function with a range of cells, and not a single cell, this would need to be run as an array formula. So be sure to press Ctrl + Shift + Enter and not just Enter.

Summing absolute values

We could avoid an array formula though by using the SUMPRODUCT function instead. SUMPRODUCT is an array function and can therefore handle ranges of cells eliminating the need for Ctrl + Shift + Enter.

=SUMPRODUCT(ABS(A2:A8))

Check if a Number is Within a Tolerance Limit

Another useful reason to use the ABS function is to check if a number is within a specified tolerance level.

In the image below we have some values from last week and some values for this week. And we need these new numbers to be within a range, or tolerance limit to last weeks. A tolerance of 20 has been set in cell E2.

This week and last weeks values

In this example the IF function will be used to test if the difference of the two numbers is within 20.

=IF(ABS(B2-A2)<=$E$2,"Yes","No")

The ABS function is used because the difference of the 2 numbers might result in a negative value. But we need the absolute value to check if it is within tolerance.

This easily identifies those within tolerance with a Yes, and those that are not with a No. The tolerance in cell E2 can easily be changed in the future if needed.

Adding a Conditional Formatting Rule

Maybe we wanted the result to be more visual by adding some Conditional Formatting. We can change the Yes and No to a 1 and 0 for easier testing.

=IF(ABS(B2-A2)<=$E$2,1,0)

I would like to add the green tick and red cross icon sets. So lets perform the following steps.

  1. Select the range of cells (in this case its C2:C6)
  2. Click Home > Conditional Formatting > New Rule (the following steps can be seen in the image below)
  3. Select Icon Sets from the Format Style drop down
  4. Select the green tick for the first icon, nothing for the second and a red cross for the third icon
  5. Change the Type option from Percent to Number
  6. Enter a 1 in the first box, and 0 in the second box
  7. Change the logical operator to a greater than sign only in the second row
  8. Check the box for Show Icon Only
Createing the Conditional Formatting Rule

The completed rule shows a green tick if the value is 1 or greater, and a red cross if 0 or less. It will also show the icon instead of the value.

Nice visual on values within tolerance

This post showed two examples of why getting the absolute value could come in handy in the real-world. There are many.

However it is typically when you need a positive number in a formula. Especially when subtracting value which might result in a negative that you do not want.

Create a Line Graph with a Target Range

Create a line graph with a target range to easily visualise values that are achieving a performance expected of them, that you can do in any computer and even if the computer is failing you can get services from sites as https://werelocal.ca/computer-repair-hamilton-on/.

The great news is that this is very simple to add to your line graphs, yet extremely effective.

Line graph with a target range Continue reading