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: “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,
…
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.
…
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: 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,
…
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,
…
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,
…
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,
…
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,
…
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.
…
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.
…
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.
…
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,
…
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,
…
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.
…
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,
…
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.
…
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,
…
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,
…
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.
…
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,
…
Tom’s Tutorials For Excel: Inserting Numbered Rows of Varying Increments
As any Excel VBA guru of the workplace knows, it’s you who gets called upon to make sense of whacky downloaded data.
Here, your company’s data warehouse shoots out a file with numbered records in column A that vary because some records are empty.
…
Tom’s Tutorials For Excel: Formatting Numbers for Thousands or Millions
When you have a range of large numbers that can be difficult to comprehend because of their size, it helps to make the range more readable by formatting the numbers as thousands or millions.
The first step in formatting the cells is to select the range,
…
Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry
Here’s a tip for shortening the effort of entering recurring lengthy text.
Suppose there are words or phrases you need to type often in your spreadsheets, such as boilerplate legal text, or the name of your company.
…
Tom’s Tutorials For Excel: Setting 2007+ PivotTables for Drag and Drop
Excel’s PivotTable models changed starting with version 2007. If you miss the ability to drag and drop field items from the field list into a pivottable’s drop zones the way you could do in the good old days of versions 2003,
…
Tom’s Tutorials For Excel: Using The Legacy PivotTable Wizard in Versions 2007 and After
Are you using Excel version 2007, 2010, or 2013? Are you a pivottable builder who misses the legacy PivotTable and PivotChart Wizard? Instead of going the Ribbon’s route of clicking the PivotTable icon on the Insert tab,
…
Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets
Sometimes overlooked is Excel’s ability to Find and Replace across all worksheets in a workbook, without needing to select all the worksheets.
• Select any cell in any sheet.
…
Tom’s Tutorials For Excel: Filling Across Worksheets
I previously posted this example for making changes on all sheets at once.
Here’s a quick and easy solution when the changes you want to put on all worksheets have already been made to one worksheet.
…
Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign
Absent from Excel’s standard negative number formats is one that colors negative numbers red with a preceding negative (minus) sign. You need a custom format to make the Before picture look like the After picture,
…
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,
…
Tom’s Tutorials For Excel: Making Changes on All Sheets at Once
Here’s a time-saving tip for newcomers or casual users of Excel. When you need to make the same changes on all your worksheets, you only need to make the changes one time and still have every worksheet show those changes.
…
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: Careful! That was *Alt*+F11 !
From your active workbook, a fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. You can do this from any worksheet.
Careful, that was *Alt*+F11! The Ctrl key is commonly used in conjunction with other keys for keyboard shortcuts.
…
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),,))
Select the range,
…
Tom’s Tutorials For Excel: History Lesson — A Reserved Sheet Name
If you try to rename an Excel worksheet or chart sheet as History, you’ll be stopped in your tracks, as the following picture sequence shows.
The question is,
…
Tom’s Tutorials For Excel: Going Home — Using the Home shortcut key
The keyboard’s Home key by itself takes you to the cell in column A of your active cell’s row, or with Ctrl+Home takes you to cell A1. If Freeze Panes is being used, Home takes you to the Freeze Panes column of the active cell’s row,
…
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,
…
Tom’s Tutorials For Excel: Showing Text Only by Hiding Numbers
You can custom format a cell to show or hide only positive numbers, negative numbers, zeros, or text. Here, a range of cells is formatted to show only text with the custom format ;; (that is,
…
Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells
When you want to center a cell’s value across several columns, please avoid the Merge Cells method. Merged cells cause more programming and design headaches than any single feature in Excel.
There is a better way,
…
Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List
Here are formulas that return various attributes of the lengthiest value in a list. In the pictures are formulas for the lengthiest value itself; its length; its row in the list;
…
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,
…
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.
…
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.
…
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.
…
Tom’s Tutorials For Excel: Summing Every Nth Cell
Here’s an array formula that allows you to sum every Nth cell, where “N” is the interval number between cells. In the pictured example, you see that every 7 cells are summed in the budget worksheet with the array formula
=SUM(IF(MOD(ROW(B7:B28),7)=0,B7:B28))
You are not limited to the SUM function.
…
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;
…
Tom’s Tutorials For Excel: Entering Fractions In Your Text
While it’s true that you can insert fraction symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.
Here are three examples that show how you can use the keyboard to enter the common fractions for one-fourth,
…
Tom’s Tutorials For Excel: Random Ramblings
Here’s something to think about regarding random numbers in Excel.
Random numbers are based on a predictable algorithm. If you know the previous number and the algorithm, you can predict the next number generated by the random function.
…
Tom’s Tutorials For Excel: Entering International Currency Symbols
While it’s true that you can insert currency symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.
Here are four examples to show how you can use the keyboard to enter currency symbols in your non-numeric text entries.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
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.
…
Read more ›
Tags: Analytics, Big Data, Business Intelligence, Comments / Screen Tips, Data Science, Data validation, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis