Monday, October 31, 2005

Recovering Data from a Corrupted Workbook that cannot be opened

If you have ever had an excel document crash and not be able to be opened then this tip is an excellent one. I have tried it on 1 file and it worked.

From ExcelTip.com

To recover data from a corrupted workbook:

1. Add a new workbook, and save it using the name of the corrupted workbook, but in a different folder than the original (in the screenshot, the workbook name is Financial Statements.xls).
2. Change the new sheet names to those in the corrupted workbook (try to remember all of them), and then save the workbook.
3. Add a new workbook. In cell A1 of the first sheet, insert a linked formula to cell A1 in the newly created workbook (steps 1 and 2).
4. Copy the formula to all cells covering the data area (as you remember it to be) in the first sheet of the corrupted workbook.
5. Repeat steps 3 and 4 for all sheets, and then save and close the new workbook.
6. Change the linked source address. From the Edit menu, select Edit Links, and then click Change Source.
7. Locate the corrupted workbook, select it and then close the Edit Links dialog box.
8. Check the result. If the data appears in the new workbook, copy all cells in each sheet and paste them back as values.


Posted by: Jethro | 10:38 AM |

Tuesday, October 25, 2005

Number of entries in a column or row in Excel

This one from Troy

If you want to know how many rows have entries in a particular column (works with columns as well as rows), create an array formula as follows:

# of rows with non blank cells
{=MAX(ROW($B:$B))-COUNTBLANK($B:$B)}

# of columns with non blank cells
{=MAX(COLUMN($2:$2))-COUNTBLANK($2:$2)}

(remember array formulas are created without the curly brackets and entered by holding down CTRL and SHIFT when hitting Enter)

Especially handy if you know that all the rows up to a certain point contain data yet you're not certain just how many rows there are and or your looking for the last cell in a column for use in a dynamic offset,
Simply doing {=MAX(ROW($B:$B))} would return the total number of cells 65,536

*NB
=OFFSET( ) only works if the target wBook is open (at least in Excel 2000)


Posted by: Jethro | 5:13 PM |

Tuesday, October 18, 2005

Using the Find and Replace feature of Excel

Excel has some powerful Find and Replace features. Using them correctly in order to achieve the desired result is not always easy however.

Clicking Edit | Find on the menu or CTRL+F will open the Find Dialog Box. Ctrl+H gets you the replace one. These are actually two different tabs in the same dialog box.

In both cases you are presented with a search box and Replace also gives you a replace box. There is also a button called Options which is most useful. Here you can extend your find and replace options to include formats, both to find and to replace.

