Find Your Home on the Range

If you are an old pro at Excel, you probably know that Naming Ranges can save you a lot of time and make your formulas more intuitive to any user of your data.

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 working in Human Resources, and you are using a dropdown box in a cell that refers to a list of 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 “Employees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box. Cool!

Using a list with the Validation tool in Excel is a useful way of maintaining consistency in a spreadsheet. Combining it with a Named Range can make it More Effective!

Two Easy Ways of Creating Named Ranges:

1.   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 Bamm! Your range is named.

2.   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 Presto! All of your ranges are named at once!

Named Ranges. Another Best Practice for Excel professionals!

Slice and Dice Your Tables

Looking for a new way to get more information out of your data? Slicer may be just the ticket!

First introduced in Excel 2010, Slicer offered a relaxed way to filter your Pivot Tables with the ease of click. Due to its popularity, Microsoft expanded Slicer’s capability to include Any Table in the Excel 2013 version.

Similar in many ways to using Report Filters, (as well as your regular Filter in a non-pivot table), one of the beauties of using a Slicer is the immediate creation of an engaging Interactive Interface for the user. Although many will argue that this is not a compelling reason to use this tool, playing around with them a bit will probably sell you on their appeal.

1.  Using Excel 2013, first make sure you have transformed your table in a true Table by selecting any cell simply select any cell within your data, and going choosing Table from the Insert menu.

2.  Then select any cell within your new table and from your Insert ribbon choose Slicer from the Filters group. A popup menu of your fields will appear, giving you options for filtering your data.

3.  The dialog will ask you which columns you want to create slicers for. Each slicer you choose will filter a single column of data, and you can create slicers as many columns as suits your needs.

4.  Note that the slicers are Additive – you can keep applying more filters as you develop the view you are seeking.

Not just a duplication of the existing table filter drop downs, Slicers give you another tool for Focusing on Your Relevant Data. Another worthwhile tool in the newer Excel versions.

Three Quick Tricks!

This week we are going to take a look at a medley of tricks that do not necessarily fall into any neat category. All they do is Save You Time and Make Your Excel Life a Little Easier.

1. Lock Your Formatting
Using the Format Painter is a terrific way to save time adding a professional look to your worksheets. When you format and select a cell, you can click Format Painter and paste the formatting into any single cell.

The Trick that many Excel users do not know is that if you Double-Click the Format Painter, it will Lock the formatting so you can apply it to as many cells as you like! Piece of Cake!

2. Move the Quick Access Toolbar
Okay, you've customized the Quick Access Toolbar, but you don’t like having it way up in the upper-left corner (after all, why stretch all the distance with your mouse!).

You can opt to have it displayed at the bottom of the ribbon by simply clicking on Show Quick Access Toolbar below the Ribbon in the dropdown menu of the Quick Access Toolbar. Easy as Pie!

3. Prevent Error Checking
The Error Checking function in Excel is highly useful for pointing out Inconsistent Formulas in a particular range. There are times, of course, when you may need to omit a range’s formula in a few cells. Excel will then Flag what it perceives as an Error!

To avoid this annoyance, go to Excel Options and choose the Formulas group. Simply clear any of the error-checking rules that are bugging you. Piece of Cake!

Three Quick Tricks. It’s all Cake and Pie!

Intro to MS Query

As has been often discussed in this blog, there are an abundance of powerful tools in Excel that are often overlooked or ignored. This has been a recent theme, so today I want to gently introduce you to MS Query in Excel.

Microsoft Query is, frankly, an Advanced Tool for mining external data. Once this data is acquired from the external source, you are then free to work your Excel magic and manipulate it as you wish. Along with other potential sources, you can secure your information from the following databases/applications:

• Microsoft SQL Server Analysis Services
• Microsoft Access
• dBASE
• Microsoft Excel
• Text file databases

To fire up MS Query, first go to the Data tab and click on "From Other Sources." Select the option "From Microsoft Query."
 

Then choose a data source, (MS Access Database for instance), and provide the required information when prompted. You can also click Browse to find the source on your hard drive or network.

From there, it is a relatively straightforward matter of following the prompts of the Query Wizard. With a small bit of experience, you will be able to extract the Data you need, and be on your way to developing the Information you want in Excel.

If your job involves analysis of information stored in other formats and sources, you owe it to yourself to take a spin with MS Query.

All the best…