Tuesday, February 23, 2010

Turn off AutoComplete in Excel 2010

Using AutoComplete, you speed up the operation of entering data in the cells and with error free. If you feel this feature has little use and decide to turn off this feature then you can do it by performing the following steps:
1. Open Microsoft Excel.
2. Click File menu and click Options to open Excel Options dialog box.
3. From the left pane, click Advanced.
4. From the right pane, in the Editing options section, click to remove the checkmark beside Enable AutoComplete for cell values.
5. Click OK.

Turn off Gridlines in Excel 2010

If you had applied some formatting to the worksheet and feel that the gridlines that appear by default seem disturbing to your formatting then you may turn off the gridlines. Perform the following steps to turn off the gridlines:
1. In Microsoft Excel, click the View tab.
2. In the Show section, click to clear the checkmark beside Gridlines.

Please note that the turning on or off the gridlines applies only to a particular worksheet. If you need to turn off the gridlines to another worksheet then you have to activate that particular worksheet by clicking on the worksheet tab and performing the two steps provided above.

Add or remove background image to the worksheet in Excel 2010

In Microsoft Excel, adding a background image is pretty easy. Before proceeding with the steps, you have to make sure that the exact location of the background image is known. Perform the following steps to add a background image:
1. Click the Page Layout tab, in the Page Setup section, click Background.
2. From the Sheet Background dialog box that appears, navigate to the location of the image file. Once the image file is located, click the image file and then click Insert.

If the worksheet has an image and you need to remove the background image then perform the following to do so:
Click the Page Layout tab, in the Page Setup section, click Delete Background.

Hide or unhide rows and columns in Excel 2010

If your workbook contains sensitive data and you do not want the data to get exposed then you may hide the rows or columns. Hiding a row actually means that the height of the row is set to 0 and hiding a column actually means setting the width of the column to 0.
To hide a row, click on the cell first. In Home tab, click Format, hover the mouse over Hide & Unhide and finally click Hide Rows.
To unhide a row, the above and below row to the hidden row must be selected first. From the Home tab, click Format, hover the mouse over Hide & Unhide and finally click Unhide Rows. For example, to unhide row 5, you have to click on any cell in row 4 and then press and hold the Shift key, click a cell in row6 and continue performing the unhide process.
To hide a column, click on the cell first. In Home tab, click Format, hover the mouse over Hide & Unhide and finally click Hide Columns.
To unhide a column, select the columns that are just left and right to the hidden column and then from the Home tab, click Format, hover the mouse over Hide & Unhide and finally click Unhide Columns.

Adjust row height in Excel 2010

If your workbook contains text or other information that spans multiple lines then the necessity arises for the row height to get increased. There are many ways to adjust the row height. Take a look at them:
1. Manually adjusting the row height – To manually adjust the row height, place the mouse cursor at the intersection between the row headers and then drag to suit. For example, if you need to increase the height of row 2, place the mouse cursor between row 2 and 3. When you see the double headed arrow, keep the left mouse button in the pressed state and drag towards up or down.
2. Adjust to a specific setting – To adjust to a specific setting, right-click on the row header and click Row Height… to open the Row Height dialog box. Input a value for the row height and then click OK.
3. Automatically adjust the row height – There are two ways by which you can adjust the row height automatically.
a. Adjust the height based on the cell value that has the largest height value – Place the mouse cursor at the intersection between the row headers and then double click. This will automatically adjust the row height.
b. Adjust the height based on a particular cell height – Click on a cell and then from the Home tab, in the Cells section click Format and finally click Autofit Row Height.

Make a file read-only in Excel 2010

If your Excel file has important information and you do not want the file to be edited then you may make the file as a read-only file. Perform the following steps to do so:
1. Open the Microsoft Excel file that you need to make as read-only file.
2. Click File menu and click Save As to open the Save As dialog box.
3. Enter a name in the File name: box.
4. Click Tools button. From the drop-down box, click General Options…
5. From the General Options dialog box, click to place a checkmark beside Read-only recommended.
6. Click OK.

When you try opening the file, you will receive a message box that says that the file should be opened as read-only unless you need to save changes to it. Click Yes. Now, edit data from the file and try saving the Excel workbook. You will receive a warning box that says that the file is a read-only and so on. In order to make changes to the file, you have to save it in a different name. Click OK. You will see the Save As dialog box. Enter a name for the workbook.

Clear the contents in cells in Excel 2010

For explaining the concept of clearing contents in cells, we will have 4 cells (B2, B3, B4, and B5). Note the font of the cells B2, B3, B4, and B5. Now, set the font of B2, B3, B4, B5 to Arial, set the size of the font to 18 and make it appear bold.

In cell B2, enter Arial 18 B + C and then press the Enter key. Right-click cell B2, click Insert Comment and then enter Comment and Arial font of size 18 will not be deleted but the data in the cell is cleared.
In cell B3, enter C and then press the Enter key. Right-click cell B3 and click Insert Comment and then enter Content in the cell and the comment will not be deleted.
In cell B4, enter Arial 18 B and then press the Enter key. Right click cell B4 and click Insert Comment and then enter Comment will be deleted but the contents and the formatting is present.
In cell B4, enter XYZ and then press the Enter key. Right click cell B5 and click Insert Comment and then enter Data in the cell plus formatting and comments will be deleted.

Now for the explanation, there are five types of clearing information. They are Clear All, Clear Formats, Clear Contents, Clear Comments, and Clear Hyperlinks. To clear information, click on the cell that you have to clear the information and then click the Home tab, in the Editing section, click Clear and then click on any of the types.
Clear All – When you click this option, all the data, formatting applied to the cells, and comments in the cell(s) will be deleted.
Clear Contents – This is the normal way people clear the contents in the cell. Excel users just press the delete key from the keyboard. Doing so will clear the information but the formatting and the comments (if any) still remain in the cell(s).
Clear Formats – When you click on this option, the content in the cell and the comments in the cell will still be present but the formatting will be cleared.
Clear Comments – When you click on this option, the comments will be cleared.
Clear Hyperlinks – When you click on the Clear Hyperlinks, the hyperlink will turn to a non-hyperlink but the formatting still remains. You have to click the Clear Formats.

View workbook properties in Excel 2010

To view the workbook properties like the name of the file, size of the file, where the file is stored, author of the file, when the file was created and more. Perform the following steps to view the workbook properties:
1. Open the Excel file for which you need to view the properties.
2. Click File menu. By default, Info tab is highlighted and the contents in the Info tab will be displayed.
3. In the middle pane, you can view the name and the place where the file is stored. You will also see the file extension (Example: .xls, .xlsx).
4. In the right pane, you will see a brief list of the properties. For viewing all the properties, click the Properties button that has a button at the right and then click Advanced Properties.

Wrap Text in a cell in Excel 2010

If you had entered some text in a cell and later discovered that part of text has been truncated due to too much wide text. To correct this, you may wrap the text in the cell. To wrap the text, perform the following:
1. Select the cell that you need to wrap the text.
2. From the Home tab, in the Alignment section click Wrap Text.
Now the text will be displayed in multiple lines. Note that the row height will also be increased.

The above provided way is the quickest way to wrap text in a cell. You may also perform the same operation by performing the following steps:
1. Right-click on the cell that you need to wrap the text and then click Format Cells... to open the Format Cells dialog box.
2. Click the Alignment tab. In the Text control section, click to place a check mark beside Wrap text.
3. Click OK.

Adjust column width in Excel 2010

If your workbook contains numbers, dates or other information and you see that the cell contains symbols that is similar to ###### then this means the width of the cell is less and is not able to hold the data. To avoid the symbols, just increase the column width. There are many ways to adjust the column width. Take a look at them:
1. Manually adjusting the column width – To manually adjust the column width, place the mouse cursor at the intersection between the column headers and then drag to suit. For example, if you need to increase the width of column B, place the mouse cursor between column B and C. When you see the double headed arrow, keep the left mouse button in the pressed state and drag towards right or left.
2. Adjust to a specific setting – To adjust to a specific setting, right-click on the column header and click Column Width … to open the Column Width dialog box. Input a value for the column width and then click OK.
3. Automatically adjust the column width – There are two ways by which you can adjust the column width automatically.
a. Adjust the width based on the cell value that has the largest width value – If you have tens to thousands of rows of data then in order to adjust the column width, place the mouse cursor at the intersection between the column headers and then double click. This will automatically adjust the column width.
b. Adjust the width based on a particular cell width – Click on a cell and then from the Home tab, in the Cells section click Format and finally click Autofit Column Width.

Setting workbook properties in Excel 2010

When you create and use a workbook, Microsoft Excel automatically enters information regarding the workbook like size of the file, when the file was created, when the file was modified, where the file is saved, author of the file, was the file printed and many other details. There are other properties that you can set. To do so, perform the following steps:
1. Open the Microsoft Excel workbook.
2. Click File menu. By default the contents in the Info tab section will be displayed. To set the properties, from the right-pane, click Properties button. You will see Show Document Panel, Advanced Properties, and Show All Properties.

You may click on any of the three options to set the properties. Once you had set the properties, you may open Windows Explorer and go the location of the workbook. Now, set the view of the window to Details (Right click on an empty space and hover the mouse over View. Finally click Details). Now, right-click the heading section (Example: Name or Date modified or Type or Size) and click More… to open Choose Details dialog box. From the list, place a check mark to the entries that you set in Microsoft Excel (Example: Tags, Author, Title, etc).

Select multiple cells in Excel 2010

Selecting multiple cells means selecting cells that span more than one cell. This may involve selecting part of data in a row or column sequentially, selecting part of data that spans multiple rows and columns, selecting the entire row or column, selecting the entire worksheet, and selecting non-sequential cells. Continue reading to know how to select multiple cells. Refer to the diagram for information related to selecting multiple cells.
1. Selecting part of data in a row or column sequentially - To select multiple cells in a row, click the first cell. Hold the Shift key and then click the last cell in the same row. You may also perform the same by clicking the first cell and holding the Shift key and pressing the right-arrow key to select the cells towards right. Clicking the first cell, holding the Shift key and pressing the left-arrow key will select the cells towards left. (Example: Click cell B1, press the Shift key. Finally click cell E2).
To select multiple cells in the column, click the first cell, hold the Shift key and click the last cell in the column (or) click the first cell, hold the Shift key and press the Up or Down arrow to select cells in a single column.
2. Selecting part of data that spans multiple rows and columns – This selection is similar to selecting part of data in a row or column sequentially except that the last cell selection will be spanning more than a row or column (Example: Click Cell B1, press the Shift key and click cell D4. You may also try clicking B2, pressing the Shift key and clicking C4).
3. Selecting the entire row or column – To select an entire row, click the row number (Example: 2 or 3 or 4 or 5). To select an entire column, click the column heading (Example: B or C or D or E or F).
4. Selecting the entire worksheet – To select the entire worksheet, press Ctrl + A (Or) press the grey box that is to the left of the Column heading A and is at the top of row number 1.
5. Selecting non-sequential cells – To select non-sequential cells, click the first cell. Press and hold the Ctrl key. Now click the cells that you need to select (Example: Click B3, press and hold the Ctrl key. Now, click cell D2, cell F4, cell A3, and cell B2).

Switching among open workbooks in Excel 2010

If multiple workbooks are opened then at any given time only one workbook will be active. To switch to another workbook, you have to activate that workbook. There are several ways for switching among open workbooks in Microsoft Excel.
1. From the Windows taskbar, click the Excel workbook that you need to open.
2. In Microsoft Excel, click View tab from the Ribbon and click Switch Windows. You will see the list of workbooks that are opened. The same can be performed by pressing Alt + W + W + (The number corresponding to the workbook).
3. You may add the Switch Windows button to the Quick Access Toolbar so that you can switch among workbooks quickly. To add the Switch Windows button, click the down arrow that is present in the Quick Access Toolbar and then click More Commands… to open Excel Options dialog box. From the Choose commands from dropdown box, click View Tab. Select Switch Windows from the left side of the selection box and then click Add >>. Finally click OK.

Comparing two workbooks side by side in Excel 2010

If you have workbooks with data that needs to be compared then Microsoft Excel has an excellent feature that helps you to compare two workbooks side by side. Excel arranges the two files horizontally. Perform the following steps to compare workbooks:
1. Open the workbooks that need to be compared.
2. With the focus on the workbook that needs to be compared, click View tab and then click View Side by Side. If you have more than two workbooks opened then you will see a dialog box with the names of the workbooks. Click on the workbook that needs to be compared and then click OK.
3. By default, Synchronous Scrolling button is enabled. This makes the two workbooks to scroll simultaneously. You may also disable the Synchronous Scrolling to disable the simultaneous scrolling of the workbooks.
4. If you had changed the height and width of the workbooks then you may click on the Reset Window Position button in the View tab to align the workbooks horizontally and also with equal size.
5. The last part is saving the layout of the workbooks for future reference. Click the Save Workspace button which is in View tab.

Change worksheet tab color in Excel 2010

Applying a color to the worksheet tab is easy. Right-click the worksheet tab and hover the mouse over Tab Color. Select a color from the color selector box. You may also apply the default color to the worksheet tab by right-clicking the worksheet tab and hovering over Tab Color and clicking No Color.

Changing the number of worksheets in Excel 2010

When you create a new workbook, three worksheets named Sheet1, Sheet2, and Sheet3 are created by default. You can change this behaviour by changing the number of worksheets to appear. Perform the steps to do so:
1. Open Microsoft Excel.
2. Click File menu and click Options to open Excel Options dialog box.
3. By default the contents in the General section appear. Under When creating new workbooks, enter a number between 1 and 255 in the Include this many sheets: box.
4. Click OK.

Specify a password for the workbook in Excel 2010

You specify a password to protect others from opening the workbook. When users try to open a workbook that has a password assigned then they will be prompted to enter the password in order to view the contents of the workbook. Perform the following steps to assign password to a workbook:
1. Open the Microsoft Excel file to specify a password.
2. Click File menu and click Info.
3. Click Protect Workbook and click Encrypt with Password to open the Encrypt Document dialog box.
4. Enter the password and click OK.
5. Confirm the password by entering the same password again.
Now, when you open the workbook, you will be prompted to enter the password.

Set the default file location in Excel 2010

By default, Microsoft Excel saves files in the My Documents or the Documents folder. You can change this location and set your own location to save the files. Perform the following steps to do so:
1. Open Microsoft Excel.
2. Click File menu and click Options to open Excel Options dialog box.
3. From the left pane, click Save.
4. In the Save workbooks section, specify the file location in Default file location: textbox.
5. Click OK.

Delete worksheets in Excel 2010

To delete worksheet in Excel 2010, perform the following steps:
1. Right-click the worksheet and click Delete.
2. In the Microsoft Excel dialog box that reads Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete, click Delete.

Rename worksheets in Excel 2010

By default, the worksheets are labeled Sheet1, Sheet2, and Sheet3. To rename a worksheet, perform the following:
1. Right-click on the worksheet and click Rename.
2. Type the name of the worksheet and press the Enter key from the keyboard.

Worksheet names can be up to 31 characters long and can include spaces. However, you cannot enter the following characters:
* (Asterisk), / (Forward slash), \ (Backward slash), ? (Question mark), and : (Colon).

Insert additional worksheets in Excel 2010

By default, three worksheets named Sheet1, Sheet2, and Sheet3 are created when you create a workbook. To add worksheets to your workbook, perform the following:
1. Under Home tab, in the Cells section, click Insert.
2. Click Insert Sheet.
That’s it; a worksheet is now added to the workbook. If a large number of worksheets are added then a few of them might be hidden. To navigate to the worksheets, click the First, Previous, Next, or Last navigation buttons that appear to the left of the worksheet tabs.You can also insert a worksheet by pressing Shift + F11 keys from the keyboard or by clicking on the tab that appears to the right of the labelled worksheet tabs (For example, clicking on the tab that appears to the right of Sheet3).

Rename a workbook in Excel 2010

If you feel that the workbook needs to be renamed then you may perform the following steps to do so:
1. Open Microsoft Excel.
2. Click File menu and click Open.
3. From the Open dialog box, navigate to the workbook that needs to be renamed. Once you had located the file, right-click on it and click Rename.
4. Type the name and press the Enter key from the keyboard.
5. Click Cancel button to close the Open dialog box.

How to save a workbook in Excel 2010?

To save a workbook in Excel 2010, click on the File menu and click on Save. If you are saving the file for the first time, you will see the Save As dialog box. From the next time, you may click on the Save button from the Quick Access Toolbar (or) click on the File menu and click on Save (or) press Ctrl + S (or) press Alt + F + S. The following is the summarization of the save operation in Excel 2010.
1. Click File menu and click Save (or) click the Save button from the Quick Access Toolbar.
2. The Save As dialog box will appear with the default settings unless you have changed the settings.
a. The workbook will be saved in the Documents folder. Change the location to save the workbook.
b. The workbook will have the name Book1. Save it under a name that best describes the workbook.
c. The workbook will be saved as Excel Workbook in the Save as type section. You can change the type section by clicking on the Excel Workbook and clicking a type from the dropdown box.
3. Click Save.

Please note that by default, Excel 2010 will save in a format that will be recognized by Excel 2007 or Excel 2010. For making your file to be recognized by an early version of Microsoft Excel, perform step 1. In step 2, in section c, click Excel Workbook and select Excel 97-2003 Workbook from the dropdown box.