Microsoft Excel Tips

[hmx_ryan]

Yup, I know that most of us are using Spreadsheets in our Daily Work in the Office or even in our Data... I'm going to share some tip and Tricks in Excel... I'll try my best to update it Daily... Hope you like it guys...

PS: I would like this thread to be a thread of Excel Tips only... If you like my Post, please, just press the Thanks Button Instead if posting a Thank you post... If you have something to share also, please, feel free to share and post it here..okies?

INDEX:
Summing Rounded Numbers
Finding the Largest Value from Two Different Lists, Subject to Specified Criteria
Saving Filtering Criteria
Calculating the Week Number
Copying Cell Content Across Sheets
Separating a Number Into Digits
Applying Colors to Maximum/Minimum Values in a List
Indicators for the maximal and minimal values
[NEW] Selecting Cells in Horizontal or Vertical Ranges
[NEW] Rounding Numbers that Meet Specified Criteria
[New] Retrieving Every nth Value In a Range

 

[hmx_ryan]

Summing Rounded Numbers

Summing Rounded Numbers ​

How Excel performs calculations:

Excel does not take number format into account when performing mathematical calculations. When performing a calculation, Excel operates on the entire number, using up to 15 significant digits, without considering the formatting.

Problem:

When the numbers displayed by the cells differ from the numbers Excel uses for calculations, there can be an apparent difference in the totals of those cells. For example, in cells B1 to B5 in the screenshot, the numbers were rounded based on their number formats. Notice that the total in cell B6 is not equal to the total of the displayed numbers, which is 16 (see explanations to the Array Formula in C6 in the screenshot at the last page of this Tip).

Solution 1:

Deleting Number (Permanent – No Undo!) 1. From the Tools menu, select Options. 2. Select the Calculation tab, and then select the Precision as displayed option button. All places after the decimal point are deleted in the cells. The numbers displayed in the cells are whole numbers, and the total is the sum of these whole numbers.

Disadvantage: There is no way to undo this operation, as the numbers have been permanently deleted.

Solution 2:

Use an Array Formula to Total Rounded Numbers To total numbers rounded to the nearest integer:
1. In cell C6, type =ROUND (see the screenshot in the first page of this Tip).
2. Press Ctrl+A for the Function Arguments dialog box, and in the first Number argument box of the ROUND formula, enter the range C1:C3.
3. In the Num_digits argument box, type 0 to round the number to the nearest whole digit and click OK.
4. After the equals (=) sign, type SUM.
5. Type ( to open the parentheses, press End, and then type ) to close the parentheses.
6. Press Ctrl+Shift+Enter to create an Array Formula (if the formula returns a #VALUE error, press F2, and then press Ctrl+Shift+Enter).

To total numbers rounded to thousands (see the screenshot at the bottom):

Follow the steps above and insert an Array Formula into cell B5, with -3 in the ROUND formula's second argument box.

 

[hmx_ryan]

Finding the Largest Value from Two Different Lists, Subject to Specified Criteria

Finding the Largest Value from Two Different Lists, Subject to Specified Criteria ​

Problem:

Columns A & B contain two lists of numbers.
We want to create a formula that will return the maximum value from either list, providing that the following two conditions are satisfied:
Numbers from List1 must be larger than 3.
Numbers from List2 must be larger than 20.

If the maximum value found does not meet the above criteria, the formula should look for the next largest number until finding the largest number matching criteria.

Solution: Use the MAX functions in the following Array Formula:
{=MAX(MAX((A2:A5>C2)*A2:A5),MAX((B2:B5>C3)*B2:B5))}

 

[hmx_ryan]

Saving Filtering Criteria

Saving Filtering Criteria ​

To save filtering criteria, use Custom Views:

Step 1: Add the Custom Views icon to the toolbar

1. Place the mouse over one of the toolbars, right click, and select Customize from the shortcut menu.
2. Select the Commands tab.
3. From Categories, select View and drag the Custom Views icon to the Menu bar.

Step 2: Save the filtering criteria

1. Select a cell from the column containing the criteria you want to Filter with.
2. Click the AutoFilter icon (see Tip #261).
3. Type any text criteria you want into the Custom Views box and click twice to save.
4. Repeat the previous step and save as many filtering criteria as needed from any sheet within the workbook.

Note:

Save as many filtering criteria in Custom Views as needed.

 

[hmx_ryan]

Calculating the Week Number

Calculating the Week Number​

To calculate a week number:

Use the WEEKNUM function.

The second argument in the WEEKNUM function, Return_type, determines on which day the week begins, for example, 1 for Sunday, 2 for Monday.

Note: This function is included in the Analysis ToolPak add-in. Users of Excel 97 and 2000: It is highly recommended that this Add-In be installed.

To install the Analysis ToolPak Add-In:
1. From the Tools menu, select Add-Ins.
2. Select the Analysis ToolPak checkbox, and then click OK.

 

[hmx_ryan]

Copying Cell Content Across Sheets

Copying Cell Content Across Sheets​

To fill across sheets (that is, copy information from selected cells to all sheets in the workbook), simultaneously:

1. Copy range of cells.
2. Right-click the sheet tab, and from the shortcut menu, select Select All Sheets.
3. From the Edit menu, select Fill and then Across Worksheets.
4. In the Fill Across Worksheets dialog box, check one of the three option buttons and click OK.
5. To remove sheet groupings, select the sheet tab, hold Shift and then click.

OR

Right-click the sheet tab and select Ungroup Sheets from the shortcut menu.

 

[hmx_ryan]

Separating a Number Into Digits

Separating a Number Into Digits​

Problem:

Separating the number in A2 into digits, so that each of cells B2:G2 will contain one digit.

Solution:

Using the MID and COLUMN functions, as follows:
=MID($A2,COLUMN()-1,1)

 

[hmx_ryan]

Applying Colors to Maximum/Minimum Values in a List

Applying Colors to Maximum/Minimum Values in a List​

To apply colors to maximum and/or minimum values:

1. Select a cell in the region, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A) to select the Current Region.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is, and type =MAX($F:$F) =$F1.
4. Click Format, select the Font tab, select a color, and then click OK.
5. In Condition 2, select Formula Is, and type =MIN($F:$F) =$F1.
6. Repeat step 4, select a different color than you selected for Condition 1, and then click OK.

Note: Be sure to distinguish between absolute reference and relative reference when entering the formulas.

 

[hmx_ryan]

Indicators for the maximal and minimal values

Indicators for the maximal and minimal values​

Problem:

Creating a formula that will indicate the maximum and minimum values in List1 by returning ""Max Value"" or ""Min Value"" in the cell next to them.

Solution:

Enter the following IF statement, using the MAX and MIN functions, in column B:
=IF(A2=MAX($A$2:$A$8),""Max Value"",IF(A2=MIN($A$2:$A$8),""Min Value"",""""))

 

[lantis]

Re: Indicators for the maximal and minimal values

Thanks! sana marami kapang tips.

 

[hmx_ryan]

Selecting Cells in Horizontal or Vertical Ranges

Selecting Cells in Horizontal or Vertical Ranges
​

Using keyboard shortcuts

To select a range of cells:
• Vertically from top to bottom, press Ctrl+Shift+Down Arrow.
• Vertically from bottom to top, press Ctrl+Shift+Up Arrow.
• Horizontally from left to right, press Ctrl+Shift+Right Arrow.
• Horizontally from right to left, press Ctrl+Shift+Left Arrow.

To select cells one after another:
• Press Shift+ Arrow.

Using the mouse

Select a cell in a range, press the Shift key and double-click the edge of the selected cell when the mouse image changes to four directional arrows.

 

[hmx_ryan]

Rounding Numbers that Meet Specified Criteria

Rounding Numbers that Meet Specified Criteria
​

Problem:

Rounding all the numbers in column A to zero decimal places, except for those that have "5" in the first decimal place.

Solution:

Use the IF, MOD, and ROUND functions in the following formula: =IF(MOD(A2,1)=0.5,A2,ROUND(A2,0))

 

[hmx_ryan]

Retrieving Every nth Value In a Range

Retrieving Every nth Value In a Range​

Problem:

Creating a new list (List2), containing every 3rd value from List1 (Column A).

Solution:

Using the INDEX function as follows:

=INDEX($A$2:$A$13,(ROW()-2)*3+3)

Enter the formula in the first cell of List2, and copy it down.

 

[simpleng_tao_lang]

baka po meron k nitong tuts copy as pdf or kht .rar file

pahingi naman po bossing!

 

[gamerdad7]

ganda nito TS salamat! ask ko lang bro kung may tips ka sa vlookup, conditioning formula, etc kasi kelangan ko sa work. thanks!

 

[ramlei]

Hi sir/ma'am,
Meron po ba kayo ng Unviewable+. Ginagamit po sya para masecure ang vba codes. Madali lang po kasi macrack kapag ginamit ang password protect sa excel. Thanks