The remainder of the advanced options are also little used by many people but give you some very powerful control over the results.

  • Within Sheet or Workbook, In versions earlier than Office 2003 this wasn't available and by default it is Within Sheet. Changing to Within Workbook will search through the entire workbook.

  • Search By Rows or By Columns. Unless you have a very large set of data or a very slow computer changing this option won't affect the speed much. However it can make a difference to the direction of the search if you want to find results in a certain order.

  • Look in Formulas, Values or Comments. This option is only available to find. Replace looks in formuals only. Changing this option allows you to control where you search. Looking for 45 in formulas will find any cell with 45 in it, but also any cells that reference row 45 or any other row with 45 in it eg. row 457. Conversely it will not find a cell that has a formula =3+42 in it. Looking in Values will find this cell as well as cells with 45 in them and exclude the cells with row references of 45. Searching commments also is a good way to find text within comments that are other wise hidden.

  • Match Case and Match Entire Cell Contents help eliminate partial results. Eg searching for 45 will not return 450 if Match Entire Cell Contents is selected.


    • Using the Find All box generates a list of all the results and allows you to select them changing the background focus in the worksheet to the selected cell. (Available in Office 2003)

    Using the Replace All can be dangerous. I tend to select the cells that I want to work with first. If you are trying to change data inside just one cell then select it and a blank cell. Otherwise Replace All will operate on the entire worksheet.


    Posted by: Jethro | 1:13 PM |

    Friday, October 14, 2005

    Using the SUBSTITUTE Function

    Adapted from Exceltip.com. This is a good tip to be used when you have numeric data with spaces mixed in it.

    For example a column of data where the thousand commas have been replaced with spaces.
    1 100
    2 200
    3 300
    etc

    To add these up (including numeric data that is correctly entered) you can use an array formula utilising the SUBSTITUTE function as follows:
    {=SUM(VALUE(SUBSTITUTE(A1:A4," ","")))}
    where A1:A4 is your data range (with no blank cells) and the curly brackets on the end are not typed in but appear when you complete the formula using CTRL SHIFT ENTER to create the formula.


    Posted by: Jethro | 10:04 AM |

    Friday, October 07, 2005

    Finding Text in Excel


    I had a request today for help. A client wanted to be able to look in a cell and based on if some words were found in that cell do something to another cell different from what he would have otherwise. To be specific. Column B had descriptions in it and column C a price. If a cell in column B had the words "No tag" in it then he wanted to add $25 to the price in C and the answer needed to be in D. Else the price should remain the same.

    Here is the formula that I used:
    =IF(ISERROR(FIND("No tag",B2,1))=TRUE,C2,C2+25)

    This formula uses the FIND function to look for the text "No tag" in Column B. If it doesn't find it it returns a #VALUE! error. I used the ISERROR = TRUE to capture this instance, and the IF statement surrounding it all process TRUE to the error and does not change the price. If it does find the text then the error doesn't occur and the ISERROR statement returns FALSE and the formula adds $25 to the price.


    Posted by: Jethro | 12:49 AM |

    Wednesday, October 05, 2005

    Office 12


    The new and coming version of Office has some different features to the current version, Office 2003.
    Specifically the User Interface has changed somewhat.
    Microsoft states:
    "When computers were new, and people were just learning how to use them, it was very helpful to have Microsoft Word, Excel and PowerPoint look very similar to each other. But over time, as we’ve added more and more features, it’s gotten harder for users to find the things they want to do with the product. Word 1.0 only had about 100 commands, and you could go through the menus and see everything you could do. But Word 2003 has over 1,500 commands, many of which are harder to find. That’s one of the key issues the new UI addresses."

    "There are far too many features to describe in any detail, but here are a few of the key innovations. The main part of the user experience is code-named the “ribbon.” It’s the one place you go to find the commands that are all about authoring –creating the document, the presentation or the spreadsheet you’re working on. There’s no longer a stack of task panes and menus and toolbars to look through. There’s just one place to look for commands.
    Office "12" Excel Screenshot.
    Office "12" Excel Screenshot.
    High-res version.

    Another feature is “galleries.” Galleries give you a visual representation of the kinds of formatting choices you can make in your document without needing to set a number of individual elements to achieve it. For example, if you want your margins to be wide or narrow or short or tall, you can go to a gallery for a visual image of what that would look like all at once instead of needing to changes several items in a dialog box. The galleries also offer “live previews” in many instances, so you can see exactly what the document is going to look like before you make the choice, which makes it easier to experiment. For example, with something simple like fonts, you can select the text in your document, go up to the font drop-down menu, and by just rolling down the menu you can see the font change happen simultaneously in the document before you’ve selected the font you want. It makes it easier to create a document that looks the way you want it to look. These live galleries are almost everywhere in the product – it’s a try-it-before-you-buy-it kind of thing that cuts out lots of steps.

    A feature code-named “Super Tooltips” integrates Help topics into the product in a new way. One of the main problems that people have with Help topics today is that they don’t know the terms used to describe features. Super Tooltips are integrated Help tips that provide quick access to information about a command directly from the command’s location in the ribbon. The tooltip itself will usually give you enough information about what that feature does so that you can use it.

    Another feature is the “Quick Launch Toolbar,” which allows you to customize the UI by adding as many commands as you like to a toolbar. It’s a place where the user can collect the specific set of commands they use frequently. There’s also a feature code-named “Floatie” which is a formatting tool that presents the most common text formatting features on a tool panel that “floats” over the selected text - improving formatting efficiency by eliminating mouse trips to the command area. So, for instance, if you’re in the picture tools and you notice that your heading needs to be bold, the Floatie means you don’t have to switch all the way to another tab just to make that change."


    Posted by: Jethro | 10:16 AM |

    Sunday, October 02, 2005

    Testing your work in Excel


    I have just sat down with a nice tall glass of iced coffee to finalise delivery of a job to a client.
    I thought I would write some notes about checking your work in Excel before delivering it to the client, boss, customer etc.

    I have in the past been caught out many times by not properly checking work. (You would think I would learn!) I will qualify that by saying its the user's fault - always!

    Seriously though it is easier to check something three times then to hand it in and get egg on your face when the customer asks why 40 + 30 + 20 = 70.

    Some of the most common errors to look for when proofing your work are:
  • Sums that do not include the entire range. Make sure that row and column totals are right.

  • Spelling mistakes - use F7 to check the spelling.

  • Incorrect decimal places. If using divisors or multipliers check that the decimal point is in the right place. The data may mean nothing to you but the client might have kittens if the decimal place is out!

  • Make sure you set up print areas, margins, headers and footers and other print settings for reports. Test them. They may look OK on the screen but may print differently. Check on their printers also.

  • Users often misunderstand what you think is obvious. Check that the data entry areas are tested thoroughly. When creating large data entry areas test them ALL. I use a formula like =RAND()*1000 to generate a random number and then copy that over all the data entry areas.

  • Enter a space or text in a number data area and see what happens. Change values to zero. Do you get #REF! or #DIV/0! or #N/A! errors?

  • Make sure that if you don't protect formulas and calculation areas you don't want users to touch that you hide them or change the format etc so that they are obviously not data entry areas.



    • There are lots more things to check but thats a goood start.


    Posted by: Jethro | 8:08 PM |