4 IFs

Happy Boxing Day All! 

“IF” Functions are some of the most useful and easy to use tools in Excel. There are several flavors of IF to choose from, and each of them can aid you in making sense of your data. Here are my 4 Favorite Ifs:

 1.  COUNTIF: =COUNTIF(Range, Criteria) 
For the Criteria, you can specify a cell, a simple number such as 10, or use a greater-than or less-than criteria such as “>20”. Be sure to use the quotation marks if doing a great-than or less-than analysis. Alternatively, you can refer to a cell that contains your criteria number (No quotation marks needed with that approach).

 2.  SUMIF: =SUMIF(Range, Criteria, SumRange) 
The Criteria is the same as in COUNTIF, but you have the option of applying it to a “SumRange”. These are the actual cells to add if the cells in the Range match the criteria. If SumRange is omitted, the cells in Range are evaluated by criteria and added accordingly.

 3.  IF: =IF(Condition, Value If True, Value If False) 
This function is extremely valuable as a stand-alone or in conjunction with other “nested” functions. You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. IF(A1>10, “Pass”, “Fail”). Just be sure to use quotation marks if inserting actual words. 

4.   IFERROR: =IFERROR(Value, Value If Error) 
Introduced for the first time in Excel 2007, this combines the IF and ISERROR functions, and essentially gives you greater control with potential Error Messages. Rather than just accepting the sometimes cryptic and less-than-helpful default error messages that Excel dishes out, you can Customize your error messages for more clarity.

 No IFs about it, IF functions are another great set of tools for Excel arsenal. Happy Holidays, All!

Layout Best Practices

Back to basics today, and a look at some Leading Best Practices for organizing your data and laying out your Excel workbooks. Although I hesitate to say that any of these guidelines are Absolute Rules, if you consistently follow these practices, you will find you can Do More with Less Effort.

Data on One Worksheet; Information on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports in another worksheet. The fewer the worksheets you have, the easier it will be for your users (including yourself, of course) to navigate and glean the information you are creating.

No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity. 

Deconstruct Your Data 
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 if you find it necessary).

Format Your Headings 
Use a bold font when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.

Sort Your Data 
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.

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.

 If you follow these guidelines, you will easily be able to use the powerful built-in features that require organized data and a logical layout. Organization = Simplification = Information.

 I wish you the best of times during the holiday season! ~Bob

Your New Default Template

As most all of know, the Default number format in Excel is General. There is nothing wrong with that, of course, but if you find that you a regularly doing work which requires a different format, you can Save Time (and a little frustration, perhaps) by changing the Number Format in the Normal style.

Let’s say you work in an accounting department and, for the sake of formality, you prefer to use the Currency option for formatting your worksheets. You can set this up manually each time, or you can utilize the convenient Templates feature found in Excel, as well as other MS Office applications.

Here is the Suggested Approach for Our Accounting Professional:

1.   Open a blank worksheet in Excel

2.  Go to the Styles gallery on you Home ribbon

3.  Right-click the Normal style and choose Modify to display the Style options

4.  Click the large Format button, and make the new formatting choices you desire

Save as a Reusable Template

1.  Once your number (as well as any other changes) format changes are made, simply click on the File tab and choose Save As.

2.  Type in the File name of your choice (e.g. AccountingTemplate1) and for the Save as type, choose Excel Template (*.xltx)

Now whenever you wish to create a new workbook, your new AccountingTemplate1 will be readily available under PERSONAL templates when you open Excel.

You will be Saving Time every time you set up a new workbook of this type; and How Cool is That!

Staying Current with Currency

It can be interesting to see which Excel topics are the most popular over a span of years. Perhaps ever-increasing globalization accounts for the attention paid to downloading Currency Rates into Excel. This topic alone has gotten over 2,000 hits on this humble site in the last two years.

Excel 2013 Update:

Somewhat curiously, however, it is not quite as easy to access and download the MSN Money Central currency data with Excel 2013 as it was with earlier versions. Although that is the case, a Few Easy Steps is all it takes to import this information into the current Excel version. Here is

What You Do in Excel 2013:

1.   Open a new workbook and select cell A1

2.  Access the Data ribbon, go to Get External Data, and click on From Web

3.   Paste the following link into the Address textbox: http://moneycentral.msn.com/investor/external/excel/rates.asp

4.  When you click on the Import button in the lower right, you get the following dialogue box:

5.   By clicking on the Properties… button in the lower-left, you can choose to Save Query Definition and Refresh every 60 minutes (or however long you choose…)

6.  Click OK

Now you will get the Current Currency Rates whenever you open this workbook. Pretty Cool, eh? Let’s see, as of this writing, you can get approximately 62 Indian Rupees for 1 US Dollar.

  Sounds like it is time to do a little traveling!

A Shortcut to Your Shortcuts

Even if you read this blog only occasionally, you know that I am a Huge Fan of Keyboard Shortcuts. There is no doubt that using keyboard shortcuts makes you more Efficient and Productive (particularly true on larger displays). An additional benefits is that these shortcuts also save stress on your hand and wrist.

The Problem is that Keyboard Shortcuts are hard to remember. Of course you can use a Cheat Sheet, run drills, or regularly devote a small amount of time to learning these wonderful tools. This will be time well spent, as the efficiently gained will pay dividends for years to come.

