Blog – Page 2 – Tom Urtis

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.

That example involved a formula with the TEXT function, meaning the date and time value in cell A3 was not a real date or real time, but a constant text value that was not unlike a text sentence such as you are reading now.

Common wisdom has it that you cannot perform mathematical functions on text dates. For example, if you try to add a 1 to the text value, you will return a #VALUE! error.

However, you start to have some luck with a formula to return all to the left of CHAR(10)…
=(LEFT(A3,FIND(CHAR(10),A3)-1)+0)+1
…which returns the serial number of (in this example) the date in cell C3 + 1, which is July 18, 2013.

All that remains is to format the cell for a readable date.
Right-click the cell and select Format Cells from the popup menu.

In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list box, select Custom.
• In the Type field, enter your desired date format, such as MMMM D, YYYY
• Click the OK button

Your final result would look like this — date math successfully perfomed on a text non date value.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Text and Constants, Tom Urtis

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

by Tom Urtis 15 Comments ↓

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Here’s a tip to place the date and time in the same cell, in separate lines.

The first step is to enter the formula
=TEXT(NOW(),"MMMM D, YYYY")&CHAR(10)&TEXT(NOW(),"HH:MM AM/PM")
The CHAR(10) notation refers to ascii character number 10, which is a carriage return character. In the cell, it looks like a small square, for example as pointed to by the red arrow.

Next,right-click the cell and select Format Cells from that pop-up menu.

In the Format Cells dialog box, click onto the Alignment tab. Click the dropdown arrow for the Horizontal field in the Text alignment section, and select Center.

Lastly, select the option for Wrap text, and click OK.

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

by Tom Urtis 6 Comments ↓

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.

To find the position of the first integer, the formula in cell B2 and copied down to cell B13 is
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Notice for example in cell B2, the formula returns 3. That is because cell A2 contains the alphanumeric string GS5453GDGD5. The first integer in that string is 5 and it is the third character (that is, in the third position) of that string.

To extract everything to the left of the first-found integer, the formula in cell C2, copied to cell C13 is
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

A final example, to extract all characters to the right of — while including — the first found integer, the formula in cell D2 and copied down to cell D13 is
=TRIM(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""))

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Find Replace Substitute, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

by Tom Urtis 9 Comments ↓

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did, you could simply do a VLOOKUP to find it), you can apply these array formulas as shown.

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Array formula to return the number itself:
=INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))

Array formula to return the cell address:
=ADDRESS(MATCH(MIN(ABS(C7:C18)),ABS(C7:C18),0)+ROW(C7)-1,3)

Posted in Tom's Tutorials for Excel
Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Find Replace Substitute, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

by Tom Urtis 5 Comments ↓

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list. It is especially handy when your source list is hundreds or thousand of rows deep.

In the pictured example, the formula in cell F2, which was copied down to cell F5 is
=OFFSET($C$2,ROW(C2)*6-6,0)

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

by Tom Urtis 20 Comments ↓

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Formula examples for hours, minutes, and seconds being added or subtracted from time.

Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)

Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)

Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)

Combination of Hours, Minutes, and Seconds
Example to add 3 hours, 16 minutes, and 48 seconds: =$B$1+TIME(3,16,48)
Example to subtract 3 hours, 16 minutes, and 48 seconds: =$B$1-TIME(3,16,48)

When you first enter your formulas, the range will look odd, similar to this:

To fix that, select the range, press Alt+O+E and format the range as Time.

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

by Tom Urtis 5 Comments ↓

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. The attempted entry in cell E6 is being rejected because it is not a date.

Step 1 of 4: Before you enter dates, set up your worksheet:
• Start by selecting the range where dates will be entered.
• Then, from your keyboard press Alt+D+L to show the Data Validation dialog box.

Step 2 of 4: In the Data Validation dialog box:
• Go to the Settings tab.
• Click the drop-down arrow for the Allow field.
• In the list of allowable settings, click to select Custom.

