Often we work with large data sheets and it becomes tedious if you want to compare data / formulas etc. You can try split (menu > window > split or alt+w+s) to break the sheet in to two. But there is another cool thing you can do, open a new window for the spreadsheet. Just press Alt + w + n (menu > window > new window) to open the same spreadsheet in 2 windows. Any changes you make in one window are immediately visible in another window(s).

Bonus tip: you can do the same thing with WORD and POWER POINT as well, cool eh?

One of the most frequent tasks for any manager is “planning”, be it putting together a hiring schedule or designing a jumbo jet, it all starts with a simple project plan and gantt chart is simple and intuitive representation of the same. But how to make a gantt chart in excel without writing too many formulas or adding conditional formats? Do not worry! with the simple trick we are going to learn today, you will be able to “gantt in 60 seconds”

For our example purposes, we will look at a fictitious project plan shown below:

Even though you can use this trick to pretty much any data format, it works better when the project plan is structured around how I represented it above.

Now lets build a gantt chart in 60 seconds, get your stopwatches out and get, set …. GO!

1. Create a bar graph

Select the data part of your plan (ie all the cells except header row in the above table) and click on chart icon in excel. Select “bar chart” as chart type and “stacked bar 2d” as sub-type (2nd left on the top row) as shown here.

Click finish. At this point your gantt chart should look like this:

2. Transform your bar graph to gantt chart

Now we will convert this stacked bar graph to a gantt chart by using chart formatting options.

• First we will reverse the way data is charted, so that it looks like a gantt chart. For this we need to right click anywhere on the “y-axis” of the graph and select “format” option. Go to “scale” tab and check “Categories in reverse order” option.
  

  Click ok, now out gantt chart should look like this:

  

  btw, what is the time on that stop clock, 34 seconds, well, thats just fine, we have got plenty of time to spruce this up.

• Now, lets get rid of first data series so that our graph looks more like gantt chart. Select the first data series of your chart (should be in violet blue color ), right click and go to “format data series” option.
  

  Select “none” for “border” and “area” options in the “patterns” tab. This will make sure that the first series is invisible, so we see second data series floating on the chart, thus making it look almost like a gantt chart.

  

  Go to “data labels” tab and check “category name” option. This will make sure our gantt chart will show labels (but on the now invisible first data series)

  

  Click ok, at this point our gantt chart should look like this:

  

• Finally we will adjust labels and backgrounds to convert this to a perfect gantt chart
  1. First lets remove the legend box on the right by selecting it and hitting “del” key.
  2. Now, lets also remove the y-axis since labels are visible on data-series 1. Just click on the y-axis (or category axis) and hit “del” again.
  3. Lets adjust the alignment of the data labels on series 1 so that they are properly visible. Right-click on the data labels and select “format” option. Go to “alignment” tab in the dialog and select “Right” for horizontal alignment and “inside end” for label position. This will ensure that our data labels are right aligned and shown at the end of data series 1, ie closer to the actual series 2 (duration of the task). See the below screen cap for more help.

     

  4. Lets also remove the plot back ground to remove the annoying grey color from our gantt chart. Just click on the grey color anywhere and hit “del”. While we are at it, you can also change the line color of x-axis (the days) to white or transparent to reduce the eye sore.
  5. Finally, lets adjust the plot area size so that we can read all the data labels and everything looks normal.

  At this point our gantt chart should look something like this:

  

  If you still have few seconds left, you can tweak the chart format to make it look better. I had 3 more seconds left, so I tried this

  

Bonus tips for enthusiastic excel experimenters:
1. Adjust the grid line format to make them more subtle
2. Select a particular task’s data point and change its color to emphasize progress / stalled statuses
3. Enhance this to add another column with no. of resources (or difficulty etc.), add this to the stacked chart and make it invisible just like series 1, but show the data labels.
4. You get the picture… so start gantting…

Also read:
Learn how to create project plans / gantt charts using conditional formatting
Create art grade excel charts with these 73 designer templates
Other uber cool excel tricks to make your colleagues zealous and your boss happy

After seeing DI’s Open Your Favorite Websites Directly from Windows Vista Start post, I thought why not use Windows XP Run dialog box as a search box. So here is a simple trick that can enable you to open your favorite sites directly from XP’s run dialog box.

Using this trick you can type “g bbc” to directly open BBC website in firefox / IE.

First step is to create a batch file with the following text:

For Firefox users:
"C:\Program Files\Mozilla Firefox\firefox.exe" "http://www.google.com/search?btnI=I'm+Feeling+Lucky&q=%1"

