Blog Archives

Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.

Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.

The Conditional formatting rule for unique entries in range A3:A14 is
=COUNTIF($A$3:$A$14,A3)=1

The Conditional formatting rule for non-unique entries in range B3:B14 is
=COUNTIF($B$3:$B$14,B3)1

The Conditional formatting rule for repeated entries in range C3:C14 is
=COUNTIF($C$3:C3,C3)1

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Download this Excel workbook that runs a PowerPoint presentation.
Download this accompanying PowerPoint presentation.

Here is how you can run a PowerPoint presentation from Excel, with the below macro named “PowerPointSlideshow”.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Here’s a shortcut to manually import an Access database table into your spreadsheet. It’ll come with the alternating shaded rows and field header drop-down arrows, but it’s fast and easy. The first picture shows an access table just for example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Showing Your Column Headers as Letters or Numbers

You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.

TTFE027a
TTFE027b
TTFE027c

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Knowing Any Workbook’s Last Saved Date and Time

You can get the last saved date and time for any workbook whether it is open or closed. In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time. In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Moving a Cell’s Lines of Text into Separate Columns

I previously posted this example showing how to enter separate lines of text into a single cell. This example shows how to move each line of a cell’s text into its own cell.

The first picture shows five cells in range A3:A7 that each have three lines of text for a person’s name,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Introduced in Version 2016, the “Tell me what you want to do” Field

Introduced in Excel 2016, you can click into the “Tell me what you want to do” field as shown in the picture, or press Alt+Q from your worksheet, to enter a topic for information. This is a fairly intuitive new tool, while saving the interim step of hitting the F1 key for help about a topic.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Introduced in Version 2016, the Multi-Select Icon for Pivot Table Slicers

Introduced in Excel 2016 is the Multi-Select icon for pivot table slicers, a welcome convenience to the heretofore requirement of pressing the Ctrl key to select multiple labels.
In the picture for example, I clicked the Multi-Select icon, then clicked to deselect the states of Arizona, Iowa, and Michigan from appearing in the pivot table’s Sum of Sales.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers

Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.
In the picture, the array formula in cell B14 that sums the San Francisco Giants’

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Customizing the Status Bar

Tom’s Tutorials For Excel: Customizing the Status Bar
You can right-click the status bar for additional edit, formatting, and analysis options.
TTFE0020a
The following picture shows a Before and After comparison of the additional information you can choose for the status bar to show. In this example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally
As the picture shows by example, you can horizontally transpose a vertical range at any cell outside the vertical range with the formula
=INDEX($A$2:$A$25,COLUMNS($A$2:A25))
Note the absolute and relative references.
TTFE0019

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher
To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner.
TTFE0018

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Hiding Your Named Ranges

Tom’s Tutorials For Excel: Hiding Your Named Ranges
In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.
TTFE0017a
The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Displaying a Column’s Width

Tom’s Tutorials For Excel: Displaying a Column’s Width
You can display a column’s width manually, or in a cell with a User-Defined Function. The first picture shows that when you press and hold your left mouse button between column headers, a column’s width appears in a tooltip.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns

Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns
Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.
The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells

Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells
The below picture shows a side-by-side comparison of summing and averaging the last 5 cells in a dynamic list, depending on if blank cells should or should not be included in the formula results.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays
When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges

Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges
Here is how you can verify if a matching value is found in both a horizontal and vertical range. In Picture #1 a match is not found, but in Picture #2 a match is found.
The formula in cell A1 is
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0=1,”Found”,”Not found”)
Conditional formatting is applied to cell A1 for two conditions.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using the Home key with Scroll Lock

Tom’s Tutorials For Excel: Using the Home key with Scroll Lock
Here are a few navigation tips using the Home key, with or without the Ctrl key, with or without Scroll Lock activated, and with Freeze Panes on or off.
TTFE0011a
TTFE0011b

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second
When you want to calculate differences in time when the measurements are in hundredths of a second, special consideration must be given for cases when the result is negative. The next pictures show examples of calculations when the Actual time is less than expected;

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Making Your Hyperlinks User-Friendly

