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!

Picture Charts!

There is no doubt that Excel provides a considerable array of Chart Types to choose from. There are times, however, when you may want to Add a Bit of Pizazz to your charts. One of the coolest ways to do this is by Replacing the Series Element (Columns or Bars work the best) with a Graphic.

You can easily Add Impact to your charts by doing the following:

1. Copy a Graphic (simple images work the best) to your clipboard
2. Create your Chart using columns or bars
3. Select the Chart Column or Bar Series
4. Go to your Home Tab and Paste the image
5. Bonus: Format the Data Series by going to Series Options and choosing 0% Gap Width

Since I live in southern California, I chose a Surfboard Graphic for my Surfing Days Chart above. To demonstrate the difference this can make, I did a Side-by-Side illustration below. I chose a Bag of Money to replace the Boring column to depict the sales by month.

Picture Charts. Another way to Add Interest and Impact to your Excel worksheets.

Surf’s Up!

Excel on the Cloud

As any technophile knows, The Cloud is the Major Buzz these days. It seems that everyone is scrambling to say, "Me Too" in their quest to capture your traffic and business via cloud-based apps or, as with Microsoft's Office 2013, span both traditional approaches and the cutting edge.

My current favorite Cloud Solution is indeed supplied by the venerable Microsoft. By placing your Excel workbooks on SkyDrive, they can be shared (at your discretion) with the world. All you need to do is go to your SkyDrive, right-click the document, and then click Share. Type the email address of the person you want to share the workbook with, and Bamm! Done!

But Wait! What makes it Really Cool in this instance is that the other parties with whom you are sharing Do Not even have to have Excel on their computer! That Totally Rocks!

Other favorites include the iPad Numbers app. Workbooks created or edited in Numbers can be converted to the Excel format, and shared on iTunes, DropBox, and other cloud-based facilities.

Google Docs is also hugely adaptive and beneficial for anyone (or any organization) who is on a budget, and still wants reasonably high functionality and ease of collaboration.

So, what about me, am I personally Truly embracing the mobile world and the cloud? Well, as an evidenced by this week's blog which I have written entirely on an iPad while awaiting an appointment, yeah, I guess you could say that I am!

Happy Thanksgiving, All!

What’s the Dif, Man?

Back in July of 2010, we looked at the obscure, and curiously undocumented function, DATEDIF. Microsoft, in all of its wisdom, (small amount of gentle sarcasm here), has chosen not to include documented information on this Essential Function in Excel. Over the past 5 years of this blog, this topic has been one of the readers’ favorite.

As any longtime user knows, the way Excel handles dates can be a bit puzzling at times. Finding the Difference between two Dates, for instance, is not readily intuitive. Here is where DATEDIF shines!

The Syntax of the Function is as Follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d (Days) = Number of days between the dates
m (Months) = Complete calendar months between the dates
y (Years) = Complete calendar years between the dates

Important Note #1 (you don’t need this craziness): The Second Date must be greater than the First Date, or you will get a Number Error.

A novel application of this function is to nest the NOW() function into it and calculate a Person’s Age. The NOW() function returns the Current Date and Time, and when used with DATEDIF, it can produce an Excel calculator that many find amusing. (Note: the “BirthDate” can refer to an easily changed Cell Value):

=DateDif(BirthDate, NOW(), “y”)

Important Note #2: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

You may well find a great many ways to use the DATEDIF function, which may lead you to wonder, Why is this Terrific Tool not documented? Ah, well, who am I to question the great Microsoft…

Go To Special

I have a question for you: Do you ever use Go To Special?

If you answered, “No” to the previous question, you are undoubtedly in the vast majority of other Excel users. You may find this to be a useful tool, however.

I’m quite sure that some time in your Excel career, you have found yourself wondering how you can locate certain types of cells within your worksheets. For example, you may have wanted to find the cells in your worksheet which contain Formulas, or perhaps those which have Conditional Formats.

You may be pleased to know that Excel provides a terrific Tool to help you locate these and many other Special Types of cells. The Go To Special dialog box can provide you with options you may not have realized that you have in Excel.

You can find this nifty tool on the far right of your Home Ribbon. Simply click on Find and Select and choose Go To Special. Once there you can choose Comments, Constants, Formulas, Blanks, Current Region, as well as many other choices.

Now that your cells have been selected, you can perform whatever operation you may see fit. This simple tool can save you a lot of time searching through your worksheets, and can inevitably Help You Prevent Errors.

