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!

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…

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!

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…

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!