Step 3 of 4: Still in the Data Validation dialog box:
In the Formula field, enter your formula rule.
Notice the range being Data Validated (the selected range in Step 1) is E3:E16.
The Data Validation formula being used, with cell E3 as the active cell in that selection, is
=AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D")

Step 4 of 4: Still in the Data Validation dialog box:
• Click onto the Error Alert tab.
• Click to select the option for (that is, click to put a check in the box next to) “Show error alert after invalid data is entered”.
• For the Style field, click the drop-down arrow and select “Stop”.
• In the Title field, enter a short headline such as you see here, and as you see in practice in the picture at the top of this tutorial.
• In the Error message field, enter an informative explanation as to why the attempted entry is being rejected, and what the user should do to correct that action.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Data validation, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3, 21, or any numerical measure in your list of numbers; it need not be a measure of 5.

As you’ll see, the relative formula rule for the 5 highest numbers is
=$B3>=LARGE($B$3:$B$22,5)

If for example you wanted to identify the 8 lowest numbers, the formula would be
=$B3<=SMALL($B$3:$B$22,8)

Being a TRUE / FALSE rule, notice the subject cell's (B3 in this example) absolute reference for the column, because the list of numbers is in column B, and the relative reference for the row, because the list being conditionally formatted extends several rows.

To accomplish this, follow these steps:

Step 1 (all versions of Excel) - -
• Select the range of interest.
• Press Alt+O+D to establish your Conditional Formatting.

IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:

Step 2 (version 2003 or before) - - In the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 3 (version 2003 or before) - - In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 4 (version 2003 or before) - -
In the Conditional Formatting dialog box, click OK, and you are done.

IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:

Step 2 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box:
• In the "Show Formatting Rules for" field, select Current Selection.
• Click on the item labeled "New Rule".

Step 3 (version 2007 or after) - - In the New Formatting Rule dialog box:
• Select "Use a formula to determine which cells to format"
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 4 (version 2007 or after) - - In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 5 (version 2007 or after) - -
In the New Formatting Rule dialog box, click OK.

Step 6 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box, click OK, and you are done.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Conditional Formatting, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

I previously posted this example, of transposing a range by copying it, and selecting the Transpose method in the Paste Special dialog box.

There are plenty of projects that require an immediate transposition using a formula to avoid the burden of manual Copy and Paste Special for Transpose every time a header cell changes.

I showed an example formula on this page that uses the INDIRECT function to transpose a list from vertical to horizontal.

Today’s example shows a different formula solution that transposes a list from horizontal to vertical, using the INDEX and ROWS functions.

In the above picture, the formula =INDEX($B$2:$F$2,ROWS($A$3:A3)) is in cell A3 and copied down to cell A7.

The yellow cells in range B2:F2 hold the original header labels which, if changed, will be automatically reflected in the vertical list in range A3:A7.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Here is a formula to count the words in a sentence or string of text.
=IF(LEN(A2)=0,0,LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1)

The SUBSTITUTE function handles the possibility of the cell containing no text or value.

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

by Tom Urtis 14 Comments ↓

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Previously, I posted this example of extracting text to the left of the first space, or of some specified character.

When you need the first two words in a string, or text to the left of the second instance of some specified character, here is a formula to extract all text to the left of the second space. I added the SUBSTITUTE function for cases, as shown in cell A5 of the picture, when a trailing comma after the second word is not wanted.

The formula in cell B2, copied down to cell B5, is
=SUBSTITUTE(TRIM(LEFT(A2,FIND(" ",A2&" ",FIND(" ",A2)+2))),",","")

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

by Tom Urtis 9 Comments ↓

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, leaving the actual underlying value unaffected.

In this example, you want the numeral 0 to look like “zero”, as such:

• Select the range of interest. In the pictured example, that would be range B5:D16.
• From your keyboard, press Alt+O+E to show the Format Cells dialog box.
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter the custom format [=0]"zero";General
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tips and Tricks, Tom Urtis

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