Tom’s Tutorials For Excel: Making Your Hyperlinks User-Friendly
When you put hyperlinks in cells on a worksheet, it’s a good practice to present them in a user-friendly, intuitive way. For example, the pictures show an original list of hyperlinked cells with their Microsoft-induced wordy screen tips. The modified list was created by the below macro to show those same cells more intuitively,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store.

Read more ›

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

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once
You can Undo up to 100 previous actions on your workbook at the same time. Clicking the drop-down arrow next to the Undo button shows the list of your most recent actions, where you can select the point at which to undo the action(s) you want to reverse.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel
When you open Excel, here is how to bypass the Start screen when you prefer to see a new workbook instead.
TTFE4a
TTFE4b
TTFE4c

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria
I previously posted this example of using the spacebar character as the mathematical operator in a formula to sum numbers in the intersecting range of multiple rows and columns. Also is this other example of using a formula to lookup an intersecting value.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet
Here is how you can copy a specific pivot field and display its data on another worksheet. In the following pictures, only the columns of a pivot table’s Sales field are copied to Sheet2 so you can work with that Sales data independent of the original pivot table.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds
You can format, analyze, and lookup (in this example, reverse VLOOKUP with CHOOSE) times measured in milliseconds.
In the first picture, times for a swimming race are measured in minutes, seconds, and milliseconds. The times in range B4:B12,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Opening Excel in a New Instance

Tom’s Tutorials For Excel: Opening Excel in a New Instance
In Excel versions 2013 and later, new workbooks open in the same instance of Excel. Here are 2 options to open your workbook in its own instance.
Option 1
TET1a
TET1b
Option 2
TET1c
TET1d
TET1e

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria

Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria

When crunching numbers, there are many ways to slice and dice the Average onion, depending on what criteria you want to include, exclude, combine, or isolate.

The picture shows a list of positive and negative numbers,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals

Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals

When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.

Read more ›

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

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).

The picture shows three ways to apply this idea.

Read more ›

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

Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN, MAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.

Read more ›

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

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.

That example involved a formula with the TEXT function,

Read more ›

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

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Here’s a tip to place the date and time in the same cell, in separate lines.

The first step is to enter the formula
=TEXT(NOW(),”MMMM D,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.

Read more ›

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

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did,

Read more ›

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

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list.

Read more ›

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

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Formula examples for hours, minutes, and seconds being added or subtracted from time.

Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)

Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)

Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)

Combination of Hours,

Read more ›

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

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

One way to verify that a bona fide date is being entered into a cell is to use Data Validation.

In the pictured example, dates are being entered into a list in column E.

Read more ›

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

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3,

Read more ›

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

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

I previously posted this example, of transposing a range by copying it, and selecting the Transpose method in the Paste Special dialog box.

There are plenty of projects that require an immediate transposition using a formula to avoid the burden of manual Copy and Paste Special for Transpose every time a header cell changes.

Read more ›

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

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Here is a formula to count the words in a sentence or string of text.
=IF(LEN(A2)=0,0,LEN(A2)-LEN(SUBSTITUTE(A2, ” “, “”))+1)

The SUBSTITUTE function handles the possibility of the cell containing no text or value.

Read more ›

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

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Previously, I posted this example of extracting text to the left of the first space, or of some specified character.

When you need the first two words in a string,

Read more ›

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

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

There are times when you want to see a number as text in order to visually set it apart from other numbers, while maintaining its value as a number. A custom format can accomplish this, because formatting a cell’s value only affects what you see,

Read more ›

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

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

When you have a worksheet with rows and/or columns that have been grouped…

…you can press your keyboard’s Ctrl+8 keys…

…to quickly and easily toggle to show or hide your Group and Outline buttons.

Read more ›

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

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Establishing your Page Setup preferences can involve a lot of steps. You won’t want to repeat those same steps over and over for each worksheet where you’ll want the same preferences.

To show the Page Setup dialog box…

Read more ›

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

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark number.

Read more ›

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

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.

Read more ›

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

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