Reducing File Size

You say your Excel files have gotten a bit “Fat” and you wish there was an easy way to reduce their size?  You’re in luck, as there are several ways of reducing Excel Bloat!

Decreasing the size of your Excel files can have several benefits.  It can make them easier to email, (and save the ire of your recipients for bogging down their inboxes…), make them open more quickly, and reduce the chance of curious error messages/problems.

Here are just a few ways of putting your Excel files on a diet:

1.   First of all, check to see if a bit of housecleaning is in order: Are there any unused worksheets, irrelevant charts, or redundant calculations in your workbook?  If so, removing them is an obvious quick fix.

2.   Delete any unnecessary formatting.  If you have rows of blank cells that are formatted (color, lines, etc.), doing away with this needless bling can be a help in your efforts to slim down.

3.   Compress any graphics in your file.  That beautiful company logo may look great in your workbook presentation, but it may be hogging unnecessary space.  Just double-click on image, go to Compress Pictures and choose the Email (96ppi) option.  This can make a surprisingly huge difference!

4.   Zip your files.  Although zipping Excel files does not have as much effect as in did years ago, the application WinZip can still help, especially if you are using (as is the case with some companies) Excel 2003.

These techniques are not all-encompassing, but they are a good start if you wish to put your files on a diet (speaking of which, maybe I’ll have a salad for lunch…).

Mail Merge

There are times when many Excel gurus will need to send an email to a list of recipients.  For instance, you may wish to send a Word document to everyone in your unit, your department, or even your entire company.  Now, I’m not talking about sending Spam, mind you, just legitimate communication that needs to be sent to a comparatively large number of email recipients.

You can, of course, accomplish this by manually entering each of the receiver’s address, but who wants to do that?!?

Another way is use to create a custom Contact Group using the innate abilities of Outlook.  This may be an acceptable course of action, especially if you have a fairly limited and stable number of addressees.

The fact is, of course, that lists of this sort are often kept in good old Excel.  They are regularly stored and updated in worksheets that are maintained in individual units and/or corporate human resource departments.

To make use of this information, save your Excel workbook to My Data Sources on your hard drive, open your Word doc and do the following:

1.    Click on the Mailings tab

2.    From the Start Mail Merge group, choose Start Mail Merge/E-Mail Messages

3.    Click on Select Recipients and choose Use an Existing List

4.    Finally, double-click on the Excel workbook housing your mailing list and finish

That’s really all there is to it.  It may, of course, take a few brief minutes of experimentation to become comfortable with this technique,.  I can assure you, however, you will be hooked once you use this procedure even once.  Mail Merge – Give it a try!

Logging TIme of Data Entry

Data Entry is a fact of life in any information system.  This is true whether the data is being entered straight into Excel or is being imported from another system which was the recipient of the data entry.

When the data was entered is often a worthy piece of information in itself.  If the data is being entered directly into Excel, there are several (some better than others…) ways of accomplishing this.  In our examples below, let’s assume we have the data being entered in Column C and we want the time it was entered in Column D:

1.    First of all, you could enter it manually by selecting the adjacent cell in Column D and pressing: Ctrl+Shift+;  This is a shortcut key for entering the current time.

2.    A better way to do this may, of course, be to use a Formula to enter the time. The =NOW() function not only records the current time, but the date as well.

3.    The problem with the approach described in #2 above is that this simple formula will Recalculate each time the worksheet is reopened or otherwise refreshed.  This is also the case (unfortunately) if you use a more sophisticated pre-filled formula such as =IF(C1="","",NOW()), since this will also update.

4.    If, therefore, you wish to take this to the next level, you may wish to try out some VBA code similar to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C1:C500")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

VBA is certainly not everyone’s cup of tea, but it is worth taking a look at occasionally.  Who know, you actually may find that you have hidden talents as a Code Expert!

Multi-Tiered Dropdowns Revisited