by Tom Urtis 5 Comments ↓

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

When you have a worksheet with rows and/or columns that have been grouped…

…you can press your keyboard’s Ctrl+8 keys…

…to quickly and easily toggle to show or hide your Group and Outline buttons.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Filter Subtotal Group, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Establishing your Page Setup preferences can involve a lot of steps. You won’t want to repeat those same steps over and over for each worksheet where you’ll want the same preferences.

To show the Page Setup dialog box…

…If you are using Excel version 2003 of before, from the menu bar click File > Page Setup.

…If you are using Excel version 2007 or after, from the Ribbon, click onto the Page Layout tab. Click the small expansion button in the lower right corner of the Page Setup panel.

Here’s the Page Setup dialog box, where you set your preferences and click the OK button.

In this example, I established Page Setup preferences for Sheet1. When you want to copy the same Page Setup preferences to other worksheets, the first step is to select that original worksheet.

The next step is to press the Ctrl key on your keyboard, and use your mouse to select the worksheets as a group to which you want to apply the same Page Setup preferences. In this example, I am copying Sheet1’s Page Setup preferences to Sheet3, Sheet5, Sheet6, and Sheet8.

Next, show the Page Setup dialog box again. Here are two keyboard shortcut tips to do that:

With the desired worksheets selected as a group as shown above, simply click the OK button on the Page Setup dialog box.

Your final step is important to remember: ungroup the worksheets!

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Printing Page Setup, Tom Urtis, Worksheets Workbooks

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark number. For example, suppose you oversee a region of several retail stores, and you want to know if their total sales dollars is within a percentage range of your regional sales goal.

In the picture, the formula in cell F2 is
=IF(ABS(C22-F1)/F1<=0.07,"In range","Out of range")

The ABS function allows you to evaluate a "plus or minus" range of (in this example) 7% for the total regional sales goal that is in cell F1.

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

by Tom Urtis 37 Comments ↓

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.

Here is how you want to present your worksheet to your users, so they know where to enter data:

Users appreciate seeing exactly where they can — and should — enter data.
You can conditionally format unlocked cells with this boolean (True or False) formula:
=CELL("PROTECT",A1)=0

The above formula is what is being used in this example, shown in the following pictured steps.
As a quick aside, you can conditionally format locked cells with this True/False formula:
=CELL("PROTECT",A1)=1

Although not absolutely necessary, it is advisable 99.9% of the time that you protect your worksheet after you have installed the conditional formatting. This will ensure that users will only have access to the unlocked cells.

Step 1 — Select the entire used range.

Step 2 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Select the option for Locked.
• Click OK, to make sure all cells in that range are locked.

Step 3 — Select the range(s) where you want to unlock the cells for users to enter data.

Step 4 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Deselect the option for Locked.
• Click OK.

IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:

Step 5 (version 2003 or before) — Press Alt+O+D to show the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.

Step 6 (version 2003 or before) — In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 7 (version 2003 or before) —
In the Conditional Formatting dialog box, click OK, and you are done.

IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:

Step 5 (version 2007 or after) —
Press Alt+O+D to show the Conditional Formatting Rules Manager dialog box:
• In the “Show Formatting Rules for” field, select Current Selection.
• Click on the item labeled “New Rule”.

Step 6 (version 2007 or after) — In the New Formatting Rule dialog box:
• Select “Use a formula to determine which cells to format”
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.

Step 7 (version 2007 or after) — In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 8 (version 2007 or after) —
In the Conditional Formatting Rules Manager dialog box, click OK.

Step 9 (version 2007 or after) —
In the New Formatting Rule dialog box, click OK, and you are done.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Conditional Formatting, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

In yesterday’s tutorial, I posted this example to return the relative row number of an item in a list.

Today’s example offers a formula to return the actual row number of an item in a given range, regardless of where on the worksheet that range is. Oftentimes, your range of interest will not start on row 1 of the spreadsheet, so it takes a formula with functions that can identify the actual row number of the item you are looking for, wherever that range may be on your worksheet.

