Blog Archives

Tom’s Tutorials For Excel: Getting Unique Items From a List

Tom’s Tutorials For Excel: Getting Unique Items From a List

In the following picture, you see a list of names, many of which are repeated 2, 3, or more times. Here’s how you can create a shorter list to show each unique item only once.

To create a list of unique names in column C based on the list of many repeated names in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Here’s a macro to locate the first and last rows and columns, and the ranges being occupied by your pivottable. The (0, 0) notation after the Address property statements is to omit the look of absolute “$”

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using an InputBox and Looping Dates for Copy and Sum

Tom’s Tutorials For Excel: Using an InputBox and Looping Dates for Copy and Sum

In January I posted this example for using AutoFilter for dates. AutoFilter is usually quicker and more efficient but I wanted to show how an example of looping through dates by their year.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filling Empty Cells With Value From Above

Tom’s Tips For Excel Videos: Filling Empty Cells With Value From Above

Sometimes you get data in a column which has a lot of blank cells that represent whatever text was in the last filled cell above them.

When you need to fill in hundreds or thousands of empty cells,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Tom’s Tutorials For Excel: Filtering Automatically On Cell Entry

Here’s a developer’s approach to make things easy for your users who need to filter a range of data, based on a value they enter in a criteria cell. A Worksheet_Change event is utilized on a worksheet that is designed for this purpose,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Here’s a formula solution to listing a noncontiguous list of items while skipping the blank cells.

In the picture, a list of items is in A1:A20, along with blank cells. In cell D1 and copied down as needed is this array formula to re-list what is in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Here’s an example of identifying the column, and then the value of the header in row 4 of that column, for the first cell in a row that is not a zero.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Here are a couple of shortcuts to quickly sum the totals for rows and columns in a data table.

The first pair of pictures shows what you have and what you want.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Here’s a method for looping through possible named ranges, and if they exist, compiling a unique list of items found among all those ranges.

I have the word “possible” here as a design wrinkle to demonstrate an option of convenience.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Crazy requests can arise when dealing with crazy data. Here you need to sum the first three numbers that appear in a list that contains numbers, text, and empty cells.

The array formula in pink-colored cell E2 is
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))

Recall,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Here are two examples of concatenating cells for whacky but not unheard-of situations.

The first example is alternating cells in column being concatenated in successive cells.
The formula in cell B4 and copied down to cell B21 is:
=OFFSET(A1,ROW()-1,)&IF(ISBLANK(OFFSET(A1,ROW()-1,)),””,” “)
&OFFSET(A2,ROW()-1,)
Note that I broke the single-line formula into two lines here,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Have you seen or asked yourself this kind of question before:

Why doesn’t my list of positive and negative numbers sum perfectly to zero like it should?

Setting the option for Precision as Displayed might solve the issue superficially,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets

Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets

Here’s a visual representation of how to compose a formula on one worksheet that performs a mathematical operation from data on several other worksheets. The numbers in cells J5:J11 on the January, February, and March worksheets are all summed in the Total_Quarter1 worksheet.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Monitoring Changes in Data With Conditional Formatting

Tom’s Tutorials For Excel: Monitoring Changes in Data With Conditional Formatting

Without any programming involved, you can monitor a workbook for cells that were changed from their original values.

In the first set of pictures, range B5:B8 is being monitored by Conditional Formatting in hidden columns D:E.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Returning a Formula’s Arguments

Tom’s Tutorials For Excel: Returning a Formula’s Arguments

I saw a question last year on one of the Excel newsgroups, asking for a way to return a formula’s arguments. I wondered, as you probably are, why this person wanted such a thing.

Still, I was intrigued at the question,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges

Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges

Here’s a way to structure a SUM function for operating on an intersection of rows and columns.

In the picture, there are row and column totals for the entire year’s Income budget. But suppose you only want to look at,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria

Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria

When you are faced with a LOOKUP task and the possibilities require too many nested “OR” operations, consider an array constant in your formula.

In the picture, a company is grouping its employees by the first initial of their last name.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Numbering a Dynamic List of Filled Items

