Category Archives: Excel

Excel: Reading List

We already 135 posts in this blog. That is a lot. You may have read few of these posts but not all. I write articles in random order covering all products. This keeps all types of readers interested.

Now the time has come to give you a snapshot of what was covered by specific topic. Here is a list of Excel posts – in the right order. You should go from first to last in the order shown below. This will increase your knowledge incrementally. Continue reading Excel: Reading List

Did you know? Pivot Tables can increase the file size!

The problem with large Excel files

Today I was conducting an efficiency optimization session for Risk Management team of a global bank. During the discussion, one of the problems we discussed was large Excel files. Although there are many reasons for large Excel files, one of the reasons is easier to manage and it is largely unknown. It relates to files containing one or more Pivot Tables.

Quick Summary

Pivot Table creates a copy of the source data and saves it in the file. This increases the file size and also slows down the open / close operations. You can ask Excel NOT to save the copy of the data and save on file size. Right click inside pivot, Pivot Table options, Data tab, clear the checkbox Save source data with file. That solves the file size inflation problem.

pivot table increase file size

Read more to understand when to use and when NOT to use this feature.



Continue reading Did you know? Pivot Tables can increase the file size!

Copy formulas automatically and accurately (even with blanks)

Imagine that you have 50,000 rows of data. You want to add a new calculated column. Adding the formula is easy. But copying it to 50,000 rows is nothing but frustration.

Three methods are typically used: All of them are inefficient!

Copy and Paste, Drag and Double Click. Drag is lengthy – so is Copy Paste. Double Click is DANGEROUS because it stops if the column on the left has a blank cell. And usually we have lots of blank cells – at least we have to assume so!

Of course, there is a solution – a magical solution!


Continue reading Copy formulas automatically and accurately (even with blanks)

How to create Table of Contents in Excel

The Problem

How often have you received complex Excel files from others which have many worksheets, some color coded (but you don’t know the meaning of the color), some having similar names …lots of data, calculations, pivots, charts … and then you face a simple problem. Where do I start? Which areas am I supposed to view? The first sheet which opens is the sheet and the area of the sheet which was open at the time of last file save. Which does not necessarily mean that is the area of primary interest. It is quite confusing.

The Risk

It is not just confusing. It is dangerous. The sender may have intended you to look at a particular sheet / range but in reality, you end up looking at some other area – and take some decision. This is a communication gap – which is difficult to bridge. This can lead to misinterpretation, erroneous decision making and chaos.

The solution: Create a Table of Contents for complex Excel workbooks

The problem: Excel does not provide a built-in feature to create a TOC. But that does not constrain us. As we saw in the last article, we can use a Hyperlink within a document to create a set of links. By default, Excel allows you to create a hyperlink to existing sheets. In practice, sheet based navigation may not be enough – because, the same worksheet may have multiple areas of interest. Therefore, Excel allows us to create other named items which can be used as hyperlink targets. You can create Range Names.

How to define range names?

Select any range in a worksheet. Go to the name box, type a name and press ENTER. No spaces allowed in the name. Now these names appear in Hyperlink Dialog. how to create a table of content in Excel by Dr. Nitin Paranjape You can also create and edit names using Formulas tab – Name Manager.

Creating a TOC in Excel

Here are the steps:

  1. Rename each sheet – never keep the defaults to Sheet1, Sheet2 and so on
  2. Delete empty sheets
  3. Identify ranges which are important for creating the TOC and assign names to them.
  4. Create a new sheet called Contents
  5. Create the TOC by typing relevant text
  6. For each TOC item, insert a hyperlink either to a sheet or a defined name
  7. Just before saving and closing the file keep the content sheet active
  8. Close the file

Now everyone can use the TOC we just created to browse the file in an informed manner.

Creating a “Back” link

In order to complete the navigation, it is a good idea to have a BACK link at each of the hyperlinks. This is easy. Type Back in a cell just above the named range and insert a Hyperlink to the Contents sheet. Copy this cell to all hyperlink targets.

Remember to update the TOC

Whenever you add or delete more data or sheets, consider if you need to edit the Contents sheet the reflect the changes.

International Phonetic Converter using VLOOKUP

The problem

Have you ever tried any of these?

  • Calling an airline to change the booking
  • Calling a helpdesk for some troubleshooting
  • Calling your bank for some transaction
  • (or similar situations…)

In each of these cases, you have to tell lot of information accurately… PNR number, mother’s maiden name, your name, your email id …

The operator never understands your spellings correctly… and then we go into phonetic innovation…

“e for eerie … sorry.. e for eon, f for (not the first word which comes to your mind wlemoticon-winkingsmile.png) … f for for… n for Nitin (your own name always comes to your mind first), s for psychiatry … “

it is hilarious Winking smile

But it can be frustrating as well as detrimental if wrong information goes to the other side. We desperately need a better way to communicate accurate spellings to the other party.

The solution – International Phonetic Converter

To avoid this, there is an accepted international phonetic convention – which all operators know. Ships, Airlines, Armed forces – everyone uses it so that there is no confusion and mishaps.

We can also use it… but you many not have it with you when you are calling the helpdesk.

So I created a small phonetic converter tool to solve this problem. It is a simple Excel file.

Click here to open it ( )

Just type your text and it will tell you the equivalent words.

International phonetic converter using Excel Vlookup

This will NOT work on some mobile phone browsers.
Mobile friendly version coming soon…

How does it work?

It is a simple VLOOKUP based worksheet.