One of the most popular ongoing topics on my LinkedIn group, Excel Enthusiasts, (catchy name, eh?), is the surprisingly easy-to-create Multi-Tiered Dropdown configuration for more sophisticated research into your data.  Wow, that’s a mouthful, but hang on, it’s Really Cool!

The popularity of the topic is not surprising, since looking up information with the use of only one parameter is insufficient in many cases.  Take for instance that you have a large database with 65 different cities and 14 states. If you want to use the City Name in an Interactive DropDown List to pull up reports based on that city, you are faced with at least a Couple of Possible Glitches:

1. You will be presented with a Long List of City Names in your dropdown (65 in this case) 
2. Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)

There are, of course, a great number of other scenarios where a single parameter is simply lacking in lookup power.  So let’s build a Dropdown List of States that generates a Second Dropdown List of Cities for that state only. This way you can use the parameters of Both the State and the City to assemble your information!

Here is How It is Done:

 

Let’s assume you have a Horizontal Database containing your States and Cities in E2:N12, and your interactive cells set up in B2:C3 as per the illustration above. Note: The list of States would be in the first column of the database, E2:E12.

 

1. Select the Table, E2:N12

     a. Go to Formulas / Defined Names and choose Create from Selection

     b. Assure that the check box with Left Column only is checked and click OK

 

2. Select B3 and Create a Dropdown Box by Using Validation

     a. Under “Allow” choose List and select $E$2:$E$12

 

3. Select C3 and Create a Dropdown Box Using Validation

     a. Under “Allow” choose List and insert the formula =INDIRECT($B$3)

     b. Click OK (Click Yes if you get an error alert…)

 

4. Now to Make It Look a Bit More Professional…

     a. Select, F2:N12 (Note: Do Not include Column E)

     b. Go to the Home tab and select Find & Select from the right-hand side of the ribbon

     c. Select Go to Special and choose Blanks and click OK

     d. Right-click the selected area and Delete / Shift Cells Left

 

And that’s all there is to it! This is a very straightforward technique that results in Multi-Tiered DropDown Lists.  Incredibly useful when setting up Sophisticated Interactive Reports.

 

Interactivity in Reports is, as you may know, a theme that I address with regularity.  The reason is that they are what (whether they know it or not) your users Really, Really Want!

Add a Background (or not…)

When some Excel users learn that they can insert a Background Image into their Excel worksheets, they suddenly consider themselves the Picassos of the Excel World and believe that images belong everywhere.  This is, of course, certainly not advisable, and may raise the eyebrows of any executives that may see their handiwork. 

There are instances, however, when Backgrounds are appropriate or even desirable.  Take for instance an important Company Logo.  If inserted in the background in a very light-colored, non-interfering manner, the logo may serve as a tasteful Watermark.  This can be quite effective if you use a special company graphic that adds just a hint of a background without obscuring the readability of the data being presented in the worksheet.

 
How to Add the Background Image

Adding an image to a worksheet could not be easier (just try to resist doing it to all of your work…).  Simply do the following:

1. On the Toolbar, click on the Page Layout tab.
2. Go to the Page Setup group on the ribbon and click Background
3. Browse to the file in your own network or on the web, and double-click it
4. Voila!  Your image has been inserted!

 So, What if You Don’t Like the Background?

To remove an image, simply follow the same instructions listed above, but at Step 2 click “Delete Background”.

A Few Added Suggestions:

• Take Care in Choosing Your Picture:  Adding images can significantly increase the file size of your workbook if you choose a large image, so make sure it is comparatively small.
• Recommended Image Type:  For best results you should use standard image types such as JPG, PNG, or GIF.
• Format to Improve Appearance:  Use an image that provides good contrast with your Excel content, and try hiding cell gridlines and applying solid color shading to cells that contain data. It can be very effective.

Adding Backgrounds.  When used with caution, they can add a bit of pizazz to your worksheets.  Try it out the next time you are posting scores for your Company Bowling League.  Cheers!

