Skip to main content
Sign In |
  Help (new window)

Dr. Nitin's KLOG

Go Search
Home
  

Other Blogs
There are no items in this list.
Dr. Nitin Paranjape's web site > Dr. Nitin's KLOG
KLOG means Knowledge Log.
KLOG is the term I use to differentiate my posts from BLOGs.
KLOG is always guaranteed to contain knowledgeable, well researched and immediately usable information.
Personal comments, gossip, speculation, jokes are strictly avoided.

7/9/2007

How to overcome the 65k (or 1 million) row limit and analyze data in Excel?

This problem has been haunting people for a very long time.
The recent increase of the row limit to 1 million+ rows may have solved the problem. But sooner or later you are bound to have data which exceeds this limit as well. So what we need is a better approach which works across Excel versions.

Here I am assuming that you don't want to scroll through thousands of rows but you want to create a Pivot Table to analyze the data.

Here is the summary of steps involved.

  1. Split data into multiple sheets.
  2. Create a named range for each block of data
  3. Save the Excel file
  4. Create and ODBC data source based upon the Excel file.
  5. Open a new, blank Excel file
  6. Create Pivot Table based upon the Excel ODBC data source
  7. Edit the query manually and create a UNION based select query which combines all the blocks of data
  8. Make a pivot table directly (don't try to get the data into Excel sheet, because it will not fit).
  9. Pivot table has no row limit!
Posted at 1:04 PM by Nitin Paranjape | Category: Excel | Permalink | Email this Post | Comments (0)

3/31/2007

How is the Office 2007 product?

This question is very frequently asked to me. As everyone knows how much I like Office, everyone wants to know what I feel about Office 2007.

   

Here are my views:

  1. Needless to say, it is a great product.
  2. There is completely new interface (menus and toolbars) for Word, Excel, PowerPoint and Outlook editors.
  3. The interface for base Outlook, InfoPath, Visio, Project, Publisher is still similar to earlier version.
  4. Initially, say for few days, you may feel out of place because you will not find the usual menus and toolbars. But soon you will get used to it. Nevertheless, it is a negative aspect.
  5. However, this negative aspect has a huge positive side to it. In older versions of Office, there were too many menus and toolbars and dialogs. These went on increasing with every version. Nobody really noticed most of them... simply because it was too difficult to notice them! Therefore, many great and useful things were never used by the world. In Office 2007 all those nice things have been brought to the surface. Therefore, you are bound to find many useful gems while you work.
  6. While working on Office 2007, it is a good idea to keep thinking what you want to do currently, and then look at the menu options and click on the most logical one. At any point, there is only ONE TOOLBAR. Now it is called a Ribbon. And surprisingly all relevant options will be available there. Once you consciously follow this method, you will start becoming more productive very quickly.
  7. There are lot of new features added in Office 2007. However, while you use it, don't worry about what is new in 2007. Worry about what is new (and useful) to you.
  8. Finally, the most important thing is this....
    You will need to rethink every action you are currently habituated to. Because, unless proved otherwise, there is a better, more elegant way of doing it. Just find the right way and change your (mostly bad) habits. That is called productivity!

More details on this topic later....

Do post your feedback.

Posted at 9:58 PM by Nitin Paranjape | Permalink | Email this Post | Comments (0)
Welcome to my KLOG

Well, I am starting to write again after a gap of 3 years. As some of you may know, I used to write a weekly column in Express Computers for over 6 years (700+ articles). It stopped in 2002.
Some recent articles can be found at www.expresscomputeronline.com/techforum.shtml

Now I am back. Well, thousands of people write 'blogs'. I did not jump into the bandwagon. Now I am starting off but with a twist.

I will be writing Knowledge Log - KLOG. (Don't know if someone has coined this term already!). What does that mean? It means the same thing my articles were known for earlier.

Each piece posted here will be based upon the following principals:

  1. The content is well researched and tried and tested to maximum possible extent.
  2. The purpose is to simplify technology and make it possible to use it fully.
  3. The approach will be practical, common sense based and easy to understand
  4. The objective will be to reduce the readers some effort of evaluating and trying out the topic in question.
  5. The content will try to focus on highly useful but often ignored functionality in the related technology

When I used to write every week, it was a great thing because it kept me on my toes. Now I hope to get back into top gear to make that happen again.

Your feedback is most welcome.

Dr. Nitin Paranjape


 

Posted at 9:57 PM by Nitin Paranjape | Permalink | Email this Post | Comments (1)

 ‭(Hidden)‬ Admin Links