You can download the Excel file from here

The link given above is showing the web editable version of this workbook using SharePoint.

Mobile friendly version coming soon…

This is one example where applied knowledge of Office can help you in many other aspects of life.  Try it out and let me know your feedback.  Thanks!

Use PowerPivot instead of VLOOKUP

This time I wanted to try a new style of writing.

I created a presentation instead of the usual document like style.

PowerPivot vs Vlookup

Download presentation and view

Download the presentation (300kb) from here

Download sample file with data and Pivot from here

View on SlideShare

Tell me if you like this style…

Introducing PowerPivot – do you need it?

If you use Pivot Table, then you need PowerPivot! Read on to find out why.

If you have never used Pivot Table, you should not read this article. You must learn Pivot Table first.

PowerPivot is an add-in which works on Excel 2010 / 2013 (Professional Editions only). For 2010 you have to download and install it from

For 2013, it is a part of the base installation. However you still have to activate it. File – Options – Add-Ins – Open Dropdown – COM Add-ins – GO. Enable the checkboxes next to PowerPivot, PowerView and Inquire. We will discuss the other two items later. This time we focus on why PowerPivot.

Finally you should see a PowerPivot tab in Excel.

PowerPivot tab

What is PowerPivot

It is a new way of creating Pivot Tables for analyzing data. It eliminates all the shortcomings or limitations of traditional Pivot Tables… and adds more features.

Limitations of the good old Pivot Table

Large data = slow performance

Pivot Tables cannot handle very large amount of data. Even with few hundred thousand rows it starts to become slower and slower. Often you have to wait for a long time for a drag and drop of a column to finish all the calculations. Opening such large files is also extremely slow.

Row limit of Excel sheet

Maximum of  1,048,576 rows… just over 1 million. That is the limit. If your data is bigger than this, you must use some external database and then connect Excel to it. Of course, even if it works the usage will be extremely slow.

In addition, we have dependence on the external database – which makes sharing the files more difficult.

Pivot Table works on one tabular block of data

If your data is originating in multiple blocks of information, Pivot Table cannot handle it.

For example, you have transactional data of 100 rows containing the columns
Product ID, Qty, Price, Total Cost

You also have a master table containing Product Id and Product Name.

Pivot Table cannot be told to use data from the Transaction table along with the Product Master.

The workaround is to use VLOOKUP…

The VLOOKUP problem

That brings us to the next limitation of Pivot Table. Continuing with the example above, we will have to add a new column in the transaction table which uses VLOOKUP to lookup the product ID from the Product Master table and get the value of Product Name.

VLOOKUP function is now 10,000 times making the workbook even more slow to respond. Furthermore, there are only a few products. However the name of each product repeats thousands of times in the transaction table – leading to increased file size reducing the performance further.

It is not uncommon to have multiple VLOOKUP based columns in a single Pivot Table.

Yes, there is a workaround – Copy paste the VLOOKUP column as values immediately after the formula is pasted. This way we retain the desired output but eliminate the overhead of VLOOKUP. Although this works, when data changes (more data is added) you have to add more VLOOKUPs manually and then convert them again to values.

This is repetitive and cumbersome as well as error prone.

Either flat data or cube data

Many people have not explored Pivot Table as a front end to an SQL SERVER cube. Pivot Table integrates beautifully with SQL server cubes and exposes all features of the data warehouse elegantly. However, there is a limitation.

You cannot combine some data from the cube along with some data which is outside the cube. In practice, this type of requirement is common.

The solution: PowerPivot

PowerPivot is designed to solve all these problems and more – much more.

Here is the concept.

Stores data inside PowerPivot. Not Excel worksheets

Whether data originates in Excel or other places, PowerPivot stores it. Data is NOT stored in regular Excel sheets at all. This way, the storage mechanism can be made more elegant, fast and scalable.

Diverse data sources

Powerpivot Table Import Wizard

It can create a Pivot Table using data originating from different sources. For example, transactions data can originate in a database whereas the Product Master could be a simple Excel table.

Handles millions of rows of data without inflating the file size

PowerPivot can store millions of rows of data. Of course we are worried about file size bloating up. But don’t worry. It stores it in a highly compressed manner. You can actually try this out. Take a large Excel file with data contained in a worksheet. Note the file size. Now import the same data into PowerPivot, save the file and check out the difference. You will be very surprised. The file size can reduce anywhere between 4 to 100 times depending upon the pattern of the data (compressibility).

Eliminates VLOOKUPs by creating relationships

Excel is not a database. We were using VLOOKUP as a workaround to create relationships. PowerPivot – like a database – allows you to specify which field in the transaction file relates to (is same as) the field in the master file. This type of MANY-to-ONE relationship eliminates the need for VLOOKUP and associated performance penalty.

Powerpivot Create Relationship

More functions

As though this was not enough, PowerPivot adds more functionality which is completely missing in Excel.

    1. Ability to create hierarchies from flat data (Category, Group, Product, SKU)
    2. Ability to use Picture URLS (for use in PowerView)
    3. Creating Flat Pivot Tables
    4. 70+ new functions which extend the capability of Excel to something similar to sophisticated cube based formulas (without the need to learn complex MDX syntax)
    5. Time intelligence functions are very powerful and extremely useful
    6. Ability to merge cube and flat data
    7. Ability to refresh all data sources
    8. Calculated columns for additional customization
    9. Default field sets, and many more…

In the next article we will see how VLOOKUP can be eliminated using PowerPivot.