Wednesday, October 19, 2016

Seeking Your Goal

Goals are, with little doubt, what keeps businesses focused on objective for which the company was founded and a guidepost for maintaining profitability.  The classic management tool that is built in to Excel is, of course, Goal Seek.

Goal Seek is, of course, a “What If” tool enabling scenarios. What If scenarios are essential instruments of analysis for nearly any business, and Goal Seek 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. Goal Seek can save you an enormous amount of work, and make you look good in the process (and we all like to look “Good”…).

The following is a very Simple Example showing 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 corner office big wigs. 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:

Supposition: Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)
1) Insert the number of Expected Calls into Cell B2
2) Add 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.

This tool can, of course, be used to keener advantage with more complex scenarios, but our example demonstrates the overall concept and use of Goal Seek.  This handy utensil can be very useful for the boardroom or any strategic venue. Another vital addition to an Excel Guru’s tool belt!

Wednesday, October 12, 2016

Revisiting Intersections

As you may have gathered, I have a soft-spot in my heart (some might say my mind…) for obscure Excel tools and techniques. The Intersect Operator is most certainly one of those obscure tools, and it can be extremely helpful if you know how to use it.  

Although there are a great many ways to gather information from Excel databases and tables, some can involve rather cumbersome formulas that can take a significant amount of time to construct and verify.  On the other hand, the Intersect Operator, is not only Powerful and Versatile, it is also quite Effortless to use (“Effortless” is always a good thing…)!

This handy tool uses the vertical and horizontal ranges in a cross-tab Table or Database, and finds the value at the Intersection (ergo, the Intersect Operator…).  The syntax (Special Note: Be sure to use No brackets or commas…) is simply:

= (RangeName1 RangeName2)

For the Ranges, you can use the generic names such as =(C2:C42 A10:K10).  This certainly gets the job done, but it is advantageous in many cases to use Named Ranges, as they are typically much more effective.

As you probably know, you can very quickly name all of the ranges in your database by selecting all cells (use Ctrl + A), and the click Ctrl + Shift + F3. This will bring up the Create Names dialogue box as shown below.  Just click OK, and Presto - Named Ranges!


Example: Once your ranges are Named, you can then find a value with the modest function (I’m using Cell E5 for the example):

=(Los_Angeles Quarter3)  Note: Be sure to include the space between the column title and the row title.

There are many, many ways of obtaining this information in Excel, but the Intersect Operator is certainly an important technique to keep in mind.  It may be an “Effortless” way to get the information you are looking for.

Wednesday, October 5, 2016

Every Picture Tells a Story

Charts are the quintessential graphical way to display your Excel data and information. There is no doubt about that. The fact is, however, that there are times when you want the fine print details as well. Happily, there is a truly excellent tool in Excel that enables you have both!
The Camera Tool 

Though not shown by default on the Excel ribbon, the Camera Tool can be enormously useful when you are presenting a chart, and would like to include a Resizable Image of the source data included within your graphical exhibit.  To gain ready access to this handy gizmo, simply go to the Quick Access Toolbar in the upper-left of your worksheet, and from the small drop down button, click More Commands.  Scroll down to Camera and click Add

Bamm! You now have the Camera Tool at your fingertips whenever you need it.

So how do you use this little piece of magic? Glad you asked!

1)  Select the data table of which you wish to use with your chart,  and Click the Camera Icon on your toolbar (just like taking a photo with a camera)

2)  Then, go to the area on the sheet on which you want the data to appear (probably a blank area on the chart), and Left-Click 

3)  Resize and Reposition until it makes you happy 

Why not just copy and paste, you ask? Because the image you captured with the Camera Tool is dynamically linked to the original! This is not just a static image, folks; Every time you update your data table, (and consequently your chart), your data image updates as well!

I guess the lines from the old Rod Stewart song were right, “Every picture tells a story, don't it…”

Wednesday, September 28, 2016

Charts and Anti-Charts

I believe we can all agree that Charts are a quintessential way of telling your story in Excel in a way that will be readily understood and appreciated. We have talked about modifying and enhancing the built-in options for charts, improving on the standard fare that we are all-too-familiar with.

Newer versions of Excel offer new, additional ways of making mini-charts that help to visually expand on often-dry, lifeless data on a line-by-ling basis.  Sparklines are a good example of the newer charting tools that are available.