In the picture below, cell H2 contains the formula…
=MATCH(MAX(F5:F15),F5:F15,0)+CELL("Row",F5)-1
…to return the actual row number (12 in this example) of the maximum number in range F5:F15.

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.

For example, suppose you have a table of data such as that pictured below, where you want to know the row position in that table of the maximum annual expense item. As you see, the expense item is Payroll because its annual number is the largest in range F5:F15. True, the maximum number in the list resides on row 12 of the spreadsheet, but what you need to know is that maximum number’s position, relative to the range of interest.

In cell H2, the formula =MATCH(MAX(F5:F15),F5:F15,0) returns 8 because the 8th row (position) in the range of F5:F15 holds the maximum number.

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

Here’s how to return the lowest positive number in a list that has positive and negative numbers.

In the pictured example, the number 1 is returned in cell L3 because it happens to be the lowest number above par (in column C) in the list of this year’s Masters Golf Tournament final scores.

The array formula in cell L3 is =MIN(IF(C4:C50>0,C4:C50)).

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

by Tom Urtis 10 Comments ↓

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

Sometimes you’ll need to find a number in a list that is farthest from zero, where some numbers might be positive and others might be negative.

This task has 2 considerations:
1. You will need more than a simple MIN or MAX function.
2. You will want to decide how to display the number, either as…
(a) …its raw actual appearance in the list, be it positive or negative, or
(b) …its absolute representation as a positive-looking number even if that raw number is negative.

The first picture shows the raw actual number of -22 in cell E3, with this array formula:
=IF(AND(MIN(B4:B15)<0,MIN(ABS(B4:B15)))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))

EDIT ON JUNE 21, 2013:
See Jeremy’s comment below, in which he found my formula in cell E3 to be in error.
Jeremy’s formula, which is correct and which you should use instead of mine, is this array:
=IF(ABS(MIN(B4:B15))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))
Thank you Jeremy!
– Tom

In the next picture, the absolute number 22 is returned in cell H3 with this array formula:
=MAX(ABS(B4:B15))

Note, these formulas are both array formulas. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

You may know that the keyboard shortcut Alt+F reveals your list of most recently used files.

You can press Alt+T+O to show the dialog box to modify your list of viewable files.

If you are using Excel version 2003 or before:
• In the Options dialog box, click the General tab.
• In the Settings area, make sure a checkbox is next to “Recently used file list”.
• Enter or scroll for the number of viewable files. In 2003 or before, the maximum is 9.
• Click the OK button to confirm your selections.

If you are using Excel version 2007 or after:
• In the Excel Options dialog box, click the Advanced item in the vertical pane at the left.
• Scroll down to find the Display section.
• In the Display section, find “Show this number of Recent Documents”.
• Enter or scroll for the number of viewable files. In 2007 or after, the maximum is 50.
• Click the OK button to confirm your selections.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

by Tom Urtis No Comments ↓

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.

Finally, select your desired color from the palette and click OK. That’s it — you’re done!

When the sheet is active, its tab name will be underlined in the color you chose.

When the sheet is not active, you’ll see its tab in full color.

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

by Tom Urtis 5 Comments ↓

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

The formula that returns the active worksheet’s name is
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

The formula that returns the active worksheet’s full path and name is
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Did you ever want to watch your spreadsheet in two separate windows in real time, where in one window you can see its values, and in the other window you can see its formulas?

Here’s how you can accomplish this:

Step 1
With your sheet of interest active, press Alt+W+N to display your workbook in a new window.

Step 2
• Press Alt+W+A to show the Arrange Windows dialog box.
• In the Arrange Windows dialog box, I selected the Horizontal option. Click OK.

Step 3
Press Ctrl+~ (that’s the Ctrl key and the Tilde key) to show formulas in the new window.

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

You can press the Ctrl key on your keyboard while turning your mouse wheel forward to zoom in, or backward to zoom out.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