Go To Special; A useful tool to have in your Excel arsenal!

Excel 2013 Update!

Excel 2013 is part of the new Microsoft Office suite, of course, and many of us are eager to see the final version released. I have been using the beta version of the suite for several months, and you can count me in as one of the early adopters who is excited (and maybe a bit impatient) to get my hands on the Final Release.

Two Big Questions are Out There:

1. When will it be released?
2. How much will it cost?

As concerns the First Question, we do not as yet know with any absolute precision, but industry insiders now say it looks like it will be sometime in January. Although this may seem like a long time to wait for the update on our favorite spreadsheet software, kudos to Microsoft may be in order for waiting until they are more sure the bugs are out.

As concerns the Second Question, that information is here! We now know there will be Two Basic Approaches to acquiring the 2013 version of MS Office (and, of course Excel):

1. The Traditional Method of purchasing software: Locally-installed and with the license valid for just a single PC or Mac. The pricing will be similar to what is has been in the past: Office 2013 Home & Student will cost $139.99, Office 2013 Home & Business is $219.99, and Office 2013 Professional will retail for $399.99.

2. A newer Subscription Method: Alternatively, consumers can opt for the online subscription-based offerings. Office 365 Home Premium is $99.99 per year (or $8.33 per month), and Office 365 Small Business Premium is $149.99 per year per user (or $12.50 per month per user).

If you are like me, you may be adverse to paying for a Subscription, rather than having your own copy on your personal hard drive, but consider this: With Home Premium Subscription, the you also get Outlook, Access, and Publisher (not included with Office 2013 Home and Student edition), an additional 20GB of online file storage with SkyDrive, and 60 minutes per month of Skype calls. Plus, it can be Shared across Five different users and/or devices!

Whichever way you choose to acquire your Excel 2013 product, I am confident that you will find things to like in this latest rendition. If you haven’t yet experienced the beta, you can go to Microsoft.com to download a Free Trial version today.

Happy Halloween and Excelling!

Self-Expanding Charts

This Trick is a real Halloween Treat!

If you have ever created a Chart that needed Updating on a regular basis, you know what a bore it can be to do the frequent revisions. That sort of work may keep some people employed, but there are Better Ways to handle this task.

Building a Self-Expanding Chart is remarkably simple, and can Save You A Good Deal Of Time and boredom (and, after all, who needs more boredom!)

To create your Self-Expanding Chart in Excel 2007 or versions thereafter, here is what you do:

1. Create your Chart as usual ( you can use the sample data below)
2. Select any cell that contains the data that is used by the chart
3. Choose Insert / Tables group / Table to convert the range to a table

Amazingly that’s all there is to it! Excel will recognize the data as a Table and any data that is added to it is Automatically Updated in the Chart.

Give this Charting Trick a try, and find out why it is such a Treat!

Custom Date Formats

Although they are a major part of the grist which drives the Excel, handling Dates can be a bit tricky and are often misunderstood. The most common Date Formats are as follows:

•   m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
•   d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But suppose you want to create a Date Format that is Not included in the built-in list in Excel? No worries, here is what you do. Let’s assume that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example, the results will be a date format in the syntax: yyyy/dd/mmmm.

Controlling your Date Formats, even when they are Custom, is really quite easy in Excel. The payoff is Improved Communication. And that is, of course, The Bottom Line…

Find and Delete Rows

Who says you can’t teach an old dog new tricks? I just ran across this Fabulous way to select rows (or columns, cells, etc) in a database that contain a Key Word, and Delete Them All at Once.

Let’s say that you have a database or table that contains records you wish to delete. Furthermore, let’s say the records you wish to get rid of all have the word Excelite included in one of the cells. It may be possible to sort the database and delete the records, but this may not be convenient (or even impossible if the Key Word does not lead the string in a cell).

Here is What You Do:

1.   Choose any cell on the worksheet, and press Ctrl+F on your keyboard
2.   Type the Key Word (“Excelite” in our example) in textbox and click the Find All button
3.  Now Select an item in the list, and press Ctrl+A to select the entire list
4.   Go to the Home tab, click the down-arrow on Delete (in the Cells group on the ribbon), and then click Delete Sheet Rows
5.   Voile! Your unwanted records are history!

You will note when you click the down-arrow on Delete that you are also presented with options to:

• Delete Cells
• Delete Sheet Columns and
• Delete Sheet

