web 2.0


Excel Highlight the Active Cell

In a video (http://www.youtube.com/watch?v=bGjqDGF7xaM) published on YouTube back in 2008, I explained how to highlight the active row in Excel. In this brief article, I discuss how you can highlight the active cell in Excel. In this particular case, I will only change the font color of the cell, but you can later change the conditional formatting and apply whatever formatting you wish.

Here's how it is done:

1. Create a named range called AddressOfActiveCell using the formula: =ADDRESS(5,6)
2. Select the entire worksheet and apply a conditional formatting using the formula (the active cell being A1 upon selection): =ADDRESS(ROW(A1),COLUMN(A1))=AddressOfActiveCell
3. Now, open the visual basic editor and insert the following code:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

    With ThisWorkbook.Names("AddressOfActiveCell"
)
        .Name =
"AddressOfActiveCell"
       
.RefersToR1C1 = "=address(" & ActiveCell.Row & "," & ActiveCell.Column &
")"
   
End
With

End
Sub

Check the video tutorial here:

Tags: , , , , ,

Microsoft Excel - VBA

Excel 2010: Conditional Format Data Bars Improvements

Excel 2010 reinvents the conditional format for data bars. We now have more control over the conditional formatting as a whole and not only databars; however, with data bars we now have the option to plot negative values as well.

Here’s how it looks like:


Figure 1: Excel 2010 Conditional Format Data Bars

Once we apply one of the built-in conditional formats for data bars, we can access the Conditional Format Manage Rules dialog box in order to edit the new conditional format rule:


Figure 2: Determining Negative Value and Axis...


Finally, you can determine various aspects of how the value are displayed as well as the axis:


Figure 3: Determining data bars aspects such as axis settings

Tags: , , ,

Microsoft Excel | Microsoft Office 2010

Excel 2010 No Cell Icon

Excel 2007 introduced Icon Sets to Excel Conditional Formatting. Excel 2010 introduces No Cell Icon to Conditional Formatting. In Excel 2007, after applying your Icon Set Conditional Format, you would get something like this:


Figure 1: Icon Set Conditional Format

The problem, of course, is that the whole this is a mess. If the idea is to identify poor grades, you’d need to create extra Conditional Formats so that you could hide the undesired icons. Excel 2010 introduces No Cell Icon which removes the need for extra formulas and extra Conditional Formats. Here’s how it looks like:


Figure 2: No Cell Icon

By choosing No Cell Icon, Excel 2010 will omit the icons for the selected conditions saving you all the hassle of extra conditioning:

 


Figure 3: Result after applying No Cell Icon

Tags: , ,

Microsoft Excel | Microsoft Office 2010

Office 2010 Backstage View: How Excel Options should look like

VERSION: 14.0.4006.1110

A few days ago, I wrote about the "Backstage View" (see article here: http://www.msofficegurus.com/post/Office-2010-Backstage-View.aspx) which is a new addition to Microsoft Office 2010. However, when we move to Excel Options (or any other "Options" in Microsoft Office), the well-know dialog box is opened:


Figure 1: Excel Options dialog box

I think that a better way to present the Options dialog box is to embed it in the Backstage View. Here's how it might look like:


Figure 2: Options dialog box embeded

Tags: , , ,

Microsoft Excel | Microsoft Office 2010