Take the Indirect Route

Named Ranges can be a terrific boon to the efficiency and ease-of-use of your Excel workbooks. Although using named ranges is one of my favorite Best Practices, curious problems may arise when referring to these effective shortcuts. To obtain true mastery in this regard, however, the savvy Excel Guru can employ the humble Indirect Function! 

Used in its simplest form, the Indirect Function can simply take the Name in the cell it is referencing and return the Named Range that it refers to. Using the Indirect Function along with drop-down boxes created with Validation, is a very user-friendly way of making Interactive Reports.

Create a Spreadsheet Like the One Shown Above, and then: 

 1) Create your Named Ranges by selecting each of your ranges and typing the new Name in the Name Box on the upper-left corner of your worksheet. In the above example graphic, Name each of the monthly rows for the Units shown. For instance, for December, select C15:G15 and name it December (no sense in making this any more difficult than that…).

2) In cell D18 insert the formula “=SUM(INDIRECT(C18))”. The Indirect Function reads the name of the month you choose with the drop-down box in C18 and the formula sums up the total for the units.

Using the Indirect Function along with Validation it is just one example of how to use this powerful tool in Excel. I wish you all a very Happy and Successful New Year

Funnel Charts (not cakes…)

Funnel Charts, (Not to be confused with Funnel Cakes, which are something completely different…), are one of the newer features in Excel that deserve a closer look. This new chart that appeared in Excel for the first time last year, is excellent for illustrating processes that incrementally become more focused as each stage is completed. Since I have extensive experience in the insurance industry, I am showing how business “Funnels” into an insurance company. 

You will have to have the latest version of Excel, of course, but it is a good idea to frequently update anyway…

With reference to the Funnel Chart above, here are the Simple Steps:

1.    As in the illustration (which you may have to enlarge for best viewing), use one column for the Process steps and the other for the Amounts

2.    Select the Data in the simple table you have created

3.    Click Insert > Recommended Charts > Funnel

4.    Format according to your tastes and/or your audience

The result is a remarkably easy-to-understand (which should be a major goal of Any chart…) illustration of how your processes interrelate. This new chart will be, I am quite sure, very useful for many of us in the future. Funnel Charts – Another way to picture your data and enhance your business communication!

Formatting Shortcuts Revisited

Yes, it’s true. I am an enormous fan of Keyboard Shortcuts. As every true Excel Guru will tell you, these shortcuts can save you many hours of work time over the long haul, and enhance your image as one of the Cool Kids in School.

One set of keyboard shortcuts that is typically overlooked is the ability to pull up the Right-Click menu to Change the Number Format in the cell or range of cells you are working on. Most all of us know that Right-clicking with your mouse and pulling up a context-sensitive menu is a good solution, but using the keyboard is Even Faster (and Cooler)!

The Right-Click Keyboard Shortcuts all start with “Ctrl+Shift”, which makes them more effortless to remember. As we discussed 4 years ago, the following are the most Commonly Used Tricks for rapid 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+%

It will, of course, take a little bit of time to become fully accustomed to using these shortcuts, but the time invested will certainly be worth it. Mastering keyboard shortcuts is an excellent way to maximize your Excel time (and make you appear the Brilliant Soul you truly are…).

Special Naming Ranges Tricks

As we have discussed in this blog, and most of us know quite well, Naming Ranges is a well agreed-upon Best Practice.

As with any tool within Excel, however, if you dig into it a little bit deeper you’ll find there are additional tips and tricks that can add to your knowledge and help you do even more in less time. Take for instance the following Two Tricks for Naming Ranges:

1.   Limiting the Scope of a Name

If you wish to reuse a Name on different worksheets within a workbook, you can limit the scope of a single use of the name to a single worksheet:

a.    Choose FORMULAS, and then click Define Name

b.   Fill in the details, specify SCOPE, and click OK

2.   Defining a Constant or Formula for a Name

The ability to have a Constant or Formula available in Name form can be a true timesaver when working with complex workbooks:

a.    Choose FORMULAS, and then click Define Name

b.   Type the NAME, SCOPE, and (if you wish, COMMENT)

c.    In the dialog box, REFERS TO, insert the Constant or Formula preceded by an =, and click OK

