Tag: Excel

Using VBA logical operators AND - OR

VBA Logical Operators in Microsoft Excel




The most common logical operators in VBA are: AND and OR. Each of these operators is very useful for evaluating conditions and making appropriate decisions about the code that will be executed


The AND Logical Operator


The AND logical operator is the operator that helps us to enforce two conditions. This operator allows to execute a block of code that must meet condition1 AND condition2.

In the following example I have the qualification of two exams. Only if both exams have a grade higher than 3, then the student will be approved, otherwise the grade will be disapproval.


VBA Logical Operators Initial Example


We are going to use an IF statement to compare the exams qualification with the grade condition. This comparison is that we usually do with the logical operators.

The code that will be executed when the button is pressed will be the following:


VBA Logical Operators AND Code


When we execute this code we will obtain the “Approved” result, this is because both exams have a rating greater than 3:


VBA Logical Operator AND approved example


In this way we verify that the logical operator And helps us to force both conditions to be fulfilled. However, if the value of one of the cells is less than 3, then we will have a different result:


AND Logical Operator Reprobate Example


The logical operator And will return the true value only when both conditions are met and it will suffice that one of them is not met to obtain a negative result.


The OR Logical Operator


The OR logical operator translates will allow us to know if at least one of the conditions is fulfilled, that is to say, if the condition1 OR condition2 is fulfilled.


If we change the previous example a bit and say that it is sufficient that one of the two grades is greater than 3 for the student to be approved, then we can modify the code as follows:


Code for OR logical operator example


If any of the grades are higher than 3, then the student will be approved:


OR logical Operator Approved Example


The only way the logical OR operator returns a false value is that none of the conditions are met. In our example, the student will be reprobate only when both grades are less than 3:


OR Logical Operator Reprobate Example


To conclude in the following table we can see in each case how the logical operators evaluate the condition:


Conditions Table for VBA Logical Operators AND - OR


Excel Total 



Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

How to Select Cells by Value in Microsoft Excel



When you have a lot of data in a table and want only to select a group of cells with a certain value we can use Excel search for quick selection of those cells.

From the following table of data I want to select all the cells that have the value $ 1,000 and once selected place the cell text in bold

Select Cells by Value

Selecting by the Search Method

An alternative is to use the Excel search box to select the cells that meet the condition set. To do that we open the search bog, then we specify the value we’re looking for and finally click the Find All button.

Find All Selected Data by Value

Excel display all the cells that match the specified value, we select only the results for the worksheet cells are also selected Tip: To select all results can click on the first row of results and then press the Shift key and click the last row. Since you have selected all results, close the search window and observe the cells selected in the worksheet ready to apply the text in bold

Formatting Select Data

Using a Conditional Formatting Rule

If we make a selection of cells to make a format modification, then we can use an alternative method Once we have selected the range containing the information (b2:E16) I must press the Condittional Format button on the Home tab and select the Highlight cell rules option and then Equal To button.

Conditional Formatting to Select Cells by Value

In the dialog box I have to specify the quantity that I’m looking for and select the Custom Format option.

Custom Format

Then I’m going to select a Bold style for the fonts to format the cells that comply with this rule

Conditional Formatting Bold Style

Then click OK to close the dialog box and then click OK to apply the conditional formatting rule, we will have the cells of value equal to $1000 with a bold style format. Excel Total


How to Use Excel Comments

How to Use Excel Comments



Excel allows you to insert comments in any cell of your Worksheet in Microsoft Excel. Those Excel Comments are very useful to make a check, give some instructions or simply give a detailed explanation about the cell content.

Add a comment to a Cell

The first step is to select the cell that you will add the comment and then go to the Review tab and in the group Comments and press the New Comment button (In MAC only New). Clicking on that button, the box necessary to enter the comment is displayed.

Add a Microsoft Excel Comment

Enter your comment in the box and to finish click outside the box. If you need to enlarge the box only positions the mouse over any of the corners and drag the edge to have the desired size. Excel identifies a cell that has a comment through a small triangle in the upper right corner of the cell. To see the comment from a cell only you must position the mouse over that cell and Excel will display the comment.

Modify or Eliminate a Comment

When you select a cell containing a comment Edit and Delete Comment buttons are enabled.

Excel Comment Options

