EXCEL: 11 very useful excel keyboard shortcuts

Leave a comment

11 very useful excel keyboard shortcuts

EXCEL: Double Click Tricks

Leave a comment

  1. Double Click on the XL logo on the very top left corner to Close Excel.
  2. Adjust column widths by selecting multiple columns and double clicking on the separators (the thin line between the column letters).  This method also works for the rows.
  3. Auto-fill a series of cells with data or formulas using the double-click. Lets say you have a table where in one column you have some data and in the next you have written a formula in the first row. Simply, select the one cell then double click on the autofill box which can be found at the bottom right corner of the cell.  The mouse converts to a skinny black cross. The trick works for formulas, auto-fills (of numbers, dates, what not) as long as the adjacent column has data.
  4. Jump to last row/column in table with double-click. Just select any cell in the table and double click on the cell-border in the direction you want to go.
  5. Lock a particular feature and reuse them with double-click. You can lock any repeatable feature (like format painter, drawing connectors, shapes etc.) by just double clicking on the icon (in Excel 2007 this works for format painter, but for drawing shapes you need to right click and select lock drawing mode). This can save you a ton of time when you need to repeat same action several times.

EXCEL: Insert Multiple Columns at Once

Leave a comment

Suppose you have three columns (Column B, Column C, and Column D) that contains information, then you want to insert blank columns in between these columns here is how to do it:

  • Hold down control key.
  • Select one column at a time
  • Right click and choose Insert
  • Done!

Suppose now you want to insert three blank columns to the left of columns Column B,  here is how to do it:

  • Select column B, C and D at a time
  • Right click and choose Insert
  • Done!

Announcing my new app: Ease-eKeys

2 Comments

I am proud to announce my apps for the iPhone and iPod. It called Ease-eKeys. Everyone always asks what is that shortcut command in Word that does…well, my apps does just that. For 99 cents you will get all the shortcuts and keyboard commands for Word Excel PowerPoint and OneNote. The iPad apps will come down the road. Send me your comments. Thank you!

Ease-eKeys photo 1 Ease-eKeys photo 2 Ease-eKeys photo 3 Ease-eKeys photo 4

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.

Older Entries

Follow

Get every new post delivered to your Inbox.

Join 472 other followers