Red Light - Green Light

Everybody raise their hand who likes typical, boring, drab Excel workbooks. Anybody? No? I didn’t think so. I am sure most of us would agree that Excel worksheets usually lack any kind of Pizzazz to make them engaging. Well today we are going to look at how to take the “Cold” out of Cold Hard Data.

A worksheet can serve as a Decision Tool and showing a Yes, No, or Maybe adjacent to your agenda items can certainly get the job done. but it’s Boring, Boring, Boring, isn’t it? Since the advent of Excel 2007 you have sets of Graphical Icons with your decision lists that can give your reports some of that much-needed Sparkle!

Let’s suppose you have a proposed agenda for an upcoming annual corporate meeting. Possible topics have been submitted from various departments, and you want to use a Semaphore for an icon when make a preliminary worksheet showing a:

• Green Light for a definite inclusion in the program
• Yellow Light for a possible inclusion
• Red Light to exclude the topic from the agenda

Here’s What to Do:

1.  Select the range of cells in which you want a Semaphore to appear
2.  Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
3.  Go back to Conditional Formatting and choose Manage Rules
4.  Click Edit Rule and put a check in the Show Icon Only box and Apply
5.  By default, a “1” will be a Red Light; “2” will be a Yellow Light, and “3” will be a Green Light

Your Decision Lists will instantly take on a New, Engaging Look and will be sure to gain closer looks by any of the users. And, of course, the cool thing is that it is Easy (and “Easy” is a good thing…). 

Best Chart Titles Ever!

Using Dynamic Chart Titles has been one of the most popular topics of this blog. Since dynamically controlling your chart’s Titles can make your charts even better, it is fitting to look at this fantastic technique one more time. Believe me, if you are looking for an “Oh, Wow!” feature for your next report that includes a chart, a Dynamic Chart Title is for you! By linking the chart title to a value displayed in a specific cell on your worksheet, you can create a very accessible chart that will amaze and delight your users (and maybe garner you the attention you deserve…).

For example, we will presume you have a table of data and a chart that changes dynamically every time you change the value in a dropdown box. It is then remarkably easy to create our eye-catching title functionality:

1. Create your chart linking it to your dynamic table
2. Select your chart by clicking on it
3. Click the Chart menu and choose Chart Options
4. From the Chart Options window, click on the Chart Title box
5. Enter a temporary placeholder value (Such as “Chart1) and click OK
6. Make sure the temporary title is selected and click the Formula Bar above the sheet
7. Type "=" then click on the cell that contains the dropdown box, (simply created using Validation/List), and click Enter

How Totally Cool is That!  Now every time you change the Name or Value in the Drop-Down, the Chart Automatically Updates its Title! This is an advanced, (but effortless) way to make the charts in your report Stand Out from the mundane masses.

Seriously, give this technique a try; you’re going to like it!

A Few More Tricks…

Excel Tricks can be a seemingly magical way to save time and look Cool (and, of course, looking “Cool” is the most important…) in Excel. Here are a few of my current favorites:

1. Select Non-contiguous Cells and Ranges
We all know how to select contiguous cells in a range or database, but how about non-contiguous cells?  Selecting these cells in a worksheet is as simple as holding down the CTRL key and click on the cells you want.  Presto!

2. Enter in Bulk

Let’s say you want to enter the same numbers or same text in a block of cells. There are a number of tedious ways of doing this, but if you want to save your wrist and look Totally Cool in the process, you can do the following:

    a) Select the entire range of cells you want to contain the same content

    b) Type your words or values (be sure to do this while having the entire block of cells selected)

    c) Press CTRL+ENTER and Bamm - That’s it!

3. Align Text Your Way!
Right-Click and access the Alignment tab on Format Cells. It’s a Snap aligning your cell in any orientation you want.  For instance, do you want the text in your cell to be vertically oriented?  Just click the Vertical Orientation Graphic, and Boom, mission accomplished!

4. Jump Between Worksheets

To move from worksheet to another does not mean you need to reach for your mouse.  To switch to the next worksheet to the left, keep your hands on the keyboard and simply enter Ctrl + Page Up. Or change to the worksheet to the right by entering Ctrl + Page Down. Nice!

5. Use Your Chart in Another App
Do you want to use your chart in PowerPoint, Word, or some other application? Select your chart and Copy/Paste as a Picture.  You can then feel assured that it will stay true to the original.  Easy Magic (and “Easy” is always a good thing…).

Tricks, Tricks, Tricks! Every Excel Guru should have a sack of them! 

Keep an Eye on Things

I think we can all agree that it is a Best Practice to, as the old saying goes, to Keep an Eye on Things. This is as true in Excel as it is in any business setting.

Doing this can, of course, be challenging at times.  Keeping an eye on the values of certain cells that are not immediately visible on a worksheet can be, at very least, inconvenient. This is can be especially problematical if you are working with a spreadsheet that has grown overly bulky in size, as continual scrolling to remote areas on your spreadsheet or workbook can be time-consuming and tedious.

So, is there a built-in tool in Excel that can assist us in this regard? Yes, there is! Watch Window to the rescue!  This frequently overlooked powerhouse of a tool can help anyone from a novice to the most experienced Excel user. The convenient Watch Window can display the value of any cell or range of cells in a viewing pane that can be located on your screen wherever you wish. This mighty toolbar keeps track of the following assets of anything you wish to keep tabs on:

·       Workbook

·       Sheet

·       Name

·       Cell

·       Value

·       Formula

How do you set it up? Very simply:

1.     Select the Formulas tab on the ribbon

2.     Select Watch Window from the Formula Auditing section

3.     The Watch Window will then appear

4.     Click the Add Watch button to specify the cell(s) you wish to monitor

It is that remarkably easy!  Watching what is happening on your critical areas of your data could not be simpler!

The next time you feel the need to “Keep an Eye on Things”, give Watch Window a try. I think you will like it!

A Forgotten Tool

There are so many valuable tools in Excel that it is easy to forget some that are both beneficial and quick to use. That is why it is a good practice to take a look at some Old Friends once in a while.

Paste Special is one of those Excel tools that is, indeed, a great friend, as it can do a number (17, in fact) of things that are highly useful on a daily basis.

Yes, indeed, there are no less than 17 Cool Things you can do with Paste Special.  To find it, Right-Click (Right-Click is also our friend…) a cell in your worksheet, and Paste Special will appear near the top of the dropdown list.

Here are my 5 Favorite Ways to use Paste Special:

1.  Paste Values: Need a great way to get rid of the formulas that created the data you’re working on? Simply copy the results of formulas, and Paste the Values in a new range of cells. 

2.  Paste a Link: Want to link to data in another worksheet? Merely copy the cell in another worksheet, click the Paste Link button on bottom-left of the Paste Special dialogue box and, Bamm, you can paste the dynamic (“dynamic” is a good thing…) link to a new location.

3.  Paste Validation: Would you like to reuse Validation you have created elsewhere in your workbook? Copy the cell or range that has Validation applied, and paste validation only to a new cell or range.  Totally Cool!

4.  Multiply or Divide: Need to apply some basic arithmetic to a range? You can copy a value and use Paste Special to multiply or divide an entire existing range of numbers.  Very useful of updating your data…

5.  Transpose: Want to change the view of your worksheet? You can instantly transpose a column into a row, or a row into a column.  Changing your mind couldn’t be easier!

These are my personal Paste Special favorites, but you may well have others that are essential to you.  Do a quick review of this tool some time, and find out why it is a “Great Friend” to have at hand!

Data Entry

I know what you may be thinking, “Data Entry” – What a bore!  Yes, I agree, and that is why a few tricks are important to make this necessary function more stress-free.

Enter the highly useful Data Entry Form that for many gurus is an unlikely boon in Excel. In addition to being an efficient way to enter data into an Excel database, the Form also allows you to other useful functions such as:

   • Edit or delete individual records
   • Scroll through each of your data records
   • Locate records containing specific information

If you or someone you work with frequently adds records to databases, give the Data Form Tool a try.  For anyone who routinely enters data, it can make your life a bit easier, and “Easier” is always a good thing…

How to Add the Form to Your Workbook for Current Excel Users:

For users of Microsoft Excel 2007 – 2016, you won’t initially find the Data Form command in the Ribbon. To apply this command, you need to customize the Ribbon by simply doing the following:

1.    Click the File tab and Options button to enter the Excel Options

2.    Click the Customize button at left bar

3.    Select the Commands Not in the Ribbon and find out the Form from the command list

4.    Click the Add button

5.    Click the OK button to finish up

Now go to your database and select the Form from your Quick Access Toolbar.  Alacazam!  Up pops a new Data Entry Form for updating your database!  Ah, “Easy” is a good thing…

24-Hour Time

24-hour time is a beneficial tool in many circumstances.  If you have ever been in the military, you know that the armed forces operates on the basis of a 24-hour clock, beginning at midnight (0000 hours). So, 1:00 AM is 0100 hours, 2:00 AM is 0200 hours, and so-on up until 11:00 PM which is 2300 hours. This has the advantage of preventing ambiguity and has been the standard for centuries.

Not only is it used in the military, this system is the most commonly used time notation in the world today, and is the international standard for noting time. This is particularly true in the fields of medicine, computer support, and other disciplines.

Since occasionally we may be faced with translating Military Time to our typical U.S. Standard Time in Excel, we will take a look at how this can be easily done. A couple of Cool Formulas really help out!

First of all, let’s assume you have Cell A1 populated with military time expressed in the typical 4 digits. Using the TIMEVALUE function, which very neatly converts time represented by a text string into the Decimal Number Excel can work with, we can use the following to perform our quick bit of magic:

=TIMEVALUE(LEFT(A1, 2) & “:” & RIGHT(A1,2)

What this formula does is to simply choose the left two digits and put a colon between them and the last two digits. Concatenation can has its place!

A somewhat more sophisticated approach is to use an embedded TEXT function to return a formatted string, and then use TIMEVALUE as we did in the previous example:

=TIMEVALUE( TEXT(A1, “00\:00” ))

Whichever method you use, you will want to be sure and Format your result cell to Time. For example, the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting. 

Although this may seem a bit obscure, more businesses than ever are using this for their time element in databases, so familiarity with these techniques can be valuable, and You will be the one who knows how to handle this in Excel!