Knowing several Best Practices is the hallmark of any true Excel Guru. However, knowing a few extra special tricks in addition to these best practices can get you a place higher on the mountain.

More Rounding…

Last week we discussed the use of the CEILING and FLOOR rounding functions. Those are, of course, Not All that is available when it comes to controlling your Precision in Excel. We will look at the INT, EVEN, and ODD functions.

=INT(number)

This function rounds the number down to the nearest integer. Many possible applications, including staffing of projects.

=Even(number)

Using this function, your number rounds away from zero to the nearest even integer. If your staffing requires pairs of individuals working together, this may be just your ticket.

=ODD(number)

As you might expect, this function always rounds away from zero to the nearest odd integer. Undoubtedly useful in certain Odd situations (sorry, I couldn’t resist…).

INT, EVEN, and ODD. More ways to round your data your way in Excel.

Ceiling & Floor

When Excel is displaying slightly different values than what is actually stored in the cells that you are working on, it can be beneficial to use one of the several Rounding functions. Two of these rounding functions are CEILING and FLOOR.

The use of a rounding function is often recommended, especially if the numbers you are investigating are the result of multiple mathematical calculations. Multiple calculations may, of course, result in the introduction of rounding errors. These behind-the-scenes errors can cause small inaccuracies which may be very significant when precision is necessary.

The syntax for CEILING is CEILING(Number, Significance) where

·         Number is the number being rounded, and

·         Significance is the number of additional integers to which the number is being rounded.

The following Examples make this a bit clearer:

·         = CEILING(20.3, 1) = 21

·         = CEILING(20.3, 5) = 25

·         = CEILING(-19.9, 2) = -18

The FLOOR function does the very same thing as the CEILING function; however it always rounds down.

CEILING and FLOOR. Two more very useful functions for your Excel toolbelt.

Time Management with Excel

I admit that I have been a bit overwhelmed with work lately, and as an instructor and avid proponent of Time Management, I find this slightly upsetting. If, in fact, you employ good time management techniques, you can very directly determine what you should be working on and when. By the way, Ready for Anything by David Allen is an excellent book on this subject.

But how can Excel help in this regard? Well, let’s say that you have your Calendar and To-Do Lists in PDF form. You can import data in this formant directly into an Excel spreadsheet using a PDF to Excel Converter. The result is that you to easily keep track of key dates or to-do lists. You can eliminate a plethora of reminder notes and other calendars. In the process, you’ll also save paper and time, (Time Management, get it?...)

A large project with several professionals collaborating can use this Excel spreadsheet as a database for Project Management. Share the spreadsheet, and everyone can add their own information. You can include data such as availability, contact info, element ideas, resource tools, and so forth. For starters, check out the many If you are organized, you won’t be sitting at your desk wondering what to do next and, that my friends, is a very good thing.

Sparklines Series # 5

This week we are concluding our examination of Sparklines with this fifth installment of our series on this remarkably versatile Excel tool.

In this series we have explored the basics of creating Sparklines, changing their type, highlighting values, adding text, as well as changing the color and weight. In this final week will take a look at how to handle Hidden and Empty Cells in Sparklines.

Let’s examine the procedure of managing these potentially troublesome hidden and empty cells:

1.   Select the Sparkline group

2.   From the left side of the Sparkline Tools/Design ribbon, click on Edit Data

3.   Select Hidden & Empty Cells

4.   You will be presented with Hidden and Empty Cell Settings

5.   Choose Show Empty Cells:

·       Gaps - Show a space between points

·       Zero: Show as a zero (0) value

·       Connect data points with line: Connects the points before and after the empty cell

Try giving Sparklines a try sometime. You may be surprised at how efficient they are to create, and how well they will be received by your users.

Sparklines Series #4

This week we are continuing with the fourth installment of our series on the surprisingly versatile and useful Excel tool called Sparklines.

As we have noted, these Miniature Charts fit into a single cell or range in your worksheet and, as with any chart, they provide you with a visual representation of your data. The beauty of Sparklines, of course is that they provide this visual representation without having to resort to a full-blown chart.

Let’s see what else we can do with these Miniature Marvels. For instance, let’s say that you want to add a little bit more Visual Pizzazz to your Sparkline group. Here is what you do:

Change the Color and Weight of Your Sparklines

1.   First select a Sparkline or Sparkline group, and then choose Sparkline Tools/Design

2.   Then select a style of your choosing to change both the Sparkline and the Marker color

3.   Alternatively, you can change just the Sparkline color and weight

Customize the Axis Settings

1.   Select the Sparkline or Sparkline group

2.   Choose Sparkline Tools/Design

3.   Choose the Axis and options of your choice

Sparklines. Who knew all the wonders of the small but powerful tools?

Sparklines Series #3

This week we are proceeding with our series on the wonderful little Excel beasts, Sparklines.

As was mentioned last week, among the several advantages for using Sparklines, is their versatility. We looked at changing Sparkline types and removing Sparklines, but as they say in cheesy TV ads, “That’s not All!” Let’s look at the following Customization Options:

Adding Text

Adding text to a sparkling cell is a Snap (or is it a Spark?)! Simply select the cell, type in the text that you want, and press Enter. The text will display in the foreground with the Sparkline in the background. Totally Cool!

Highlighting Values

Highlighting data with a Sparkline is slightly more complex, but really no big deal. First select the Sparkline, choose Sparkline Tools/Design, and select the following options from the Show group:

1.   Add your markers to all values by checking Markers (go figure…)

2.   You can show negative values by checking Negative Points (not rocket science, eh?)

3.   Showing the highest or lowest values can be accomplished by checking High Point or Low Point

4.   You can also show first or last values simply by checking First Point or Last Point (this really is a Snap!)

I suspect you are beginning to see, there is a bit more to these little Sparkline gems than first meets the eye. The next time you have five minutes to yourself, play around with the surprisingly versatile little tools, and see how you can add some more Pizzazz to your worksheets. Ciao, baby!

Sparklines Series #2

Last week we started an overall inspection of the powerful little Excel tool whimsically name Sparklines.

These Miniature Charts fit into a single cell or range in your worksheet and can provide you with a visual representation of your data without having to resort to a full-blown chart.

Among the several advantages for using Sparklines, (Simplifying your visual representations of your data, creating Mini-Trendlines, etc.), they are also quite versatile. Take, for instance, the following:

Changing Sparkline Types

If, let’s say, you have created a Sparkline group, and changed you mind as to the type of design element you wish to use – No Problem! Simply select your Sparkline group and choose Sparkline Tools/Design. Then select one of the alternative types from the (guess what…) “Type” group.

Removing Sparklines

Very easy, and only slightly more difficult than you would expect. Once again, simply select the Sparkline group you wish to delete, choose Sparkline Tools/Design, and click the “Clear” button.

Sparklines! Simple, Powerful, and Effective. Next week we will look at some ways of highlighting values, and other customizing you can freely do with Sparklines!

Sparklines Series #1

Today we are going to start a brief series of posts revolving around the powerful little (it truly is “little”) Excel tool referred to as Sparklines.

These amazing miniature appliances were first introduced in Excel 2010, and they have gained a fair amount of popularity. Sparklines simply are Miniature Charts that fit into a single cell or range in your worksheet. As with any chart, they provide you with a visual representation of your data, but in this case, they do so without having to resort to a full-blown chart.

Although these tiny charts are typically used in a separate cell range adjacent to your data, they actually reside in the background of a cell similar to formatting.  It can overlay (or underlay) other contents of a cell, adding creative possibilities for displaying your information.

There are several advantages for using Sparklines. They can Simplify your visual representations of your data, and can do so with just a couple of clicks!  They are particularly useful in creating Mini-Trendlines for tracking a myriad of business metrics. Insert them in an adjacent column of a data-heavy report, and you get a result that can tell you at a glance the direction of your department.

Creating Sparklines

1.         Select a group of cells in which you want to insert your Sparklines.

2.         Find the Sparklines group on the Insert ribbon, and click the type of Sparkline that you want to create, (Line, Column, or even Win/Loss).

3.         In the Data box, select and insert the range of the cells that contain the data on which you want to create your Sparklines.

Bamm - Sparklines! Simple as that! Next week we will look at some creative ways of customizing these little gems, so you can truly make your Excel reports, well, Sparkle!

Precedence