An alternative way to use these Efficiency Gems is to use a Special Trick that most Excel users are not aware of. Here is What You Do:

1.  Simply Select any Cell or Range on a worksheet without activating the cursor

2.  Press the Forward Slash key on your keyboard or number pad, and Presto…

3.  Letter Labels will magically appear across your toolbar (e.g. ‘F’ for File, ‘H’ for Home, ‘N’ for Insert, etc)

4.  Type the letter that corresponds to the Ribbon you wish to view and Bamm, you will be taken to the ribbon of your choice which will also have Labels that you can choose with your keyboard!

This is a remarkably easy and effective way to get in step with using Keyboard Shortcuts. It is, in effect, a Shortcut to Your Shortcuts!

I hope you have a Safe and Truly Wonderful Thanksgiving!  ~Bob

Customizing the Quick Access Toolbar

Like many useful features in Excel, the Quick Access Toolbar is often overlooked by even the savviest Excel users. This handy feature appears above the ribbon on the left and by default includes the commands, Save, Undo, and Redo.

While that is nice, So Much More can be added to this convenient locale.

By clicking the dropdown arrow on the Quick Access Toolbar, you can easily add commonly used commands such as Sort, Quick Print, Email, etc.

Don’t Stop There, however! After accessing the dropdown menu, click on More Commands (near the bottom). Excel will then present an Excel Options dialogue box that gives you hundreds of choices that you can add (Caution: It is easy to get carried away…).

1.   Choose the type of command you want to add from the Choose Commands From list. Popular Commands is the default, but you can also have other options.

2.  Select the command you want to add to your newly enhanced toolbar and click the Add button. The command button will then appear on the list on the right.

3.  Then use the Move Up and Move Down arrows to reorder buttons on the Quick Access Toolbar.

4.   Click OK

The result will be an Extremely Convenient customized toolbar that you will likely find to be one of your Favorite Excel features!

Double-Click: The Big List

Although I am a fan of using Keyboard Shortcuts, the Mouse also offers unique opportunities to augment your life on your worksheets. One way to enhance your maneuverability in Excel is to master the art of Double-Clicking. 

 Here are 10 Amazing Ways to Use Double-Click: 

 1. Perfectly Adjust Column Widths – Just select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too.

 2. Auto-Fill a Series of Cells with Data or Formulas - Just select the formula in first cell, Double-Click in the “handle” (small Black Square in bottom-right-corner) and Presto! This works for formulas, auto-fills (of numbers, dates, etc) as long as the adjacent column has data.

 3. Rename a Worksheet Tab: Double-clicking the tab allows you to immediately edit the name.

 4. Insert a Split - Double-Click just above scroll-bar to include a horizontal split; Works for a vertical split too, by clicking on the little bar shape next to the right of horizontal scroll-bar.

 5. Edit a Cell Formula: Double-click any cell to edit its contents in place (rather than using the formula bar.)

 6. Close Excel 2007 (only) – Simply Double-Click the Office Button.

 7. Collapse Ribbon to Get More Space – I like this one. Just Double-Click on ribbon Menu Names.

 8. Lock Format Painter – Save a Ton of Time by Double-Clicking on the Format Painter icon, making it Reusable. (So Cool!..)

 9. Jump to Last Row / Column in Table – Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

 10. Pivot Table Drill-Down: Double-click on any data value within a pivot table to instantly create a new worksheet which will show the Underlying Records that comprise that value.

 Double-Clicking: Demonstrate your Mastery of Excel with this Important Tool!

Summarizing Your Data

Summarizing your data is essential for providing and maintaining Information for your business. Let’s say that you have a database of your Sales by State. The Database may contain thousands of records, and it would quite probably be interesting to all of the stakeholders of this data to see a Summary of the Number of your Sales by each State.

This is, of course, not difficult to do, but a review of a couple of Key Techniques is always a good thing. The Keys in this instance are:

1.  Naming Your Ranges
2.  Using the COUNTIF Function

First of all, Name the Range that contains the State. You can do this by selecting the range in your database, (including blank cells below for future growth), and typing the Name of the Range in the Name Box in the upper-left-corner of your worksheet. In this example, we will assume you have named it “State” (Oh, these clever Americans…).

Then in your Report Table (as in our example above), you can list the States that are included in the database. Assuming your first entry is in A2, put in the following formula in the first adjacent cell:

 =COUNTIF(State, A2) 

Then just copy the formula down next to complete your report table and, Presto! You have a Summary of the Number of Sales by State! 

Using these Simple Techniques can quickly give you the Information you are looking for. Give it a try!

The Count on Halloween

Happy Halloween All!

The COUNTIF function is an Extremely Useful, and sometimes misunderstood, way to derive valuable information from your data.

COUNTIF is a highly flexible counting function and, although it can be a little Tricky, getting familiar with its use can be a real Treat!

The reason this function can be challenging to some users is because of the prevalent use of Quotation Marks within the formulas. The following examples will demonstrate how it works (in each case, the formula uses a range named Data):

Count the number of cells that contain the first name of Dracula - "Vlad" (BTW, not case sensitive):
=COUNTIF(Data,"Vlad")

Count the number of cells that contain the words "Vlad" and “Igor”:
=COUNTIF(Data,"Vlad") + COUNTIF(Data,"Igor")

