Wednesday, June 15, 2016

Conditional Formatting of Cells Containing Formula (Excel pre-2016)

Excel 2016 has a built in function IsFormula to return if the cell contains formula reference or values. As this function is not available in Excel 2010 or earlier editions, one workaround is to create a custom or user defined function in VBA.

In case you do not want to enable Macro or want to avoid VBA, the alternative is to use XLM (Excel 4 Macro) which was the original Excel Macro language before VBA was introduced in Excel 5.0.

In the Name Manager under Formulas ribbon, create a new Name - let's call it IsFormula which should refer to =GET.CELL(48,INDIRECT("rc",FALSE))

Create a new conditional formatting rule with the formula "=IsFormula" with a custom format and apply that on the range where you want to check for cells containing formula.

Thursday, November 05, 2015

Make custom Pivot Table Style available in multiple Excel files

When you create a custom Pivot Table Style, it remains available only for the file it was created in. If you try to apply the same style to a different file you'll realize that the custom style doesn't appear in the list of available styles for new file. 

In order to transfer the custom style or to save it for other Excel files, follow these steps:

1. Select the entire pivot table (Select --> Pivot Table) where the custom style is already applied and copy (Ctrl+C). 
2. Paste this pivot table in a temporary sheet in a new file or your destination file. 
3. The custom style will now appear in the list of available styles. 
4. Delete the copied pivot table. 

Monday, October 26, 2015

Excel Error 2010 /xl/calcChain.xml

Recently, one of the Excel 2010 files which I have been using for the past few months to make regular updates in, suddenly ran into issue and started to show error - Records removed from /xl/calcChain.xml.

To resolve this issue, follow these instructions:

1. Change the Excel file's extension from .xlsx to .zip

2. Unzip the contents and go to /xl/ folder.

3. Delete caclChain.xml

4. Re-zip the contents of the folder

5. Change the extension from .zip back to .xlsx.


Upon opening the file, if Excel will not find calcChain.xml, it will recalculate the formulas and will re-create the calcChain.xml file.

Friday, December 26, 2014

Find last date of current/given month using EOMONTH() function

We know that the Excel function EOMONTH(start_date, months) can be used for determining the last date of the month that is the number of months before or after the start_date.

But what if we want to find the last date of the current/given month. In this case, the months parameter should be 0. This will return (serial number) the last date of the same month as given in start_date.

How to avoid duplicate conditional formatting rules with copy/insert

When working on an Excel sheet which has Conditional Formatting rules, creating additional rows/columns/cells automatically create duplicates entries of Conditional Formatting. 

Below is the screenshot of first instance of Conditional Formatting entries

After copying a blank row to extend the table, Excel automatically creates duplicate entries as shown below in Conditional Formatting Rules Manager window

In order to avoid getting duplicate rules created, the workaround is to Insert a new row/column/cell and then copy over only the formulas to the new row/column/cell - this will retain originally created Conditional Formatting rules and will not unecessarity duplicate those rules.

This is applicable for Excel 2007/2010.

Friday, January 15, 2010

How applications run without using PATH variable

Ever wondered how some applications, for example Winword, Excel, Outlook, start automatically when called from 'Run' window without providing their complete path even though the system PATH variable doesn't have these applications' locations? The answer is in this registry key:
HKLM\Software\Microsoft\Windows\CurrentVersion\App Paths\

So the next time any of the applications do not start without the complete path but you are sure that it exists on the system, check that the registry should have the right information.

Thanks to Mohit Sharma for discovering this info! :)

Thursday, September 04, 2008

Strange Errors in Excel

I Came across a very strange error message in Excel 2003 - "Your entry cannot be used. An integer or decimal number may be required" This message appeared whenever I tried changing font size in a cell/sheet or when tried moving from one tab to any other tab in Options window.

Eventually, it turned out that this issue was the result of missing Decimal symbol in Regional Settings. As soon the decimal symbol was restored, this issue was resolved.

Thursday, July 12, 2007

Feedburner integrated with feeds

Feedburner now offers feeds integration for blogs. Gone are the days when one had to fiddle with the blogger template to replace blogger's default feed source with feedburner's feed link! Just go to the template page now and enter you feedburner feed source. Mind you, this configuration will not replace your default blooger feed source; it'll only redirect that link to feedburner's feed link. Also note that, apparently this configuration redirects only Atom feeds, and not RSS feeds.

