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!

A Bag of Tricks

If you have frequented this blog in the past, you probably know that I like to occasionally take a look at some helpful, (and at times, a bit obscure), Tricks in Excel.

Here are 5 of my Current Favorites:

#1 Selecting Data or the Entire Worksheet 
First of all, a couple of favorite basics.  Select any cell in your database and click Ctrl+A on your keyboard; Bamm! You have selected the database. Want to select the entire worksheet? Just click the “A” once again! 

#2 Display Formulas So You can Troubleshoot Issues 
This can at times be a vital, however simple, tool. Select any cell on cell on your worksheet and while holding down the Ctrl key, press “~” on your keyboard. Bamm! All of your formulas will be visible! 

#3 Lock Your Formatting 
Most of us know that the Format Painter is a terrific way to save time when adding a professional look to your worksheets. We know that when you format and select a cell, you can click Format Painter and paste the formatting into any single cell or contiguous range.

The Trick that many of us do not know is that if you Double-Click the Format Painter, it will Lock the formatting so you can apply it to as many cells as you like (contiguous or otherwise! 

#4 Prevent Error Checking 
An important automatic function of Excel, the Error Checking function is highly useful for pointing out Inconsistent Formulas in a range. There are times, of course, when you may need to omit a range’s formula in a few cells. Excel will then Flag what it perceives as an Error! 

To avoid this annoyance, go to Excel Options and choose the Formulas group. Simply clear any of the error-checking rules that are bugging you. 

#5 Move the Quick Access Toolbar
Customizing the Quick Access Toolbar is a beneficial action that many experienced Excel users employ.  However, you may not like having it up in the upper-left corner (it can be a stretch with your mouse!).

Happily, you can opt to have it displayed at the bottom of the ribbon by simply clicking on Show Quick Access Toolbar below the Ribbon in the dropdown menu of the Quick Access Toolbar.

5 Quick Tricks. Give them a try!

Popular Topics #3

In this final installment of Popular Topics, Scatterplots and the Coefficient of Determination have been a very frequent hit in the past 8 years.

A Scatterplot Chart and accompanying COD (Coefficient Of Determination) are regularly used to show the relationship between two sets of data. For example, a sales manager may plot the Number of Sales Calls Taken with the Number of Sales Made. Another case is comparing the Average Length of Time a customer service representative takes per call and the Overall Quality Score of their calls.

The Strength of the correlation is particularly important.  To determine the strength between sets of data, experienced Excel users can make a Scatterplot Chart and:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your aesthetic preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

The R-Squared value is your Coefficient of Determination (COD) that will indicate how strong your data on your two axes. In the graph example below, the COD value is .5574 (or approximately 56%) representing a Strong Correlation (and therefore reliable).

The next time you have two related sets of data, try using a Scatterplot and Coefficient of Determination to test the strength of the correlation. This can be very informative, and can positively impact business decisions.  The technique is Fast, Effective, and remarkably Easy.

Popular Topics #2

As we discussed last week, in the 8-year publication of this blog, there have been several popular topics that have gotten more than their share of views. Besides the Insert Function Wizard, the ability download Currency Rates directly into Excel has been a remarkably trendy subject.

Longtime users of Excel probably know that Microsoft used to provide a connection to their MSN data source.  That, perhaps unfortunately, is no longer the case, so Excel users are faced with finding an alternative.

This being the case, FloatRates.com has become a popular website to get this data. Their XML data is easily extracted, and users do not need to do anything fancy with the parameters.  If you are looking for the latest US Dollar exchange rates, click on http://www.floatrates.com/daily/usd.xml and choose View Source in your browser to obtain the Excel-friendly data which you can copy into a worksheet.

From within your worksheet, using Get External Data / From Other Sources / From XML data import will help Excel to ignore the innate formatting and use the XML data only. With a little practice, you will find that it is not difficult.

Well, it may not be quite as easy as in The Old Days, but getting your current exchange rates into Excel is still pretty easy to do.  All this talk about exchange rates makes me think, Is it time for a vacation yet?...

Cheers!

Popular Topics

In the 8-year publication of this blog, one of the most popular topics has been the Insert Function Wizard.  A Wizard is, of course, a help feature in Excel that automates finding a function by asking the user a series of rudimentary questions.

It’s Been Around

This wizard has been around a long time, and has been important tool for newcomers and experienced Excel users alike.  The names Excel functions are not always intuitive, so the Wizard can be invaluable when searching for what you need. 

Where Is It?

In any contemporary versions of Excel, you can find the Insert Function Wizard nestled neatly in the left corner on the Formulas ribbon.  There you can enter a brief description of what you want to do, (let’s say you want to calculate the Yield of security you are considering), and Presto, a list is presented for you to choose from.

A Snap to Use

Then is a simple matter to complete follow the step-by-step instructions of the utensil to complete you function/formula. The simplicity and efficacy of this handy wizard is the driving reason it has remained one of Excel’s favorite Go-To Tools. 

Simply Do the Following
1) Pull up the Insert Function Wizard
2) Type your description in the search textbox (for example, “loan payment”)
3) Select the function (it would likely be PMT)
4) Complete the Function Arguments and click OK

The Insert Function Wizard. Give it a try and find out how easy it is to perform a bit of Magic when you need it.

Troubleshooting

If you have been working with Excel for quite some time, you have probably Inherited an Excel workbook that was, to say the least, a bit confusing. The original creator of the workbook is, of course, no longer accessible, so you are saddled with the task of Troubleshooting.   Troubleshooting is, without question, a Valuable Skill in Excel, and possessing this skill will most certainly make you an MVP in most corporations.  

One common issue is the use of formulas (and, to be perfectly frank, the peculiar way some users set up their workbooks). An easy way to identify and display the cells with formulas is to hold down the Ctrl button and press the tilde (that squiggly character in the upper left of your keyboard: ~). When you press this keyboard combination, all your formulas will appear in their cells (and pressing the combination again will bring back your original view).

The foregoing does not solve All your investigative challenges, of course, as the Aggravating Problem can be trying to determine from where a formula is drawing its information. Happily, Excel contains a Very Helpful Tool for clicking on a formula cell and tracing its Precedents. To find it, go to Formulas / Formula Auditing.

From there, clicking on Trace Precedents will give you a Graphical Illustration with arrows that show you from where your information is being derived. “Pretty Cool!” you say? Yes, I quite agree!

Let’s face it, problems happen in Excel, and Ctrl + ~ and Trace Precedents are two excellent tools to help you figure out those cryptic Excel workbooks that you inherit from others. They can help you quickly get your Excel workbook back on track and get on with your day. You may even stop cussing old What’s-his-name who created the workbook in the first place…