Wednesday, November 30, 2016

Gantt Charts

Gantt Charts have been around for a long time, and there are many software solutions available for companies of all sizes. Along with Microsoft Project, there are several specialty software solutions for producing these charts.  For many, however, good old Excel handles this task quite well.

You say you are not familiar with Gantt Charts?  Here is what they are all about.
First of all, they are a comparatively simple bar charts that illustrate and track the evolution of a project.  They allow the user to Juggle time and resources to obtain desired results. Developed by a management consultant by the name of Henry Gantt back in the early 20th century.  These robust charts clarify the start and finish dates of a project’s elements, and can be easily grasped at a quick glance (the hallmark of an effective chart). Although they were considered revolutionary at the time old Henry developed them, Gantt charts are considered mainstream today.

When used correctly and consistently (some individuals have been known to fudge with the results in the past), Gantt Charts can be invaluable tools to managers, analysts, and employees in the front lines.  Here is how to create one in Excel:

1   1. Open a New workbook in Excel (version 2013 or later is recommended)
2   2. Type in “Gantt” in the Search for online templates box
C   3. Choose the Project Planner template and click Create

The Project Planner Gantt chart template will then open, and you can customize it to suit your business needs.  Note: You can access Manage Rules under Conditional Formatting on the Home ribbon and revise the formatting and range of the chart.  I recommend that you modify the formatting, which can give you some control over the aesthetics.

With these very accessible Excel Gantt Charts, you can quickly see where each activity is according to plan.  Give it a try the next time a Big Project comes by, and enhance your Juggling skills!

Wednesday, November 23, 2016

Database Best Practices

Database Best Practices is always a worthy topic to review.  Assuring good database management includes several of these Best Practices and can they can take many forms.  One important Best Practice goal is to control and Eliminate Blanks in any of our database records.

Conditional Formatting is a familiar topic, and most of us have probably used it occasionally to highlight important information in our workbooks.  A unique method of using this tool takes a Reverse (or Negative) approach to this, however.  It is easy to see how this technique can significantly aid the goal of eliminating blanks.  Let’s look at how Reverse Conditional Formatting can be used to quickly highlight potential discrepancies.

For instance, let’s say you have a worksheet in which you are entering data (it may be numbers, text, or mixed) in a field, and you want it to be Very Apparent if a cell within that column is Blank). Here is a convenient way to use conditional formatting to do this:

In our simple example, let’s say you are going to be putting data in the short range of A1:A16.

1. Your first step is to apply a Fill Color to your range (A dark red, blue, or gray are good choices.)
2. Then select your range and go to Conditional Formatting / New Formatting Rule
3. Choose Use a formula to determine which cells to format and put the following:

4. =IF(NOT(A1=""), TRUE, FALSE)
5. Finally, for your Format, use Fill / No Color

Now whenever you place data (numbers, text, or mixed) in one of your cells in the range, the dark Fill Color is Cleared!

The cells that do NOT have data entered will retain the Original Dark Fill Color which will most certainly draw your attention to a possible problem.  Using this approach will help assure that you are maintaining a clean, viable database that will in turn enable you to extract accurate information, and that obviously, is what it is all about…

Tuesday, November 15, 2016

Templates for Real Work

Convenience means saving time and money. It also means a reduction in stress, and I am sure we all agree, that is a very good thing.

When it comes to Excel, using Custom Templates is one way of achieving such
convenience. 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.

Any Excel user can easily find thousands of Office.com prefab templates by simply going to File/New.  Choices range 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.

In addition to all the terrific prefab templates, you can create your own for your unique work environment. For instance, let’s say you work in an accounting department and you prefer to use the Currency option for formatting your worksheets. You can set this up manually each time, or you can utilize the custom Templates feature found in any remotely recent version of Excel.

A Suggested Approach for Our Accounting Professional:
1.   Open a blank worksheet in Excel
2.   Go to the Styles gallery on you Home ribbon
3.   Right-click the Normal style and choose Modify to display the Style options
4.   Click the large Format button, and make the new formatting choices you desire

Once your number (as well as any other changes) format changes are made, simply click on the File tab and choose Save As. Type in the File name of your choice (e.g. AccountingTemplate2016) and for the Save as type, choose Excel Template (*.xltx)

Now whenever you wish to create a new workbook, your new AccountingTemplate1 will be readily available under PERSONAL templates when you open Excel.

The Convenience of using Templates.  Save time, money, and unwanted stress today!

Tuesday, November 8, 2016

Dates, Dates, Dates


No, we are not talking dates and figs today…

As most experienced Excel masters will tell you, working with dates in Microsoft Excel can occasionally be a maddening experience for many users.  Upon closer inspection, however, there are some excellent Date/Time functions built into Excel, and one of the most versatile is the (cleverly named) “DATE” function.

The syntax of the DATE function is =DATE(Year, Month, Day), therefore if you enter as follows, =DATE(2016, 11, 8), it will return today’s date of November 8, 2016.

