EXCEL: Recovering Unsaved Workbooks in 2010

Leave a comment

The most important rule is Properly SAVE your Work!  However there are times when:

  • you forget to save a workbook
  • you accidentally save it when you should have saved it as a different name
  • lose work due to  power outage

So DON”T PANIC, you can quickly and easily recover older (unsaved) versions of your workbook.

Excel 2010 saves your unsaved versions of files in a specific folder on your hard drive. Please take note that these are new, unsaved files or temporary files, similar to files that you open from an email or file transfer. Here are the steps to recover the unsaved file:
To recover an unsaved version of a file:

  1. Open Excel 2010 and click on the File tab.
  2. Click Recent on the left, and then click the Recover Unsaved Workbooks button.
  3. In the Open dialog box, you’ll see the contents of your UnsavedFiles folder. For Windows Vista/7, this location is: C:\Users\User_Name\AppData\Local \Microsoft\Office\UnsavedFiles. For Windows XP, the location is: C:\Documents and Settings\User_Name\Local Settings\Application Data\Microsoft\Office\UnsavedFiles.

Note that the files saved in your Unsaved Files folder will no longer be available after four days from the file’s creation or modification.

EXCEL:How Large is the Excel Worksheet?

1 Comment

By pressing Ctrl+End, the active cell will automatically jump to the last cell that has input in the worksheet of an Excel workbook. Therefore, there is no data beyond this row or no data beyond this column anywhere in the worksheet. And the opposite of Ctrl+End is Ctrl+Home.

EXCEL:Using Custom Rules for Conditional Formatting

Leave a comment

Recently, I was asked how to create Conditional Formatting using different rules.  I found a site from About.com Spreadsheets and it actually explains it.  Here it is!

Finding Data that Exceeds 25% and 50% Increases with Conditional Formatting

In the following example, two custom conditional formatting rules will be applied to the range of cells B2 to B5.

custom rules 1

  • the first rule checks to see if the data in cells A2 to A5 is greater than the corresponding value in B2 to B5 by more than 25%
  • the second rule checks to see if the same data in A2:A5 exceeds the corresponding value in B2:B5 by more than 50%

As can be seen in the image above, if either of the above conditions is true, the background color of the cell or cells in the range B1:B4 will change.

  • for data where the difference is greater than 25%, the cell background color will change to blue
  • if the difference is greater than 50% the cell background color will change to red

The rules used to accomplished this task,

  = (A2-B2)/A2 > 25%
  = (A2-B2)/A2 > 50%

will be entered using the conditional formatting New Formatting Rule  

As mentioned, the conditional formatting rules that check for the two conditions will be entered using the conditional formatting New Formatting Rule dialog box.

 

Setting conditional formatting to find greater than 25% increase

 

    1. Highlight cells B2 to B5 in the worksheet
    2. Click on the Home tab of the ribbon
    3. Click on the Conditional Formatting icon in the ribbon to open the drop down menu
    4. Choose New Rule to open the New Formatting Rule dialog box as seen in the image above
    5. In the top half of the dialog box, click on the last option: Use a formula to determine which cells to format
    6. In the bottom half of the dialog box, click in the Format values where this formula is true: line
    7. Type the formula: = (A2-B2)/A2 > 25% in the space provided
    8. Click on the Format... button to open the Format Cells dialog box
    9. In this dialog box, click on the Fill tab and choose a blue fill color
    10. Click OK twice to close the dialog boxes and return to the worksheet
    11. At this point, the background color of cells B3 and B5 should be blue

 

Setting conditional formatting to find greater than 50% increase

 

    1. With cells B2 to B5 still selected, repeat steps 1 to 6 above
    2. Type the formula: = (A2-B2)/A2 > 50% in the space provided
    3. Click on the Format... button to open the Format Cells dialog box
    4. Click on the Fill tab and choose a red fill color
    5. Click OK twice to close the dialog boxes and return to the worksheet
    6. The background color of cell B3 should still be blue indicating that the percent difference between the numbers in cells A3 and B3 is greater than 25% but less than or equal to 50%
    7. The background color of cell B5 should change to red indicating that the percent difference between the numbers in cells A5 and B5 is greater than 50%

Entering the Tutorial Data

custom rules 2

  1. Enter the data into cells A1 to C5 as seen in the image above

Note: Step 3 of the tutorial will add formulas to cells C2:C4 that show the exact percentage difference between the values in cells A2:A5 and B2:B5 in order to check the accuracy of the conditional formatting rules.

