Blog Archives

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: 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: Coloring Your Worksheet Tabs

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.

Read more ›

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

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

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.

Read more ›

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

Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes

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,

Read more ›

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

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

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.

Read more ›

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

Tom’s Tutorials For Excel: Rounding Times by the Minute

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,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

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,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

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,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

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,

Read more ›

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