Keywords: Excel 365; FILTER; if_empty; AND; Asterisk; OR; Plus +
Click here or the picture to download this free, safe, interactive workbook.
…
Blog Archives
Tom’s Tutorials For Excel: Explaining Excel’s FILTER function to extract multiple “or” criteria records, by using the plus + operator.
Keywords: Excel 365; FILTER; if_empty; OR; Plus +
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Explaining Excel’s FILTER function to extract multiple “and” criteria records, by using the asterisk * operator.
Keywords: Excel 365; FILTER; if_empty; AND; Asterisk
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Using XLOOKUP for horizontal lookups.
Keywords: Excel 365; XLOOKUP; horizontal; HLOOKUP; Data Validation; Conditional Formatting
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Conditional Formatting, Data validation, Excel 365, HLOOKUP, horizontal, XLOOKUP
Tags: Conditional Formatting, Data validation, Excel 365, HLOOKUP, horizontal, XLOOKUP
Tom’s Tutorials For Excel: Showing a double XLOOKUP (XLOOKUP with nested XLOOKUP) for an intersecting horizontal with vertical lookup.
Keywords: Excel 365; XLOOKUP; Intersect; Row; Column; Data Validation; Conditional Formatting
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Column, Conditional Formatting, Data validation, Excel 365, Intersect, Row, XLOOKUP
Tags: Column, Conditional Formatting, Data validation, Excel 365, Intersect, Row, XLOOKUP
Tom’s Tutorials For Excel: Using XLOOKUP’s match_mode argument for lookups with mathematical operations for one formula.
Keywords: Excel 365; Excel 365; XLOOKUP; match_mode; math
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Showing a double XLOOKUP (XLOOKUP with nested XLOOKUP) for an intersecting horizontal with vertical lookup.
Keywords: Excel 365; Excel 365; XLOOKUP; Intersect; Row; Column; Data Validation; Conditional Formatting
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Column, Conditional Formatting, Data validation, Excel 365, Intersect, Row, XLOOKUP
Tags: Column, Conditional Formatting, Data validation, Excel 365, Intersect, Row, XLOOKUP
Tom’s Tutorials For Excel: Showing XLOOKUP’s optional sixth argument named search_mode and its four search options for reverse (right-to-left) and forward (left-to-right) lookups.
Keywords: Excel 365; XLOOKUP; search_mode; Binary; Conditional Formatting
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Binary, Conditional Formatting, Excel 365, search_mode, XLOOKUP
Tags: Binary, Conditional Formatting, Excel 365, search_mode, XLOOKUP
Tom’s Tutorials For Excel: Exampling XLOOKUP for combined wildcard lookup_values of question mark “?” and asterisk “*” characters for reverse (right-to-left) and forward (left-to-right) lookups.
Keywords: Excel 365, XLOOKUP, match_mode, Wildcard, Question, Asterisk
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Asterisk, Excel 365, match_mode, Question, Wildcard, XLOOKUP
Tags: Asterisk, Excel 365, match_mode, Question, Wildcard, XLOOKUP
Tom’s Tutorials For Excel: Using XLOOKUP with the asterisk “*” wildcard character for reverse (right-to-left) and forward (left-to-right) lookups.
Keywords: Excel 365; XLOOKUP; match_mode; Wildcard; Asterisk
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Using XLOOKUP with the question mark “?” wildcard character for reverse (right-to-left) and forward (left-to-right) lookups.
Keywords: Excel 365; XLOOKUP; match_mode; Wildcard; Question
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Explaining the XLOOKUP function’s if_not_found optional fourth argument for reverse and forward lookups.
Keywords: Excel 365; XLOOKUP; if_not_found
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Understanding the XLOOKUP function’s match_mode optional fifth argument for exact and inexact matches.
Keywords: Excel 365, XLOOKUP, match_mode
Click here or the picture to download this free, safe, interactive workbook.
…
Tom’s Tutorials For Excel: Explaining the XLOOKUP basics: reverse (right-to-left) and forward (left-to-right) lookups.
Keywords: Excel 365; XLOOKUP; Conditional Formatting; Data Validation
Click here or the picture to download this free, safe, interactive workbook.
…
Posted in Tom's Tutorials for Excel
Tags: Conditional Formatting, Data validation, Excel 365, XLOOKUP
Tags: Conditional Formatting, Data validation, Excel 365, XLOOKUP
Tom’s Tutorials For Excel: Explaining Excel’s XLOOKUP Basics
Explaining the basics of Excel 365’s XLOOKUP worksheet function.
To see the enlarged image, right-click on it and select “Open Image in New Tab”.
…
Tom’s Tutorials For Excel: Filtering for the literal question mark “?”, asterisk “*”, and tilde “~” wildcard characters.
Filtering for the literal question mark “?”, asterisk “*”, and tilde “~” wildcard characters, by using a preceding tilde.
…
Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart
Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart
Here is a step by step example of how to program a pivot table and pivot chart, including how to place those objects exactly where you want them on your worksheet.
The programming code is in the downloadable workbook,
…
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
…
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”.
…
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,
…
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.
…
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.
…
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,
…
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.
…
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.
…
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’
…
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.
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,
…
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.
…
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.
…
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.
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.
…
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.
…
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.
…
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.
…
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.
…
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.
…
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.
…
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;
…
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,
…
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.
…
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.
…
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.
…
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.
…
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.
…
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.
…
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,
…
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
Option 2
…
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,
…
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.
…
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.
…
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.
…