As anyone who works with our favorite spreadsheet program for any length of time knows, it is very apparent that Excel and Math go hand-in-hand. That being the case, this week we will take a look at how Excel controls the order of Precedence.

For Arithmetic, the order of Precedence for symbols is as follows:

o   Negative = -

o   Percent = %

o   Exponential = ^

o   Multiplication = *

o   Division = /

o   Addition = +

o   Subtraction = -

For Comparing data, the order of Precedence is as follows:

o   Less than = <

o   Greater than = >

o   Less than or equal to = <=

o   Greater than or equal to = >=

o   Does not equal = <>

You will note that the foregoing differs at times from traditional math. As in nearly all of mathematics, however, you can control the order of precedence by using parentheses.

Math. Every Excel Guru’s favorite subject in school!

Rounding

As has been observed in many scientific and business circle, Precision would appear to always be preferable.  For example, .98977 is more precise that 1.0.  There are times, however, when precision is Not desirable, and may even not make much sense.

For instance, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Obviously, it would be difficult to find that .92 person (Please Note that we are not taking number of person-hours into consideration) so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.

 

You can, of course, use formatting in Excel that will make your numbers Appear to be rounded.  The trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. If you are dealing with numbers representing currency that have four decimal places, you would obviously want to express the results in no more that 2 decimal places, so you can format for showing exactly that (the unwanted Precision will nonetheless be lurking in the invisible details)! 
 

So what is the solution? Enter the ROUND function!  The syntax for the ROUND function is:  ROUND( number, digits )

A Few Examples are Illustrated Below:

In the following worksheet, Column B contains the rounding functions:

1.      B1 has:  =ROUND(A1, 2) and therefore rounds to two decimal places.

2.      In B2, =ROUND(A1, 0) is being used to round to zero decimals.

3.      In B3, more extreme rounding is being achieved by using =ROUND(A3, -1)

4.      B4 uses the same function as B3 and applies it to B4:  =ROUND(A4, -1)

Note:  If you always want Excel to round up, you can use the ROUNDUP function.  In our illustration, this would result in B3 being 30 rather than 20.  The ROUNDDOWN function does (no surprise) just the opposite.

Sometimes Precision is simply not the best choice.  In such cases, use ROUND!

Pivot Tables – A Final Look (for now…)

As is apparent in our exploration of Pivot Tables in the last three weeks, they are powerful, versatile, and a quick way to do analysis on the fly. This week, we will wrap up this series with a quick look at some additional ways to garner more utility out of this vital tool.

Changing the Summary Function

Go to any cell within the Values section of your Pivot Table and Right-Click. Then select Summarize Values By and click on the calculation of your choice (Sum, Count, Average, etc).

Changing How Values are Shown

In a similar way as you change the Summary function, Right-Click any value cell. Then select Show Values As and choose the calculation you wish to use (you may be surprised at the extensive list…).

Give Your Pivot Table Some Color

Providing some Design and Color can go a long way to making your Pivot Table easier and more pleasant to use. It can also add a considerable touch of professionalism that is so often lacking. Simply click on any cell within your table and open the Design tab/ribbon. Note that you can choose from a variety of Styles, as well as Banded Rows, Banded Columns, and a surprising assortment of Layout options.

Pivot Tables. Powerful, Versatile, Quick. Give them a try…

Pivot Tables – A Third Look

As most seasoned Excel users know, there are multiple ways to Filter pivot tables. With the advent of Excel 2010, however, you have a new, powerful option to use to filter (or should we say, Slice) your data.

Slicers give you the choice of using labeled buttons you can click to filter your Pivot Table data on the fly. In addition to quick filtering, slicers also indicate the current filters being used, which, of course, makes it easier to understand what is being shown in a filtered Pivot Table report.

How-To Slice

1.   Select your table and then choose PivotTable Tools, Analyze, Insert Slicer

2.   Check the fields you want to filter, then click OK

3.   To Filter: Click items in any order

4.   To Clear a Filter, click the filter graphic

Multiple Pivot Tables

To filter multiple tables with a single slicer, select the slicer and then choose Slicer Tools, Options, Report Connections. To delete a slicer, simply select it and press Delete.

Slicers. Another great cooking tool for your Excel kitchen set!