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!

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!

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…).

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.