This is just the begining of the services users will start to get with feedburner's acquisition by Google. Like, few days ago, feedburner announced two of its previously PRO (paid) services as freely available now - FeedBurner Stats PRO & MyBrand.

Active Directory Explorer v1.0 by Sysinternals

Sysinternals released a new tool for Active Diretory administrators called Active Directory Explorer v1.0

Active Directory Explorer (AD Explorer) is an advanced Active Directory (AD) viewer and editor. You can use AD Explorer to easily navigate an AD database, define favorite locations, view object properties and attributes without having to open dialog boxes, edit permissions, view an object's schema, and execute sophisticated searches that you can save and re-execute.

AD Explorer also includes the ability to save snapshots of an AD database for off-line viewing and comparisons. When you load a saved snapshot, you can navigate and explorer it as you would a live database. If you have two snapshots of an AD database you can use AD Explorer's comparison functionality to see what objects, attributes and security permissions changed between them.

Download page:

Wednesday, April 25, 2007

VBScript: Function Code to Convert Bytes to KB/MB/GB/TB

When querying for disk space sizes using WMI, it returns the numbers in bytes. These large numbers in bytes do not make much sense until they are converted into Kilobytes (KB), Megabyte (MB), Gigabyte (GB), or Terabyte (TB) and so on. Quite frequently I need to convert these sizes in bytes to KB/MB/GB/TB for better interpretation. I therefore created a quick VBScript function which I call inside VBScript code whenever I need to convert numbers in bytes to KB/MB/GB/TB.

Function ConvertSize(Size)
Do While InStr(Size,",") 'Remove commas from size
    CommaLocate = InStr(Size,",")
    Size = Mid(Size,1,CommaLocate - 1) & _
        Mid(Size,CommaLocate + 1,Len
(Size) - CommaLocate)

Suffix = " Bytes"
If Size >= 1024 Then suffix = " KB"
If Size >= 1048576 Then suffix = " MB"
If Size >= 1073741824 Then suffix = " GB"
If Size >= 1099511627776 Then suffix = " TB"

Select Case Suffix
    Case " KB" Size = Round(Size / 1024, 1)
    Case " MB" Size = Round(Size / 1048576, 1)
    Case " GB" Size = Round(Size / 1073741824, 1)
    Case " TB" Size = Round(Size / 1099511627776, 1)
End Select

ConvertSize = Size & Suffix
End Function

Saturday, April 14, 2007

How To Uninstall RDP 6.0

I have been seeing couple of queries in few tech forums for uninstalling Remote Desktop Connection (RDP) 6.0, or how to revert to original version of XP SP2 RDP client (mstsc.exe version 5.1.2600.2180).

RDP 6.0 was released as a patch (KB925876), therefore, by default it doesn't appears as a seperate entry in Add/Remove list. To uninstall it, you'll have to click on the check box of "Show updates" in Add/Remove window, select "Update for Windows XP (KB925876)", and remove it.

Alternatively, you can also browse to the folder C:\WINDOWS\$NtUninstallKB925876$\spuninst\ and run spuninst.exe from there, which will uninstall RDP 6.0 client and revert to the older version.

Friday, April 13, 2007

IE Search Shortcuts