by Tom Urtis No Comments ↓

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. In the pictured example, an entry of Jewelry, Car, Boat, or House would not be allowed.

The following steps show how this can be done.

Step 1 of 5
Select the cell(s) where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example it is just one cell being data validated: =ISERROR(MATCH(A2,C2:C4,0))

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Data validation, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

by Tom Urtis No Comments ↓

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, AND that only numeric increments of a quarter of a number are allowed. For example, the entry of 6.75 or 3.25 are allowed, but 4.35 or 1.62 are not allowed.

The following steps show how this can be done.

Step 1 of 5
Select the range of cells where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(D5,0.25)=0

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

by Tom Urtis 6 Comments ↓

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.

In this first example, the active cell is C5 which displays what is in precedent cell G2.
Pressing Ctrl+[ takes you to cell G2.

It also works for going to a precedent cell on a different worksheet.
In this next example, active cell E3 on Sheet1 displays what is in cell A4 of Sheet3.
Pressing Ctrl+[ takes you to cell A4 of Sheet3.

This also works for going to the precedent cell of a different workbook. In this final example, active cell B3 on Sheet2 of Book1 displays what is in cell H6 on Sheet1 of Book2.
Pressing Ctrl+[ takes you to Book2 (which does not need to be open at the time — Excel will open it for you), Sheet1, cell H6.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

by Tom Urtis 3 Comments ↓

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.

The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).
It returns “Black Onyx” because that is what is in cell C15, corresponding to the fact that cell A15 contains the last number (a time, but still a number) in column A of that list.

The formula in cell F2 is =INDEX(A3:D14,MATCH(REPT("z",255),A3:A14,1),4).
It returns “4:04” because that is what is in cell D14, corresponding to the fact that cell A14 contains the last text item in column A.

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.
• Lookup in reverse (to the left) of the aforementioned numbers in a table.

The formula in cell G2 is =INDEX(A2:A27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell H2 is =INDEX(B2:B27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell I2 is =INDEX(C2:C27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell J2 is =MIN(D2:D27)

The formula in cell G3 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell H3 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell I3 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell J3 is =SMALL(D2:D27,2)

The formula in cell G4 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell H4 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell I4 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell J4 is =SMALL(D2:D27,3)

The formula in cell G7 is =INDEX(A2:A27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell H7 is =INDEX(B2:B27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell I7 is =INDEX(C2:C27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell J7 is =MAX(D2:D27)

The formula in cell G8 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell H8 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell I8 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell J8 is =LARGE(D2:D27,2)

The formula in cell G9 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell H9 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell I9 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell J9 is =LARGE(D2:D27,3)

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

by Tom Urtis 1 Comment ↓

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.

From your keyboard, press F5 to show the GoTo dialog box. Then click the Special button.

To select only cells with comments, select the Comments option in the GoTo Special dialog box, and click OK.

Here is an example of all comment-containing cells that are now selected.

To select all cells with data validation, in the GoTo Special dialog box, select the option for Data Validation, also select the option for All, and click OK.

Here is an example of all cells that are now selected which contain data validation. In column C, I validated the cells for only Male or Female to be entered. In column E, I validated the cells to be a date between January 1, 1930 and December 31, 1997.

Posted in Tom's Tutorials for Excel
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

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

by Tom Urtis 5 Comments ↓

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.

Before the copy and paste.

After the copy and paste. Notice the same formula in cell G2 as in cell B7.

Select the range and press Ctrl+H to show the Find and Replace dialog box.

In the Find what field, enter the = character.
In the Replace with field enter some unused character, for example the # character.

Select the formula cells, right-click the selection and click Copy.

Right-click the upper right corner destination cell, and click Paste.

Select the entire used range and press Ctrl+H again to show the Find and Replace dialog box.

In the Find what field, enter the # character to reinstate the formulas.
In the Replace with field enter re-enter the = character.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cut Copy Paste Clear Fill, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

by Tom Urtis 5 Comments ↓

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+' (the Ctrl and apostrophe keys), you can replicate that value or formula.

If the cell of interest contains a formula, and you only want to replicate that cell’s value but not its formula, you can press Ctrl+Shift+' (the Ctrl and Shift and apostrophe keys).

The following pictures illustrate this functionality.

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.

Step 1
Start by entering the function name, followed by the open parentheses character.
The function’s arguments tooltip appears as usual.

Step 2
Press Ctrl+Shift+A.

Step 3
The function’s arguments tooltip text now appears in the cell. The tooltip itself is still visible.

Step 4
I dragged the tooltip away from the cell in this example.
Notice I continued to enter my VLOOKUP function arguments in the cell, directly over the tip text.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

by Tom Urtis 3 Comments ↓

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

by Tom Urtis 3 Comments ↓

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Here’s a cool formula that you can plug into any cell, which will dynamically sum a list of numbers from the top of the list to the cell of the row the formula is in.

For example, in the picture, cell C11 holds the formula
=SUM(B$3:INDEX(B:B,ROW()))
which sums the numbers in range B3:B11.

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.

As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

You can use that same formula, =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""), and copy it across as such:

Here is the formula to list column letters vertically, as seen in the following picture:
=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num, basically what type of function you’re wanting to perform:
1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Filter Subtotal Group, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.

Based on the picture:

• Formula of only positive numbers summed: =SUMIF(B3:B15,">0")

• Formula of only negative numbers summed: =SUMIF(B3:B15,"<0")

• Formula of all numbers summed as if positive: =SUM(ABS(B3:B15))
Note, this last formula is an array formula. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

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

by Tom Urtis 4 Comments ↓

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.

Here is how to represent elapsed work times in decimalized fashion.

Step 1
• Enter and copy as needed the numeric formula, example =HOUR(C3-B3)+(MINUTE(C3-B3)/60).
• Select the formula range and right-click that selection.
• From the right-click popup menu select Format Cells.

Step 2
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Number.
• In the Decimal Places field, select your desired setting. I selected 2 decimal places.
• In the Negative Numbers field, I selected the black font style -1234.10.
• Confirm your format selections by clicking the OK button.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Here are formulas to return various dates of first and last days of a given month.

First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)

Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)

First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))

Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)

First day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+3,1)

Last day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+4,0)

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

