Naming Ranges – A Best Practice

Naming Ranges is one of those Best Practices that is so often neglected.  In spite of the fact that you can save you an enormous amount of time and make your formulas more intuitive to any user of your data, most users of Excel simply do not take the (very little) time to complete this important step.

Why Named Ranges

Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

For example, let’s say you are managing a Sales Department, and you are using a dropdown box in a cell that refers to a list of your employees. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature on Validation.

Rather than having to put up with the bother of adding/deleting employees from the reference list and then having to adjust the list range, you can create a Named Range “SalesEmployees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box.  Wow, amazingly simple!

Combining Named Ranges with the Validation tool in Excel is a very powerful way of maintaining consistency in a spreadsheet, and making them more accessible for anyone to understand.

How to Create Named Ranges:

1.     The Quickest Way is to Use the Create from Selection Tool

a.      Select your Table or Database (including the headers…)

b.     Go the to the Defined Names group on the ribbon

c.      Choose Create from Selection

d.     Assure the Top Row is checked

e.      Click OK and Bamm! All of your ranges are named at once!

2.     Alternatively, You Can Use the Name Box

a.      First Select the Range of cells you wish to name

b.     Now click inside the “Name” box on your toolbar at the upper-left of your screen

c.      Type Your Chosen Name for the range

d.     Hit the Enter key and Presto! Your individual range is named.

 Named Ranges truly are a worthy Best Practice for you to adopt in 2016.  It will add efficiency and clarity to any applicable workbook.

 Happy New Year, All!

Most Popular Topic of 2015

Of the 30,000+ views this blog received in 2015, the most popular topic was the Insert Function Wizard.  A Wizard is, of course, a help feature in Excel that automates finding a function by asking the user a series of easy-to-answer questions.

Tried and True

This wizard has been around a long time, and has been vital tool for many neophyte and experienced Excel users alike.  The names the functions are not always intuitive, so it can quickly help you zero in one the function you need.

Easy to Find

Whether you are using Excel 2007, 2010, 2013, or 2016, you can find the Insert Function Wizard nestled neatly in the left corner on the Formulas ribbon.  Simply enter a brief description of what you want to do.  For instance, let’s say you want to calculate the Yield of security you are considering, and Bamm, a list is presented for you to choose from.

Easy to Use

Then is a simple matter to complete follow the step-by-step instructions of the utensil to complete you function/formula.

The simplicity and efficacy of this handy wizard is the driving reason it has remained one of Excel’s favorite Go-To Tools. 

Cheers!

Three Swift Actions

As I have told my students many times, “There is no glory in effort”.  If you can achieve the same results in a short time and with less effort, Bravo to you, well done!

Here are 3 Swift Shortcut Actions that can save you time, effort, and (perhaps most importantly) make you even more indispensable as the Go-To Professional for manipulating data in Excel:

 
#1 – Swift Navigation to Worksheets: Rather than scrolling a large workbook, access a quick and easy shortcut to the worksheet of your choice by right-clicking the lower-left corner of your workbook. A Menu will appear showing the Workbook Tab Names. You can then choose your location and navigate to it directly. You can go right to your worksheet and skip by the rest – Totally Cool!

#2 – Swift Formatting: Select the cell or range with the formatting you wish to copy, click the Paint Brush on your toolbar. Your cursor will then turn into a brush that you can use to “Paint” any other cell or range with the formatting you have picked up from the previous cell or range (you can think of your selection as the Paint Can).

#3 – Swift Chart:  Always a crowd-pleaser, you can simply select your table of data, and press F11 at the top of your keyboard. In the blink-of-an-eye, a full-featured chart will be inserted in your workbook. If you don’t like the default chart choice, just right-click it and choose Change Series Chart Type.

Three Swift Tricks – There’s No Glory in Effort!

You May Need Excel 2016…

After having worked with Excel 2016 for a while now, I am convinced that many of us may Need (or at very least Want) Excel 2016.  Here are four of the reasons:

1)  Support for TouchScreen: Excel 2016 works great with a keyboard and mouse, of course, but it also supports an exceptional Touch mode that adds space between command buttons on the Ribbon. This makes them easier to select with your finger (or stylus if you prefer) as well as supporting the major touchscreen gestures.

2)  Excel Online: If you find yourself without a device running Excel 2016, you are still okay. As part of an Office 365 subscription you can use Excel Online in almost any major web browser and still review and edit your workbooks.

3)  Cloud File Support: The new Excel Save and Open screens make it easy to add your OneDrive (or SharePoint) site as a place to store and edit your workbooks. By using the Cloud, you are assured access to them on any device running Excel 2016.

