Excel Guru

Subtotal by age

May this New Year brings you a peace filled life, warmth and togetherness in your family and much prosperity! Happy New Year

Hello Friends

MS Excel 2007: How to Create a Pivot Table

1. In this example, the data for the pivot table resides on Sheet1.

2. Highlight the cell where you'd like to see the pivot table. In this example, we've selected cell A1 on Sheet2.

Next, select the Insert tab from the toolbar at the top of the screen. In the Tables group, click on the arrow under the PivotTable button and select PivotTable from the popup menu.

3. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D2156 in Sheet1.

4. Your pivot table should now appear as follows:

Next, choose the fields to add to the report. In this example, we've selected the checkboxes next to the Order ID and Quantity fields.

5. Next under the Values box, click on the "Sum of Order ID" and drag it to the Row Labels box.

Finally, we want the title in cell A1 to show as "Order ID" instead of "Row Labels". To do this, select cell A1 and type Order ID.

Your pivot table should now display the total quantity for each Order ID as follows

Set up a cell to allow a maximum number of characters

1. Select the cells that you wish to restrict to a certain number of characters. In this example, we've selected cells A2 to A6.

2. Select the Data tab in the toolbar at the top of the screen. Then in the Data Tools group, click on the Data Validation drop-down and select Data Validation.

3. When the Data Validation window appears, set up your criteria. In this example, we've setup the cells to allow a text length of less than or equal to 15.

4. Next, click on the Error Alert tab.

5. Enter an Error message to appear when data entered does not conform to the validation rules.

6. Now if a value is entered in one of those cells that is longer than 15 characters, the following error message will appear:

As you can see, currently the formulas are visible. When you select cell A1, you can see the formula in the formula bar.

To hide the formulas, first you'll need to un-protect all of the cells on your sheet. To do this, select all of the rows and columns in your sheet. Right-click on then select "Format Cells" from the popup menu.

When the Format Cells window appears, select the Protection tab. Uncheck the "Locked" checkbox. Click on the OK button.

Next, select the cell(s) that you wish to hide the formulas for. Right-click and then select "Format Cells" from the popup menu.

When the Format Cells window appears, select the Protection tab. Check the "Hidden" checkbox. Click the OK button.

For the formulas to be hidden, you must also protect the worksheet. To do this, select the Review tab in the toolbar at the top of the screen. Then click on Protect Sheet button.

A "Protect Sheet" window will appear. You may enter a password to protect the sheet if you wish. The password is optional. Click on the OK button.

Now when you view your spreadsheet, the formula in cell A1 will no longer appear in the edit bar when the cell is selected.

Question: How do I create a superscript value in a cell in Microsoft Excel 2007?

Answer: step 1 - select the text that you wish to convert to superscript. This can either be the entire cell or only a character in the cell.

step 2 - While your mouse is over the selected text, right-click and then select "Format Cells" from the popup menu.

step 3 - When the Format Cells window appears, select the Font tab. Check the Superscript checkbox.

step 4 - Now when you return to your spreadsheet, you should see the selected text as a superscript value.

MS Excel: How to use the SUMIF Function (WS)
This Excel tutorial explains how to use the Excel SUMIF function with syntax and examples.

Description

The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000).

The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIF function can be entered as part of a formula in a cell of a worksheet.

To add numbers in a range based on multiple criteria, try the SUMIFS function.

Excel Formula for remove extra spaces from text line

Example (as Worksheet Function)

Let's look at some Excel ROUNDUP function examples and explore how to use the ROUNDUP function as a worksheet function in Microsoft Excel: