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.

Wednesday, August 31, 2016

Watermarks in Excel

The use of watermarks in business has been prevalent for a very long time.  They serve many functions and are certainly useful in Excel.  An Excel user may wish to include a WATERMARK to indicate a Special Status of an Excel worksheet. For instance, you may wish to mark it PROOF, CONFIDENTIAL or DRAFT.  A watermark can be a quick and easy solution.

First, you may wish to create your own specific image.  In WordArt, you can easily create an image (such as DRAFT), upload it to an image-handling app, and save it as a .png file.  Then it is a simple matter of adding it to your Excel worksheet as a Background Image.

To add your Background Image/Watermark, simply do the following:
  1. On the Toolbar, click on the Page Layout tab.
  2. Go to the Page Setup group on the ribbon and click Background
  3. Browse to the file in your computer or network, and double-click it
  4. Bamm!  Your Watermark has been inserted!
One Additional Suggestion:  Format your image that provides a proper Low Contrast with your Excel content. You don’t want the image to overpower and obscure the data in your worksheet.

This may take a few minutes to do this the first time, but the results are really quite effective. If you ever find the need to add a WATERMARK in your Excel worksheet, give it a try!

Wednesday, August 24, 2016


There are countless undiscovered treasures in Excel, so it pays to explore a bit and be Choosy.  For instance, Nested IF functions can be very powerful, but they have limitations (the maximum nested functions is 7 in older versions of Excel) and they can be a bit difficult and cumbersome.  That is why it is nice to have a Choice!

Having a Choice is almost always a Good Thing, and fortunately, there is a preferable alternative to using the occasionally awkward IF functions. The CHOOSE function is often a better selection, as it is considerably more versatile! The CHOOSE function is remarkably straightforward and simple to use, and is best when combined with other Excel functions. It quite humbly returns a value from a list based on a given Position (Index Number).

Here is the Unsophisticated but Valuable Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Now for Some Basic Examples:

=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central

It also works with ranges:

=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30

You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to Cell A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would quite obviously return East.

The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Be Choosy and give it a try sometime!

Wednesday, August 17, 2016

MicroGraphs Rock!

Let’s face it, Not Everyone has a current (or even near-current) version of Excel. This is particularly true in many corporate settings, since not all companies see the value in upgrading our favorite spreadsheet program.  As you may know, “Sparklines” first appeared in Excel 2010, and are for many Excel users, a quick and easy way to graphically demonstrate their data without much work.  Sparklines make it a breeze to create readily-intelligible micrographs within the cells of your Excel spreadsheets that highlight important details of your data. 

But what if you use an older version of Excel, you ask?  Can I still make cool, little micrographs in my workbooks?  Yes, You Can!

A very effective way to make these engaging little graphs is to incorporate an unusual and diminutive Formula along with a splash of Conditional Formatting.  Let’s take a look at how this can be done…

Let’s say you have a Crack Sales Team in your company, and you want to graphically show their results without going to the trouble of creating a bunch of individual charts. Using the illustration below, put the Producers of your company’s products in Column A, and in Column B put the Units Sold each producer has sold. Here is the super-simple formula you should put in Cell C2 (and then copy it to C8):

= REPT( “l” , B2/1,000) 
For each Approximate Count of One Thousand, the formula puts an old-fashioned Hash Mark, (using a bold, simple font, such as Trebuchet or Arial works well), in Column C. With a bit of cool Conditional Formatting to add color, the result is a clean, professional report that integrates MicroGraphs!

Using these elementary graphs makes your data Visually Comprehensible and enhances the user’s understanding of what is being said in your Excel reports.
Try it out sometime if you are using a pre-2010 version of Excel, (or even if you are using a more contemporary version), and see what you think. They really do Rock!