TweakUI has a nice handy feature of creating Internet Explorer search shortcuts. So, instead of opening first and then typing the search string, TweakUI lets you assign a search prefix (e.g. “g”) to Google’s search URL. You can then type “g searchstring” directly in IE’s address bar to search for “searchstring” using Google search. For example, Google’s search URL for the search string “ipod” is To create a shortcut for this search URL, copy-paste it in TweakUI window and replace the search string “ipod” with “%s” (

You can create search shortcuts not only for Search engines, but for any site which offers search functionality by observing their search URLs in the address bar. Below are some of the search shortcuts that I use for some common sites.

Shortcut Search URL What for?
c Citrix Support Articles
d WHOIS lookup on
e Lookup info on Windows Event IDs
f Lookup details of any file extension
g Google search
kb;en-us;%s Microsoft KB support articles
p Lookup executable/DLLs details
r Word meaning references
w Wikipedia search

After you have created these shortcuts, you can export the relevant registry key as a backup or to import that to any other computer. These shortcuts are located at HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\SearchURL. Export this key into a .REG file and import it on any other computer. These shortcuts work with IE7 as well.

Here is the .REG file with above shortcuts. If you are geeky enough, you can add/modify entries in your registry even without TweakUI!

Thursday, April 12, 2007

A Quick Workaround for error "Failed to Launch. The Server returned CharlotteAppHostUnreachable"

Sometimes Citrix application when launched via Program Neighborhood Agaent (PNAgent) shows the error message - Failed to Launch. The Server returned CharlotteAppHostUnreachable. The issue apparently is related to Zone Data Collector (ZDC), though I haven’t been able to find the reason accurately.

Usually server restart resolves the issue. If not, then to avoid downtime for the application, a quick workaround is to create an .ICA file for the application and provide that to users. Directly accessing the remote application via .ICA file would work even if the application shows that error message when launched via PNAgent. This way you can continue to troubleshoot the issue without impacting application users!


Monday, April 09, 2007

How To Disable Credentials Prompt of RDP 6.0

Remote Desktop Connection client 6.0 introduces new authentication features to improve security for Windows Vista and Windows Longhorn Server, which makes it mandatory for the user to enter logon credentials before RDP client can establish connection to the remote server (" Enter your credentials for <server>. These credentials will be used when you connect to the remote computer" ). But if the remote machine is configured to show logon warning message or if the remote system happens to be Windows 2000 or XP, you’ll need to enter the credentials again at remote machine’s logon screen.

There is however a workaround to skip the credentials screen that RDP 6.0 client shows by choosing “Do not attempt authentication” under Authentication options on the Advanced tab, but this option is not set permanently. To permanently skip the additional credential screen of RDP 6.0 client, edit the Default.RDP file in notepad to include enablecredsspsupport:i:0 . The Default.RDP is located in each user’s My Documents folder. Including enablecredsspsupport:i:0 disables the Credentials Security Service Provider for the connection. If you use separate .RDP files for different server, modify each of those .RDP files. Below is the content section of the default.rdp file with enablecredsspsupport:i:0 option included.

authentication level:i:0

prompt for credentials:i:0
negotiate security layer:i:1

Note that this workaround is suggested only if you connect Windows 2000/2003/XP systems because according to Terminal Services Team blog post“This option does disable the new credential prompting behavior, but it also disables support for Network Level Authentication for Vista (and Longhorn Server) RDP connections; Network Level Authentication requires credentials to be provided by the client before a session is created on the server side.” So if you do connect to Vista/Longhorn over RDP, you’ll not be able to use this option.


Friday, April 06, 2007

How To Disable Outlook Security Warning - "A program is trying to access e-mail addresses..."

When any software tries to access Outlook Address Book programmatically by using Outlook libraries, the system shows the security warning message –

A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this?
If this is unexpected, it may be virus and you should choose “No”.

According to KB329765“This behavior occurs because there is no running session of Outlook to determine the correct security profile to load. Therefore, the default security profile is used, causing the security prompt. When you programmatically access an item in the Address Book, a session must be running to determine the correct security profile to load. When Microsoft Outlook is not running, the security dialog prompts the user because the default security profile is used.”

Although offered as a security feature, this prompt can be very annoying if the application frequently needs to access the address book or to send mails.

The workaround is to disable this security prompt by setting/creating a REG_DWORD registry entry CheckAdminSettings = 1 located at HKEY_CURRENT_USER\Software\Policies\Microsoft\Security

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Security] "CheckAdminSettings"=dword:00000001

The table below shows other applicable values for CheckAdminSettings.

Value What Oulook Does
Key not present Uses its default settings
0 Uses its default settings
1 Looks for settings in the Outlook Security Settings folder, applying them according to the defaults and specific users you've specified.
2 For Outlook 2002 and Outlook 2003 only: Looks for settings in the Oulook 10 Security Settings folder, ignoring any settings in the Outlook Security Settings folder. Use this value when you want Outlook 2002/2003 to use different settings
Anything else Uses its default settings

