Top Ten Favorite Shortcuts for 2015

It’s been about two years since I touched on Keyboard Shortcuts, so “The time has come, the Walrus said, to talk of many things” (to quote Lewis Carroll):

As I have told have told hundreds of Excel students of mine over the years, Keyboard Shortcuts can add speed, efficiency, relief from the stress of overusing the mouse.  Although Carpal Tunnel syndrome is not as prevalent among Excel users as once thought, it is still a concern, especially if you are a woman (women contract the syndrome three times more often than men…).

These shortcuts are just plain Cool, and have no doubt about it, they will set you apart from Mere-Mortal Excel Users.

So, in Reverse Order, here are my Current Favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2015:

10.  ALT+F1 - Creates an Embedded Chart of the selected data (Always a crowd pleaser!)

 9.  CTRL+H - Find and Replace (I can’t imagine working without it!)
 8.  CTRL+K - Displays the Insert Hyperlink dialog box for new hyperlinks (Very useful for mimicking websites in your workbooks!)
 7.  Shift+F10 – Opens the right-click menu (Handy when your fingers aren’t on the mouse…)
 6.  CTRL+SHIFT+% - Applies the Percentage Format with no decimal places (Just how I like it!)
 5.  CTRL+9 - Hides the selected rows (Cleans up your worksheet, and it simply Cool!)
 4.  CTRL+: - Time-Stamp your worksheet (Good for time management…)
 3.  SHIFT+TAB - Moves to the Previous Cell (Once you get used to it, you will use it!)
 2.  F5 - Brings up the Go TO dialogue box (Great for navigating to a named range!)

And my Current #1 Favorite Keyboard Shortcut is (Drumroll please…):

 1.  CTRL+PageUp - Jumps to the previous worksheet (CTRL+PageDown jumps to the next worksheet) 

 
Try a few a few of these shortcuts sometime, and see how they can make your Excel Life Better!

The Red Light

Nothing grabs your attention like a Red Light.  (At least, we hope that’s the case when you’re driving.)  Cold, hard data in Excel can be, well, Cold.  Numbers can be rather numbing and even the best analyst may have moments when she or he has lapses in attention.

Since Excel 2007 was introduced, there is a small, but powerful tool available in Excel that is in my estimation considerably underused.  As we all know, Conditional Formatting can help focus the attention of the report user, but after all these years, merely coloring the cell background or font can be rather drab.  That is why I embrace the comparatively new Conditional Formatting Icons. 

For instance, in the table below, a Red Light Icon appears in the Alert column whenever the monthly sales were less than 20,000 (assuming that is the sales goal).

Here is How to Set this Up:

1. Select the range of cells in which you want a Semaphore to appear the (Alert column in the example)
2. Insert a simple IF formula that returns a “1” or a “3” (i.e. IF(C4<20 1="" 3="" b="">) Note: By default, a “1” will be a Red Light and “3” will be a Green Light  
3. Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
4. Go back to Conditional Formatting and choose Manage Rules
5. Click Edit Rule and put a check in the Show Icon Only box and Apply

 Your Alert field will instantly take on the engaging Stop/Go formatting that will be sure to catch the attention of any of the report users.  Very Cool!

NETWORKDAYS

If you are in the position of Scheduling Projects or managing individuals’ time, knowing the number of Workdays between two dates can be highly important.  Knowing how many business days lie ahead is obviously essential for planning, as is calculating the number of days/hours that have been worked on a project.  

Whereas there are specialized types of software for managing projects, (such as Microsoft Project), Excel can be very useful in this arena.

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 with the following syntax.  Linking the Dates with Absolute References to cells containing this information is a best practice:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

Similarly, let’s say you have a Start Date for a project and you have a budget of a certain number of workdays to spend completing it.  To calculate the Date that it will be completed, you can easily use the WORKDAY function. Applying the following syntax, you can quickly calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

Another Best Practice:  Since Excel can occasionally misinterpret (or not understand) some imported data, you can use an additional function to ascertain that the “StartDate” is interpreted correctly.  By using the DATE function in the place for StartDate, you alleviate any concern in this regard.

Calculating workdays with the NETWORKDAYS and WORKDAY functions.  More key tools brought to you by Excel.  Cheers!

Printing Worksheets

Have you ever Printed one of your worksheets that you wanted to bring to a meeting, and discovered that it printed on Several Pages of paper, rather than on One (which was wanted you wanted to bring to the meeting…)?

Sure you have; we all have had this happen to us at one time or another (let’s face it, we are not as yet in a totally Paperless work environment…).

A typical example is that some of your data on the right side of your worksheet (perhaps just a column or two) prints on its own page.  This obviously doesn’t make a very appealing handout for your meeting.  Bummer!  So, what do you do?

I’ll bet some of you have tried resizing your fonts or your column/row widths, and hoped for the best when you reprinted your handout.  Whereas that may work, there is a handy tool in Excel that makes it so much easier.

All you have to do is go to View / Workbook Views / Page Break Preview. In this mode, you can easily Control how your worksheet prints with a minimum of effort!

In Page Break Preview you get a look at your worksheet with page breaks shown as Dashed Blue Lines. The dashed lines show the Automatic page breaks which Excel has chosen by default. By hovering you mouse pointer over one of the lines, you can left-click/hold and move it wherever you wish. The lines will then become Solid Blue, indicating the Manual Page Breaks that you have chosen (Cool!).

A Small Word of Caution
This technique is highly useful when you have just a Very Few columns or rows that do not initially print on the single page you wish to distribute.  What happens when there are Many columns or rows that you wish to add to your printed sheet, however?  It may be tempting to manually slide your Page Breaks to include a significant volume of columns or rows, so that you can have an Elegant One-Page Handout for your meeting.  Guess what?  It works!  You will, indeed, get all of your data printed on One Page!  You may, however, need to invest in some Magnifying Glasses so your audience can read it!

Page Break Control; Another Excellent Excel Tool that puts you in Control and saves you time!  (Good Stuff…).

Excel Detective Work

Doing a bit of Detective Work is sometimes necessary in Excel.  For instance, you may have inherited an Excel workbook from a former coworker, wish to investigate the techniques used by a current colleague, or merely refresh your memory on how you created a workbook in the now fuzzy past.

Whereas there are specialized commercial tools for doing hardcore fraud auditing on Excel worksheets, most of us simply need a Couple of Techniques that will provide a glimpse into what is going on in the background of our spreadsheets:

1)  Are there Formulas Employed in My Workbook?

Rather than manually checking each cell or range to reveal the formula in the Formula Bar, (as used to be the case in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

By selecting any cell within your worksheet and then using this simple keyboard combination, All of the formulas in All of your cells will be revealed.  You can then proceed to investigate further to see if there are any flaws in their construction.

2)  In-Depth Review of Precedents and Dependents

If you need an Omnipotent View of what your data is doing, where it is going, and where it is coming from, try using the tools in the Formula Auditing group on the Formulas tab. The tools include Trace Precedents, Trace Dependents, Show Formulas, Error Checking and more.

One of the great advantages of using the Formula Auditing tools is the fact that the information is returned in Easily-Understood Graphics that help you make Quick Sense of what is going on in your workbook.  Once armed with this information, you can proceed to work the magic that has earned you the Office Excel Guru alias.

Some simply applied tools to help you do your Sherlock Holmes work in Excel.  And it’s all quite Elementary, my dear Watson…

Negative Space and CF

Making your data Come Alive in Excel is much more than mere esthetics.  Indeed, it is all about better communication and the dissemination of information.

We are all familiar with Conditional Formatting, and probably most of us have used it to highlight important information in our workbooks.  A unique method of doing this takes a Reverse (or Negative) approach to this however.  Let’s explore this cool way to use Conditional Formatting:

For instance, let’s say you have a worksheet in which you are entering data (it may be numbers, text, or mixed) in a field, and you want it to be Very Apparent if a cell within that column is Blank (this is a recommended practice for good database maintenance). Here is a truly different way to do this:

For sake of example, let’s say you are going to be putting data in the short range of A1:A16.

1. Your first step is to apply a Fill Color to your range (A dark gray is a good choice.)

2. Then select your range and go to Conditional Formatting / New Formatting Rule

3. Choose Use a formula to determine which cells to format and put the following:

4. =IF(NOT(A1=""), TRUE, FALSE)

5. Finally, for your Format, use Fill / No Color

Now whenever you place data (numbers, text, or mixed) in one of your cells in the range, the dark gray Fill Color is Cleared! Bamm, it’s gone!

The cells that do NOT have data entered into them will retain the Original Fill Color.  Try this out and see if this does not make your worksheet Come Alive!

Combo Charts Revisited

In my current class of Excel Gurus, I once again reiterated one my favorite maxims, “If you have to think about what the chart is trying to say, it’s a Bad chart”.  So how do we communicate better with our charts?  Enter Combo Charts, which are a one of the mainstays when improving your message in your Excel Charts.

Let’s say you want to compare the number of your Customers for two years, and you want to be able to instantly see which months were the big hitters and which were the duds.  This is easily done, and can be enhanced using Combo Charts and the addition of a bit of attentive formatting.

Let’s Look at an Example:

1. Create a standard Column Chart using the table below:

 2. This will produce the totally ugly and Difficult-to-Decipher Standard Column Chart as illustrated below (you really do not want to use this style and format…):

3. Right-click once on any single column for the year 2013. This will select all of the columns for that year.
4. You can then select Change Series Chart Type and choose an Area Chart
5. Double-click on your chart, and bring up the Design Tab Tools.
6. Then take a couple of minutes to add some visual appeal by formatting your chart to create an easy-on-the-eyes chart similar to the one below:

And there it is! You have created a Combo Chart that Communicates Effortlessly with your spreadsheet viewers. Totally Cool!