Later, Alligator…

At over 9 years old, Excel Enthusiasts has been one of the longest running blogs on the Internet. It is time to move on, however, so if you Subscribe to this blog on Amazon Kindle, Please CANCEL YOUR SUBSCRIPTION this week!

Thanks so much for reading this little blog. You Folks have been Terrific!!

In closing, let’s say that you are working on a project where you want a list of weekdays, but you don’t want to manually enter them in your column or row. The solution is, of course, Autofill!

But how do you do this? Well, you may not have noticed that you can also use the Autofill tool for Auto-filling Weekdays. Here’s what you do:

1.   Enter the starting day into a cell (Monday, Tuesday, whatever…)

2.   Place you mouse pointer over the lower-right corner of the cell (the small black square is the “handle”) until you see the narrow crosshairs appear

3.   Right-click the handle and drag to select the range you want to fill with weekdays

4.   When you release the mouse button, a dropdown menu will appear

5.   Select Fill Weekdays

That’s It! A filled series of Weekdays! This tip works on nearly any version of Excel, so give it a try some time.

Later, Alligator!  ~Bob

Definitions

Defining a Name for a Constant or Formula is an excellent practice in Excel. It can simplify the use of your Excel workbook, (especially by others who may inherit the maintenance of the workbook), and add a touch of elegance as a bonus. 

This “elegance” is achieved by using descriptive Names, rather than cold cell references.

Defining a Name Is Very Straightforward:

1.   Select the Formulas ribbon and then click on Define Name

2.   Type the Name and Scope (typically the workbook…)

a.   Inserting a Comment is optional, but recommended in many cases

3.   in the Refers To dialog box, type the reference to the constant or formula and click OK

Although this practice of Defining Names may not seem like a Big Deal in your Excel life, it can add an easily accomplished additional touch of professionalism to your work. Give it a try!

Date-Related Data

As many of us know, Excel stores dates as the number of days elapsed since January 1, 1900, and times are stored as an additional fraction of the date number. Cells will, obviously, display the number or date and/or time, depending on the cell format that you choose.

What is cool, of course, is that you can use Date Functions to perform date calculations and extract the proper date-related data.

The following are Five of the Date-related Functions that are useful to nearly any Excel user:

1.   =DATE(year, month, day)

o   This function returns the Excel date number for the date indicated by the year, month, and day

2.   =YEAR(date) or =MONTH(date) or =DAY(date)

o   Returns the year, month, or day of the date

3.   =TODAY()

o   Returns the current date (as calibrated in the computer system being used)

4.   =WEEKDAY(date, start weekday)

o   Returns the week’s day number for the date specified

5.   =NETWORKDAYs(start, end, holidays)

o   Counts number of days between the start and end dates, excluding Saturdays Sundays and holidays

Handling Dates and Time in Excel can be a bit daunting. If you take a few minutes and master the foregoing Five Functions, however, it can make your Excel life easier.

A Small Excel Annoyance

Let’s face it, there are some small annoyances within many of the software programs we use. This is, of course, also true of a few in Microsoft Excel.

Take for instance the way Excel changes the column widths when you refresh the data in one of your Pivot Tables. You’ve got your table looking smart and professional, you refresh your data and, Bamm, it’s a bit of a mess!

So how do you maintain your chosen column width without having to reformat after every refresh? There are a few good ways of doing this, but the easiest and most effective is to do the following:

1.   Right-click your table and go to PivotTable Options

2.   Click on the Layout & Format tab

3.   In the Format grouping at the bottom, remove the checkmark from Autofit column width on update

Simple as that. Oftentimes, the best solutions are modest and elegant. Give it a try, and get rid of one of the small annoyances in your Excel work.

Turn the Tables

Excel Tables are, without a doubt, powerful tools for data analysis. If you are not familiar with Tables, or you just haven’t gotten around to using these comparatively new gizmos very much, I encourage you to take a closer look.

Here are Three of My Favorite Benefits of Excel tables:

1.   They’re a Breeze to Create

There are two essential ways to create an Excel Table: The first is to select your data, go to the Insert ribbon, click on the Tables down-arrow and choose (are you ready for this?) Tables! Okay, that’s cool, but the way the Cool Kids in School do it is to simply select your data and click Ctrl + T. Bamm, Done!

2.   Slice Those Tables!

Slicers are another one of the comparatively new tools in Excel that should not be overlooked. They are particularly effective at visualizing your data which, is a best practice in getting your message across without unnecessarily pouring through the numbers. Here’s what you do: Selector table, go to the Design ribbon, and select Insert Slicer. Nothing to it!

3.   Create Self-Expanding Charts

If you are like most Excel users, you have spent a great deal of time manually updating your chart data ranges when making repetitive reports. This is a total drag and needless work if you use Tables. If you set up your chart based upon data in Table format, any additions that you make to the data within the table will automatically be reflected in the chart. Now I ask you, How Cool Is That? 

Excel Tables. Make a New Year’s resolution to integrate them into your daily Excel routine. Happy New Year’s, All!

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!