This setting applies in current user hive only. Therefore, to apply it by default to all users, make the same entry in Default User hive (HKEY_USERS\.DEFAULT). This NTUSER.DAT file can then be copied to other systems as well where the Outlook security warning needs to be disabled. Of course, it goes without saying that this setting can also be exploited by viruses. Also note that CheckAdminSettings registry change works only with Exchange Server.


Thanks to my manager for sharing this info!

Friday, March 30, 2007

VBScript: Execute process remotely with WMI

For our terminal/citrix servers we have to regularly run delprof command for deleting inactive user profiles to minimize disk space consumption. The annoying part is to login into each of the servers just to run this command. Even though delprof supports deleting profiles on remote servers with the switch /C:\\<computername>, but that runs terribly slow over WAN links.

A workaround is to remotely execute delprof (or for that matter any other command) on the remote server by using tools like PsExec which installs a temporary service on the remote machine to be able to execute process remotely, and unintalls the same service after the process finishes. Because of its dependency on installing a service, PsExec might not always be a viable option in production environment. So, what's an alternative now?

Enter WMI.

It offers the capability to execute process remotely with the limitation of not allowing any user interaction - the process will run in the background without showing any interface on user's session, which is a perfect feature for running silent installs or non-interactive processes, for example, delprof /q/i

Below is the code snippet which does the job.

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2:Win32_Process")
intReturn = objWMIService.Create(
"delprof /q/i", Null, Null, intProcessID)

This script also uses Popup function of WScript.Shell instance to display message box which disappears after specified seconds - great for showing quick status messages without waiting for any user interaction.

Here is the complete script which executes delprof command on the target server and shows before and after free space information at the end, on the client side.


  • Delprof executable should already be installed on the target server.
  • Because the script also checks before and after free space on C: drive, it assumes that \Documents and Settings folder is located on server's C: drive.


NOTE: For some weird reason, delprof command is case sensitive. Therefore, DELPROF /q/i does not recognize the "quiet" and "ignore" switches and still prompts: Delete inactive profiles on \\SERVERNAME? (Yes/No)

Wednesday, March 21, 2007

VBScript: Unload Non-Active User Hives From Registry

In Windows 2000 Terminal Server/Citrix environments, it often happens that after users have logged off, their user registry hive doesn't unload automatically from HKEY_USERS\, which in turn keeps consuming registry space and causes it to go out of sufficient free space. The workaround is to launch REGEDT32.EXE (on W2K) or REGEDIT (on W2K3) and manually unload user hives from HKU, but again you'll need to manually figure out who all are active users and exclude those from unloading. This manual process requires converting each of the logged-in user IDs into their respective SIDs and searching them in HKU to exclude.

This script addresses these issues and automates the process of unloading only non-active user hives from HKEY_USERS. This script first converts each of the loaded user SIDs into user names, then attempts to unload all but the active ones by matching those user names with the output from 'query user' command. Below are more details:

Expected Input: Target computer name. By default it’ll show local computer name where it is executed from, which can be changed to any target system. This script can be run from the local desktop session, no need to login into remote system.

Definite Output: The output file will open automatically in notepad after script execution, and will be stored in C:\TEMP\UnloadHive-SERVERNAME.log on the system where it is run. The output file shows a quick summary of how many hives unloaded successfully and registry space gained. This script also keeps updating a single log file (CSV) with summary result of each execution - in case any data analysis or trend analysis is required to do in future.

Dependency: Requires psgetsid.exe to be present in the same directory where script is. It is recommended to copy psgetsid.exe in the executable path on the local machine (e.g., C:\Windows\).

Download the script here.

Monday, November 27, 2006

Importing Yahoo! mails to Gmail

Since I don't have anything new to post, I'll just bookmart some useful information I came across. If you (me) ever think about migrating to Gmail from Yahoo!

Sunday, September 17, 2006

Wednesday, August 30, 2006

Stay Secure!

This is definitely not good. During last one week two of my friends lost their yahoo accounts, possibly got hacked. Amit was on SANS security training in Singapore, with 65 other potential hackers security professionals. After the last day in his training when he came back home he realized that his Yahoo! Account password wasn’t working anymore. My other colleague, Sudhakar, accessed his yahoo mail on his roommate’s laptop and next thing he knew the following day was, his yahoo password wasn’t working anymore.

