Removing colors, shading, and borders from the cells where is it not necessary would drastically reduce the size of a workbook. ( For example, the above picture shows borders even on empty cells.)

Selecting Clear > All from Edit menu would help you remove all formats applied to a cell along with any content.

2. Clear All Empty Cells

You need to select all empty cells and apply Clear > All or Delete them to remove any accidently applied formats.

To clear all empty cells:

1.   Go to the first completely blank column at the right of your data. Select the column by clicking on its letter. Hold Shift+Ctrl and then hit the right arrow key until you have selected all the way out to Column IV. Select Edit menu and choose Clear > All or From Edit menu, select Delete.

2.   Go to the first completely blank row at the bottom of your data. Select the row by clicking on its number. Hold Shift+Ctrl and then hit the down arrow key until you have selected all the way down to Row 65536. Select Edit menu and choose Clear > All or From Edit menu, select Delete.

3. Reduce the workbook size by compressing pictures

You can tell excel to compress the images to reduce the file size. (This will force saving the images at lower resolution of 96dpi instead of standard 200dpi )

Select one or more images in a file and then:

1.   On the Picture toolbar (To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Compress Picture..

Note: If you do not see the Picture toolbar, point to View, then point to Toolbars, and then click Picture.

2.   Select each of the option shown below to see the difference.

4. Modify Pivot Tables settings.

If your workbook contains multiple PivotTables, all based on a single data source, Excel may create an intermediate dataset for each PivotTable, instead of using one intermediate dataset. This, of course, could increase the size of your workbook very rapidly.

You can also instruct Excel to not save your intermediate data tables in the same disk file with the workbook. This will make the size of your workbook file much, much smaller, but it will also require that PivotTables be refreshed every time you open your workbook.

Follow these steps:

1.   Run the PivotTable Wizard to create your PivotTable as you normally would.

2.   When you get to the final screen of the PivotTable Wizard (the one with the checkered flag on it), click the Options button to display the PivotTable Options dialog box.

3.   Clear the Save Data with Table Layout check box.

4.   Choose the Refresh on Open check box.

5.   Click on OK to close the PivotTable Options dialog box.

6.   Finish the steps in the PivotTable Wizard.

Try the same with other arrow keys or by holding Ctrl or Shift Alone to see the difference.

1. Click Start and select Default Programs (as shown in image below):

2. In the Windows that opens, click Set program access and computer defaults.

3. Click Custom option and and select Internet Explorer.

4. Click Ok.

For those who are not aware, here is an overview of Validation option available in the Data menu of Microsoft Excel. You can find few examples @ Microsoft here.

LetÆs go one step ahead, to see how to do this dynamically. I have taken a case with following lists:

Figure 1

I am assuming the following data sheet:

Figure 2

Now, we want the sub-type column to be filled in depending on Type selected and Price depending on the sub-type selected.

Been with such a task before? LetÆs see how dynamic data validation can come in handy.

Steps:

1. Having your list on a separate sheet can be more convenient if it needs to be updated at a later stage. I have created a separate sheet called Criteria and added my lists to it. Refer Figure 1 above.

2. Now, your list range should be named. For e.g.: For the list, Type, select A2:A3 ignoring the column heading, from Insert menu, choose Name -> Define. Use the column heading ôTypeö as the name and click Ok. Similarly name other list ranges too using their column heading as the name.

3. Now, move to the sheet and select the cell where you want to use this list. In our case, B2 of ôDataö Sheet. Then from, Data menu, select Validation.

4. In the validation Criteria, select List, and in the Source box, type ô=typeö without quotes.

5. Click Ok. This would fetch all values from Type range in Sheet ôCriteriaö and fill them up in a drop down box at B2 cell ôDataö sheet.

6. Now, we want the column C to list down the values depending upon the value select in column B2. For this, select the C2, from Data menu, select Validation. In the validation Criteria, select List, and in the Source box, type ô=indirect($B$2)ö without quotes. Click ok and ignore any error messages displayed.

Figure 3

Now, what does Indirect($B$2) do ?

The function Indirect, would parse any text provided and use that as reference. Here, in our case, the text select in cell B2 would be used as a reference in cell C2.

If you want set validation for multiple cells, select cells in column C, with the first cell has the focus. If I have selected the range C2:C10, I will make sure the cell C2, has the focus. Then access, Data > Validation and enter the Source as ô=indirect($B2)ö. This would automatically change the following cell reference to the cell adjacent to it.

7. Follow Step 6 to refer column C likewise to fill in price info.

8. Test the validation by selecting the values.

Figure 4

Using VBA

You can also fill in validation list dynamically using VBA. Every sheet would have a SelectChange event within which your code to dynamically validate should work.

Here is the dynamic validation code sample that would fill in values:

Cells(activecell.row,2).select æ Assuming the 1 is where we have our content
Myval = <your list generated at runtime>
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myval
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With

Download a sample excel file from here

Cheers

Though I have heard about this feature, I never used it until I had a task of splitting up an address column. Microsoft ExcelÆs Text to column feature allows you to split data to multiple columns easily.
LetÆs say you have thousands of names in format ôFirst, Last Nameö in a single column and your task is to sort the list by last name. Wondering how? Use ExcelÆs Text to column feature to split the name into two columns by specifying comma as a delimiter. So Simple! Right?

Let me demonstrate this feature by splitting an address block.

Below is a screenshot of an actual data on which we are going to work:

1. Select the entire column (by clicking on the column heading), or a group of cells whose contents you need to split. In our case, I have selected cell B2.

2. From the Data menu, select Text to Columns.

3. You will be displayed with a window similar to one shown below to choose the type on how you want the contents to be split:

Delimited: If you want the contents to be split where ever there is a Tab, Semicolon, comma, space, or any other delimiter such as a new line.

Fixed width: When you do not have any such delimiters but can specify place where the split needs to occur. Here is an example at Microsoft Office Online.

In our scenario, we need to choose delimiter as a new line to split the contents line by line.

4. You should be a similar window once you select Delimited and press Next.Choose the appropriate delimiter from the resulting window. As I want the split to occur line by line, I need to choose new line character which is not readily available. So I choose Other, and in the box next to it, I press ôCtrl + Jö simulateneously which indicates to excel that a new line character should be considered as a delimiter.

One interesting feature I noted here is Text qualifier option. Assume that you have a Company name such as ôCompany A, Incö which you wouldnÆt want to split by specifying comma as a delimiter. In such a case, put a check next to ôTreat Consecutive Delimiter as oneö and select double quote or a single quote whichever as available to the split not to occur.

5. Click Next to display the following window:

Here you can choose the data format that you would like to apply by selecting the column as shown above and choosing appropriate format. Then you need to specify from where the results are to be placed by specifying the cell address in the destination box. For example, if you are working on a group of cells say B1 to B10 and would like to paste the results in the immediate next column say C1 to C10, just selecting C1 in the destination box would do the trick. Check the below screenshot that shows how wonderfully Microsoft ExcelÆs Text to Column feature has split the address block:

Option 1: Adjust the column width.

• Double-click the left column border in the column header to set the column to Best Fit. (Or)
• Drag the left column border in the column header to the width you want.

If the text is too long, you may have to scroll left to right to view all the row contents.

Option 2: Resize the contents to fit within the cell with these steps.

• Select the cell with text that’s too long to fully display, and press [Ctrl]1.
• In the Format Cells dialog box, select the Shrink To Fit check box on the Alignment tab, and click OK.

This might shrink the text to sacrificing legibility.

Option 3: Wrap the text.

• Select the cell with text that’s too long to fully display, and press [Ctrl]1.
• In the Format Cells dialog box, select the Wrap Text check box on the Alignment tab, and click OK.

This might increase the height of the cell.

Best Option: Insert a space in the cell next to the cell where you have the content. Excel should automatically truncate the long text display whereas your text will still be intact within that cell.

See image below:

Hope it helps.

1. DNS settings
2. Your Browser Settings.
3. Anti-virus / Firewall settings.
4. Rarely due to Virus/Trojan infection.



DNS settings: DNS (Domain Name System) is a service that translates domain names like yahoo.com to IP address. If one DNS server doesn’t know how to translate a particular domain name, it asks another one, and so on, until the correct IP address is returned. Usually for a home network, your DNS server IP address would be your gateway / router address or the main server IP address through which other systems connects to Internet. You can check the TCP/IP properties under Wireless Network properties to determine the current setting for DNS. This would be normally set to “Obtain DNS Server address automatically” unless specified by your Internet Service Provider or network administrator.

Browser Settings: For Wireless networks, make sure you run inetwiz from Start | Run and choose Connect using my Local area network (LAN) setting (Refer, article on creating a new broadband connection for additional help) . Additionaly, you can also check Tools | Internet Options | Connections tab | LAN settings to set to automatically detect proxy settings or specify any proxy IP address if used (for Internet Explorer only).

Firewall / Anti-virus settings: Check you firewall and anti-virus settings if they are not blocking ports used for your internet connection. Normally the default port 80 should be opened. You may also test your firewall settings by disabling it temporarily to see if you can access Internet.

Virus/ Trojan infection: Corrupted Windows XP files could cause your Internet connection not to work. Run anti-virus / anti-spyware like spybot/ adware to see if you can detect /remove/ clean any infected files.

Check the article on improving system performance for recommended links here.

Hope this helps. Please feel free to post your comments.