4)  Pivot Table Enhancements: Along with the addition of Slicers and Timelines, Excel will now recommend and create a pivot table for you. Locate the cell cursor in one of the cells of the data list and select Insert/Table/Recommended on the Ribbon. Excel 2016 shows you a list of different pivot tables that you can create on a new worksheet in the current Excel workbook. All you need to do is click OK, and Bamm, new pivot table!

There are other new Cool Features, of course, and I may talk about those in a future post. Right now, however, the above four particularly strike me as some many of may Need…

All Along the Watchtower

We all know how potentially daunting it can be to keep an eye on the values of certain cells that are not immediately visible on a worksheet. This can be especially problematical if you are working with a spreadsheet that has grown very large in size, as continual scrolling to remote areas on your spreadsheet can be time-consuming and tedious.

This is where the Watch Window shines!  This powerful tool is often overlooked by even the most experienced Excel users. The Watch Window can display the value of any cell or cells in a viewing pane that can be relocated (as with any toolbar) on your screen. This handy toolbar keeps track of the following assets of any cell you wish to keep tabs on:

• Workbook
• Sheet
•  Name
• Cell
• Value
• Formula

Here is how you set it up: 

1. Select the Formulas tab on the ribbon
2. Select Watch Window from the Formula Auditing section
3. The Watch Window will appear
4. Click the Add Watch button to specify the cell(s) you wish to monitor

Simple as that!  What a great way to watch the critical areas of your data!

As Bob Dylan wrote, “All along the Watchtower, Princes kept the view…”

All Along the Watchtower

We all know how potentially daunting it can be to keep an eye on the values of certain cells that are not immediately visible on a worksheet. This can be especially problematical if you are working with a spreadsheet that has grown very large in size, as continual scrolling to remote areas on your spreadsheet can be time-consuming and tedious.

This is where the Watch Window shines!  This powerful tool is often overlooked by even the most experienced Excel users. The Watch Window can display the value of any cell or cells in a viewing pane that can be relocated (as with any toolbar) on your screen. This handy toolbar keeps track of the following assets of any cell you wish to keep tabs on:

• Workbook
• Sheet
•  Name
• Cell
• Value
• Formula

Here is how you set it up:

1. Select the Formulas tab on the ribbon
2. Select Watch Window from the Formula Auditing section
3. The Watch Window will appear
4. Click the Add Watch button to specify the cell(s) you wish to monitor

Simple as that!  What a great way to watch the critical areas of your data!

As Bob Dylan wrote, “All along the Watchtower, Princes kept the view…”

Comments

Most of us would agree that Lack of Communication is one of the core obstructions in the conducting of most businesses.  This is typified by the sometimes exasperating experience professionals have with some Excel workbooks.

Over time, workbooks can become overwhelmingly complex and, what may seem straightforward and obvious when you create a spreadsheet, may be bewildering in the future (especially for other users). This is where Cell Comments shine!  These are built-in Excel equivalents of the still-used Post-It Notes that have been popular in businesses for so many years.

As with so many things Excel, there are a variety of ways you can insert and format comments. I have, however, been a long-time evangelist of using the Right-Click option with your mouse, as it is intuitive and context-sensitive.

To insert a Comment, simply Right-Click on a cell and choose Insert Comment from the dropdown menu.

·       This will Insert a comment with the Username of the PC automatically included in the textbox (which you can delete or change under your general settings)

·       You can edit the comment and insert your directions at this time (or any time) by right-clicking the cell and choosing Edit Comment 

·       Subsequently, a Small Red Triangle will appear in the upper corner of any cell that includes a comment

Now every time the user of the workbook will see the customized Comment pop up whenever they hover their mouse over the modified cell.  Pretty Cool!

You can do More, however, as a great deal of formatting options are available for your comments.  For instance, suppose that you have different types of data in your worksheet and you want to Customize the look of your comments for various groupings. Here is what you do:

1. Right-click the cell and choose Edit Comment
2. When the comment appears, right-click the Edge of the textbox and choose Format Comment
3. Choose the Colors and Lines tab and customize your special look for the comments by changing Fill Color, Transparency, Line Color and Style

Using Comments is most certainly a Best Practices recommendation in Excel. It’s all about Communication, and if you haven’t been doing so, consider the use of comments today.  Future users of your workbooks (including yourself) will Thank You.

Happy Thanksgiving All!

Coolest Chart Titles!

I think we can all agree that Charts are tremendous depictions of data that conveys information in a powerful Visual manner.  Dynamically controlling your chart’s Titles, however, make the even better!

Let’s say that you have a table of data and a chart that changes dynamically every time you change the value in a dropdown box.  If you create and use interactive reports, (highly recommended), this is a common occurrence.  So, would it be possible to have the Title of the chart change to reflect the value (perhaps a name) chosen in the dropdown? Absolutely!  This is an advanced, (but very easy) way to make the charts in your report Stand Out from the mundane masses.
Here is How It is Done:

Let’s say you have created a table of data that changes in accordance with the Employee Name shown in a Drop-Down Box (easily done using Validation / List), with and Equals Sign (=), you can Link the Chart Title to reflect the name chosen in the drop-down.

If your chart Does Not have a Title, do the following:

 
1.     Click anywhere in the chart
2.     On the Design tab, click a layout that contains a title from the Chart Layouts group
3.     Select the Chart Title
4.     Go to the Formula Bar and type an Equals Sign: “ = ”
5.     Then Select the Drop-Down Box Cell to which you want to link and click Enter

Bamm! Your chart's title changes is now Synced with the dropdown value!  Try using this technique and enjoy seeing your reputation as an Excel Guru grow even more!

A Few Tricks…

Abracadabra!  I have always enjoyed quick tricks that accomplish very worthy tasks in Excel. Some of these are Oldie-Goldies, but Hey, they’re all really Good Stuff!

1. Select Noncontiguous Cells and Ranges
We all know how to select contiguous cells in a range or database, but how about noncontiguous cells?  Selecting these cells in a worksheet is as simple as holding down the CTRL key and click on the cells you want.  Presto!

2. Align Text Your Way!
Right-Click and access the Alignment tab on Format Cells. It’s a Snap aligning your cell in any orientation you want.  For instance, do you want the text in your cell to be vertically oriented?  Just click the Vertical Orientation Graphic, and Kazam, mission accomplished!

3. Jump Between Worksheets

To move from worksheet to another does not mean you have to reach for your mouse.  To switch to the next worksheet to the left, keep your hands on the keyboard and simply enter Ctrl + Page Up. Or change to the worksheet to the right by entering Ctrl + Page Down.  Poof!

4. Use Your Chart in Another App
Do you want to use your chart in PowerPoint, Word, or some other application? Select your chart and Copy / Paste as a Picture.  You can then feel assured that it will stay true to the original.  Voila!

Tricks are Good Stuff!  Give them a try and Amaze your colleagues!

What’s Happening in the Background?...

Have you ever inherited an Excel workbook, and had trouble figuring out how it works?  There are times in any Excel user's life when you're just not sure what is going on behind the scenes in your workbook.  This is an important concern, so let’s look a couple of Key Techniques for investigation of this nature.

1)  Precedents and Dependents?

Do you need a tool to see what your data is doing and where it comes from?  The Formula Auditing group on the Formulas tab is where you should go!  There you will find a great deal of help enabling you to Trace Precedents, Trace Dependents, and even Show Formulas. The information is returned in easily-understood graphics that help you make sense of the workbook you inherited from old what’s-his-name (why didn’t he leave some notes?...).

2)  Formulas in My Workbook?

Okay, let’s click on each cell and see if there are Formulas at work.  Ha, what a drag that would be!  Rather than clicking on individual cells, (as you needed to do in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the following cool little Keyboard Shortcut (you may know that I am big fan of keyboard shortcuts!):

Ctrl + ~

This simple keyboard combination immediately unveils the formulas in All of your cells, from which you can then determine if there is a flaw in their construction (old what’s-his-name did have his off days…).

If you are ever faced with a mysterious workbook, these two Investigative Tools can make your Excel life a log easier.  (And “Easier” is a good thing…).

Outlook and Excel

The topic of Concatenation came up in one of my live Excel classes this week and, considering how cool and useful it can be, I am addressing it in this week’s post.

Whether they are employees, clients, or vendors, Lists of Names of people are often kept in Excel. The format may vary, of course, but often the First Name and Last Name are contained in separate fields (which is how it should be…).  There are times, of course, when you may want to send an email via Outlook to a group of folks that you have in one of these Excel databases.

Outlook, as well as some other email systems, requires that the names be entered in a Last Name/Comma/First Name format. So what do you do if that is Not the way your Excel list is arranged?  Here is where Concatenation becomes useful!

Let’s say you have a list of employees with the First Names in column A and Last Names in column B. Combining them into an Email-Friendly column of names in a “Last Name, First Name” format is (as you will see) incredibly easy.

Let’s assume your table starts in cell A1. Using the convenience of Concatenation by putting the following formula in C1:

=B1&", "&A1

Notice: Be sure to use the quotation marks and insert a space after the comma.  This provides the syntax that Outlook will immediately recognize.  This formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Drag the formula down to fill Column C, and then copy and paste it into Outlook (any version from 2007 on…).

Outlook will very quickly recognize anyone in your address book and those to whom you’ve sent emails previous.  Happy Halloween All!