Using Templates

Over the many years I have been using Excel and working with other people using this wonderful application, I have noticed that Templates are not used nearly as much as they probably should be.  This is unfortunate, as these predesigned worksheets can save you a huge amount of time, and make you look even more professional in the process.

With Excel open, you can easily find thousands of Office.com prefab templates by simply going to File/New.  There you will be presented with choices ranging from Daily Work Schedules to Event Planning; College Accounting to Retirement Planning; Wedding Budgeting to Garden Planning; and much, much more.  You will probably be able to find a template to fit nearly any need you have, whether business or personal.

Take for instance Calendars.  When you click on File/New and type Calendars in the search box, you instantly offered a selection of over 100 calendar templates!  When you see a thumbnail example of one that you think may be a likely option, simply choose it and click Create.  Bamm, you are all set to go with built-in fields, formulas, and formatting!  It really couldn’t be much easier.

In addition to all of the terrific built-in features in the Calendar templates one little trick that I have long used is inserting Hyperlinks in the individual dates.  Using this basic technique, I can be instantly transported from the note in the calendar date to the actual file it is referencing. 

Now, this is just a mere example of how templates can serve you when working with Excel.  Hopefully, I have reminded you of this outstanding and underused tool, and encouraged you to keep it in mind the next time you are setting up a new workbook.

Templates – Cool Stuff!

Birthday Reminder

Remembering an individual’s birthday can be very important.  It may seem like a little thing, but it can make a big difference in a person’s day if he or she feels remembered (True in both personal and corporate settings…).

With our old friend Excel, you can use a clever, and reasonable accessible formula to create a Daily Reminder of whose birthday it is on any given day.

Let’s say you have a list of 300 birthdays in Column A (A2:A301) which you (cleverly) name “Birthday” in A1, the corresponding First Name is in Column B, and Last Name is in Column C.  These may be the names of the employees at your company, (or maybe you have a really big family!).

Here’s What You Do:

1.    Select the entire range A2:C301

2.    Then go to your Home ribbon and choose: Conditional Formatting>New Rule>Use a formula to determine which cells to format, and enter:

3.   =MONTH($A2)&DAY($A2)=MONTH(TODAY())&DAY(TODAY())

4.    Click the Format button, select the Fill tab, and choose a color

Now, every time you open this workbook, Excel will automatically highlight the person (or people if more than one on any date) whose birthday is today.

You may never miss a birthday again!  (Your Mom would be proud…).

Outlook: No Cloudy Skies!

No we’re not talking about Microsoft Outlook here, nor are we dreaming of plans for the weekend.  What we are talking about is taking a refreshed look at Forecasting. 

As we discussed back in May of 2013, Forecasting can be very challenging.  Is it going to be cloudy, sunny, a mix?  Are our sales in a particular region going to rise, fall, or remain about the same?  Unlike such esoteric fields such as theoretical physics, the potential problem with most forecasting is the fact that we are dealing with historical data which Doesn’t Necessarily correlate with future outcomes.

Historical data can be valid, however, and happily, Excel has a built-in function that is cleverly named, “FORECAST” (where do the people at Microsoft come up with these ingenious names?!?) that can calculate linear forecasts.  The syntax for this function (stated simply) is as follows:

=FORECAST(ValueToForecast, RangeY, RangeX)

·       ValueToForecast is the point in the future which you need to forecast.

·       RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).

·       RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

With reference to the example below (and assuming no seasonality…), let’s say that you wish to forecast the Customer Service Calls for July. Here is what you should do:

 

1.  In cell C8 simple put:  =FORECAST(B8,C2:C7,B2:B7)

2.  Press Enter
3.  Bamm! Instant Forecast! (You should get approximately 1,403…)

A Couple of Worthwhile Notes:

1. The syntax of FORECAST can strike some users as slightly counterintuitive, so you may want to be patient with yourself while getting used to it.
2. The underlying assumption for your Forecast is, of course, Linear Data. If your data does not fit this model, (Seasonality or other factors) you may wish to explore other solutions.

The FORECAST function – Outlook: No more cloudy skies ahead!

First Names - Last Names

When importing data from sources outside of Excel, it is a common annoyance to find the imported information is not in the format you wish to have it in. For instance, as good database constructors, we know it is highly preferable to have discrete pieces of data in each field.

For example, it is better to have a field with the First Names and a second field with the Last Names of your employees rather than a single field with both. This is a common issue when importing data that is in the format, LastName, FirstName.

As we have explored previously in this blog, there are old favorites, as well as new ways of dissecting your data. For all versions of Excel you can, for instance, use the following formulas:

To extract just the First Name:
=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:
=RIGHT(A1, LEN(A1)-FIND(" ", A1))