These can also be useful, but you will probably find the Delete Sheet Rows to be the most handy. With the high cost of Energy, it’s always good to have an additional way to Save Some in Excel!

Happy Excelling!

Take Control with Your Ctrl Key

Some of us don’t take advantage of the humble Ctrl Key. It sits there on our keyboards in two different positions making it convenient for any of us to use it to Best Advantage.

So just what can you do with the Ctrl Key?   Many Cool Things; Here a Few…

1) Speedy Navigation: When you press Ctrl and any Arrow Key (up, down, left, or right), you Jump to the last populated cell in that direction. Zip! Just like that!

2) Fill the Contents and Format: Using the topmost cell of a selected range as reference, click Ctrl+D to Fill the Selected cells below. (An easy Crowd Pleaser…).

3) Simultaneous Data Entry: When you want to put the Same Information into multiple cells, all you need to do is Copy it, Select the cells you wish to paste it into, and use Ctrl+Enter to fill them simultaneously. This really Rocks!

4) Make Noncontiguous Selections: Select any cells you want, hold down the Ctrl key while you click on a cell or drag through a range of cells. As long as you hold down the Ctrl key, you can click and select Anything You Want on the spreadsheet.

5) Print Without the Mouse: Sometimes we still need to Print a worksheet. Rather than using the mouse, another simple Crowd Pleaser is to simply press Ctrl+P. Bamm! Ready to print!

There are many other Cool Ctrl Tricks that you can use to help you Control your worksheets, but I think you will find some of these discussed to be your Favorites.

Cheers!

The Intersect Operator!

This is an under-used Excel feature that (I’m guessing) you probably have never run across. Although there are several ways to look up information in Excel tables, the Intersect Operator is a very Convenient and Versatile way to do this. The whole idea is to use the vertical and horizontal ranges in a cross-tab Table or Database, and find the value at the Intersection.

The syntax is simply: = RangeName1 RangeName2 (Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =B2:B34 B10:H10), but that is a bit clumsy (and who needs Clumsy…).

A much better way is create Named Ranges. This can be easily done by Highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a dialogue box as shown below (Click OK). Very Cool!

Once you have your ranges Named, you can then (as with the example below) find a value with the elementary (my dear Watson) function: =East Quarter3

But That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))

Yes, there are other ways of doing this in Excel, but any Excel Geeks worth their pocket protectors should have the Intersect Operator in their bag of tricks.

All the best!

Removing Duplicate Records

Based on email that I receive, Removing Duplicates in databases is one of the most popular and useful topics for Real-Life Excel professionals. Being able to do this, enables a user to Combine Databases with repetitive data into a properly structured format. It is also a great Troubleshooting Tool for eliminating unwanted duplicate entries.

The really good news is that doing this with the last three versions of Excel (2007, 2010, and the beta 2013) is Remarkably Easy. Here is all you have to do to complete this simple task:

1. Select the entire database (be careful not to select any self-generating key fields)
2. Go to the Data tab in the Data Tools group and click Remove Duplicates
3. Select the column(s) on which you want to base your removal and click OK
4. A message will pop up telling you how many (if any) values were found and removed

There are always little twists that can pop up in Excel World, of course. One such instance was raised by a reader earlier today, where he had a database in which he wanted to remove Duplicate Records based on the Absolute Value of a field. In other words, he wished to remove any record where field values had the same absolute value but different sign, e.g. 7 & -7, 11 & -11, etc.

Although there are several ways to approach this, one Straightforward Method is to:

1. Add an Additional Field and use the ABS( ) Absolute Value function.
2. Then it is a Stress-Free procedure to use steps #2-4 above to weed out the unwanted data

At first glance Removing Duplicates to maintain the Integrity of your data may seem like a challenging endeavor. As is the case with nearly everything in Excel, however, once you know how to do it, it seems so Simple, you laugh…

Interactive Dashboards

A Parable:
There once was an Excel professional, (you may have known him or her), who would create wonderful, detailed reports that went on and on and on for many pages in the professional’s workbook. The professional’s work was impeccable. All the information was there, every bit of it! So why (why, why, why) did it Not get used and appreciated like it should?

The problem with this fine professional’s work was it was not Concise, Convenient, or Controllable.

The solution is to create what has become popularly known as a Dashboard. A key part of the definition of a Dashboard is that they can fit on a single computer screen so it can be monitored at a glance.

Dashboards can be linked to complex (or comparatively simple) formulas that work in the background gathering information from a centralized database. The formulas may be comprised of Database functions, Boolean arrays, IF functions, or several others.

Here are some Key Features you should strive for in your Dashboard:

1. The data is the Star, but keep in mind the other elements
2. Make it Interactive with dropdowns, spinners, scrollbars, etc.
3. Keep your audience in mind (it’s Their Dashboard…)
4. Don’t clutter the screen with unnecessary components
5. Make it attractive enough to hold your audience’s interest, but…
6. Don’t make it so stylized it muddles the message
7. Avoid 3-D charts (fancy, but 2-D tend to be more functional)
8. Experiment with the new Conditional Formatting features

A truly fine Excel professional will always make a Dashboard that is Concise, Convenient, and Controllable. It's all about Information, not just the data. A dashboard is only as valuable as the Information that can be obtained from it!

IS Functions!

As has been discussed in philosophy (and used by certain politicians), “It all depends on what your definition of “Is” is”. In this blog, however, we are going to look at some useful IS Functions, and how they are used.

Simply put, IS Functions check the type of value in each cell in a range, and returns TRUE or FALSE depending on the outcome. For example, the ISNUMBER function returns the logical value TRUE if value in a cell is number; otherwise it returns FALSE (if it is text or blank for instance).

IS functions have many uses in Excel, including Handling Errors in formulas and working in conjunction with Conditional Formatting to highlight various data. The following are 5 of My Favorites:

1. ISERR  -  Any error value except #N/A
2. ISERROR  -  Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3. ISNUMBER  -  Refers to a number
4. ISTEXT  -  Refers to text
5. ISBLANK  -  Refers to an empty cell

Although they can all be used in several ways, I find ISERR and ISERROR to be particularly useful in Handling Errors in formulas. For instance, ISERROR can be used in an IF function allowing you to return the value “0”, rather than a awkward “#N/A”.

The other IS Functions mentioned are highly utile when using Conditional Formatting to identify/highlight certain types of data.

As with so many things in Excel, the Key is to keep IS Functions in mind when working with your data, as you never know when they will just the right fit! (ISn’t that always the case?...).

Three Terrific Tricks!

In the 4+ years this blog has been published, three of the most popular Excel Tricks have been the following. They are Easily Mastered, and they can definitely make your life a bit easier in Excel World…

#1 Selecting Data or the Entire Worksheet
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 Copy the Formatting of a Cell or Range and Apply it to Another Cell or Range
Select the cell or range with the formatting you wish to copy, (Note: This works for Conditional Formatting as well), and click the Paint Brush on your toolbar. Your cursor will then turn into a brush that you can use to “Paint” any other cell or range with the formatting you have picked up from the previous cell or range (think of the selection as the Paint Can).

#3 Display Formulas So You can Troubleshoot Issues
This is so easy, you will laugh. Select any cell on cell on your worksheet and while holding down the Ctrl key, press “~” on your keyboard. Alacazam! All of your formulas will be visible!

Are these Simple Tricks off the Hook, or What! Take five minutes and give them a try!

Top Ten Keyboard Shortcuts for 2012

As any of you long-time readers of this blog know, every year or so, I like to take a look at what my Current Favorite Excel Keyboard Shortcuts are. This changes over time as I learn new tricks and incorporate them into my daily work with Excel.

Have no doubt about it, learning and using Keyboard Shortcuts in Excel will greatly enhance your speed and efficiency. Using shortcuts also will help relieve stress from overuse of the mouse, and will Set You Apart from Mere-Mortal Excel Users.

So, in Reverse Order, here are my current favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2012:

10.  CTRL+SHIFT+% (Applies the Percentage Format with no decimal places – Handy!)
 9.   CTRL+; (Enters the Current Date – I’m a time freak…)
 8.   ALT+F1 (Creates an Embedded Chart of the selected data – Far Out!)
 7.   Ctrl + H (Find and Replace – I wouldn’t want to live without it!)
 6.   CTRL+9 (Hides the selected rows – Tidies up your worksheet…)
 5.   F5 (Brings up the Go TO dialogue box – Great for navigating to a named range!)
 4.   SHIFT+TAB (Moves to the Previous Cell – I use it all of the time…)
 3.   Ctrl + Home (Brings you to the Start of the Worksheet – Oldie, but a Goodie!)
 2.   ALT+F4 (Closes Excel – Hey, you’ve got to knock off work sometime…)