For IE users:
"C:\Program Files\Internet Explorer\IEXPLORE.EXE" "http://www.google.com/search?btnI=I'm+Feeling+Lucky&q=%1"

Save the file anywhere (preferably in C:\Program Files) with a name “g.bat”. The above one line of code essentially starts Firefox or IE and requests Google to fetch your favorite site using “I am feeling lucky” feature. The “%1″ towards end tells windows to get the first parameter followed by “g” and passes it to Google.

Before you can use this you just need to modify your environment variables. For this:

1. Right click on “my computer” and select properties
2. In the dialog box go to “advanced” tab and click on “environment variables” button

3. Select “path” from system variables and click “edit” button

4. Append “;C:\Program files” (if you have saved the batch file in some other directory mention that path here) to the path in the end (dont forget the ; before C: )

5. Click OK all the way back.

Now you can use run dialog box as Google search bar with “I am feeling lucky”, You can use the same trick to do searches on ebay, amazon, imdb, yahoo or wikipedia or anything else.

Do you know that you can use excel find - replace dialog to change cell formats?

Often we prepare a report or project plan and our boss or someone else in the team feels that all the red color cells need to change to blue or something similar, worry not, you can use find-replace (ctrl+h) to do this for you.

I have created a small video demo on how to do this, check it out yourself

If you are not able to see the video:

1. Press Ctrl+h
2. click on “Format…” button on “find what:” area and specify the source format
3. click on “Format…” button on “replace with:” area and specify the target format
4. Select “replace all” or “replace” depending on your need
5. Smile, you have just automated a mechanical and boring task

add to del.icio.us | add to Stumble

How many times you created a chart in Microsoft excel and formatted it for minutes (and sometimes hours) to reduce the eye-sore?

Well, I will tell you my answer, its 293049430493 times

Worry not! for you can become a charting superman (or elastigirl) by using these 73 free designer quality chart templates in literally no time (well, almost)

These templates will take care of typical formatting activities like,

• Remove that ugly Grey color background from the chart
• Change the default grid line format from intrusive solid black to a duller shade of dotted Grey
• Adjust the fonts (to verdana in this case), remove annoying chart auto-font-scaling
• Move the legend to a meaningful location and adjust its size
• And, ofcouse, fix the colors

so that you, the user can focus on your data and not on “why in the world anyone would design a default format like this…”, so go ahead and unleash the charting pro in you.

Download the free MS Excel chart / graph templates

(I have put them in 3 separate excel sheets):

or, since you are so good, download one zip file, quick and easy!

If you are wondering how to use these templates, scroll all the way down the post

1. Bar / Column Chart Templates:

(29 of them)

2. Stacked Bar / Column Chart Templates:

(22 of them)

3. Pie Chart Templates:

(22 of them)
Even though I seldom use pie-charts (since they hide more than they show and all that) I know a lot of people do use them and hence here they are,

How to use these templates?

• Method 1 - Easy and Quick:
  • Download the chart templates (download links at top and bottom of this post)
  • Copy both the chart you wanted and the “data used” portion
  • Paste in your workbook
  • Change the values, remove columns (or add them if you wish)
  • Modify formatting if needed
  • Be careful now, as your boss may feel zealous for your charting skills
• Method 2: Slightly geeky but works like a charm!
  • Download the chart templates (download links at top and bottom of this post)
  • Select the chart you want, right click and select “Chart type” from the context menu
    [note: for more detailed steps & how-to, look in the excel worksheets you have downloaded
  • In the dailog, go to “custom types” tab and select “User-defined” radio button (towards bottom left)
  • Click on “Add…” button, and give your chart-template a name that you can remember
  • When you are done, click ok, and the chart is now added to your user-defined-charts library
  • In future, when you want to use the chart, simply click on charts icon on tool bar, and select the chart type as custom -> user defined ->your chart name
  • Now, watch out as your charts start stealing eyeballs in the boardroom!

Finally we can say good bye to default chart formats and all the associated eyesore

Download the free MS Excel chart / graph templates

(I have put them in 3 separate excel sheets):

or, since you are so good, download one zip file, quick and easy!

Thats all, feel free to share this content with your friends by bookmarking it on del.icio.us | Stumble upon

1. F2 - Edit a cell, takes the cursor to the end of the cell
2. F4 - Repeat last action (for eg. if you have inserted a row, repeats that action again) - doent really work with paste through.
3. Ctrl+1 - Open “Format cell dialog”

What are your 3 most favorite excel key board shortcuts?