Microsoft Excel FAQ

General tips index

2007-04-05T03:26:00.001-07:00

General tips index

This section contains general tips that apply to Microsoft Excel and the Windows Operating System. This includes general definitions, concepts, and more. Browse the index for more.

Check Box
Dialog Boxes
Dropdown Box
How To Install Fonts
Hyperlink
Multiple Instances Of Excel
Toggle Button
Tool Bars
Tool Tips
What Are True Type Fonts

Open file as copy

2007-04-03T19:57:00.000-07:00

How to open a new copy of a Excel file?
If a copy of a file is opened, where would be the copy?
I have opened a file as copy. Can I use access the file after it is closed?

To open a file as copy, just follow the steps

Select your file through the file open box (CTRL+O)
Now at the bottom you may notice an ARROW BOX next to the OPEN button (in fact, it is a drop down box)
Click the arrow and select OPEN AS COPY.
Now you could be having a copy of the original version of the file.

Where is my Copy file?
How to access my Copy file?
If you open a Excel file using the OPEN AS COPY option, then a new file is created and saved in the same directory of the original file. So if you have a file, say test.xls in D drive. Then if you open the file as copy, then Excel creates a file in the D drive with the name Copy(1) of test.xls.
You can access this file through the path D:/Copy(1) of test.xls. You can always rename and copy your file somewhere else but by default Excel saves the copy of the file in the same drive. Also, Excel duplicates the original file and whatever is in the original file, it is copied including formats, formulas and other functionalities.

Multiple copies of a single file
If you open multiple copies of a single file, then Excel number it in ascending order. So if you open a second copy then Excel gives it the name D:/Copy(2) of test.xls and the third copy is named D:/Copy(3) of test.xls. So you can always access the. If you open the copy of a copied file then excel names it as copy of copy, copy of copy of copy and so on.
In the above example, if you open the file Copy(1) of test.xls as a copy then Excel names it as Copy(1) of Copy(1) of test.xls and if you open this file again as Copy then Excel would name it as Copy(1) of Copy(1) of Copy(1) of test.xls. It may sound a bit confusing but its quite clear.

Open a file as copy only it is absolutely required or you are working on an important file or you are working on other's job and need to preserve the original data. Opening a file as copy takes up disk space (its nothing when compared to 40GB disks and more) and adds your files list and make us a mess of things if you open too many copies.

Toolbars in Microsoft Excel

2007-04-03T06:07:00.000-07:00

What are tool bars?
Tool bars available in Microsoft Excel
Can custom tool bars be created in Microsoft Excel?

Definition:
Tool bars are list of commands arranged in a bar to facilitate one click action. It's a set of buttons arranged in a organised manner, the set of buttons being called tool bars and the buttons being called command buttons (other names are also being used to define a command button). Clicking a button on the tool bar triggers an action.

Shown above is the standard tool bar in Microsoft Excel.

Tool bars in Microsoft Excel:
The following tool bars are available in Microsoft Excel (Excel 2003)

Standard
Formatting
Borders
Chart
Control Tool box
Drawing
Exit design mode
External Data
Forms
Formula Auditing
List
Picture
Pivot table
Protection
Reviewing
Text to speech
Visual Basic
Watch Window
Web
Word Art
Worksheet Menu Bar

The above are the frequently used tool bars in Microsoft Excel that are displayed by right clicking the worksheet menu bar. There are also other tool bars such as 3D, Shapes, Color and more in Microsoft Excel.

Custom tool bars in Microsoft Excel:
You can create custom tool bars in Microsoft Excel and also assign actions and events to the tool bars you have created. Excel supports this feature. But you need to program in Visual Basic environment to create and assign actions in tool bar.

Open file as read only

2007-03-05T05:17:00.001-08:00

How to open a Excel file as read only?
How to open a new copy of a Excel file?
Can I open my Excel file in Internet Explorer?

To open a file as read only, just follow the steps

Select your file through the file open box (CTRL+O)
Now at the bottom you may notice an ARROW BOX next to the OPEN button (in fact, it is a drop down box)
Click the arrow and select OPEN READ ONLY.
Now you could be having a read only version of the original file.