What is distinct about the DATE function is its Flexibility.  It can, for instance, accept inputs from cells, functions, and calculations. For example, =DATE(2010+1, 6, 17) returns June 17, 2011. =DATE(2016, 11, 8+47) returns December 25, 2016 (Merry Christmas!). Pretty Cool!

So let’s say that you have an interactive What If report that has cell B1 with a continuously updated current date, (The =TODAY() function is an obvious choice), and cell C1 displaying the date which is a variable of numbers of years in the future based on the value you place in cell A1. Your formula in C1 would look like the following:

=DATE(YEAR(B1) + A1, MONTH(B1), DAY(B1))

If today is November 8, 2016 and you have the number 4 in cell A1, the above formula would return November 8, 2020.

The DATE function is worth taking some time to get to know. Make it a “Date” to remember…

Wednesday, November 2, 2016

Navigation and the Ultimate Mouse


As most Excel Gurus will tell you, there are a great many ways to Navigate in his or her Excel workbooks.  Navigating can be a bit of drudgery, especially if you are working with very large worksheets. Not a concern if you are working with a worksheet with 20 records, but If you are working with one with 20,000 records, it’s a Big Deal!

Now, if you’ve been reading this blog for a length of time, you may know that I am a huge fan of using Keyboard Shortcuts for navigation. In fact, here are some of the more common moves you can make without ever touching the mouse:

1. Control / Down Arrow: Takes you to last cell in column with data
2. Control / Right Arrow: Takes you to last cell in row with data
3. Control / End: Takes you to the last row, column and cell
4. Control / Home: Returns to cell A1
And, of course, there are some very clever moves with you can make with any Mouse, and even in certain cases the Name Box.

Speaking of the Mouse (this is not an advertisement, just an observation…), computer mice have come a long way over the years. In search of the Perfect Mouse, I have gone through countless designs and models from nearly all the major manufacturers. None are “Perfect” of course, but I recently acquired what I consider to be the Best Mouse I have ever used in Excel.

The mouse is the Logitech MX Master.  It offers smooth operation, customization options, and good ergonomics. What I particularly like about it, however, is the Thumbwheel that lets you scroll side-to-side.  This enables you to rapidly Navigate right or left on your worksheet, just like the ubiquitous scroll wheel lets you Navigate vertically.  The only drawback that gripes people, is that it is a bit expensive.

Whether using keyboard shortcuts, mouse tricks, or the latest in computer accessory gear, it’s all about Navigation.  Happy journeys!

Tuesday, October 25, 2016

Count (Dracula?)


No, we are not talking about the infamous Count Dracula this week, but since Halloween is next Monday, we most certainly are going to discuss the varied and highly utile Count functions (and have a little fun with Dracula along the way…)!

There are several major types of Count Functions. In summary, they are:

1. COUNT: Simply counts the number of cells in a range that contain a number
2. COUNTA: Counts the number of non-blank cells in a range (including those with text)
3. COUNTBLANK: Counts the number of empty cells
4. COUNTIF: Flexible counting function (this can be a little tricky, so we will explore with some examples)

The COUNTIF function is a Wickedly Useful, and sometimes Tricky (Hey, next week is Halloween), method to derive valuable information from your data, so we are going to look at some examples (I think you will find them a real Treat!)

The reason this function can be challenging to some users is because of the prevalent use of Quotation Marks within the formulas. The following examples will demonstrate how it works (Note: In each case, the formula uses a range named Data):

Count the number of cells that contain the first name of Dracula - "Vlad" (BTW, not case sensitive):
=COUNTIF(Data,"Vlad")

Count the number of cells that contain the words "Vlad" and “Igor”:
=COUNTIF(Data,"Vlad") + COUNTIF(Data,"Igor")

Count the number of cells containing Any Text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 3-letter words:
=COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "V":
=COUNTIF(Data, “V*”)

Count the number of cells that contain a value Greater Than Or Equal to 13:
=COUNTIF(Data,">=13")

Count the number of cells that contain a value from 13 to 33:
=COUNTIF(Data,">=13")-COUNTIF(data,">33")

With a bit of ingenuity, COUNTIF can indeed be a Treat to use. So, this Halloween, don’t forget the Count!  (I am sure Vlad will be pleased…)

Wednesday, October 19, 2016

Seeking Your Goal


Goals are, with little doubt, what keeps businesses focused on objective for which the company was founded and a guidepost for maintaining profitability.  The classic management tool that is built in to Excel is, of course, Goal Seek.

Goal Seek is, of course, a “What If” tool enabling scenarios. What If scenarios are essential instruments of analysis for nearly any business, and Goal Seek allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. Goal Seek can save you an enormous amount of work, and make you look good in the process (and we all like to look “Good”…).

The following is a very Simple Example showing how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the corner office big wigs. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Supposition: Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)
1) Insert the number of Expected Calls into Cell B2
2) Add the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

This tool can, of course, be used to keener advantage with more complex scenarios, but our example demonstrates the overall concept and use of Goal Seek.  This handy utensil can be very useful for the boardroom or any strategic venue. Another vital addition to an Excel Guru’s tool belt!