Count the number of cells containing Any Text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 3-letter words:
=COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "V":
=COUNTIF(Data, “V*”)

Count the number of cells that contain a value Greater Than Or Equal to 13:
=COUNTIF(Data,">=13")

Count the number of cells that contain a value from 13 to 33:
=COUNTIF(Data,">=13")-COUNTIF(data,">33")

With a little imagination, COUNTIF can indeed be a Treat to use. So, this Halloween, Don’t forget the Count!

Better Charts

If you read this blog with any regularity, you know that the construction of Better Charts is of keen interest to me. It should be to you, as well. Charts, after all, are the Best Communicators of information that any Excel user has in his or her arsenal. Charts Visually Convey your data in the most quickly comprehended (if done well) manner.

It is, therefore, worth any Excel Guru’s time to assure that they are created with skill and insight. Of course, no one has any time to waste on frivolous enhancements, so how can you get the most Value for your Chart-Improvement Efforts?

Here is a Quick, 2-Minute Drill for Polishing Your Charts: 

1.  Chart Type: Quickly scan the types of charts available and consider which (it may not be the one “that we always use”…)

2.  Legends seldom add any additional information to a well-constructed chart. Assure that the chart is communicating well and then Right-click and Delete the Legend! 

3.  Gridlines tend to Clutter your visual information. Consider if that is the case, then Right-Click and Delete the Gridlines!

4.  Rounded Corners can add a bit of Bravura to your chart and set it apart from the mundane. Go to Format Chart Area, select Border Styles, and put a check mark next to Rounded Corners.

5.  Formatting:  Add a little Zest to your charts by Formatting Your Plot Area. Right-Click and choose a Gradient Fill that adds a touch of Finesse while maintaining a professional look.

Taking a couple of extra minutes can Separate Your Work from the commonplace charts that we all see too often. A mere 2 minutes effort can Make Them Shine!

Formatting Keyboard Shortcuts

Greetings Excel Enthusiasts! I had a big Excel class of 54 professionals start this week at Continuing Education Group (CEG), so I have been a busy boy.

If you read this blog regularly, you know that I’m a huge fan of Keyboard Shortcuts. Over time, these shortcuts can save you literally hours of work time (and even improve your image as the resident Excel Guru…).

We all need to Change the Number Format in a cell or range of cells on a regular basis. Right-clicking and pulling up a context-sensitive menu is a good solution, but using the keyboard is Even Faster!

The shortcuts all start with “Ctrl+Shift”, so they are pretty easy to remember. The following are the most commonly used tricks for quick number formatting:

1.   General = Ctrl+Shift+~

2.   Number = = Ctrl+Shift+!

3.   Time = Ctrl+Shift+@

4.   Date = Ctrl+Shift+#

5.   Currency = Ctrl+Shift+$

6.   Percentage = Ctrl+Shift+%

Although it may take a little time to become fully accustomed to using these shortcuts, the time invested will certainly be worth it in the long run. They may even Free up some time for a Vacation!

Cheers!

Picture Charts Revisited…

This is a topic that is So Cool, it deserves another look! As we all know, Excel provides a great many Chart Types to choose from. But is that all there is? What if I want to make my chart really Stand Out?

Picture Charts to the rescue! You can Add a Lot of Zing to your charts with a very little used, (but Uber-Cool), trick. You can accomplish this cure to apathetic charts is by Replacing the Series Element (use Columns or Bars as your base chart, as they tend to work the best) with your own Graphic.

You can easily Add Vitality to your charts, and once again establish yourself as The Excel Guru, by doing the following:

1. Create a simple, no-background graphic

2. Copy the Graphic to your clipboard

3. Create your Chart using columns or bars (recommended)

4. Select the Chart Column or Bar Series

5. Go to your Home Tab and Paste your custom image

6. For Extra Impact, Format the Data Series by going to Series Options and choosing 0% Gap Width

Living in Southern California now, I chose to illustrate this technique with a Surfboard Graphic for the illustration above. To further demonstrate the difference this can make, I did a Side-by-Side illustration below. I chose a Bag of Money to replace the Boring, Boring, Boring column to depict the sales by month.

Picture Charts are another Cool Trick that can make your work get noticed. Another way to Add Interest and Inspiration to your Excel worksheets.

Try it out once and I think you will be hooked!

Concise, Convenient, and Controllable

A Sad Story 
Once upon a time in Corporateville, there was a hard-working, but underappreciated Excel professional. This fine Excel pro would create wonderful, detailed reports that went on and on in glorious detail for many pages in this professional’s workbook report. The professional’s work was of the highest caliber, absolutely impeccable. All the information was there, every last bit of it! So why then (Why Oh Why) did the stakeholders not make Good Use of all of the fine information?

The Answer 
The reason is really very simple: This fine professional’s work was not provided in a Concise, Convenient, or Controllable format.

The Solution 
The solution is to create what is typically known as a Dashboard. The Key Features in a Dashboard make it user-friendly and fit on a single computer screen. The user should be able to monitor and understand it at a glance.

