Microsoft Excel Tips



			Home \| Our Constitution \| Our Projects \| Business Opportunities \| Links Microsoft Excel Tip KEEP YOUR USERS FROM PRINTING WORKBOOKS There are times when you may not want your users to be able to print a workbook. The data may be confidential, or the workbook may simply not be suitable for printing. Here's how you can disable the printing of a workbook. Follow these steps: 1. Start Excel with a new workbook. 2. Type some text on Sheet1. 3. If your workbook window is maximized, right-click the Excel icon to the left of the File menu. Otherwise, right-click the icon in the workbook's window title bar. 4. Select View Code from the shortcut menu. 5. In the VB Editor, enter the following code. Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True End Sub 6. Press [Alt][F11] to return to Excel. 7. Now try printing the workbook--it can't be done. Be careful: This method is not completely safe. If the workbook is opened with macros disabled, the workbook can be printed--because the VBA code won't run. Stay tuned: Next time, we'll show you how you can make sure that a workbook is opened with macros enabled. ENSURE ACCURATE DATA ENTRY IN EXCEL BY USING DATA VALIDATION TO CREATE DROP-DOWN LISTS You don't have to know how to program in Visual Basic to create drop- down lists and validate entries in an Excel spreadsheet. The Data Validation feature makes it easy to eliminate data entry errors. http://www.techrepublic.com/article.jhtml?id=r00320000427jed10.htm Today's Windows 2000 Professional Tip KEEPING A DIAL-UP CONNECTION ALIVE If you spend a lot of time on the Internet and use a dial-up connection, it can sometimes be a hassle having to reconnect frequently. Most ISPs implement an automatic disconnect-on-idle after a given period of time (generally 15 to 20 minutes) to help maximize the number of available connections and avoid busy signals. A lot of ISPs actually hate unlimited access because of the impact it has on their user-to-modem ratios, but if the service is unlimited, you're probably going to take advantage of it, and why not? You are, after all, paying for unlimited access! Keeping a connection alive also has other implications. If you're in the middle of a long download and the remote server times out, you probably don't want the connection dropped before the server can start processing your download again. There are a handful of ways to keep a connection open, and all rely on the fact that as long as there is packet traffic on the connection more frequently than the ISP's defined idle time, the connection won't be dropped (at least not for being idle). One easy way to keep a connection open is to open your e-mail program and configure it to check for mail every five minutes or so. Another way is to open a console window and start a recursive ping going. Or, you can choose one of several third-party utility programs designed specifically to keep the connection alive. Check your favorite download site for possibilities. AT YOUR IT SERVICE If you buy IT services (consulting, integration, project outsourcing, or staffing) you know how time-consuming and expensive it is to identify, evaluate, and engage the best providers. Put all that behind you with TechRepublic's ITRadar. You can select from thousands of IT services firms, organized by competency and geography, to find the right solution--fast! http://www.techrepublic.com/promotion.jhtml?pc=NN1141&s=false MAKE SURE THAT MACROS ARE ENABLED Last time we told you how you can prevent users from printing a workbook with a little code. But we warned you that if the workbook is opened with macros disabled, the workbook can be printed--because the VBA code won't run. But you can make sure a workbook is opened with macros enabled. Depending on your macro virus protection settings, users may be prompted to enable or disable macros when opening a file with VBA code. To make sure that a workbook is opened with macros enabled, you can password-protect it and open it from another workbook. Follow these steps: 1. Create two new workbooks and save them with the names "Openme" and "Protected." 2. Password-protect the Protected workbook so it can't be opened without providing the password "carrot." To password-protect the file, go to File \| Save As. In the dialog box, click Tools and choose General Options. Type carrot in the Password To Open field and click OK. You'll be prompted to re-enter the password, and then you can save as usual. 3. Go to the VB Editor and enter the following code in ThisWorkbook of Openme.xls. Private Sub Workbook_Open() Workbooks.Open _ Filename:=ThisWorkbook.Path & "\Protected.xls ", _ Password:="carrot" ThisWorkbook.Close End Sub 4. Enter the following code in ThisWorkbook of Protected.xls. Private Sub Workbook_Open() MsgBox "Macros are enabled!" End Sub 5. In the VB Editor, go to Tools \| VBAProject Properties and choose the Protection tab. Select the Lock Project For Viewing check box for both workbooks. 6. Save the changes to both workbooks. Users won't be able to open Protected.xls because it's password- protected. Instead, they must open Openme.xls and enable macros--and Protected.xls will then automatically open. CHECKING IF YOUR ADD-INS ARE ADDED Sometimes workbooks or VBA modules depend on an installed add-in. In these cases, it's a good idea to make your code check that the required add-in is installed when the workbook is opened. The sample code below (which should be entered in ThisWorkbook) checks to see if Excel's Analysis ToolPak is installed. If the add-in isn't installed, the workbook will close. Private Sub Workbook_Open() Dim ok As Boolean ok = False On Error Resume Next ok = AddIns("Analysis Toolpak").Installed On Error GoTo 0 If Not ok Then MsgBox "Please install the Analysis ToolPak." ThisWorkbook.Close SaveChanges:=False End If End Sub THE FASTEST WAY TO PARSE TEXT IN EXCEL If you want to parse strings such as "First Last" into separate columns, you don't need to use fancy formulas. Excel has a tool that makes the job a snap. http://www.techrepublic.com/article.jhtml?id=r00320000816det02.htm GETTING OVER YOUR FEAR OF EXCEL'S DATABASE FUNCTIONS Kick your spreadsheet skills up a notch by taking this crash course in how to use Excel's database functions. http://www.techrepublic.com/article.jhtml?id=r00320000517gcn01.htm BREAK UP THE TEXT IN A MSGBOX In Excel VBA, it's easy to use a message box to present information to your users. But you can make your messages even easier on their eyes by splitting the message into several lines. You can break up lines of text by using the vbCr constant. It's also practical to use the underscore character [_] to split the code into several lines, which will make the line breaks in the code resemble the ones in the resulting message text. Try out the sample code below to see how it works. Sub NarrowMessage() MsgBox _ "Your message will be sent" & vbCr & _ "to the selected recipient" & vbCr & _ "within a few seconds." & vbCr & _ vbCr & _ "Thanks for using TechRepublic." INTRO TO EXCEL: HOW TO GET STUDENTS STARTED Al Hedstrom has taught enough Excel classes to know how to create a solid foundation for students to build on. Read his tips on how to use humor in class and how to work in a few math reminders. http://www.techrepublic.com/article.jhtml?id=r00320000517hed01.htm HOW TO TEACH CHARTING IN EXCEL IN 15 MINUTES It's best to be thorough when introducing a new subject, but trainers don't have the luxury of unlimited time. These Excel tips will help you cover a lot of ground in a short period of time. http://www.techrepublic.com/article.jhtml?id=r00620001003mar01.htm FIND OUT WHICH PRINTER YOU'RE USING In Excel VBA, you can use Application.ActivePrinter to determine the name of the currently selected printer. You can also alter this setting to change it to another active printer--if you know its name. The printer names depend on your operating system and environment, so you'll have to check what Application.ActivePrinter displays, depending on the currently selected printer. You can also print to a certain printer by specifying the ActivePrinter parameter when using the PrintOut method of a sheet. Here are three sample procedures to see how it works. Remember to alter the code to reflect your printer's name. * To display the name of your selected printer, try this procedure. Sub ShowActivePrinter() Debug.Print Application.ActivePrinter End Sub * To change the currently selected printer, try this sample code. Sub SetActivePrinter() Application.ActivePrinter = "\\Printserver\p2 on Ne01:" End Sub * This example illustrates how to print to two printers. Sub PrintToTwoPrinters() Dim p As String p = \\Printserver\p1 on Ne02: ActiveSheet.PrintOut ActivePrinter:=p p = \\Printserver\p2 on Ne01: ActiveSheet.PrintOut ActivePrinter:=p DOWNLOAD OUR SPACE INVADER ICONS TO HELP NEW USERS UNDERSTAND EXCEL Are you looking for a way to energize beginning Excel classes? The Great Space Cursors from the Galaxy Excel may be just the tool you need. The story and graphics in this download will help new students master and remember the workings of the Excel cursor. http://www.techrepublic.com/article.jhtml?id=r00320001011bla44.htm PROGRAMMATICALLY DETERMINING IF A WORKSHEET EXISTS IN EXCEL 97 Need to write a routine in Excel 97 that determines whether a worksheet exists? TechRepublic passport owner PBate asked the question and got an answer in the TechRepublic forums. http://www.techrepublic.com/article.jhtml?id=r00320000120jed10.htm IMPROVE HTML DOCUMENTS WITH IMAGE FRAMES AND TEXT BOXES If you're looking for a quick and easy way to make important information stand out on your Web pages, we've got the answer. Find out how to create image frames and text boxes that can help enhance your HTML documents. http://www.techrepublic.com/article.jhtml?id=r00320001204det01.htm WILL YOUR WEB HOST SHUT DOWN YOUR SITE WHEN THERE'S A PAYMENT DISPUTE? Your Web host might brag about the sophisticated technology used to keep uptime at the max. But will your Web host pull the plug if you have a billing dispute? Here are steps you can take to keep your site up and running. http://www.techrepublic.com/article.jhtml?id=r00620001204bol01.htm PLAN SMART FOR EXHIBITING AT AN IT TRADE SHOW Trade shows offer great opportunities to meet potential clients and partners--but choosing the right event can be tough. We've gathered several resources to help you pick the best events and some tips to help you market your business successfully. http://www.techrepublic.com/article.jhtml?id=r00720001204gli01.htm STOP LOOKING SO HARD! It's easier than you think to find the IT answers you need. Search all of TechRepublic's articles and content and discover the technology information you need. Type in your own words or phrases, or browse by pre-selected IT topics. You can sort your results to find what's most relevant at the top of your list. Take TechRepublic's enhanced search feature out for a test drive today! http://www.techrepublic.com/promotion.jhtml?pc=NN1121&s=false COPY A LIST OF UNIQUE VALUES When you have a long list with lots of duplicates and want to extract a list where each value appears only once, Excel can help you out--and it's really quite easy. Follow these steps: 1. Select the list. Make sure it has a header on the first row. 2. Choose Data \| Filter \| Advanced Filter. 3. In the dialog box, confirm that the List Range is really the range that contains your list. 4. Under Action, select Copy To Another Location. 5. Select the Unique Records Only check box. 6. In the Copy To text box, enter the cell where you want to place the list of unique values. 7. Click OK. COLOR-COORDINATE YOUR WORKSHEETS Do you remember the blue-and-white z-fold paper used for computer printouts? The design of the reports may not have been much fun, but at least it was easy to follow the lines--especially because of the colored paper. But you can also use this in Excel to help distinguish between entries. Let's use conditional formatting to make the rows of a list alternate between yellow and green. Follow these steps: 1. Select rows 1:20. 2. Choose Format \| Conditional Formatting. 3. For Condition 1, choose Formula Is from the drop-down list and enter the following formula: =MOD(ROW()-1,6)<=2 4. Click Format. On the Border tab, set a thin border above and below. On the Patterns tab, choose a light yellow. 5. Click Add to add a second condition. 6. For Condition 2, choose Formula Is from the drop-down list and enter the following formula: =MOD(ROW()-1,6)>2 7. Click Format. On the Border tab, set a thin border above and below. On the Patterns tab, choose a light green. 8. Click OK. In you insert or delete rows, the colors will change dynamically. FOR TEACHING AND USING EXCEL If you need help with charting, data entry, or formatting in Excel, TechRepublic has plenty of ideas for you. Power users and trainers have contributed many suggestions about how to get the most from this application. http://www.techrepublic.com/article.jhtml?id=r00320001006com44.htm MAKE YOUR INFORMATION JUMP OFF THE PAGE WITH EXCEL'S COLOR-CODING FEATURE Large, complex worksheets can become a mind-numbing blur for those trying to make sense out of the numbers. Show your students how to colorize important data so readers can cut to the chase. http://www.techrepublic.com/article.jhtml?id=r00319991118jod05.htm ADD GRAPHICS TO YOUR BAR CHARTS In a bar chart, you can use stretched or stacked images instead of just a color--and you have lots of images to choose from in the ClipArt gallery. To show you how, let's create a simple bar chart and add an image to it. Follow these steps: 1.Enter the following in A1:B5. Apples Q1 45 Q2 56 Q3 49 Q4 42 2. Select A1:B5 and press [F11] to create a bar chart. 3. Go back to the worksheet and insert a picture of an apple. (Select Insert \| Picture \| Clip Art and choose an image.) 4. Select the apple and copy it. 5. Go back to the chart, select the bars, and choose Edit \| Paste. If you want to stack or stretch the pictures, right-click a bar and select Format Data Series. On the Patterns tab, click Fill Effects and use the Format section on the Picture tab to make your selections. SPEEDING UP EXCEL DATA ENTRY Positively the best way to enter data in Excel is by using the Data menu's Form option. Find out how. http://www.techrepublic.com/article.jhtml?id=r00319990422jed04.htm REMOVING BLANKS FROM A COLUMN OF ENTRIES IN EXCEL Sometimes the easiest solution to a problem is right under your users' noses. Jeff Davis shares an Excel tip that can save your users time. http://www.techrepublic.com/article.jhtml?id=r00319990707jed25.htm LOCATE HARD-TO-FIND OBJECTS If you've drawn objects on a worksheet that are very small or that have no color, it can be difficult to find them again. Here are a couple of tricks to find almost-invisible objects. Follow these steps: 1. Draw a new object (preferably one that's large and colorful enough so that you won't lose it). 2. With the new object selected, press the [Tab] key. Pressing [Tab] will select the next object. You can keep pressing [Tab] until you find the missing object. To find objects, you can also select all of them at once. Follow these steps: 1. Select Edit \| Go To. 2. Click Special. 3. Select Objects and click OK. SAFELY CARVING UP SPREADSHEETS FOR CUSTOM PRINTING This trick allows spreadsheet users to cut or copy print ranges without worrying about how formulas react. http://www.techrepublic.com/article.jhtml?id=r00319990506jed02.htm INTRODUCE YOUR STUDENTS TO EXCEL'S CHART WIZARD Your Excel students may not have "chart production" in their job descriptions, but sooner or later, someone's going to ask them to create one. Here's an overview of the basics. http://www.techrepublic.com/article.jhtml?id=r00319990916jod05.htm ARE WE CONNECTING? Excel contains a number of drawing tools. One of them is the connector, which is great to use when creating flowcharts or organizational charts. Connectors attach to handles on other drawing objects, stretching and adjusting nicely if the objects they're attached to are moved on the worksheet. If you haven't used them, you should try them out! Follow these steps: 1. Click the Drawing toolbar button to make sure that the drawing toolbar is visible (or select View \| Toolbars \| Drawing). 2. Draw a couple of shapes on the worksheet. 3. Click AutoShapes and select a style from the Connectors list. 4. Connect the shapes by drawing the connector from a handle on one shape to a handle on another. Now move the shapes around: The connectors will adjust automatically! USE YOUR INTERNSHIP PROGRAM TO RECRUIT IT STAFF It used to be that internships were for college kids who needed experience but not necessarily money. But in this Tech Watch, Bob Weinstein explains that IT companies should take heed of the changing perceptions about internships. http://www.techrepublic.com/article.jhtml?id=r00520001108wei01.htm MORE HIGH-TECH VISAS NOT A SOLUTION TO THE IT SKILLS SHORTAGE Silicon Valley lobbyists are celebrating Congress' approval of an increase in the number of H1-B temporary visas for foreign workers. This Gartner FirstTake asserts that the victory won't solve the shortage of IT skills for U.S. companies. http://www.techrepublic.com/article.jhtml?id=r00720001023ggp01.htm AUDIT YOURSELF WITH THESE TOOLS Excel's Auditing tools can help you understand how a worksheet is constructed--and they can help you trace the cause of errors. This is an invaluable set of tools when you're trying to figure out the mechanisms of a complex spreadsheet that someone else has constructed. To familiarize yourself with these tools, it's a good idea to check out the Auditing toolbar (choose Tools \| Auditing \| Show Auditing Toolbar). You can select a cell and display arrows to all formulas that depend on this cell. If a formula on another sheet depends on the cell, double- click the arrow to see the exact location of the dependent formula. When a cell with a formula is selected, you can display arrows to the cells that this formula depends on. If an error value is the result of a formula, click the Trace Error button to locate the cause of the error. HIT THE GROUND RUNNING WITH EXCEL'S CUSTOM WORKSPACES Give your Excel students instant access to their worksheet data by teaching them how to build custom workspaces. http://www.techrepublic.com/article.jhtml?id=r00319991021jod05.htm HOW TO AUDIT YOUR HELP DESK Testing the performance of your help desk can be very beneficial, but it has to be done properly. You need to make your staff comfortable with the process, and you have to make sure the right people are asking the right questions. http://www.techrepublic.com/article.jhtml?id=r00320000608vic01.htm SHIFT YOUR FOCUS WITH THESE KEYBOARD TRICKS Many of the toolbar buttons in Excel have a different meaning if you click them while pressing the [Shift] key. Here are some examples: * If you press [Shift] while clicking the Align Left button on the Standard toolbar, it has the same effect as clicking Align Right. * If you'd like to see a print preview, hold down [Shift] and click the Print button. * Pressing [Shift] while clicking the Underline button makes a double underline. If you'd like to see what a specific key combination does, point your mouse at a toolbar button and press the [Shift] key while holding down the left mouse button. The button image will change to the icon of what the combination will do. Move the mouse off the button before releasing the mouse button. Pressing [Shift] and clicking the File and Edit menus will display a few menu options that aren't usually available. And here's a dangerous combination to avoid: When you quit Excel, you're asked if you want to save changes to the workbook. Clicking No while pressing [Shift] means no for all currently open workbooks, and Excel closes down without saving any of the changed workbooks. HOW TO EDIT, REVISE, AND ENHANCE TRAINING MATERIALS Students will always get more out of a class if the courseware has been designed to fit them or their circumstances. Mary Ann Richardson explains how to take a basic class plan and edit it to fit students' needs, interests, and abilities. http://www.techrepublic.com/article.jhtml?id=r00620000807mar01.htm DOWNLOAD TECHREPUBLIC'S ASSET-TRACKING SPREADSHEET This Microsoft Excel asset-tracking spreadsheet solution will have information about your company's IT expenditures at your fingertips. Use it to measure the costs and life spans of your equipment, and it will help you make informed purchasing decisions. http://www.techrepublic.com/article.jhtml?id=r00720001030rec01.htm THE QUICKEST WAY TO ENTER THE SAME VALUE This is a very simple little trick, which can be useful when you want to enter the same value into several cells. Follow these steps: 1. Select the cells where you want the value entered. To select several ranges, hold down [Ctrl] while selecting cells with the mouse. 2. Type a value. 3. Press [Ctrl][Enter]. All selected cells will display the value you just entered--no need for copying and pasting! GETTING YOUR SHARE OF COMP TIME How many hours did you work last week and the week before? If you're not taking advantage of comp time, here's how to make on-demand comp time part of your professional package. http://www.techrepublic.com/article.jhtml?id=r00320001128jed01.htm ROLL OUT YOUR IP PHONES WITH MINIMAL IT TIME Planning a move to IP phones? Here's one way to make short work out of training users and installing the devices. http://www.techrepublic.com/article.jhtml?id=r00320001121jed01.htm IMMEDIATE CALCULATIONS IN THE FORMULA BAR If you enter a formula in the formula bar and press [F9], the result of the formula will be calculated, and you'll see only the result of the calculation. If only a part of the formula's text is selected, that part will be evaluated when [F9] is pressed. If a range reference is selected, pressing [F9] replaces the reference with its values. To see how it works, try out these three examples. * Type the following in A1. =25+11+9 Press [F9] and then press [Enter]--45 is entered into the cell. * Type the following in A2. =25+(1/8) Select (1/8) with the mouse, and press [F9] and then [Enter]. The formula =25+.125 is written into A2. * Enter a list of numbers in the range B1:B5. Select cell B6 and click the AutoSum button. The range reference B1:B5 is selected. Press [F9] and see what happens. Then press [Enter] to see what happens. BALANCING PERSONAL BUDGETS CAN HELP STUDENTS LEARN EXCEL'S SOLVER Textbook examples often use business concepts to illustrate a tool or function. But sometimes, students can learn more from everyday examples. http://www.techrepublic.com/article.jhtml?id=r00320000911mar01.htm HOW TO DELIVER THE RIGHT KIND OF IT TRAINING Jeff Davis provides a plan of action for identifying and delivering the kinds of internal IT training your company needs. http://www.techrepublic.com/article.jhtml?id=r00320001017jed01.htm WHERE AM I? If you want your users to know exactly where they're working in a workbook, use this trick to put the workbook's path, filename, or the active sheet's name into a cell. Here's what to do: In an Excel workbook, enter the following formula in a cell. =CELL("filename") The result of this simple formula is the path of the workbook, the workbook's filename within square brackets, and the sheet name. If you want to return only the path, filename, or worksheet name, you can use Excel's text functions. Here's how. * To return only the path name, enter the following formula in a cell. =LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1) * To return only the filename, enter the following in a cell. =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH ("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) * To return only the worksheet name, enter this formula in a cell. =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH ("]",CELL("filename")) FLIP-FLOP THAT WORKSHEET DATA WITHOUT ANY FUSS Here's a practical Excel skill that could save your students hours of tedious labor and frustration. http://www.techrepublic.com/article.jhtml?id=r00319990716jod05.htm PREVENT EXCEL WORKSHEET MISHAPS WITH DATA VALIDATION Tiny errors can cause huge headaches for Excel users--but you can introduce your students to a feature that will help them avoid data entry mistakes. http://www.techrepublic.com/article.jhtml?id=r00319990625jod05.htm Home \| Our Constitution \| Our Projects \| Business Opportunities \| Links