The Excel Chronicles - Coloring alternate rows in MS Excel 2003 and earlier

There had been many new features that were added in MS Excel 2007 onwards, which makes life a lot easier. I would still like to highlight a few tips and tricks that are not required in the current versions of MS Excel. Hope the tips bring up new ideas.

In any text presentations that are wide in nature, it always helps to have a striped background. Remember the old printer paper.

We look for this feature in almost all reporting tools. Web Intelligence even generates the default reports in striped mode. In Excel, follow the steps to achieve this.

Step 1: You would need at least 1 column of incremental numbers 1,2,3,4… Usually this can be the leftmost column. Once the stripes are made, this column can be hidden. In the example, column A has the series of numbers.

Step 2: In column B5, initiate to create a ‘Conditional Formatting’ by selecting menu option Format->Conditional Formatting

Step 3: Select condition as ‘Formula Is’ and enter the formula as =mod($A5,2)=0 The cell reference should be the start of the numeric series.

Step 4: Select ‘Format’ and select the ‘Patterns’ in the Format cells window and select the color of choice.

Step 5: Add condition 2 as ‘Formula Is’ and enter the formula as =mod($A5,2)=1 Select an alternate color following step 4. 

Step 6: Once the format is set, I prefer the paste special option to copy only the format to all the cells required. So, copy cell B5 and select the area that needs to be striped.

Step 7: Select Edit->Paste Special menu option, and select ‘Formats’ in the Paste option. Click OK and viola you get your stripes.

Step 8: If required, hide the numeric column A, and this is what you will get, a striped table in Excel.

SAP LiveOffice toolbar is missing

There can be couple of reasons that can make the LiveOffice toolbar disappear, including install of new softwares. Step 1 describes the easy way to get the toolbar back. If that does not work, try Step 2 which is the manual way of doing it.

Step 1:

• Run enable_addin.exe from the LiveOffice program folder
• The path would be typically be C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\Live Office 12.0

• Select the office product that the user use for Live Office

• Restart Office Application and see if the LiveOffice toolbar is visible now
• If not, check if it shows up in the toolbars
• Select it, and you should be good to go

Step 2:

• If step 1 does not work, there is a hard way to set the Live Office toolbar up
• This is done by manually re-enable the LiveOffice Com-Add-In:
• In Excel, Select Tools->Customize
• In the Customize window, select Command tab
• Select Command in categories, and COM Add-Ins… in Commands
• Drag and drop COM Add-Ins… to any of the existing toolbars

• Double click to open the Com-Add-In configuration Window

• In the Com-Add-In configuration window, select BusinessObjectsTM Live Office XI 3.1 and hit Remove
• Click Add and browse to the dll to manually add it
• The addin would be typically available in the following path: C:\Program Files\Business Objects\ BusinessObjects Enterprise 12.0\win32_x86\crystal_addin_framework.dll
• This should restore the Live Office menu