If you are using Excel 2013 however, you have the marvelous new Flash Fill gizmo that automatically fills your data when it senses a pattern.  This clever new tool works well with lists of names, numbers, as well as a variety other data.

A Third Way, however, (this works with all versions of Excel) is to use the familiar Find and Replace instruments. Here is how you can do this:

1.    Create/Drag two identical columns of data for the First Name and Last Name next to your imported data field as illustrated below:

 

 

2.    Then select the data in your First Name field, press Ctrl-H to pull up Find and Replace and type in asterisk-comma: ‘*,’ in the Find what textbox (see below):

 

 

3.    To find the Last Name field, simply type asterisk-space-comma: ‘, *’ in the Find what textbox

And there you have it; three excellent ways to Have it Your Way (it doesn’t only apply to hamburgers…)!

Copying a File Directory

Here’s a little timesaver that can save you a lot of unnecessary work.  Over time, the number of files in a segment of your directory can reach a volume that can make it challenging to remember what they each represent.  There are also times which you may wish to email or otherwise note what you have on your hard drive.

You could, of course, manually copy them into an Excel worksheet, but who wants to do that!  There are, happily, easy ways to copy a List of Files into a worksheet.  Here is the first one:

1.    Open the folder on your drive and select all the documents with CTRL-A

2.    To select the files, use the shift button and mouse: Shift+Right-Click and then

3.    Choose Copy as Path from the dropdown menu

4.    Return to your spreadsheet and Right-Click/Paste

5.    Using this approach, you will paste the full path, Including the filename, of each document

6.    Finish up with any Formatting you choose

A more sophisticated way, perhaps, is to use Power Query.  Power Query is a free add-in that you can download from the Microsoft website and automatically install in your 2010 or 2013 version of Excel.

With Power Query, you can:

·        Import data from multiple logs

·        Connect to data across a wide variety of sources

·        Create custom views of your data

·        Perform data cleansing operations

·        Do Online Search for data from a large collection of public data sources

·        Much more…

We will take a closer look at Power Query in a future blog post, but in the meantime, remember, there are Easy Ways to copy a file directory.  And, of course, “Easy” is always a good thing!

Slicing Pivot Tables

A little over a year ago, I discussed Slicers in this blog.  At that time, a review of how Slicers (which debuted in Excel 2010) could be applied to regular tables in Excel.  In a recent class on Pivot Tables, a surge of interest among my students in the topic of Slicers has prompted me to revisit this comparatively new, and very useful, tool.

A Pivot Table is, of course, a fabulous tool in itself.  Combining Slicers into it makes it even better.  The main reasons they are so cool, is because they:

1.   Enable filtering the contents of your pivot table on more than one field

2.   Automatically update as additional data is entered

3.   Are Additive – you can keep applying more filters as you develop the view you are seeking.

4.   Provide an engaging, customizable interface for the users of your workbook (see example below…)

Regarding the 4^th point addressed above, you have the option of reshaping and recoloring your Slicers.  Since they float, you can put them wherever you wish, giving your interactive report a crisp, professional look (how Cool is that!).

How to Add Slicers to Your Pivot Table:

1.   Select one of the cells in your pivot table, and click the Insert Slicer button in the Sort & Filter group of the PivotTable Options ribbon

2.   Select the Check Boxes for the fields that you want to use as filters in your pivot table and click OK

Badda-bing!  That’s all there is to it!  Need a way to spruce up your Pivot Table report?  Give Slicers a try!

Reducing File Size

You say your Excel files have gotten a bit “Fat” and you wish there was an easy way to reduce their size?  You’re in luck, as there are several ways of reducing Excel Bloat!

Decreasing the size of your Excel files can have several benefits.  It can make them easier to email, (and save the ire of your recipients for bogging down their inboxes…), make them open more quickly, and reduce the chance of curious error messages/problems.

Here are just a few ways of putting your Excel files on a diet:

1.   First of all, check to see if a bit of housecleaning is in order: Are there any unused worksheets, irrelevant charts, or redundant calculations in your workbook?  If so, removing them is an obvious quick fix.

2.   Delete any unnecessary formatting.  If you have rows of blank cells that are formatted (color, lines, etc.), doing away with this needless bling can be a help in your efforts to slim down.

3.   Compress any graphics in your file.  That beautiful company logo may look great in your workbook presentation, but it may be hogging unnecessary space.  Just double-click on image, go to Compress Pictures and choose the Email (96ppi) option.  This can make a surprisingly huge difference!

4.   Zip your files.  Although zipping Excel files does not have as much effect as in did years ago, the application WinZip can still help, especially if you are using (as is the case with some companies) Excel 2003.

These techniques are not all-encompassing, but they are a good start if you wish to put your files on a diet (speaking of which, maybe I’ll have a salad for lunch…).