Tom’s Tutorials For Excel: Numbering a Dynamic List of Filled Items

For situations such as lists that change often, with blank cells between topics that must be clearly indexed or numbered, here’s a formula that can do the job.

In the picture, a list of Excel training topics is being compiled.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Here’s how to transfer multiple selected items from an ActiveX ListBox onto your spreadsheet without intervening empty cells.

Private Sub cmdConfirm_Click()
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False

‘Declare variables for row and ListBox item index.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT

Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT

Here’s an example of using the INDIRECT and SUM functions to add numbers from the top of column B to the row where today’s date (or whatever date you specify) is found in column A.

The formula in cell E2 is
=SUM(INDIRECT(“B1:B”&MATCH(E1,A:A,0)))

If you want to bypass the helper cell for today’s date,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: SUMIF For Days Past Last Date

Tom’s Tutorials For Excel: SUMIF For Days Past Last Date

Here’s an example of two formulas working in tandem for a common goal.

In cell E1 is the formula
=LOOKUP(9.99999999999999E+307,A:A)
which returns the last number (in this case a date) in column A.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Horizontal SUMPRODUCT

Tom’s Tutorials For Excel: Horizontal SUMPRODUCT

Just a quick example of the SUMPRODUCT function being utilized for data arranged horizontally across a row, instead of the more usually-seen columnar arrangement.

The formula in cell B12 is =SUMPRODUCT((A7:L7=B10)*(A8:L8=B11))
The formula in cell B23 is =SUMPRODUCT((A18:L18=B21)*(A19:L19=B22))

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Ranking With Conditional Omission

Tom’s Tutorials For Excel: Ranking With Conditional Omission

Here’s an example of ranking a list while omitting an item for consideration. In blue cell E2 is the name of an employee to eliminate from consideration in the ranking process.

The formula in cell C5 and copied down to cell C20 is
=IF(A5$E$2,SUMPRODUCT(($A$5:$A$20″”)+0,(B5

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average

Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average

When you have a mish-mash of alphanumeric strings in a list, here’s how you can sum or average their corresponding numbers based on a partial string criterion.

The picture shows two examples with the criterion in cell B4.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Sets of Identical Items

Tom’s Tutorials For Excel: Summing Sets of Identical Items

Here’s a formula method to subtotal numbers in one column for each set of items in another column.

A company’s quarterly sales are listed, with varying salespeople each quarter. At each change in quarter in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

When you want to highlight more than just the single lowest (minimum) or single highest (maximum) value in a list, here’s how.

In the above picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Here’s how you can insert a row between changes in items in your table range. In the picture, you prefer your data to be sorted by Client Name in column A. To make the finished table more readable,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Here’s how you can attack a lookup action for a unique list of items, when the only difference among the characters in the list is their upper or lower case.

The criteria item to lookup is in blue-colored cell D5.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Companies and organizations often keep a list of their members’ birth dates, for purposes of sending out birthday greetings or managing employees’ birthdays with a complimentary day off work.

As the above picture shows,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Because you are reading this, chances are you’re the go-to person in your workplace who’s counted on to work miracles in Excel.

In the picture, an ill-advised data entry practice shows a quantity of 3 production items,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: SUMIF formula examples

Tom’s Tutorials For Excel: SUMIF formula examples

Here are examples of formula constructions with the SUMIF function.

When you want to refer to a cell in the formula, enclose the mathematical operators in quotes.
In the pictured Example 1, the formula is =SUMIF(A2:A30,”=”&C1,B2:B30)
In the pictured Example 2,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Your Worksheets

Tom’s Tutorials For Excel: Listing Your Worksheets

Here are a few ways to list the names of the worksheets in your workbook.

For starters, you can right-click on the sheet navigation buttons to the left of your sheet tabs, and a list of sheets will appear.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Here’s how you can format differences in a long list when comparing it to a shorter list.

In the picture, column A holds a comprehensive list of names, and column B contains a shorter list where some names are found in column A (colored green),

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell

Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell

Here’s an easy way to keep a running log of changes to a cell’s text.

Suppose you want your employees to enter an explanation or description of some kind into a cell regarding a topic on your spreadsheet.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries

Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries

In cases where duplicate entries are expected, you can easily keep a running count of each entry with a formula to tell you how many times the entry exists in your list. There is a number next to each entry to tell you how many times in the list that particular entry has thus far appeared.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Blinking Cells and Flashing Objects

Tom’s Tutorials For Excel: Blinking Cells and Flashing Objects

Free and safe downloadable workbook with blink examples

With VBA, you can make cells or their contents look as if they are blinking or flashing. This is not a popular tactic because, although amusing at first,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Organize Your Mailing List With Excel’s Text-To-Columns Feature

Tom’s Tutorials For Excel: Organize Your Mailing List With Excel’s Text-To-Columns Feature

If you are faced with a mailing list where each line of information was entered into a single cell, here’s how you can organize the list by separating it into columns.

Your original mailing list with info bunched into one cell.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Fill Formula to Last Used Row

Tom’s Tutorials For Excel: Fill Formula to Last Used Row

The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Match From List

Tom’s Tutorials For Excel: Match From List

The below picture shows one way to see if any member of a collection of smaller strings (what I call “Key Codes”) are found in a much longer Product Code string.

The formulas in the yellow cells search the entire Key Code range of A4:A8.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria

Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria

Sometimes you need to look up a value based on more than one criteria, a task for which VLOOKUP falls short. In the picture labeled Example 1, see how you you can use the INDEX and MATCH functions to invoke two criteria that return the price of a particular automobile.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The HLOOKUP Function

Tom’s Tutorials For Excel: The HLOOKUP Function

HLOOKUP is a function not as easily grasped nor widely used as its VLOOKUP counterpart, probably for good reason as most lists are vertical in nature. The picture shows an example of using the HLOOKUP function to find a lookup value (“Mar”

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Show Your Named Ranges

Tom’s Tutorials For Excel: Show Your Named Ranges

If you actually want to see your named ranges labeled on your worksheet, you can set your window’s Zoom to 39% or less. Here’s an example.

In this first picture is a company’s annual budget organized for Income,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The NPV (Net Present Value) Function

Tom’s Tutorials For Excel: The NPV (Net Present Value) Function

Excel’s NPV (Net Present Value) function calculates the sum of a series of net cash flows, each of which has been discounted to the present using a static discount rate. Future payments are regarded as negative values,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Advanced Filter is among Excel’s most powerful and versatile tools. This example shows how a formula can serve as the criterion for filtering a table, based on a separate list of criteria items.

In Picture #1,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

Here’s how you can perform mathematical operations when dealing with Boolean (TRUE or FALSE) values in a cell, for example to return count, sum and average values.

Notice in the formulas that the TRUE and FALSE criteria are not enclosed in double quotes.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rolling Averages

Tom’s Tutorials For Excel: Rolling Averages

Sometimes you want to know an average of the most recent instances of a recurring item. For example, some companies base their retirement pension plan on the average of an employee’s last three years of annual salary.

In this example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays

Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays

Array formulas are useful when calculations involve multiple criteria. In the following pictures, three examples show increasingly complex array formulas that, when you view them in order, can help you understand how array formulas are structured.

Recall,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: List and Count Unique Items

Tom’s Tutorials For Excel: List and Count Unique Items

Instead of creating a pivot table, you can use formulas to list and count unique items from a larger list. In the following pictures, a clothing store’s daily record of items sold is condensed into a list of unique items in column C,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Count Percentages in a Range

Tom’s Tutorials For Excel: Count Percentages in a Range

You can count how many percentage numbers fall within an upper and lower range. In the picture, a pair of lower and upper range percent values in cells D2 and E2 serve as criteria for your count.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: LOOKUP By MIN or MAX

Tom’s Tutorials For Excel: LOOKUP By MIN or MAX

You can do a lookup based on other worksheet functions such as MIN and MAX.

In Picture #1, the formula =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)) returns Isabella because her Sales Dollars number of $5,012 is the lowest (minimum) number in column B.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,