Not all individuals or offices (corporations are often a bit slow in this regard) have the most update Excel versions to work with.  So what if you are using, (or your audience is using), previous versions of Excel such as 2003 or 2007? Is there a Dynamic Way to provide a Visual Display of the data without using a conventional chart?  Well, Yes, of course, (why else would I be asking the rhetorical question…).  By using the 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 Five Asterisks: =REPT(“*”, 5)

The advantage of using this about this innovative is that it works in Any Excel Version, old or new.  You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.
For example, 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 calling it an Anti-Chart, since we are not using a built-in chart option) in Column C. We can then insert the following function in our Chart Column C: =REPT(“*”, B2/1,000):

Noting that we are dividing the number in B2 by 1,000, this 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. Presto - Anti-Chart! Give it a try!

Wednesday, September 21, 2016

Double-Click, Click, Click!

If you read this blog with any regularity, you know that I am a huge fan of Keyboard Shortcuts.

That being the case, I am nonetheless a proponent of using the mouse when it offers opportunities to augment your productivity.  Double-Clicking with your mouse is one such way to add speed and efficiency to your Excel work.

Here is an Updated List of My Favorite Ways to Use Double-Click:

 1. Adjust to Ideal Column Widths – Select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too. Perfect Solution!

 2. Auto-Fill a Series of Cells with Data or Formulas - Select the formula in first cell, Double-Click in the “handle” (small Black Square in bottom-right-corner) and Bamm! 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. Collapse Ribbon to Get More Space – One of my favorites. Just Double-Click on ribbon Menu Names.

 7. Lock Format Painter – Save a lot of Time by Double-Clicking on the Format Painter icon, making it Reusable. Great Solution!

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

 9. 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.

Keyboard shortcuts a Totally Cool, but Double-clicking with your mouse is also a powerful tool that can save you many hours of work. Try any or all of the nine techniques and see if you do not agree that Double-Click Rocks!

Wednesday, September 14, 2016

More Room, Please

In spite of the current Tiny House rage, it is safe to say that most of us prefer to have a bit More Room.  In addition to our abodes, this can apply also apply to Excel.

 As we all know, the focus of Excel is numbers. There are, of course, times when you need to enter some Text into cells as well, and you may even want to Add Another Paragraph (or perhaps just a line break) in a single cell. If you are working in Word, that’s easy – just hit the Enter key.

In Excel, however, hitting Enter will just take you to the next row below. “That is So Wrong!” you say, but don’t despair, the solution is so easy it will make you Laugh with delight (or maybe just smile…).

As we also all know, there is typically More Than One Way to do just about anything in Excel. I am a big fan of using the keyboard whenever possible, but we will look at a couple of techniques to enable a user to accomplish this very Simple, but Useful trick.

First Technique:
Once you have selected the cell you will be entering the text, simply click the Wrap Text button in the Alignment group of the Home ribbon (Excel 2007 – Excel 2016).

Second (and Totally Coolest!) Technique:
Using just the keyboard, all you need to do is press Alt+Enter to start a new line in the cell in which you are typing or editing. Does that Rock or What! Ask the other Excel Gurus in this office if they know this trick (I bet they don’t…).

Wednesday, September 7, 2016

Excel Charts: Other Uses

Charts are, without a doubt, one of the Superstar tools in Excel.  They can take otherwise dull, confusing data, and make it Visually Exciting and Informative.  After all, if your hard work and data is not easily understood, it serves no good purpose.

Limiting the accessibility of these spirited charts for use only in Excel worksheets, however, is not making the most of what they can offer.  For instance, how many of us have suffered through mundane PowerPoint presentations showing rows of dreary data and narrated by a presenter that has the droning voice of a far-off engine?  PowerPoints can be so much better with the introduction of some lively Charts!

So how do you add a Chart to a PowerPoint presentation?  For a Static chart, simply select the chart in Excel, right-click copy it, and simply paste/embed it into your presentation.  It can then be resized and further modified if you wish.

But what if you want to make a dynamic link between your Excel worksheet and your PowerPoint, so that when your data and chart changes on your worksheet, it also changes in your presentation?  Merely copy your chart as you did before, but when you paste it, use the “Keep Source Formatting and Link Data (F)” option.  You then have the luxury of having nearly instantaneous changes in your PowerPoint whenever you have changes in your Excel worksheet.
Very Cool!  Use this elementary technique to enhance the overt Professionalism in your presentations and documents.