by Tom Urtis No Comments ↓

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)

Tom’s Tutorials For Excel: Summing a Range Diagonally

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Summing a Range Diagonally

Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.

The array formula that accomplishes this in cell B17 is
=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

One note about this particular formula, in case you are wondering about the +9 portion. Because this is an array formula, the numerical range of rows and columns must equal each other in the summing process, as evidenced by the = operator in the formula. The range of interest is B11:F15, or to express it another way, from row 11 to row 15, and from column 2 to column 6. To satisfy the array’s requirement that elements be evaluated in equal numerical index terms, 9 is added to column 2 to equal 11 as a starting point, which is the same starting row number of the range’s upper left cell B11, that is, row 11. The array sums the elements in the range whose row and column indices equal each other. The +9 notation simply aligns the row and column elements to begin at the same number.

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

by Tom Urtis No Comments ↓

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, the large numbers represent the size of storage space kilobytes on computer hard drives. You’d like to see those numbers in a format that’s easy to comprehend, such as in gigabytes with one decimal and a “GB” suffix.

To make this happen, first, select the range of numbers, and right click the selection. From the popup menu, click to select Format Cells.

Next, in the Format Cells dialog box:
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter #,##0.0,,,"GB"
• Click the OK button.

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

by Tom Urtis No Comments ↓

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.

In the next picture, a monthly calendar of daily events is listed, with Fridays being conditionally formatted in yellow. The Conditional Formatting formula rule that produces this result is
=WEEKDAY($A3)=6.
Notice that the non-date cells in column B are also highlighted in yellow, by virtue of the absolute reference to column A in the formula.