As mentioned, the conditional formatting rules that check for the two conditions will be entered using the conditional formatting New Formatting Rule dialog box.

Setting conditional formatting to find greater than 25% increase

    1. Highlight cells B2 to B5 in the worksheet
    2. Click on the Home tab of the ribbon\
    3. lick on the Conditional Formatting icon in the ribbon to open the drop down menu
    4. Choose New Rule to open the New Formatting Rule dialog box as seen in the image above
    5. In the top half of the dialog box, click on the last option: Use a formula to determine which cells to format
    6. In the bottom half of the dialog box, click in the Format values where this formula is true: line
    7. Type the formula: = (A2-B2)/A2 > 25% in the space provided
    8. Click on the Format... button to open the Format Cells dialog box
    9. In this dialog box, click on the Fill tab and choose a blue fill color
    10. Click OK twice to close the dialog boxes and return to the worksheet
    11. At this point, the background color of cells B3 and B5 should be blue

Setting conditional formatting to find greater than 50% increase

    1. With cells B2 to B5 still selected, repeat steps 1 to 6 above
    2. Type the formula: = (A2-B2)/A2 > 50% in the space provided
    3. Click on the Format... button to open the Format Cells dialog box
    4. Click on the Fill tab and choose a red fill color
    5. Click OK twice to close the dialog boxes and return to the worksheet
    6. The background color of cell B3 should still be blue indicating that the percent difference between the numbers in cells A3 and B3 is greater than 25% but less than or equal to 50%
    7. The background color of cell B5 should change to red indicating that the percent difference between the numbers in cells A5 and B5 is greater than 50%

Calculating % Difference

To check that the conditional formatting rules entered are correct, we can enter formulas into cells C2:C5 that will calculate the exact percent difference between the numbers in the ranges A2:A5 and B2:B5

custom rules 3

    1. Click on cell C2 to make it the active cell
    2. Type in the formula =(A2-B2)/A2 and press the Enter key on the keyboard
    3. The answer 10% should appear in cell C2, indicating that the number in cell A2 is 10% larger than the number in cell B2
    4. It may be necessary to change the formatting on cell C2 in order to display the answer as a percent
    5. Use the fill handle to copy the formula from cell C2 to cells C3 to C5
    6. The answers for cells C3 to C5 should be: 30%, 25%, and 60%
    7. The answers in these cells show that the conditional formatting rules created are correct since the difference between cells A3 and B3 is greater than 25% and the difference between cells A5 and B5 is greater than 50%
    8. Cell B4 did not change color because the difference between cells A4 and B4 equals 25%, and our conditional formatting rule specified that a percentage greater than 25% was required for the background color to change to blue

Applying Conflicting Conditional Formatting Rules

custom rules 4

When multiple rules are applied to the same range of data, Excel first determines if the rules conflict.

Conflicting rules are those where the chosen formatting options for each rule cannot both be applied to the same data.

In the example used in this tutorial, the rules conflict since both rules use the same formatting option - that of changing the background cell color.

In the situation where the second rule is true - the difference in value is greater than 50% between two cells - the first rule - the difference in value being greater than 25% is also true.

Excel's Order of Precedence

Since a cell cannot have both a red and blue background at the same time, Excel needs to know which conditional formatting rule it should apply.

Which rule gets applied is determined by Excel's order of precedence, which states that the rule that is higher in the list in the Conditional Formatting Rules Manager dialog box has precedence.

As shown in the image above, the second rule used in this tutorial (= (A2-B2)/A2 > 50%) is higher in the list and, therefore, has precedence over the first rule.

As a result, the background color of cell B5 is changed to red.

By default, new rules are added to the top of the list and, therefore, have a higher precedence.

To change the order of precedence use the Up and Down arrow buttons in the dialog box as identified in the image above.

Applying Non - Conflicting Rules

If two or more conditional formatting rules do not conflict both are applied when the condition each rule is testing becomes true.

If the first conditional formatting rule in our example (= (A2-B2)/A2 > 25%) formatted the range of cells B2:B5 with a blue border instead of a blue background color, the two conditional formatting rules would not conflict - since both formats can be applied without interfering with the other.

As a result, cell B5 would have both a blue border and a red background color, since the difference between the numbers in cells A5 and B5 is greater than both 25 and 50 percent.

Conditional Formatting vs. Regular Formatting

In the case of conflicts between conditional formatting rules and manually applied formatting options, conditional formatting rule always takes precedence and will be applied instead of any manually added formatting options.

If a yellow background color was initially applied to cells B2 to B5 in the example, once the conditional formatting rules were added, only cells B2 and B4 would stay yellow.

