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!