Excel has many built-in formulas that will check for errors for you. Were going to take a look at three of those formulas, which are ISNA, ISBLANK and ISERROR. Let’s start by taking a look at the formulas:

ISNA(value)
ISERROR(value)
ISBLANK(value)

Each of these formulas looks at value and evaluatues whether it is #N/A (with ISNA), #Value (with ISERROR), or blank (with ISBLANK).

When You Can Use These Formulas
These formulas come in very handy when you are working with a large group of formulas and need further analysis of the results. Having and #N/A or #Value in your results, as I’m sure you may have experienced, will most likely result in receiving more errors the more that particular result is analyzed. As you’ll see in the examples below, vlookup is a very common formula which will return an #N/A if the lookup value is not found. Or if you try to add a string to a number, the result will return #value.

Read more »

One of the easiest ways to begin learning how to use the Macro ability of excel is to use the “Record Macro” button. To begin recording a macro, goto Tools->Macro->Record New Macro. After you hit that button, any actions you perform will be recorded. When you are finished performing the actions hit the “Stop” button on the box that popped up when you began recording. To view the code of the actions that was recorded, goto Tools->Macro->Macros and click on macro that was just recorded and hit edit.

The macro (or VBA) section of excel works great to help you perform actions in a spreadsheet that you perform repeating on a consistant basis. It will save you much time and effort (as well as headaches) and by looking at the code you can begin to learn how the code works.

The next step (which we will be adding soon) is to learn some basic functions to assist the macro, such as for loops and other application functions, to increase the ability of the macro to perform the feats you would like. The expandability of the macro is its greatest feature, as it can perform almost any process that you can think of.

I will be adding some examples in a near future post as to what the macros can be specifically told to do through recording them.

When using Excel, there will be many instances where you will want to count a particular range of cells. Excel has numerous ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your counting even easier. Let’s go ahead and take a look at each of the formulas:

COUNTA(value1,value2,…)

COUNTBLANK(RANGE)

Both COUNTA and COUNTBLANK can accept individual objects or a Range of Cells. The difference between the two is what they are made to count. COUNTA counts the number of cells that are not empty and returns the number of cells containing some form of data. COUNTBLANK, on the other hand, will count the number of empty cells in the range and return the total (the opposite of COUNTA). Next let’s take a look at some examples.

Read more »

I use excel macros everyday with my current job duties. A section I will soon be adding to ExcelHints.com is examples of macros that I use all the time. Macros can be used for several reasons, the most common is when there is a task that you need done on regular basis. Automating the process that you repeat all the time can speed up the amount of time you spend with the repetitious work.

Macros can also be useful to perform complicated tasks that would otherwise be very time consuming to perform. Here’s an example of how I used a macro today: we have a group of excel files with in numerous folders (about 2500 files in 750 folders) that needed an item changed within each file. If the macro found an instance of the item it was looking for, the item number was updated to the new number. After this macro was written, it took about 20 minutes for it to search the each file and make the appropriate changes. Doing this by hand would have taken several days with many people working on it.

I will also be adding a section about how I go about testing the macro before I use it on live data. Always remember to backup your original data before running any type of macro on it!

There are many times when analyzing large amounts of data when you might want to sum or count a column or range based on certain criteria. One example is if you have a database of products you have sold, you might want to know the sum of profits for a particular product - which is where the sumif formula would come in handy. Or possibly for the same example you want to know how many instances of a particular item you have sold, which is countif’s formula specialty.

Let’s start by taking a look at the formulas:

COUNTIF(range,criteria)
SUMIF(range,criteria,sum_range)

Both the Countif and Sumif formulas have very similar criteria. When you are using the Countif formula you need to the it the range of cells that you want evaluated with a certain criteria. Sumif is much the same way with one exception, the sum_range can be different from the range you are evaluating with a specific criteria. We well show numerous examples below to illustrate how the formulas work and their differences.

Read more »

One of the easiest ways to manipulate a string is by using the left, right and mid formulas. There are many times when users of excel are given a long string and only need a certain section of that string to get the results they desire. Using these formulas will greatly reduce the number of headaches you have when given a string with all the characters together instead of different columns.

Let’s start by taking a look at the formula:

Left(text, Num_Chars)
Right(text, Num_Chars)
Mid(text, Start_Num, Num_Chars)

For the Left and Right Formulas text is the string you want to break apart and num_chars is the number of characters you want the left or right of. For the Mid formula, there is also a start_num which tells the formula where in the string you would like to start taking the number of characters from.

Read more »