And My Number One Favorite Shortcut for 2012 is (you may think this is a curious…):

 1.   CTRL+K (Displays the Insert Hyperlink dialog box for new hyperlinks – Very useful when making sophisticated, interactive workbooks!)

There you have it! Try a few, and see how they can make your Excel life Even Better!

The Efficacy of Goal Seek

After our stroll through one of the cool new features of Excel 2013 last week, I thought it would be good to take a look at a Classic Management Tool that has been available in Excel for several years.

"What If” scenarios are essential instruments of analysis for nearly any business. Excel provides a great tool called Goal Seek which allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. As with so many little-used tools in Excel, it can save you a ton of work, and make you look good in the process.

Now we will utilize a very Simple Example to explore how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the executive branch. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Assumption: The Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)

1) Put the number of Expected Calls into Cell B2
2) Insert the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

Obviously, this tool can be used to greater advantage with more complex scenarios, but you can see by our the example that Goal Seek can be a handy tool for the boardroom or any planning venue. Give it a try sometime!

Excel 2013 Quick Analysis Tool

Have no doubt about it – Excel 2013 brings an improved interface and several Really Cool New Tools!

One of my favorites is the Context-Sensitive Quick Analysis Tool. This great new gizmo makes it considerably easier to take the data in your worksheet and enable the creation of:

1. Charts
2. Tables
3. Pivot Tables
4. Sparklines
5. Conditional Formatting
6. Totals

All that you need to do is select a range of data, click the Quick Analysis icon that appears to the lower-right, and an easily navigated box of suggestions pops up giving access to all manner of analysis options.

The Quick Analysis tool is Truly Brilliant at allowing users to perform complicated tasks with a few quick taps. For instance, in the example below, the range from B3:D18 was selected, generating the Quick Analysis tool Icon that reveals the Major Categories of Formatting, Charts, Totals, Tables, and Sparklines. Clicking on any of the Major Categories gives you several options from which to choose.

The Quick Analysis Tool. One more reason to consider upgrading to the latest version of Excel. As I often tell my students, it is good to stay current with the latest versions of software, lest you wake up one day terribly behind the technology curve.

Multi-Tiered DropDown Lists

I am sure that we all agree that DropDown Boxes are highly useful when creating Interactive Reports. Having only one dropdown box in a report can be insufficient, however.

Let’s say that you have a large database with 60 different cities and 11 states. If you want to use the City Name in an Interactive DropDown List to pull up reports based on that city, you are faced with at least Two Potential Problems:

     • You are going to be faced with a Long List of City Names in your dropdown (60 in this case)
     • Many city names Occur in More Than One State (e.g. Dallas, TX & Dallas, GA or Glendale, CA & Glendale, AZ)

So wouldn’t it be Cool if you could build a Dropdown List of States that would generate a second Dropdown List of Cities for that state only? This way you could use the parameters of Both the State and the City to glean your information!

Here is How You Do This:

First of all, let’s assume you have a Horizontal Database containing your States and Cities in E2:N12, and your interactive cells set up in B2:C3 as per the illustration above. Note: The list of States would be in the first column of the database, E2:E12.

1. Select the table, E2:N12
     a. Go to Formulas / Defined Names and choose Create from Selection
     b. Assure that the check box with Left Column only is checked and click OK

2. Select B3 and create a dropdown box by using Validation
     a. Under “Allow” choose List and select $E$2:$E$12

3. Select C3 and create a dropdown box using Validation
     a. Under “Allow” choose List and insert the formula =INDIRECT($B$3)
     b. Click OK (Click Yes if you get an error alert…)

4. Now for some cleaning up…
     a. Select, F2:N12 (Note: Do Not include Column E)
     b. Go to the Home tab and select Find & Select from the right-hand side of the ribbon
     c. Select Go to Special and choose Blanks and click OK
     d. Right-click the selected area and Delete / Shift Cells Left

That’s It! This really isn’t very difficult, and the resulting Multi-Tiered DropDown Lists are incredibly useful when setting up Sophisticated Interactive Reports.

Have no doubt about it, people will think you are a Genius when you show them this stunning technique. Give it a Try!

Traveling with Excel

With the Internet and ever-expanding jet travel, it is axiomatic to say that we are in an “Ever-Shrinking World”. National Currencies have frequent fluctuations, of course, and our friend Excel Can Help!

So, let’s say you have scheduled a business/pleasure trip to Japan, and you would like to download the most up-to-date currency exchange rates directly into the Excel workbook you are using for your trip planning. It Couldn’t Be Easier!

