Skip to content
Archive of posts filed under the MS Excel category.

Generate random numbers in MS Excel

A common requirement is to generate a set of random numbers that meet some underlying criterion. For example, a set of numbers that are uniformly distributed from 1 to 100. Alternatively, one might want random numbers from some other distribution such as a standard normal distribution. While there are specialized algorithms to generate random numbers […]

Draw a circle in an Excel chart

By default, Excel has a limited number of charts. That does not mean that those are the only charts one can create. It turns out that with a little imagination and creativity, we can format and configure the default charts so that the effect is like many other kinds of charts. One of the more […]

Worksheet as a chart – multiple conditional formats

Several years back, I wrote an article on how to use multiple cells to simulate conditional formats that involved more than 3 conditions. Three versions of Excel later, I still receive requests related to this post. So, I updated it to include more screenshots and a downloadable file. In Excel 2003 and earlier, conditional formatting […]

TM Goal Seek

TM Goal Seek enhances the existing user interface to Excel’s Goal Seek feature. The built in Goal Seek is a simple optimization tool that suffices for a large number of scenarios. The UI, unfortunately, is extremely unwieldy and unfriendly. TM Goal Seek is a simple add-in that is easier to use than the default dialog […]

The Excel FORMULATEXT function

With Excel 2013, Microsoft introduced several new functions, one of which is worth a separate mention. FORMULATEXT displays as text the formula in a cell. For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0116%20formulatext%20function.shtml Tushar Mehta

The Range.Find method and a FindAll function

Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened. Consequently, every time I want to use the Find method, I continue to have to jump through hoops […]

Do consumers prefer the ease of an EXE installer or the transparency of a ZIP file?

For all the various add-ins available from my website, I have supported two downloadable formats, an EXE and a ZIP file. The EXE is easier to install and includes an uninstall capability. The ZIP hopefully provides the consumer greater transparency and control over what is on their computer. In addition to the software downloadable from […]

Excel Services Interactive View

Analyze data with Excel on the web Microsoft has introduced a new web based capability that extends its Excel Services offering.  This new capability provides a limited interactive view of any table in a web page. An introduction to this service is below.  For those interested in additional capabilities and more advanced and useful capabilities […]

Model business dependencies in an Excel worksheet

The Civilization game dependency tree How to build a dependency tree when there is no obvious connection between the business model and an Excel model. A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance […]

Unicode and diacritic characters

The primary role of Excel is analysis and visualization of data, which put less emphasis on the use of special text characters. Nonetheless, there will always be some need for special characters, both Unicode characters and diacritics in Excel. A diacritic in English is a glyph that modifies the sound of the character to which […]