Clicking on the Edit Comment button will show the comment box allowing you to do the editing again. If instead you want to delete the comment is sufficient to press the Delete button. Now if you have an Excel worksheet that contains many comments, you can use the Previous and Next buttons to move between each. Or conversely, if you want to display all comments sheet at the same time you can click Show All Comments button. You can learn more about Microsoft Excel Comments and Review options here: Excel School


The 3 Time Functions Most Used in Microsoft Excel

The 3 Time Functions Most Used in Microsoft Excel


Excel provides three functions that help us work with time information in a Worksheet. These functions are useful for extracting information specific about the hour, the minute and the second of a cell containing data with a Number category of Time.


Each function, according to its name, extracts a specific part of the data information (hour, minute or second) with only specifying the cell containing the data you want to analyze. For example, suppose that cell B2 has the following information:

Time Type Data in Microsoft Excel


1. The HOUR function

To get only the hour number of this cell you can use the HOUR function as follows:

The Hour Function in Microsoft Excel

Just see that Microsoft Excel returns the 17 value because the hours by default is showed with the 24 hours format.

2. The MINUTE function

To get the result of the minutes we use the MINUTE function as follows:

The Minute Function in Microsoft Excel


3. The SECOND function

Finally the last one is the SECOND function to get the value of the seconds of the cell information:

The Second Function in Microsoft Excel

We learn in this post that when you need to extract specific information from a cell value related to time, you can use those 3 functions to get the specific value of hour, minute and second that you can use in later calculations. You can learn more about Microsoft Excel Time functions here: Excel School


Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

Excel Visual Basic (VBA) for Beginners to Cleanse a Dataset


This Excel video series helps Excel users just starting out with code in the Visual Basic program, or those who have never coded before, who are curious about it and just heard about it, about its powerful utilisation. In a lot of cases, Excel users find themselves engaged in tricky manual tasks in study subjets or work tasks, that take up time, create stress and lose of time. Data cleansing is one such task.

To explain that, if you have ever gone through a list of data and looked for mistakes (in dates or numbers, for example) then you have done a data cleansing task. With a little coding knowledge, that we are going to see in the following videos, it is possible to dramatically speed up this kind of task, reduce the stress and increase your productivity. You can learn more about Microsoft Excel go on here: Excel School

The 1st Part: Introduction

The 2nd Part: Beginning with VBA

The 3rd Part: Using Messaging Boxes

The 4th Part: Stopping the program

The 5th Part: Finishing and testing the program



PMT Function Calculating a loan payment

PMT Function Calculating a Loan Payment



In this video, learn how to calculate the payment for a loan based on constant payments and a constant interest rate. You need to know: the interest rate, the number of periods and the present value of the loan. You can learn more about Microsoft Excel financial functions here: Excel School



Dependent Data Validation Lists in Microsoft Excel

Dependent Data Validation Lists in Microsoft Excel

This video will walk you through the process of setting up and using dependent data validation lists with Microsoft Excel. You could refer to this as cascading data validation. This is very useful to create templates of databases that you need to be completed by other users. The other users only have a limited option to fill each cell. You can learn more about Microsoft Excel productivity here: Excel School


Using Navigation Tools in Microsoft Excel

Using Navigation Tools in Microsoft Excel

Learn about essential training in Microsoft Excel, one of the first tools that you have to begin to use, the navigation. Save a lot of time, learning basic keyboard combinations, or commands with the mouse, to navigate between worksheets, workbooks, cells, columns, rows, etc. And begin to increase of the productivity in the using of Microsoft Excel You can learn more about Microsoft Excel productivity here: Excel School

Good Looking Gauges in Microsoft Excel

Good Looking Gauges in Microsoft Excel

Show really cool gauges to show indicators in your work. Let’s make good presentations of data learning with this video. Microsoft Excel has a lot of functionalities, not only create charts and tables, with a bit of creativity and learning from experts, take the most out of this powerful tool. You can learn more about Microsoft Excel charts and applications here: Excel Training Videos

Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

How To Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

With this video we are going to use the “Invert If Negative” feature in the charts. This is useful to show the negative values in the chart with another color and you don’t have to manually change the color in each bar. You can learn more about Microsoft Excel charts and applications here: Excel Training Videos