I can’t stress enough how risky it is to access your email/e-banks/e-commerce transactions over public networks or on some others’ machine. It is not a matter of having complete trust on someone you know well enough, even if that person happens to be your family member/close friend/colleague, because you can never be sure if his own machine is secure enough. You cannot rule out the possibility that his machine might already be hacked, and all it takes is just one attempt for you to enter your secret credentials on that hacked machine (or if he hasn’t intentionally been running malicious programs).

An end-user never bothers to secure his own machine apart from following regular recommendations – things like, keep the anti-virus definitions updated, install anti-spywares like Spybot & Windows Defender and keep the definitions updated, regularly scan your system with anti-virus/anti-spywares, blah-blah. But the important aspect we don’t usually realize is that there are other channels to hack into system and keep it infected in a manner that regular anti-virus/anti-spywares cannot detect. The biggest limitation with these scanning tools is that these are all definition based and not behavioral/pattern based. What this means is that unless the loophole/vulnerability/threat becomes visible in public domain and a patch/definition is released, these scanning tools will not be able to detect them. For example, anyone with a decent programming knowledge can develop a quick key-logger/virus/Trojan and release it within limited scope, may be within among his contact circle. Behavioral based scanning tool, on the other hand, keep monitoring the system at lower layers (of OS architecture) and are better able to detect system modifications that key-logger tries to make to activate itself.

The other aspect most people ignore is that they do not change the default system configuration. For example, after a typical Windows installation, quite a few system services get active which might not really be required for user, but which can act as potential security holes. Network services like ‘client for Microsoft networks’ and ‘file and print sharing’ are always active on all the network interfaces – physical network interface as well as wireless interface. Unless a good firewall is installed on the system, it is not very difficult to hack into the system using just these two services and activate some Trojan/key logger on that system. Rootkit is another new category of tools which are even harder to detect with traditional scanning tools.

Here are some quick recommendations. This is not an exhaustive and polished list, but just few quick ones on the top of my head. Of course, it goes without saying that if you use same laptop at both office and home, you should check with your system administrator before making these modifications. 

  • Always ensure that your system is completely patched with up to date hot-fixes. You can use Microsoft’s Baseline Security Analyzer to do the gap analysis and install all the required patches.
  • Never trust any system other that your own (secure) system for entering your credentials (email/banking/credit card/etc). Remember, all it takes is just one attempt, even if that system belongs to your closest friend/family member/colleague. I personally confess of having captured password details of my friends, though I have never (mis)used those details!
  • Disable Remote Registry service. An example where this can be exploited is, lot of instant messengers store user passwords in encrypted form inside registry. All it takes is extracting the relevant registry keys remotely and attacking it offline. Again, anyone who knows me, when I say to them that they haven’t changed their passwords for a long time, I really mean it (sometimes)! J
  • Even when using your own system over public wireless network, do not enter confidential details. The risk with these public hotspots is that you can never know that the person sitting next to you can possibly be running some network capturing tool to sniff your data packets to crack it offline later. There are tools available which can capture your network interface’s MAC address and inject those same MAC address in their own machine’s network packets to trick the wireless switch to send the returning packets to their system. If you really have to use public wireless hotspots for entering confidential details, do that only over VPN connection.
  • Disable ‘Client for Microsoft Networks’ & ‘File and Print Sharing’ on wireless network interface unless you use open wireless access in your office/home for logon authentication and/or sharing files/print attached to your own system. At home if you connect your DSL directly to your system, you should either install a good firewall or disable these two services on the interface where DSL connects (physical LAN port or wireless), because when DSL is directly connected to your system, it is your system which gets the public IP address and gets exposed to Internet. Do yourself a big favor and get a switched-router instead of connecting your system directly to public interface.
  • Avoid installing any third-party softwares without first testing it on some dummy machine. Use VMWare Workstation (paid) or Virtual PC (free) for testing softwares in isolated environments.
  • Regularly run Autoruns and Process Explorer on your system to monitor what all processes are configured to autostart and currently running. If all the entires in these tools scare you first, start getting yourself familiar with it. 

Here are some quick directions on what you should have on your system: 

Don't believe me? Read this: