Keywords: Excel 365; XLOOKUP; horizontal; HLOOKUP; Data Validation; Conditional Formatting
Click here or the picture to download this free, safe, interactive workbook.
…
Blog Archives
Tom’s Tutorials For Excel: Using XLOOKUP for horizontal lookups.
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: 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: 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: 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: 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.
…
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.
…
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,
…
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.
…
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,
…
Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range
Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range
Here is how you can apply a Data Validation list for cells in one sheet, with that source list existing on another sheet. The process involves creating a named range for the source list,
…
Tom’s Tutorials For Excel: Commenting a Cell With Data Validation
Tom’s Tutorials For Excel: Commenting a Cell With Data Validation
As a practical alternative to using a comment for supplying information about a cell, consider using Data Validation instead. The advantage is, the user sees the popup message for directions or tips when they select the cell as they enter or edit data.
…
Tom’s Tutorials For Excel: Forcing entries in UPPER case
Tom’s Tutorials For Excel: Forcing Entries in UPPER Case
Here’s how you can force users to enter all alpha (letter) characters in UPPER case, using Data Validation and no programming code.
To make this happen, press Alt+D+L on your keyboard to show the Data Validation dialog box.
…
Tom’s Tutorials for Excel: Prevent Duplicate Entries
Tom’s Tutorials for Excel: Prevent duplicate entries
There are times you will be using Excel that duplicate entries could be disastrous such as lists of employee’s Social Security numbers, patient’s confidential medical records or student’s contact information information to name a few. Here Tom Urtis explains how to ensure that never happens:
If you are entering a list of values that are meant to be unique,
…
Tom’s Tutorials for Excel: Data Validation from Custom List
Tom’s Tutorials for Excel: Data Validation from Custom List
A special solution that Tom has in his ‘Most Wanted’ file.
Data Validation does not directly access Custom Lists, but with VBA you can establish Data Validation in a cell, with the list of drop-down items being that of one of your Custom Lists.
…
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 ›
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Data validation, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis