Sunday, June 14, 2009

Excel: Coloring Alternate Rows

Excel How To Automatically Color Alternating Rows in Excel using Conditional Formatting. How to highlight every 5th row. (Keywords: Greenbar; every other row)

Follow these steps to "conditionally" format cells so they have alternating colors on every other row (or other number). Doing this can make a sheet easier to read and follow. The formatting automatically adjusts when new rows and columns are inserted or deleted.

Steps for Excel 2003 and Older
(Steps for Excel 2007, below)

1. Highlight the cell range where you want the alternating rows or highlight the entire rows.

2. Menu: Format, Conditional Formatting

3. In the 'Condition 1' pull-down, select "Formula Is"

  • In the Formula bar, type this formula, including the equal-sign:
  • Click the Format Button.
  • Along the top row of tabs, choose [Patterns]. See below for an illustration.
  • Change the Background color to a light tan or soft-green
  • Okay, Okay

Steps for Excel 2007(with the Ribbon Bar Menu; see above for older versions of Excel)

1. Highlight the range of cells where you want the alternating rows to be applied or select the entire rows.

2. On the "Home" Ribbon, select "Conditional Formatting".
Click image for a larger view; click Back to return.

3. Select New Rule
  • In the Rule Type Dialog, choose "Use a formula to determine which cells to format"
  • In the formula bar type this formula:
  • If copying and pasting, be sure you don't end up with any quotes, as in ="Mod(Row(), 2)"
  • Click the "Format..." button; then choose the [Fill] tab.
  • Choose your favorite color; click OK, OK

Variations - Every 5th row:

The formula:  =Mod(Row(),5) highlights all rows except those divisible by 5 -- in other words, each row, except those evenly divisible by 5, would get a "1,2,3, or 4" in the remainder.  This inverts the highlights, where every row is highlighted but the fifth ones.  This is probably not what you want.

To highlight every 5th row, use a slightly more complicated formula that resolves to a True or False:


Change the word "Row" to "Column" to highlight alternating columns.


The "row()" function returns the current row-number. As an aside, =row(C15) returns row-15.

The Mod function divides the current row number by (2) and records the "remainder" (remember grade-school division?). With this division, the rows are either odd or even (a remainder of zero or one).

Notice the Conditional format is set to trigger when the formula is "true". Here is the trick: In Excel-lingo, a "1" is true and a zero (0) is false (actually, in this case, any number greater than zero is considered True). Because the MOD formula always resolves to a 1 or zero (T/F), this is enough to set the conditional and trigger the formatting. A simplified example:

Notice how the row you highlighted can be either shaded or not -- the calculation is based on the physical row number, not on where you started the process.

Performance Considerations:

Applying a conditional format to individual cells introduces extra calculations with each cell.

If applied to tens of thousands of cells, this has the potential to impact performance. Although I have not tested this, I suspect conditional formatting applied to the Rows (rather than the cells) is more efficient because the rows would inherit a default formatting and each cell within would not have to be re-calculated.

On large sheets, I recommend applying conditional formatting to the rows instead of individual cells.

Modifying Existing Rules:
To change an already-built rule, click "Conditional Formatting", Manage Rules. Select the specific rule to change; then "Edit Rules".

Click "Conditional Formatting", "Clear Rules" to remove the rule.

Related Keyliner Articles:
Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

Thursday, June 11, 2009

Dell Inspiron Mini 10

Brief product review on the Dell Inspiron 1011 (Mini 10) Netbook computer as well as answers to screen resolution and wallpaper questions.

Note: Most searches targeting this article are looking for the answer to one of these two questions: 1) How to set the wallpaper/background and 2) how to change the default resolution. Both are somewhat answered immediately below. The remainder of this article is a review of the Dell Mini 1011 notebook.

Setting the Default Wallpaper

If you are having problems setting the background wallpaper, it is because the Windows 7 Starter Edition is installed and amazingly, Microsoft disabled the feature for branding reasons with other OEM's.

Final results: There is no native way to change the Wallpaper and you will have to rely on a third-party. You may consider this link, which I've not tried myself because I do not have the Starter Edition:

Changing the Default Screen Resolution

The screen resolution is physically set to 1024 x 576, Wide screen 16:9 aspect ratio, and this resolution cannot be changed (due to the physical nature of LCD screens). This is by design. Read the review below for additional details.

Dell Inspiron 1011 Mini Review

My family bought a Dell Inspiron 1011 "mini netbook," sight- unseen, to replace my wife's dying notebook. The goal was to have a small, lightweight computer for email, light surfing and other such needs and we wanted a machine small enough to sling around the house. Nor did we want to spend a boat-load of money. For the same price, we considered the Amazon Kindle but decided a general-purpose device was better -- we now firmly believe this was the correct decision.

In Summary:

  • An overall good Netbook; recommended
  • Very good keyboard
  • No issues with speed; 1GB Ram, upgradeable to 2G
  • Originally and still ships with Windows XP
  • Works very well surfing, email, short documents, etc.
  • Runs Office 2007 without issues
  • Minor issues with the touchpad
  • Screen width at 1024 is nice
  • Screen depth at 576 is a concern
  • Small, very portable, 1" thick
  • Adequate ports
  • Acceptable 2.5hr battery life

Overall Size:

The size is remarkably small and Dell's web site does not give the machine an adequate sense of scale; hopefully the pictures in this blog and my descriptions will give a better feel for the machine's real size.

From above, the netbook is smaller than an 8x10 sheet of paper. Edgewise, with the lid shut, it is an inch thick and oddly, that inch feels bulky in relation to its other dimensions. From the photo below, the netbook is dwarfed by my daughter's small, 15" laptop (--that is not a 17" laptop in the background).


The keyboard is a 90% keyboard that is relatively comfortable and we have no complaints. The main keys are nearly a standard width while the key-height is slightly narrower. All keys have good vertical travel.

  • Enter, Shift and Control Keys are all properly sized
  • Backspace key is nearly full-size and is in a good position
  • Fkeys are considerably smaller than normal
  • Home, End, Insert and Delete are small, along the top row
  • Arrow keys are offset (yea!) , in a standard T-shape; smaller than normal, but better than most laptop keyboards.

The Screen:

The screen is noticeably shortened (1024 x 576 - 16:9 Widescreen resolution) and almost all new netbooks, from any brand, uses this same screen. Because of its height, you will have to scroll down in all websites. My wife says you have to scroll in most sites anyway so this is little change; There is some truth to this observation. However, there is no doubt this has been annoying.

Actual Screenshot:

Physically, the screen has a 3/4" plastic bezel around the outside edges, making it look somewhat old fashioned. We wished the extra inch of space on the top and bottom bezels were used for more glass. The reason for the size is they are trying to stay at a 16:9 aspect ratio.

To maximize screen real estate, set the Windows Task bar to "autohide" (other mouse-click the task bar, selecting properties) and remove all browser toolbars from the top row.

Several programs, such as Microsoft Works (included), have warnings about not running properly because of screen resolution but I had no problems installing and using Office 2007.

Several drivers, especially HP Photosmart Deskjet Printer drivers will not install because the screen resolution is too small to display the printer dialogue boxes and after a lot of research, I've not found a way around this problem.

The 576 pixels are bothersome and this is the main reason this machine has not completely replaced my wife's desktop computer. However, it is undeniably small and that is why we bought it. It has been on several trips and has performed admirably.


The left and right mouse buttons are mechanical and are built into the pad itself. Pressing on the lower corners distorts the pad and acts as the button. This is a novel approach and has taken some getting used to. Because the buttons are built in, you can not "feel" them until you click. The mouse-buttons take a bit more pressure than we would like. You can, of course, tap the pad anywhere on the pad for a normal left-click and for clicking and dragging.

With the touchpad, you can scroll by sliding your finger along the right and bottom edges, which moves the window's scroll bars -- but like all the other touchpads I have met, the idea seems to be a suggestion, often ignored. In the Mouse control panel, be sure to set the scroll-width zone as wide as possible.

I have seen other reviews complain about the touchpad, but we have not found any substantive issues beyond the minor ones mentioned above.

Speed and Memory and Other Goodies:

Since this is not a workhorse and we have no intention on taxing it, the default 1G of RAM (upgradable to 2GB) is adequate, especially with Windows XP. We have found basic editing, word processing, and surfing speeds are all good, with no complaints.

It has three USB ports, wired and wireless network, VGA analog out, audio-in and out, media-card reader and a webcam.

Although it comes in other colors (blue, red, white, etc.), the bling adds 10% to the cost. We bought basic black. The battery charger is a standard wall-wart with a thin cable and it is refreshingly small and portable and easily fits in a bag or purse.

Other Addons:

On any computer, I believe a mouse is easier to use than a touchpad. Although I have reviewed (and still like) the Microsoft 7000 Wireless mouse, Logitech has a standard mouse that I am very interested in -- Logitech M505. With this mouse, you can leave the dongle plugged in all the time and it is rated for better than a year on one set of AA batteries. Note how diminutive the transmitter is; it only sticks out about a 1/4 of an inch (less than 1cm):

See also the Keyliner review on a much-less expensive Gearhead Wireless mouse. We are also in the market for an external USB DVD drive, for watching movies. Undoubtedly, I'll review both of these products when I get around to buying them.

Carry Bags:

Speaking of bags, we spent $20.00 on a Belkin messenger bag, shown on Dell's site as the perfect bag for this laptop. It wasn't so. The bag is too spacious (which is not a good thing) and it is limp and lifeless, with too much velcro. Yes, we know it is a messenger bag, but it needs a backbone.

We found a replacement at the local office supply store: A Targus 10.2" Netbook bag (TNC101US), designed especially for the netbooks. This case has two zippered compartments; one for the netbook and the other for the powersupply, disks, mice, etc. Priced at a reasonable $25. At first we liked the bag, but the pockets and added padding (all good design features) made the bag thicker than we liked.

Ultimately we replaced the Targus bag with two others, each progressively thinner than the previous, but we found them in unlikely places (a clearance bin for bicycle accessories, and at an office-supply store); neither of which will be found in the general market. My advise, try this bag but keep an eye out for a thinner bag, if you can find one.

Thickness: 1.1 inches
Width: 10.1 inches
Depth: 7.0 inches
Weight: Less than 3.00lbs

Screensize: 8.75 inches wide x 5 tall
10.1 inches diagonal
1024 x 576 pixels -- this is the only size it supports
Intel Integrated Graphics Media Accelerator 500

Processor: Atom N270 1.6ghz, 512K L2 Cache
1GB Ram; upgradable to 2GB (2GB,DDR2,533MHZ )
Wireless Network / RJ45
3 USB Ports
3-in-1 Media Card Reader
External VGA port
TouchPad mouse controller
Windows XP SP3
Chipset: Intel Poulsbo US15W

Links of Interest:
Dell 1011 Vendor Site
Dell XPS M1530 Review
Gearhead Wireless Mouse Review
Microsoft Wireless Mouse 7000 Review