Gantt Charts in Excel

When used correctly and consistently, Gantt Charts can be invaluable tools to managers, analysts, and employees in the front lines.  So what, exactly, are Gantt Charts?

Well, first of all, they are a comparatively simple bar charts that illustrate and track the evolution of a project.  Developed by a management consultant by the name of Henry Gantt back in the early 20^th century.  These respected charts clarify the start and finish dates of a project’s elements, and can be easily grasped a quick glance.  Groundbreaking at the time they were developed, Gantt charts are considered mainstream today.

Along with Microsoft Project, there are several specialty software solutions for producing these charts.  For many, however, good old Excel handles this quite satisfactorily, thank you.  Here is what you should do:

1.     Open a New workbook in Excel (version 2013 is recommended)

2.     Type in “Gantt” in the Search for online templates box

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 and aesthetic needs.  Please 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, as the default, IMHO, is rather ugly.

With these easily created and used 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!

Text + Formula

Better and more powerful communication is always a worthy goal.  This is, of course, just as true in Excel as in other business venues.  One way to do this is to Mix Text with Formulas. 

By using the Concatenation (simply done with Ampersands) feature along with Formulas, you can build interactive messages within your reports, making them easier to understand and less likely to incur human error.

Using the example illustrated below (click on it to enlarge it), let’s say that you have a table of data and a formula that returns the sales for the 4th quarter in cell G6.  The formula in G6 interacts with the dropdown box in cell C4, (dropdowns are a snap to create using a Validation list…), to display the 4th Quarter Sales by Rep.

You could stop there and hope that the users of the report understand what is being shown, or you could create a piece of Excel Magic in cell F6. 

As you can see in the formula bar, a Text string is used in conjunction with the name being shown in the dropdown box in C4:  =“4th Quarter Sales for ”&C4&“:”

The outcome is an Interactive Message that works with the dropdown box and the results of the formula used in cell G4.  Note:  Leave a space after the end of the text string so as to mimic a proper sentence.

As illustrated, try using ampersands with text strings combined with formulas in your next report.  Your users will thank you!

Self-Expanding Charts Revisited

Sometimes you find a technique that is So Useful and So Easy that it may make you laugh.  I wrote about this topic a couple of years ago and since it is such a vital, albeit modest trick, it is worth revisiting and illustrating.

As is often the case, many Excel users of all levels spend a great deal of valuable time doing things that can be automated.  It may keep the unenlightened ones busy, but there are better ways to find self-worth.

Take Updating Charts for instance.  If you have worked in a responsible position as an Excel professional for an appreciable length of time, you undoubtedly have found yourself spending time doing the repetitious and tedious task of keeping the charts in your reports in sync with your revised data.

One painless way to avoid having to do this is to create a Self-Expanding Chart that requires no effort to maintain its currency.  If you using Excel 2007 or later, it couldn’t be easier.  Here is what you do:

1.  Create your Chart as you normally do
2.  Select any cell that contains the data that is used by the chart
3.  Choose Insert / Tables group / Table to convert the range to a table

4.  Validate the process by adding another record in the table

In the illustration below, you can add the July Sales data, and the chart will automatically update itself with a new column.

How utterly Cool is that!  Like I said before, sometimes things are so easy, that it makes you laugh!

Converting Numbers to Formatted Times

Date and Time data can come in many different formats.  This is particularly true when you are downloading records into Excel from other database programs, such as SQL, Access, Adabus, etc.

For instance, what do you do if your time data is expressed in an overly simplified and decidedly cold format?  For the sake of example, let’s say 531 would represent 5:31, 1112 would represent 11:12, and so forth.

Now any razor-sharp geek is likely not going to mind this austere method of characterizing time, but most typical end users of reports may not be so forgiving.  The solution, however, as illustrated below, is not too difficult.

By using an IF statement and some Text functions in Excel, a formula can be created to convert your bleak data into a user-friendly format.  Although at first glance the formula below may appear quite complex, if you take a moment and break it down into its parts, you will see it is not that challenging.

To save time, however, you can just copy the formula and plug it into your spreadsheet.  For this example, you would put it into cell B2, double-click the handle, and Voila, Time Conversion!

 

If you have 10 records or 10,000, they will all be converted at the click of a mouse.  Humans like consistency of data representation, so if you ever need to convert some somber time data, this may be just the ticket!

