You are here: Home / Excel 2007

How to view multiple worksheets in Excel 2007 / 2010

December 1, 2011 By

If you have a workbook with more than one worksheet, then you can view two or more worksheets at the same time. Follow these steps:

1. Select the first worksheet.

2. In the View tab, Window group click New Window.

3. Select the second worksheet. Now you have two windows, one on display and the other one in the background.

4. In the View tab, Window group click Arrange All. The Arrange Windows window will open.

5. Select one of the options then click OK. The Tiled option, for example, will show the sheets next to each other, one on the left and the other on the right. The Horizontal option will show them one under the other.

 

Filed Under: Customization, Excel 2007, Excel 2010 Tagged With: , ,

How to join text from multiple cells into one string

October 25, 2011 By

If you have multiple cells that contain text and you want to join or merge them in one cell then you can use the concatenate function.

Syntax:

CONCATENATE(text1, text2, text3 ……)

You can concatenate up to 255 items into one text string.

Example:

Cell A1 = “MBA”

Cell B1 = “Master of Business Administration”

Cell C1 contains the formula: CONCATENATE(A1; B1)

The value of C1 will be “MBAMaster of Business Administration”

You can also use literals (fixed text) in the CONCATENATE function. E.g. if C1 has the formula:

CONCATENATE(A1; “ – “; B1

Then the value of C1 will be “MBA – Master of Business Administration”

You can also use the ampersand (&) calculation operator to achieve the same result as the CONCATENATE function. The last example will look like this:

=A1 & ” – ” & B1

 

Filed Under: Excel 2007, Excel 2010, Formulas, Functions Tagged With: , , ,

How to import Access table into Excel 2007

July 14, 2011 By
  1. Open a new workbook, or an existing one and select a worksheet.
  2. On the Data tab, Get External Data group click From Access.

  3. The Select Data Source window will open. Select the Access database file and click Open.
  4. The Select Table window will open. Select the table you want to import and click OK.

  5. The Import Data window will open. Specify how you want to view the data and whether you want it in the existing worksheet or a new one. Click OK.

  6. Here is how your data will look like in Excel:

Filed Under: Data, Excel 2007, Excel 2010, General Tagged With: , ,

Conditional formatting in Excel 2007 , Top / Bottom rules

June 10, 2011 By

IF you have a range of data and you want to highlight, say, the top 10 values by giving them a unique fill color or font color, then follow these steps:

  1. In the Home tab, Styles group click Conditional formatting.
  2. Point to Top/Bottom Rules and click Top 10 Items.

  3. The Top 10 Items window will open.

  4. Specify the number of items to format (left) and the formatting options from the drop-down list on the right. If you are not satisfied with what is on the list, you can click Custom Format at the bottom and then set up your own.
  5. Your data will look like this.

Filed Under: Excel 2007, Excel 2010, Formatting Tagged With: , ,

How to calculate a running total in a column

June 2, 2011 By

Suppose you have a journal in which you register daily sales, and you want to know the accumulated sales each day.

We can do this in Excel as follows:

Column A:  Date entry.

Column B: Sales entry.

Column C: A formula to calculate the running total

  1. In C2 enter the same value as B2. This is the first row of sales.
  2. In C3 enter the formula : =C2+B3.
  3. Select cell C3 and drag the fill handle down until the end of your data.
  4. Now C4 will have the formula C3+B4, C5 will be C4+B5 and so on. The running total will be updated automatically.

Filed Under: Excel 2007, Excel 2010, Formulas Tagged With: , ,

Protect formula cells but allow access to data cells in Excel 2007 / 2010

May 12, 2011 By

By default all cells in a worksheet are locked. I.e when you protect a worksheet every cell in the worksheet will be protected.

To handle cell protection differently then you have to change the default settings for the required cells.

Here is how to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Uncheck the Locked and Hidden check boxes, then click OK.

These five steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Check the Locked and Hidden check boxes, then click OK.
  6. On the Home tab, Cells group, click Format.
  7. Under Protection click Protect sheet.

Applies to: Excel 2007 , Excel 2010

  1. On the Home tab, Cells group, click Format.
  2. Under Protection click Format cells. The Format Cells window will be shown.
  3. Click on the Protection tab.

Filed Under: Excel 2007, Excel 2010, Security and Protection Tagged With: , ,

How to create a user-defined function in Excel 2007 or Excel 2010

April 23, 2011 By

If you know Visual Basic programming then you can create your own user-defined functions in Excel 2007 or Excel 2010. Follow the steps below:

1.      Click on Excel Developer tab. If you can’t see the developer click Here.


2. On the Code group click Visual Basic.

3. You will be switched to Visual Basic Editor Menu.

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

Filed Under: Excel 2007, Excel 2010, Functions, Visual Basic Tagged With: , , ,

Automatically insert decimal points in Excel 2007 or 2010

April 13, 2011 By

To automatically insert decimal points when entering a number in a cell, follow these steps:

  1. Click the  Microsoft Office Button (File tab in case of Excel 2010).
  2. Click Excel Options (at the bottom right of the menu).

  3. Excel Options window will open.

  4. In Excel Options window (left side) click Advanced.
  5. On the right side  check the box labeled: Automatically insert a decimal point , then specify the number of decimal points you want in the combo box labeled: Places (next line).
  6. Click OK.

Filed Under: Customization, Data, Excel 2007, Excel 2010 Tagged With: , ,

How to change Excel 2007 default color scheme

March 30, 2011 By

To change Excel 2007 default color scheme, follow these steps:

  1. Click the  Microsoft Office Button.
  2. Click Excel Options (at the bottom right of the menu).

  3. Excel Options window will open.

  4. In Excel Options window (left side) click Popular.
  5. On the right side, next to Color scheme click the arrow of the drop down list and select the required scheme.
  6. Click OK.

Filed Under: Customization, Excel 2007 Tagged With: ,

How to split contents of a cell into adjacent columns

March 20, 2011 By

If you have a cell, or column, that contains some text and you want to distribute that text into the adjacent columns, then :

1.       Select the cell, range of cells within the same column or the entire column that you want to distribute its data.

2.       On the Data tab, Data Tools group, click Text to Columns. This will start the Convert Text to Columns Wizard .

3.       In the first step of the wizard specify your data type then click Next :

a.       “Delimited” means data fields are separated by special characters like commas. E.g ” Shakespeare,William “.

b.      “Fixed” means fields are aligned in equal size columns separated by spaces. E.g ” Shakespeare William “.

4.       In the second step of the wizard you specify the field delimiters, if you choose “Delimited” in the previous step, or specify the field widths, if you choose “Fixed” in the previous step. Click Next when you are done.

5.       The last step lets you specify the data format of each column, if it is different from the default set by Excel. Click Finish when you are done.

6.       The data will be distributed into a number of columns depending on the number of separators or spaces in the original column.

Filed Under: Excel 2007, Excel 2010, Formatting Tagged With: , ,
« Older Posts