HLOOKUP is a tool that makes it easy to find the information you’re looking for without the hassle. You can search for specific data in any row of a table or spreadsheet quickly and efficiently, giving you the time to focus on more pressing issues. Using HLOOKUP can make your job just a little bit easier when using Excel.
Here, we’re going to go over everything that you need to know about the HLOOKUP function. We’ll be discussing:
- The basics of the HLOOKUHow to use HLOOKUP
- How to use HLOOKUP
- When to use HLOOKUP
Let’s get started!
What is HLOOKUP
… Read the rest
What is the Now Function in Excel?
For those new to all things Microsoft software, the now function in Excel continuously updates the date and time whenever there is a change within your document. You can either format the value by now as a date or opt to apply it as a date and time with a numerical format. The purpose of the function is to set (and keep track of) the date and time.
Notes on Use
While the Now function in Excel does not have parameters, it does require empty parenthesis. The value returned by the Now function … Read the rest
Learn how to subtract in Excel with this valuable how-to guide. This article will walk you through each step of the process from start to finish.
Excel is a powerful program that makes organizing numbers and data easy for anyone. But, learning how to perform even simple functions can be a bit tricky when first starting out. Excel can perform many different functions and one of the most basic is subtraction. Below you will find a complete guide on how to subtract in Excel.
We don’t know why Microsoft didn’t make it but there is no subtract function in Excel. … Read the rest
If you have a large database of information, it can be difficult to make sense of all those names numbers. The Excel SUM formula lets you focus on specific categories within an Excel worksheet and come up with subtotals that can help you to spot trends and patterns in your data.
Read on to find out more about:
- How the SUM function works
- Using the Excel SUM function
- Different applications for SUM formulas
About the SUM Function
The SUM function is one of the simplest functions in Excel, but it’s also one of the most useful. It’s one of several … Read the rest
Excel makes it easy to figure out the average of a group of numbers, no matter how large or small. It makes it easier for you to analyze important data. You will learn how to use Excel’s “average” function right here.
Most of us are familiar with average values. They offer a great way, to sum up information in a single number. Which gives us an immediate picture of any dataset.
If you have a large set of data, Excel can help you to find statistical values such as the average.
Not only can this help to enhance your understanding … Read the rest
Sometimes you need to extract the integer portion of a number. Sometimes the fractional part. Sometimes both. Excel makes it easy to get the integer and somewhat harder to get the fraction. If you just want the answer, skip to the technical details.
The Integer Part: Excel INT Function
What could be easier than the Excel INT function? I mean INT almost screams INTEGER. So the name is intuitive. You almost “know” what it’s going to do, even if you haven’t used it before.
With only one argument, it’s execution is even simpler. Just feed it a number and … Read the rest
I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really.
The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) divided by two (2) equals two (2), with one (1) left over.
All numbers are evenly divisible by one (1) so the MOD function returns any fractional part when the second argument is one (1).
In the screen shot below, cell C2 has … Read the rest
I just upgraded my wife to a new iPhone 4S and since she’s finished with her contract, AT&T will now unlock her old iPhone 4.
Having an unlocked phone is advantageous when traveling overseas because you can pick up a Sim card with a phone plan and save some money. The question I want to answer here is, “Is it worth it?”
I’ve spent time in the UK and the best place to get a Sim card or even buy an inexpensive mobile phone is with O2. Great coverage, products, service, and you can find them practically … Read the rest
I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.
Conditional Drop Down List (Excel)
There are two named ranges,
that refers to the range E1:E3 and
that refers to range F1:G3.
A defined name, myItemListH, … Read the rest
As part of Shark Week I’ve committed to write something for VLOOKUP week. (It’s what I get for using twitter.) So without further ado.
I love the VLOOKUP Function in Excel. As the name implies, it’s a vertical lookup. Meaning the function will lookup data in columns.
The VLOOKUP Function Arguments
The VLOOKUP function has four arguments and in my opinion the fourth argument always gets overlooked, yet it’s the first thing you need to know. So, like reverse polish notation, we’ll start from the inside and work out to explain each argument.
The range lookup … Read the rest
I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.
Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.
Update Your List Range with VBA
… Read the rest
I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column of data.
The situation is depicted below. Cell C2 is active, and has the formula =B2+A2. I want to copy it down to the rest of the column in this data range. However, cells B6 and B11 are empty, along with countless others below the … Read the rest
I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.
Here’s what a US user sees:
From: 6/6/2011 to 6/10/2011
Here is the formula:
=”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)
The Min and Max dates are in column A on a worksheet named ExtractData.
The problem is trying to automatically change the date format in the second argument of the TEXT Function … Read the rest