Blog Archives

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: 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: 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: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data

Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data

Here are examples of how numbers or data can be extracted using the RAND function.
The examples and formulas listed below relate to the following picture.

Example 1: Randomly return a number between numbers,

Read more ›

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

Tom’s Tutorials For Excel: SUMIF With Character Count Criteria

Tom’s Tutorials For Excel: SUMIF With Character Count Criteria

With the question mark wildcard character, which represents one placeholder character of any kind, you can sum or count items in a list based on a minimum, maximum, or exact count of characters.

In the pictured example,

Read more ›

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

Tom’s Tutorials For Excel: Calculating Times Between Time Zones

Tom’s Tutorials For Excel: Calculating Times Between Time Zones

With the TIME function you can add or subtract times between time zones.
Then you can adjust the formula cells for a recognizable time format.

The formula in cell D3 copied down to cell D9 is
=IF(C3=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))

TimeDifferences_BeforeFormat_AfterFormat

Select the range,

Read more ›

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

Tom’s Tutorials For Excel: Counting All Data Types in a Range

Tom’s Tutorials For Excel: Counting All Data Types in a Range

Any data that you put in a cell will always fall into one of four types:
• Text
• Numeric
• Boolean (TRUE or FALSE)
• Error

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String

Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String

Here are formulas to strip away the last Nth characters from a string, where N is the count of characters you want to strip. Notice that if you have a list of items with varying locations of the character of interest,

Read more ›

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

Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Here are two formulas showing elapsed time in hours between a Start Time and an End Time.

In the first picture, the formula in cell E1 is =(B2-A2)*24.
You can format that cell as Number and set as many or few decimals as you like.

Read more ›

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

Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only

Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only

Here are formulas using the SUMPRODUCT function that sum a range of cells that reside in only the even-numbered or odd-numbered rows.

Summing only the even-numbered rows.
The formula in selected cell C21 copied to cell F21 is
=SUMPRODUCT(C4:C18,MOD(ROW(C4:C18)+1,2))

Summing only the odd-numbered rows.

Read more ›

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

Tom’s Tutorials For Excel: Verifying a Time as AM or PM

Tom’s Tutorials For Excel: Verifying a Time as AM or PM

Here’s how you can verify a cell’s time value as either morning (AM), or afternoon or evening (PM). This is useful if you encounter a cell whose formatting does not show the time itself, such as in the pictured example below.

Read more ›

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

Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells

Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells

Here’s how you can incrementally sum a range of cells. In the pictured example, each set of five cells in range D2:D26 are summed in range G2:G6.

The formula in cell G2 and copied down to cell G6 is
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*5,0,5,1))

You can do this with any incremental count;

Read more ›

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

Tom’s Tutorials For Excel: Extracting the Last Word in a Cell

Tom’s Tutorials For Excel: Extracting the Last Word in a Cell

When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that,

Read more ›

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

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates

The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1).

Read more ›

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

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative

I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted.

Read more ›

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

Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups

Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups

I previously posted this example of a reverse lookup, using the INDEX and MATCH functions.

The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return,

Read more ›

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

Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error

Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error

I previously posted this example with the SUMIF function for bypassing the #DIV/O! error.

Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function,

Read more ›

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

Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references

Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references

I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references.

Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut.

Read more ›

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

Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets

Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets

Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2,

Read more ›

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

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Here is an example for returning the first word in a cell, with a formula that can be easily modified to return all contents to the left of the first found specified character in a cell.

Read more ›

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

Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

I recently posted two examples of array constants:
how to avoid a lookup table for VLOOKUP, and how to average the 4 highest numbers in a list.

Here’s how you can sum all numbers in a list except for the two lowest or the two highest.

Read more ›

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

Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

I recently posted this example of an array constant to avoid a lookup table for VLOOKUP.

Today, an array constant is employed to collect — and then calculate the average of —

Read more ›

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

Tom’s Tutorials For Excel: Calculating the Date for Easter

Tom’s Tutorials For Excel: Calculating the Date for Easter

Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.

Specifically,

Read more ›

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

Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

When you have a mixed list of cells that contain negative numbers, positive numbers, text, and blanks, here is a formula to calculate the average for only the positive numbers.

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July,

Read more ›

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

Tom’s Tutorials For Excel: Formatting a Date With Text

Tom’s Tutorials For Excel: Formatting a Date With Text

Here’s how you can enter a date in a cell, and apply a custom date format to make the cell look as if it contains text. You can use that date in math functions elsewhere, as shown below.

Read more ›

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

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

When using a lookup function such as VLOOKUP, you can avoid a lookup table on a worksheet and insert the lookup elements directly into the formula itself. This is done by using an array constant which is shown by example in the pictured formula
=VLOOKUP(B4,{0,”D”;71,”C-“;72,”C”;76,”C+”;79,”B-“;

Read more ›

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

Tom’s Tutorials For Excel: Flipping a List

Tom’s Tutorials For Excel: Flipping a List

If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())

Read more ›

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

Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function

Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function

You can add a text notation to a worksheet formula by using the N function. This is useful when you want to look at a formula and know something about it by reading actual descriptive words.

Read more ›

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

Tom’s Tutorials For Excel: Entering a Static Date and Time

Tom’s Tutorials For Excel: Entering a Static Date and Time

As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.

Those functions belong to a category of volatile functions,

Read more ›

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

Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values

Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values

By pressing the Ctrl+~ keys on your keyboard, you can toggle between showing all the worksheet’s formulas or showing only values.

Read more ›

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

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.

Read more ›

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

Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function

Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function

Here’s a tip for showing Excel’s Help window for the function you are in the process of entering.

In the picture, the numbers in column C are wanting to be subtotaled. The SUBTOTAL function has 11 different mathematical argument numbers —

Read more ›

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

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Sometimes you want options in your VBA arsenal for requests to show results in ad hoc fashions.

In the picture, a table has several columns, each containing a varying count of numeric entries.

Read more ›

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

Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

In yesterday’s blog entry, I showed an example of using the Wingdings2 font style with Conditional Formatting to show a checkmark in a cell for completed items. Here’s how you can employ a progress bar using a the REPT function with the Wingdings font style.

Read more ›

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

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Here’s how you can automatically show a checkmark in a cell, using standard formatting for a font type, and Conditional Formatting to identify when the checkmark exists. There is no programming code involved; it’s all native Excel formatting.

Read more ›

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

Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula

Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula

Here’s how you can use Data Validation with the INDIRECT function in a single formula to quickly and easily view the contents of other worksheets in your workbook.

Suppose your workbook is the company budget,

Read more ›

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

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.

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: Formatting Cells Containing Formulas, Constants, or Nothing

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.

With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.

Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.

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: 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: 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: 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: , , , , , , , , , , , , ,