If You are Using Excel 2007 or Excel 2010:

1) Select cell A1
2) Go to Data / Get External Data / Existing Connections and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

If You are Using the Older (but still serviceable) Excel 2003:

1) Select cell A1
2) Go to Data / Import External Data / Import Data and choose MSN MoneyCentral Investor Currency Rates
3) Click OK

Presto! In a few seconds, you will have the exchange rates for Australian Dollars to Venezuelan Bolivars!

Okay, You World Traveler, You – Is that Cool or What? I see that today you can get 78 Japanese Yens for one US Dollar (sounds like a bargain to me…)!

Excel 2013 Preview is Here!

The Excel 2013 Preview is now ready for download, and it is Pretty Exciting! While presenting an updated, but familiar interface, Microsoft has made several improvements to our favorite spreadsheet program.

What strikes me as particularly imaginative in this new version is the advances in Excel’s ability to Predict what the user wants to do. Based on your data, Excel 2013 will recommend Charts, Pivot Tables, or a Fill for a range (called “Flash Fill”). Excel learns from the patterns in your work and makes intelligent and time-saving suggestions.

The new Quick Analysis tool that appears to the lower-right of a selected range is a tremendous boon to any power user. Symbols or colors help you analyze data in just a click, as Excel 2013 uses these elements to identify and highlight trends and changes. I really like this cool new tool, as it can immediately add Pizazz and (Gasp!) comprehension to your worksheets.

Microsoft has truly leveraged the Internet this time. Workbooks are by default saved on SkyDrive (Free!) or SharePoint. You can send a link to anyone, along with editing permissions if you wish. You can even select portions of your work and share it on Facebook or other social networks!

Although I haven’t had the new software long enough to explore All of its many new features, I can unguardedly say that this innovative new version really Rocks the Casbah! Go to Microsoft.com to download a free trial version today.

A Deeper Look at Trendlines

Trendlines in charts are essential when it comes to Viewing the Big Picture. The overall trend of your data is valuable information that may not be readily discernible. Adding a Trendline can make all the difference in the communicative value of your chart.

First of all, let’s review how to add one to a chart. To add a Trendline, simply right-click on the Data Series Line on your chart, and choose Add Trendline from the dropdown list. This will immediately insert a Trendline on your chart.

But Which Trendline Should You Use?

Aye, that is the question! Trendlines come in several different flavors. The key is to choose the one which most reflects the, well, Trend of your data (that may or may not be obvious…). This can be ascertained by determining the R-squared value of the various trendlines. You can easily do this by checking the Add R-squared value to chart box in the Trendline Options area.

To Help you Choose the Best Fit, the Following is a Concise Description of the Various Trendlines:

Linear Trendlines
For simple linear data sets, a linear trendline is a best-fit straight line. Note: Your data is linear if the pattern in its data points approximates a line, and typically shows that something is increasing or decreasing at a steady rate.

Logarithmic Trendlines
A logarithmic trendline that describes a curved line that is used when the rate of change in the data increases or decreases quickly and then levels out.

Exponential Trendlines
An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing rates.

Power Trendlines
Power trendlines are curved lines typically used with data sets that compare measurements that increase at a specific rate. Often used in examples of acceleration.

Polynomial Trendlines
This is a bit of a different animal. A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. Though more challenging to use, it certainly has a place in your Excel Tool Belt.

When you know how to use the easily-mastered Trendlines, a world of quick Excel analysis opens before your eyes. Gotta love it!

It is Good to have Options

Greetings All! I hope you have had a great week so far!

In this post I thought it was a good time to “Walk on the Wild Side” a little bit, and look at the (surprisingly easy) use of Option Buttons.

Option Buttons have several applications, and can be especially useful in creating Quizzes or Polls in Excel. There are a number of steps, but I guarantee that it will be well worth it if you hang in there and give them a try!

Here is how you do it:

1. Make sure the Developer tab is visible on your toolbar (available in Excel Options)
2. Under the Developer tab, choose Insert / Option Button (Form Control)
3. Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices
4. Now, (this is Very Important), to make the buttons work together, return to your Insert dropdown and choose Group Box (Form Control)
5. Then draw your Group Box all the way around your Option Buttons

Just Three More Steps and We’re There!

6. Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate
7. Now is where the Fun really begins. Create a Formula that is based on the value that is shown in the Cell Link
8. For example, let’s say you have linked three Option Buttons to cell $A$1 and you wanted Option 3 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell A2, type =IF(A1=0,"", IF(A1=3, "Correct!", "Incorrect"))

Now when the user chooses Option 3 of the three possible, they are rewarded with the “Correct” feedback. Option Buttons, give them a try; It is always good to have options…

Intersections

This is a Really Cool Trick that you probably have never run across! The concept is known as Range Intersection, and identifies the value in the cell that two ranges have in common.

The syntax is remarkably effortless: =Range1 Range2

You simply state the two ranges in the formula separated by a Space (Important Note: No commas, semicolons, etc, Just a Space).

Where using a Range Intersection becomes particularly powerful is when you use Named Ranges in your table. Using the following table for an example, I have named the columns in accordance with their Headers (Quarter1, Quarter2, etc) and the rows according to the State in Column A (California, Minnesota, etc).

The formula, =Minnesota Quarter2, was then put into cell B7 and the result, 1,420, was returned. This is easily verified by identifying the Intersecting Cell of the two ranges: C3.

Using the Range Intersection tool is an elegant way to select values in a Cross-Tab table. Just another way to accomplish a daily task with our favorite software. Cheers!

Customize Your Date Formats

Most Excel users are familiar with the mm/dd/yyyy date format. It can, however, often be very convenient to use Custom Date Formats. It is surprisingly easy to set up custom formats on your worksheet, and this week’s blog will illustrate several highly practical and useful formats.

To customize the Date Formats of your cells, simply do the following:

1. Right-click the cell or range, and choose Format Cells from the dropdown
2. Choose Custom under the Category
3. Type in your Format Code in the Type dialog box

Using today’s date of June 20, 2012 as an example, I have created the following table that exemplifies this cool technique. Please Note the Formula in the Formula Box ( =TEXT($A$1, A4 ) which references cell A1 and the Format chosen:

By using Custom Date Formats, you can modify how Excel displays your dates, and maybe make your life just a little bit easier. Give it a try sometime! It really is easy…

The Any-Version Anti-Chart

Excel 2010 (and the next Excel generation rumored to be released late this year) has brought new ways of providing Visual Displays of data other than traditional charts. This is a true advance, since creating a dynamic traditional chart can be a bit of a hassle at times.

But what if you are using, (or your audience is using), earlier versions of Excel such as 2003 or 2007? Is there a Cool Way to provide a Visual Display of the data without using a conventional chart?

Yes, There Is! Using the little-know and seldom used REPT function, you can produce an Anti-Chart that can provide you with a display that is immediately understood, dynamic, and easy to create. The REPT function repeats a text string the number of times you specify. For instance, the following formulas returns Nine Asterisks: =REPT(“*”, 9)

The cool thing about this innovative, yet simple technique is that it works in Any Excel Version. You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.

Let’s say that you have a table with Months in Column A, Sales in Column B, and we want our Bar Chart (we’ll call it an Anti-Chart, since we are not using Excel’s typical charting capabilities) in Column C. We can then insert the following function in our Chart Column C: =REPT(“*”, B2/1,000):

You will note that we are dividing the number in B2 by 1,000 is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell C2 and drag it down to complete your visual illustration of your data. Bamm! Anti-Chart!

Cheers!

Hyperlink Coolness!

Navigating via Hyperlinks is a way of life for us on the Internet. What many users do not know (or at least do not take advantage of) is that you can use this feature within an Excel workbook to add both Functionality and Coolness!

By using Hyperlinks in a worksheet, the user can instantly access another area in the workbook, another relevant workbook or application, or a place on the web. You can insert a hyperlink into a cell or a Shape in any version of Excel.

Inserting a Hyperlink into your workbook is, as are most things in Excel when you know how, Super Easy. In any Excel version of the past 10 years, simply choose the cell that you want to put the link into, and Right-Click / Hyperlink. Then click on the appropriate Link-to area in the left-hand column, and complete the address information. Bamm! You have your Link!

If you want to make your worksheet Even Cooler, however, you can quite easily draw a shape like a Button for your link. Here is what you do:

1) First get rid of all of your extraneous gridlines by going to the View ribbon and Uncheck Gridlines.

2) Go to the Insert ribbon, click on Shapes, choose whatever shape makes you happy (and makes your spreadsheet distinctive), draw it onto your worksheet and format it to your specifications.

