Picture Your Access Data

Access 2013, as well as the previous versions of Access are excellent applications to house your company’s database.  In the latest version, you have the ability to build a Web App Database where data can be retrieved by various other programs including, of course, Excel.

Although Access is a powerful database tool, you may wish to be able to Visualize you data in the form of a well-constructed Chart.  For instance, let’s say that you have your net sales quantified by region, and you would like to slap together a quick Pie Chart for your presentation to the board.  Here is where Excel is your app of choice!

With Excel 2013 open, do the following:

1.   Open your Data ribbon

2.   Click on the Get External Data down arrow

3.   Select From Access

4.   Click on the Access File you wish to view

5.   Choose the Table you want to import and click OK

Your Access data will then be imported in a contemporary Excel Table from which you can select the data from which to make your Pie Chart.

Simple as that!  Importing and manipulating your Access data in Excel has never been easier.  Totally Cool!

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!