Copy and Paste Multiple Items Quickly
Easily Save, Navigate, and Open Files
Add New Data Without Reformatting Cells
Analyze Data More Effectively with PivotTables and Pivot Charts
E-mail Your Data Without Leaving Excel
Create High-Impact Reports with Custom Cells
Work Collectively with Web Components
BY ANY OTHER NAME: SAVING WORKBOOK SETTINGS
SAVE USER SETTINGS TO THE REGISTRY
OPENING A WORKBOOK IF IT ISN'T OPEN ALREADY
USING EXPONENTIATION IN FORMULAS
BE EXACT WHEN COMPARING STRINGS
FORGETTING FUNCTION ARGUMENTS?
AVOID PROBLEMS WITH MERGED CELLS
CREATING HYPERLINKS IN A WORKSHEET
TAKE COMMAND OF BUTTON PROBLEMS
PUT THE FULL FILENAME IN THE FOOTER
USING THE OFFSET WORKSHEET FUNCTION
THESE FUNCTIONS KNOW THEIR NAMES
GET UPDATED ON THE LATEST TRENDS
A LIST BOX WITH A DYNAMIC NAMED RANGE
CONVERT FORMULAS TO VALUES IN THE BLINK OF AN EYE
See how your changes look without having to deselect highlighted cells. The new See-Through View lightly shades selected cells rather than reversing them out, so you can make formatting changes and immediately see if you've achieved your desired results.
Technical Tips © KRMS Productions 2000
Copy and Paste Multiple Items Quickly
Collect and Paste makes it easy to work with multiple pieces of information from Excel or any other Office application. Copy up to 12 pieces of information from any Office documents, and then paste them, either one at a time (in any order) or all at once, into your spreadsheet or another document. No more jumping back and forth to copy and paste information between different documents.
Technical Tips © KRMS Productions 2000
Easily Save, Navigate, and Open Files
The new File Open/Save dialog box gives you one-click access to your most commonly used folders and locations, making it easier to save, navigate, and open files in various locations. The new dialog box also includes the History Folder, which contains shortcuts to your most recently accessed documents.
Technical Tips © KRMS Productions 2000
Add New Data Without Reformatting Cells
Add new columns and rows without having to manually apply formatting and formulas. The new List AutoFill feature intelligently applies existing column or row formulas and formatting to new fields.
Technical Tips © KRMS Productions 2000
Analyze Data More Effectively with PivotTables and Pivot Charts
Add and remove data fields directly on your worksheet to view data exactly the way you want to-it's easy with the improved PivotTable® interface. And use the new PivotTable AutoFormat feature to easily create professional-quality reports.
New PivotChart™ views offer powerful, visual representations of your data that can easily be manipulated for further analysis. Because PivotChart and PivotTable views are linked, they are updated in tandem as you change or move fields.
Technical Tips © KRMS Productions 2000
Use the Office 2000 Save As Web Page feature to save your work in HTML file format. This allows anyone with a Web browser to view your Office document and see the same rich quality as the original itself displays. You can also edit Web pages created in Excel using Office Web Editing-simply "round-trip" your document back into Excel, where you can update it with full program functionality.
Technical Tips © KRMS Productions 2000
E-mail Your Data Without Leaving Excel
Integration with the rest of Office 2000 makes it easy to share your work. Just click the new Office E-mail button on your toolbar to open the Microsoft Outlook® Mail header, and then e-mail your document "as is," right from Excel. Your document is sent in HTML format, which allows any recipient to view it. Recipients with Excel can open your spreadsheet in the application for further analysis.
Technical Tips © KRMS Productions 2000
Create High-Impact Reports with Custom Cells
Rotate and indent text within a cell, even merge cells, without tricky formatting. Custom cells make dynamic spreadsheet presentation a breeze.
Technical Tips © KRMS Productions 2000
Work Collectively with Web Components
Excel and Office 2000 bring you the power of Office Web Components, providing Web viewers the ability to manipulate spreadsheet, chart, and PivotTable data from within their browser for on-the-spot data analysis. Simply click the Add Interactivity button when you Save As Web Page.
Technical Tips © KRMS Productions 2000
Set up Web Queries to import real-time data-such as stock quotes and currency exchange rates-from the Web into your spreadsheet or database. Excel 2000 walks you through the steps of creating new Web queries, and allows for automatic refreshes.
Technical Tips © KRMS Productions 2000
If you find you've overestimated or underestimated just how big a range of data needed to be, you can take advantage of the Resize property of a range. Like the Offset property we talked about last time, Resize is an important part of Excel VBA, and it's something you should be familiar with. The Resize property adjusts a specified range to a determined number of rows and columns. It takes two arguments (RowSize and ColumnSize). You must supply at least one of the arguments, but you don't have to supply both. If a RowSize or a ColumnSize is not specified, the range will retain the current number of rows or columns. Here are some examples to try out: Sub SelectFourByFourRange() ActiveCell.Resize(4,4).Select End Sub Sub SelectThreeByFiveRange() ActiveCell.Resize(3,5).Select End Sub Sub MakeTheSelectionThreeColumnsWide() Selection.Resize(,3).Select End Sub
Technical Tips © KRMS Productions 2000
BY ANY OTHER NAME: SAVING WORKBOOK SETTINGS
Here's a way to save values to a workbook without writing them to worksheet cells. You may have used named ranges before, but this time let's use names that refer to values instead of ranges. These names and their values are stored inside the workbook. The following VBA procedures illustrate how to create these kinds of names and how to retrieve values from them. Sub WriteValuesToNames() 'Store values in workbook names ActiveWorkbook.Names.Add _ Name:="Customer", _ RefersTo:="Acme Inc", _ Visible:=False ActiveWorkbook.Names.Add _ Name:="InvoiceNo", _ RefersTo:=1536, _ Visible:=False End Sub Sub ReadValuesFromNames() 'Declare variables Dim Customer As String Dim InvoiceNo As Long 'Retrieve values from names to variables Customer = Evaluate(ActiveWorkbook.Names("Customer").RefersTo) InvoiceNo = Evaluate(ActiveWorkbook.Names("InvoiceNo").RefersTo) 'Display retrieved values MsgBox Customer & vbCr & InvoiceNo End Sub
Technical Tips © KRMS Productions 2000
SAVE USER SETTINGS TO THE REGISTRY
VBA makes it very easy to write and read registry values. The three statements are simple to use, and you can specify an application name, a section, a key, and a setting. All entries are saved in the registry under the key HKEY_CURRENT_USER\Software\VB and VBA Program Settings. Like their names imply, SaveSetting stores a setting in the registry, GetSetting retrieves a value stored in the registry, and DeleteSetting removes a setting from the registry. Here are examples of procedures using all three settings. Sub WriteValuesToRegistry() 'Write two settings to the registry SaveSetting _ Appname:="SuperApp", _ Section:="User", _ Key:="First name", _ Setting:="Jim" End Sub Sub ReadValuesFromRegistry() 'Declare a variable Dim FirstName As String 'Read setting from registry 'If there is no entry, return "*NoEntry*" FirstName = GetSetting( _ Appname:="SuperApp", _ Section:="User", _ Key:="First name", _ Default:="*NoEntry*") 'Show the result If FirstName = "*NoEntry*" Then MsgBox "No such entry!" Else MsgBox FirstName End If End Sub Sub DeleteRegistryEntries() 'Delete all settings for "SuperApp" DeleteSetting Appname:="SuperApp" End Sub NOTE: Please remember that editing your registry is potentially dangerous; always have a verified backup before you begin.
Technical Tips © KRMS Productions 2000
OPENING A WORKBOOK IF IT ISN'T OPEN ALREADY
When you try to open a workbook, Excel will issue a warning if a workbook with that name is already open. When using VBA to open a workbook, it's advisable to let the program take care of this situation. But if you want to use VBA to open a workbook that's not already open, here's a procedure to try. Sub OpenWorkbookIfItIsNotOpen() 'Declare a variable Dim wb As Workbook 'Try setting wb to the open workbook "test.xls" 'If unsuccessful, wb will still be Nothing afterwards On Error Resume Next Set wb = Workbooks("test.xls") On Error Goto 0 'If wb is still Nothing, open workbook from disk If wb Is Nothing Then Set wb = Workbooks.Open "c:\test\test.xls" End If End Sub
Technical Tips © KRMS Productions 2000
USING EXPONENTIATION IN FORMULAS
You're probably already familiar with Excel's simple arithmetic operators for addition, subtraction, multiplication, and division. Have you also used the exponentiation operator? You can use a caret [^] to calculate exponentiation in Excel formulas. Here are some examples: =3^2 returns 9, which is 3² or 3*3. =5^3 returns 125, which is 5³ or 5*5*5. Exponentiation has higher precedence and is evaluated before other arithmetic operators. To override this order of calculation, use parentheses, which are necessary when calculating roots. Here are some more examples: =81^1/2 returns 40.5. 81^1 is evaluated first and then divided by 2. =81^(1/2) returns 9, which is the square root of 81. =64^(1/3) returns 4, which is the cube root of 64.
Technical Tips © KRMS Productions 2000
When values are imported to Excel from text files or other external sources, they may contain unwanted characters. Excel provides two functions that help clean up these values.
* The CLEAN worksheet function removes all non-printing characters from a text.
* The TRIM worksheet function removes all spaces from a string, except single spaces between words. You can combine the two functions to effectively clean up string values. Let's say you have a list of strings in the range A2:A100 that you want to clean up.
To try it out, follow these steps:
1. Enter this formula in cell B2: =TRIM(CLEAN(A2))
2. Use the Fill Handle to copy the formula from B2 to B100.
3. With the range B2:B100 still selected, select Edit | Copy.
4. Select cell A2 and choose Edit | Paste Special.
5. Click Values and click OK.
6. Select B2:B100 and press [Delete].
Technical Tips © KRMS Productions 2000
The DSUM worksheet function makes it easy to add values in a list that meet a given set of criteria.
To try it out, follow these steps:
1. Enter this data in A1:C8. Name State Order Brown AZ 1500 Smith TX 1200 Baker CA 900 Brown WA 1800 Hughes MN 1500 James TX 1400 Banks AZ 700
2.Enter this criteria in E1:G2. Name State Order TX
3. Enter this formula in cell E6. =DSUM(A1:C8,"Order",E1:G2)
The formula returns 2600. DSUM looks at the data in A1:C8 and returns the sum of all Order values for the records that meet the criteria entered in E1:G2. If you change the criteria, the formula will recalculate to show the sum for the new criteria. Change the criteria in E1:G2 to the following. Name State Order B* >1000 E6 will show 3300, which is the sum of all orders made by people whose names start with B who ordered more than 1000.
Technical Tips © KRMS Productions 2000
Excel has no built-in function to find the count of a certain character in a string, but with a combination of the LEN and SUBSTITUTE worksheet functions, you can do it. The SUBSTITUTE function substitutes all occurrences of a string with another string. Here's the syntax: =SUBSTITUTE(text,old_text,new_text) An optional fourth argument specifies that only a certain instance of the search text should be replaced. Try out these examples to see how it works. Enter this string in cell A1: Abcdabcdabcd The following formula searches the text in A1 and substitutes all occurrences of "a" with "x." The formula will return "xbcdxbcdxbcd." =SUBSTITUTE(A1, "a", "x") This formula searches the text in A1 and substitutes the second instance of "a" with "". The formula will return "abcdbcdabcd." =SUBSTITUTE(A1, "a", "", 2) You can find the count of the character "a" by calculating the length of the string and subtracting the length of the string with all occurrences of "a" substituted with "". The result of this formula is 3. =LEN(A1) - LEN(SUBSTITUTE(A1, "a", ""))
Technical Tips © KRMS Productions 2000
BE EXACT WHEN COMPARING STRINGS
When comparing text strings, the match is not case-sensitive. Although this is usually practical, you may want to do an exact match. Excel provides the EXACT worksheet function to help you out.
To try it, follow these steps:
1. Type Adam in cell A1.
2. Type ADAM in cell A2.
3. Enter this formula in cell A4: =A1=A2
4. Enter this in cell A5: =EXACT(A1,A2) As you'll see, the expression A1=A2 evaluates to True, but EXACT(A1,A2) evaluates to False. You can also use the EXACT function to determine if a cell value is entered using uppercase. =EXACT(A1,UPPER(A1))
Technical Tips © KRMS Productions 2000
It's a common problem with lists: Data entry can lead to duplication. When your lists contain duplicates, you often need to find and get rid of them. An easy way to find duplicates is to use the COUNTIF worksheet function with a mix of absolute and relative cell references. COUNTIF takes two arguments: range and criteria. The function searches the range and returns the number of cells that match the value specified in the criteria.
To try it out, follow these steps:
1. Enter a list of values in the range A1:A20. Make sure that some of the values are entered more than once.
2. Enter this formula in cell B1 and copy it down to B20. =IF(COUNTIF($A$1:A1,A1)>1,"***","") You'll see *** in column B for all duplicate values in column A, so it's now easy to spot the duplicates.
Technical Tips © KRMS Productions 2000
By using the INDIRECT worksheet function, Excel can create formulas with very flexible references. The function takes a text as an argument and converts it to a reference. Let's see how it works.
Follow these steps:
1. Enter numbers in the range A1:C5.
2. Type A1:A5 in cell E1.
3. Enter this formula in cell E2: =SUM(INDIRECT(E1))
The formula returns the sum for the range A1:A5. If you change the text in cell E1, the formula will recalculate to show the sum for the range you entered.
You can use this combined with a simple drop-down list to sum a range on a certain worksheet. In a new workbook, follow these steps:
1. Enter numbers in A1:A5 on Sheet1, Sheet2, and Sheet3.
2. Select cell G1 on Sheet1.
3. Choose Data | Validation.
4. Click the Settings tab.
5. Choose List from the Allow drop-down list.
6. Type Sheet1,Sheet2,Sheet3 in the Source text box and click OK.
7. Enter this formula in cell G3 on Sheet1: =SUM(INDIRECT(G1&"!A:A"))
Now, you can use the drop-down list in cell G1 to show a sum for column A on Sheet1, Sheet2, or Sheet3.
Technical Tips © KRMS Productions 2000
FORGETTING FUNCTION ARGUMENTS?
It can be difficult to remember function names, and it's almost impossible to remember the order of the function arguments. To help you out, Excel provides the Paste Function dialog box and the online help system, which you're probably already familiar with. But there's also a neat keyboard shortcut that will paste in the function arguments when you've typed a function name.
Try it out:
1. Type the following in a cell, but don't press [Enter]: =ROUND(
2. Press [Ctrl][Shift]A.
When you press [Ctrl][Shift]A, Excel will fill in the function arguments for you and even provide the ending parenthesis! The arguments are always complete words, so you can easily double-click to select an argument and replace the word by typing a value or selecting a range.
Technical Tips © KRMS Productions 2000
Data validation is a powerful feature that was introduced with Excel 97. It allows you to control what a user can type in a cell. For example, you could specify that a cell should only allow whole numbers between 1 and 50, texts that are less than 10 characters long, or dates that are greater than April 1, 2000. When you have a lot of users entering data, this can help prevent mistakes.
To try it out, follow these steps:
1. Select a cell or range and choose Data | Validation.
2. In the Data Validation dialog box, select the Settings tab.
3. Specify what values you want to allow in the selected range.
4. The Input Message tab allows you to enter a text that's displayed automatically when the cell is selected.
5. The settings on the Error Alert tab specify what will be displayed when invalid data is entered. The Style setting specifies the options a user will have when an incorrect value has been entered.
6. After making your selections and typing your error message, click OK.
Technical Tips © KRMS Productions 2000
AVOID PROBLEMS WITH MERGED CELLS
To merge several cells into one, simply select a range of cells and click the Merge And Center button on the Formatting toolbar. This is commonly used to center a header across several columns. Unfortunately, worksheets that contain merged cells can be a bit awkward to work with. If you've merged the range A1:E1 and select any of the columns A through E, Excel 97 will not allow you to cut, copy, delete, or insert. It works better in Excel 2000, but it's still not perfect.
To insert or delete columns or rows that run through merged cells, follow these steps:
1. Select a single cell where you want to insert or delete a row or column.
2. To insert, choose Insert | Columns or Insert | Rows.
3. To delete, choose Edit | Delete, select Entire Column or Entire Row, and click OK.
For centered headers, you should avoid merging cells.
Try the following method instead. Follow these steps:
1. Select the range in which the header should be centered. The first cell should be the active cell.
2. Type the header and press [Enter].
3. Select Format | Cells and click the Alignment tab.
4. From the Horizontal drop-down list, choose Center Across Selection and click OK.
Technical Tips © KRMS Productions 2000
CREATING HYPERLINKS IN A WORKSHEET
Excel makes it easy to create clickable hyperlinks in a worksheet. Links allow you to navigate within a workbook or to a location in another workbook. A hyperlink can also open a Web page or create an e- mail message to a specified recipient.
These instructions are for Excel 97. Follow these steps:
1. Save the workbook.
2. Select the cell or object to which you want to attach a hyperlink.
3. Choose Insert | Hyperlink or press [Ctrl]K.
4. Enter the link information and click OK.
Here are a few examples:
A link within the workbook: Named location: Sheet1!G10
A link to another workbook: File path or URL: c:\test\test.xls Named location: Sheet2!D5
A link to a Web page: File path or URL: http://www.techrepublic.com
A link to create an e-mail message: File path or URL: mailto:[email protected]
To remove a link, select the cell or object, press [Ctrl]K, click Remove Link, and click OK.
Technical Tips © KRMS Productions 2000
Here's how to quickly rearrange rows or columns without having to use the Insert menu or cut and paste options.
Follow these steps:
1. Select the rows or columns that you want to move.
2. Point the mouse at the border of the selected range, and press [Shift] while dragging the range where you want it. While you drag, Excel will clearly show where the row or column will be inserted. If you press both [Shift] and [Ctrl] while you drag the range, Excel will insert a copy of the row or column.
Technical Tips © KRMS Productions 2000
Excel has no limit on the number of sheets in a workbook. But if your workbook contains many sheets, you won't be able to see all the sheet tabs at the bottom of your screen, which can make them difficult to navigate. To the left of the sheet tabs, there are little arrows called tab- scrolling buttons.
Here are some tips for using them:
* Click the first or last button to scroll to the first or last sheet.
* Click the middle buttons to scroll one sheet to the left or right.
* Press [Shift] and click the middle buttons to scroll many sheets left or right.
* Right-click any button to show a shortcut menu through which you can activate any sheet.
The first 15 sheets are shown in the menu. If the workbook contains more than 15 sheets, the menu will contain a More Sheets option, which shows a scrollable list of all available sheets.
Technical Tips © KRMS Productions 2000
This is, by far, the quickest way to perform simple calculations in Excel. Some people who have used Excel for years aren't aware of this excellent feature, so we have to tell you about it!
To try it out, follow these steps:
1. Make sure that the status bar is visible. Select Tools | Options and choose the View tab. Select the Status Bar check box and click OK.
2. Enter the following in A1:A6: 10, 15, 20, Adam, John, 18.
3. Select the range A1:A6.
4. On the status bar at the bottom of the screen, you should see Sum=63, which is the sum of the values in the selected range. If you don't see the sum, right-click somewhere on the status bar and choose Sum from the shortcut menu.
Don't forget to check out the other menu options.
Technical Tips © KRMS Productions 2000
It's a common task to loop through a number of rows and delete ones that are empty or that meet some other criteria. But if you loop from the first to the last row, you'll encounter problems when two consecutive rows are deleted. For example, if row 6 is removed, row 7 will become row 6, and it won't be checked because the loop goes on to row 7, which used to be row 8. How can you prevent this? The trick is simply to loop backward--from the last row to the first.
Use this little VBA procedure to try it out.
Sub RemoveEmptyRows() 'Declare a variable. Dim r As Long 'Loop from row 100 to row 1. For r = 100 To 1 Step -1 'Delete row if it is empty. If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r End Sub
Technical Tips © KRMS Productions 2000
If your worksheet contains complex calculations, writing data to the worksheet can be slow. To speed up a VBA procedure, you can temporarily turn off calculation. It's a good idea to remember the original calculation setting so that you can restore it afterward. Enter the formula =SUM(A:A) on the active sheet and run the procedure below. You'll notice a considerably longer execution time if you don't set the calculation to Manual before the loop. Sub SlowLoop() 'Declare variables. Dim r As Long Dim calc As Long 'Remember calculation mode before setting it to Manual. calc = Application.Calculation Application.Calculation = xlCalculationManual 'Enter random values in A1:A4000. For r = 1 To 4000 'Write a value to the cell. Cells(r, 1).Value = Rnd Next r 'Force calculation. Application.Calculate 'Set calculation mode back to original value. Application.Calculation = calc End Sub
Technical Tips © KRMS Productions 2000
Sometimes, you may want to keep users from scrolling around and viewing specific parts of a worksheet. To keep them from exploring, you can set the ScrollArea property of the worksheet. For some reason, this setting isn't saved when you save the workbook, so you have to reset it each time the workbook is opened, which can be accomplished with a Workbook_Open procedure. Let's see how it works.
Follow these steps:
1. In the VBA Editor, enter the following code in ThisWorkbook: Private Sub Workbook_Open() Sheets("Sheet1").ScrollArea = "A1:D5" End Sub
2. Save the workbook.
3. Close and re-open the workbook.
You will not be able to scroll away from the range A1:D5. It will also be impossible to select cells outside this range.
Technical Tips © KRMS Productions 2000
The Personal Macro Workbook makes it easy to store VBA procedures that you can always easily access. It's very simple to record macros and store them in this workbook. The Personal Macro Workbook is stored with the filename Personal.xls in the XLSTART directory. All Excel workbooks in this directory will open automatically whenever Excel starts. The Personal Macro Workbook is hidden, so you won't be able to tell it's open. To view the Personal Macro Workbook, select Windows | Unhide. Personal.xls is created the first time you record a macro to it. To try it out, let's record a macro that will convert a selected range to values.
Follow these steps:
1. Start Excel with a new, empty workbook.
2. Choose Tools | Macro | Record New Macro.
3. In the Record Macro dialog box, type ConvertToValues in the Macro Name text box.
4. Type V in the Shortcut Key box.
5. Choose Personal Macro Workbook from the Store Macro In drop-down list. Click OK.
6. Select Edit | Copy.
7. Choose Edit | Paste Special.
8. Select Values and click OK.
9. Press [Esc] to turn off the blinking.
10. Select Tools | Macro | Stop Recording.
11. Close Excel and answer Yes to save the Personal Macro Workbook.
The recorded macro will now be available whenever you run Excel.
Technical Tips © KRMS Productions 2000
TAKE COMMAND OF BUTTON PROBLEMS
In Excel 97, there's a bug that can cause a problem when a VBA procedure is started by a command button or another ActiveX control. Let's take a look at the problem and find a way to avoid it.
Follow these steps:
1. Start Excel with a new workbook.
2. Select View | Toolbars | Control Toolbox to make the toolbox visible.
3. Click the Command Button tool and draw a button on the worksheet.
4. Double-click the newly drawn button and add this code: Private Sub CommandButton1_Click() Application.Calculation = xlCalculationManual Application.Calculation = xlCalculationAutomatic End Sub
5. Press [Alt][F11] to return from the VBA Editor to Excel.
6. Click the Exit Design Mode toolbar button.
Now click the command button. It should generate a runtime error 1004. To avoid it, add the following line of code at the beginning of the procedure. ActiveCell.Activate Or, you can set the TakeFocusOnClick property of the command button to False.
Technical Tips © KRMS Productions 2000
You can convert an Excel chart to a .gif file, but this can't be done manually through Excel's menus. You need to use a little VBA procedure. Here's how to do it: Sub SaveChartAsGifFile() 'Declare variables. Dim f As Variant Dim ch As Chart 'Set ch to the active chart. Quit if a chart isn't active. On Error Resume Next Set ch = ActiveChart On Error Goto 0 If ch Is Nothing Then Exit Sub 'Get filename for new .gif file. Quit if [Cancel] is pressed. f = Application.GetSaveAsFilename( _ InitialFilename:="", _ FileFilter:="GIF Files (*.gif), *.gif", _ Title:="Export Selected Chart as GIF File") If f = False Then Exit Sub 'Export chart as .gif file. ch.Export FileName:=f, FilterName:="GIF" End Sub
Technical Tips © KRMS Productions 2000
The last used cell of a worksheet can be reached by pressing [Ctrl][End] or by using ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell). But both these methods work incorrectly when the used range has been reduced since the workbook was saved. The function below uses the Edit | Find menu option to correctly find the last used cell. To manually do a backward search, hold down [Shift] when clicking Search Next in the Find dialog box. Function LastCell(ws As Worksheet) As Range 'Declare variables. Dim r As Long Dim c As Long 'Avoid errors that will occur if sheet is blank. On Error Resume Next 'Find last used column. c = ws.Cells.Find( _ What:="*", _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column 'Find last used row. r = ws.Cells.Find( _ What:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'Set return value. Set LastCell = ws.Cells(r, c) On Error GoTo 0 End Function
Technical Tips © KRMS Productions 2000
PUT THE FULL FILENAME IN THE FOOTER
Excel has a built-in feature that makes it very easy to add the name of the workbook or the worksheet to the page header or footer.
To set it up, follow these steps:
1. Select File | Page Setup.
2. On the Header/Footer tab, click Custom Header or Custom Footer.
3. Click the section where you want the filename or worksheet name to appear (Left, Center, or Right).
4. To add the filename or the worksheet name, click one of the two icons on the right.
5. Click OK twice to exit the dialog boxes. It's not as easy to automatically display the full path and filename.
For that, you need a little VBA procedure similar to the one below, which should be entered in ThisWorkbook: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub
Technical Tips © KRMS Productions 2000
When working with dates and times in Excel, it's helpful to know how these values are actually stored. To get a better idea, check out the following example.
Follow these steps:
1. Enter the following in A1:A4: 1900-01-05, 2000-07-08, 06:00, 2000- 07-08 12:00.
2. Select A1:A4 and choose Edit | Copy.
3. Select B1 and choose Edit | Paste Special.
4. In the Paste Special dialog box, select Values, and click OK. Examine the values in B1:B4. Dates are stored as the number of full days since Dec. 31, 1899. Times are stored as fractions, indicating how much of a full day the time represents. All calculations are made using these values.
Here's another example to try.
Follow these steps:
1. Enter the following in D1:E3: Repair time 06:15 Hourly rate 50 Amount =E1*E2
2. Format cell E3 as a number. (Right-click the cell, choose Format Cells, choose Number from the Category list, and click OK.)
The result may not be what you expect. It's the result of 0.260417 multiplied by 50. To convert a time value to hours, multiply it by 24. You can change the formula in E3 to the following: =E1*24*E2
Technical Tips © KRMS Productions 2000
The MATCH worksheet function is used to search a range and return a number representing the position where the searched value is found. MATCH takes three arguments. Here's the syntax: =MATCH(LookupValue, LookupArray, MatchType) MatchType can be -1, 0, or 1. MatchType -1 finds the smallest value greater than or equal to the LookupValue. MatchType 1 finds the largest value less than or equal to the LookupValue. The following example uses MatchType 0, which performs a search for an exact match.
To try it out, follow these steps:
1. Enter the following in A3:A7: Adam, John, Chuck, Andrew, Mike.
2. Type Chuck in C1.
3. Enter this formula in C2: =MATCH(C1, A3:A7, 0) The result will be 3, which tells us that Chuck is found as the third value in the range A3:A7. If you change the value in cell C1, the formula will show you where that value is found, or it will display #N/A if there is no match. A perfect match for the MATCH function is the INDEX worksheet function, which we'll explain next time.
Technical Tips © KRMS Productions 2000
Last time, we talked about the MATCH function, which searches a range and returns a number representing the position where the value is found. The INDEX worksheet function is a good complement to the MATCH function; it's used to look up a value from a known row and column within a range. INDEX takes three arguments: =INDEX(Array, RowNumber, ColumnNumber)
To try it out, follow these steps:
1. Enter the following in A1:C5. Id Product name Price CA Canoe 1100 FB Football 30 TN Tent 210 GL Glove 19
2. Enter the following in E3:F5. Row 3 Product =INDEX(A:C, F3, 2) Price =INDEX(A:C, F3, 3)
F4 and F5 show the values from row 3, columns 2 and 3 within the range A:C.
To show other products, change the row number in F3. You can use MATCH to automatically find the row number for a certain ID.
Follow these steps:
1. Enter this in F1:E1. Id FR
2. Enter this formula in F3. =MATCH(E1, A:A, 0)
The formula finds the correct row number, which is used to retrieve the product name and price.
Technical Tips © KRMS Productions 2000
USING THE OFFSET WORKSHEET FUNCTION
A few weeks ago, we discussed the Offset and Resize properties in VBA. Today, let's take a look at the OFFSET worksheet function, which acts as a combination of those VBA properties. OFFSET returns a reference to a range that's a specified number of rows and columns from a cell or range of cells. The OFFSET function takes five arguments. Here's the syntax: =OFFSET(Range, RowOffset, ColumnOffset, Height, Width) The height and width arguments are optional. If any of these arguments are not specified, they're assumed to be the same as the size of the original range. Enter this formula to sum all values from the third row only within the range A1:E15. =SUM(OFFSET(A1:E15,2,0,1)) This formula totals the first column of the range A1:E15. =SUM(OFFSET(A1:E15,0,0,,1))
Technical Tips © KRMS Productions 2000
Last time we talked about using the OFFSET worksheet function to return a value from a specified row or column of a range of cells. The OFFSET function can also help make functions easier if you often work with named ranges. Once you start using named ranges in a workbook, they tend to grow greater in number than you originally intended. It can become tedious to maintain and resize the ranges when you add or delete data. One way to keep the number of ranges down is to use fewer names for larger ranges. Then you can use the OFFSET function to refer to a certain column or row of that range. If the range A2:E100 is named Products, you can refer to the first column of the range like this. OFFSET(products,0,0,,1) This function refers to the row above the Products range. OFFSET(products,-1,0,1) This function sums all values in the third column of the Products range. =SUM(OFFSET(products,0,2,,1))
Technical Tips © KRMS Productions 2000
THESE FUNCTIONS KNOW THEIR NAMES
If you often deal with names in your work, you know how frustrating it can be when a name isn't in the format that you need. If you need to sort by first name or last, and the entire name is in one cell, there are ways to make life a little easier. Here are three worksheet formulas that split up names such as Doe, John to John and Doe--and even turn the name around completely to John Doe.
To try them out, follow these steps:
1. Type Doe, John in A1.
2. To parse out just the first name, enter this formula in B1. =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))
3. To extract only the last name, enter this formula in cell C1. =TRIM(LEFT(A1,FIND(",",A1)-1))
4. To switch the order of the name, enter this formula in cell D1. =TRIM((RIGHT(A1,LEN(A1)-FIND(",",A1)))&" "& LEFT(A1,FIND(",",A1)-1))
Technical Tips © KRMS Productions 2000
If you type text into a cell, you can press [Alt][Enter] to insert a line break within the cell. The Wrap Text property of the cell is automatically set when you use this keyboard shortcut. The row height is also increased to make room for the extra line of text. To insert a line break in a formula, you can use CHAR(10), which inserts a line break character. You need to make sure that the cell's Wrap Text property is selected; otherwise, it won't work. You also have to adjust the row height to show the extra line.
Let's try it!
Follow these steps:
1. Open a new worksheet in Excel.
2. Select the entire first column.
3. Choose Format | Cells and click the Alignment tab.
4. Select the Wrap Text check box and click OK.
5. Enter this formula in cell A1: ="a" & CHAR(10) & "b"
6. Select the entire worksheet.
7. Choose Format | Row | AutoFit. A and b will appear on separate rows in A1.
Technical Tips © KRMS Productions 2000
The Conditional Formatting feature was introduced in Excel 97. This feature allows you to change cell formatting depending on the cell's value. It can be very practical to highlight values that are above or below certain limits or to show all invoices past a certain date. Here are a couple of examples to try out to see how it works. EXAMPLE 1
Follow these steps:
1. Enter random values between 1 and 100 in A1:A20.
2. Select A1:A20.
3. Choose Format | Conditional Formatting.
4. In the first drop-down list, choose Cell Value Is. In the second drop-down list, select Not Between. Enter 20 in the third box and 80 in the fourth box.
5. Click Format.
6. On the Font tab, select Bold from the Font Style list box, and choose red from the Color box.
7. Click OK twice. All values that are not between 20 and 80 will now be bold and red. EXAMPLE 2
Follow these steps:
1. Enter dates between July 1, 2000 and Sept. 30, 2000 in D1:D20.
2. Select D1:D20.
3. Choose Format | Conditional Formatting.
4. In the first drop-down list, select Formula Is. Enter =D1 <TODAY() in the second box.
5. Click Format.
6. On the Patterns tab, select the color red.
7. Click OK twice. All dates before today are now shown in red cells.
Technical Tips © KRMS Productions 2000
If you use Excel 2000, you have a PlaceBar on the left side of your Open and Save As dialog boxes.
By default, this bar shows History, My Documents, Desktop, Favorites, and Web Folders shortcuts. If you're constantly navigating out on the network to access specific files, you know how much time all that clicking can take. What if you could add shortcuts to your most needed files and folders? By downloading and using Microsoft's free WOPR 2000 PlaceBar Customizer, you can modify the PlaceBar to include your own places. Microsoft didn't actually create this application, but they've tested it and found it useful.
Check out Microsoft's Office Update site for more information. http://officeupdate.microsoft.com/2000/downloadDetails/PlaceBar.htm
Technical Tips © KRMS Productions 2000
GET UPDATED ON THE LATEST TRENDS
If you've created a line or scatter chart, you can add a trendline by right-clicking the charted line and choosing Add Trendline from the shortcut menu. Trendlines are used to graphically display trends in data; there are several types of trendlines available to suit different kinds of values. By selecting the Display Equation On Chart check box on the Options tab of the Add Trendline dialog box, you can see the formula that's used to calculate the trendline. Frequently people can get very odd results when they try to use the displayed trendline equation to calculate values--especially when the trendline is based on a high-order polynomial equation. The problem is that the trendline equation is displayed with far too low precision.
To show the equation with better precision, try this:
1. Select the trendline equation.
2. Click the Increase Decimal button 10 times. The equation should now show values with enough precision to give good results when used for calculations.
Technical Tips © KRMS Productions 2000
A LIST BOX WITH A DYNAMIC NAMED RANGE
When you use a list box to show values from a range of cells, you must specify the range that contains the list. If the size of the list changes, you have to respecify the input range for the list box--unless you use a dynamic named range. Let's try it out. First, let's create the dynamic named range.
Follow these steps:
1. Start Excel.
2. Enter a list of fruits in A1:A4 on Sheet2.
3. Select the range A1:A4 and choose Insert | Name | Define.
4. In the Names In Workbook text box, enter FruitList. In the Refers To box, enter =OFFSET($A:$A,0,0,COUNTA($A:$A)).
5. Click Add and click Close. Now, we'll use the named range in a list box.
Follow these steps:
1. Select Sheet1.
2. Make sure you can view the Forms toolbar. (Click View | Toolbars | Forms.)
3. Click the List Box button and draw a list box on the sheet.
4. Double-click the list box and select the Control tab.
5. In the Input Range box, enter FruitList. In the Cell Link box, enter A1.
6. Click OK. Now, you can add or remove fruits on Sheet2, and the list box will automatically reflect any changes made to the list.
Technical Tips © KRMS Productions 2000
CONVERT FORMULAS TO VALUES IN THE BLINK OF AN EYE
Here's a really quick way to convert formulas to values.
Don't blink-- you'll miss it!
Follow these steps:
1. Select the range you wish to convert.
2. Position the mouse at the border of the range. The mouse pointer should be an arrow.
3. Hold down the right mouse button. Drag it a little to the right and then back again to the original position.
4. Select Copy Here As Values Only from the shortcut menu.
Technical Tips © KRMS Productions 2000
In Excel, you can format a cell to display a picture that's been selected from a list box.
Let's try it out.
Follow these steps:
1. Open a new worksheet in Excel.
2. Select Sheet2 and make all cells approximately 2 inches by 2 inches.
3. Enter Frog, Lion, and Bird in A1:A3.
4. Insert suitable Clip Art pictures into cells B1:B3. Resize them to make them fit inside the cell.
5. Select Sheet1 and draw a list box using the Forms toolbar. 6. Double-click the list box and select the Control tab.
7. In the Input Range box, enter Sheet2!$A$1:$A$3. In the Cell Link box, enter A1. Click OK.
8. Select Insert | Name | Define.
9. In the Names In Workbook text box, enter pic. Enter this formula in the Refers To box: =OFFSET(Sheet2!$B$1,Sheet1!$A$1-1,0)
10. Click Add and click Close.
11. Select cell G1 and choose Edit | Copy.
12. Press [Shift] and select Edit | Paste Picture Link.
13. Change the picture link's formula to =pic. Now when you select a picture in the list box, it will show up in the picture link.
Technical Tips © KRMS Productions 2000
With Office 2000, Microsoft took the old system of long and short menus one step further by implementing adaptive menus. This means that some of the menu items are hidden by default, based not only on how long it's been since you last used an item, but also on how often you used the item in the past. Excel 2000 shows only the most frequently used menu items. The full menus show up if you open a menu and wait a few seconds or if you click the arrow at the bottom of the menu. You can also double-click the menu name to open and show the full menu. If you always want to see the full menus, it's as simple as clearing a box.
Here's what to do:
1. Choose Tools | Customize.
2. Select the Options tab.
3. Deselect the Menus Show Recently Used Commands First check box.
4. Click Close.
Technical Tips © KRMS Productions 2000