Dashboards
Though user-friendly and simple in appearance, 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 Six Key Features You Should Strive for in your Dashboard: 

 1.  Although the data is the Star, one should keep in mind other elements

 2.  It should be Interactive with drop-downs, spinners, scroll bars, etc

 3.  Make it engaging so it will hold your audience’s interest, but…

 4.  Do not make it so stylized it muddles the message

 5.  Avoid 3-D charts (elegant, but 2-D versions are more functional)

 6.  Consider using some of the new Conditional Formatting features where desirable

When properly constructed, a Dashboard is Concise, Convenient, and Controllable. It is all about providing the stakeholders with Information, not just data. A dashboard should make the user’s life noticeably easier (and bring you the Appreciation you deserve!)

Excel Lists for Email

This topic came up again in one of my Excel classes this week and, considering how incredibly useful it can be to so many of us, I thought I would share it with you in this post.

Whether they are employees, clients, or vendors, Lists of Names of people are often kept in Excel. There are times, of course, when you may want to send an email via Outlook to a group of folks that you have in one of these Excel databases.

Outlook and some other email systems requires that the names be entered in a Last Name/Comma/First Name format. So what do you do if that is not the way your Excel list is arranged?

Let’s say you have a list of employees with the First Names in column A and Last Names in column B. Combining them into an Email-Friendly column of names in a “Last Name, First Name” format is so easy you’ll laugh. Here is what you do…

Let’s assume your table starts in cell A1. Using the magic of Concatenation, (there’s a word for yah!), put the following formula in C1:

=B1&", "&A1

Note: Be sure to use the quotation marks and insert a space after the comma.

This formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Drag the formula down to fill Column C, and then copy and paste it into Outlook.

Bamm! Your Outlook Mailing List is Ready to Go!

INDEX + MATCH = INFORMATION

There are many ways to look up information in tables and databases. Excel provides us with functions such as VLOOKUP, DSUM, DGET, SUMPRODUCT, and several others. There are pluses and minuses when working with any of these tools, but in certain circumstances one or more of them can be an excellent solution. It’s all about returning Information in the way you want it.

Flexibility is always a plus, of course. When it comes to Versatility, the combination of using INDEX and MATCH can be hard to beat! This is especially true when comparing these powerful tools with the decidedly more limited VLOOKUP and HLOOKUP functions.

 For instance, using this combination alleviates the need to stay locked into the first column for a reference point. As I discussed in this blog in years past, a simple example goes a long way in illustrating this piece of Excel Magic:

 INDEX and MATCH can serve as your able-bodied servants. Let’s take a look at a simple example using the Illustration above.

 For instance, MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

 =MATCH("Tampa", $A$2:$A$8,0) 

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula: 

=INDEX($A$2:$A$6,4) 

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

 =INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0)) 

Using the INDEX and MATCH functions together enables the crafty Excel Guru to extract information from a table or database of nearly any size or dimension. It gives you the ability to sidestep some of the shortcomings of other techniques, and lets you Have it Your Way!

Indirectly to the Point

Using Named Ranges in your work in Excel is a highly recommended Best Practice. A problem may arise when trying to refer a Named Range, however. The solution is mastering the easy-to-use Indirect Function! 

If you read this blog occasionally, you know that I am big fan of Interactive Reports. Using the Indirect Function, along with drop-down boxes created with Validation, is another very cool way of making reports of this type.

 As I discussed in this blog several years ago, there are many uses for the Indirect Function. Used in its simplest form it simply take the Name in the cell it is referencing and returns the Named Range that it refers to.

 Try Creating a Small Spreadsheet Similar to the Graphic Above, and Try the Following: 

 1) Create your Named Ranges by selecting each of your ranges and typing the new Name in the Name Box on the upper-left corner of your worksheet. In the above example graphic, Name each of the monthly rows (excluding the cells with the month name) for the Units shown. For instance, for August, select C12:F12 and name it August (clever name, eh?).

 2) Then go to Data Validation in Tools and choose Allow List (choose the list of items that you want to appear in the drop-down box). For example, in the above graphic, the cell C2 with “April” in it contains the interactive drop-down created using the list of months.

 3) Now for the Good Stuff! In cell D2 insert the formula“=SUM(INDIRECT(C2))”. The Indirect Function reads the name of the month you choose with the drop-down box in C2 and the formula sums up the total for the units.

 Using the Indirect Function, along with Validation, is an easy way to make a truly powerful interactive report. How Cool is That!

Spreadsheets: The Big Three

Spreadsheet applications have been around for quite a long time and, as is typical of technical evolution, the strong have survived while the weak fell away into obscurity. Currently, the Big Three are Apple Numbers, Google Sheets and, of course, Microsoft Excel.

 As is the case in most categories, it is no surprise that Excel is the King (or Queen if you prefer) when it comes to Functions. In addition to other tools, Functions, arguably, Make Spreadsheets Go. It is therefore interesting to take a quick look at the major applications in this regard.

 Apple Numbers 
There is no doubt that Apple’s Numbers is pretty to look at and does a great job on charts and graphics. The software also claims to make using functions and formulas less complicated through the use of an “intuitive” browser. Approximately 255 functions are available as is built-in help and the ubiquitous tooltips. Newly added functions include DATEVALUE, NETWORKDAYS, YEARFRAC, and several more.

Google Sheets 
Google Sheets is a free online spreadsheet application that lets you simultaneously work with other people. As with all of the major spreadsheet apps, it has substantially evolved over time and currently has approximately 315 functions to choose from. You can even Chat in real time with others who are editing the functions in your spreadsheet, and do most of the work that professionals require in this arena.

