Watermarks in Excel

The use of watermarks in business has been prevalent for a very long time.  They serve many functions and are certainly useful in Excel.  An Excel user may wish to include a WATERMARK to indicate a Special Status of an Excel worksheet. For instance, you may wish to mark it PROOF, CONFIDENTIAL or DRAFT.  A watermark can be a quick and easy solution.

First, you may wish to create your own specific image.  In WordArt, you can easily create an image (such as DRAFT), upload it to an image-handling app, and save it as a .png file.  Then it is a simple matter of adding it to your Excel worksheet as a Background Image.

To add your Background Image/Watermark, 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 computer or network, and double-click it
4. Bamm!  Your Watermark has been inserted!

One Additional Suggestion:  Format your image that provides a proper Low Contrast with your Excel content. You don’t want the image to overpower and obscure the data in your worksheet.

This may take a few minutes to do this the first time, but the results are really quite effective. If you ever find the need to add a WATERMARK in your Excel worksheet, give it a try!

Be CHOOSY!

There are countless undiscovered treasures in Excel, so it pays to explore a bit and be Choosy.  For instance, Nested IF functions can be very powerful, but they have limitations (the maximum nested functions is 7 in older versions of Excel) and they can be a bit difficult and cumbersome.  That is why it is nice to have a Choice!

Having a Choice is almost always a Good Thing, and fortunately, there is a preferable alternative to using the occasionally awkward IF functions. The CHOOSE function is often a better selection, as it is considerably more versatile! The CHOOSE function is remarkably straightforward and simple to use, and is best when combined with other Excel functions. It quite humbly returns a value from a list based on a given Position (Index Number).

Here is the Unsophisticated but Valuable Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Now for Some Basic Examples:

=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central

It also works with ranges:

=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30

You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to Cell A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would quite obviously return East.

The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Be Choosy and give it a try sometime!

MicroGraphs Rock!

Let’s face it, Not Everyone has a current (or even near-current) version of Excel. This is particularly true in many corporate settings, since not all companies see the value in upgrading our favorite spreadsheet program.  As you may know, “Sparklines” first appeared in Excel 2010, and are for many Excel users, a quick and easy way to graphically demonstrate their data without much work.  Sparklines make it a breeze to create readily-intelligible micrographs within the cells of your Excel spreadsheets that highlight important details of your data. 

But what if you use an older version of Excel, you ask?  Can I still make cool, little micrographs in my workbooks?  Yes, You Can!

A very effective way to make these engaging little graphs is to incorporate an unusual and diminutive Formula along with a splash of Conditional Formatting.  Let’s take a look at how this can be done…

Let’s say you have a Crack Sales Team in your company, and you want to graphically show their results without going to the trouble of creating a bunch of individual charts. Using the illustration below, put the Producers of your company’s products in Column A, and in Column B put the Units Sold each producer has sold. Here is the super-simple formula you should put in Cell C2 (and then copy it to C8):

= REPT( “l” , B2/1,000) 

 

For each Approximate Count of One Thousand, the formula puts an old-fashioned Hash Mark, (using a bold, simple font, such as Trebuchet or Arial works well), in Column C. With a bit of cool Conditional Formatting to add color, the result is a clean, professional report that integrates MicroGraphs!

Using these elementary graphs makes your data Visually Comprehensible and enhances the user’s understanding of what is being said in your Excel reports.
Try it out sometime if you are using a pre-2010 version of Excel, (or even if you are using a more contemporary version), and see what you think. They really do Rock!

The MMXVI Olympics

Excel users, as I am sure many of you will agree, can be a rather serious lot.  “Fun” does not seem to be in many guru’s vocabulary.  Although of no real Practical use, it can be interesting, (in a geeky sort of way), to play around with Roman Numerals. For an even very experienced Excel user, it is particularly interesting to find that an application has a built-in Roman numeral function.

Practicality, of course, can be overrated, and it is readily apparent that Hollywood, Football and, of course, the Olympics have all used Roman numerals on a regular basis. If you also wish to do something off-the-wall in an Excel report, (I probably wouldn’t recommend it for your next quarterly report to your boss, unless you are in the market for a new job soon…), you can use the Roman function.

For a classic numeral, (other formats are available, but why complicate things), simply enter a value in cell A1 and type, “=Roman(A1)” in cell B1. Hit Enter and Presto, a Roman numeral of the A1 number! If you typed in 2016 you will get the Roman Numeral representing this year’s Summer Olympics.  Pretty Cool!

If you are a Super Geek when it comes to Roman Numerals, and you want to explore this function a bit more, you can use the Second Argument option with the ROMAN function, which converts your results to varying levels of brevity.  Note the following values in the table below:

 

 

ROMAN Numerals.  A bit of Fun in this Olympic year!

Organize Your Data

Today we are going to look at some important Best Practices for organizing your data and laying out your Excel workbooks. These are not, of course, Absolute Rules, but if you follow these practices, you will find Greater Control and More Options in your Excel adventures.

Data on One Worksheet; Reporting on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports/information in another worksheet. The fewer the worksheets you have, the easier it will be for your users (peers, employees, executives, and yourself, of course) to navigate and obtain the information they want to see when they want to see it.

No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity.  Just don’t do that.

Deconstruct Your Data for Better Analysis
Always try to divide your data down to its Minimum Components. You will be able to use more powerful functions and search your data much more effectively. For instance, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later using Concatenation if you find it necessary to do so).

Format Your Headings 
Use a bold font and centering when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.  Besides that, they just plain look better!

Sort Your Data 
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.  If it makes logical sense, why not do it…

Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.  This may not be as necessary as Excel becomes more powerful, but it will almost always be More Intuitive.

If you follow these guidelines, you will easily be able to use the most powerful built-in Excel features that require organized data and a logical layout. As I have said in the past, Organization = Simplification = Information.  Good Stuff!