To start, show the Conditional Formatting dialog box by pressing Alt+O+D.

Versions 2003 and before, Step 1 of 3
• Select “Formula is” from the drop down list.
• Enter your conditional formula as shown.
• Click the Format button.

Versions 2003 and before, Step 2 of 3
In the Format Cells dialog box:
• Click the Patterns tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2003 and before, Step 3 of 3
You return to the Conditional Formatting dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 1 of 5
In the Conditional Formatting Rules Manager dialog box, click New Rule.

Versions 2007 and after, Step 2 of 5
In the New Formatting Rule dialog box, click the option “Use a formula to determine which cells to format”. Then, enter your formula rule as shown, and click the Format button.

Versions 2007 and after, Step 3 of 5
In the Format Cells dialog box:
• Click onto the Fill tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2007 and after, Step 4 of 5
You return to the New Formatting Rule dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 5 of 5
You return to the Conditional Formatting Rules Manager dialog box, where you click the OK button to confirm all your choices.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Conditional Formatting, Data Science, Date Time, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

by Tom Urtis 2 Comments ↓

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession, in order to show a single one.

For example, suppose you want a custom footer to be This & That. When setting up your custom footer, you need to actually enter This && That.

Here are the steps.

Step 1

Step 2
In the Page Setup dialog box:
• Click onto the Header/Footer tab.
• Click the Custom Header or Custom Footer button. In this example I clicked Custom Footer.

Step 3
In the Footer dialog box, in the Center Section field, I entered This && That. Notice the two ampersand characters in immediate succession. Then, click the OK button.

Step 4
You will return to the Page Setup dialog to confirm the look of your custom footer text. I circled in red what it looks like in this example. Simply click the OK button to confirm your custom footer text.

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

by Tom Urtis No Comments ↓

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. In the next picture, you want to enter email addresses so you want to make sure the “@” character is included. The following steps show how this can be done.

Step 1
Select the range of interest, and from your keyboard press Alt+D+L to show the Data Validation dialog box.

Step 2
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 3
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =ISNUMBER(FIND("@",B2))

Step 4
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Data validation, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

Tom’s Tutorials For Excel: “Did You Know…”

by Tom Urtis 1 Comment ↓

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, then select next cell below it, and reproduce that value by pressing Ctrl+' (Ctrl + apostrophe). Note that the value itself is reproduced but not the formatting.

Did you know:
Did you delete a sheet by mistake? You can restore it by immediately closing the workbook without saving it. When you reopen the workbook, your sheet will be restored.

Did you know:
Editing shortcuts on your keyboard…
• Copy – Ctrl+C
• Cut – Ctrl+X
• Paste with option to repeat – Ctrl+V
• Paste without option to repeat – Enter

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cut Copy Paste Clear Fill, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

by Tom Urtis 2 Comments ↓

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.

In Excel version 2003, find the Text Box icon on the Drawing toolbar by clicking View > Toolbars > Drawing to show that toolbar, then click onto the Text Box icon as shown.

For Excel versions 2007 or after, click onto the Insert tab, click the Shapes button, and click onto the Text Box icon as shown.

As you see in the following pictures, draw the shape onto your worksheet, and select it. In the formula bar, enter a formula such as =A1, or whichever cell whose value you want the shape to show.

Whether the linked cell (A1 in this example) is meant for manual data entry, or it holds a formula, the shape will also show that cell’s contents.

Posted in Tom's Tutorials for Excel
Tags: ActiveX / Form Controls, Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Pictures Shapes Graphics, Tom Urtis

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

by Tom Urtis 4 Comments ↓

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, as seen in the next picture. The following steps show how this can be done.

Step 1 of 5
Select the range of cells where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(B3,5)=0

Note, this example validates numbers divisible by 5, but you can adjust the Data Validation formula to allow other divisible-worthy numbers. For example, if you want to only allow entry of numbers divisible by 3, your Data Validation formula would be =MOD(B3,3)=0.

CATEGORIES