Microsoft Excel 
It is no surprise that Excel leads the industry with over 400 functions. It is doubtful that anyone in the foreseeable future will surpass the sheer depth of functionality that Excel enjoys. For instance it is particularly notable for many professionals that no other spreadsheet software includes anything like Pivot Tables. Although there is specialized number-crunching software, Microsoft continues to do an admirable job in updating and refining its crowning application.

Which one to use? Well, that depends on who you work with and what your needs are. When in doubt, however, you can’t go wrong with Good Ol’ Excel…

Handling Duplicate Data

Handling Duplicate Data is well-known to be a Vexing Problem for many Excel users. 

There are tools within Excel that simply delete all duplicate data, but there are times when it is more advisable to Identify these Duplicate Values prior to eliminating them.

For instance, let's say you are working with Combined and Blended human resources data that lists employees with their home addresses. If the information in the blended data includes Duplicates of employees who have had updates, and shows different addresses for these duplicates, it would be good to have an Expedient Way of doing identifying them before taking action.

Conditional Formatting can offer an excellent solution:

1.   Select the range (e.g. A1:A98).

2.   Choose Format / Conditional Formatting to display the CF dialog box

3.   Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$A$98,A1)>1 in the second box

4.   Click the Format button to bring up the Format Cells dialog box.

5.   Select the Patterns tab, and choose a background color

6.   Click OK twice to return to your worksheet

Bamm! If the range contains any duplicate entries, they will be highlighted with the background color you chose previously!

Removing Duplicates in databases is one of the most infamous topics for many Excel professionals. Being able to do this, enables a user to Combine Databases with repetitive data into a properly structured format.

With the last three versions of Excel (2007, 2010, and 2013), this 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

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

Why to Use AutoCorrect...

“I Haven’t Got Time to Save Time”. You may not have ever heard or said these words, but the fact is that many people conduct their professional lives as if this phrase were the mantra to their philosophy.

Being an Excel Enthusiast, you naturally believe there are easier ways to do things, and that brings to today’s subject, AutoCorrect!

If you routinely need to type out a Company Name or any other so-called “Boilerplate” text in Either Excel or Word, you can Save Time by setting up your AutoCorrect option to Automatically change the text you are writing.

Of course, AutoCorrect is initially set up with a list of typical misspellings and symbols, but this is for the everyday masses, not Excel Gurus such as yourself! Let’s say you work for National Public Radio, and the legal department wants the entire name of your service Spelled Out each time it is entered into a worksheet.

While this may seem like a chore, AutoCorrect can make this is as easy as typing NPR.

Here is How You can Set this Up:

1. Go to FILE and select Options from the bottom of the column

2. Choose Proofing and click on the AutoCorrect Options button

4. In the Replace box, type NPR

5. In the With box, type National Public Radio

6. Click Add and then OK That’s all there is to it! But wait! There is also a World of Opportunities to have a little Fun as well!

Let’s say you have a friend named Gary at work (any name will do). While he is away from his computer, go into AutoCorrect and enter Gary in the Replace box and Geek Boy in the With box.

Do this in Word also, and he will be stunned every time he types his name! This is great adolescent fun, (but it helps if you know Gary has a sense-of-humor…). Just be sure you have him do this in your presence, so he doesn’t unnecessarily embarrass himself (and you).

Using AutoCorrect can better your life. Unless, of course, You don’t have Time to Save Time…

Embedding Excel in Other Programs

We all agree that Excel is the Quintessential Tool for crunching numbers and developing corporate information. There are times, however, when you may want to Upgrade the way you present your findings to the stakeholders of the data.

If, for instance, you are making a presentation with PowerPoint, it is typically much more effective if you insert your Excel information, (a chart, for instance), rather than having to switch programs midstream.

To do this, simply open a PowerPoint presentation (or Word document if that suits your purposes) to add the Excel worksheet, then Insert Object. Choose Microsoft Excel Chart from the Object Type and click OK.

 

You will instantly have a generic Excel chart inserted into your presentation or document. From there, it is equally easy:

• Just right-click the chart and choose Edit Data from the dropdown list. You can import it from a worksheet you have already created or, if you choose, create your own data on the spot.

• Changing the Chart Type can also be accomplished from the same dropdown, as can formatting (Format Chart Area…)

• If you want to save the chart image as a static image, you can choose Save as Picture and save to a file, locking in all that you have done for possible future use.

By using these tools, you can enhance that overt Professionalism in your presentations and documents. Very cool!

Excel Wizardry

As with so many things in life, what seems like “Wizardry” in Excel is really a matter of knowing some Simple, albeit powerful, Tricks.

#1 - Multiple Lines of Text within a Cell
Have you ever wanted to add multiple lines of text to an individual cell? How can this be done, you ask? The answer is so easy, you’ll laugh.

When you want to insert a line break within a cell to break up your text for readability, just hold down Alt and hit Enter. You are also turning on Wrap Text when you do this. Cool!

#2 - Delete Blank Rows
If you have blank rows of cells within your database or table, it can potentially cause issues when deriving information (and who needs that…). Select a column, press F5, click on Special, select the Blanks option, and click OK.