You could use this option if you just want to view the file and don't want to make any changes. You should select a file before using this option. If you navigated to the folder but haven't selected the file, the OPEN READ ONLY option would not be highlighted (The option would show up but you cannot click on it. It would be disabled)

To open a copy of a file, just follow the steps and instead of OPEN READ ONLY use the OPEN AS COPY option. The step by step instructions are:

Select your file through the file open box (CTRL+O)
Now at the bottom you may notice an ARROW BOX next to the OPEN button (in fact, it is a drop down box)
Click the arrow and select OPEN AS COPY.
Now you could be having a copy of the original version of the file.

Note:
If you open a copy of the file, then a duplicate version of the same file is created in the directory of the original file. See more here

You can also use the OPEN AND REPAIR option if you suspect your file is corrupted. Excel may present you with a message box which you may utilise.

You cannot view Microsoft Excel files in Internet Explorer from withing Microsoft Excel. But you can open it from Internet Explorer. The OPEN IN BROWSER button would not highlight if you select a Excel file but you can use this option with a html file (The option would get highlighted if you select a html file or any web page)

Create a read only file

2007-03-05T05:17:00.000-08:00

How to create a read only file?
How to make a file read only?
How to make a read only file read write?

By default, all files created and opened in Microsoft Excel are read write files. This applies not only to Microsoft Excel but almost all other applications. Also, a file cannot be created read only just because if you create a read only file you can never edit it. But you can make a file read only so that others don't tamper with it.

To make a Excel file read only (Microsoft Excel 2003),

Goto Tools ->Options->Security (The Tools menu in the worksheet menu bar)
Check the READ-ONLY RECOMMENDED box.

This would make your file read only and when the file is opened, it would prompt you with the message box whether you want the file to be opened in read only mode or not. If you click YES, the file would open in read only mode. If you click NO, the file would open in read write mode and you can save your changes.

For Microsoft Excel 2000 and other previous versions

Goto File->Save As
Click the TOOLS button on the right corner and select GENERAL OPTIONS
Now check the READ-ONLY RECOMMENDED box.

This works well in Microsoft Excel 2003 also.

The above option, though making the file read only, gives the user the option to edit it. So even if an user unknowingly presses the NO button , when prompted for read only opening, he may still be able to edit the file. If you want to prevent this then,

Navigate to your file through Windows Explorer or the File Open Box and select it.
RIGHT CLICK and select PROPERTIES
Check the READ ONLY box.

This always open the file read only and even if you already made your file read only, by using the first option, this overrides it since this function is part of the operating system rather than the application. To make the file read write again, just uncheck the READ ONLY box.

Read only file

2007-03-05T00:30:00.000-08:00

What is a read only file?
How to make changes to a read only file?
How to save changes made to a read only file?

READ ONLY FILE
A read only file, as the name implies, is a file whose contents can be only read but cannot be written into. In other words, you can view the contents of a read only file but cannot change or make modifications to that file. Of course, a read only file is not a protected file but one which cannot be written into. You can make changes and modifications to a read only file but the same cannot be saved in the same file name. For example, if you have a file name test.xls and you have made it a read only file, then changes can be made to that file but the same cannot be saved under the name test.xls but can be saved with an another name.

SAVING A READ ONLY FILE
As already pointed out, read only files cannot be saved in the original name and they must be saved in an another name. So follow the steps below to save a read only file

Go to FILE->SAVE AS (The save as dialog box would be displayed)
Now select the path where you want to save the file from the LOOK IN box.
Now type a file name in the FILE NAME box below
SAVE the file by clicking the SAVE button.

The above steps holds good for almost all applications.
If you want to save the read only file in its original name, then save the file in another name, delete the original file and rename the newly created file.

Also see,
Creating a read only file
Open a file as read only

Instance

2007-03-05T00:15:00.000-08:00

Instance
Multiple Instances
Opening multiple instances of Microsoft Excel.

The word instance can be defined as the number of occurrences of an event. Thus when you start Microsoft Excel for the first time, then it may said as the first instance of Microsoft Excel. When you open Microsoft Excel for the second time with the first instance already running, then it is said to be the second instance of Microsoft Excel.

Normally, applications are objects created by programming languages. So you can run multiple instances of the same application. But running multiple instance causes your CPU to allocate more memory. Thus the number of times you open an application depends on your CPU memory (RAM). As long as your CPU permits, you can keep on opening multiple instances of an application.

It is normally recommended that you don't run multiple instances of the same application. This is because running multiple instances not only act as a drag on your CPU but it is difficult for the user too to navigate through the applications. Thus unless it is necessarily required, you need not open multiple instances of an application. The above apply not only to Microsoft Excel, but almost for all applications, in general.

Opening Multiple Instances
To open an other instance of Microsoft Excel, just click the Excel icon on the desktop or select Microsoft Excel from Programs->Microsoft Office->Microsoft Excel

Possible errors and solutions:
Cannot open multiple instances of Microsoft Excel

This may due to the fact that CPU is already full on memory and cannot allocate memory for the new instance (This is almost a rarity and would happen only if you are using a RAM less than 64 MB and have loaded quite a good number of applications).
Some or all of the libraries required for Microsoft Excel cannot open (This may happen if you have loaded a lot of add-ins and COM automation at Excel start up. Reinstalling Microsoft Excel may sort out this problem)

I get a lot of message boxes when I open the second instance of Microsoft Excel

You may have specified Excel to open a set of files at start up. Since they are already opened in the first instance, when Excel tries to reopen them at the second instance, it displays a message box indicating the files are already open. To solve this problem, try sticking to a single instance of Microsoft Excel.

Microsoft Excel automatically opens a second instance

When an external application such as email contains an Excel attachment, it may launch Excel again to display the contents. This is because,the external application may instruct to create a new instance of Microsoft Excel to load the file (You normally may not experience such problems with Outlook and other recognised software creators). To solve this, download the file to the computer and open it.
If a new instance of Microsoft Excel is opened all of a sudden, then somebody might have scheduled to run it. Check Windows Scheduler for details.

I cannot open a file in the second instance of Microsoft Excel or my file open as read only

By rule, a file with the same extension cannot be opened more than one time. Thus if you have opened file in one instance, you cannot open the same file in second instance. Also, if the file is shared on network and is opened by an other user, you cannot open it. To solve this, close the file and try reopening again or if you need to just look at the contents, open the file as read only.

Default workbook

2007-03-05T00:12:00.000-08:00

Default workbook in Microsoft Excel

By default, Microsoft Excel (all versions) creates a workbook when you start Microsoft Excel. The name of the workbook is usually Book1. If you open multiple instances of Microsoft Excel, then each of the instance would open a new workbook. The number of worksheets in the workbook depend on the option set for the number of worksheets in the default options.

Microsoft Excel doesn't open a default workbook under the following circumstances,

When you open a file from START->DOCUMENTS.
When you open a file from Windows Explorer.
When you download and open a file from a web browser.
When Microsoft Excel is set to open a default workbook when it starts.
When any file is stored in the XLSTART folder.

There may also other circumstances where Microsoft Excel may not open a workbook. If you experienced such circumstances, you may post in the comments section.
There seems to be no way from making Microsoft Excel not open a workbook when it starts. If you have an idea, you may post it.

Entering percentages

2007-02-27T20:20:00.000-08:00

How to enter percentages in Excel?
When I format cells as percentages, the exact percentages don't show up. They get multiplied by 100.

To convert a number into percentage,
RIGHT CLICK->FORMAT CELLS->NUMBER->PERCENTAGE and CLICK OK.
You can also specify the number of decimal places to follow percentage.

Tip:

It is always advised to format cells as percentage before you enter values. This is because if you enter your values and format it to percentage, they get multiplied 100 since cent percentage is equal to 100. For example, if you enter the number 4 and format cells as percentage, you get the value 400%. But if you format the cells as percentage and enter 4, you get the value 4%.You can also enter decimal values if you format the cells prior to entering values.

Full screen

2007-02-15T19:54:00.003-08:00

How to maximize the screen capacity in Microsoft Excel?
How to view full screen in Microsoft Excel?
How to turn off full screen in Microsoft Excel?

Full screen is an option in Microsoft Excel, whereby you could hide all the tool bars and maximize the viewing capacity. This would especially be useful if you are to type in a large amount of data or the nature of your work don't need tool bars for a wrong time

To turn on FULL SCREEN, Go to VIEW menu and click FULL SCREEN.
To turn on FULL SCREEN, just repeat the step since the Full screen button is a toggle button.

By default, FULL SCREEN is turned off in Microsoft Excel.

When you use the Full screen option for the first time, you would see a new tool bar with the name CLOSE FULL SCREEN. Close this tool bar (To close it, click the X on the right) . Don't click the CLOSE FULL SCREEN button since it would turn off the full screen.

The full screen options removes the tool bars, status bar and the formula bar. Full screen doesn't mean that you could not have your tool bars. You can always have your favorite tool bar by selecting it from the view menu

Tool tips

2007-02-15T19:53:00.000-08:00

What are tool tips?
How to disable tool tips in Microsoft Excel?
How to enable tool tips in Microsoft Excel?

Definition
Tool tips are small graphical interfaces that shows up text (rarely images) when the mouse pointers hover them. Tool tips show a box with text when you hover the mouse on them. Tool tips provide important information and they are quite useful in giving us quick information about a tool bar or a command without activating them. It must be noted that tool tips just show information and do not activate a command nor they trigger any action. Tool tips often instruct on the command name or give a brief description about the action a command would trigger if it is clicked.Just move around your mouse on the tool bars to have a look at the tool tips

Tool tips in Microsoft Excel (and almost all other applications) show a short description of what the tool bars would do when clicked. Descriptions such as New,Open, Save indicate that clicking the button would create a new file, open a file, save a file. In fact, they provide quick reference to the actions that would be performed by the tool bar buttons.

To enable tool tips in Microsoft Excel (and almost all other applications)

RIGHT CLICK on the Worksheet Menu Bar (The menu bar at the top containing File, Edit, View menus)
Select CUSTOMIZE and select the OPTIONS tab
Now check the SHOW SCREEN TIPS ON TOOL BARS option.
Close the dialog box using the CLOSE button

By default, tool tips are enabled in Microsoft Excel unless you disable it. Most users don't disable it as it is quite useful and provides quick handy information.

To disable tool tips just follow the above steps and uncheck the SHOW SCREEN TIPS ON TOOL BARS option.

Step by step instruction

RIGHT CLICK on the Worksheet Menu Bar (The menu bar at the top containing File, Edit, View menus)
Select CUSTOMIZE and select the OPTIONS tab
Now check the SHOW SCREEN TIPS ON TOOL BARS option.
Close the dialog box using the CLOSE button.

Gridlines

2007-02-15T00:18:00.001-08:00

What are grid lines?
My grid lines are gone. Where can I find them?
How to change the color of the grid lines?

Grid lines are horizontal and vertical lines found in Microsoft Excel. They are just like graph lines.

By default, grid lines are on in Microsoft Excel. To turn them off,

Goto TOOLS->OPTIONS->VIEW
Uncheck the GRID LINES box.
Click OK

To turn them on,

Goto TOOLS->OPTIONS->VIEW
Check the GRID LINES box.
Click OK

To change the grid lines color,

Goto TOOLS->OPTIONS->VIEW
Next to GRIDLINES COLOR box, select your color.
Click OK.

Hyperlink

2007-02-15T00:17:00.004-08:00

What is a hyperlink?

A link in a document to information within that document or another document. These links are usually represented by highlighted words or images. When a reader selects a hyperlink, the computer display switches to the document or portion of the document referenced by the hyperlink.

Hyperlinks are mostly shown in blue color with a underline to indicate that clicking it would take you to a new page or an another document. Most hyperlinks are text links. If a graphic,image or a picture is used as a hyperlink, it is generally called a hot spot.

Delete columns

2007-02-15T00:17:00.003-08:00

How to delete columns in Microsoft Excel?

To delete rows RIGHT CLICK->DELETE->ENTIRE COLUMN
Alternatively, you may select the column you want to delete and EDIT-> DELETE->ENTIRE COLUMN. (This is not at all a shortcut)

If you want to delete column in a single click, you should

Select the entire column
RIGHT CLICK ->DELETE

If you want to delete more than one column in a single click just follow the above steps ,by selecting a range of cells, rather than a single cell.

You can also repeat deleting columns by pressing CTRL+Y. But to use this option you must have deleted the column from the EDIT menu.

Also see,
Delete rows
Insert columns
Insert rows

Delete rows

2007-02-15T00:17:00.001-08:00

How to delete rows in Microsoft Excel?

To delete rows RIGHT CLICK->DELETE->ENTIRE ROW.
Alternatively, you may select the column you want to delete and EDIT-> DELETE->ENTIRE ROW. (This is not at all a shortcut)

If you want to delete rows in a single click, you should

Select the entire row
RIGHT CLICK ->DELETE

If you want to delete more than one row in a single click just follow the above steps ,by selecting a range of cells, rather than a single cell.

You can also repeat deleting rows by pressing CTRL+Y. But to use this option you must have deleted the column from the EDIT menu.

Also see,
Delete columns
Insert columns
Insert rows

Insert Columns

2007-02-15T00:16:00.001-08:00

How to insert columns in Microsoft Excel?
How to insert more than one column at a time?

To insert rows, select the cells above which you want to insert rows. Go to the INSERT menu and select COLUMNS. Alternatively, you can RIGHT CLICK->INSERT->ENTIRE COLUMN to insert a column.
If you want to insert column in a single click, you should

Select the entire column
RIGHT CLICK ->INSERT

If you want to insert more than one column in a single click just follow the above steps ,by selecting a range of cells, rather than a single cell.

If you insert a column it is normally inserted to the left of the cell. So if you insert a column from B2 it would be inserted to the left of B2

You can also repeat inserting columns by pressing CTRL+Y. But to use this option you must have insert column from the Insert menu.

Also see,
Insert rows
Delete rows
Delete columns

Insert rows

2007-02-15T00:15:00.000-08:00

How to insert rows in Microsoft Excel?
How to insert more than one row at a time?

To insert rows, select the cells above which you want to insert rows. Go to the INSERT menu and select ROWS. Alternatively, you can RIGHT CLICK->INSERT->ENTIRE ROW to insert a row.
If you want to insert row in a single click, you should

Select the entire row
RIGHT CLICK ->INSERT

If you want to insert more than one row in a single click just follow the above steps ,by selecting a range of cells, rather than a single cell.

If you insert a row it is normally inserted above the cell. So if you insert a row from B2 it would be inserted above B2.

You can also repeat inserting rows by pressing CTRL+Y. But to use this option you must have insert row from the Insert menu.

Also see,
Insert columns
Delete rows
Delete columns

Hide or unhide a workbook

2007-02-14T21:21:00.001-08:00

How to hide a workbook?
How to unhide a workbook?

To hide a workbook,

Goto Window->Hide

To unhide a workbook,

Goto Window->Unhide

If you have more that one worksheet hidden, then the list of all workbooks hidden would be displayed, from which you may select the workbook you want to unhide.

Note:
Always unhide a worksheet when you quit Microsoft Excel. This is because if you start Microsoft Excel, the next time again, and open the file (workbook) you have hidden, you may not see it. Of course, you can unhide it from the window menu but it is preferred to unhide a workbook before closing it.

Tips and tricks index

2007-02-14T21:17:00.001-08:00

Microsoft Excel tips and tricks index

This section contains tips and tricks in Microsoft Excel, which you would find really useful. This covers a wide range of topics. Just browse from this index or use search to find your query.

Add A Range Of Cells
Cell Reference When Cut,Paste Formulas
Convert Grams Into Kilograms
Count A Range Of Cells
Count Numbers In A Range Of Cells
Delete Rows With Zero Values
Entering 00, Preceding Numbers With Zero
Entering Percentages In Excel
Find Average From A Range Of Cells
Find Maximum Value From A Range Of Cells
Find Minimum Value From A Range Of Cells
Hiding Zeroes In Worksheet
Short Cut For Entering Time And Date
Shortcut For Displaying Formulas

Dropdown box

2007-02-14T05:02:00.000-08:00

What is a drop-down box?

A drop-down box is a common control with several options among which the user should select one option. The drop down box often comes with a default option which would work in case the user fails to select a option. It acts as an interface between the user and the computer. One such drop down box is show on the side

Display formulas

2007-02-13T22:25:00.002-08:00

How to quickly find out the cells that contain formulas?
Shortcut to quickly display formulas.

To quickly display formulas in worksheet, press CTRL + ~
To display values, press CTRL + ~ again.

This would display all cells that contain formulas. If your worksheet is protected then pressing CTRL + ~ would not display the formulas but cells containing formulas can be identified by an increase in column width.

Delete rows or columns with zero values

2007-02-13T22:25:00.001-08:00

How to delete rows that contain zero values?

Sometimes you may be having a list of names and corresponding values. Certain names may contain zero values and to delete those names one by one, would rather turn out be a nightmare task. To do this within a few clicks, just follow the steps:

Goto DATA->FILTER and select AUTOFILTER
You would see a arrow mark in all the columns. This is the FILTER button
Now go the column that contains zero values and click the FILTER button.
The FILTER button would display the list of all values in the column in a drop-down box. Now select 0 from the drop-down box.
The FILTER button would turn into BLUE color and would display all the zero values. Now RIGHT CLICK and select DELETE ROW.
Click the FILTER button again and select ALL to display the values again.

Worksheet navigation

2007-02-13T02:25:00.000-08:00

How to navigate through worksheets?

In case you have lesser number of worksheets, say 4 or 5, you can easily navigate through them by clicking the sheet tab. In case you have a large number of worksheets, say 10 or more, you can use the worksheet navigation tab.

The above bar is located on the top left of the sheet tabs. Right click on the sheet tab to display the list of all worksheets. The worksheet navigation tab has four buttons. The first button takes you to the first worksheet and the last button to the last worksheet (similar to the Home and end key). The second button takes you to the next worksheet and the third button the the previous worksheet.

It is advised not to keep too many worksheets in a workbook, unless it is absolutely necessary, since they may result in navigation problems (not for the computer)

Convert grams into kilograms

2007-02-12T22:00:00.001-08:00

How to convert grams into kilograms?
How to convert kilograms into grams?

Assuming that you have typed your data in grams and your boss wants them in kilograms and that too in short time. To make things worse, your data is quite big to effect changes.
No problem, you can do this at a flash. Just follow the steps below:

Type 1000 in an empty cell
Right click and select copy (or press CTRL+C)
Now select the range of your data (assume that your data in grams consists of the range A1:A120").
Right click and select PASTE SPECIAL.
Select the DIVIDE option from the OPERATION group box.
Click OK and your job is done.

To convert kilograms into grams repeat the above steps and the select the MULTIPLY option instead of the DIVIDE option from the OPERATION group box. You can use the same tip to convert any unit into another. Just use the appropriate option.

Default Font size

2007-02-12T21:59:00.005-08:00

What is the default font size in Microsoft Excel?
How to change the default font size in Microsoft Excel?

The default font size in Microsoft Excel is 10 points (all Excel versions)
The minimum font size is 1 point and the maximum font size is 409 points.

Know more about font size here

To change the default font size,

Go to TOOLS->OPTIONS
Select the GENERAL tab
Change the FONT SIZE in the SIZE box. (This would be located to the right of the Standard Font box.

To know how to change the default font,click here

Also see,
True type font
Install a font