Browse by Tags

Excel corrupts certain workbooks in migrating from 2003 to 2007

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree. The following in 2003 Figure 1 becomes, in 2013 (and in 2010), Figure 2 The [...] Read More...

Published Sun, Apr 04 2013 3:04 AM by Daily Dose of Excel

Filed under: Excel12, Bugs, VBA, Formulas, Names, Excel 14, Excel 2007, Excel 2010, Excel 2003, Excel 15, Excel 2013, bug

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 [...] Read More...

Published Thu, Feb 02 2013 6:12 PM by Daily Dose of Excel

Filed under: Excel12, Add-ins, User Interface, Goal Seek, Excel 14, data analysis, Excel 15

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 [...] Read More...

Published Thu, Aug 08 2012 5:42 PM by Daily Dose of Excel

Filed under: Excel12, VBA, Formulas, User Interface, Excel 14, Games, Excel 2007, Excel 2010, Excel 15, Excel model, Excel 2013, arrow, business model, dependent, precedent, visualization

Excel Advanced Filter

Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom [...] Read More...

Published Sat, Jul 07 2012 3:51 PM by Daily Dose of Excel

Filed under: Excel12, User Interface, Filtering, Excel 14, Excel Basic, Excel Intermediate, Excel 2007, Excel 2010, advanced filter, remove advanced filter, filter advanced, custom filter, advanced data filter, data analysis, advance filtering, advanced sort & filter, advanced filter criteria

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 [...] Read More...

Published Sat, Jun 06 2012 4:50 PM by Daily Dose of Excel

Filed under: Excel12, Excel 14, Excel Basic, Excel 2007, Excel 2010, Unicode, diacritical character, diacritic

Freeze Panes and Split Panes

When working with a worksheet that contains a large number of rows and/or a large number of columns with row and column headers, it is very helpful to always view the headers no matter where one scrolls through the document. An example is shown below. The table lists the year-by-year carbon dioxide emissions by country [...] Read More...

Published Sun, Jun 06 2012 8:41 PM by Daily Dose of Excel

Filed under: Excel12, User Interface, Excel 14, Excel Basic, Excel 2007, Excel 2010, row header, freeze panes, split panes, column header, view worksheet, scroll

Installation Instructions for Office add-ins

I updated the installation instructions for Office VBA add-ins to include Office 2010, Office 2007, and Office 2003. The updated material is at http://www.tushar-mehta.com/excel/software/installation/index.htm. Tushar Mehta Read More...

Published Sun, Jun 06 2012 1:28 AM by Daily Dose of Excel

Filed under: Excel12, VBA, VBA Basics, Excel 14, Excel Basic, Excel 2007, Excel 2010, add-in, VBA Basic, Excel 2003, macro warning, security, installation

Minimum and maximum values of numeric data types

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them. I decided to “translate” the documentation defining the different data types into code. The [...] Read More...

Published Tue, Jun 06 2012 7:21 PM by Daily Dose of Excel

Filed under: Excel12, VBA, VBA Basics, Excel 14, Excel 2007, Excel 2010, excel 2010 32-bit, excel 2010 64-bit, VBA Basic, vba 7, vba 6

Use an Excel chart to show a time snapshot and trace a path

These Excel charts were inspired by Hans Rosling’s TED presentation on Religion and Babies (http://www.ted.com/talks/hans_rosling_religions_and_babies.html). He is absolutely great at engaging the viewer with his ability to bring data to life. One of the things he did in his presentation was show the equivalent of an Excel bubble chart. He showed how different countries measured [...] Read More...

Published Mon, Jun 06 2012 1:51 AM by Daily Dose of Excel

Filed under: Excel12, Charting, Excel 14, Data Visualization, Excel Advanced, Chart, Excel 2007, Excel 2010, dynamic path

TM Excel Dynamic Path Navigator

One of the features that consumers may find useful is retracing their path as they navigate through different workbooks, different sheets, and different windows. This navigation would be equivalent of a web browser’s Previous and Next feature. While Excel does not support this natively, the code in this note enables this capability. For a version [...] Read More...

Published Sat, May 05 2012 12:22 PM by Daily Dose of Excel

Filed under: Excel12, User Interface, Navigation, Excel 14, VBA Code Library, dynamic path, add-in, prev, next, forward, previous, macro workbook, back

Align Primary and Secondary axes

There are instances when there are data series plotted on both the primary and secondary axes. For example, suppose we want to plot the two series A and B in Figure 1, with the elements in column B as the … Continue reading → Read More...

Published Sat, Apr 04 2012 8:03 PM by Daily Dose of Excel

Filed under: Excel12, Charting, Excel 14, Data Visualization, Excel Basic, Visual Display, Chart, Excel 2007, Excel 2010

Protect a global variable in another VB project

Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads [...] Read More...

Published Wed, Nov 11 2011 1:45 AM by Daily Dose of Excel

Filed under: Excel12, VBA, Fluent UI, Excel 14, Excel Basic, Excel Intermediate

Save a global variable in an Excel workbook

Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads [...] Read More...

Published Sun, Aug 08 2011 11:01 PM by Daily Dose of Excel

Filed under: Excel12, Windows API, Fluent UI, Excel 14, Excel Basic, Excel Intermediate

Cardinal (real) numbers and a log scale for a Column Chart x-axis

The x-axis of a Column Chart can contains either category values (the x values are equidistant) or dates. Neither of these allows for a truly numeric x axis. This tip shows how to simulate a column chart that has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’ – [...] Read More...

Published Sun, May 05 2011 12:45 AM by Daily Dose of Excel

Filed under: Excel12, Charting, Excel 14, Data Visualization

Cardinal (i.e., real) numbers and a log scale for a Column chart x-axis

The x-axis of a Column chart can contains either category values (the x values are equidistant) or dates. Neither of these allows for a truly numeric x axis. This tip shows how to simulate a column chart that has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’ – [...] Read More...