Blog Archives

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

In yesterday’s tutorial, I posted this example to return the relative row number of an item in a list.

Today’s example offers a formula to return the actual row number of an item in a given range,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.

Read more ›

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

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

Here’s how to return the lowest positive number in a list that has positive and negative numbers.

In the pictured example, the number 1 is returned in cell L3 because it happens to be the lowest number above par (in column C) in the list of this year’s Masters Golf Tournament final scores.

Read more ›

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

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

Sometimes you’ll need to find a number in a list that is farthest from zero, where some numbers might be positive and others might be negative.

This task has 2 considerations:
1. You will need more than a simple MIN or MAX function.

Read more ›

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

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

You may know that the keyboard shortcut Alt+F reveals your list of most recently used files.

You can press Alt+T+O to show the dialog box to modify your list of viewable files.

Read more ›

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

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Here’s a tip for newcomers to Excel, for the ability to color your worksheet tabs. This feature has been available starting with Excel version 2002.

Right-click the worksheet tab you want to color, and select Tab Color from the pop-up menu.

Read more ›

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

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

Read more ›

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

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Did you ever want to watch your spreadsheet in two separate windows in real time, where in one window you can see its values, and in the other window you can see its formulas?

Read more ›

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

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

You can press the Ctrl key on your keyboard while turning your mouse wheel forward to zoom in, or backward to zoom out.

Read more ›

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

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Here’s an example of using Data Validation to NOT allow a particular data item entry.

Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify.

Read more ›

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

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Data Validation is an excellent way to control data entry to meet a certain condition.

Suppose you want to insure that numbers entered in the yellow cells are OK for decimals,

Read more ›

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

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.

In this first example,

Read more ›

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

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.

The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).

Read more ›

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

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.

Read more ›

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

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

I previously posted this example of selecting only constants or formulas.

You can do the same with cells that contain comments or data validation.

Select the range of interest.

Read more ›

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

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.

Before the copy and paste.

Read more ›

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

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+’ (the Ctrl and apostrophe keys), you can replicate that value or formula.

Read more ›

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

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.

Step 1
Start by entering the function name,

Read more ›

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

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)

Recall,

Read more ›

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

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Here’s a cool formula that you can plug into any cell, which will dynamically sum a list of numbers from the top of the list to the cell of the row the formula is in.

For example,

Read more ›

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

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.

As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,””)

You can use that same formula,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num,

Read more ›

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

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.

Based on the picture:

• Formula of only positive numbers summed: =SUMIF(B3:B15,”0″)

• Formula of only negative numbers summed: =SUMIF(B3:B15,”

Read more ›

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

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.

Read more ›

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

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Here are formulas to return various dates of first and last days of a given month.

First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)

Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)

First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))

Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)

First day of the month,

Read more ›

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

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)

Read more ›

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

Tom’s Tutorials For Excel: Summing a Range Diagonally

Tom’s Tutorials For Excel: Summing a Range Diagonally

Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.

The array formula that accomplishes this in cell B17 is
=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))

Recall,

Read more ›

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

Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes

Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes

When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.

For example, in the Before and After comparison pictures,

Read more ›

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

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.

Read more ›

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

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession,

Read more ›

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

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Data Validation is an excellent way to control data entry to meet a certain condition.

In this example, suppose you want to insure that a specific character is included in a text entry for a range of cells.

Read more ›

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

Tom’s Tutorials For Excel: “Did You Know…”

Tom’s Tutorials For Excel: “Did You Know…”

Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.

Did you know:
You can enter any value in a cell,

Read more ›

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

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.

Read more ›

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

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Data Validation is an excellent way to control the entry of data in your worksheet to meet a certain condition. In this example, suppose you want to insure that numbers entered in the yellow cells are divisble by 5,

Read more ›

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

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Times by the Minute

Tom’s Tutorials For Excel: Rounding Times by the Minute

Here are 3 formulas that round a time to its nearest minute, or up to the next minute, or down to the previous minute. This example uses the ten best times of the 2012 Boston Marathon. You’ll notice the number 1440 in the first formula,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Here are 3 formulas that round an elapsed time by the 5-minute mark, in terms of nearest 5-minute mark, up to the next 5-minute mark, and down to the last (lower) 5-minute mark.

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Here are 3 formulas that round an elapsed time by the quarter-hour, in terms of nearest quarter-hour, up to the next quarter-hour, and down to the last (lower) quarter-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized quarter-hour segments,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Here are 3 formulas that round an elapsed time by the half-hour, in terms of nearest half-hour, up to the next half-hour, and down to the last (lower) half-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized half-hour segments,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.

Read more ›

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

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest quarter-hour.
• Up, to its next quarter-hour.
• Down, to its last quarter-hour.

Read more ›

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

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized hours,

Read more ›

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

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

When you want to sum or average a range of cells that need to each be rounded beforehand, here’s an efficient alternative to rounding each cell individally. In the pictured example, daily rainfall amounts with one decimal are to be summed,

Read more ›

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

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW function.

Read more ›

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

Tom’s Tutorials For Excel: Sorting Email Addresses by Domain and Recipient

Tom’s Tutorials For Excel: Sorting Email Addresses by Domain and Recipient

Yesterday, I posted this method for parsing the recipient and domain names from an email address.

As an example of why you would want to separate a recipient name from its domain name,

Read more ›

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

Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names

Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names

If you have a list of email addresses and you want to parse the recipient and domain names, you can do so with the following formulas as shown in the picture. Note that the formulas will take into account the varying lengths of recipient and domain names.

Read more ›

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

Tom’s Tutorials For Excel: Entering a Static Random Number

Tom’s Tutorials For Excel: Entering a Static Random Number

I previously posted these examples of data entry using random numbers.

In each of those examples, by design, the random numbers returned by the RAND function were volatile. That is, any change to worksheet data would recalculate those formulas,

Read more ›

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

Tom’s Tutorials For Excel: Ranking a List in Random Order

Tom’s Tutorials For Excel: Ranking a List in Random Order

Here’s an example of setting up your worksheet to randomly rank a list of items. In this first set of pictures, the list of names is randomly ranked three times, simply by hitting the F2 key on any cell in the worksheet,

Read more ›

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

Tom’s Tutorials For Excel: Sorting a List in Random Order

Tom’s Tutorials For Excel: Sorting a List in Random Order

Here’s how you can sort a table in a random order, instead of strictly as ascending or descending.

In the example, a company has 10 coveted parking spaces that are nearer to the office building.

Read more ›

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