Tutorials For Excel everyday with EVBA.info part 3[Free PDF ebook] - EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

## Saturday, October 26, 2019

Tutorials For Excel everyday with EVBA.info part 3[Free PDF ebook]

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

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.

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.
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
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”
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.
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.
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!
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
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.

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.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1