Forum Home
Press F1
Thread ID: 49181	2004-09-12 04:40:00	FAQ #72 - Tips for MS Excel	-FAQ- (807)	Press F1

Post ID	Timestamp	Content	User
271550	2004-09-12 04:40:00	FAQ #72 - Tips for MS Excel Originally written by Parry 1 . Excel is taking forever to do calculations . What can I do? The more calculations Excel needs to do, the longer the process . Normally you will not notice this but in a large workbook with thousands of rows may take several minutes to complete calculations . You cannot prevent Excel taking time to do lots of calculations so the trick is to remove as many unnecessary formulas in your workbook as possible . Ask yourself, “Do I still need to have a formula in a cell or is just having the result in the cell good enough?” If you only need the results then do the following: - a) Select the cells and click Copy b) Without altering the selection, select Edit-Paste Special and choose the Values option . You have now successfully over-ridden the existing cells with the just the values & Excel wont need to do spend time doing calculations on these cells . A tip is to leave the last row with the formulas in case you need to use the calculations again . You may also wish to format these cells with a colour to make them stand out . Besides changing formulas to values you can turn off Excel’s auto-calculation feature . Normally Excel will make calculations as it sees fit, and it won’t just make calculations on recent changes, but the whole workbook . To turn off Automatic Calculation select Tools-Options-Calculation tab and change the calculation option to manual . Select OK to finish . When you want to recalculate cells select F9 . Be warned that leaving this option on permanently can cause problems later if you forget that Excel is not recalculating as you may not notice that some formula results are not correct . However, it is a handy option if you are making many changes to a large workbook to select manual calculations until you have finished making the changes . 2 . I have a large workbook and it takes a long time to scroll to the last cell then back to the top of the sheet . To move quickly to the last cell with data select CTRL-END . To go to the top again (cell A1) select CTRL-HOME . For other shortcuts look in Excels Help and enter “keyboard shortcuts” in the Index search . 3 . When I drag a formula down over lots of rows I keep over-shooting the row I want . Excel is too fast for me, please help . It can be frustrating trying to drag a formula down with the mouse, so try these shortcuts:- D-Click with Adjacent Cells: If you have a formula that has data in the adjacent column, you can copy the formula down to the end of this adjacent column by double clicking the fill handle (little square at bottom of selected cell) . Using GoTo and FillDown: You can use the GoTo option to select a range from your current position then the FillDown option to fill-in the cells with your formula . Here’s how: - a) Select the cell where you have your formula you wish to copy . b) Select Edit-GoTo from the Menu or CTRL-G c) Enter the last cell where you want to copy the formula to in the Reference box d) Hold down the Shift key and select the OK button . You have now made a selection of the rows e) Select Edit-Fill-Down (or another direction as required) to copy the formula 4 . I am getting a #Name? error in a Vlookup function . What does this mean and how can I fix it? The #Name? error is Excel’s way of telling you it doesn’t recognise a named reference in your formula . In a Vlookup formula your looking up a name then obtaining a value based on that corresponding name in a specified range . Excel cannot find the name in that range so it cannot give you a value and thus returns an error instead . You can fix this by simply adding the name in the range you are referring to . However this is not always convenient and may not make sense for you . For example, what if you are using Vlookup to find a discount rate for a customer . Should you have to add all of your customers whether they get discounts or not & put in a zero discount factor for each not receiving a discount? Of course not, you only want to list those customers who actually get a discount . To fix this problem use a combination of the IsError, If and Vlookup formulas . In this example we will put a 0 value if the vlookup returns an error & where [vlookup] is your current vlookup formula: - = If ( IsError ([vlookup]) = false, [vlookup], 0) The IsError function determines whether the Vlookup formula will return an error, and returns a true/false answer . If the answer is false (ie no error & vlookup found a customer with a discount) then complete the vlookup as normal and return the discount rate . If the answer is true (there was an error), then return a zero discount rate instead . 5 . Use conditional formatting to format your spreadsheet Not only can you use Excel to do calculations on figures, you can format cells based on a formula criteria set by you . To do this use Excel’s conditional formatting feature . a) Select the range of cells to be included in the criteria b) Select Format-Conditional Formatting from the Menu c) Select the criteria you require, either based on a cell value or where you can enter a formula Example 1: Format cells where sales values are between than $100 and $200 . a) Select a range that includes sales values b) Select Format-Conditional Formatting from the Menu c) Select options “Cell Is”& “Between” then enter 100 in the first box and 200 in the second box d) Select the format button and choose your Font, Border & Patterns that you want to apply to these cells . e) Select OK, then OK Example 2: Format cells so that each alternate row is coloured f) Select a range that includes sales values g) Select Format-Conditional Formatting from the Menu h) Select option “Formula Is” and enter the formula “=MOD(ROW(),2)=0” i) Select the format button and choose Patterns that you want to apply to these cells . There are many different types of formatting you can do, perhaps only limited by your imagination . If you think of other helpful tips, why not share them with other PF1 users . Good Links for Excel Help http://www . mrexcel . com/ . cpearson . com/excel/topic . htm" target="_blank">www . cpearson . com http://www . sheet . com -- Excel experts Original FAQ available from here ( . pcworld . co . nz/thread . jsp?forum=1&thread=23525&message=72879&q=faq+%2351#72879" target="_blank">pressf1 . pcworld . co . nz) .	-FAQ- (807)
1