VLOOKUP’s a great function, but when you try to copy a VLOOKUP formula, watch out for a gotcha you’ll see in Excel Video 174. When you copy most formulas in Excel, Excel updates the references as you copy. That’s not true with VLOOKUP. When you copy a VLOOKUP formula,...

Excel Video 173 reviews VLOOKUP, one of the most powerful Excel functions for medical practice managers. I initially covered VLOOKUP starting with Excel Video 61. Now that we have more background there are several more tricks we can use to make lookups even more...

Here’s a hyperlink to the discussion on hyperlinks in Excel Video 172. We’ll talk about the HYPERLINK function first. It’s pretty straightforward, but inserting a hyperlink from the Insert tab on the ribbon is even faster. The reason I show you the HYPERLINK function...

You can’t CHOOSE an easier function to get started with Lookup & Reference functions than Excel Video 171. The CHOOSE function simply gives Excel which number in a list of items to choose from. You can enter up to 254 items in the list and tell Excel which item to...

It won’t take HOURs to explain HOUR, MINUTE, and SECOND in Excel Video 170. All three functions are very simple. The format is = HOUR(cell reference). MINUTE and SECOND work the same way. You’ll see how I use each of the three functions to pull the respective piece of...

The time formatting we reviewed in the last Excel Video has a catch we’ll discuss in Excel Video 169. As long as there are less than 24 hours (or 60 minutes or 60 seconds), the formatting we introduced in the last Excel Video works just fine. As soon as you go over 24...

Make the most of your TIME by watching Excel Video 168. I’ll cover three time related functions in this video. First, the TIME function lets you enter and/or calculate time in Excel, very similar to what we did with the DATE function in the last two Excel Videos. The...

When we last left our heroes in Excel Video 166, we had a clever formula that automatically updated after entering data in one cell. In Excel Video 167, we’ll go one step further and automatically calculate that cell as well. When you open the spreadsheet now, all of...

Excel Video 166 reviews four very basic Excel date functions, but we can combine the functions to make rolling reports update automatically. DATE is a function that lets you enter the year, month, and day separated by commas. Excel turns that information into a date....

Excel Video 165 uses the WEEKDAY function to calculate which days are weekends to help you figure out call. The only trick to the WEEKDAY function is deciding which day of the week you want to call day 1. I typically use option 2 so that Saturdays are given the value...

DATEVALUE doesn’t get the tax returns of the date your daughter just introduced, but Excel Video 164 does show you how to use DATEVALUE to change text to a date. Just like Excel Video 162 changed text to values with the VALUE function, DATEVALUE changes text to a...

NOW and TODAY sounds like the name of a bad soap opera, but they’re two functions to get the current date and time that we’ll discuss in Excel Video 163. The NOW function retrieves the computer’s date and time, while TODAY retrieves the computer’s date. Note that you...

Excel Video 162 helps you convert text to values and values to text. Before Excel 2007, I used the VALUE and TEXT functions a lot more than I do now. I’ll show you what I do now to convert values to text and text to values, and then I’ll show you how to use VALUE and...

Excel Video 161 helps you repeat helps you repeat text as many times as you want you want. I don’t use the REPT function as much as I used to, but there are a few instances when you may find it helpful. Essentially REPT repeats whatever text you enter (remember to put...

Excel Video 160 covers another easy Excel function. This time we’ll use TRIM. TRIM simply removes extra spaces from text. Older data systems occasionally filled any extra space at the end of the data with actual spaces. TRIM is an easy way to get rid of those spaces....

Just in case you want to change the case of your data, Excel Video 159 has three easy functions to do just that. UPPER changes all of the text to upper case. LOWER changes all of the text to lower case (especially helpful if you want your data to look like most of the...

Now that we’re familiar with LEFT, RIGHT, and MID, Excel Video 158 combines FIND and LEN into one formula that doesn’t require the extra columns for FIND and LEN that we’ve used in the past Excel Videos. When you first see the combined formulas, you’ll probably wonder...

Excel Video 157 uses MID to extract the first name from our list of patients. LEFT helped us retrieve the patients’ last names and RIGHT got the patients’ first names and middle initials. Now we’ll use MID to just get the patients’ first names. MID requires three...

RIGHT works just like LEFT did in Excel Video 155 with similar parameters. First, you tell Excel where the text string is you want to extract characters from. Second, you tell Excel how many characters to extract. The trick with RIGHT is knowing how many characters to...

You’ll be LEFT behind if you try to extract a patient’s last name without watching Excel Video 155. The LEFT function has two parameters. First, enter the text string you want to extract text from, which in our example is the cell with the patient’s full name. Then,...