Because the conditional formatting rules entered apply to cells B3 and B5, their background colors would change from yellow to blue and red respectively.

Interesting CTRL commands

1 Comment

CTRL+Q quits your application!

CTRL+W closes your file and will prompt you to save your file if necessary.

CTRL+SHIFT+C copies the format of the text (click in the text)

CTRL+SHIFT+V is the format painter (click in the text)

EXCEL: Convert Function

1 Comment

Excel includes the CONVERT function as part of its Analysis ToolPak add-in. The Analysis ToolPak is included with Excel, and it’s a breeze to install.

To install the Analysis ToolPak add-in:

  1. Choose Tools | Add-ins from the menu bar. (In 2007, click on the Office button and choose Excel Options, then click on Add-Ins, then click Go. In 2010, select the File tab and choose Options, then click on Add-Ins, then click Go.)
  2. In the Add-ins dialog box, select the check box next to Analysis ToolPak in the list box and click OK.

To convert miles to kilometers:

  1. Select cell E5 and type =CONVERT(.
  2. Click the Paste Function button to access the Function Arguments window.
  3. Select cell D5, which holds the value you wish to convert, so the cell reference appears in the Number text box.
  4. Press [Tab] and enter mi in the From_Unit text box.
  5. Press [Tab], enter km in the To_Unit text box, and click OK.
  6. Select cell E5 and drag its fill handle down to cell E9 to apply the formula to the other cells.
  7. Select the range E5:E9 and click the Decrease Decimal button on the Formatting toolbar until the values display in tenths of a kilometer.

The CONVERT function also converts units of measurement for mass, pressure, liquid, and many others. For a complete list, along with the unit abbreviations accepted by the function, type CONVERT in Excel’s Help window.

Once you create a worksheet based on our example, you can use the CONVERT function to instantly perform the conversions from miles to kilometers.

POWERPOINT, WORD & EXCEL: Screen Clipping Saves the Day

2 Comments

Screenshot feature in 2010 allows you to instantly copy an image from any open program.  There are two ways you can use Screenshot. The first option allows you to choose one of the opened program and insert the entire screen image (this is exactly like a Print Screen). The second option allows you to go directly into the opened program that you came from and clip a portion of that screen.

Screenshot is more efficient than Cropping since cropping keeps the entire image in the application even though you hid parts of it.  Also, if you want to save that portion as a .jpeg file, unfotunatley, the ENTIRE image is saved.

To capture a screenshot as a Print Screen:

  1. Make sure you have one or more program(s) opened.
  2. Go to the Insert tab.
  3. Click on the Screenshot icon under the Illustration group.
  4. Click on one of the thumbnails that represent the opened files. (Tip: Hover your mouse pointer over a thumbnail to see the filename and application of the potential screenshot.
  5. The screen image is now inserted into the application that you are working on.

To clip a portion of the opened program.

  1. Click on the opened program and then immediately go back into the application you are working on.
  2. Go to the Insert tab.
  3. Click on the Screenshot icon under the Illustration group.
  4. Click on Screen Clipping.
  5. This action will place you immediately into the application that you came from.
  6. WAIT for the screen to go WHITE.
  7. A crosshatch will appear. Click and drag and select the portion of the screen you ONLY want.
  8. The selected item is now inserted into the application that you are working on.

 

EXCEL: Using HyperLinks Instead of Custom Views to Help Navigate the User

Leave a comment

Prior to Excel 2007, you were able to create custom views and place the Custom View icon on the menu bar and a down arrow appeared to the right of the icon.  This allowed users to select the view that wascreated for their particular viewing.  It was a quick way to navigate to a particular part of the spreadsheet.  Unfortunately, this method is no longer available.  Instead, you create the views as before but to go to one of the views, you now click on the View tab, select Custom Views, choose a View, and then click on Show.

This method has too many steps; therefore, I created hyperlinks to do the same job but on with one click. Here are the steps:

  1. Name the highlighted range that you want the user to go to.
  2. In an empty sheet, I call it the “Menu”sheet.  Design it in order to direct the user to go to a range on found in another sheet or to a specific sheet.
  3. Choose Insert and select the Hyperlink option (or Control+K) to open the Insert Hyperlink dialog box.
  4. Click the Place In This Document icon on the left.
  5. You can Choose three ways:
    1. choose the range name under the Define Names
    2. choose a sheet name
    3. choose a sheet name with a specific cell location
  6. Click OK to create the hyperlink.

Older Entries

Follow

Get every new post delivered to your Inbox.

Join 468 other followers