Excel Tips and Tricks for All Levels

I’m going to add this section to the Microsoft Excel Help section (menu on the left) of the website. An excel user contacted me and questioned how to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:

HYPERLINK(link_location,friendly_name)

where link_location is where you want the hyperlink to go to and friendly_name is how the link will appear on your sheet.

For example, if you have a file on your desktop it would look something like this:

Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)

Read More

A common excel formula that can come in handy in many projects with Excel is the Today() Function. The Today() Function is used to return the serial number of the current date. By formatting the cell where the formula is used, it will return the Month, Day and Year of today. One of the easiest functions in Excel to use, here is the formula:

=Today()

Since today is June 5, 2007, the formula will return 6/5/07 since it is automatically formatted in date format. If you were to change the formatting to a number, the formula would return the serial number 39238 (If you are wondering what this number stands for, it is the number of days from January 1, 1900 – Tomorrow the serial number returned would be 39239)

There are many places in your projects you will be using the Today() function, from showing the date your spreadsheets were last updated to calculated how many days from today a deadline is. Once you learn to use this function regularly it will be a function you can’t live without.

Over the next few posts, I will be adding many example of how to use the numerous date and time functions built into excel. Please contact us with any questions or suggestions you may have.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

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.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.