This is an array formula and must be entered with Control + Shift + Enter. After you enter the formula in the first cell, drag it down and across to fill in the other cells. The gist of this formula is this: we are using the SMALL function to get a row number that corresponds to an “nth match”. Once we have the row number, we simply pass it into the INDEX function function, which returns the value at that row....

As the formula is copied down, it will return a “Happy Birthday!” when a birthday in column C matches the current date. Otherwise, the formula will return empty string (""), which displays as a blank cell. The long way The traditional “long way” solution to this problem is to use a formula like this: Working from the inside out, the logical test inside the IF function is based on the AND function: Inside the AND function, there are two logical expressions, one that checks the month, and one that checks the day: The MONTH function returns the month for a given date as a number (i....

A very common example of this is when you have names in two columns and you want to check if the names are exactly the same or what names are missing in one column. Comparing text in Excel is fairly easy and can be achieved with simple formulas. In this tutorial, I will show you how to compare text in Excel using simple arithmetic operators or the EXACT function. I will also cover how you can compare text in two columns to find out the missing text entries....

Let’s take a look. Let’s create a pivot chart that shows product sales by Region. Since we have a pivot table already, we’ll create a pivot chart first, and then start customizing. Go to the Options tab of the PivotTable Tools ribbon and click the PivotChart button. For chart styles, we’ll use a simple column chart. At this point, our chart is plotting the full set of source data. Let’s filter the pivot chart to show only certain products: Banana Chocolate and Orange Chocolate....

Let’s take a look. This pivot table shows total sales by Product. Let’s add the Date field as a column label, and then group to show sales by year. When you first add a Date field as a row or column label, Excel will break out the data for every value in the field. To group a date field, first select one of the dates. Then right-click and choose Group from the menu....

Notice that in the pic above, Morning is in the second row in the same cell. You may want to insert a line break in Excel when you have multiple parts of a text string that you want to show in separate lines. A good example of this could be when you have an address and you want to show each part of the address in a separate line (as shown below)....

Let’s take a look. Once a conditional formatting rule is defined, it can be revised or deleted. To see and edit a conditional formatting rule, select Manage Rules from the Conditional Formatting menu. By default the Manage Rules dialog box is set to the Current Selection. If you’ve already selected at least one cell that has the conditional formatting rule applied, the rule will appear automatically in the list. If not, switch the dropdown menu to This Worksheet....

Let’s take a look. When you add a label field to a pivot table, the items in that field are sorted alphabetically. For example, if we add Product as a row label to this pivot table. We get sales by Product, with all products listed alphabetically. If we remove the Product field and add the City field, we see sales by City, with cities in alphabetical order. This sorting is automatic for column labels as well....

When you apply conditional formatting with formulas, it can be hard to get the formulas to work properly, because you can’t see what happens to the formula when the rule is applied. You can think of conditional formatting as an “overlay” of invisible formulas that sit on top of the cells. When a formula in the overlay returns TRUE for a given cell, the formatting is applied. Formulas that don’t return TRUE (or the equivalent) do nothing....

Let’s take a look. Here we have a set of dates in column B of our table. Let’s start off by copying these dates to all columns. Let’s look first at the Short Date and Long Date formats. We can apply both of these date formats using the Number Format menu on the home tab of the ribbon. If we visit the Format Cells dialog box, we see that both the Short and the Long date formats are marked with an asterisk indicating that they are controlled by regional date and time settings in the operating system....

Let’s take a look. To start off, let’s copy the numbers in column B to the rest of our table. The format of all cells in the table is General, so let’s apply the Number format to columns C through H. The default settings for Number format are two decimal places, no commas to separate thousandths, and a standard minus sign for negative numbers. Let’s make adjustments to the Number format to match the headings in our table....

Syntax ISTEXT(Value) Value (required) – This is the value you want to test if it’s text. Example In this example we test a few values. Notice that even though a value can contain numbers the result is still text.

Syntax MONTH(Date) Date (required) – This is the date value that you want to return the month from. Example In this example we use the function on several dates in different formats. Notice the text values we try result in a #VALUE! error while numerical values outside of the range 1 to 2,958,465 result in a #NUM! error.

Many Excel users often end up creating multiple Pivot tables in the same worksheet, which is not a very organized way of doing things. But thanks to a readily available in-built option in Excel, you can move your existing Pivot table from the current worksheet to a different worksheet with a few clicks. In this short tutorial, I will show you the best ways to quickly move a Pivot table in Excel....

The results in column E are decimal values with the percentage number format applied. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns a decimal number for each city in the list. When these numbers are formatted with the Percentage number format, they are displayed as percentages....

And you can see we got Rank, Title, Director, Rating, Genre, and Runtime. So let’s use a Pivot table to answer some questions about this data, and my first question is: What are the top 10 films by rating? Okay, so there are the top 10 films in that list by rating. And the next question is more difficult, it’s: How do these films compare in terms of runtime…how many films are one hour, one-and-a-half hours, and so on?...

where “points” is the named range Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note the the RANK function by itself will assign the same rank to duplicate values, and skip the next rank value. You can see this in the Rank 1 column, rows 8 and 9 in the worksheet. The second part of the formula breaks the tie with COUNTIF: Note the range we give COUNTIF is an expanding reference: the first reference is absolute and the second is relative....

Syntax ROW(Range) Range (optional) – This is a single cell or range which you want the row number from. For ranges the row number of the upper left most cell will be returned. If no range is selected, the row in which the formula appears is returned. Example In this example we get the row number based on a single cell reference, no cell reference and a reference to a range of cells....

The formula returns only Tuesdays and Thursdays as the formula is copied down. If the start date in B5 is changed, new dates are dynamically generated. One of the the arguments provided to WORKDAY.INTL is called “weekend”, and controls which days are considered non-working days. The weekend argument can be provided as a number linked to a preconfigured list, or as a 7-character code that covers all seven days of the week, Monday through Saturday....

This setting controls the default for all new charts in all workbooks. Note that hen you just randomly select cell in a set of data, Excel will try to plot all data. If you only want to plot one series, you can hold down the control key and select just that data. Then Excel will only plot the data you’ve selected. Once you have a chart, there are a number of shortcuts you can use....