atlas

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Posted on May 22, 2013 by Tom Urtis

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.

Tagged , , , | Leave a comment

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

Posted on May 21, 2013 by Tom Urtis

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),"[","")

Tagged , , , , , , | Leave a comment

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

Posted on May 20, 2013 by Tom Urtis

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.

Tagged , , , | Leave a comment

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

Posted on May 19, 2013 by Tom Urtis

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.

Tagged , , | Leave a comment

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

Posted on May 15, 2013 by Tom Urtis

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.

Tagged , , | Leave a comment

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

Posted on May 14, 2013 by Tom Urtis

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

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.

Tagged , , , , | Leave a comment

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

Posted on May 13, 2013 by Tom Urtis

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.

Tagged , , , , | Leave a comment

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

Posted on May 12, 2013 by Tom Urtis

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.

Tagged , , , | Leave a comment

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

Posted on May 7, 2013 by Tom Urtis

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)

Tagged , , , , , | Leave a comment

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

Posted on May 6, 2013 by Tom Urtis

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.

Tagged , , , | Leave a comment