3) Then Right-Click / Hyperlink, (It bears repeating that “Right-Click is Our Friend”, as it gives you Context Sensitive dropdown choices), and finish the link in the same way we discussed earlier this week.

By using Hyperlinks, you can aid the ease of navigation in your Excel workbook by mimicking website design and, perhaps as importantly, you can look like the Excel Rock Star you really are!

Insert Function Wizard Revisited

Occasionally I like to revisit the topics that the readers of this blog have found the most interesting in times past. Over 2 years ago, we discussed the Insert Function Wizard, and it has become one of the most popular subjects that has been addressed in the past 4 years.

Whichever version of Excel you may be using, the Insert Function Wizard can be a very helpful in performing a bit of Magic when you need it, )and who among us can’t use a little “Magic” now and then?)

Let’s face it, there are times when you may think that Excel has a Function that you can use in a formula, but you just don’t know what it is called or how to use it. You can describe it to another user, but what Excel calls it is simply a mystery. The solution is (a drum roll and an Alacazam here…) the Function Wizard!

Where Is It?
To find the wizard, to Insert Function under Formulas if you are using Excel 2007 or 2010, and type a brief description of what you want to do. For instance, let’s say you want to calculate what your Loan Payments are going to be when you buy the new GuruMobile you have had your eye on.

How Do You Use It?
1) Pull up the Insert Function Wizard
2) Type your description in the search textbox (in this case, “loan payment”)
3) Select the function (in this instance it would be PMT)
4) Complete the Function Arguments and click OK

Magic
Alacazam! You have your specialized function for your formula! Give it a try and find out how easy it is to perform a bit of Magic when you need it.

Excel on iPad?

As the business world is becoming ever more aware of the value of getting Real Work done on a tablet, it is high time we take another look at doing spreadsheets on the increasingly ubiquitous iPad. With utmost respect to Android-based tablet users, (Documents to Go is outstanding on these fine devices, but that is another story unto its own), we are just going to be looking at the world of Apple in today's blog.

The Apple spreadsheet application, Numbers, is a truly capable workhorse which can abide quite nicely with Excel. For anyone who has wrestled with a laptop on a long plane trip, or simply didn't want to tote their 7-pound monster to a quick business stop, you can understand the beauty of being productive on an iPad.

It is true, of course, that not all Excel’s features are available when working on an iPad (for instance, it has about half as many functions). Unless you are getting into more uncommon tasks, however, it is more than sufficient, and there is something very satisfying about being flopped on a sofa and still being able to work on a spreadsheet. Not only that, but as Apple continues to refine their software, the possibilities continue to grow.

Compatibility

So, what about compatibility with Excel you ask? Obviously, since the world revolves around Excel, (or at least that’s what we Excel fans think…), you want to be able to import/export between Numbers and Excel the good news is, of course, that you can, subject restraints on such things as Pivot Tables (no such animal in Numbers).

Navigating/Viewing

Although it is a bit foreign at first, tapping to select cells and using the convenient selection handles to choose a range becomes second-nature quite quickly. The now commonly known pinching gestures zoom you in or out on your data and charts, and it is easy to get hooked on these new ways of getting around a spreadsheet.

Sharing Your Work

It is, of course, always good to have options for sharing your work. Once you have created your spreadsheet masterpiece on your iPad tool, you can easily email it in its original Apple format, a PDF or, of course, as an Excel document.

Although it may not be for everyone, you may find this novel way of interfacing with your data a pleasant and productive alternative way of working with your Excel creations. Cheers!

Debugging Techniques

There are times in any Excel user's life when you're just not sure what is going on behind the scenes in your workbook. In this edition of Excel Enthusiasts, we are going to take a look at two fundamental, but Vital Techniques for investigation of this nature.

1)  Are there Formulas in My Workbook?

Rather than clicking on each cell to reveal the formula in the Formula Bar, (as you needed to do in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

This simple keyboard combination unveils the formulas in All of your cells, from which you can then determine if there is a flaw in their construction.

2)   What If I Need to do an In-Depth Review of Precedents and Dependents?

If you need More Power in seeing what your data is doing and where it comes from, look no further than the Formula Auditing group on the Formulas tab. There you will find Trace Precedents, Trace Dependents, and even Show Formulas. The information is returned in easily-understood graphics that help you make sense of the workbook you inherited from old what’s-his-name.

These two Fundamental Tools can make your Excel life a little easier. (And who wouldn’t want that?...)