Play It Again, Sam…

It is always fascinating to me to find Easily-Applied and Useful Shortcuts in Excel. Although today’s shortcut is Super-Easy to use, a quick survey of accomplished Excel Enthusiasts has revealed that few Excel users are aware of this handy tool. This just supports the well-founded knowledge that no one knows Everything about Excel!

While it is common and very intuitive to copy and paste Repetitive data to a set of noncontiguous cells (copy data, select the cells and paste), entering Newly Typed data doesn’t work quite as swimmingly (as the British may say…).

To enter Repetitive New Data into a series of noncontiguous cells, simply hold down the Ctrl key and select all of the cells into which you want to enter your new data. Then type the text you want to enter and (a little drum roll, please…) press Ctrl+Enter.

And, Here’s lookin’ at you, kid, Excel will enter the typed text into All of the Cells in your selection. Now, that’s really nice.

This is may seem a bit elementary, but as any long-time Excel user will testify, “ The fundamental things apply as time goes by.”

Cheers.

Avoiding Color-Coding Problems

Using Color is a very appealing way to format cells in your Excel worksheet. It makes the information you are analyzing and providing to other users visually apparent and adds life to what may otherwise be dull data.

Color-Coding can be problematical, however. If you wish to analyze your data by color, doing this can cause Unnecessary difficulties. Let’s say, for instance, that you want to produce the Average of cells based on color, (e.g. perhaps they represent Regions), and you have coded these cells with five different colors. How Do You Do This?

The VBA coding gurus would say, “Easy, you just write a complex code that you can link to a macro, and Badda-Bing, there you have it!” The fact is, of course, not many of us are comfortable using VBA, and this can also be an issue if the workbook is ever passed on to another user for maintenance.

The Best Solution is to Avoid the Problem entirely, while still making use of your Color-Coding scheme. Simply add an additional column, (which can be hidden, if you wish), and indicate the analysis criteria (this may be “Region”, “Status”, “Type”, “Customer”, whatever…) in this new field.

By constructing your spreadsheet with this additional column/field, you will then be able to easily use a simple AverageIf function (or whatever other function suits your purposes) and easily analyze your data. No VBA Required!

By avoiding the use of special programming, you will avoid potential problems in the future, and you will still be able to avail yourself of Visual AND Analytic Attributes.

In other words, you can have the Best of Both Worlds.  Cool…

Need More Room in a Cell? No Problem!

Happy New Year, All! I hope you are off to a Great Start in 2013!

As everyone know, the focus of Excel is numbers. There are, of course, times when you want to enter Text into cells as well. Sometimes 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 Bogus!” you say, but take heart, the solution is so easy it will make you Laugh.

Now, 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 ways to enable a user to accomplish this Simple, but Useful trick. (Come to think of it, some people may describe me in those terms: Simple but Useful – Ha!).

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 2013).