Time, Time, Time

Ever since the early beginnings of Excel, handling Time in this program has caused many people a great deal of, well, time and grief.  This is especially true when it comes to data which has been Imported from some other program (usually a large database handling system).

There are all sorts of anomalies that can bedevil Excel users, but this week we are going to look at the common instance of when data is imported as text, And the application of one particularly Cool Trick! 

When you import the data you are going to be working with in Excel from such Database Software Systems such as Oracle, SQL, SAP, or others, you will often have the time data in the form of Text.  It cannot, therefore, be manipulated in Excel with sorting, functions, etc.  But how do you know if this is the case?

One giveaway that the new time data in your Excel worksheet is in text format is that the Time (or what appears to be “Time”) is aligned on the left-hand side of the cells into which it was imported.  If it was an Excel-friendly time-format it would be aligned on the right-side of the cells.

Another way to determine if the cells in question contain text is to use the built-in ISTEXT() function.  By placing a new column next to your Time column, you can insert this function and quickly copy it down your worksheet.  If the results come back as TRUE, your data is in the Text format.

So how do You Change Your Data from Text Format to Excel-friendly Time Format?  Easy:

1.   In a cell outside the database, (but on the same worksheet), put the Number “1” in it, and format it the same as the cells in the database
2. Then copy the new cell with 1 in it, and select the column with your suspect Time data
3. Next use Paste Special – Multiply (Great old trick…) on all of the “Time” data, converting it to Excel-Friendly numbers
4. Then simply configure the newly amended Time data into the desired time format

Badda-Bing, that’s it!  Converting your Text-based-Time to Excel-friendly-Time couldn’t be much easier.  Give it a try!

Imaginary Numbers Revisited

I am guessing I know what you are thinking:  Imaginary Numbers are something as useful in the real world as the stories of Alice in Wonderland (or, more accurately, “Alice’s Adventures in Wonderland”).

Although most of us seldom or ever run into any reference to Imaginary Numbers, there is a small faction of individuals in the sciences, such as electrical engineering, that could not succeed well without them.  The analysis of AC circuits, for example, would not be possible without these oddities in the math world.

If you are involved in an academic Math Curriculum (student or instructor), however, you will almost certainly encounter and use Imaginary Numbers at some time during your studies.

For those of us who have not had a math class in a long while, (or if you are not a Math Geek like me), a brief review of Imaginary Numbers is in order. They are simply a number that can be written as a real number multiplied by the Imaginary Unit i. The Square of i is -1. An imaginary number has a negative or zero square. For example, 5i is an imaginary number, and its square is -25.

In terms of imaginary numbers, the Square Root of -9 is 3i.

They are nothing at all new, as they were conceived in ancient Greece by a brilliant mathematician and engineer by the name of Hero of Alexandria. “Hero” (that’s quite a name, isn’t it?...) is noted for many inventions, including the first-recorded steam engine and a rocket-like reaction engine.  Not bad a first-century Greek.

So, what about Excel?  Interestingly, Excel can easily handle these oddities of the math world. It even has a couple of specially designed functions to deal with Imaginary and Complex (expressed in the form a + bi, where a and b are real numbers and i is the imaginary unit, where i2 = −1).

The Imaginary function operates with the syntax:
   =IMAGINARY(inumber)

The Complex function uses this syntax:
   =COMPLEX(realnumber, inumber, [syntax])

There are also several built-in tools in Excel for using imaginary numbers with typical arithmetic functions of addition, multiplication, etc.

So, why am I even discussing this topic today?  Well, if you have children in school, I wouldn’t be surprised if your son or daughter comes home one day with homework involving Imaginary Numbers.  Wouldn’t it be cool if you could show them how this can be done in Excel?  It may even be a surprise for her or his teacher! 

Imaginary Numbers.  Not just topics for Alice and the White Rabbit...