More Room, Please

In spite of the current Tiny House rage, it is safe to say that most of us prefer to have a bit More Room.  In addition to our abodes, this can apply also apply to Excel.

 As we all know, the focus of Excel is numbers. There are, of course, times when you need to enter some Text into cells as well, and you may even want to Add Another Paragraph (or perhaps just a line break) in a single cell. If you are working in Word, that’s easy – just hit the Enter key.

In Excel, however, hitting Enter will just take you to the next row below. “That is So Wrong!” you say, but don’t despair, the solution is so easy it will make you Laugh with delight (or maybe just smile…).

As we also all know, there is typically More Than One Way to do just about anything in Excel. I am a big fan of using the keyboard whenever possible, but we will look at a couple of techniques to enable a user to accomplish this very Simple, but Useful trick.

First Technique:
Once you have selected the cell you will be entering the text, simply click the Wrap Text button in the Alignment group of the Home ribbon (Excel 2007 – Excel 2016).

Second (and Totally Coolest!) Technique:
Using just the keyboard, all you need to do is press Alt+Enter to start a new line in the cell in which you are typing or editing. Does that Rock or What! Ask the other Excel Gurus in this office if they know this trick (I bet they don’t…).

Excel Charts: Other Uses

Charts are, without a doubt, one of the Superstar tools in Excel.  They can take otherwise dull, confusing data, and make it Visually Exciting and Informative.  After all, if your hard work and data is not easily understood, it serves no good purpose.

Limiting the accessibility of these spirited charts for use only in Excel worksheets, however, is not making the most of what they can offer.  For instance, how many of us have suffered through mundane PowerPoint presentations showing rows of dreary data and narrated by a presenter that has the droning voice of a far-off engine?  PowerPoints can be so much better with the introduction of some lively Charts!

So how do you add a Chart to a PowerPoint presentation?  For a Static chart, simply select the chart in Excel, right-click copy it, and simply paste/embed it into your presentation.  It can then be resized and further modified if you wish.

But what if you want to make a dynamic link between your Excel worksheet and your PowerPoint, so that when your data and chart changes on your worksheet, it also changes in your presentation?  Merely copy your chart as you did before, but when you paste it, use the “Keep Source Formatting and Link Data (F)” option.  You then have the luxury of having nearly instantaneous changes in your PowerPoint whenever you have changes in your Excel worksheet.

 

Very Cool!  Use this elementary technique to enhance the overt Professionalism in your presentations and documents.

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!