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,
…
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.
…
Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs
Here’s a tip for newcomers to Excel, for the ability to color your worksheet tabs. This feature has been available starting with Excel version 2002.
Right-click the worksheet tab you want to color, and select Tab Color from the pop-up menu.
…
Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number
Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.
…
Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes
When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.
For example, in the Before and After comparison pictures,
…
Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date
In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.
…
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: 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: 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: 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: 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.
…
Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell
If you have the date and time in a single cell, such as when you enter the NOW function, you can custom format the cell as MMM D, YYYY * H:MM AM/PM to separate the date (flush left inside the cell),
…
Tom’s Tutorials For Excel: Formatting Dates as Past or Future
Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture,
…
Tom’s Tutorials For Excel: Returning text with your formula’s numeric results
I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells.
…
Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.
I previously posted this example for highlighting the selected cell.
Today, here is how you can highlight not only the current cell, but the cell you’d selected before you selected your current cell.
…
Tom’s Tutorials For Excel: Entering New Lines and Bullets in a Cell
Here is how you can enter text on its own line in a single cell. In the picture, some food items have been entered into cell A2. As you can see in the pictures that follow,
…
Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds
Here is how you can take an elapsed time originally represented in Hours, Minutes, and Seconds, and show it as just Minutes, or as Minutes and Seconds. For example, the picture shows the top 10 finishing times in column B for runners of the 2012 Boston Marathon.
…
Tom’s Tutorials For Excel: Hiding Zeroes in Cells
Here’s a way to hide zeroes in cells, to improve the readability of your spreadsheet by making only the relevant non-zero numeric values visible.
Select the range of interest, right-click on that selection,
…
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.
…
Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only
Here’s how you can fill in a range of dates that only includes traditional workdays of Monday through Friday, while bypassing the weekend dates for Saturdays and Sundays.
Step 1
You start by entering your beginning date into a cell.
…
Tom’s Tutorials For Excel: Formatting Cell Contents as Invisible
Here’s how you can make a cell’s contents be invisible, while keeping the cell’s rows and/or columns unhidden. In the following pair of pictures, employee salaries are in column F, but you want them hidden while column F remains visible.
…
Tom’s Tutorials For Excel: Formatting Numbers in Decimalized Millions
When you have large numbers that you want to display in millions for easier reading, you can custom-format those cells as
#.0,,;#.0,,;0
…
Tom’s Tutorials For Excel: Formatting a Negative Number In Square Brackets
Here’s a quick method for displaying a negative number in square brackets.
The custom format is 0.00;\[0.00\]
…
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: 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,
…
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.
…
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.
…
Tom’s Tutorials For Excel: Adding Text With a Custom Format
There are times when you want to append text to a cell’s existing text, without using a helper column with a concatenation formula, and without any programming methods.
As you know, formatting a cell only changes the visual appearance of the cell;
…
Tom’s Tutorials For Excel: Formatting Numbers and Text With Custom Colors
Using the familiar Format Cells dialog box and your imagination, you can totally bypass Conditional Formatting to custom-color your numbers and text as you like.
In the above picture,
…
Tom’s Tutorials For Excel: Calculating Negative Time
Here’s an example for calculating the difference between times, and expressing a negative result when a larger time is subtracted from a smaller time.
In the picture, runners are listed for comparing their 1000 meter run times against the record time.
…
Tom’s Tutorials For Excel: Listing and Verifying Fonts
While you can press Alt+O+E to call the Format Cells dialog box and see what fonts are available, with the below macro named ListFonts you can see your fonts on a worksheet all at once, formatted as their font names.
…
Tom’s Tutorials For Excel: Blinking Cells and Flashing Objects
Free and safe downloadable workbook with blink examples
With VBA, you can make cells or their contents look as if they are blinking or flashing. This is not a popular tactic because, although amusing at first,
…
Automatically Show UPPER Case Text In Cells
If you want to make sure that text entries in lower case — even alphanumeric entries — will automatically be displayed in UPPER case as soon as the entry is made, here’s how to do it.
Let’s say you want range A1:A10 to always show upper case letters.
…
Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros
When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed.
…
Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text
When combining dates with words in a formula, dates don’t always look like dates in the result.
For example, the above picture shows a date in cell A1.
…
Tom’s Tutorials For Excel: Summing Time
In the pictures, the formula in cell B3 is =B2-B1 which returns the elapsed time in hours and minutes between the Start time and the End time. You want the visual value 6:45 (meaning six hours and forty-five minutes), but instead Excel gives you an elapsed time that looks like a time of day,
…
Tom’s Tutorials For Excel: Spreadsheet Passwords — The Facts About Protection
Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.
It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection,
…
Tom’s Tutorials for Excel: Sum and Count Cells By Color
To sum, count, or perform a mathematical operation on a range of colored cells, you will need VBA. Excel does not have a built-in native worksheet function to mathematically evaluate contents of colored cells, such as summing the numbers in all red-colored cells.
…
Tom’s Tutorials for Excel: Color the active cell, row, or column.
Here’s Tom Urtis’ Monday long tip. Any questions? Just leave us a comment.
Sometimes you want to see where you are on the worksheet, or highlight relevant ranges as you navigate,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
