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…”