The Mouse Rules! (Sometimes)

If you have been a reader of this blog for any length of time, you know that I am a big advocate of using Keyboard Shortcuts in Excel.  That being said, there are certain times when The Mouse Rules!

Let’s take a look at Five Great Shortcuts using the mouse in Excel:

Move Selected Cells by Dragging the Border

1.      Select a range of cells

2.      Drag the thick border line to move the cells

3.      Celebrate that this is faster than keyboard shortcuts!

Autofit Column Widths

1.      Select the columns you want to adjust

2.      Double click on the column letter separator line

3.      Bamm!  Perfect Fit!

4.      Bonus!  You can also use this technique to adjust row heights

Alt+Click for Stock Quotes

1.      Enter a company stock symbol in a cell (SBUX for Starbucks sounds good to me at the moment…)

2.      Alt+click the cell to launch the research pane

3.      Convenient way to follow your favorite stocks!

Select Non-Contiguous Ranges

1.      Use Ctrl+Click to select non-contiguous cells

2.      Many uses, such as special selections for formulas, formatting, editing

3.      How Cool is that!

 Grab the Handle for Autofilling

1.      Select a cell or range of cells

2.      Grab the Handle (small black square at the lower-right

3.      Drag horizontally or vertically to autofill the data (works on numbers, days of the week, and even Custom Data such as Week 1, Week 2…, Employee 1, Employee 2, etc.)

4.      Bonus: If your range is aligned with an adjacent range, simply Double-Click the handle!

 Yes, Keyboard shortcuts totally Rock, but sometimes The Mouse Rules!

Excel Uses by Real People

The versatility of Excel lends itself to nearly endless applications.  As I am sure most of us would agree, the potential uses are only limited by the user’s imagination.

In a very large recent poll, real people were asked what they use Excel.  Hundreds of responses were obtained, and many of them are quite interesting.  Some of them may, in fact, be inspirational.  Here is a sampling of 20 of the more interesting ways Real People have used or are using Excel:

 

1.      Tracking pupils' attainment and progress

2.      Tracking personal weight and blood sugar levels
3.      A version of a Boggle board
4.      Reservation databases displaying room availability
5.      Calculating the stability of material on the deck of a ship
6.      Queue calculator for medical patients
7.      Solvers for word puzzles
8.      Timesheets and labor calculations
9.      Closing quote data for over 1000 companies
10.   Randomly generating passwords
11.   Tracking sports data
12.   Recreated the format of a popular quiz show
13.   Managing meeting rooms availability
14.   Pricing engine for tour groups
15.   Genealogical research
16.   Tracking weight loss
17.   Video teleprompter for giving presentations
18.   Recipe scaling template
19.   Managing personal academic studies
20.   Creating attractive custom labels

Fascinating, isn’t it?  If you have any unusual ways that you have used Excel, I would love to hear about it.  Send me a note at ExcelEnthusiasts@gmail.com.  All the best!

Round like a Circle

On the face of things, Precision seems to always be preferable.  For instance, 12.324 is more precise that 12.3.  There are times, however, when precision is Not desireable, and may even not make much sense.

For example, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Clearly, you can’t find that .92 person (although one of my ex-brother-in-laws comes to mind…), so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.

 

There is always formatting in Excel that will make your numbers Appear to be rounded, of course.  If you are dealing with numbers representing currency that have four decimal places, you would obviously want to express the results in no more that 2 decimal places, so you can format for showing exactly that!  The trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. 
 

So what do you do?  (Trumpets please…)  Use the ROUND function!  The syntax for the the ROUND function is:  ROUND( number, digits )

Four Examples are in Order:

In the worksheet below, Column B contains the rounding functions:

1.      B1 has:  =ROUND(A1, 2) and therefore rounds to two decimal places.

2.      In B2, =ROUND(A1, 0) is being used to round to zero decimals.

3.      In B3, more extreme rounding is being achieved by using =ROUND(A3, -1)

4.      B4 uses the same function as B3 and applies it to B4:  =ROUND(A4, -1)

Note:  If you always want Excel to round up, you can use the ROUNDUP function.  In our illustration, this would result in B3 being 30 rather than 20.  The ROUNDDOWN function does (no surprise) just the opposite.
 
Sometimes Precision is the enemy.  In such cases, use ROUND!