Excel Tips & Tricks
Part 1: Creative Use of the Go To Dialog Box (F5)
1. Range Selection Techniques (17 Examples)
Select a cell or range quickly without scrolling.
You want to select a specific number of cells in a row starting from a particular cell in a row. When the task involves a large number of cells, it is not easy to figure what cell should be the last cell in the row. You will have to turn on the RIC1 reference style (Tools>Options>Views).
For example, you need to select 75 cells from the cell C125. You turn on the R1C1 feature and figure the last (the 77th) column to select. Then you scroll down to C125 and across 75 cells to select the desired range. Lastly you turn off the reference style. What a chore! If you try to select the last filled (not just formatted) cell in a row where there are many filled cells interspersed by blank cells, then you will have more aggravation.
With our tips, however, you will find these tasks a breeze. You don't have to do any scrolling. You don't have to turn on/off the reference style. It takes only a few quick keystrokes!
We have 17 examples demonstrating this kind of techniques.
1) Select the last filled (not just formatted) cell in a row.
2) Select the last filled (not just formatted) cell in a column.
You have a long non-contiguous single column range made up of filled and unfilled cells and want to select the last filled cell without scrolling.
3) Select the last filled cell in a worksheet.
Do you want to select the last non-empty cell in your worksheet without scrolling?
The Excel's built-in method (Edit>Go To>Special>Last Cell) does not necessarily take you where you want. Press the F5 key and type "LC"* in the reference box and press the Enter key. As long as your last cell is not on the last row (65536), it will be selected in about 10 seconds. Although this method is a little slow compared to the built-in feature, it is always reliable.
4) Select a blank column next to an existing one-column range.
You have a long non-contiguous single column range made up of filled and unfilled cells and want to select a new blank range identical in size right to the filled range. You do not want to use the mouse to scroll all the way down and want to accomplish this task without using any macro or VBA code.
5) You want to select a particular number of cells, say 125 cells, across in a row beginning with the last filled cell in a row.
You do not want to turn on the R1C1 reference style setting to count the number of columns.
6) Select a range from a cell in a row to the last filled cell in the row.
7) Select the last N, say 5 cells to the left of the last filled cell in a row.
8) To select 10 cells to the right of the current cell in a row,
9) To select the entire column containing the last filled cell in a row,
10) Select a single row range from the active cell to a particular column, say the 15th column.
11) Select a specific number of columns, say, ten columns from a current column, say the column C.
12) Select a specific number of cells, say, 75 in a row from a particular cell, say C125.
13) Select a specific number, say 120 cells to the right of a particular cell, H5.
14) Select a specific number, say 10 columns to the right from a particular column, say the column H.
15) Select a specific number, say 15 columns to the right from a particular column, say the 25th column.
16) Select the cell with the maximum value in a given range, say a named range called "TestData".
17) Two methods to select multiple named ranges.
2. Find your VBA procedure quickly from your workbook window.
You need to look up your VBA procedure or Macro (whose exact name you don't remember) quickly from your workbook window. You find the Macro (Tools>Macro>Macros) dialog box inadequate because it does not list all your procedures or Macros. It excludes: any private procedure, any procedure requiring a parameter (argument), any function procedure, or any procedures in a module containing an Option Private Module statement.
Part 2: Alternatives to array formulas
You find Excel array formulas powerful yet intimidating.
The good news is that in many cases, you can convert your array formula into a regular formula and have the best of both worlds. We provide ten such examples (single formulas).
1) Cross tab table
You may think that this kind of table can only be built using only an array formula.
With a little ingenuity you can get the job done using an amazingly simple regular formula.
2) Rank, running sum, sort by group (9 formulas)
[click on image to enlarge]
Ranking by group, running summing by group, and sorting by group is tricky.
This screen shot demonstrates eight regular formulas and one array formula.
Those regular formulas substitute their array counterparts
Note that the list is sorted by region.
Formula # 1: Assigns sequential numbers to items within each region, using one function.
Formula # 2: Calculates running sum by region, using one function.
Formula # 3: Ranks items within each region, using one function.
Formula # 4: Sorts items within each region, using three functions.
Formula # 5: Sorts the overall list, using three functions.
Formula # 6: Reverses values in the Sales column, using three functions.
Formula # 7: Finds the top sales figure within each region using two functions.
Formula # 8: Finds the top three sales figures within each region using two functions.
Formula # 9: Calculates an average sales figure for each region and finds sales above the average within each region, using one function.
Note: You can use Columns J, K, L as your AutoFilter criteria to get respective information.
3) Transposing data using regular formulas
[click on image to enlarge]
This screen shot shows you how you can transpose your data using a regular formula.
The formula involves three built-in functions. You can move around the transposed range without affecting the data.
4) A two-column lookup
This screen shot shows how to use a regular formula to look up something based on values in two columns.
5) How many matches between two single column lists?
You normally would use this array formula:
{=COUNT(MATCH(Rng1,Rng2,0))}
However, we have a non-array solution.
6) How do I count the number of unique items in a range?
7) You want to calculate the average of sales of your top 3 salespersons in a single formula without using an array formula.
8) How do I get the sum of serial numbers 1 through 100?
9) What would be the cumulative principal payments for the first threes years on a 30 year $200,000 loan with a 6 % annual interest rate?
10) How many Fridays are there between 1/1/06 and 3/31/06?
11) Is a particular value in a range?
12) You have sales data by salesperson for two months and want to calculate the average of the changes in a single formula.
13) How do I calculate the sum of an integer in a cell?
14) Summing top three values in a range
15) Sum every second value in a range
16) Count the occurrences of a particular weekday in a given month of a given year.
Part 3: SUMPRODUCT
You find the SumProduct function powerful and versatile. However, you may have just scratched the surface. For example, you may be familiar with this kind of formula: {=SumProduct((Region="North")*(Month="Jan")*Sales)}.
Our workbook provides two examples where we put the function to work to solve much more complex problems.
Part 4: Other Useful Regular Formulas
1. Nth particular weekday in each month of a given year.
By selecting a year and a weekday number, you can dynamically get a list of dates on which the weekday falls by week/month for the year.
Regular formulas were used to demonstrate this technique.
2. Get random month names.
You may want to fill a range with random month names. Select the range and enter the following formula into the first cell
and press the Ctrl/Enter key combination:
Or better yet, name the formula (Insert/Define/Define) and enter the name into your range.
3. How do you know whether your list is unique?
Do you want to check whether your list is unique?
4. Do you need a formula to sum all the numbers above from a particular cell?
The range may include blank cells or text values. You will find this formula handy. You may insert or delete rows anywhere above the cell.
5. A formula returning month names (Jan through Dec).
You need to enter 12 month names into a range in a column or a row.
You may think that you have to write a macro for something like this.
However, our simple formula gets the job done.
6. A formula returning random dates(mm/dd/yyyy) between two dates.
7. A formula returning random month names.
8. A formula returning random alphabets (A-Z).
Part 5: Advanced Filter
1) Criteria
This screen shot demonstrates how to use complex computed criteria.
You can filter your list by group automatically by selecting a category.
If you want to restore your list, just select "Show All".
A few lines of VBA codes support this automation.
If you want to filter overall top three products, use the criteria for Filter # 2.
2) Copy filtered data to a different sheet.
Filtering your filtered data to a different sheet is tricky. Excel says it can't be done. We show you how to get around the problem step-by-step.
Part 6: Other Tips
1. Delete or change names quickly without using the Define Name (Insert>Name>Define) dialog box.
1) How to rename a filled range without deleting the contents of the range.
2) How to delete the name, keeping the contents of the range.
3) How to delete both the name and the contents of the range.
4) How to delete a name for an empty named range.
2. How to assign self-adjusting sequential numbers to items in your list.
You need to number items in your list sequentially. You sometimes delete items from your list or add new items to the list. You want those item numbers adjusted automatically every time you add or delete items.
3.How to make a replica of a (single) regular of array formula:
You are going to edit your formula and want to back it up.
You do not want the original cell references changed.
4. Navigation Tips in your VBE window:
5. How to locate a VBA procedure quickly in the VBE.
You have multiple public procedures scattered all over multiple standard modules and want to find a particular procedure quickly. Our tip comes in handy if you don’t remember the name of your macro.
We offer an unconditional 30-day Money Back Gurantee and free e-mail support! Your order is secured via RegNow SSL Secure Server (see RegNow Security/Privacy statement). You will be able to download the product immediately after ordering!
|