Now that you have the blanks targeted, click over to Excel’s Home tab and go to the Cells group. Select Delete Sheet Rows and Bamm, no more blanks!

#3 – When Numbers are Not Numbers
Excel will occasionally store numbers like a “0” as Text instead of a numeral, (often when importing from another program). This can be problematic, especially if you have a sizeable database. The solution, once again, is so easy you’ll laugh!

  First, simply type a “1” into an empty cell outside of you data. Select and copy it to your clipboard, and then select the range of numbers you wish to fix. Right-click and choose Paste Special, select Multiply, and click OK. Problem Solved!

Simple tricks that look like Wizardry. Everything is simple when you know how!

Errors, Errors, Everywhere!

We’ve all Been There, Done That. You are creating an Excel masterpiece formula when you are troubled, nagged, and thoroughly bugged by some Error Messages.

The following is a handy Alphabetical Listing of Error Messages and what they mean:


• #DIV/0! - You divided by 0 or by an empty cell (Everyone knows you can’t do that…)
• #N/A - A return value of the function is not available (You probably messed up the formula)
• #NAME? - Okay, you might have used an undefined range or cell name (easily corrected…).
• #NULL! - You specified an intersection of two areas that do not intersect (Curious error…).
• #NUM! - There is a problem with a number in a formula (Is it really a number?).
• #REF! - An invalid cell reference is mentioned in a function (A cell reference is not valid).
• #VALUE! - The wrong type of argument or operand is used in the formula (Yeah, yeah, yeah)

Now let’s look at a few Handlers you can use to Attack Errors:

1.   ISERR - Any error value except #N/A
2.   ISERROR - Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3.   IFERROR – Introduced in Excel 2007, this combines the IF and ISERROR functions

Let’s use the IFERROR in an example. Suppose you have two columns of data which you are simply dividing in a third column. If you Divide by 0 or Divide by a Blank Cell, you will obviously get an Error Message.

 If, however, you want the message to be Perfectly Clear, (as our former President Nixon was fond of saying…), you use IFERROR to define a Custom Response such as “Error in Calculation” or “Problem with 2nd Value”, or something more clever that you invent for your circumstances. In the example shown, the formula would be:

 =IFERROR (Column_1 / Column_2, “Error in Calculation”)

By not simply accepting Errors and Error Messages, you can better Take Control of your Excel workbooks. Always a good thing…

Macro Robots

Although the topic of Excel Macros is a bit overly ambitious for a blog of this type, it is a subject about which many individuals are interested. Macros are not part of most Excel users skillset, but they can help make your day-to-day tasks much easier and the basics should not be intimidating.

Many of us have workbooks which we need to continually update with new data and repeat certain tasks over and over. A Recorded Macro can be your ticket to lessening your repetitious work and simplifying your routine chores.

A Macro can record your mouse clicks and keystrokes while you work, and then allows you to play them back in future renditions of your workbook. When you run the macro, it will play the commands in the same order that you recorded them. It’s like having a Macro Robot at your command!

For example, let’s say you track the performance of the representatives in a call center. Every week a report must be created for management, and it involves routine updates. Here's how to record a macro to apply these updates:

1.   In the report workbook, click the start of the cells you are going to update.
2.   Point to the Developer tab, and then click Record Macro.
3.  In the Record Macro dialog box, enter a Name that applies to your operation. For ease of operation later on, assign a custom Shortcut Key.
4.   Now perform the calculations, formatting, moving, etc that applies to the repetitive and monotonous update.
5.   Finish recording the macro by clicking the Stop Recording button.

To Run your Macro, simply go to the update start cell and enter your Custom Shortcut Key.

Bamm! Instant update! Your newly created Macro Robot has just done all of the work that may have taken you several minutes or an hour to complete. Give it a try – Totally worth the effort!

Give Me the Green Light!

Let’s face it, Excel worksheets can at times be a Bit Drab. A worksheet can serve as a Decision Tool and showing a Yes, No, or Maybe next to your agenda items can get the job done, but it’s really Ho-Hum, isn’t it? Since the introduction of Excel 2007 you have some very interesting options, however. You can use sets of Graphical Icons with your decision lists, and give your reports some Much Needed Life!

For an example, let’s suppose we have a proposed agenda for an upcoming annual corporate meeting. Possible topics have been submitted from numerous sectors, 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 is How to Accomplish This:

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 garner the closer interest of any of the users. And, of course, the cool thing is that it is Easy (Isn’t anything when you know how…).

The Missing Data Entry Form

The highly useful Data Entry Form was a favorite of many Excel users in the past, and for many savvy gurus, it still is.

Besides being an efficient way to enter data into an Excel database, the Form also allows you to:

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

The primary use for most Excel aficionados is, of course, Data Entry (ergo the name of the form…).

Back in the day of Excel 2003 (and prior versions), the Form was neatly located in the Data dropdown list on the toolbar. Starting with Excel 2007, however, it was not shown on any of the default ribbons, and many users simply forgot about this great little tool (or thought it was no longer available).

So Where Did It Go?

Good News: It is still available, even on Excel 2013. To add it onto the ribbon of your choice, simply do the following:

   1. Go to Options / Customize Ribbon
   2. Choose Commands Not Shown on the Ribbon
   3. Scroll down to Form and add it to a New Group in the location of your choice

