Selecting only blank cells

This is one of the questions colleagues at work are often asking me. The need arises usually when you are importing data from the mainframe to Excel.
Many times you are getting something like this:


If you want to produce a Pivot Table from this data, you will have to fill the blank cells in column A:
  • values in cells A3 and A4 must be "Peter"
  • values in cells A6 and A7 will must be "Paul"
  • values in cells A9 and A10 must be "Mary"

This can be done manually in our example. But suppose you have, let's say, 2,000 records in your table. In that case you can take advantage from the "Go To - Special" feature in Excel.

How?

First select the whole relevant range (the column range, in our case A1:A10). Then press "Ctrl + G" or F5. You will get this:



Hit the "Special" button and mark "Blanks"


and hit "OK"


Now enter "=" (the equal sign) in cell A3 (the first blank cell in the selected range), , click cell A2 (the cell above the first blank cell), and hit "Ctrl + Enter". You will get this:

This technique can be useful in many other cases. For example, if you want to delete a whole line when some cell is blank (like in our example), you can select all the blank cells using F5, then press the right button in the mouse, from the pop-up menu select "Delete --> Entire Row" and press "OK". All the rows with a blank cell in the selected range will be deleted.

How to find where the two curves cross

Sachin, a colleague at work, came to me with this question: How can I find the value of the point where to lines cross in a graph?
Excel don't seem to have a build-in procedure to find such a value. The workaround I suggest is to use the Solver Add In.

Let's look at this example:

The formulas to produce the graph are:
  • in cells B2 to B10: =3*A2+25
  • in cells C2 to C10: =A2^2+2*A2+1

Obviously Y1 crosses Y2 somewhere between X=4 and X=6. In order to find the exact value of X we'll use the Solver (in the menu bar: Tools-->Solver. If you can't see it, then you have to install the Add In).

Before opening the Solver copy the formulae in cell B10 and C10 to cells B12 and C12.


In the "set target cells" window write (or select) B12.

In the "by changing cells"window write or select A12.

In the "subject to constraints" window add two constraints:

  • B12 = C12 (the answer to our question)
  • A12>=0 (in order to get a positive value)

Now, your sheet will look like this:

Hit the "Solve" button, and you will get this:



CONVERTER FROM XL TO EXE

This utility converts Excel files from the XL format to an EXE format (convert XLS to EXE, convert XLA to EXE, convert Excel to EXE). Like XL files, the EXE files created are not independent of Excel, but they allow you to define Excel's properties before opening, and improve the portability and functionality of your workbooks.

The conversion, moreover to maintain the original format features, inclusive for editing thru the Excel, adds others functionalities where the autocompression and the control over the Excel highlight. So, by be specialized in Excel and VBA, can be more advantageous than ZIP files, ZIP autoextracts and program installation utilities.

Download Converter

Tested in the Microsoft Excel for Windows versions 97, 2000, 2002(XP), 2003, and 2007.

EXE format features:

- Can be read and write (Changes done in an EXE file opening in the Excel to write are saved in the proper EXE file);
- When saving changes, backups are created. Optionally, you can specify whether to overwrite or not overwrite previous backups (This makes possible more restoration levels than using XL format);
- Can be read-only, very read-only, doesn’t permit ‘Save’ and ‘Save as’ too;
- Changes can be saved to a new XL file, leaving the EXE unaltered (Good to be used like a template);
- Changes can be saved to multiple XL files with an incrementing number (Good to be used like an invoice template);
- Can be opened in the Excel without macro alert or with a custom alert (Good to work with known files containing macros using the Excel in medium or high security level);
- Can show, without text size limit, an alert before opening in the Excel (Good to installation alerts, copyright, guidance etc.);
- Can require password-to-open so safe that only can be cracked by attempts (brute force) same knowing the converter source code. Then, if you put a password and forget it, no-one, neither this utility author, can help you;
- There are four levels of autocompression: none, normal (faster), medium (slower), and high (slower). Normal is equivalent to a normal zip file. This makes large files more portable, mainly when they need protection, because protected files in XL format are of low compression. In a test with a simple workbook of 5 Mb, the ZIP compressed only 14% and this got 74% because the cryptography is done after the compression;
- Can be attached one or more text or binary files (Good to be used as installer file);
- All attached XL files can be opened too at start and controlled as one whole like a 'workbook' of workbooks;
- Can be opened in an exclusive Excel window (exclusive Excel instance) refusing, while open, any try to share with other strange workbook;
- Can be forced the closure of running Excel instances at start up. This is helpful to use the EXE as an installer for a COM add-in;
- When opened in a separate instance, it can start Excel in automation mode, very automation mode; Excel will run totally clean without XLSTARTs, add-Ins, and COM add-ins too. This is better than /automation Excel command line option that doesn’t avoid COM add-ins;
- When opened in a separate instance, it can control the Excel window style (maximize, minimize, normal, or hidden). These four last features are good to be used when your workbook is a dictator application;
- You can avoid the original converted EXE file from being renamed;
- All the above features can be configured when you convert;
- The conversion can be done thru the utility interface or by command line;
- The command line can contain all of the options, alert text, and file names and paths to attach. You can generate long and complex command lines from the user interface and send them to the clipboard to be pasted anywhere, such as in an argument of VB or VBA Shell function or in the Windows Run dialog box;
- You can convert back to XL format using Excel's Save As command if not read-only;
- When convert, it creates a backup of the original XL format file without overwriting previous one;
- Files in this format can function as EXE in CD Autorun;
- The conversion only adds about 129Kb which, depending on the file size, can be regained during autocompression;
- The converter and any converted files are standalone in the presence of the Office library, no extra library is necessary and neither changes in your Excel or Windows setting.