Second (and 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. How Cool is That! Go ahead and see if your Excel Guru colleagues know this trick…

Cheers!

Roman Numerals

Happy Boxing Day, All! I hope you are enjoying the holidays!

The old adage, “All work and no play makes Jack a dull boy”, is as true today as it was when it was first published in 1659. Therefore, this week’s blog is being devoted to a bit of Geeky Fun. Although of no real practical use, it can be interesting, (once again, in a geeky sort of way), to play around with Roman Numerals.

Interestingly, there is a Roman Numeral function that is built-in to Excel. Why that Microsoft has done this is not entirely clear from a practical standpoint.

Practicality, of course, can be overrated, and it is readily apparent that Pro Football, Hollywood, and the Olympics have all used Roman numerals on a regular basis. If you also wish to do this sometime in Excel, (You can even use it for your next quarterly report to your boss, i individual has a really good sense of humor), you can use the ROMAN function.

Try it out! For a Classic Numeral, (other formats are available, but who needs them…), 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! Now you will be all set if the NFL needs someone to come up with the name for the next Super Bowl!

The RAND Function & Excel Slot Machine

It’s time for a little review and some Fun! Let’s look at the fascinating RAND function. This intriguing and useful function returns a Random Number that is Greater Than or Equal to 0 and Less Than 1. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a New Random Number.

It should be noted that some hard core statisticians have voiced concerns about the true Randomness of the RAND function (it is prone to sequential correlations if large runs of numbers are taken), but it suffices for nearly all but the most demanding statistical applications, and if fine for us mere mortals.

The Syntax for the Rand function is simply:

RAND( )

If you want to create a random number between two numbers, (where a is the smallest number and b is the largest number), you can use the following:

=RAND()*(b-a)+a

If you want only Whole Numbers you can use:

RANDBETWEEN()

For example, =RANDBETWEEN(1, 500) will produce a Random Whole Number between 1 and 500.

There are countless statistical applications, of course, but you can also use it in some entertaining applications. For example, some Excel Enthusiasts have used it to create Tetris-style and Dice-Rolling games in Excel.

I have used the RAND function in conjunction with other functions and graphics to create a Slot Machine in Excel (send me a request at excelenthusiast@gmail.com if you would like a copy of the Slot Machine spreadsheet). I will be happy to send a copy to you.

The RAND function. Great for use in statistical applications, building games, slot machines, and other Fun Stuff!

Cheers!

Navigating the Seas of Excel

It’s Back to Basics Week, so today we are going to review the topic of navigating in Excel. Sailing the Seas of your Excel worksheets can be drudgery for any Excel sailor, so we all need a few simple tricks. Imagine that you wish to go to your last entry at the bottom of a list that contains 20 records, scrolling to where you wish to go is simple and effective. When you have a list containing 20,000 records, however, it can be more than dreary.

As any Slick Excel Seafarer knows, keyboard shortcuts rule when it comes to saving time sailing from one location to another on your spreadsheet.

Here are a few Navigation Tricks you can make without ever touching a mouse (or getting sent to the brig):

1. Control / Down Arrow: Goes to last cell in column with data

2. Control / Right Arrow: Goes to last cell in row with data

3. Control / End: Goes to last row, column and cell

4. Control / Home: Returns to cell A1

Mouse Tricks (Every ship has Mice)

Another way to navigate to the end of your data (whether in a column or row) is to precisely hover your pointer on the adjacent Border of Cell in your range and double-click. If you wish to navigate to the last cell in a column of data that starts with cell C1, for example, you can select C1 and double-click on the Bottom Border of the cell.

Another Alternative (It's always good to have Choices)

When you know the Exact Address of some remote cell, simply enter the address (e.g. G2100) in the Name Box and Avast Me Hearties; you are swiftly transported directly to that location.

So, put on your Pirate Patch and try a different way or two of Navigating on the Excel Seas.  You can sail to where you want to be in The Blink of an Eye, Laddie!

Custom Lists in Excel

"Have it your way" does not only apply to old Burger King ads, you can also create Custom Lists in

Excel that meet your special requirements. Excel provides many familiar Built-in Lists, such as Sunday-Saturday, January- December, etc. You may find your own, user-defined lists very practical, however, as they can be used for sorting or Auto-Filling your data.

• Let's say, for instance, that you want to want to make a Special Sorting Order for the Sales Personnel in your company: Vice President of Sales, National Sales Manager, Regional Sales Manager, Department Sales Manager, and Sales Representative.
• Perhaps you want a Custom List by region: Northeast, Central, North, South, Northwest, Southwest. It is all quite easy.

Assuming your custom list is not too lengthy, you can type the values directly in the dialog box (if your list is long, you can import it from a range of cells.)

Using Excel 2007 for this Demonstration, Here is the Easiest Way to Do This:

1. Click the Microsoft Office Button, (go to File/Advanced tab in later versions ), and then click the Excel Options button
2. Click the Popular category, and then under Top options for working with Excel, click the Edit Custom Lists button
3. In the Custom Lists box, click NEW LIST, and then type the entries in the List entries slot
4. When the list is complete, click Add
5. Click OK twice

 

Presto! The items in the list that you selected are added to the Custom Lists box.

Cool Feature: The Custom List is added to your computer's registry, so it is available for use in other Excel workbooks.

Try it out! It takes only a minute or two to make your own, reusable Custom List!