This handy little form will now be readily available whenever you may need it. If you have never used it, give it a try!

Why You Should Use Subtotals

Quick and Easy. Always a good thing. Using Excel’s built-in Subtotal feature can save you an enormous amount of time and give you a remarkably easy way to Organize Your Data with practically no effort. Here are 3 Reasons Why you should put Subtotals in your Excel tool belt:

1. Versatility: As you would expect, Subtotals are able to summarize your data by SUM. Additionally, however, your data can also be reviewed by Count, Average, Max, Standard Deviation, and others.

2. Dynamic: With the ready access to controlling what type of data and which field at your fingertips, you have the ability to slice and dice your information on the fly (Good Stuff!).

3. Sharable: Often what an analyst or manager wants is a “Quick and Dirty” way of reviewing their data in a logical manner. Moreover, however, it is typically desirable to share the Information (“Information” is clearly superior to mere “Data”) with others. Subtotals are a sharp, concise way of doing exactly that!

As with so many marvelous tools in Excel, Subtotals are far too often misunderstood, ignored, or simply not thought of. Give it a try, but here’s an Important Tip: Since you need to initially sort your data according to what you wish to summarize, it might be a good idea to save it to a Practice File before trying this out for the first time.

Subtotals. Another example of Excel Gold.

Quick Stats

Statistics. As Mark Twain once said, there are “Lies, damned lies, and statistics”. Nonetheless, Statistics are integral parts of our everyday lives. We are bombarded by statistics in advertising, news stories, and the popular media. They are, frankly, Essential to our modern way of life.

As Excel users, we are much more likely to use statistics than the Average Joe. A very Quick and Easy way to obtain most of the common stats used in business is to go to the Data Ribbon in Excel and choose Data Analysis (on the far right of the ribbon).

You can then try this out by choosing Regression from the Data Analysis dropdown. Using the data shown above, put the Sales Data in the Input Y Range and the Month Data in the Input X Range, and choose a single cell for the Output Range.

The results will Instantly give you the following Regression Statistics:

• Multiple R
• R Square
• Adjusted R Square
• Standard Error

You will also get grouped ANOVA stats for Regression and Residual.

For those of us who live with statistics, this technique can be a boon for obtaining a Quick Summary of your Stats. 4 out of 5 Excel Gurus Recommend It!

Military Time

As anyone who has been in the service (or watched enough movies) knows, the military 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.

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.

When it comes to Excel, some of us may be faced with translating Military Time to our typical U.S. Standard Time. This isn’t terribly difficult, but 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. Cool!

Another bit 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, of course, want to be sure and Format your result cell to Time. For instance the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting.

You may not need to do this type of conversion soon, but when you do, You’ll Stand Out from the Rank and File!

Standard Deviation

When it comes to common statistical functions, Standard Deviation is perhaps the most overlooked and underused. This has always struck me as being unfortunate, since with very little imagination, it can be easily used and adopted to a wide range of everyday business purposes.

For those of us who have not been in a math class in a while, Standard Deviation is simply, (and I do mean, “Simply”), is a measure of how widely values are dispersed from the average value.

For instance, let’s say that you oversee a Call Center and you would like to use Excel to monitor a new metric regarding talk times. Suppose that you already track weekly results and know that your company’s representatives can provide your customers good service in a Mean Average of 6-8 minutes. When it comes to Standard Deviation of your Week-over-Week monitoring, you would undoubtedly prefer to see Smaller values versus Larger values (tighter grouping around the average times).

So how can you use Excel to track this useful statistic? If you are still using Excel 2003 or Excel 2007, using the old STDEVP is your Go-To function. This assumes that you are using the Entire Population of data (ergo, the “P”). If you are using just a Sample of your data, then you will want to use STDEV.

This was made more intuitive in Excel 2010 and Excel 2013, where the function for finding standard deviation for the Population is STDEV.P and the Sample is STDEV.S. (Hooray for Microsoft for continuing to make these sorts of refinements.)

Rather obviously, this type of information is Most Effectively Illustrated quite effortlessly with a Line Chart or a Bar Chart. You can then see at a glance whether talk times are under control in this regard.

This is, of course, a mere example of the virtually countless ways of using Standard Deviation in your Excel reports. Just another way that Excel can improve our business lives (which, as Martha Stewart would say, “Is a Good Thing”.)

Calculating Workdays

Do you ever find yourself Counting Workdays until your next vacation, holiday, or other event? If you are like my sister who is retiring in a few weeks, the answer may be a resounding “Yes!”

If you have a set of dates in Excel and you want to calculate the number of Business Days, (excluding weekends and holidays), you can easily do this using the NETWORKDAYS function. The syntax is as follows:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

If you are working with an older version of Excel (prior to Excel 2007), you should note that the NETWORKDAYS was available only after you installed the Analysis ToolPak add-in.

Also, starting with Excel version 2010, if you wish to calculate the workdays using something other than the standard weekend days of Saturday and Sunday, you can use the new NETWORKDAYS.INTL. With this new function, you can customize your weekend days as simply as your holidays.

But let’s suppose you have a start date for Project and you know how many workdays it will (theoretically, at least) take to complete it. How do you calculate the Date that it will be done? Here is where the WORKDAY function comes into play. Using the following syntax, you can easily calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

