add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Information
Excel is more than just an electronic spreadsheet for calculating, editing and printing data.
Excel lets you display and save information in cells by using formulas and lets you add comments to the cell. It also lets you download information from Web sites and view remote results of data and results of calculations through pictures and through a new tool in Excel 2002, the Watch Window.
Furthermore, you can link information in cells to objects such as text boxes and use an Excel VBA macro to calculate data and retrieve information from cells.
Viewing Worksheet Name, Workbook Name and Path
The full path of the folder where the active workbook is saved is the type of information you need in order to open or save the workbook.
You can use the CELL worksheet function to call information, or alternatively, you can add macros to your workbook that will present the information in the title bar.
Using the CELL worksheet function
The CELL worksheet function returns information about a cell, including its formatting, contents and location.
Type the CELL worksheet function in a cell (the function can be found in the Information category in the Paste Function dialog box) with the text filename in quotation marks: =CELL(“filename”). The function returns the full path and file name.
Worksheet function that returns the full path:
=MID(CELL(“filename”),1,FIND(“[“,CELL(“filename”))-1)
Worksheet function that returns the workbook name:
=MID(CELL(“filename”),1,FIND(“[“,CELL(“filename”))+1, FIND(“]”,(CELL(“filename”))-FIND(“[“,CELL(“filename”))-1)
Worksheet function that returns the sheet name:
=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+1,255)
Adding the path to the title bar or status bar
You can show the full path in the title bar or status bar using a VBA Macro. See Chapter 14, Opening, Closing and Saving Workbooks.
Viewing data and information in cells
Watch Window
New
In 2002
In the Watch Window, you can view a cell in an active or open workbook. You
can view the results of a calculation, see the formula and links, and define
the name of the cell.
This is a new and exciting feature that solves a well-known, difficult problem. You can view the resulting change in a distant cell. For example, you can see how changing one parameter affects the results when calculating profit and loss in a budget or when writing a business plan.
Use Tools, Formula Auditing, Show Watch Window.
Picture
Before the Watch Window in Excel 2002, you could use Paste picture or the camera icon to see the results of a distant cell.
Creating links for viewing through a picture
1. Select the relevant cells in the sheet, and press Ctrl+C (copy).
2. Select a different cell in any worksheet.
3. Press Shift. From the Edit menu, select Paste picture.
Note
The sub-menu Paste picture is added to the Edit menu when you press Shift. The picture will show the value of the original cell as it changes.
Adding the camera icon to the toolbar
Select one of the toolbars and right-click. Select Customize,and then select the Commands tab. From Tools, select Camera, and drag the icon to the toolbar. Click Close.
Select a cell or range in the sheet. Click the Camera icon, and select a cell in a different sheet or open workbook. Click the cell to create the object.
Use of the Camera is limited. You can only view calculation results or the data in a defined range of cells.
Linking Cells to a Textbox or Object
1. From the Drawing toolbar, select Text Box. Add a text box to the worksheet.
2. Select the Text Box, and press F2.
3. In the Formula bar, create a link to a cell by typing = and then selecting the cell. The contents of the cell are displayed in the Text Box.
Using a MsgBox to Display Information from Cells
When working in a workbook with many sheets and information, as is the case with a budget or business plan, you need to call up information from various cells with the results of calculations in different cells from different sheets.
If you work in Excel 97 or Excel 2000, you do not have access to the Watch Window and using a picture will not solve the problem because it only captures a single range.
The solution is to use a macro to create a MsgBox(see Chapter 27, Write Your First Program).
Record a macro using a shortcut, such as Ctrl+Shift+Q, and type the following code into the macro you are recording:
Press Ctrl+Shift+Q.
The result:
Explanations and Comments:
1. Define cell names in the workbook – Before starting to enter the code for the model, define cell names for the cells containing results. In the example, we defined names for three cells – IncomeTax, NetIncome, and CashFlow.
2. Add new lines in the MsgBox – Enter the line of code for the MsgBox in a single line. It is divided into another line through the letters vbCr.
3. Number formats – See the Format formula with the variable and how to format it.
4. Change the text in the message title – Specify the title in the title parameter.
For more information about MsgBox syntax, use the Help function in VBA.
Adding Comments to a Formula
Use this trick to add a comment to a formula: at the end of the formula, add a + (plus) sign, the N function, and an open parentheses; then type your comment in quotation marks, and close the parentheses. You can view the comment in the formula bar when you select the cell.
Example:
=A1+A2*4.71+N(“Total Sales for January and February * Rate of Exchange”)
Saving Information in Comments
Excel lets you add comments to cells. A Comment is a box in which you can enter free text. Each comment is limited in length to approximately 32,000 characters.
Adding Comments
1. Select a cell.
2. Press Shift+F2 or right-click, and from the shortcut menu, select Insert Comment.
3. In the Comment box, type the text you want.
By default, Excel does not display comments. The comment is displayed when you have the mouse pointer over the small red triangle in the upper right corner of any cell with a comment.
Changing the Name of the Comment Author
By default, each comment includes the author’s name. To change or cancel the name of the comment author, perform the following steps: From the Tools menu, select Options, General, and User name. Change or delete the user name as desired. The change will only apply to new comments that you insert.
Changing the Default Comment Format
Changes to the default format of Comments are done from the Display Properties dialog box in Windows.
1. Minimize Excel and any other open programs.
2. Right-click. From the desktop shortcut menu, select Properties.
3. Select the Appearance tab.
4. In the Item box, select ToolTip, and change the color.
5. In the Font box, change the font as desired, and select the font size and color.
6. Click OK to the new selection.
7. Click OK again at the bottom of the dialog box.
Note
Changing the ToolTip impacts all of the ToolTips in Excel, including those that appear below the toolbar icons.
Viewing Comments
From the Tools menu, select Options, View or use the Reviewing toolbar. Excel offers three display options:
© None – The comment indicator (red triangle) does not appear, and comments are not displayed.
© Comment indicator only – A small red triangle in the upper-right corner of the cell indicates a comment. The comment is displayed when the cell is selected.
© Comment & indicator – All comments inserted in the sheet are displayed.
Displaying a single comment
Select a cell with a comment. Right click, and from the shortcut menu, select Show Comment.
Change the location of the comment by dragging it to a location where it does not hide data. You can only change the location of a comment when the comment is displayed.
Copying Comments to Different Cells
1. Select a cell with a comment, and press Ctrl+C (copy).
2. Select a different cell, and right-click. From the shortcut menu, select Paste Special.
3. Select Comments, and click OK.
Deleting Comments
Select a cell with a comment, and right-click. From the shortcut menu, select Delete Comment.
Deleting all comments in a sheet
1. Press F5 (Go To dialog box), and click Special.
2. In the Go To Special dialog box, select Comments.
3. Click OK.
4. Right-click, and from the shortcut menu, select Delete Comment.
Printing Comments
From the File menu, select Page Setup, Sheet, and click the Sheet tab. Before printing, select one of the following options in the Comments box:
© None – Will not print comments.
© At end of sheet – Will print the comments on a separate page after printing the sheet.
© As displayed on sheet – Will only print the comments that are displayed.
Tip – Print a Single Comment
Select a cell containing a comment. From the File menu, select Page Setup, Sheet.In the Comments box, select At end of sheet. Now click OK, and then click the Print icon.
Adding Pictures to Comments
Want to send your photo inside a comment? Your incredible picture can be included in a comment and surprise whoever opens the workbook. Or maybe you want to display pictures of your colleagues near the cells that contain their contact information?
Select a cell that contains a comment, right-click, and from the shortcut menu, select Show Comment. Select the edge of the comment so that the comment is surrounded by dots, not by slashes. Right-click, and from the shortcut menu,select Format Comment, Colors and Lines, Fill. Open the Color box. Choose Fill Effects. Click the Picture tab, and click Select Picture. Select a picture and click OK. Resize the comment as appropriate.
Send Information to Comments
In Excel, you cannot link a comment to a cell. In other words, you cannot make things easier by typing text or numerical data into a cell and have it be displayed in a comment.
The solution is to use VBA.
The code that will let you add and update text in comments appears below.
To add text to a comment:
To update or change text in a comment:
Importing and Refreshing Information from the Internet
An incredible amount of information is available on the Internet. Excel users can have access to updated information such as stock quotes, exchange rates, indexes, and so forth.
This book includes the technique for importing the information from the Internet and refreshing it within the three versions of Excel.
Excel 97
In Excel 97, you must save the site address (URL) for every new query in a text file with the extension .iqy.
To save an address in a text file as a query, perform the following steps:
1. Open the Internet site from which you want to import information to an Excel sheet. For example, open the site www.bloomberg.com. The site includes a table of various currency exchange rates. The address of the page containing the table of currency exchange rates is http://www.bloomberg.com/markets/fxc.html. Copy the address by selecting it and pressing Ctrl+C.
2. In Windows, select Start, Programs, Accessories, Notepad.
3. Press Ctrl+V to paste the address into the text file. Save the file under a name with the extension .iqy (be sure to use lower case letters). For example, save the file under the name CrossCurrencyRates.iqy.
4. Open Excel 97. From the Data menu, select Get External Data, Run Web Query.
5. In the Run Query dialog box, search for and select the File CrossCurrencyRates.iqy.
6. In the Returning External Data to Microsoft Excel dialog box, click OK.
7. Wait a few seconds, and the data is transferred from the Internet page to the Excel sheet.
Excel 2000
1. From the Data menu, select Get External Data, New Web Query.
2. In the Enter the address box, paste the full address of the Web site. For example, in order to import and refresh information from the Internet in the Excel 97 section, enter:
http://www.bloomberb.com/markets/fxc.html.
Click Save Query, type a name for the query, and click Save.
3. Click OK.
4. In the Returning External Data to Microsoft Excel dialog box, click OK.
Excel 2002
Excel 2002 lets you select the exact data table on the Web page to import and refresh only the data table.
New
In 2002
1. From the Data menu, select Import External Data, New Web Query.
2. In the New Web Query dialog box, in the Address box, type or paste the address of the Internet site.
3. In the dialog box, notice the Web page. Click the small arrow in the upper left-hand corner of the table to select only the data table.
4. Click Import.
5. In the Returning External Data to Microsoft Excel dialog box, click OK.
Refreshing the Internet Data
(Excel versions 97, 2000, 2002)
Note
The Internet site does not have to be open.
Select the cell in the sheet containing the data. From the Data menu, select Refresh Data, or display the External Data toolbar and click the Refresh Data icon.
To display the External Data toolbar, select one of the toolbars. Right-click, select the External Data toolbar, and click OK.
Refreshing the Internet Data Automatically
On the External Data toolbar, click the Data Range Properties icon.
Select the Refresh every option, and set the number of minutes between each refresh action. Select the Refresh data on file open box to refresh the data automatically whenever the file is opened.
Running a Saved Query
In Excel 97, from the Data menu, select Get External Data, Run Web Query.
In Excel 2000, from the Data menu, select Get External Data, Run Saved Query.
In Excel 2002, from the Data menu, select Import External Data, Import Data.
Select the query you saved, and click Get Data (in Excel 2002, click Open).