If you wish to ascertain that the “StartDate” is interpreted correctly by Excel, (this is a recommended Best Practice), you can use the DATE function in its place, and alleviate any concern in this regard.

Calculating Workdays: As with most anything we encounter, when you use the right tools, it’s like being on vacation…

Readers’ Request: Easter Eggs

I find it interesting that one of the Most Popular Excel subjects among readers of this blog is Easter Eggs. As any of you who have been with me for a while knows, I addressed this a little over a year ago, and I regularly get requests to update this topic.

Virtual Easter Eggs are, of course, hidden games or messages that have been built into various software by cunning developers who have a sense of humor (utterly lacking at Microsoft these days) and enjoy building in a bit of intriguing fun into Excel. In years gone by, users who were “In-the-know” felt smug knowing how to reach the cryptic, and often entertaining, secret content.

The term “Easter Eggs” is attributed to one of the founding fathers of computer games, Warren Robinett. While working for Atari in the late 1970s, Robinett created a hidden screen which read, “Created by Warren Robinett”. Back then it was not uncommon for game designers to be given little credit for their work, so he probably felt his small ruse was justified (I agree…).

“Old-timers” of Excel will likely remember that Excel 97 had an ambitious Flight Simulator hidden within the application (it was pretty cool!). Using a simple combination of keyboard commands brought you to this remarkable (considering the era) simulator game.

Although a good deal more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled the classic Spy Hunter game (which, if you are interested in the “oldies”, can still be found online).

Excel 2003 included an Office Quiz featuring the Crabby Office Lady (remember her?). If you still have a copy of this version, you can access this egg by typing in “Tortured Soul” in the search box.

Although there have been occasional rumors to the contrary, I don’t believe there are any more hidden gems in the Excel versions after 2003. In fact, it has been reported that Microsoft will fire any employee who is caught inserting an Easter Egg into any of their applications.

That’s a pity, in my opinion. Sure, I fully understand the potential for security problems, and the inherent lack of professionalism in playing around with Easter Eggs, but I also find the days of Easter Eggs to be a charming time in software development. An age of innocence, if you will. Ah well, here’s a nod to the good old days…

Back to Basics!

Basics. I have heard many supposed Excel Experts scoff at the idea of revisiting the topic of Basics. The fact is, however, as with so many other disciplines, an occasional review of some of the fundamentals is inevitably a worthwhile Tune-up.

Navigating your worksheets, for instance, can be Drag unless you know a few simple tricks. For instance, if you wish to go to your last entry at the bottom of a list that contains 30 records, scrolling to where you wish to go is a Breeze! When you have a list containing 30,000 records, however, it is a bit tedious.

As is true with so many Slick Moves in Excel, keyboard shortcuts rule when it comes to saving time moving from one location to another on your spreadsheet.

Here are few Slick Moves you can make without ever touching a mouse:

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

When using your mouse, another way to navigate to the end of your data (whether in a column or row) is to carefully place your pointer on the adjacent border of cell in your range and double-click. For instance, if you wish to navigate to the last cell in a column of data that starts with cell B1, you can select B1 and double-click on the bottom border of the cell.

Want one additional way to navigate when you know the exact address of some remote cell? Simply enter the address (e.g. ET30000) in the Name Box and Zap: you have beamed directly to that location (without the help of Scotty...).

And how about Entering Repetitive Data?

While it is 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 slick (I admit, I like “Slick” moves…)

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. Bamm! Your Data is Entered!

Basics. Excel has a multitude of easily-mastered Slick Tricks that can save you time, and make you look good at the same time. It pays to revisit them once-in-a-while…

Imaginary Numbers

As they used to say on the old Monty Python Flying Circus show, “And Now for Something Completely Different”!

Imaginary Numbers are not something that most of us run into, and only a small percentage of us will ever find a use for them in our jobs. They do come up as a topic in most Math Curriculums, however.

For those of us who have not had a math class in a long while, (or if you were not a Math Geek like me), an Imaginary Number is a number that can be written as a real number multiplied by the imaginary unit i. The Square of i is -1. An imaginary number has a negative or zero square. For example, 5i is an imaginary number, and its square is -25.

In terms of imaginary numbers, the Square Root of -4 is 2i.

Weird stuff, I know. Real-World applications can be found in Engineering and Scientific fields. They are nothing terribly new, as they were conceived as far back as the ancient Greeks (by a smart guy be the name of Heron of Alexandria).

Imaginary Numbers in Microsoft Excel

Interestingly enough, Excel can handle these quirks of the math world. It even has a couple of specially designed functions to deal with Imaginary and Complex (expressed in the form a + bi, where a and b are real numbers and i is the imaginary unit, where i2 = −1).

The Imaginary function operates with the syntax:
   =IMAGINARY(inumber)

The Complex function uses this syntax:
   =COMPLEX(realnumber, inumber, [syntax])

There are also several innate Excel tools for using imaginary numbers with your typical arithmetic functions of addition, multiplication, etc.

I suspect that I know what you may be thinking, “What does this have to do with me?”. Well, I wouldn’t be surprised if your son or daughter encounters Imaginary Numbers in school some time, and wouldn’t it Rock if you could show them how to do this in Excel?

He or she might even think you are pretty smart. Imagine that!